How to Install PostgreSQL on Ubuntu [Setup & Configurations Covered]

This guide will teach you how to install PostgreSQL on Ubuntu and change its key configurations.

PostgreSQL is one of the best open source object-relational database systems. Whether you are a developer, sysadmin, or DevOps engineer, it is essential to know the basic setup and configurations involved in a PostgreSQL setup.

As per the latest StackOverflow developer survey, 40.42% of developers use PostgreSQL as a database.

Install PostgreSQL on Ubuntu

It is always better to download the latest version available in the official PostgreSQL Ubuntu repository.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

Install the latest version of PostgreSQL

sudo apt-get -y install postgresql

If you want to install a specific version of PostgreSQL, you can search for the available versions from here and use the version with the install command as shown below.

sudo apt-get -y install postgresql-12

Lets validate PostgreSQL by checking the service status.

sudo systemctl status postgresql

Connect to PostgreSQL & Change Password

You can connect to the PostgreSQL server using the psql client

When you install PostgreSQL, the psql client also gets installed.

Execute the following command to connect to the server.

sudo -u postgres psql

Let’s check the version of PostgreSQL using the following command.

SELECT version();

Now, we have to change the postgres user’s password so that we can allow remote PostgreSQL connection using password authentication.

Execute the following query to set a password. Replace myPassword with a password of your choice.

ALTER USER postgres PASSWORD 'myPassword';

PostgreSQL Remote Connection Configuration

By default, the PostgreSQL server is accessible only on the loopback interface (127.0.0.1) on port 5432 and through the Unix socket. Therefore, you can only connect to the PostgreSQL server from within the Ubuntu server where PostgreSQL is installed .

You can verify it by listing all the TCP connections using the ss Linux networking command.

ss -nlt

To enable remote connection to PostgreSQL, we need to allow it in the postgresql.conf file.
For Ubuntu systems, postgresql.conf is located in /etc/postgresql/14/main/ location. 14 is the version number.

If you are using a different PostgreSQL version, change it accordingly.

Or you can use the following find command to locate the file.

sudo find / -name "postgresql.conf"

Open the file postgresql.conf file.

sudo vi /etc/postgresql/14/main/postgresql.conf

Under CONNECTIONS AND AUTHENTICATION section you will find the following commented parameter.

#listen_addresses = 'localhost' 

Replace it with the following and save the file. With this configuration, we are enabling PostgreSQL server connections to accept connections from all IP addresses.

listen_addresses = '*' 

To apply the change, restart the PostgreSQL service.

sudo systemctl restart postgresql

Now, if you list all the TCP connections, you can see the PostgreSQL service listening on all interfaces, as shown below.

ss -nlt

We have allowed only connections on all interfaces. However, If you try to connect to PostgreSQL from a remote machine, you will get the following error because you need to enable client connections to all the Databases and users.

psql: error: connection to server at "", port 5432 failed: FATAL:  no pg_hba.conf entry for host "", user "postgres", database "postgres", SSL encryption
connection to server at "", port 5432 failed: FATAL:  no pg_hba.conf entry for host "", user "postgres", database "postgres", no encryption

To allow client connections to all databases, you need to edit the pg_hba.conf file.

Open pg_hba.conf file.

sudo vi /etc/postgresql/14/main/pg_hba.conf 

Add the following line to the end of the file.

host    all          all            0.0.0.0/0  md5

Test PostgreSQL Remote Connection

Now, let’s try connecting PostgreSQL from a remote machine.

First, install the PostgreSQL client on the system you are trying to connect to the PostgreSQL server.

For Ubuntu,

sudo apt-get install postgresql-client

For MAC

brew install libpq
brew link --force libpq

Once the client is installed, execute the following psql command to connect to the PostgreSQL remotely. Replace 192.168.5.5 with your PostgreSQL server IP. When it prompts for a password, enter the password we generated during the initial configuration.

psql -h 192.168.5.5 -U postgres

Important PostgreSQL Server Configurations

The following table contains important PostgreSQL configurations.

PostgreSQL Server Installation FAQs

Conclusion

This guide looked at the steps to install PostgreSQL on a Ubuntu server.

For the Redhat server, Checkout the PostgreSQL installation on Redhat

For the Amazon Linux server, Checkout the PostgreSQL installation on Amazon Linux

Also, if you are using Kubernetes, check out my guide on setting up PostgreSQL statefulset on Kubernetes

As a DevOps engineer, it is essential to know the basic configurations involved in a Database.

If you are starting your DevOps engineer journey, look at my comprehensive guide to becoming a DevOps engineer.