Thread: pg_basedump compare with pg_export_snapshot()+pg_dump for full backup

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()+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