Are you looking to install a robust and scalable database management system on your Ubuntu 22.04 server? Look no further than PostgreSQL! In this tutorial, we will guide you through the steps to install PostgreSQL Server on Ubuntu 22.04 and configure it to allow remote connections.

What is PostgreSQL?

PostgreSQL, also known as Postgres, is a free and open-source relational database management system (RDBMS) that is widely used in web and enterprise applications. It is known for its reliability, data integrity, and ability to handle large volumes of data.

Prerequisites

• Ubuntu 22.04 server
• Basic understanding of Linux commands and file systems
• A non-root user with sudo privileges

Step 1: Update the Package Index

Before installing PostgreSQL, update the package index using the following command:

sudo apt update

This will ensure that your system has the latest package list.

Step 2: Install PostgreSQL Server

Next, install the PostgreSQL Server package using the following command:

sudo apt install postgresql

This will install the PostgreSQL Server and its dependencies.

Step 3: Install PostgreSQL Client (Optional)

If you also want to install the PostgreSQL Client, use the following command:

sudo apt install postgresql-client

This will allow you to connect to the PostgreSQL Server from the command line.

Step 4: Configure the PostgreSQL Server

By default, the PostgreSQL Server will only listen on localhost.

To allow remote connections, edit the /etc/postgresql/14/main/postgresql.conf file (replace 14 with your version number):

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

Uncomment the line that starts with # listen_addresses and add the IP address or hostname you want to allow connections from:

listen_addresses = 'localhost, 192.168.1.100'

Replace 192.168.1.100 with the IP address or hostname you want to allow.

Step 5: Configure the PostgreSQL User

By default, the PostgreSQL Server creates a system user named postgres with an empty password. To set a password for the postgres user, use the following command:

sudo passwd postgres

Enter a new password for the postgres user.

Step 6: Enable and Start the PostgreSQL Service

Enable the PostgreSQL Service to start automatically on boot:

sudo systemctl enable postgresql

Start the PostgreSQL Service:

sudo systemctl start postgresql

Verify that the PostgreSQL Service is running:

sudo systemctl status postgresql

Step 7: Edit pg_hba.conf to Allow Remote Connections

Edit the /etc/postgresql/14/main/pg_hba.conf file (replace 14 with your version number):

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

Add the following lines to allow remote connections:

host    all             all             192.168.1.0/24          scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256

Replace 192.168.1.0/24 with the IP address range you want to allow. The 0.0.0.0/0 line allows connections from any IP address.

Step 8: Restart the PostgreSQL Service

Restart the PostgreSQL Service to apply the changes:

sudo systemctl restart postgresql

Step 9: Test Remote Connection

Test the remote connection using the psql command from a different machine:

psql -h <server_ip> -U <username> -d <database>

Replace <server_ip> with the IP address of the PostgreSQL Server, <username> with your PostgreSQL username, and <database> with the name of the database you want to connect to.

Conclusion

In this tutorial, we have successfully installed PostgreSQL Server on Ubuntu 22.04 and configured it to allow remote connections. Remember to replace the IP addresses and usernames with your own values. With these steps, you can now access your PostgreSQL database from any location, making it easier to manage and maintain your data.