Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "PostgreSQL HA environment analysis". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

PostgreSQL HA environment built based on streaming replication, after the Old Standby node is upgraded to the New Master node, the timeline will switch to the new timeline, such as from n to n + 1. The timeline of the Old Master node is still the original timeline, such as still n. By using pg_rewind tool, you can make the original "intact" Old Master node become the New Standby node.

illustration

As shown below:

After the execution of the active/standby switch, the timeline of the New Master node is switched to n + 1, and pg_rewind enables the Old Master to synchronize with the New Master at the bifurcation point and become the New Standby node.

measured

New Master

After switching to New Master, execute the following SQL:

testdb=# create table t_new(id int,flag varchar(40));CREATE TABLEtestdb=# insert into t_new select c,'flag'||c from generate_series(1,1000000) as c;INSERT 0 1000000testdb=#

Old Master

Before executing pg_rewind, restart the main library and execute the following SQL

testdb=# create table t_fork(id int,flag varchar(40));CREATE TABLEtestdb=# insert into t_fork select c,'flag'||c from generate_series(1,1000000) as c;INSERT 0 1000000testdb=#

Execute pg_rewind

[xdb@localhost testdb]$ cp /data/archivelog/* ./ pg_wal[xdb@localhost testdb]$ pg_rewind --target-pgdata=$PGDATA --source-server="host=192.168.26.25 port=5432 dbname=testdb" --progressconnected to serverservers diverged at WAL location 0/B41F12B8 on timeline 23rewinding from last common checkpoint at 0/AFCF99E0 on timeline 23reading source file listreading target file listreading WAL in targetneed to copy 360 MB (total source directory size is 501 MB)369312/369312 kB (100%) copiedcreating backup label and updating control filesyncing target data directoryDone!

Configure the recovery.conf file

[xdb@localhost testdb]$ mv recovery.done recovery.conf[xdb@localhost testdb]$ vim recovery.conf [xdb@localhost testdb]$ cat recovery.conf standby_mode = 'on'primary_conninfo = 'user=replicator password=replicator host=192.168.26.26 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'restore_command = 'cp /data/archivelog/%f %p'

restart the database

[xdb@localhost testdb]$ pg_ctl startwaiting for server to start.... 2019-03-28 12:39:31.918 CST [1961] LOG: listening on IPv4 address "0.0.0.0", port 54322019-03-28 12:39:31.918 CST [1961] LOG: listening on IPv6 address "::", port 54322019-03-28 12:39:31.920 CST [1961] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"2019-03-28 12:39:31.970 CST [1961] LOG: redirecting log output to logging collector process2019-03-28 12:39:31.970 CST [1961] HINT: Future log output will appear in directory "pg_log".... doneserver started

Synchronized with New Master data, and t_fork data table on original TL disappeared.

testdb=# select count(*) from t_new; count --------- 1000000(1 row)testdb=# select count(*) from t_old; count --------- 1000000(1 row)testdb=# select count(*) from t_fork;ERROR: relation "t_fork" does not existLINE 1: select count(*) from t_fork; ^testdb=#"PostgreSQL HA environment analysis" content introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.