Backup and Restore

This guide explains how to create a backup of your PostgreSQL database and restore it when needed.

NOTE

This guide assumes that the old PostgreSQL database server is accessible via SSH.

Choosing a Backup Format

Before starting, you should decide which format best suits your needs.

The Plain Text (.sql) format is a human-readable file containing SQL commands. It is best for smaller databases because you can open and edit the file if needed. You restore these backups using the psql command.

The Custom Archive (.dump) format is a compressed binary file. This is the preferred choice for large databases because it creates smaller files and allows for faster, parallel restoration. You must use the pg_restore command to process these files.

Create a Backup

To save a copy of your database, connect to your old PostgreSQL database server via SSH and use the pg_dump tool. Select the command that matches your chosen format and replace the placeholders with your own details.

For a Plain Text backup:

pg_dump -U your_username -d your_database_name -f backup.sql

The system will generate a backup.sql file in your current directory.

For a Compressed Custom backup:

pg_dump -U your_username -d your_database_name -Fc -f backup.dump

The system will generate a backup.dump file in your current directory.

Transfer the Backup File

First, copy the backup file (.sql or .dump) from the old PostgreSQL server to your local machine:

# Replace <Old Postgres Private IP address> with the private IP of the old server
# Replace <USER> with the server user (Ex: `ec2-user` for Amazon Linux, `admin` for Debian etc.)
# Use the correct file extension (.sql or .dump)
scp <USER>@<Old Postgres Private IP address>:/home/<USER>/backup.sql ./

Next, transfer the backup file from your local machine to the new PostgreSQL server:

# Replace <New Postgres Private IP address> with the private IP of the new server
# Replace <USER> with the server user (Ex: `ec2-user` for Amazon Linux, `admin` for Debian etc.)
# Use the correct file extension (.sql or .dump)
scp backup.sql <USER>@<New Postgres Private IP address>:/home/<USER>/

Prepare for Backup Restoration

The backup.sql file assumes that the database (your_database_name) and the user (your_username) already exist on the new server.

Refer to this guide to create the required database and user

Restore a Database

To restore the database from the backup, connect to your new PostgreSQL database server via SSH and run the command that matches your file format.

To restore from a .sql file:

psql -U your_username -d your_database_name -f backup.sql

To restore from a .dump file:

pg_restore -U your_username -d your_database_name -c backup.dump

Once the process finishes, your database will be populated with all the tables and records that were present when the backup was created.