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 that 10
is the version of my PostgreSQL installation on my server. Your version might be 9.5
, 11
or 12
or any other version.
cd ~
cd /etc/postgresql/10/main
Once you navigate into the /etc/postgresql/10/main
directory, open the file pg_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 from all
databases, all
users, all
addresses using the md5
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 file postgresql.conf
using the command below:
sudo nano postgresql.conf
Replace the line # listen_address='127.0.0.1'
or the line listen_address='127.0.0.1'
or the line # listen_address='localhost'
or the line listen_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
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:
5432
.