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

This article mainly explains "what is the function of pg_promote of PostgreSQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the role of pg_promote of PostgreSQL"?

In the previous version of PG 12, upgrading a standby database to a master database required executing commands on the standby host or triggering by generating a trigger file. In PG 12, the pg_promote function could be executed after the client connected to the database.

The following is illustrated by a simple example.

Build a stream replication environment

Referring to PostgreSQL DBA (31)-Backup&Recovery#4 (build flow replication), notice that the PG 12 recovery.conf file has been discarded and the related configuration information has been merged into the postgresql.conf file.

The following is the situation of preparing the database after building and creating the data table T1 in the main library.

Prepare the library

[pg12@localhost pg12db1] $pg_ctl startwaiting for server to start....2019-06-20 17 CST 04.361 CST [23158] LOG: starting PostgreSQL 12beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit2019-06-20 17 Ride 04.362 CST [23158] LOG: listening on IPv4 address "0.0.0.0" Port 54322019-06-20 17 LOG 04port 04.362 CST [23158] LOG: listening on IPv6 address ":", port 54322019-06-20 1704VR 04.365 CST [23158] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-06-20 174LOG 04.422 CST [23158] LOG: redirecting log output to logging collector process2019-06-20 1704LOG 04.422 CST: HINT: Future log output will appear in directory "pg_log". Doneserver started [pg12@localhost pg12db1] $psql-d testdbpsql (12beta1) Type "help" for help.testdb=# select count (*) from T1 politics psql: ERROR: relation "T1" does not existLINE 1: select count (*) from T1; ^ testdb=# select count (*) from T1; count-0 (1 row)

Next, connect to the standby library through the remote client and execute the function pg_promote to promote the standby library as the main library.

[pg12@localhost pg12db1] $psql-h 192.168.26.27-U pg12-d testdbpsql (12beta1) Type "help" for help.testdb=#-Wait for at most 30 seconds.testdb=# SELECT pg_promote (true, 30); pg_promote-t (1 row)

Log output

SELECT pg_promote (true, 30) "psql" 2019-06-20 17 CST,1/0,0,LOG,00000 1422.584 CST,23160,5d0b4c04.5a78,5,2019-06-20 17:04:04 CST,1/0,0,LOG,00000, "received promote request"2019-06-20 17 CST,1/0,0,LOG,00000 1422.584 CST,23167,5d0b4c04.5a7f,2,2019-06-20 17:04:04 CST,0,FATAL,57P01," terminating walreceiver process due to administrator command " "2019-06-20 17 invalid record length at 0/5016D48 1422.625 CST,23160,5d0b4c04.5a78,6,2019-06-20 17:04:04 CST,1/0,0,LOG,00000," invalid record length at 0/5016D48: wanted 24, got 0 "," 2019-06-20 17 14 invalid record length at 0/5016D48 22.625 CST,23160,5d0b4c04.5a78,7,2019-06-20 17:04:04 CST,1/0,0,LOG,00000, "redo done at 0/5016D10" , "2019-06-20 1714 last completed transaction was at log time 22.625 CST,23160,5d0b4c04.5a78,8,2019-06-20 17:04:04 CST,1/0,0,LOG,00000," last completed transaction was at log time 2019-06-20 1715 1422.649 CST,23160,5d0b4c04.5a78,9,2019-06-20 17:04:04 CST,1/0,0,LOG,00000, "selected new timeline ID: 2" , "2019-06-20 17 CST,0,LOG,00000 14 archive recovery complete 22.738 CST,23160,5d0b4c04.5a78,10,2019-06-20 17:04:04 CST,1/0,0,LOG,00000," archive recovery complete "," 2019-06-20 17 17 CST,23158,5d0b4c04.5a76,3,2019-20 17:04:04 CST,0,LOG,00000, "database system is ready to accept connections" "2019-06-20 17 archive command failed with exit code 1422 764 CST,23277,5d0b4e6e.5aed,1,2019-06-20 17:14:22 CST,0,FATAL,XX000," archive command failed with exit code 127 "," The failed archive command was: / home/pg12/archive.sh "," 2019-06-20 17 archive command failed with exit code 14 22 766 CST,23158,5d0b4c04.5a76,4,2019-06-20 17:04:04 CST,0,LOG,00000 "archiver process (PID 23277) exited with exit code 1", "2019-06-20 17 exited with exit code 15 exited with exit code 22.779 CST,23329,5d0b4eaa.5b21,1,2019-06-20 17:15:22 CST,0,FATAL,XX000," archive command failed with exit code 127 "," The failed archive command was: / home/pg12/archive.sh "," 2019-06-20 17 exited with exit code 15 exited with exit code 22.781 CST,23158,5d0b4c04.5a76,5 " 2019-06-20 17:04:04 CST,0,LOG,00000, "archiver process (PID 23329) exited with exit code 1", "" [pg12@localhost pg_log] $

Status in pg_control

[pg12@localhost pg12db1] $pg_controldata | grep stateDatabase cluster state: in production

As can be seen from the log-status, the standby library has been promoted to the primary library (the error prompt is that the archive command was not found).

Thank you for your reading, the above is the content of "what is the role of PostgreSQL pg_promote?" after the study of this article, I believe you have a deeper understanding of the role of PostgreSQL pg_promote, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.