How to restore multiple PostgreSQL databases on a single instance to the same point in time?

[Alon Herbst]

I have 2 separate PostgreSQL databases in Google Cloud SQL. The databases should be separate due to security concerns, but they can reside in the same instance. When restoring from a backup, it is important for me to have the databases restored to the same point in time, due to data relations.

I tried to understand how the backup works. I tried to research the backup documentation and the restore one, but found no clue.

Can I be sure that restoring an instance would bring all its databases to the same point in time?

[Omair (Cloud Platform Support)]

If you restore a Cloud SQL instance backup, it will restore all the databases in that instance to the point when backup was taken. One thing to note here is that if you restore the backup in the same instance it will overwrite the current data with the one in backup [1].

You can see the General tips about performing a restore at [2].

[1]https://cloud.google.com/sql/docs/postgres/backup-recovery/restoring#restorebackups-same-instance

[2] https://cloud.google.com/sql/docs/postgres/backup-recovery/restore#tips-restore

[Alon Herbst]

Can you explain which kind of backup is used? Is it pg_dump like / file system backup / something else?
This will help to understand how the promise to restore all databases in the instance to the exact same point in time works.

[Aref Amiri (Cloud Platform Support)]

To avoid operation disruption, the backup and recovery is done using file-system backup.

[Alon Herbst]

Thank you for the answer Aref.
Can you give more technical details on how the backup and restore are done? Which PostgreSQL files? And how is the snapshot taken without service interruption?

[diogoa...@google.com]

The Postgres file system level backup technical details can be verified here. However, please note that the internal technical details, for instance, how the Cloud SQL Postgres managed database service backups are performed, are only available to the Cloud SQL engineering team. That way, they can guarantee that the service will remain reliable and secure.