[ADMIN] Questions about upgrade standby with rsync - Mailing list pgsql-admin

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