DBA Hub

📋Steps in this guide1/10

Install and Configure PostgreSQL 15

Install and Configure PostgreSQL 15

postgresql installationintermediate
by PostgreSQL
13 views
1

Install and Configure PostgreSQL 15

- Install PostgreSQL 15 - Create directories - Change PGDATA directory - Initialize the database - Enable automatic start - Enable archive mode and change archive location - Reload PostgreSQL service - Verify configuration - Add firewall rules
2

Step 1: Install PostgreSQL 15

Code/Command (click line numbers to comment):

1
2
3
4
5
6
# Install the repository RPM:
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Disable the built-in PostgreSQL module:
dnf -qy module disable postgresql
# Install PostgreSQL:
dnf install -y postgresql15-server
3

Step 2: Create directories

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
mkdir -p /pgTb/pgsql15/trdda
mkdir -p /pgIx/pgsql15/trdda
mkdir -p /pgBackup/pgsql15/trdda
mkdir -p /pgArch/pgsql15/arch
mkdir -p /pgData/pgsql15/data
mkdir -p /pgWal/pgsql15/wal

chown -R postgres:postgres /pg*
chmod -R 700 /pg*
4

Step 3: Change PGDATA directory

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
As root:

[root@lxtrdpgdsgv01 ~]#
cat /usr/lib/systemd/system/postgresql-15.service | grep -i "Environment=PGDATA"
Environment=PGDATA=/pgData/pgsql15/data
[root@lxtrdpgdsgv01 ~]#
#
systemctl daemon-reload
5

Step 4: Initialize the database

Code/Command (click line numbers to comment):

1
2
3
4
5
Run as postgres user:
/usr/pgsql-15/bin/initdb -D /pgData/pgsql15/data --waldir=/pgWal/pgsql15/wal
/usr/pgsql-15/bin/pg_ctl -D /pgData/pgsql15/data -l logfile start
ps -ef | grep postgres
/usr/pgsql-15/bin/pg_ctl -D /pgData/pgsql15/data stop
6

Step 5: Enable automatic start

Code/Command (click line numbers to comment):

1
2
3
4
systemctl stop postgresql-15
systemctl enable postgresql-15
systemctl start postgresql-15
systemctl status postgresql-15
7

Step 6: Enable archive mode and change archive location

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
As postgres user:
cp /pgData/pgsql15/data/postgresql.conf /pgData/pgsql15/data/postgresql.conf.bkp
grep -v '^#' /pgData/pgsql15/data/postgresql.conf.bkp | grep '^[A-Za-z0-9]' > /pgData/pgsql15/data/postgresql.conf
edit -- /pgData/pgsql15/data/postgresql.conf

# Added by DBA
listen_addresses = '0.0.0.0' # ipv4 only
max_wal_senders = 10
max_replication_slots = 10
wal_level = 'replica' # or 'logical'
hot_standby = on
archive_mode = on
archive_command = 'cp %p /pgArch/pgsql17/arch/%f'
# shared_preload_libraries = 'repmgr'
wal_log_hints = on
# End
8

Step 7: Reload PostgreSQL service

Code/Command (click line numbers to comment):

1
2
systemctl stop postgresql-15.service
systemctl start postgresql-15.service
9

Step 8: Verify configuration

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sed -i "s/PGDATA=.*/PGDATA=\/pgData\/pgsql15\/data/" .bash_profile
psql
SHOW data_directory;
SHOW archive_mode;
SHOW archive_command;
SHOW archive_timeout;
ls -ld $(psql -t -c "SHOW data_directory")/pg_wal
SELECT pg_switch_wal();
ls -lrth /pgArch/pgsql15/arch/

SELECT f AS wal_file,
       (pg_stat_file('/pgArch/pgsql15/arch/' || f)).size AS size_bytes,
       (pg_stat_file('/pgArch/pgsql15/arch/' || f)).modification AS modified_time
FROM pg_ls_dir('/pgArch/pgsql15/arch') f
WHERE f ~ '^[0-9A-F]{24}$'
ORDER BY modified_time DESC;
10

Step 9: Add firewall rules

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

1
2
3
4
# need sudo or root user 
firewall-cmd --add-port=5432/tcp --permanent --zone=public
firewall-cmd --reload
firewall-cmd --list-ports

Comments (0)

Please to add comments

No comments yet. Be the first to comment!