How to Set Up a PostgreSQL Database on a VPS and Access It Remotely

Setting up a PostgreSQL database on a Virtual Private Server (VPS) is an essential skill for developers and system administrators who want to manage databases remotely. This guide will walk you through the process of setting up PostgreSQL on a Debian 12 server and configuring it for remote access.

1: Create a VPS

To get started, you’ll need a VPS. You can use any cloud provider, but for this guide, we'll assume you're using Vultur. The first step is to create a new VPS instance and select Debian 12 as your server operating system.

2: Install PostgreSQL

Once your VPS is up and running, connect to it via SSH. Then, update your package list and install PostgreSQL with the following commands:

sudo apt update
sudo apt install postgresql

This command installs PostgreSQL and its associated packages.

3: Configure PostgreSQL for Remote Access

By default, PostgreSQL is configured to only allow connections from the local system. To enable remote connections, follow these steps:

1. Edit the postgresql.conf file:

Open the configuration file located at /etc/postgresql/16/main/postgresql.conf (the version number 16 might vary depending on your PostgreSQL version).

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

Locate the line:

   #listen_addresses = 'localhost'

Uncomment it and change localhost to *:

   listen_addresses = '*'

This change allows PostgreSQL to listen for connections on all available IP addresses.

1. Edit the pg_hba.conf file:

Next, you'll need to modify the pg_hba.conf file to allow remote access:

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

Add the following line to the file:

   host    all             all             0.0.0.0/0               md5

This line allows all users (all) from any IP address (0.0.0.0/0) to connect to any database (all) using password authentication (md5).

4: Restart PostgreSQL

After making these changes, restart the PostgreSQL service to apply the new configuration:

sudo systemctl restart postgresql

5: Configure the Firewall

To allow remote connections to your PostgreSQL server, you need to ensure that your firewall permits traffic on port 5432 (the default PostgreSQL port). Use the following command to allow traffic on this port:

sudo ufw allow 5432/tcp

This command opens port 5432 for TCP connections, enabling remote access to your PostgreSQL database.

6: Connect to Your PostgreSQL Database Remotely

With everything configured, you can now connect to your PostgreSQL database remotely using a PostgreSQL client like psql, DBeaver, or pgAdmin. Use the following connection details:

• Host: The IP address of your VPS.
• Port: 5432 (or the port you configured).
• Username: Your PostgreSQL username.
• Password: Your PostgreSQL password.
• Database: The name of the database you want to connect to.

Conclusion

Setting up PostgreSQL on a VPS and configuring it for remote access is a straightforward process that gives you the flexibility to manage your databases from anywhere. By following the steps outlined in this guide, you'll have a fully functional PostgreSQL server accessible from any machine.