PostgreSQL master slave replication

Introduction

The goal is to set up automatic copying (replication) of databases from a server.

The server from which information is copied is usually called the leading or primary server. In English-language literature it is usually called master.

The server to which the copy (replica) is saved is usually called the follower or secondary one. In English this used to be called slave, and now the more politically correct term standby is being introduced.

[andrei@localhost ~]$ at the start of a line means that the commands are run as an ordinary user CentOS 7, a member of the sudo group

-bash-4.2$ at the start of a line means that the commands are run as the postgres user

-bash-4.2$ at the start of a line means that the commands are run as the postgres user

This is not always indicated, but if it is - then you should pay attention to it.

The setup depends heavily on the version, even the minor one.

Let me remind you that PostgreSQL's policy for assigning versions changed slightly between 9.6 and 10.

That is, 9.6 differs not only from 10 but also from 9.2. Some parameters available in 9.6 simply do not exist in 9.2

Preparing the working environment

You need to install and configure two PostgreSQL databases.

In this example you will learn how to do it using two virtual CentOS Linux on VirtualBox

In this example:

the leading server (master) has the IP address 192.168.56.109 (master_ip)

the follower (slave/stand by) has 192.168.56.110 (slave_ip)

Topics that are recommended to get acquainted with to create a similar setup:

On each host install PostgreSQL

sudo yum install postgresql-server postgresql-contrib

Set a password for the postgres user

sudo passwd postgres

Log in as this user

sudo su - postgres

Create an ssh key for this user.

ssh-keygen

Press Enter on all the notifications that appear.

Pass the key to the other server:

ssh-copy-id IP_address_of_another_server

Initialize the database and start the server

cd /var/lib/pgsql
postgresql-setup initdb
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

On both hosts open port 5432. If you do not know how to do this - the instructions can be found in the article

«CentOS Firewall - open a port»

PostgreSQL 9.2

PostgreSQL 9.2 is already not supported .

At the time of writing this article the closest supported version is 9.6

Setting up master

First you need to set up the leading server (master)

The location of the configuration file postgresql.conf can be obtained by running

-bash-4.2$ su - postgres -c "psql -c 'SHOW config_file;'"

Password: config_file ------------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row)

vi /var/lib/pgsql/data/postgresql.conf

wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 50

# - Connection Settings - listen_addresses = 'localhost, 192.168.56.109' # what IP address(es) to listen on;

wal_level = hot_standby # minimal, archive, or hot_standby

max_wal_senders = 1 # max number of walsender processes # (change requires restart) wal_keep_segments = 50 # in logfile segments, 16MB each; 0 disables

Restart postgresql as an ordinary user

[andrei@host ~]$ sudo systemctl restart postgresql

Log in as postgres again

sudo su - postgres

In the file pg_hba.conf add slave_ip

vi /var/lib/pgsql/data/pg_hba.conf

host replication postgres 192.168.56.110/32 trust

Now you need to make a copy of the data from the master and send it to the slave

If you have logged out of the postgres user - log in again

su - postgres

Backup (I am doing it from an empty database, right after installation)

psql -c "SELECT pg_start_backup('replbackup');"

pg_start_backup ----------------- 0/2000020 (1 row)

tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/data
psql -c "SELECT pg_stop_backup();"

An error will most likely appear

NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup pg_stop_backup ---------------- 0/20000E0 (1 row)

Since the whole directory will be copied, we can say that we are using other means

Send it to the slave

scp /tmp/db_file_backup.tar andrei@192.168.56.110:/tmp/

Setting up slave

Setting up the slave, or, as it has recently become fashionable to write, the stand by server, also begins with determining the directory with the settings.

su - postgres -c "psql -c 'SHOW data_directory;'"

Password: data_directory --------------------- /var/lib/pgsql/data (1 row)

Or

echo $PGDATA

/var/lib/pgsql/data

-bash-4.2$ su - postgres -c "psql -c 'SHOW config_file;'"

Password: config_file ------------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row)

Log in as an ordinary user (not postgres)

Stop the postgresql service:

sudo systemctl stop postgresql

==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units === Authentication is required to manage system services or units. Multiple identities can be used for authentication: 1. andrei 2. tester Choose identity to authenticate as (1-2): 1 Password: ==== AUTHENTICATION COMPLETE ===

Log in as postgres and, just in case, create an archive of the database:

sudo su - postgres
tar -czvf /tmp/data_pgsql.tar.gz /var/lib/pgsql/data

In this example we will save all the contents of the directory /var/lib/pgsql/data as an archive /tmp/data_pgsql.tar.gz.

ls /tmp | grep pgsql

data_pgsql.tar.gz

Or you can simply copy it to another folder

sudo su - postgres
bash-4.2$ mv /var/lib/pgsql/data/ /var/lib/pgsql/data.old

Delete the contents of the data directory:

rm -rf /var/lib/pgsql/data/*
ls /var/lib/pgsql/data

 

Now you need to unpack the archive that was received from the master

tar xvfP /tmp/db_file_backup.tar

Delete postmaster.pid so that the slave does not see the master's pid as its own

rm -f /var/lib/pgsql/data/postmaster.pid

In the file postgresql.conf

Specify listen_addresses and enable hot_standby

vi /var/lib/pgsql/data/postgresql.conf

# - Connection Settings - listen_addresses = 'localhost, 192.168.56.110' # what IP address(es) to listen on;

# - Standby Servers - # These settings are ignored on a master server. hot_standby = on # "on" allows queries during recovery # (change requires restart) hot_standby_feedback = on # send info from standby to prevent

recovery.conf

Now on the slave you need to edit the file recovery.conf

Perhaps the sample lies in the directory share then it can be copied from there

cp /usr/pgsql/share/recovery.conf.sample /var/lib/pgsql/data/recovery.conf

If it is not there - you need to create it in the same directory where postgresql.conf

In this example it is

/var/lib/pgsql/data

The file can be created with the command

touch recovery.conf

My example of the file can be viewed here

The example that I took as a basis - on the pgpool site

vi /var/lib/pgsql/data/recovery.conf

standby_mode = on primary_conninfo = 'host=192.168.56.110 port=5432'

Update the access permissions and start the server

chown postgres.postgres /var/lib/pgsql/data/recovery.conf
systemctl start postgresql

PostgreSQL 9.6

For 9.6 the instructions are under development

Create the repluser user

This should be done as the postgres user

su - postgres

Password:
Last login: Wed Jun 9 16:33:50 EEST 2021 on pts/1
-bash-4.2$

createuser --replication -P repluser

Enter password for new role:
Enter it again:

-bash-4.2$ exit

logout

If you have PostgreSQL version 9.6

# - Connection Settings - listen_addresses = 'localhost, 192.168.56.109' # what IP address(es) to listen on;

wal_level = replica

max_wal_senders = 1 # max number of walsender processes

hot_standby = on # "on" allows queries during recovery

hot_standby_feedback = on # send info from standby to prevent

Now you need to copy the contents of the analogous directory from the server

In PostgreSQL 9.6 you can add the --write-recovery-conf option

pg_basebackup -h 192.168.56.109 -U repluser -D /var/lib/pgsql/data --xlog-method=stream --write-recovery-conf

Now you need to copy the contents of the analogous directory from the server

pg_basebackup -h 192.168.56.109 -U repluser -D /var/lib/pgsql/data --xlog-method=stream

Password:

ls /var/lib/pgsql/data

backup_label global pg_hba.conf pg_log pg_notify pg_snapshots pg_subtrans pg_twophase pg_xlog base pg_clog pg_ident.conf pg_multixact pg_serial pg_stat_tmp pg_tblspc PG_VERSION postgresql.conf

In the file postgresql.conf replace master_ip with slave_ip

Checking replication

Checking replication does not differ for 9.2 and 9.6

On the slave log in as postgres and run psql

su postgres
psql

Study the list of existing databases

\l

List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows)

Now on the master create a new database

postgres=# CREATE DATABASE "repltest" WITH OWNER "postgres" ENCODING 'UTF8';

Wait a little and check whether the new database has appeared on the slave

\l

List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | repltest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)

Article author: Andrei Olegovich