How to Install PostgreSQL Database Server on Ubuntu 22.04
Introduction
PostgreSQL is an open-source advanced Relational Database Management System (RDBMS) designed to handle a wide range of data management tasks. It supports the Structured Query Language (SQL) for managing data in small and large enterprise applications such as analytical systems, Geographic Information Systems (GIS), Healthcare applications, and dynamic websites.
This guide explains how to deploy a PostgreSQL database server on Ubuntu 22.04 and secure it for access by trusted users on the server.
Prerequisites
Before you begin:
- Deploy a Ubuntu 22.04 on Vultr.
- Access the server using SSH.
- Create a non-root user with sudo privileges and switch to the user.
- Update the Server.
Install the PostgreSQL Database Server Package
Add the PostgreSQL repository to your server's APT sources.
console$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Import the PostgreSQL repository key to your server using the
wget
utility.console$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/postgresql.asc > /dev/null
Update the server packages to synchronize the new PostgreSQL repository.
console$ sudo apt update
Install PostgreSQL on your server.
console$ sudo apt install postgresql
Start the PostgreSQL database server.
console$ sudo systemctl restart postgresql
View the PostgreSQL service status and verify that it's active.
console$ sudo systemctl status postgresql
Output:
ā postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Sun 2024-04-21 16:08:10 UTC; 13s ago Process: 6756 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 6756 (code=exited, status=0/SUCCESS) CPU: 1ms
Use the PostgreSQL Database Command Line Interface (CLI)
The psql
client tool creates a connection to the PostgreSQL database server with support for operations such as SQL statement execution and remote database access. Follow the steps below to use the PostgreSQL client tool to connect and access your database server.
Log in to the PostgreSQL Database server using the default
postgres
user.console$ sudo -u postgres psql
Create a new sample database
hospital
.plpgsqlpsql > CREATE DATABASE hospital;
Switch to the new database.
plpgsqlpsql > \c hospital
Create a new sample
doctors
tableplpgsqlpsql > CREATE TABLE doctors( doctor_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), appointment_date DATE );
In the above SQL statement:
- The
doctor_id
column is aPRIMARY KEY
that uniquely identifies each doctor in thedoctors
table. first_name
andlast_name
columns store names in thedoctors
table.appointment_date
stores the doctor's appointment date with a patient in the hospital.SERIAL
generates a newdoctor_id
for each new record.
- The
Insert sample data into the
doctors
tableplpgsqlpsql > INSERT INTO doctors ( first_name, last_name, appointment_date) VALUES ( 'Ben', 'Joe', '2023-11-15'), ( 'Carson', 'Smith', '2023-02-28'), ( 'Donald', 'James', '2023-04-10');
Query the
doctors
table to view all available data.plpgsqlpsql > SELECT * FROM doctors;
Output:
doctor_id | first_name | last_name | appointment_date -----------+------------+-----------+------------------ 1 | Ben | Joe | 2023-11-15 2 | Carson | Smith | 2023-02-28 3 | Donald | James | 2023-04-10 (3 rows)
Exit the PostgreSQL console.
plpgsqlpsql > exit
Secure the PostgreSQL Database Server with Password Authentication
Follow the steps below to secure the PostgreSQL database server with password authentication to enable only authorized users to access databases.
Log in to the PostgreSQL database server.
console$ sudo -u postgres psql
Modify the default
postgres
user with a new strong password.plpgsqlpsql > ALTER USER postgres WITH PASSWORD 'strong_password';
Exit the PostgreSQL console
plpgsqlpsql > exit
Back up the default
pg_hba.conf
configuration file for recovery purposes. Replace16
with your actual database server version.console$ sudo cp /etc/postgresql/16/main/pg_hba.conf /etc/postgresql/16/main/pg_hba.conf.ORIG
Open the
pg_hba.conf
configuration file using a text editor such as Nanoconsole$ sudo nano /etc/postgresql/16/main/pg_hba.conf
Find the following section within the file.
local all postgres peer # TYPE DATABASE USER ADDRESS METHOD
Change the
peer
value topassword
to enable password authentication on the PostgreSQL database server.local all postgres password # TYPE DATABASE USER ADDRESS METHOD
Restart the PostgreSQL server to apply the new configuration changes.
console$ sudo systemctl restart postgresql
Log in to the PostgreSQL Database server using the
postgres
user to test the new authentication.console$ sudo -u postgres psql
Enter the
postgres
user password you set earlier and press Enter to access the database server.Create a new user role
db_manager
with a secure password.plpgsqlpsql > CREATE ROLE db_manager WITH LOGIN PASSWORD 'strong-password';
Grant the new user full privileges to the
hospital
database.plpgsqlpsql > GRANT ALL PRIVILEGES ON DATABASE hospital TO db_manager;
Exit the PostgreSQL database console.
plpgsqlpsql > exit
Log in to the PostgreSQL database as the new user
db_manager
user to test access to thehospital
database.plpgsqlpsql > psql -h localhost -U db_manager -d hospital
Enter the user password when prompted and press Enter to access the database.
Conclusion
You have installed the PostgreSQL server on Ubuntu 22.04 and used the psql
utility to access the database server. In addition, you created a new database table with sample data, a new role with database privileges, and enabled secure user password authentication. For more information and database configurations, visit the official PostgreSQL documentation.