How to Install PostgreSQL on Ubuntu 20.04

PostgreSQL is an open-source, advanced Relational Database Management System (RDBMS) designed for managing various data tasks. It uses Structured Query Language (SQL) to handle data in both small and large applications, including analytical systems, GIS, healthcare apps, and dynamic web applications.

This article walks you through installing PostgreSQL on an Ubuntu 20.04 server, enabling the database server, and securing it for production use.

Prerequisites

Before you begin:

• Have an Ubuntu 20.04 server.

• Access the server using SSH as a non-root user with sudo privileges.

• Update the server.

Install PostgreSQL

PostgreSQL is available in the default APT repositories on Ubuntu. Follow the steps below to install the PostgreSQL database server packages and enable the application to start at boot time.

1. Update the server package index.

   console Copy

   $ sudo apt update
   

2. Install the postgresql-common dependency package on your server.

   console Copy

   $ sudo apt install -y postgresql-common -y
   

3. Run the following command to execute the PostgreSQL APT repository script.

   console Copy

   $ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
   

   Press Enter when prompted to add the new repository to your server sources.

   This script will enable the PostgreSQL APT repository on apt.postgresql.org on
   your system. The distribution codename used will be noble-pgdg.
   
   Press Enter to continue, or Ctrl-C to abort.

4. Install the postgresql database server package.

   console Copy

   $ sudo apt install -y postgresql
   

5. Start the PostgreSQL database server.

   console Copy

   $ sudo systemctl start postgresql
   

6. View the PostgreSQL system service status and verify that it's active.

   console Copy

   $ sudo systemctl status postgresql
   

   Output:

   ● postgresql.service - PostgreSQL RDBMS
       Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
       Active: active (exited) since Mon 2024-05-27 16:09:21 UTC; 35s ago
       Process: 5601 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 5601 (code=exited, status=0/SUCCESS)
           CPU: 3ms

Secure the PostgreSQL Database Server

PostgreSQL operates using the default postgres privileged database user account. Follow the steps below to enable password authentication and secure the database server, ensuring that only authorized users can access the databases.

1. Check your installed PostgreSQL version.

   console Copy

   $ psql --version
   

   Output:

   psql (PostgreSQL) 17.4 (Ubuntu 17.4-1.pgdg20.04+2)

2. Log in to the PostgreSQL database server using the postgres user account.

   console Copy

   $ sudo -u postgres psql
   

3. Modify the default postgres user with a new strong password.

   sql Copy

   postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'strong_password';
   

4. Create a new user db_manager with a new strong password.

   sql Copy

   postgres=# CREATE USER db_manager ENCRYPTED PASSWORD 'strong_password';
   

5. Exit the PostgreSQL console.

   sql Copy

   postgres=# quit;
   

6. Run the following command to change the default peer value in the scram-sha-256 field in the main PostgreSQL configuration file pg_hba.conf to enable password authentication on the server.

   console Copy

   $ sudo sed -i '/^local/s/peer/scram-sha-256/' /etc/postgresql/17/main/pg_hba.conf
   

   Replace 17 with your installed PostgreSQL version number if it's different.

7. Restart the PostgreSQL server to apply the new configuration changes.

   console Copy

   $ sudo systemctl restart postgresql
   

Access the PostgreSQL Database Server

The psql utility, pre-installed with the server package, allows you to access the PostgreSQL database console. You can also use compatible graphical tools for direct connection to the console. Follow the steps below to access your PostgreSQL database and create a new sample database for use with your non-privileged user.

1. Create a new sample PostgreSQL database hospital and grant the db_manager user ownership privileges to the database.

   console Copy

   $ sudo -u postgres createdb hospital -O db_manager
   

   When prompted, enter the Postgres user password you created earlier.

2. Log in to the PostgreSQL database as the user db_manager to test access to the hospital database.

   console Copy

   $ sudo -u postgres psql -U db_manager -d hospital
   

   Enter the database user password when prompted and press Enter to access the database.

3. Create a new sample doctors table.

   sql Copy

   hospital=> CREATE TABLE doctors (
              doctor_id SERIAL PRIMARY KEY,
              first_name VARCHAR(50),
              last_name VARCHAR(50),
              appointment_date DATE
          );
   

   The above SQL statement creates a new table in the hospital database with the following columns:

   • doctor_id is a PRIMARY KEY that uniquely identifies each doctor in the doctors table.
   • first_name and last_name store names in the doctors table.
   • appointment_date stores the doctor's appointment date with a patient in the hospital.
   • SERIAL generates a new doctor_id for each new record.

4. Insert sample data into the doctors table.

   sql Copy

   hospital=> INSERT INTO doctors
          ( first_name, last_name, appointment_date)
          VALUES
          ( 'Ben', 'Joe', '2024-11-15'),
          ( 'Carson', 'Smith', '2024-02-28'),
          ( 'Donald', 'James', '2024-04-10');
   

5. Query the doctors table to view all available records.

   sql Copy

   hospital=> SELECT * FROM doctors;
   

   Output:

   sql Copy

   doctor_id | first_name | last_name | appointment_date
   -----------+------------+-----------+------------------
          1 | Ben        | Joe       | 2024-11-15
          2 | Carson     | Smith     | 2024-02-28
          3 | Donald     | James     | 2024-04-10
   (3 rows)
   

6. Exit the PostgreSQL console.

   sql Copy

   hospital=> quit;
   

Conclusion

PostgreSQL is now installed on your Ubuntu 20.04 server. You've accessed the database using the psql utility to create sample databases and tables. You can integrate PostgreSQL with your applications to securely manage database records. For more details, visit the official PostgreSQL documentation.