pg_basedump compare with pg_export_snapshot()+pg_dump for full backup

From: Fan Liu <fan(dot)liu(at)ericsson(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: pg_basedump compare with pg_export_snapshot()+pg_dump for full backup
Date: 2019-09-18 11:46:42
Message-ID: HE1PR0701MB2569AA72617029F8986352589E8E0@HE1PR0701MB2569.eurprd07.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I have some question related with create a full backup.
PostgreSQL version 10.10.

My customer request to create a full backup at the backup start time which is when the backup triggered.

We use to use pg_basebackup to create a full backup, but they recommend that to use pg_export_snapshot()<https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION-TABLE>+pg_dump.

So, I am looking for support to help me understand the PROs and CONs about them.

For pg_export_snapshot() + pg_dump, it meant that we need create snapshots for all DBs in a PostgreSQL server, then use pg_dump with snapshot id to get backups. Then pack them into one tar ball as a full backup.
But I am worries that it looks like no guarantee for consistence between DBs. Because those snapshots will still be taken at different time. I may try to create them via serval connections. But still I can't guarantee they create at same time.
If there is complexes dependency between DBs, I afraid that if inconsistence issue happen, I am not able to recovery from a backup.
If my understanding wrongly, please help to correct me.

For pg_basebackup, it does not have consistence problem between DBs. But it can't create a backup at backup start time. What I can do is create a reconry.conf and set recovery_target= 'immediate' . It stop at first consistence point which is the backup end time.
But I don't know how far will this consistence point is from the backup start time. Is there any way I can check this?
And for pg_basebackup, will checkpoint=fast help here? Which able to create a consistent point as early as possible?

Last, if above two method not work , do you see method which I could create a full backup close to the backup start time as much as possible and no consistence problems?

BRs,
Fan Liu

Browse pgsql-general by date

  From Date Subject
Next Message Matthias Apitz 2019-09-18 13:11:06 Re: Installed PostgreSQL-11 in Ubuntu 18.04.02 Server Edition: No existing local cluster is suitable as a default target
Previous Message Stephen Frost 2019-09-18 11:44:47 Re: pgbackrest restore to new location?