illustrate
The main method of building pg12 stream replication is not much different from before. The change is that pg12 merges the files and the relevant configuration needs to be modified.
Main library configuration
Also, you need to configure the connection that accepts stream replication in the main library, modify the pg_hba.conf file, and add information from another backup library.
# TYPE DATABASE USER ADDRESS METHOD host replication all 192.168.7.180/24 trust
Add in file
listen_addresses = '*' max_wal_senders = 5 wal_level = hot_standby synchronous_standby_names = ''ANY 1(standby01,standby02)'
Library configuration
Perform the same operation in both backup libraries
Backup data:
pg12@dmdb01-> pg_basebackup -h 192.168.7.180 -U bill -F p -P -R -D /home/pg12/pgdata/bk_data/ -l backup191227
Modify the file and add:
primary_conninfo = 'application_name=standby01 user=bill passfile=''/home/pg12/.pgpass'' host=192.168.7.180 port=1921 sslmode=disable sslcompression=1 target_session_attrs=any'
Then you need to generate a file in the new $PGDATA directory.
Finally, start the database (you need to note that you need to use a new data directory to start), and then perform the same operation in another backup library.
Main library verification:
bill=# select * from pg_stat_replication; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | re play_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time -------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+-------------+-------------+-------------+--- ----------+-----------+-----------+------------+---------------+------------+------------------------------ 25632 | 16384 | bill | walreceiver | 192.168.7.193 | | 59454 | 2019-12-27 08:56:35.237088+08 | | streaming | 1E/72000060 | 1E/72000060 | 1E/72000060 | 1E /72000060 | | | | 0 | async | 2019-12-27 17:08:44.95124+08 27191 | 16384 | bill | walreceiver | 192.168.7.192 | | 10306 | 2019-12-27 08:59:33.259722+08 | | streaming | 1E/72000060 | 1E/72000060 | 1E/72000060 | 1E /72000060 | | | | 0 | async | 2019-12-27 17:08:43.24504+08 (2 rows)
At this point, the construction of the synchronous stream replication standby database is completed!
Main and backup switch
When the main library fails, we need to upgrade the backup library to the main library for read and write operations. Before pg12 we generally use two methods:
pg_ctl method: Execute the pg_ctl promote shell script on the backup host
Trigger file method: The trigger_file parameter of the library configuration file, and then create the trigger file on the library host.
pg12 has started to add a new pg_promote() function, allowing us to activate the backup library through SQL commands.
pg_promote() syntax:
pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)
Two parameters:
-
wait: Indicates whether to wait for the backup library to complete or return to success after waiting_seconds seconds. The default value is true. -
wait_seconds: Waiting time, unit seconds, default 60
Switch to example:
Close the main library and simulate the main library failure:
pg12@test180-> pg_ctl stop -m fast waiting for server to shut down.... done server stopped
Activate the backup library:
bill=# select pg_promote(true,60); pg_promote ------------ t (1 row)
verify:
pg12@dmdb02-> pg_controldata | grep 'Database cluster state' Database cluster state: in production
At this point, the activation of the backup library is completed. After the original master library is repaired, we can repeat the previous steps to use the original master library as a new backup library.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.