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.
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
- 192.168.56.109 — the IP address of the server on which it will listen for Postgre requests;
- wal_level specifies how much information is written to the WAL (Write Ahead Log - a transaction log that is used for replication);
- max_wal_senders — the number of planned slaves;
- hot_standby — determines whether it is possible or not to connect to postgresql to run queries during recovery;
- hot_standby_feedback — determines whether or not the slave server will report to the master the queries that it executes.
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
- 192.168.56.109 — the IP address of the server on which it will listen for Postgre requests;
- wal_level specifies how much information is written to the WAL (Write Ahead Log - a transaction log that is used for replication);
- max_wal_senders — the number of planned slaves;
- hot_standby — determines whether it is possible or not to connect to postgresql to run queries during recovery;
- hot_standby_feedback — determines whether or not the slave server will report to the master the queries that it executes.
-
Additionally, in some PostgreSQL versions you need to specify
max_replication_slots — the maximum number of replication slots (this parameter is not needed for postgresql 9.2 — the server will not start with it);
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