当前位置:网站首页>PostgreSQL master-slave replication

PostgreSQL master-slave replication

2021-09-15 05:58:48 Don't eat sour radish

  1. The active and standby databases are started , Start the standby database wal_receiver process ,wal The process sends a connection request to the main library ;
  2. The main library starts after receiving the connection request wal_sender process , And with wal_receiver Process establishment tcp Connect ;
  1. For the library wal_receiver The process sends the latest wal lsn To the main library ;
  2. The main library performs lsn contrast , Send heartbeat information to the standby database regularly , To confirm the availability of the standby database , And there will be no passing wal Send log files , At the same time call SyncRepWaitForLSN() Function to get the latch , And wait for the standby database to respond ; The release timing of the latch is related to the selection of the active / standby synchronization mode ;
  1. The standby database calls the operating system write() Function will wal File write cache , And then invoke the operating system fsync() Function will WAL Refresh to disk , Then proceed WAL File playback ; At the same time, the standby database returns... To the primary database ack Confirmation information ,ack The message contains write_lsn、flush_lsn、replay_lsn, It is used to inform the main database of the current WAL Application location and status of logs in the standby database , The relevant location information can be accessed through pg_stat_replication To view ;
  2. If enabled hot_standby_feedback Parameters , The standby database will send a message to the primary database regularly xmin Information , To ensure that the main library will not vacuum Tuple information required by the standby database ;

 

 

Lord :

#  Modify the configuration file 
vim postgresql.conf
	wal_level = hot_standby			#  modify WAL The output level of log information 
  max_wal_senders = 10				#  Set the largest WAL Number of sending processes , A stream replicated standby database usually consumes only one sending process from the primary database , Must be less than max_connections
  wal_keep_segments = 512			#  Set up WAL Number of log files retained , Default single WAL The size of the file is 16M, Here is the 512*16MB=8GB
	  logging_collector = on			#  Open log 
  log_directory = 'pg_log'		#  Set log path 
  log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'		#  Set the log file name 
  synchronous_commit = off		#  Turn off sync commit , Only for off, Otherwise, the primary database will wait for the standby machine to respond , When the standby machine fails, the host machine will also have problems 

#  Create replication users 
create user repl replication login password'123456';

#  Modify user authentication file 
vim pg_hba.conf 
	#  Add the following configuration , Considering the exchange of active and standby roles , Suggest pg_hba.conf Active and standby 
  host	replication		repl	192.168.10.128/32		md5
  host	replication		repl	192.168.10.129/32		md5

from :

#  Test whether the slave server can connect to the master server database 
psql -U postgres -h 192.168.10.128
#  After the connection is successful, stop the slave Library Service , Clear slave node data 
rm -rf xxx/data/*
#  Copy data from primary to secondary 
pg_basebackup -h 192.168.10.128 -U repl -D xxx/data -X stream -P
#  Copy share In the catalog recovery.conf.sample file , And modify it recovery.conf
cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf
vim recovery.conf
	standby_mode = 'on'
  recovery_target_timeline = 'latest'
  primary_conninfo = 'host=192.168.10.128 port=5432 user=repl password=123456'
  
#  Pay attention to modifying the standby database configuration file , Because it is copied from the main library 
  hot_standby = on   # Allow queries while backing up 
  max_standby_streaming_delay = 30s # Optional , Maximum delay for stream replication 
  wal_receiver_status_interval = 10s # Optional , Maximum interval from reporting status to master 
  hot_standby_feedback = on # Optional , Feedback to the master when querying conflicts 
  max_connections = 1000 # Default parameters , Non master slave configuration related parameters , Represents the number of connections to the database , When doing the main read service from the library , The setting value needs to be higher than the main library 
  
 Be careful :data Directory permissions should be 0700

Active standby switching :

 Stop the main warehouse 
pg_ctl promote  Perform a switch from the library , It was found after execution that recovery.conf Turn into recovery.done file 
mv recovery.done recovery.conf  Execute in the original master database , Modify the main database information 
 Start the original main library 

 

Synchronous mode :

PG Provides 5 It's a synchronous mode , from synchronous_commit Parameter control ;

  • off: For native WAL Logs can be committed without writing to disk , It's asynchronous mode , There is a risk of data loss ;
  • local: Whether there is a spare warehouse or not , You only need to ensure the of this machine WAL Just brush the log to disk ;
  • remote_write: Wait for the main library log to be refreshed to disk , At the same time, the log is transferred to the operating system cache of the standby database , You don't need to brush the disk to submit , Can't avoid operating system crash ;
  • on: If there is no spare warehouse , said WAL The log needs to be refreshed to disk before it can be submitted ; If there is a synchronous standby database (synchronous_standby_name Not empty ), You need to wait for the remote standby library to refresh to the disk , Only the master library can submit ;
  • remote_apply:PG Features of the higher version , After the standby database swipes the disk and the playback is successful , The transaction is marked as visible , Used for load balancing , Separation of reading and writing, etc ;

 

 

  • Master slave switching process
1. stay A do checkpoint, Three times 
postgres=# checkpoint ;


2. modify A Of pg_hba.conf and reload
 Comment on the following line 
#host all all 0.0.0.1/0 md5

 Reload pg_hba.conf
/data/postgres/pgsql/bin/pg_ctl reload -D /stage/data

3. stay A On kill The current user is connected 
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where usename<>'postgres';
pg_terminate_backend
----------------------
(0 rows)

4、 Stop standby B、D
 notes crontab
/data/postgres/pgsql/bin/pg_ctl stop -D /stage/data

5. Wait for the active and standby status to be consistent 
 How many times checkpoint;
select * from pg_stat_replication;

 Insert data into the original master table to verify consistency 

create table tbase_test09(id int,dt date);

INSERT INTO public.tbase_test09 VALUES (100,now());

# wait for A and C On xlog Agreement , The last one xlog Of md5sum The values of are exactly the same , Go on with the second 6 Step 

6. Stop host A

 notes crontab
/data/postgres/pgsql/bin/pg_ctl stop -D /stage/data

7. promote C For the host 
/data/postgres/pgsql/bin/pg_ctl promote -D /stage/data
 Check whether it can be accessed and read / write normally 

版权声明
本文为[Don't eat sour radish]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/09/20210909120916540y.html

随机推荐