Hello, all!
I have problem with upgrading standby via rsync.
Documentation https://www.postgresql.org/docs/9.5/static/pgupgrade.html say:
> Verify that the "Latest checkpoint location" values match in all clusters.
But it is impossible for "WAL archive only" standby, if I not mistaken.
Standby can make restartpoint only at master checkpoint location, because standby cannot write WALs.
When standby use only WAL archive, without streaming replication, last WAL from master with shutdown checkpoint
record do not archived. I think may be because WAL archiving process already terminated, when last WAL switched.
For example:
==== standby log ===
2017-06-01 13:09:25 GMT LOG: recovery restart point at 0/3000790
2017-06-01 13:09:26 GMT LOG: recovery restart point at 0/603FB10
2017-06-01 13:09:27 GMT LOG: recovery restart point at 0/903ED28
2017-06-01 13:09:33 GMT LOG: recovery restart point at 0/C03EFF0
2017-06-01 13:09:38 GMT LOG: recovery restart point at 0/F040E28
now stop master and then standby:
==== master control data after shutdown === ==== standby control data after shutdown ===
Latest checkpoint location: 0/12000020 Latest checkpoint location: 0/110402A8
Prior checkpoint location: 0/110402A8 Prior checkpoint location: 0/D067A00
Latest checkpoint's REDO location: 0/12000020 Latest checkpoint's REDO location: 0/F040E28
Latest checkpoint's TimeLineID: 1 Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: on Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1267 Latest checkpoint's NextXID: 0/1267
Latest checkpoint's NextOID: 16393 Latest checkpoint's NextOID: 24576
Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1254 Latest checkpoint's oldestXID: 1254
Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestActiveXID: 1266
Time of latest checkpoint: Thu Jun 1 16:09:32 2017 Time of latest checkpoint: Thu Jun 1
16:09:302017
Minimum recovery ending location: 0/0 Minimum recovery ending location: 0/12000000
As you can see, when standby was run — it was consistent with master: "Minimum recovery ending location: 0/12000000"
but it last restartpoint is previous checkpoint (from master), because last WAL (000000010000000000000012) from master
do not archived:
==== xlogdump ====
m/9.2/pg_xlog/000000010000000000000012:
[page:0, xlp_info:6, xlp_tli:1, xlp_pageaddr:0/12000000] XLP_LONG_HEADER XLP_BKP_REMOVABLE
Unexpected page info flags 0006 at offset 0
[cur:0/12000020, xid:0, rmid:0(XLOG), len/tot_len:64/96, info:0, prev:0/11387BB8] checkpoint: redo
0/12000020; tli 1; nextxid 1267; nextoid 16393; nextmulti 1; nextoffset 0; shutdown at 2017-06-01
16:09:32 MSK
ReadRecord: record with zero len at 0/12000080
This WAL file is only at master pg_xlog, and not in WAL archive.
And my second question, this algorithm with rsync described only starting from pg 9.5,
is it possible to use it for upgrade from pg 9.2 to pg 9.4?
Thanks!
--
Sergey Burladyan