Fedora 40: Install PostgreSQL 16

Introduction

This guide covers the installation and configuration of PostgreSQL 16 on Fedora 40 using the 5W1H approach. We will explore the What, Who, Where, When, Why, How, Consequences, and Conclusion of setting up PostgreSQL 16.

Overview Install PostgreSQL 16 Remote Connection PostgreSQL over SSL/TLS Backup and Restore Streaming Replication

Navbar

• Overview
• Install PostgreSQL 16
• Remote Connection
• PostgreSQL over SSL/TLS
• Backup and Restore
• Streaming Replication

Overview

What

PostgreSQL is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance.

Who

This guide is intended for system administrators and developers who need to set up a reliable database server to manage and store data on Fedora 40.

Where

The setup process can be carried out on any server running Fedora 40, whether it's a physical machine, virtual machine, or cloud instance.

When

Setting up PostgreSQL should be done during a planned maintenance window to avoid potential disruptions to existing services.

Why

Implementing PostgreSQL 16 on your server offers several advantages:

Pros	Cons
Advanced features and extensions	Complexity in initial setup
High performance and scalability	Learning curve for new users
Strong community support	Requires regular maintenance and updates

How

Follow these steps to set up PostgreSQL 16 on Fedora 40:

Step 1	Update your system: sudo dnf update -y
Step 2	Install the PostgreSQL repository: sudo dnf install -y https://download.postgresql.org/pub/repos/yum/16/fedora/fedora-40-x86_64/pgdg-fedora-repo-latest.noarch.rpm
Step 3	Install PostgreSQL 16: sudo dnf install -y postgresql16-server postgresql16
Step 4	Initialize the database: sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
Step 5	Start and enable the PostgreSQL service: sudo systemctl start postgresql-16 sudo systemctl enable postgresql-16
Step 6	Configure the firewall to allow PostgreSQL traffic: sudo firewall-cmd --permanent --add-service=postgresql sudo firewall-cmd --reload

Consequences

Setting up PostgreSQL 16 on Fedora 40 can have the following consequences:

Positive	Enhanced data management and reliability. Scalable and high-performance database solution. Strong community and development support.
Negative	Initial setup and configuration complexity. Requires ongoing management and updates. Steeper learning curve for new users.

Conclusion

Setting up PostgreSQL 16 on Fedora 40 is essential for achieving robust, high-performance data management. While the initial setup can be complex and demands regular maintenance, the benefits of a scalable, reliable, and feature-rich database system make PostgreSQL 16 a valuable addition to any IT infrastructure. By following this guide, system administrators can ensure their database servers are ready to deliver high-quality data management services effectively.

Install : PostgreSQL 16

Install PostgreSQL to configure database server.

Step [1]Install and start PostgreSQL.

                     
[root@bizantum ~]# dnf -y install postgresql-server
[root@bizantum ~]# postgresql-setup --initdb
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
 
[root@bizantum ~]# systemctl enable --now postgresql
                    
                

Step [2] By default setting, it's possible to connect to PostgreSQL Server only from Localhost with [peer] authentication. Refer to the official site below about details of authentication methods. ⇒ https://www.postgresql.jp/document/10/html/auth-pg-hba-conf.html

                     
# listen only localhost by default
[root@bizantum ~]# grep listen_addresses /var/lib/pgsql/data/postgresql.conf
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# authentication methods by default
[root@bizantum ~]# grep -v -E "^#|^$" /var/lib/pgsql/data/pg_hba.conf
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
                    
                

Step [3]On [peer] authentication, it needs OS user and PostgreSQL user whose name are the same to connect to PostgreSQL Server.

                    
# add an OS user
[root@bizantum ~]# useradd fedora
# add an PostgreSQL user and his Database with PostgreSQL admin user
[root@bizantum ~]# su - postgres
[postgres@www ~]$ createuser fedora
[postgres@www ~]$ createdb testdb -O fedora
# show users and databases
[postgres@www ~]$ psql -c "select usename from pg_user;"
 usename
----------
 postgres
 fedora
(2 rows)

[postgres@www ~]$ psql -l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 testdb    | fedora   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(4 rows)
                    
                

Step [4]Try to connect to PostgreSQL Database with a user added above.

                    
# connect to testdb
[fedora@www ~]$ psql testdb
psql (16.1)
Type "help" for help.

# show user roles
testdb=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 fedora    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

# show databases
testdb=> \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 testdb    | fedora   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(4 rows)

# create a test table
testdb=> create table test_table (no int, name text); 
CREATE TABLE

# show tables
testdb=> \dt 
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+--------
 public | test_table | table | fedora
(1 row)

# insert data to test table
testdb=> insert into test_table (no,name) values (01,'Fedora'); 
INSERT 0 1

# confirm
testdb=> select * from test_table; 
 no |  name
----+--------
  1 | Fedora
(1 row)

# remove test table
testdb=> drop table test_table; 
DROP TABLE

testdb=> \dt 
Did not find any relations.

# exit
testdb=> \q 

# remove testdb
[fedora@www ~]$ dropdb testdb
[fedora@www ~]$ psql -l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)
                    
                

Remote Connection

It's possible to connect to PostgreSQL Server only from Localhost by default like here, however if you'd like to connect to PostgreSQL from Remote hosts, change settings like follows.

Step [1] There are many authentication methods on PostgreSQL, though. On this example, Configure scram-sha-256 password method.

                     
[root@bizantum ~]# vi /var/lib/pgsql/data/postgresql.conf
# line 60 : uncomment and change
# if listen only IPv4, specify '0.0.0.0'
listen_addresses = '*'
[root@bizantum ~]# vi /var/lib/pgsql/data/pg_hba.conf
# add to last line
.....
.....
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
# specify network range you allow to connect on [ADDRESS] section
# if allow all, specify [0.0.0.0/0]
host    all             all             10.0.0.0/24             scram-sha-256

[root@bizantum ~]# systemctl restart postgresql
                    
                

Step [2]If Firewalld is running, allow PostgreSQL service.

                     
[root@bizantum ~]# firewall-cmd --add-service=postgresql
success
[root@bizantum ~]# firewall-cmd --runtime-to-permanent
success
                    
                

Step [3]To connect to a PostgreSQL Database with password, set password for each PostgreSQL user.

                    
# connect to own database
[fedora@www ~]$ psql -d testdb
psql (16.1)
Type "help" for help.

# set or change own password
testdb=> \password
Enter new password:
Enter it again:
testdb=> \q

# also possible to set or change password for any users with PostgreSQL admin user
postgres@dlp:~$ psql -c "alter user fedora with password 'password';"
ALTER ROLE
                    
                

Step [4]Verify settings to connect to PostgreSQL Database with password from remote hosts.

                    
[root@node01 ~]# psql -h www.bizantum.lab -d testdb -U fedora
Password for user fedora:   # password
psql (16.1)
Type "help" for help.

testdb=> # connected
                    
                

PostgreSQL over SSL/TLS

Enable SSL/TLS connection to PostgreSQL.

Step [1] Get SSL/TLS certificate or Create self signed certificate first. It uses self signed certificate on this example.

Step [2]Copy certificates and configure PostgreSQL.

                     
[root@bizantum ~]# cp /etc/pki/tls/certs/server.{crt,key} /var/lib/pgsql/data/
[root@bizantum ~]# chown postgres:postgres /var/lib/pgsql/data/server.{crt,key}
[root@bizantum ~]# chmod 600 /var/lib/pgsql/data/server.{crt,key}
[root@bizantum ~]# vi /var/lib/pgsql/data/postgresql.conf
# line 108 : uncomment and change
ssl = on
# line 110, 113 : uncomment and change to your certificate
#ssl_ca_file = ''
ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
ssl_key_file = 'server.key'
[root@bizantum ~]# vi /var/lib/pgsql/data/pg_hba.conf
# line 112 and later : settings for authentication methods
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
# add to last line
# [hostssl] ⇒ use TCP/IP connection only when enabling SSL/TLS
# [10.0.0.0/24] ⇒ allowed network to connect
# [scram-sha-256] ⇒ use scram-sha-256 password method
hostssl all             all             10.0.0.0/24             scram-sha-256

[root@bizantum ~]# systemctl restart postgresql
                    
                

Step [3]Verify settings to connect to PostgreSQL Database from hosts in network you allowed to connect.

                    
# no SSL/TLS on Unix socket connection
[fedora@www ~]$ psql testdb
psql (16.1)
Type "help" for help.

testdb=> \q

# on TCP/IP connection, SSL/TLS is enabled
# on SSL/TLS connection, messages [SSL connection ***] is shown
[fedora@www ~]$ psql -h www.bizantum.lab testdb
Password for user fedora:
psql (16.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

testdb=> \q


# SSL/TLS is enabled from other hosts, too
[root@node01 ~]# psql -h www.bizantum.lab -d testdb -U fedora
Password for user fedora:
psql (16.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

testdb=>
                    
                

Backup and Restore

For Backup and Restore PostgreSQL Data, it's possible to run with [pg_dump] and [pg_restore].

Step [1]Backup Databases.

                     
# available types for [--format=*]
#     p = plain (SQL)
#     c = custom (compression)
#     t = tar
#     d = directory

# [fedora] user takes backup of [testdb]
[fedora@www ~]$ pg_dump -U fedora --format=t -d testdb > pg_testdb.tar
[fedora@www ~]$ ll
total 8
-rw-r--r--. 1 fedora fedora 6656 May 15 10:06 pg_testdb.tar


# admin user [postgres] takes backup of all databases
[postgres@www ~]$ pg_dumpall -f ~/backups/pg_DB_all.sql
[postgres@www ~]$ ll ~/backups
total 4
-rw-r--r--. 1 postgres postgres 3248 May 15 10:07 pg_DB_all.sql
                    
                

Step [2]Restore Databases.

                     
# [fedora] user restores [testdb] database from backup file
[fedora@www ~]$ pg_restore -U fedora -d testdb pg_testdb.tar

# admin user [postgres] restores all database from backup file
# if the type of backup file is SQL text, use [psql] command for restoring
[postgres@www ~]$ psql -f ~/backups/pg_DB_all.sql
                    
                

Streaming Replication

Configure PostgreSQL Streaming Replication. This configuration is common Primary/Replica settings.

Step [1]Install and start PostgreSQL Server on all Nodes, refer to here [1].

Step [2]Configure Primary Host.

                     
[root@bizantum ~]# vi /var/lib/pgsql/data/postgresql.conf
# line 60 : uncomment and change
listen_addresses = '*'
# line 211 : uncomment
wal_level = replica
# line 216 : uncomment
synchronous_commit = on
# line 314 : uncomment (max number of concurrent connections from streaming clients)
max_wal_senders = 10
# line 328 : uncomment and change
synchronous_standby_names = '*'
[root@bizantum ~]# vi /var/lib/pgsql/data/pg_hba.conf
# last line : comment out existing lines and all new lines
# host replication [replication user] [allowed network] [authentication method]
host    replication     rep_user        10.0.0.31/32            scram-sha-256
host    replication     rep_user        10.0.0.51/32            scram-sha-256

# create a user for replication
[root@bizantum ~]# su - postgres
[postgres@www ~]$ createuser --replication -P rep_user
Enter password for new role:   # set any password
Enter it again:
[postgres@www ~]$ exit
[root@bizantum ~]# systemctl restart postgresql
                    
                

Step [3]Configure Replica Host.

                    
# stop PostgreSQL and remove existing data
[root@node01 ~]# systemctl stop postgresql
[root@node01 ~]# rm -rf /var/lib/pgsql/data/*
# get backup from Primary Host
[root@node01 ~]# su - postgres
[postgres@node01 ~]$ pg_basebackup -R -h www.bizantum.lab -U rep_user -D /var/lib/pgsql/data -P
Password:   # password of replication user
30924/30924 kB (100%), 1/1 tablespace
[postgres@node01 ~]$ exit
[root@node01 ~]# vi /var/lib/pgsql/data/postgresql.conf
# line 60 : uncomment and change
listen_addresses = '*'
# line 339 : uncomment
hot_standby = on
[root@node01 ~]# systemctl start postgresql
                    
                

Step [4]On all Nodes, If Firewalld is running, allow PostgreSQL service.

                    
[root@bizantum ~]# firewall-cmd --add-service=postgresql
success
[root@bizantum ~]# firewall-cmd --runtime-to-permanent
success
                    
                

Step [5]That's OK if result of the command below on Primary Host is like follows. Verify replication works normally to create databases or to insert data on Primary Host.

                    
[postgres@www ~]$ psql -c "select usename, application_name, client_addr, state, sync_priority, sync_state from pg_stat_replication;"
 usename  | application_name | client_addr |   state   | sync_priority | sync_state
----------+------------------+-------------+-----------+---------------+------------
 rep_user | walreceiver      | 10.0.0.51   | streaming |             1 | sync
(1 row)