PostgreSQL: Digital Ocean restore DB

To restore a PostgreSQL database on DigitalOcean, there is a tool that can recover data for a period of 7 days. In order to restore the data, we need to:

• Select the project in which we need to restore the database (in the image, it is snapshooter_test)

• After that, click on the desired cluster, which will take you to the management section of that cluster. (On the screenshot, it is: test-snapshooter-postgresql-14)

• Then, we need to click the blue "Actions"(1) button located in the upper right corner, and in the menu, select "Restore from backup" (2).

• After that, we can choose to create a new cluster from the most recent backup or from a specific period. If we want to restore from a specific period, we select the desired date and time and click "Restore to New Cluster." In the "Enter database name" field, we can either change it or leave it as default, based on your preference.

• After that, the process of creating the new cluster for the selected period will start. The status of this process is indicated by a blue progress bar located below the name of the new cluster.

• Once the process is complete, the new cluster will be created and ready for use.

• It will also be displayed in the list of databases for the selected project. It is worth noting that if a read-only node was connected to the server from which the backup was created, it will not be restored in the new cluster.

Enjoy

Additional information: If you are sure that the old cluster is no longer needed, it can be deleted to save costs on infrastructure maintenance. However, you should only delete it if you are 100% certain that it is no longer needed—this is crucial because the backups created in the previous period will also be deleted along with the cluster.

For the new cluster, if it will be used as the primary, a read-only node should be created.

Additionally, if you decide to use the new cluster as the primary, the password and username will remain the same as when connecting to the old one; only the hostname will change. In our example, the old host was test-snapshooter-postgresql-14-do-user-8527978-0.j.db.ondigitalocean.com, and it became test-snapshooter-postgresql-14-jul-26-backup-do-user-8527978-0.h.db.ondigitalocean.com or if you using internal connection hostname (private VPC network ) private-test-snapshooter-postgressql-14-do-user-8527978-0.j.db.ondigitalocean.com was changed on private-test-snapshooter-postgressql-14-jul-26-backup-do-user-8.h.db.ondigitalocean.com .

If we need to restore data from a period older than 7 days, we can use the Snapshooter service. Instruction how to restore database using the Snapshooter service