PostgreSQL 14 simple replication setup

Colleagues hello to all.

In today's article, we'll talk about how to set up replication in PostgreSQL. To put it simply, replication is copying the state of one server to another by means of wal logs. Well, to be more precise, absolutely any changes that occur on the main server will be copied to the replica server.

Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR

I will use such concepts in the article as source and replica. The source is the main server, it can also be called master, and the replica is the server that will accept all changes from the main server, it can also be called standby.

Servers I will use.

postgresql1.local - 192.168.2.231 source

postgresql2.local - 192.168.2.232 replica

Article content:

1. Setting parameters postgresql.conf on source.
2. Create a replication user on the source.
3. Configuring parameters pg_hba.conf on the source.
4. Move data from source to replica.
5. Privilege settings
6. Starting a replica and checking the status of replication.

1. Configuring postgresql.conf parameters on the source.

For the simplest replication setup, we only need to set two parameters in the postgresql.conf configuration file on the source.

Find the parameter wal_level and set the values replica.

wal_level = replica

Find the parameter max_wal_senders and set the values ​​to 100.

max_wal_senders = 100

Be sure to restart the database after making changes.

$. pg_ctl restart

2. Create a replication user on the source.

Replication in PostgreSQL is implemented by transferring wal logs, and the account that we will now create will be responsible for transferring these logs. Usually the name of the account that is responsible for replication is called, for example, user_slave, slave, or replica. We will create an account called replicator.

sql> CREATE ROLE replicator WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT REPLICATION CONNECTION LIMIT -1 PASSWORD 'Qwerty123';

The main thing is not to forget to specify that this user has the right to replicate. The REPLICATION privilege is responsible for replication in PostgreSQL.

3. Configuring pg_hba.conf parameters on the source.

Now we need to configure access so that there is access from the replica server to the source server and vice versa from the source server to the replica server. The  pg_hba.conf file is responsible for setting up access. Find it and add it to it:

host replication replicator 192.168.2.231/32 trust
host replication replicator 192.168.2.232/32 trust

After making changes, we need to either restart the database or run a housekeeping function so that the  pg_hba.conf file is reread by the database. I use a utility function.

sql> SELECT pg_reload_conf();

4. Transfer data from source to replica.

Now it's time to migrate all the content from the origin server to the replica server. For these purposes, I will use the  pg_basebackup utility. Perform data transfer on the replica server:

$. pg_basebackup -h 192.168.2.231 -U replicator -D /app/postgresql/pgdatabase/data --write-recovery-conf --progress --verbose

5. Privilege settings

After migrating data from the source to the replica on the replica server, you need to assign the postgres owner of all migrated directories.

$. chmod -R 0700 /app/postgresql/pgdatabase/data

6. Launching a replica and checking the replication status.

Now we can start the database on the replica server.

$. pg_ctl start

The database on the replica started successfully.

Now on the source we need to check whether the replica server can connect to it, for this we execute the request on the source:

sql> select application_name, state, sent_lsn, write_lsn,  sync_state from pg_stat_replication;

The replica server has successfully connected to the source server and replication has started working for us.

Thank you all, I hope that my article helped you in some way.