Skip to main content
PostgreSQL is a powerful, open-source object-relational database system. It is a highly scalable, SQL-compliant database management system that is used to handle large workloads. PostgreSQL is a popular choice for many developers and organizations due to its robust features, extensibility, and reliability. In this tutorial, we will walk you through the process of setting up PostgreSQL on Ubuntu on a VPS.

​
Install PostgreSQL

sudo apt update
sudo apt install postgresql
sudo service postgresql start # start the service

​
Check PostgreSQL is running

sudo service postgresql status
Make sure the service is running

​
Use psql to connect to the database

sudo -u postgres psql
This will open a CLI where you can run SQL commands. After first launching psql, you may check the details of your connection by typing \conninfo into the interpreter. 
postgres=# \conninfo
Connection to database "postgres" by user "postgres".
  Host: localhost
  Port: 5432
  Username: postgres
  Database: postgres
  SSL off

​
List databases

postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
  ----------+----------+----------+------------+------------+-----------------------
  postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
            |          |          |            |            | postgres=CTc/postgres
  template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
            |          |          |            |            | postgres=CTc/postgres

  (3 rows)

​
Create a database

postgres=# CREATE DATABASE mydb;
CREATE DATABASE

​
List databases

postgres=# \l
                                List of databases
     Name   |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
  ----------+----------+----------+------------+------------+-----------------------
  mydb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
  postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
            |          |          |            |            | postgres=CTc/postgres
  template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
            |          |          |            |            | postgres=CTc/postgres

  (4 rows)

​
List tables

postgres=# \dt

          List of relations
  Schema | Name | Type  |  Owner
  -------+------+-------+----------
  public | foo  | table | postgres
  public | bar  | table | postgres

  (2 rows)

​
Setup PostgreSQL server

Open the file /etc/postgresql/<version>/main/postgresql.conf and add the following lines: 
listen_addresses = '*'
This will allow connections from any IP address. This is not recommended for production environments.

​
Connection Settings

Add the following lines in /etc/postgresql/<version>/main/pg_hba.conf: 
# Allow connections from any IP address
host    all             all             0.0.0.0/0               md5

​
Restart the PostgreSQL server:

sudo service postgresql restart
# or
sudo systemctl restart postgresql
This server will be accessible at localhost:5432 if your backend server is running on the same machine. If you are running the backend server on a different machine, you will need to configure the host and port settings in the .env file.

​
Summary

In this tutorial, we have learned how to set up PostgreSQL on Ubuntu on a VPS. We have also learned how to create a database, list tables and configure connection settings.

Next: Advanced Node.js Logging

Continue your journey by learning how to master Winston and Morgan for production logging.