On this my first article talking about PostgreSQL Database, I will describe how to install a PostgreSQL on CentOS Linux 9. I am using a CentOS aarch64 processor version, installed on VMWare Fusion.
We will create a disk to store data and others files for PostgreSQL, to isolate database files of operational systems files. Below, you can see that I already added a disk in my VM and now we should have to create a partition and after that, format the new partition.
First we need to execute fdisk on disk:
[root@dbserver ~]$ fdisk /dev/nvme0n2
Welcome to fdisk (util-linux 2.37.4).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Device does not contain a recognized partition table.
Created a new DOS disklabel with disk identifier 0xcec28f36.
Command (m for help): n
Partition type
p primary (0 primary, 0 extended, 4 free)
e extended (container for logical partitions)
Select (default p):
Using default response p.
Partition number (1-4, default 1):
First sector (2048-62914559, default 2048):
Last sector, +/-sectors or +/-size{K,M,G,T,P} (2048-62914559, default 62914559):
Created a new partition 1 of type 'Linux' and of size 30 GiB.
Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.
Now, we need to format the partition. I will format in xfs format:
[root@dbserver ~]$ mkfs.xfs /dev/nvme0n2p1
meta-data=/dev/nvme0n2p1 isize=512 agcount=4, agsize=1966016 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=1, sparse=1, rmapbt=0
= reflink=1 bigtime=1 inobtcount=1 nrext64=0
data = bsize=4096 blocks=7864064, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0, ftype=1
log =internal log bsize=4096 blocks=16384, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
Lets create the mount directory on linux:
[root@dbserver ~]$ mkdir /pgsql
[root@dbserver ~]$ mount /dev/nvme0n2p1 /pgsql/
[root@dbserver ~]$ df -h /pgsql
Filesystem Size Used Avail Use% Mounted on
/dev/nvme0n2p1 30G 247M 30G 1% /pgsql
[root@dbserver ~]$
Edit /etc/fstab file and add /pgssql/ directory, for the next time you reboot the server, everything start ok.
[root@dbserver /]$ vim /etc/fstab
[root@dbserver /]$ cat /etc/fstab
#
# /etc/fstab
# Created by anaconda on Tue Apr 22 22:21:50 2025
#
# Accessible filesystems, by reference, are maintained under '/dev/disk/'.
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info.
#
# After editing this file, run 'systemctl daemon-reload' to update systemd
# units generated from this file.
#
/dev/mapper/cs_192-root / xfs defaults 0 0
UUID=005ef7fa-de33-4ae0-a30b-c7b351af22f4 /boot xfs defaults 0 0
UUID=6775-6DFB /boot/efi vfat umask=0077,shortname=winnt 0 2
/dev/mapper/cs_192-swap none swap defaults 0 0
/dev/nvme0n2p1 /pgsql/ xfs defaults 0 0
Let’s add PostgreSQL yum repository on Linux. If you are not using the same operation system, you will need to change the link, choosing the right OS.
[root@dbserver ~]$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-aarch64/pgdg-redhat-repo-latest.noarch.rpm
CentOS Stream 9 - BaseOS 15 MB/s | 9.9 MB 00:00
CentOS Stream 9 - AppStream 21 MB/s | 21 MB 00:00
CentOS Stream 9 - Extras packages 25 kB/s | 20 kB 00:00
pgdg-redhat-repo-latest.noarch.rpm 9.7 kB/s | 12 kB 00:01
Dependencies resolved.
================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
================================================================================================================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-50PGDG @commandline 12 k
Transaction Summary
================================================================================================================================================================================================================================================================================
Install 1 Package
Total size: 12 k
Installed size: 16 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : pgdg-redhat-repo-42.0-50PGDG.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-50PGDG.noarch 1/1
Installed:
pgdg-redhat-repo-42.0-50PGDG.noarch
Complete!
[root@dbserver ~]$
Now, we can install PostgreSQL database:
[root@dbserver ~]$ dnf install -y postgresql-server postgresql
PostgreSQL common RPMs for RHEL / Rocky / AlmaLinux 9 - aarch64 2.9 kB/s | 659 B 00:00
PostgreSQL common RPMs for RHEL / Rocky / AlmaLinux 9 - aarch64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0xB9738825:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: B031 F89F C983 E982 6290 6B6E 177B 343B B973 8825
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-AARCH64-RHEL
PostgreSQL common RPMs for RHEL / Rocky / AlmaLinux 9 - aarch64 1.7 MB/s | 483 kB 00:00
PostgreSQL 17 for RHEL / Rocky / AlmaLinux 9 - aarch64 4.5 kB/s | 659 B 00:00
PostgreSQL 17 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0xB9738825:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: B031 F89F C983 E982 6290 6B6E 177B 343B B973 8825
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-AARCH64-RHEL
PostgreSQL 17 for RHEL / Rocky / AlmaLinux 9 - aarch64 1.2 MB/s | 391 kB 00:00
PostgreSQL 16 for RHEL / Rocky / AlmaLinux 9 - aarch64 4.0 kB/s | 659 B 00:00
PostgreSQL 16 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0xB9738825:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: B031 F89F C983 E982 6290 6B6E 177B 343B B973 8825
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-AARCH64-RHEL
PostgreSQL 16 for RHEL / Rocky / AlmaLinux 9 - aarch64 1.9 MB/s | 672 kB 00:00
PostgreSQL 15 for RHEL / Rocky / AlmaLinux 9 - aarch64 4.6 kB/s | 659 B 00:00
PostgreSQL 15 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0xB9738825:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: B031 F89F C983 E982 6290 6B6E 177B 343B B973 8825
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-AARCH64-RHEL
PostgreSQL 15 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.6 MB/s | 922 kB 00:00
PostgreSQL 14 for RHEL / Rocky / AlmaLinux 9 - aarch64 4.4 kB/s | 659 B 00:00
PostgreSQL 14 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0xB9738825:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: B031 F89F C983 E982 6290 6B6E 177B 343B B973 8825
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-AARCH64-RHEL
PostgreSQL 14 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.8 MB/s | 974 kB 00:00
PostgreSQL 13 for RHEL / Rocky / AlmaLinux 9 - aarch64 4.1 kB/s | 659 B 00:00
PostgreSQL 13 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.4 MB/s | 2.4 kB 00:00
Importing GPG key 0xB9738825:
Userid : "PostgreSQL RPM Repository <pgsql-pkg-yum@lists.postgresql.org>"
Fingerprint: B031 F89F C983 E982 6290 6B6E 177B 343B B973 8825
From : /etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-AARCH64-RHEL
PostgreSQL 13 for RHEL / Rocky / AlmaLinux 9 - aarch64 2.4 MB/s | 923 kB 00:00
Dependencies resolved.
================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
================================================================================================================================================================================================================================================================================
Installing:
postgresql aarch64 13.20-1.el9 appstream 1.6 M
postgresql-server aarch64 13.20-1.el9 appstream 5.6 M
Installing dependencies:
postgresql-private-libs aarch64 13.20-1.el9 appstream 135 k
Transaction Summary
================================================================================================================================================================================================================================================================================
Install 3 Packages
Total download size: 7.4 M
Installed size: 31 M
Downloading Packages:
(1/3): postgresql-13.20-1.el9.aarch64.rpm 6.0 MB/s | 1.6 MB 00:00
(2/3): postgresql-private-libs-13.20-1.el9.aarch64.rpm 155 kB/s | 135 kB 00:00
(3/3): postgresql-server-13.20-1.el9.aarch64.rpm 3.4 MB/s | 5.6 MB 00:01
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.2 MB/s | 7.4 MB 00:02
CentOS Stream 9 - AppStream 1.6 MB/s | 1.6 kB 00:00
Importing GPG key 0x8483C65D:
Userid : "CentOS (CentOS Official Signing Key) <security@centos.org>"
Fingerprint: 99DB 70FA E1D7 CE22 7FB6 4882 05B5 55B3 8483 C65D
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql-private-libs-13.20-1.el9.aarch64 1/3
Installing : postgresql-13.20-1.el9.aarch64 2/3
Running scriptlet: postgresql-server-13.20-1.el9.aarch64 3/3
Installing : postgresql-server-13.20-1.el9.aarch64 3/3
Running scriptlet: postgresql-server-13.20-1.el9.aarch64 3/3
Verifying : postgresql-13.20-1.el9.aarch64 1/3
Verifying : postgresql-private-libs-13.20-1.el9.aarch64 2/3
Verifying : postgresql-server-13.20-1.el9.aarch64 3/3
Installed:
postgresql-13.20-1.el9.aarch64 postgresql-private-libs-13.20-1.el9.aarch64 postgresql-server-13.20-1.el9.aarch64
Complete!
[root@dbserver ~]$
Version downloaded was PostgreSQL 13. Create a new directory called 13, under /pgsql/ mounted directory:
[root@dbserver ~]$ mkdir -p /pgsql/13
[root@dbserver ~]$ cd /pgsql/
[root@dbserver pgsql]$ ls -l
total 0
drwxr-xr-x 2 root root 6 Apr 22 20:35 13
[root@dbserver pgsql]$
Default yum installation, create pg binaries under /usr/bin/ directory:
[root@dbserver ~]$ cd /usr/bin/
[root@dbserver bin]$ ls postgre* pg_*
pg_basebackup pg_checksums pg_controldata pg_ctl pg_dump pg_dumpall pg_isready pg_receivewal pg_recvlogical pg_resetwal pg_restore pg_rewind pg_upgrade pg_verifybackup postgres postgresql-setup postgresql-upgrade
[root@dbserver bin]$
Lets change owner to postgres user on /pgsql mount directory:
[root@dbserver /]$ chown -R postgres.postgres /pgsql/
[root@dbserver /]$ ls -l /
total 24
dr-xr-xr-x. 2 root root 6 Jun 25 2024 afs
lrwxrwxrwx. 1 root root 7 Jun 25 2024 bin -> usr/bin
dr-xr-xr-x. 6 root root 4096 Apr 22 19:30 boot
drwxr-xr-x 20 root root 3420 Apr 22 19:44 dev
drwxr-xr-x. 144 root root 8192 Apr 22 20:03 etc
drwxr-xr-x. 2 root root 6 Jun 25 2024 home
lrwxrwxrwx. 1 root root 7 Jun 25 2024 lib -> usr/lib
lrwxrwxrwx. 1 root root 9 Jun 25 2024 lib64 -> usr/lib64
drwxr-xr-x. 2 root root 6 Jun 25 2024 media
drwxr-xr-x. 3 root root 18 Apr 22 19:26 mnt
drwxr-xr-x. 3 root root 16 Apr 22 19:25 opt
drwxr-xr-x 2 postgres postgres 6 Apr 22 19:44 pgsql
dr-xr-xr-x 311 root root 0 Apr 22 19:36 proc
dr-xr-x---. 3 root root 163 Apr 22 19:36 root
drwxr-xr-x 45 root root 1240 Apr 22 20:03 run
lrwxrwxrwx. 1 root root 8 Jun 25 2024 sbin -> usr/sbin
drwxr-xr-x. 2 root root 6 Jun 25 2024 srv
dr-xr-xr-x 12 root root 0 Apr 22 19:36 sys
drwxrwxrwt. 13 root root 4096 Apr 22 20:15 tmp
drwxr-xr-x. 12 root root 144 Apr 22 19:23 usr
drwxr-xr-x. 21 root root 4096 Apr 22 19:30 var
[root@dbserver /]$
Now, we can start our PostgreSQL database. First change user from root to postgres and then execute initdb:
[root@dbserver /]$ su - postgres
[postgres@dbserver ~]$ initdb -D /pgsql/13/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /pgsql/13/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Sao_Paulo
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /pgsql/13/data -l logfile start
[postgres@dbserver ~]$
Lets see which files were created:
[postgres@dbserver ~]$ cd /pgsql/13/data
[postgres@dbserver data]$ ls -l
total 52
drwx------ 5 postgres postgres 41 Apr 22 20:42 base
drwx------ 2 postgres postgres 4096 Apr 22 20:42 global
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_commit_ts
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_dynshmem
-rw------- 1 postgres postgres 4760 Apr 22 20:42 pg_hba.conf
-rw------- 1 postgres postgres 1636 Apr 22 20:42 pg_ident.conf
drwx------ 4 postgres postgres 68 Apr 22 20:42 pg_logical
drwx------ 4 postgres postgres 36 Apr 22 20:42 pg_multixact
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_notify
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_replslot
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_serial
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_snapshots
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_stat
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_stat_tmp
drwx------ 2 postgres postgres 18 Apr 22 20:42 pg_subtrans
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_tblspc
drwx------ 2 postgres postgres 6 Apr 22 20:42 pg_twophase
-rw------- 1 postgres postgres 3 Apr 22 20:42 PG_VERSION
drwx------ 3 postgres postgres 60 Apr 22 20:42 pg_wal
drwx------ 2 postgres postgres 18 Apr 22 20:42 pg_xact
-rw------- 1 postgres postgres 88 Apr 22 20:42 postgresql.auto.conf
-rw------- 1 postgres postgres 28094 Apr 22 20:42 postgresql.conf
[postgres@dbserver data]$
In the future, to not receive failures during backup, lets change read privilege for files:
[postgres@dbserver data]$ chmod 740 -R *
[postgres@dbserver data]$ ls -ltr
total 52
-rwxr----- 1 postgres postgres 3 Apr 22 20:42 PG_VERSION
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_twophase
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_tblspc
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_stat_tmp
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_stat
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_snapshots
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_serial
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_replslot
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_notify
drwxr----- 4 postgres postgres 36 Apr 22 20:42 pg_multixact
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_dynshmem
drwxr----- 2 postgres postgres 6 Apr 22 20:42 pg_commit_ts
-rwxr----- 1 postgres postgres 28094 Apr 22 20:42 postgresql.conf
-rwxr----- 1 postgres postgres 88 Apr 22 20:42 postgresql.auto.conf
-rwxr----- 1 postgres postgres 1636 Apr 22 20:42 pg_ident.conf
-rwxr----- 1 postgres postgres 4760 Apr 22 20:42 pg_hba.conf
drwxr----- 2 postgres postgres 18 Apr 22 20:42 pg_xact
drwxr----- 3 postgres postgres 60 Apr 22 20:42 pg_wal
drwxr----- 2 postgres postgres 18 Apr 22 20:42 pg_subtrans
drwxr----- 2 postgres postgres 4096 Apr 22 20:42 global
drwxr----- 5 postgres postgres 41 Apr 22 20:42 base
drwxr----- 4 postgres postgres 68 Apr 22 20:42 pg_logical
[postgres@dbserver data]$
Lets enable PostgreSQL linux service.
First, check postgresql.service status:
[root@dbserver /]$ systemctl status postgresql.service
× postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
Active: failed (Result: exit-code) since Tue 2025-04-22 20:52:11 -03; 47s ago
Process: 50738 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=1/FAILURE)
CPU: 784us
Apr 22 20:52:11 dbserver systemd[1]: Starting PostgreSQL database server...
Apr 22 20:52:11 dbserver postgresql-check-db-dir[50738]: Directory "/var/lib/pgsql/data" is missing or empty.
Apr 22 20:52:11 dbserver postgresql-check-db-dir[50738]: Use "/usr/bin/postgresql-setup --initdb"
Apr 22 20:52:11 dbserver postgresql-check-db-dir[50738]: to initialize the database cluster.
Apr 22 20:52:11 dbserver postgresql-check-db-dir[50738]: See /usr/share/doc/postgresql/README.rpm-dist for more information.
Apr 22 20:52:11 dbserver systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
Apr 22 20:52:11 dbserver systemd[1]: postgresql.service: Failed with result 'exit-code'.
Apr 22 20:52:11 dbserver systemd[1]: Failed to start PostgreSQL database server.
You will see an error, because data directory is empty. We create that under /pgsql. Let’s fix it. Remove data directory empty created by default PostgreSQL yum installation:
[root@dbserver /]$ cd /var/lib/pgsql/
[root@dbserver pgsql]$ ls
backups data
[root@dbserver pgsql]$ rmdir data
Now, we need to create a symbolic link on /var/lib/pgsql/ for /pgsql/13/data directory:
[root@dbserver pgsql]$ ln -s /pgsql/13/data /var/lib/pgsql/
Enable PostgreSQL service:
[root@dbserver pgsql]$ systemctl enable --now postgresql.service
[root@dbserver pgsql]$ systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
Active: active (running) since Tue 2025-04-22 20:55:11 -03; 8s ago
Process: 51305 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
Main PID: 51307 (postmaster)
Tasks: 8 (limit: 10229)
Memory: 16.4M
CPU: 19ms
CGroup: /system.slice/postgresql.service
├─51307 /usr/bin/postmaster -D /var/lib/pgsql/data
├─51308 "postgres: logger "
├─51310 "postgres: checkpointer "
├─51311 "postgres: background writer "
├─51312 "postgres: walwriter "
├─51313 "postgres: autovacuum launcher "
├─51314 "postgres: stats collector "
└─51315 "postgres: logical replication launcher "
Apr 22 20:55:11 dbserver systemd[1]: Starting PostgreSQL database server...
Apr 22 20:55:11 dbserver postmaster[51307]: 2025-04-22 20:55:11.575 -03 [51307] LOG: redirecting log output to logging collector process
Apr 22 20:55:11 dbserver postmaster[51307]: 2025-04-22 20:55:11.575 -03 [51307] HINT: Future log output will appear in directory "log".
Apr 22 20:55:11 dbserver systemd[1]: Started PostgreSQL database server.
[root@dbserver pgsql]$
Now, looks good.
If you have a firewall and didn’t stop or disable yet, you can do that:
[root@dbserver pgsql]$ firewall-cmd --add-service=postgresql --permanent
[root@dbserver pgsql]$ firewall-cmd --reload
Connect to postgres user and change the password for database administrator user. Choose a hard password and test psql prompt:
[root@dbserver pgsql]$ su - postgres
Last login: Tue Apr 22 20:33:56 -03 2025 on pts/0
[postgres@dbserver ~]$ psql -c "alter user postgres with password '12345'"
ALTER ROLE
[postgres@dbserver ~]$ psql
psql (13.20)
Type "help" for help.
postgres=#
Now, you have a PostgreSQL installed and ready. In our next article, I will show how to configure PostgreSQL to accept remote connections.
My Piece of Data 