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
[email protected]
tangoe.com
This e-mail message, including any attachments, is for the sole use of the
intended recipient of this message, and may contain information that is
confidential or legally protected. If you are not the intended recipient or
have received this message in error, you are not authorized to copy,
distribute, or otherwise use this message or its attachments. Please notify the
sender immediately by return e-mail and permanently delete this message and any
attachments. Tangoe makes no warranty that this e-mail or its attachments are
error or virus free.
-----Original Message-----
From: Stefan Petrea [mailto:[email protected]]
Sent: Wednesday, March 28, 2018 8:03 AM
To: [email protected]
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 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://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
[email protected]
tangoe.com
This e-mail message, including any attachments, is for the sole use of the
intended recipient of this message, and may contain information that is
confidential or legally protected. If you are not the intended recipient or
have received this message in error, you are not authorized to copy,
distribute, or otherwise use this message or its attachments. Please notify the
sender immediately by return e-mail and permanently delete this message and any
attachments. Tangoe makes no warranty that this e-mail or its attachments are
error or virus free.
