From: Sergey Burladyan <eshkinkot@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Questions about upgrade standby with rsync
Date: Tue, 06 Jun 2017 01:53:29 +0300
Message-ID: <87tw3uvyfq.fsf@seb.koffice.internal>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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 s=
tandby 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 alread=
y terminated, when last WAL switched.

For example:
=3D=3D=3D=3D standby log =3D=3D=3D
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:

=3D=3D=3D=3D master control data after shutdown =3D=3D=3D                  =
    =3D=3D=3D=3D standby control data after shutdown =3D=3D=3D
Latest checkpoint location:           0/12000020                 Latest che=
ckpoint location:           0/110402A8
Prior checkpoint location:            0/110402A8                 Prior chec=
kpoint location:            0/D067A00
Latest checkpoint's REDO location:    0/12000020                 Latest che=
ckpoint's REDO location:    0/F040E28
Latest checkpoint's TimeLineID:       1                          Latest che=
ckpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: on                         Latest che=
ckpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1267                     Latest che=
ckpoint's NextXID:          0/1267
Latest checkpoint's NextOID:          16393                      Latest che=
ckpoint's NextOID:          24576
Latest checkpoint's NextMultiXactId:  1                          Latest che=
ckpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0                          Latest che=
ckpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1254                       Latest che=
ckpoint's oldestXID:        1254
Latest checkpoint's oldestXID's DB:   1                          Latest che=
ckpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0                          Latest che=
ckpoint's oldestActiveXID:  1266
Time of latest checkpoint:            Thu Jun  1 16:09:32 2017   Time of la=
test checkpoint:            Thu Jun  1 16:09:30 2017
Minimum recovery ending location:     0/0                        Minimum re=
covery ending location:     0/12000000

As you can see, when standby was run =E2=80=94 it was consistent with maste=
r: "Minimum recovery ending location: 0/12000000"
but it last restartpoint is previous checkpoint (from master), because last=
 WAL (000000010000000000000012) from master
do not archived:
=3D=3D=3D=3D xlogdump =3D=3D=3D=3D
m/9.2/pg_xlog/000000010000000000000012:

[page:0, xlp_info:6, xlp_tli:1, xlp_pageaddr:0/12000000] XLP_LONG_HEADER XL=
P_BKP_REMOVABLE=20
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/113=
87BB8] 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 f=
rom pg 9.5,
is it possible to use it for upgrade from pg 9.2 to pg 9.4?

Thanks!

--=20
Sergey Burladyan


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin