virtualMachinemastodon

Configure pgAdmin 4

NOTE: This section assumes that you have downloaded and installed pgAdmin 4.

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:

NOTE: Ensure that your virtual machine has a local network IP address. To configure your virtual machine to connect to your local network, refer to these instructions. Once connected, you can obtain the IP address of your virtual machine at any time by running the command ifconfig -a in the virtual machine server console.


NOTE: In the steps below, you will be modifying a couple of PostgreSQL configuration files:

  • PostgreSQL configuration settings file: /opt/bitnami/postgresql/conf/postgresql.conf
  • PostgreSQL client authentication configuration file: /opt/bitnami/postgresql/conf/pg_hba.conf
  • Log in to your virtual machine console.

  • Open port 5432 in the server firewall (instructions).

    IMPORTANT: By default, the database port for the nodes in this solution cannot be accessed over a public IP address. As a result, you will only be able to connect to your database nodes from machines that are running in the same network. For security reasons, we do not recommend making the database port accessible over a public IP address. If you must make it accessible over a public IP address, we recommend restricting access to a trusted list of source IP addresses using firewall rules. For development purposes, you can also use a VPN or SSH tunnel. Refer to the FAQ for information on accessing restricted ports using an SSH tunnel or opening ports in the server firewall.

  • At the server console, edit the PostgreSQL client authentication configuration file pg_hba.conf and add the following at the end, then save the file:

      host all all all md5
    
  • Edit the PostgreSQL configuration file postgresql.conf and replace this line

      listen_address='127.0.0.1'
    

    with:

      listen_addresses = '*'
    
  • Save the file.

  • Restart the PostgreSQL server:

      sudo /opt/bitnami/ctlscript.sh restart postgresql
    

Your PostgreSQL server is now configured to accept remote connections, and you can connect to it 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.

    pgAdmin 4 configuration

  • 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 username as postgres and password (use the same password you used when previously configuring the server to accept remote connections) for the database.

  • Click “Save” to apply the configuration.

    pgAdmin 4 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:

    pgAdmin 4 configuration

Last modification February 9, 2023