Обсуждение: pg_dump -Fd -j2 on standby in 9.6.6
Hi, I downloaded the code from this github tag https://github.com/postgres/postgres/archive/REL9_6_8.tar.gz I unpacked the code on disk where gdb was looking for it /build/postgresql-9.6-9VrjLn/postgresql-9.6-9.6.8 Then I ran this oneliner which is a summary of my debug process. So, apparently pg_dump -Fd -j2 does not work in the case of pre-9.2 but it also doesn't work for standby servers. gdb /usr/lib/postgresql/9.6/bin/pg_dump -ex 'set confirm off' -ex 'set listsize 30' -ex 'set pagination off' -ex 'b 1131'-ex 'r -Fd -j2 -d TIMS_SOMEDB -h /var/run/postgresql/pg-prod-7 -f /tmp/TIMS_SOMEDB_fd.dmp' -ex 'l' -ex 'p AH->numWorkers' -ex 'p AH->remoteVersion' -ex 'p dopt->no_synchronized_snapshots' -ex 'q' 1131 else if (AH->numWorkers > 1 && 1116 * worker, we'll be passed dumpsnapshot == NULL, but AH->sync_snapshot_id 1117 * is already set (if the server can handle it) and we should use that. 1118 */ 1119 if (dumpsnapshot) 1120 AH->sync_snapshot_id = pg_strdup(dumpsnapshot); 1121 1122 if (AH->sync_snapshot_id) 1123 { 1124 PQExpBuffer query = createPQExpBuffer(); 1125 1126 appendPQExpBuffer(query, "SET TRANSACTION SNAPSHOT "); 1127 appendStringLiteralConn(query, AH->sync_snapshot_id, conn); 1128 ExecuteSqlStatement(AH, query->data); 1129 destroyPQExpBuffer(query); 1130 } 1131 else if (AH->numWorkers > 1 && 1132 AH->remoteVersion >= 90200 && 1133 !dopt->no_synchronized_snapshots) 1134 { 1135 if (AH->isStandby) 1136 exit_horribly(NULL, 1137 "Synchronized snapshots are not supported on standby servers.\n" 1138 "Run with --no-synchronized-snapshots instead if you do not need\n" 1139 "synchronized snapshots.\n"); 1140 1141 1142 AH->sync_snapshot_id = get_synchronized_snapshot(AH); 1143 } 1144 } 1145 $1 = 2 $2 = 90606 $3 = 0 I was expecting the code to be the same as the one in [1], but it seems to be different (I don't know why) I wonder why synchronized snapshots are not supported on standby servers. The code in [1] says they should work for standby servers. My master is 9.6.6 and my slave is 9.6.6 as well. Any ideas would be appreciated. Thank you, Stefan [1] https://github.com/postgres/postgres/blob/REL9_6_STABLE/src/bin/pg_dump/pg_dump.c#L689 Stefan Petrea System Engineer/DBA, Network Engineering stefan.petrea@tangoe.com tangoe.com This e-mail message, including any attachments, is for the sole use of the intended recipient of this message, and may containinformation that is confidential or legally protected. If you are not the intended recipient or have received thismessage in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments. Pleasenotify the sender immediately by return e-mail and permanently delete this message and any attachments. Tangoe makesno warranty that this e-mail or its attachments are error or virus free.
Just wanted to clarify what my question is. I tried this with both pg_dump 9.6.6 and 9.6.8.
What I'd like is to use pg_dump -Fd -j2 to generate dumps faster. But I get the error
pg_dump: Synchronized snapshots are not supported on standby servers.
Run with --no-synchronized-snapshots instead if you do not need
synchronized snapshots.
I could take these dumps on the master instead of the standby but that would degrade the performance.
That was part of the reasoning behind having standbys, so we could offload the backup cronjobs we have to them.
How would you approach this aspect of generating dumps faster?
Or, can pg_dump -Fd -j be made to work on standbys?
Also, does using --no-synchronized-snapshots mean that the dumps will be inconsistent?
Thank you,
Stefan
Stefan Petrea
System Engineer/DBA, Network Engineering
stefan.petrea@tangoe.com
tangoe.com
This e-mail message, including any attachments, is for the sole use of the intended recipient of this message, and may
containinformation that is confidential or legally protected. If you are not the intended recipient or have received
thismessage in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments.
Pleasenotify the sender immediately by return e-mail and permanently delete this message and any attachments. Tangoe
makesno warranty that this e-mail or its attachments are error or virus free.
-----Original Message-----
From: Stefan Petrea [mailto:Stefan.Petrea@tangoe.com]
Sent: Wednesday, March 28, 2018 8:03 AM
To: pgsql-general@postgresql.org
Subject: [EXTERNAL]pg_dump -Fd -j2 on standby in 9.6.6
Hi,
I downloaded the code from this github tag
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpostgres%2Fpostgres%2Farchive%2FREL9_6_8.tar.gz&data=01%7C01%7Cstefan.petrea%40tangoe.com%7C608018c65c68463cc42f08d594693951%7C3ba137049b66408a9fb9db51aba579e4%7C0&sdata=S3p52va81Sk1NfmGsVieXL3qyliDhY0G5adlKwzglWY%3D&reserved=0
I unpacked the code on disk where gdb was looking for it /build/postgresql-9.6-9VrjLn/postgresql-9.6-9.6.8
Then I ran this oneliner which is a summary of my debug process. So, apparently pg_dump -Fd -j2 does not work in the
caseof pre-9.2 but it also doesn't work for standby servers.
gdb /usr/lib/postgresql/9.6/bin/pg_dump -ex 'set confirm off' -ex 'set listsize 30' -ex 'set pagination off' -ex 'b
1131'-ex 'r -Fd -j2 -d TIMS_SOMEDB -h /var/run/postgresql/pg-prod-7 -f /tmp/TIMS_SOMEDB_fd.dmp' -ex 'l' -ex 'p
AH->numWorkers' -ex 'p AH->remoteVersion' -ex 'p dopt->no_synchronized_snapshots' -ex 'q'
1131 else if (AH->numWorkers > 1 &&
1116 * worker, we'll be passed dumpsnapshot == NULL, but AH->sync_snapshot_id
1117 * is already set (if the server can handle it) and we should use that.
1118 */
1119 if (dumpsnapshot)
1120 AH->sync_snapshot_id = pg_strdup(dumpsnapshot);
1121
1122 if (AH->sync_snapshot_id)
1123 {
1124 PQExpBuffer query = createPQExpBuffer();
1125
1126 appendPQExpBuffer(query, "SET TRANSACTION SNAPSHOT ");
1127 appendStringLiteralConn(query, AH->sync_snapshot_id, conn);
1128 ExecuteSqlStatement(AH, query->data);
1129 destroyPQExpBuffer(query);
1130 }
1131 else if (AH->numWorkers > 1 &&
1132 AH->remoteVersion >= 90200 &&
1133 !dopt->no_synchronized_snapshots)
1134 {
1135 if (AH->isStandby)
1136 exit_horribly(NULL,
1137 "Synchronized snapshots are not supported on standby servers.\n"
1138 "Run with --no-synchronized-snapshots instead if you do not need\n"
1139 "synchronized snapshots.\n");
1140
1141
1142 AH->sync_snapshot_id = get_synchronized_snapshot(AH);
1143 }
1144 }
1145
$1 = 2
$2 = 90606
$3 = 0
I was expecting the code to be the same as the one in [1], but it seems to be different (I don't know why) I wonder why
synchronizedsnapshots are not supported on standby servers. The code in [1] says they should work for standby servers.
My master is 9.6.6 and my slave is 9.6.6 as well.
Any ideas would be appreciated.
Thank you,
Stefan
[1]
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fpostgres%2Fpostgres%2Fblob%2FREL9_6_STABLE%2Fsrc%2Fbin%2Fpg_dump%2Fpg_dump.c%23L689&data=01%7C01%7Cstefan.petrea%40tangoe.com%7C608018c65c68463cc42f08d594693951%7C3ba137049b66408a9fb9db51aba579e4%7C0&sdata=jWEm%2B1Nhqa4His6Xj3p2Bxfx8pEfEuCHdrxRHfgwUs0%3D&reserved=0
Stefan Petrea
System Engineer/DBA, Network Engineering
stefan.petrea@tangoe.com
tangoe.com
This e-mail message, including any attachments, is for the sole use of the intended recipient of this message, and may
containinformation that is confidential or legally protected. If you are not the intended recipient or have received
thismessage in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments.
Pleasenotify the sender immediately by return e-mail and permanently delete this message and any attachments. Tangoe
makesno warranty that this e-mail or its attachments are error or virus free.
On 28 March 2018 07:02:30 CEST, Stefan Petrea <Stefan.Petrea@tangoe.com> wrote: >Hi, > > >I wonder why synchronized snapshots are not supported on standby >servers. > > > If you want to take backups using -Fd on a standby you should pause the reply-process. In this case you don' t need synchronizedsnapshots. Regards, Andreas. -- 2ndQuadrant - The PostgreSQL Support Company
Hi Andreas, Thank you for your suggestion about pausing/resuming the standby! I've tried running on the standby SELECT pg_xlog_replay_pause() Then running the dump, and it threw the same error I had seen before. Seeing that I wasn't able to do it, I just ran SELECT pg_xlog_replay_resume() On the slave. I've asked in the IRC channel as well and I've been told that in version 10 this would work on standbys. I've looked at the code [3] and indeed it does not have the check for standby anymore (as it did in the previous gdb code listing in my initial email). But.. upgrading to 10 would be quite a heavy thing for now, since it would involve many changes so I don't believe that would be feasible right now. [3] https://github.com/postgres/postgres/blob/REL_10_STABLE/src/bin/pg_dump/pg_dump.c#L1149 Stefan Petrea System Engineer/DBA, Network Engineering stefan.petrea@tangoe.com tangoe.com This e-mail message, including any attachments, is for the sole use of the intended recipient of this message, and may containinformation that is confidential or legally protected. If you are not the intended recipient or have received thismessage in error, you are not authorized to copy, distribute, or otherwise use this message or its attachments. Pleasenotify the sender immediately by return e-mail and permanently delete this message and any attachments. Tangoe makesno warranty that this e-mail or its attachments are error or virus free. -----Original Message----- From: Andreas Kretschmer [mailto:andreas@a-kretschmer.de] Sent: Wednesday, March 28, 2018 8:26 AM To: pgsql-general@lists.postgresql.org; Stefan Petrea <Stefan.Petrea@tangoe.com>; pgsql-general@postgresql.org Subject: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6 WARNING: The sender of this email could not be validated and may not match the person in the "From" field. This is potentiallya spam/phishing email! Contact IT if you need assistance. On 28 March 2018 07:02:30 CEST, Stefan Petrea <Stefan.Petrea@tangoe.com> wrote: >Hi, > > >I wonder why synchronized snapshots are not supported on standby >servers. > > > If you want to take backups using -Fd on a standby you should pause the reply-process. In this case you don' t need synchronizedsnapshots. Regards, Andreas. -- 2ndQuadrant - The PostgreSQL Support Company
Am 28.03.2018 um 09:46 schrieb Stefan Petrea: > Hi Andreas, > > Thank you for your suggestion about pausing/resuming the standby! > > I've tried running on the standby > SELECT pg_xlog_replay_pause() > Then running the dump, and it threw the same error I had seen before. run pg_dump with --no-synchronized-snapshots, it is save in this szenario. > This e-mail message, including any attachments, this is a public mailing list ... Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
On 03/28/2018 03:05 AM, Andreas Kretschmer wrote: [snip] > > This e-mail message, including any attachments, > > this is a public mailing list ... The intended recipient is the public mailing list, no? -- Angular momentum makes the world go 'round.
On 03/28/2018 03:05 AM, Andreas Kretschmer wrote:
[snip]> This e-mail message, including any attachments,
this is a public mailing list ...
The intended recipient is the public mailing list, no?
A mailing list doesn't really "receive" email - it is only a distribution mechanism. IANAL but basically adding boilerplate like that to mail sent to a public mailing list is pointless - there are no "intended recipients" but rather the message is being put into the public domain for anyone to consume, regardless of whether they are/were a member of the mailing list to which the email was addressed.
David J.