1

We recently moved our database cluster from self-hosted to Digital Ocean Managed Postgres. We would like to rollback the entire database to a recent point in time.

The backups are available for up to a week

According to the docs we cannot directly restore the database from the backup:

When you restore from a backup, we create a new copy of your cluster’s primary node. You cannot restore directly into the primary node itself because this creates alternative timelines for the database that introduce unwarranted complexity in a managed service. By restoring into a new primary node, a single linear timeline history is preserved.

When the new cluster is restored, it is a parallel cluster (see image below), there does not seem to be any way to make it assume the connection parameters of the original cluster.

The only way forward we can see is to do a full export from the new parallel cluster and drop the original database and import from command line.

Is there another option here?

enter image description here

enter image description here

2
  • Why is it important to keep the connection parameters? Those can be changed for applications as a part of your restore process. Dec 1, 2019 at 19:36
  • Because they are stored on each app server accessing the DB, so each would need a release with the new params.
    – port5432
    Dec 1, 2019 at 23:00

2 Answers 2

1

Change the connection parameters for each application to point at the new database instance at the new point in time.

Fastest and least complexity on the database side. Adding a dump and restore may extend the restore time considerably. Avoids complexity and confusion with two points in time on the same instance. May be possible to keep the old instance for some time, to abort the restore.

More complexity on the application side, may need to configure or redeploy. Possibly not trivial, although should be possible, if say you were to switch database providers.

3
  • Is this standard practice these days? I would have expected we could restore directly from backups, overwriting the timeline history.
    – port5432
    Dec 2, 2019 at 14:41
  • If you were the sysadmin you could do that. In this case, your database hosting refuses to overwrite the instance. A different database host is fine, as long as the data is there. An opportunity to test renaming the connection (to their new service address, or a DNS CNAME you maintain). Dec 2, 2019 at 18:29
  • Heroku has hosted databases and it's possible to easily restore from a backup. Anyway, hopefully I won't need it again. :-)
    – port5432
    Dec 3, 2019 at 19:25
0

In the end I restored to another cluster, then dumped the DB locally and restored it. Not the best solution but I could see no other option.

Restore to new cluster

As per the DO user interface.

Export DB to local

pg_dump -d '<DO new cluster connection string>' -Fc > prod_digital_ocean.dmp

Reimport

pg_restore --no-acl --no-owner --clean -d '< DO old cluster connection string>' prod_digital_ocean.dmp

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .