01.01. Database PostgreSQL Server Installation & Configuration

  <<< Back   Next >>>
1. Add PostgreSQL Yum Repository:
[root]# dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2. Disable modules some other version of PostgreSQL:
[root]# dnf -qy module disable postgresql

3. Confirm the list of enabled repositories:
[root]# dnf repolist
[root]# dnf clean all

4. Check to see if PostgreSQL 14 packages are available on the repository:

[root]# yum search postgresql14

5. Install PostgreSQL 14 server 
[root]# dnf -y install postgresql14-server.x86_64 postgresql14.x86_64

6. Check installed version:
[root]# rpm -qi postgresql14-server

7. Install Barman client utilities:
[root]# dnf -y install barman-cli.noarch

8. Create user owner postgres and database catalogs:
[root]# 
groupadd postgres
useradd -c 'PostgreSQL software owner' -d /home/postgres -g postgres -s /bin/bash postgres
mkdir -p /srv/postgres/data/
chown -R postgres:postgres /srv
chmod 700 /srv/postgres/data/

9. Initialize and start/stop database service:
[root]# su - postgres
-- add profile
export PATH=/usr/pgsql-14/bin:/usr/sbin:/usr/bin:/usr/sbin:/usr/bin:/sbin:/bin
export PGDATA=/srv/postgres/data/14
[postgres]$ 
mkdir /srv/postgres/data/14
/usr/pgsql-14/bin/pg_ctl initdb -o "-k" -D /srv/postgres/data/14
/usr/pgsql-14/bin/pg_ctl -D /srv/postgres/data/14 -l logfile start
/usr/pgsql-14/bin/pg_ctl stop

10. Change service configuration:
[root]# vi /usr/lib/systemd/system/postgresql-14.service
Environment=PGDATA=/srv/postgres/data/14
Environment=PGLOG=/srv/postgres/data/14/pg_log/postgresql.log

11. Enable and start service:
[root]# systemctl enable postgresql-14.service
[root]# systemctl start postgresql-14.service

12. Add replication slot:

[postgres]$ psql>

select pg_create_physical_replication_slot('barman'); 

-- check

select * FROM pg_replication_slots ;

13. Add user for barman connection:

[postgres]$ psql>

create user barman with password 'pasword123';

create user streaming_barman with password 'pasword123';

alter user barman WITH SUPERUSER;

alter user streaming_barman WITH SUPERUSER;

14. Modify server parameters:

alter system set listen_addresses='*';

alter system set archive_command = 'barman-wal-archive barman-bcp pg-db01 %p';

alter system set archive_mode = 'on';

alter system set archive_timeout = 60;

alter system set wal_level = 'replica';

alter system set max_wal_senders = 10;

alter system set max_replication_slots = 10;

-- check

select name,setting,unit from pg_settings where name in ('wal_level','archive_mode','archive_command','archive_timeout','max_wal_senders','max_replication_slots');

15. Add rules for connection:

$ cat /srv/postgres/data/14/pg_hba.conf 

host    all                  barman                    192.168.1.40/32       md5

host    replication     streaming_barman  192.168.1.40/32       md5

16. Configure Passwordless Authentication in Barman Server:

[postgres@pg-db01]$ 

ssh-keygen -t rsa -b 2048

cat ~/.ssh/id_rsa.pub|ssh barman@barman-bcp "cat >>~/.ssh/authorized_keys"

-- check

ssh barman@barman-bcp

17. Check service configuration:

[root]# systemctl restart postgresql-14.service

[root]# systemctl status postgresql-14.service

18. Test WAL copy:

[postgres@pg-db01 ~]$ barman-wal-archive --test barman-bcp pg-db01 DUMMY

Ready to accept WAL files for the server pg-db01

  <<< Back   Next >>>