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
