RE: [EXTERNAL]pg_dump -Fd -j2 on standby in 9.6.6 - Mailing list pgsql-general

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.