Preface
PostgreSQL Streaming Replication is a new WAL delivery method provided by 9.0. When using stream replication, whenever the Primary node WAL is generated, it will be immediately passed to the Standby node, and stream replication providesasynchronousandsynchronousTwo modes, the synchronization mode can ensure the loss of data 0.
1. Configure the environment
1.1 Environment introduction
| Host Name | IP address | Role | Data Directory |
|---|---|---|---|
| 172-16-104-7 | 172.16.104.7 | Master | /data/pgsql12/data/ |
| 172-16-104-56 | 172.16.104.56 | Standby | /data/pgsql12/data/ |
PostgreSQL Version: PostgreSQL 12.2 Operating System: CentOS Linux release 7.8.2003 (Core)
1.2 Main library whitelist
Master node configurationpg_hba.conf Indicates that the user connection accepts stream replication:
host replication all 0/0 md5
The meaning of the above SQL statement is to allow any user to initiate a stream replication connection to this database from any network (0/0) network, using MD5's password authentication.
1.3 Main library parameter configuration
# Listenlisten_addresses = '*' # The maximum concurrency number of stream replication clients, set to 0 to disable replicationmax_wal_senders = 10 # WAL log levelwal_level = replica
The above parameters need to be restarted and take effect after the PostgreSQL service is restarted.
2. Stream copy construction
Use pg_basebackup to restore the main library data backup to the Standby node and build itAsynchronous/synchronousStream replication, steps summary:
- Prepare the environment PostgreSQL primary and standby nodes.
- Parameters are adjusted pg_hba.conf, to create a copy user.
- Back up the data of the primary node and restore it to the backup node.
- Modify primary_conninfo to start the backup library.
- Check whether the startup is successful.
2.1 Backup Recovery
Perform full backup in the main library:
pg_basebackup -D /data/pgsql12/backup -v -P -X stream -Upostgres -h 127.0.0.1 -p5432 -R
Backup SCP to the backup node:
scp -r ./backup/ [email protected]:/data/backup
Close the Standby node, clear the Standby node's data file, or use mv to modify the directory name:
# Back up the data directory, or you can clear it directlymv /data/pgsql12/data /data/pgsql12/data_bak # Transfer backup files to data directorymv /data/pgsql12/backup /data/pgsql12/data # Modify file groupchown -R postgres:postgres /data/pgsql12
2.2 Create a copy user
The main library creates users dedicated to stream replication:
CREATE ROLE repl REPLICATION LOGIN PASSWORD 'repl123';
2.3 Parameter modification
PostgreSQL usage The file indicates that the instance is a Standby node. Adding the -R parameter (write configuration for replication) using pg_basebackup will be in Write to the fileprimary_conninfo Parameter information, we need to adjust it according to the actual situation:
primary_conninfo = 'host=172.16.104.7 port=5432 user=repl password=repl123'
2.4 Start and check
After the parameter configuration is completed, start the Standby node:
pg_ctl -D /data/pgsql12/data/ -l /data/pgsql12/logs/ start
On the master node, you can query the monitoring information for stream replication through SQL below:
postgres=# \x Expanded display is on. postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 27408 usesysid | 24865 usename | repl application_name | walreceiver client_addr | 172.16.104.56 client_hostname | client_port | 40990 backend_start | 2023-09-05 14:11:56.978627+08 backend_xmin | state | streaming sent_lsn | 6/4001BB0 write_lsn | 6/4001BB0 flush_lsn | 6/4001BB0 replay_lsn | 6/4001BB0 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2023-09-05 15:03:58.408518+08
instate = streaming It means that the stream replication status is normal. If there is an exception, you can view the information in the error log, or it will be started.tail -f error_log, pay attention to the output exception information in real time.
2.5 Synchronous stream replication
The above 2.1~2.4 introduces how to build itasynchronousStream copy.
The disadvantage of PostgreSQL asynchronous stream replication is that when the main library is damaged, activation of the standby library may lose some data. This is the same as MySQL asynchronous replication. The main library only sends incremental logs. After the hangup, some logs may not be received from the library. When switching occurs, data loss will occur. Synchronous replication can solve this type of problem. However, it should be noted that if the configuration synchronous replication Standby node is sluggish, the Priamry node will be stuck, so there are generally multiple Standby nodes, which at least ensures that WAL is synchronized to one Standby node.
Add one more synchronous replication configurationsynchronous_standby_names There are 3 configuration methods for parameters:
synchronous_standby_names = 's1,s2,s3'
In this example, if there are three Standby nodes, s1, s2, and s3, running, it means that s1 is a synchronous node, and the other nodes are potential synchronous nodes, that is, WAL can be submitted only by passing it to the s1 node.
synchronous_standby_names = 'FIRST 2 (s1, s2, s3)'
In this example, if four standby servers s1, s2, s3, and s4 are running, the two standby servers s1 and s2 will be selected as synchronous standbys because they appear in the front of the list of standby server names. s3 is a potential synchronous backup, and when either s1 or s2 fails, it will replace it. s4 is an asynchronous fallback because its name is not in the list.
synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
In this example, if four backup servers s1, s2, s3, and s4 are running, the transaction commit will wait for a reply from at least any two of the backup servers. s4 is an asynchronous fallback because its name is not in this list.
Now our architecture is a Primary node and a Standby node. Now it is adjusted toSynchronous stream replicationModify the main library parameters:
# where walreceiver is the name of the Standby node, set by application_name in primary_conninfosynchronous_standby_names = 'walreceiver'
Modifying this parameter does not require restarting the database, just use reload to reload the configuration:
pg_ctl reload -D /data/pgsql12/data/
Query the status information of stream replication in the Primary node:
postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 13561 usesysid | 24865 usename | repl application_name | walreceiver client_addr | 172.16.104.56 client_hostname | client_port | 42126 backend_start | 2023-09-06 17:18:48.297466+08 backend_xmin | state | streaming sent_lsn | 6/50007D0 write_lsn | 6/50007D0 flush_lsn | 6/50007D0 replay_lsn | 6/50007D0 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2023-09-06 17:45:00.706196+08
insync_stateDepend onasync Becomesync Indicated as synchronization mode.
2.6 Synchronous replication level
There is another parameter to affect synchronous replicationsynchronous_commit Used to set the synchronization level of the transaction:
postgres=# select * from pg_settings where name = 'synchronous_commit';
-[ RECORD 1 ]---+------------------------------------------------------
name | synchronous_commit
setting | on
unit |
category | Write-Ahead Log / Settings
short_desc | Sets the current transaction's synchronization level.
extra_desc |
context | user
vartype | enum
source | default
min_val |
max_val |
enumvals | {local,remote_write,remote_apply,on,off}
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f- local: After the WAL log is persisted locally (no need to worry about remoteness) transaction commit can be returned.
- remote_write: The WAL log is passed to the memory of the backup library (does not have to wait for it to be persisted) before returning the transaction commit.
- remote_apply: The WAL log is transmitted to the backup library and applied, and the transaction commit is returned.
- on: The WAL log is passed to the backup library and is persisted (does not have to wait for it to be applied) before returning.
- off: No need to wait for the WAL log to be persisted locally, and the transaction commit can be returned immediately regardless of whether it is transmitted to the remote or not.
For synchronous replication, optional values are remote_write, remote_apply, and on.
3. Stream replication monitoring
3.1 Role judgment
select pg_is_in_recovery();
Determine whether the database is the main libraryf It means it is the main library.t It means that it belongs to the library role.
3.2 Main library viewing stream replication
To view the stream replication information, you can view the pg_stat_replication view in the main library, and you can view the status information of stream replication:
- sent_lsn: The location where the WAL is sent.
- write_lsn: The backup library has received this part of the log, but has not been flushed to disk.
- flush_lsn: The backup library has written WAL to disk.
- replay_lsn: The location where the WAL is applied in the library.
- sync_state: Synchronous mode.
- state: Stream replication status.
select * from pg_stat_replication;
Output result:
postgres=# \x
Expanded display is on.postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 13561
usesysid | 24865
usename | repl
application_name | walreceiver
client_addr | 172.16.104.56
client_hostname |
client_port | 42126
backend_start | 2023-09-06 17:18:48.297466+08
backend_xmin |
state | streaming
sent_lsn | 6/50007D0
write_lsn | 6/50007D0
flush_lsn | 6/50007D0
replay_lsn | 6/50007D0
write_lag |
flush_lag |
replay_lag |
sync_priority | 1
sync_state | sync
reply_time | 2023-09-07 10:06:18.000504+08
3.3 Delay monitoring
Use the SQL below to view how many bytes of the Standby node lags behind the main library WAL log:
select pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) from pg_stat_replication;
3.4 Repository query copy information
You can also query it in the backup librarypg_stat_wal_receiver View, obtain monitoring information for stream replication:
- pid: WAL Receive process ID.
- status: streaming replication status, only streaming is the normal state.
- receive_start_lsn: The location of the WAL The first WAL log used when the WAL receives process starts.
- receive_start_tli: WAL The first timeline number used when the WAL receives the process starts.
- received_lsn: The location of the last WAL log that has been received and has been written to disk.
- received_tli: The timeline number of the last WAL log that has been received and has been written to disk.
- last_msg_send_time: After receiving the last WAL log message, send the sending time of the confirmation message to the main library.
- last_msg_receipt_time: The reception time of the last WAL log message received by the backup library.
- slot_name: Use the name of the copy slot.
- conninfo: The connection string, password and other security-related information connected to the main library will be hidden.
select * from pg_stat_wal_receiver;
postgres=# \x Expanded display is on. postgres=# postgres=# select * from pg_stat_wal_receiver; -[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ pid | 113395 status | streaming receive_start_lsn | 6/5000000 receive_start_tli | 3 received_lsn | 6/50007D0 received_tli | 3 last_msg_send_time | 2023-09-07 10:20:00.207856+08 last_msg_receipt_time | 2023-09-07 10:20:00.20971+08 latest_end_lsn | 6/50007D0 latest_end_time | 2023-09-06 17:19:46.661221+08 slot_name | sender_host | 172.16.104.7 sender_port | 5432 conninfo | user=repl password=******** dbname=replication host=172.16.104.7 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any
This is the end of this article about PostgreSQL stream replication. For more related PostgreSQL stream replication content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!