Installing and Configuring PgAdmin 4

Introduction

pgAdmin is the most popular and feature-rich platform for administration and development of PostgreSQL databases. Check the pgAdmin official page for more information.

Connect to your Remote PostgreSQL Database

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.

Edit PostgreSQL Configuration

• 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

Edit Firewall/Security Connection Groups

• 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.

Connect to PgAdmin 4

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:

Resources

1. Configure PgAdmin 4