pgAdmin is the most popular and feature-rich platform for administration and development of PostgreSQL databases. Check the pgAdmin official page for more information.
To connect to your remote PostgreSQL database server using pgAdmin 4, follow these steps:
NB: Only use sudo
if your PostgreSQL database was installed as root.
-
Run the command below to confirm your PostgreSQL version:
psql - version
-
Navigate to the PostgreSQL configuration directory on your server, which is in
/etc/postgresql/10/main
. Take note that10
is the version of my PostgreSQL installation on my server. Your version might be9.5
,11
or12
or any other version.cd ~ cd /etc/postgresql/10/main
-
Once you navigate into the
/etc/postgresql/10/main
directory, open the filepg_hba.conf
using the command below. This file controls: which hosts are allowed to connect, how clients are authenticated, which PostgreSQL user names they can use, which databases they can access:sudo nano pg_hba.conf
-
Replace the following line below:
# Database administrative login by Unix domain socket local all postgres peer
with the line below
# Database administrative login by Unix domain socket local all postgres md5
-
Also, replace the following line below:
# "local" is for Unix domain socket connections only local all all peer
with the line below:
# "local" is for Unix domain socket connections only local all all md5
-
Next, add the following at the end of the file to allow
host
connections fromall
databases,all
users,all
addresses using themd5
method (to allow inbound/remote connections to the PostgreSQL database in production), and then save the file:# remote connections host all all all md5
-
Still within the
/etc/postgresql/10/main
directory, open and edit the filepostgresql.conf
using the command below:sudo nano postgresql.conf
-
Replace the line
# listen_address='127.0.0.1'
or the linelisten_address='127.0.0.1'
or the line# listen_address='localhost'
or the linelisten_address='localhost'
with the line below, in order to allow PostgreSQL database to listen to connections from all addresses:listen_addresses = '*'
-
Save the file, and navigate to the root directory of your server:
cd ~
-
Restart or reload the PostgreSQL server using the command below:
sudo systemctl restart postgresql # To restart
sudo systemctl reload postgresql # To reload
- Navigate to your server instance where PostgreSQL database installed and Open your Security Group settings for that server instance.
- Add a new inbound rule using the configuration below, and save in order to open PostgreSQL port 5432:
Type: PostgreSQL
Protocol: TCP
Port Range: 5432
Source: Custom
CIDR,IP or Security Group: 0.0.0.0/0,::/0
If you couldn't edit your firewall/security connection group using the method above, you could also try this method. However, this method works only on Linux servers, and I don't often recommend it as it may exhibit unexpected behaviours. For example, if you are using ufw
, you would run the following command:
sudo ufw allow postgres/tcp
Your PostgreSQL server is now configured to accept remote connections.
You can connect to the PostgreSQL database using pgAdmin 4. Follow these steps:
- Launch pgAdmin 4.
- Go to the "Dashboard" tab. In the "Quick Link" section, click "Add New Server" to add a new connection.
- Select the "Connection" tab in the "Create-Server" window. Then, configure the connection as follows:
- Enter your server's IP address in the "Hostname/ Address" field.
- Specify the "Port" as
5432
. - Enter the name of the database in the "Database Maintenance" field.
- Enter your "User name" and "Password" that you configured for the remote database.
- Click "Save" to apply the configuration.
- Check that the connection between pgAdmin 4 and the PostgreSQL database server is active. Navigate to the "Dashboard" tab and find the state of the server in the "Server activity" section: