How to Install and Connect to PostgreSQL on an Ubuntu VM

This documentation provides a step-by-step guide to installing PostgreSQL on an Ubuntu virtual machine (VM) and configuring it for remote access using a graphical tool like pgAdmin 4.

1. Install PostgreSQL on the Ubuntu VM 🖥️

  1. Update the package list: Ensure your system is up to date before installation.Bash

    sudo apt-get update

    image.png

  2. Install the database engine: This command installs PostgreSQL and its contrib package.Bash

    sudo apt-get install postgresql postgresql-contrib

image.png

wait to you see this

image.png

image.png

  1. Set the postgres user password: Switch to the postgres user and set a strong password. This is required for remote connections.Bash

    sudo -i -u postgres

    image.png

    psql -c "ALTER USER postgres WITH PASSWORD 'your_strong_password';"

    image.png

    Then, type exit to return to your user account.

2. Configure Remote Access 🛡️

By default, PostgreSQL only accepts local connections. You must edit the configuration files to allow remote access.

  1. Configure listen_addresses: Open the main configuration file and change the listen_addresses setting to allow connections from any IP.Bash

    sudo nano /etc/postgresql/<version>/main/postgresql.conf

    Find #listen_addresses = 'localhost' and change it to listen_addresses = '*'.

    image.png

  2. Configure client authentication: Open the pg_hba.conf file to configure password-based authentication for remote connections.Bash

    sudo nano /etc/postgresql/<version>/main/pg_hba.conf

    Find the line

     local all postgres peer

    image.png

    and replace it with

     local all postgres md5.

    image.png

    Then, at the end of the file, add a new line to allow connections from any IP using a password:

    host all all 0.0.0.0/0 md5.

    image.png

  3. Restart the service: Restart the PostgreSQL service to apply the changes.Bash

    sudo systemctl restart postgresql

3. Open the Firewall 🌐

You must open the default PostgreSQL port, 5432, on your VM to allow incoming connections.