PostgreSQL replication

/ Editor Leon | 六小編

Original link: https://editor.leonh.space/2023/postgresql-replication/

Prepare two hosts with PostgreSQL installed, one is called primary and the other is called replica. The concept of replication is that any changes in the primary database will be reflected in the replica immediately, just like the clone of the shadow. Of course, if the two hosts If you are located in a different place, it will not be so instant. After a brief introduction to the concept of replication, let’s get to the point.

Primary setting

Create a duplicate account

Create an account and grant copy permission:

 CREATE ROLE repuser WITH REPLICATION PASSWORD ' <PASSWORD> ' LOGIN;

configuration override settings

Configure primary replication settings, take my machine as an example, the configuration file is in /etc/postgresql/15/main/postgresql.conf.

The configuration file is divided into several sections:

Go to the “CONNECTION AND AUTHENTICATION” section and modify the following items:

  • listen_addresses , change from 'localhost' to the primary’s own IP, or if there are multiple network cards, you can change it to * more generously.

Go to the “WRITE-AHEAD-LOG” section and modify the following items:

  • wal_level was originally commented out, but the comment was removed and changed to replica .

The write-ahead log (WAL) above is simply the transaction record of the database. The so-called replication is to transfer the WAL of the primary to the replica, just like running and swimming. A does what B does.

Locate the “REPLICATION” section and modify the following items:

  • max_wal_senders was originally commented out, remove the comment and change the value to 2 .
  • max_replication_slots was originally commented out, but the comment was removed and changed to 2 .

Restart PostgreSQL after changing:

 > sudo systemctl restart postgresql

Create replication slots

The primary’s own WAL will be deleted over time, but under the replicatoin slot mechanism, it will wait until the replica has received the record before being queued for deletion. If the replica is disconnected, the primary will help it keep the record and wait until the replica is online That day (seemed a bit romantic).

Enter the PostgreSQL interface and create a replication slot:

 SELECT * FROM pg_create_physical_replication_slot(' replica_1_slot ');

Build and leave to go back to the Linux shell.

Set up connection and authentication

The configuration file for PostgreSQL user authentication is in /etc/postgresql/15/main/pg_hba.conf, and the bottom part looks like this:

 # Allow replication connections from localhost, by a user with the # replication privilege.  
# TYPE DATABASE USER ADDRESS METHOD  
local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256

The bottom three lines:

  • The first line indicates that all users with replication authority can log in from the local machine through the Unix socket, and the login authentication is based on the Linux system authentication.
  • The second line indicates that all 127.0.0.1 (local) users with replication authority can log in through TCP socket and authenticate with password.
  • The third line is the same as above, except that the IP is native IPv6.

In our replication scenario, replica is another host, so open a path for it and add a line:

 # TYPE DATABASE USER ADDRESS METHOD  
host replication all samenet scram-sha-256

samenet here means the client on the same network segment as the primary machine. For example, if the primary IP is 192.168.100.212/23, its network segment is 192.168.100.1 – 192.168.101.254 .

Restart PostgreSQL after changing:

 > sudo systemctl restart postgresql

In this way, the replica should be connected.

Replica settings

Stop the replica PostgreSQL service

 > sudo systemctl stop postgresql

Cut replica database

Backup before hacking:

 > cd /var/lib/postgresql/15 > sudo -u postgres tar --create --file =main.tar main

As a shadow fighter, you cannot have your own identity. Cut off the original database file of the replica:

 > sudo -u postgres rm --recursive --force /var/lib/postgresql/15/main/*

backup primary database

Execute the following command on the replica to back up the current primary database to the replica:

 > sudo -u postgres bash  
( postgres_shell )> pg_basebackup \ --host =192.168.100.212 \ --username =repuser --password \ --pgdata =/var/lib/postgresql/15/main \ --progress --verbose

Rebuild the replica with the primary database

After backing up the primary to the replica, rebuild the replica based on the primary database so that both have the same foundation.

Touch an empty file:

 > sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

Later, when the PostgreSQL service detects this file, it will start restore mode to rebuild the database.

But there is no rush to call the service at this moment, there are more configurations waiting for us…

configuration override settings

Here to configure the replication settings on the replica side, modify the file /etc/postgresql/15/main/postgresql.conf.

Go to the “WRITE-AHEAD-LOG” section and modify the following items:

  • wal_level was originally commented out, but the comment was removed and changed to replica .

Locate the “REPLICATION” section and modify the following items:

  • max_wal_senders was originally commented out, remove the comment and change the value to 2 .
  • max_replication_slots was originally commented out, but the comment was removed and changed to 2 .
  • primary_conninfo was originally commented out, cancel the comment, and fill in 'host=192.168.100.212 port=5432 user=repuser password=<PASSWORD> application_name=r1' .
  • primary_slot_name , originally commented out, cancel the comment and fill in replica_1_slot .

in:

  • The setting values ​​of the first two items are the same as those of primary.
  • application_name=r1 in the third item, r1 in this part is the identification name for the communication between replica and primary, which will be used later.

Start the replica PostgreSQL service

Finally ready to start:

 > sudo systemctl start postgresql

Confirm copy status

First look at the log confirmation of the newly started replica:

 > tail /var/log/postgresql/postgresql-15-main.log

You should see a message similar to the following:

 2023-03-14 10:00:12.276 CST [9401] LOG: starting PostgreSQL 15.2 (Ubuntu 15.2-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit 2023-03-14 10:00:12.277 CST [9401] LOG: listening on IPv4 address "0.0.0.0", port 5432 2023-03-14 10:00:12.277 CST [9401] LOG: listening on IPv6 address "::", port 5432 2023-03-14 10:00:12.279 CST [9401] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2023-03-14 10:00:12.283 CST [9404] LOG: database system was interrupted; last known up at 2023-03-14 09:06:15 CST 2023-03-14 10:00:12.349 CST [9404] LOG: entering standby mode 2023-03-14 10:00:12.352 CST [9404] LOG: redo starts at 0/C000028 2023-03-14 10:00:12.353 CST [9404] LOG: consistent recovery state reached at 0/C000138 2023-03-14 10:00:12.353 CST [9401] LOG: database system is ready to accept read-only connections 2023-03-14 10:00:12.378 CST [9405] LOG: started streaming WAL from primary at 0/D000000 on timeline 1

It can be seen that at this time, the replica is used as an avatar. In addition to copying, only reading is allowed, and data cannot be manually added or changed.

Then enter the PostgreSQL of the replica to query the replication status:

 \x # Turn on the expanded display mode  
SELECT * FROM pg_stat_wal_receiver;

The \x is just to beautify the output format, and the beautified output should look like this:

 -[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pid | 9405 status | streaming receive_start_lsn | 0/D000000 receive_start_tli | 1 written_lsn | 0/D000148 flushed_lsn | 0/D000148 received_tli | 1 last_msg_send_time | 2023-03-14 10:22:14.280002+08 last_msg_receipt_time | 2023-03-14 10:22:14.30126+08 latest_end_lsn | 0/D000148 latest_end_time | 2023-03-14 10:00:12.385886+08 slot_name | replica_1_slot sender_host | 192.168.100.212 sender_port | 5432 conninfo | user=repuser password=******** channel_binding=prefer dbname=replication host=192.168.100.212 port=5432 application_name=r1 fallback_application_name=15/main sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

status can be seen in the synchronous stream.

Then look at the primary side.

Enter the primary PostgreSQL query replication status:

 \x # Turn on the expanded display mode  
SELECT * FROM pg_stat_replication;

The result is as follows:

 -[ RECORD 1 ]----+------------------------------ pid | 27576 usesysid | 16388 usename | repuser application_name | r1 client_addr | 192.168.100.213 client_hostname | client_port | 50768 backend_start | 2023-03-14 10:00:12.366893+08 backend_xmin | state | streaming sent_lsn | 0/D000148 write_lsn | 0/D000148 flush_lsn | 0/D000148 replay_lsn | 0/D000148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-03-14 10:13:23.53618+08

in:

  • state can be seen as a synchronous stream.
  • Several -lag are empty, indicating that the replica is not behind.

If there is no problem with the above, the replication mechanism will be successfully established, and all operations on the primary will be synchronously reflected on the replica. At present, this mode can only be regarded as a hot backup, and it cannot be called high availability, because the primary single point of failure will not be replaced immediately .

This article is transferred from: https://editor.leonh.space/2023/postgresql-replication/
This site is only for collection, and the copyright belongs to the original author.