How to Set Up a Remote PostgreSQL Database Server on Ubuntu 18.04 May 9, 2025

In this article, we will explain how to set up a remote Postgresql database server on Ubuntu 18.04.

Setting up a remote Postgresql server lets you host your database on a separate machine and connect to it from your application or another server. This improves scalability, security, and performance in distributed environments.

Prerequisites:

1. A server running Ubuntu 18.04 with Postgresql installed
2. Root or sudo access to the server
3. A client machine or application with Postgresql client installed
4. A static IP address for both the server and client (or resolvable hostname)

Follow the steps below to configure a remote Postgresql database server on Ubuntu 18.04.

1. Install PostgreSQL (if not already installed)

   sudo apt update
   sudo apt install postgresql postgresql-contrib

2. Configure Postgresql to Accept Remote Connections
   1. Edit the Postgresql.conf file:

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

      Locate the line:

      #listen_addresses = 'localhost'

      Change it to:

      listen_addresses = '*'

      This allows Postgresql to listen for connections on all interfaces.

   2. Configure Client Authentication in pg_hba.conf:

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

      Add the following line at the end (replace your-client-ip and your-database-name as needed):

      host    all             all             your-client-ip/32         md5
      If you want to allow an entire subnet:
      host    all             all             192.168.1.0/24            md5

   3. Restart PostgreSQL

      sudo systemctl restart postgresql

   4. Create a Postgresql User and Database
      Switch to the postgres user:

      sudo -i -u postgres

      Create a new user and database:

      createuser your_username --pwprompt

      createdb your_database_name –owner=your_username
      Grant all privileges:

      psql
      GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;
      \q

   5. Allow Postgresql Port (5432) Through the Firewall

      sudo ufw allow 5432/tcp
      sudo ufw reload

   6. Connect Remotely
      From the client machine:

      psql -h your-server-ip -U your_username -d your_database_name

In this manner, you have effectively configured a remote Postgresql database server on Ubuntu 18.04. Always ensure that proper firewall rules and strong user credentials are in place to keep your database secure.

Planning your PostgreSQL setup? Learn How to decide the size of PostgreSQL databases and tables