pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_upgrade + replica servers + rsync --size-only is unsafe
6+ messages / 2 participants
[nested] [flat]
* pg_upgrade + replica servers + rsync --size-only is unsafe
@ 2020-12-02 16:18 Rob Emery <re-pgsql@codeweavers.net>
0 siblings, 1 reply; 6+ messages in thread
From: Rob Emery @ 2020-12-02 16:18 UTC (permalink / raw)
To: pgsql-admin
Hiya,
We're pretty sure that we've just hit a scenario where the instructions for
pg_upgrade with standby servers aren't quite safe.
We were testing our upgrade process with a copy of our live cluster in a
lab environment and we found that using
rsync --verbose --archive --delete --hard-links --size-only
--no-inc-recursive /media/postgresql/data/main /media/postgresql/data/9.6
PGRETESTA02:/media/postgresql/data
Resulted in:
2020-12-02 14:49:11.513 GMT [20884-1] LOG: database system was shut
down in recovery at 2020-12-02 13:57:56 GMT
2020-12-02 14:49:11.513 GMT [20884-2] LOG: entering standby mode
2020-12-02 14:49:11.557 GMT [20884-3] LOG: consistent recovery state
reached at 19E/25000098
2020-12-02 14:49:11.557 GMT [20884-4] LOG: invalid record length at
19E/25000098: wanted 24, got 0
2020-12-02 14:49:11.559 GMT [20883-1] LOG: database system is ready
to accept read only connections
2020-12-02 14:49:11.593 GMT [20888-1] FATAL: database system
identifier differs between the primary and standby
2020-12-02 14:49:11.593 GMT [20888-2] DETAIL: The primary's
identifier is 6901669428825624285, the standby's identifier is
690161835164
1138930.
however if we don't use --size-only, then the process worked fine and we a
smooth upgrade.
I think the documentation should be updated to not have --size-only?
I've had a look and I can see concerns around this when it was originally
added:
https://www.postgresql.org/message-id/20150123030509.GJ3854@tamriel.snowman.net
Thanks,
Rob
--
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade + replica servers + rsync --size-only is unsafe
@ 2020-12-02 17:28 Stephen Frost <sfrost@snowman.net>
parent: Rob Emery <re-pgsql@codeweavers.net>
0 siblings, 1 reply; 6+ messages in thread
From: Stephen Frost @ 2020-12-02 17:28 UTC (permalink / raw)
To: Rob Emery <re-pgsql@codeweavers.net>; +Cc: pgsql-admin
Greetings,
* Rob Emery (re-pgsql@codeweavers.net) wrote:
> We're pretty sure that we've just hit a scenario where the instructions for
> pg_upgrade with standby servers aren't quite safe.
I've pretty much always felt those instructions should come with a
"expert use only" note.
> We were testing our upgrade process with a copy of our live cluster in a
> lab environment and we found that using
>
> rsync --verbose --archive --delete --hard-links --size-only
> --no-inc-recursive /media/postgresql/data/main /media/postgresql/data/9.6
> PGRETESTA02:/media/postgresql/data
>
> Resulted in:
>
> 2020-12-02 14:49:11.513 GMT [20884-1] LOG: database system was shut
> down in recovery at 2020-12-02 13:57:56 GMT
> 2020-12-02 14:49:11.513 GMT [20884-2] LOG: entering standby mode
> 2020-12-02 14:49:11.557 GMT [20884-3] LOG: consistent recovery state
> reached at 19E/25000098
> 2020-12-02 14:49:11.557 GMT [20884-4] LOG: invalid record length at
> 19E/25000098: wanted 24, got 0
> 2020-12-02 14:49:11.559 GMT [20883-1] LOG: database system is ready
> to accept read only connections
> 2020-12-02 14:49:11.593 GMT [20888-1] FATAL: database system
> identifier differs between the primary and standby
> 2020-12-02 14:49:11.593 GMT [20888-2] DETAIL: The primary's
> identifier is 6901669428825624285, the standby's identifier is
> 690161835164
> 1138930.
>
> however if we don't use --size-only, then the process worked fine and we a
> smooth upgrade.
This doesn't look like a --size-only issue though- how did you end up
with this when the pg_controldata file (where the system identifier is
pulled from) shouldn't even exist and won't be a hardlink to the
existing one on the old system since it's a new cluster, and therefore
should definitely be copied?
When you go to run the rsync to get the replica up to date you should
have a system that looks like this:
PRIMARY:
/srv/old_cluster
... lots of files
/srv/new_cluster
... lots of *new* files, including pg_controldata
... hardlinks to PG table/index files that aren't part of the catalog
REPLICA:
/srv/old_cluster
... lots of files
/srv/new_cluster
... entirely empty
Then you run the rsync, at the /srv level, and all those 'new files' in
/srv/new_cluster on the primary should get copied over to
/srv/new_cluster on the replica, while any files which are hard-linked
between old_cluster and new_cluster should end up as hard links on the
replica.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade + replica servers + rsync --size-only is unsafe
@ 2020-12-03 11:41 Rob Emery <re-pgsql@codeweavers.net>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 6+ messages in thread
From: Rob Emery @ 2020-12-03 11:41 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
Hello Stephen,
>Then you run the rsync, at the /srv level, and all those 'new files' in
>/srv/new_cluster on the primary should get copied over to
>/srv/new_cluster on the replica, while any files which are hard-linked
>between old_cluster and new_cluster should end up as hard links on the
>replica.
>
>Thanks,
Thanks very much for your reply.
We've re-ran our test upgrade this morning just to be certain and we
reproduced the same result once again, it appears that the issue is not
that the pg_controlfile is hardlinked, but that it exists at all.
The output of pg_controldata shows a different ID on the master and the
secondary as you suspected:
Master:
$ ./pg_controldata /var/lib/postgresql/9.6/main
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6901965895850147745
Database cluster state: in production
pg_control last modified: Thu 03 Dec 2020 10:11:21 GMT
Secondary:
$ ./pg_controldata /var/lib/postgresql/9.6/main
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6901966223447496344
Database cluster state: shut down
pg_control last modified: Thu 03 Dec 2020 09:54:00 GMT
The pg_control file does exist on the secondary 9.6 prior to the rsync
because it is created by the initdb command (which is step 4. of the
instructions - https://www.postgresql.org/docs/9.6/pgupgrade.html; we
verified that by running `/usr/lib/postgresql/9.6/bin/initdb
/var/lib/postgresql/999/main` and it creates a pg_control file).
The pg_control files are both exactly 8192 bytes; removing the --size-only
option resolves this and causes rsync to copy the control file across,
which can be confirmed by the --dry-run option:
$ rsync --verbose --dry-run --archive --delete --hard-links
--no-inc-recursive /media/postgresql/data/main /media/postgresql/data/9.6
PGRETESTA02:/media/postgresql/data
building file list ... done
... <snip>
9.6/main/global/pg_control
... <snip>
sent 252,838 bytes received 666 bytes 507,008.00 bytes/sec
total size is 12,061,021,649 speedup is 47,577.24 (DRY RUN)
So it seems like setting --size-only guarantees disaster in this flow?
However we are definitely confused as to why this isn't hit more often, as
it seems that this would always happen?
I've attached the following that might clarify:
- raw output of the rsync that we ran during step 12.a of our instructions
(master-rsync.txt)
- pg_controldata raw output from both master and secondary once the error
occurs (master-pgcontroldata.txt and secondary-pgcontroldata.txt)
- A copy of our internal instructions with the commands that we're using at
each step to build our script. We've based this on the instructions at
https://www.postgresql.org/docs/9.6/pgupgrade.html with some tweaks for our
configuration.
Some things to note about our setup which I'm adding for completeness as it
may have a bearing on the result we're seeing:
- the 9.5 database is currently in /media/postgresql/data/main and we are
moving to a /media/postgresql/data/main/$version structure to make it
easier for upgrades as part of this move (hence why the rsync command isn't
'symmetrical' in the arguments)
- additionally we symlink the /var/lib/postgresql/$version/main directory
off to that location.
- We're using Debian 9 and the postgresql apt packages
- As we are using Debian, the configuration for the postgresql DB's lives
in /etc/postgresql/$version/main
Many Thanks
Rob
On Wed, 2 Dec 2020 at 17:28, Stephen Frost <sfrost@snowman.net> wrote:
> Greetings,
>
> * Rob Emery (re-pgsql@codeweavers.net) wrote:
> > We're pretty sure that we've just hit a scenario where the instructions
> for
> > pg_upgrade with standby servers aren't quite safe.
>
> I've pretty much always felt those instructions should come with a
> "expert use only" note.
>
> > We were testing our upgrade process with a copy of our live cluster in a
> > lab environment and we found that using
> >
> > rsync --verbose --archive --delete --hard-links --size-only
> > --no-inc-recursive /media/postgresql/data/main /media/postgresql/data/9.6
> > PGRETESTA02:/media/postgresql/data
> >
> > Resulted in:
> >
> > 2020-12-02 14:49:11.513 GMT [20884-1] LOG: database system was shut
> > down in recovery at 2020-12-02 13:57:56 GMT
> > 2020-12-02 14:49:11.513 GMT [20884-2] LOG: entering standby mode
> > 2020-12-02 14:49:11.557 GMT [20884-3] LOG: consistent recovery state
> > reached at 19E/25000098
> > 2020-12-02 14:49:11.557 GMT [20884-4] LOG: invalid record length at
> > 19E/25000098: wanted 24, got 0
> > 2020-12-02 14:49:11.559 GMT [20883-1] LOG: database system is ready
> > to accept read only connections
> > 2020-12-02 14:49:11.593 GMT [20888-1] FATAL: database system
> > identifier differs between the primary and standby
> > 2020-12-02 14:49:11.593 GMT [20888-2] DETAIL: The primary's
> > identifier is 6901669428825624285, the standby's identifier is
> > 690161835164
> > 1138930.
> >
> > however if we don't use --size-only, then the process worked fine and we
> a
> > smooth upgrade.
>
> This doesn't look like a --size-only issue though- how did you end up
> with this when the pg_controldata file (where the system identifier is
> pulled from) shouldn't even exist and won't be a hardlink to the
> existing one on the old system since it's a new cluster, and therefore
> should definitely be copied?
>
> When you go to run the rsync to get the replica up to date you should
> have a system that looks like this:
>
> PRIMARY:
>
> /srv/old_cluster
> ... lots of files
> /srv/new_cluster
> ... lots of *new* files, including pg_controldata
> ... hardlinks to PG table/index files that aren't part of the catalog
>
> REPLICA:
>
> /srv/old_cluster
> ... lots of files
> /srv/new_cluster
> ... entirely empty
>
> Then you run the rsync, at the /srv level, and all those 'new files' in
> /srv/new_cluster on the primary should get copied over to
> /srv/new_cluster on the replica, while any files which are hard-linked
> between old_cluster and new_cluster should end up as hard links on the
> replica.
>
> Thanks,
>
> Stephen
>
--
Robert Emery
Infrastructure Director
01785 711633
<> Codeweavers
Phone: 0800 021 0888
Website: codeweavers.net
Barn 4, Dunston Business Village, ST18 9AB. Registered in England and
Wales No. 04092394 VAT registration no. 974 9705 63
--
Attachments:
master-pgcontroldata.txt (text/plain, 2.3K) download
./pg_controldata /var/lib/postgresql/9.6/main
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6901965895850147745
Database cluster state: in production
pg_control last modified: Thu 03 Dec 2020 10:11:21 GMT
Latest checkpoint location: 19E/260700C0
Prior checkpoint location: 19E/2606FFC8
Latest checkpoint's REDO location: 19E/26070088
Latest checkpoint's REDO WAL file: 000000010000019E00000026
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:421007004
Latest checkpoint's NextOID: 31273359
Latest checkpoint's NextMultiXactId: 2294
Latest checkpoint's NextMultiOffset: 4804
Latest checkpoint's oldestXID: 229505562
Latest checkpoint's oldestXID's DB: 16393
Latest checkpoint's oldestActiveXID: 421007004
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 16394
Latest checkpoint's oldestCommitTsXid:421005911
Latest checkpoint's newestCommitTsXid:421007003
Time of latest checkpoint: Thu 03 Dec 2020 10:11:21 GMT
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: on
max_connections setting: 1800
max_worker_processes setting: 8
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: on
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
run-sheet.txt (text/plain, 3.0K) download
Note: HAVE TO BE IN THIS STATE FIRST:
1. 9.5 main exists on both servers
2. 9.5 main replication is configured and working
3. pg_lsclusters needs to show a 9.6 main cluster
4. /etc/postgresql/9.6/main needs to contain the .conf files and the conf.d needs to be checked out
5. /var/lib/postgresql/9.6/main needs to be symlinked to /media/postgresql/data/9.6/main
6. /media/postgresql/data/9.6/main needs to be an “empty” postgresql db (see step 1)
we need a "working" PG9.6 (i.e. create a db)
1. sudo su postgres
2. /usr/lib/postgresql/9.6/bin/initdb /var/lib/postgresql/9.6/main;
3. rm /var/lib/postgresql/9.6/main/postgresql.conf /var/lib/postgresql/9.6/main/pg_hba.conf /var/lib/postgresql/9.6/main/pg_ident.conf
7. Stop pg 9.5 on the master (as yourself)
a. sudo systemctl stop postgresql@9.5-main
8. Run the pg_upgrade in check mode -- check it's OK (as postgres)
a. /usr/lib/postgresql/9.6/bin/pg_upgrade --check --old-bindir=/usr/lib/postgresql/9.5/bin/ --new-bindir=/usr/lib/postgresql/9.6/bin/ --old-datadir=/var/lib/postgresql/9.5/main --new-datadir=/var/lib/postgresql/9.6/main --link --old-options="-c config_file=/etc/postgresql/9.5/main/postgresql.conf" --new-options="-c config_file=/etc/postgresql/9.6/main/postgresql.conf"
9. Run the pg_upgrade in break-everything mode (as postgres)
a. /usr/lib/postgresql/9.6/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/9.5/bin/ --new-bindir=/usr/lib/postgresql/9.6/bin/ --old-datadir=/var/lib/postgresql/9.5/main --new-datadir=/var/lib/postgresql/9.6/main --link --old-options="-c config_file=/etc/postgresql/9.5/main/postgresql.conf" --new-options="-c config_file=/etc/postgresql/9.6/main/postgresql.conf"
10. Stop the standby servers & upgrade (as yourself)
a. sudo systemctl stop postgresql@9.5-main
11. Backup the recovery conf on the standby (as postgres)
a. cp /var/lib/postgresql/9.5/main/recovery.conf /tmp/recovery.conf
12. Sync the datafiles (on the master as postgres)
a. rsync --verbose --size-only --archive --delete --hard-links --no-inc-recursive /media/postgresql/data/main /media/postgresql/data/9.6 PGRETESTA02:/media/postgresql/data
13. Start PG9.6 on the master -- check error logs etc (as yourself)
a. sudo systemctl start postgresql@9.6-main
b. sudo tail -f /var/log/postgresql/postgresql-9.6-main.log
14. On the standby, copy the recovery.conf into position (as postgres)
a. mv /tmp/recovery.conf /var/lib/postgresql/9.6/main/recovery.conf
15. Start PG9.6 on the standby (as yourself)
a. sudo systemctl start postgresql@9.6-main
b. sudo tail -f /var/log/postgresql/postgresql-9.6-main.log
10. Run the statistics update thing on the master
a. ./analyze_new_cluster.sh
11. Drop the old cluster on the primary and then the standbys (as postgres)
a. pg_lsclusters
b. pg_dropcluster 9.5 main
12. reload systemd and cleanup (as yourself)
a. sudo systemctl daemon-reload
b. sudo systemctl status postgresql@*
c. sudo rm -R /media/postgresql/data/main
secondary-pgcontroldata.txt (text/plain, 2.3K) download
$ ./pg_controldata /var/lib/postgresql/9.6/main
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6901966223447496344
Database cluster state: shut down
pg_control last modified: Thu 03 Dec 2020 09:54:00 GMT
Latest checkpoint location: 0/14F1E58
Prior checkpoint location: 0/14F1BC0
Latest checkpoint's REDO location: 0/14F1E58
Latest checkpoint's REDO WAL file: 000000010000000000000001
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:550
Latest checkpoint's NextOID: 12408
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 543
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Thu 03 Dec 2020 09:54:00 GMT
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: minimal
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
* Re: pg_upgrade + replica servers + rsync --size-only is unsafe
@ 2020-12-03 13:10 Stephen Frost <sfrost@snowman.net>
parent: Rob Emery <re-pgsql@codeweavers.net>
0 siblings, 2 replies; 6+ messages in thread
From: Stephen Frost @ 2020-12-03 13:10 UTC (permalink / raw)
To: Rob Emery <re-pgsql@codeweavers.net>; +Cc: pgsql-admin
Greetings,
Please don’t top post on these lists. Thanks.
On Thu, Dec 3, 2020 at 06:42 Rob Emery <re-pgsql@codeweavers.net> wrote:
> Hello Stephen,
>
> >Then you run the rsync, at the /srv level, and all those 'new files' in
> >/srv/new_cluster on the primary should get copied over to
> >/srv/new_cluster on the replica, while any files which are hard-linked
> >between old_cluster and new_cluster should end up as hard links on the
> >replica.
> >
> >Thanks,
>
> Thanks very much for your reply.
>
> We've re-ran our test upgrade this morning just to be certain and we
> reproduced the same result once again, it appears that the issue is not
> that the pg_controlfile is hardlinked, but that it exists at all.
>
> The output of pg_controldata shows a different ID on the master and the
> secondary as you suspected:
>
> Master:
>
> $ ./pg_controldata /var/lib/postgresql/9.6/main
> pg_control version number: 960
> Catalog version number: 201608131
> Database system identifier: 6901965895850147745
> Database cluster state: in production
> pg_control last modified: Thu 03 Dec 2020 10:11:21 GMT
>
> Secondary:
>
> $ ./pg_controldata /var/lib/postgresql/9.6/main
> pg_control version number: 960
> Catalog version number: 201608131
> Database system identifier: 6901966223447496344
> Database cluster state: shut down
> pg_control last modified: Thu 03 Dec 2020 09:54:00 GMT
>
> The pg_control file does exist on the secondary 9.6 prior to the rsync
> because it is created by the initdb command (which is step 4. of the
> instructions - https://www.postgresql.org/docs/9.6/pgupgrade.html; we
> verified that by running `/usr/lib/postgresql/9.6/bin/initdb
> /var/lib/postgresql/999/main` and it creates a pg_control file).
>
The initdb is run only on the primary. Note that 10.b specifically says:
1.
Make sure the new standby data directories do not exist
Make sure the new standby data directories do not exist or are empty. If
initdb was run, delete the standby servers' new data directories.
Try your test again *without* running initdb on the replica.
Thanks,
Stephen
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade + replica servers + rsync --size-only is unsafe
@ 2020-12-03 13:14 Stephen Frost <sfrost@snowman.net>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 6+ messages in thread
From: Stephen Frost @ 2020-12-03 13:14 UTC (permalink / raw)
To: Rob Emery <re-pgsql@codeweavers.net>; +Cc: pgsql-admin
Greetings,
On Thu, Dec 3, 2020 at 08:10 Stephen Frost <sfrost@snowman.net> wrote:
> Greetings,
>
> Please don’t top post on these lists. Thanks.
>
Ah, bleh, you didn’t, not really anyway, just didn’t clear the history from
the bottom. Apologies for the admonishment, I blame myself for replying
from my phone. :)
Thanks,
Stephen
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_upgrade + replica servers + rsync --size-only is unsafe
@ 2020-12-03 13:50 Rob Emery <re-pgsql@codeweavers.net>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 6+ messages in thread
From: Rob Emery @ 2020-12-03 13:50 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
Aha!
That is exactly the problem; we were rsyncing over the top of an init'd db
and then using the rsync without the --size-only is cleaning up our mistake.
Thanks for the help.
-Rob
--
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-12-03 13:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-02 16:18 pg_upgrade + replica servers + rsync --size-only is unsafe Rob Emery <re-pgsql@codeweavers.net>
2020-12-02 17:28 ` Stephen Frost <sfrost@snowman.net>
2020-12-03 11:41 ` Rob Emery <re-pgsql@codeweavers.net>
2020-12-03 13:10 ` Stephen Frost <sfrost@snowman.net>
2020-12-03 13:14 ` Stephen Frost <sfrost@snowman.net>
2020-12-03 13:50 ` Rob Emery <re-pgsql@codeweavers.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox