PostgreSQL: Snapshooter restore DB

If we need to restore a database for a period longer than 7 days, we can use a dump created with the external service http://SnapShooter.com .

To do this, you need to go to the page and log in to the service. https://app.snapshooter.com/login

After entering your login and password, you will be taken to the service dashboard page.

After logging into the SnapShooter service control panel, you should navigate to the section with backups that were made according to the schedule. To do this, you need to click on ‘Backup Jobs’ (1), then select the desired backup job, in the screenshot, it’s ‘crm-prod-db-ON-prod-postgresql-db,’ and click on ‘View’ (2).

On the opened page, at the bottom, we will see a list of backups that we can use for restoring.

Identify the backup you need and click the Manage button (1). In the screenshot, this is the backup from 2024-07-30 09:00:14.

After that, a page with the dump for the selected period will open. Here, we can download it (1), view instructions (2), or restore it to the current database (3).

In case we want to restore an existing backup to a database with the same name as the one from which the backup was made, we can use the ‘Restore’ button (3).

After that, we need to select the name of the database we want to restore.

"And press the ‘Start Restore’ button, which will initiate the restoration process to restore the database with the same name."

Preferably – Restoring from dump in new database

If we want to restore the dump to a database with a new name, for example, crm-prod-db_v2, we can do this by downloading the backup. To do so, click the ‘Download’ button (1) shown in the screenshot above. This will start the process of downloading the database archive to your local computer.

Don’t pay attention to the database size, this is a test database.

After the database archive is downloaded, we need to extract it. This can be done using the command gunzip crm-prod-db.sql.gz.

After that, we need to log in to our DigitalOcean account and create a new database where we want to restore the dump.

In the dump file, we need to change SELECT pg_catalog.set_config('search_path', '', false); to SELECT pg_catalog.set_config('search_path', 'public', false);.

Then, go to the ‘Users and Databases’ section (1). At the bottom of the list of databases, there is a field for adding a new database (2), "Enter the name of the new database and click ‘Save’. (For example crm-prod-db-v2)

After that, using the psql command-line utility, we enter the following command. And

psql -U crm-prod-user -d crm-prod-db-v2 -p 25060 -h prod-postgresql-db-do-user-8527978-0.j.db.ondigitalocean.com < crm-prod-db.sql

and press ENTER.

After that, enter the database password, which is stored in 1Password (Postgresql PROD login), press Enter again, and wait for the data transfer process from the dump to complete.

Enjoy.