Database - Backup and Recovery

database sql

Backup

Take a backup of the Database using pg admin in the custom format, in the data options select

• do not save owner
• do not save namespace
• do not save privileges

Restore

Select the db you want to restore and choose restore, select

• do not save owner
• do not save namespace
• do not save privileges

after sometime it should be done

[! Caution]
make sure you only restore one Database at a time or backup one Database at a time

IMPORTANT

if you have any database triggers, make sure you point to the right schema after you dump the Database

Backing up and Restoring between Versions

When you are making backups and restore between different versions of the postgres servers, you have to be very careful regarding this. As this might results in header error.

So this happened when i was doing a backup and restore between version 16 (local) and 15 production.

to restore this onto a 15 version you have to do the following:

• make a dump from the postgres 16 remote server
• make a restore of the file onto a postgres 15 version from the same backup host
• make a backup file from the 15 remote server
• make restore on the postgres 15 server remotely

USE IDENTICAL VERSIONS

Make sure to use the same version of postgres on development and production and test databases