Skip to content

PostgreSQL

PostgreSQL is a powerful, open-source object-relational database system that has earned a strong reputation for reliability, feature robustness, and performance. In many cases, databases are external entities. Many cloud providers now provide PostgreSQL-as-a-service or project may opt for standing up a dedicated instance or instances in a highly available configuration. It may be therefore preferable to host PostgresSQL in a virtual machine locally to replicate such environments.

For best results, deploy a local Virtual Machine using Alma Linux

Following this guide for standing up a PostgreSQL database using AlmaLinux-8.5-x86_64-minimal.iso:

Installation

Always ensure the latest updates are applied:

dnf update -y

Install PostgreSQL 12 from the module stream on RHEL8:

sudo dnf install @postgresql:12 -y

Or on RHEL9, the default version is 13:

sudo dnf install postgresql-server

Database configuration

First step is to initialise PostgresSQL:

sudo /usr/bin/postgresql-setup --initdb

Enable and start service:

sudo systemctl enable postgresql.service --now

Open firewall

If firewalld is being used, add the service:

sudo firewall-cmd --add-service=postgresql --permanent

Reload the firewall rules:

sudo firewall-cmd --reload

Access PostgreSQL

Change to the postgres user:

sudo su - postgres

Start the PostgreSQL interactive terminal:

psql

Create a user and database

Create a new user, in this case efy is an abbreviation of exampleforyou, the username is efy_user:

CREATE USER efy_user WITH PASSWORD 'changeme';

The following three command demonstrate how to apply options to the user account:

ALTER ROLE efy_user SET client_encoding TO 'utf8';

ALTER ROLE efy_user SET default_transaction_isolation TO 'read committed';

ALTER ROLE efy_user SET timezone TO 'UTC';

Create a new database:

CREATE DATABASE exampleforyou_db;

Grant the user account privileges to the database:

GRANT ALL PRIVILEGES ON DATABASE exampleforyou_db TO efy_user;

List and quit:

\l
\q
exit

Enable external access

Ensure it is configured to listen on the IP Address of the host.

Edit postgresql.conf:

/var/lib/pgsql/data/postgresql.conf
listen_addresses = '192.168.0.70'

And update the configuration to allow any host on the same subnet to access the database.

Edit pg_hba.conf:

Original:

/var/lib/pgsql/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

Change methods to md5 for localhost and if needed the subnet of an IP range, in this case for running PostgreSQL in a Virtual Machine on 192.168.0.111, adding /24 for that subnet:

/var/lib/pgsql/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.1/24          md5
host    all             all             192.168.122.1/24        md5
host    all             all             ::1/128                 md5
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

Restart PostgreSQL service

systemctl restart postgresql

pgAdmin

pgAdmin is a browser based management console for PostgreSQL.

Ensure Extra Packages for Enterprise Linux (EPEL) is available:

dnf install epel-release -y

Add the pgadmin repository, note the package name is prone to change, check https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/ for latest:

dnf install -y https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm

Install pgadmin4:

dnf install pgadmin4 -y

It uses Apache, make sure mod_ssl is installed so SSL redirect works:

dnf install mod_ssl -y

Run the initial setup:

/usr/pgadmin4/bin/setup-web.sh

Enter an email address and password to complete the installation.

The service runs on HTTPS, therefore port 443, open this service:

sudo firewall-cmd --permanent --add-service=https

sudo firewall-cmd --reload

If SELinux is set to Enforcing, add the correct context so the web server can write to the database:

dnf install policycoreutils-python-utils -y

semanage fcontext -m -t httpd_sys_rw_content_t "/var/lib/pgadmin(/.*)?"

restorecon -R -v /var/lib/pgadmin

Access pgAdmin using a browser, for example: https://localhost/pgadmin4

Then simply add a new server, using localhost and the credentials added previously.

Backup & Restore

SQL Dump

On the PostgreSQL server, change to the postgres user:

sudo su - postgres

The following command will prompt for the password:

pg_dump -U dbuser -W changeme -h 192.168.122.50 -p 5432 my_db > my_db.sql

However, create a hidden file .pgpass in the home directory using the following format:

hostname:port:database:username:password

Then the backup can be done with simply:

pg_dump my_db > my_db.sql

Restoring the Dump

Is restoring to a new instance od PostgreSQL, you would first recreate the user and empty database, as documented earlier.

sudo su - postgres

psql -d my_db < my_db.sql