There are 2 types of backups in PostgreSQL:
- Logical
- Physical
Logical backups in PostgreSQL can be taken using pg_dump. A backup taken using pg_dump can exist in both a text file (human-readable script file) format or a custom format. Read More
If you take a backup in plain format (.sql file), we can use psql to restore.
If you take a backup in a custom format (tar/dir/binary), the only option to restore is pg_restore.
pg_restore is 10x faster than psql.
When you do a backup using pg_dump, it will not apply any locks; hence, it will not cause any waits to the database traffic.
There are a few things to note before using pg_dump and planning your backup strategy:
- Backing up multiple databases is not possible using pg_dump. It can only backup one database at a time.
- pg_dump is not suitable for point-in-time recovery. To do a point-in-time recovery, WAL segments need to be replayed, but it will not be possible to replay transaction logs (WALs) after restoring backups taken using pg_dump.
- To ensure that all the data from the disk is readable and have no corrupted pages, it is important to have a periodical logical backup testing.
The following are the custom formats we can use:
- binary format (.bin)
- tar format (.tar)
- directory format (.dir)
This blog post contains 2 sections. The first section is for the custom format and the second section is for the plain format. The format of a backup can be specified using the command-line argument -F. If you want to perform the backup remotely, the command should include the additional command-line arguments, such as -h for hostname, -p for port number, and -U for username
Backing up a database in a custom format and restore to another database on a target server
1. Backup the conjuror database in custom format (binary) from the source server:
Syntax:
pg_dump -h source_server -p source_port -U username database_name -F format -f backup_location/dumpfile_name.bin
Example:
pg_dump -p 5432 -U postgres -d conjuror -Fc -f backups/conjuror.bin -v
2. Create the database to which you will restore the backup taken on the target server.
psql -c "CREATE DATABASE mymagicaldb"
3. Restore the backup of conjuror database to the new database created called mymagicaldb. What will happen is that all the objects of the conjuror database will be restored to mymagicaldb.
Syntax:
pg_restore -h target_server -p target_port -U username mymagicaldb /backups/conjuror.bin -v
Example:
pg_restore -h 172.xx.xx.xx -p 5432 -U postgres -d mymagicaldb /backups/conjuror.bin -v
Backing up a database in a plain format and restoring to another database on a target server
1. Backup the conjuror database in plain format from the source server:
Syntax:
pg_dump -h source_server -p source_port -U username -d database_name -F format -f backup_location/dumpfile_name.sql
Example:
pg_dump -p 5432 -U postgres -d conjuror -Fp -f backups/conjuror.sql -v
2. Create the database to which you will restore the backup taken on the target server.
psql -c "CREATE DATABASE mymagicaldb2"
3. Restore the backup of conjuror database to the new database created called mymagicaldb2. All the objects of the conjuror database will be restored to mymagicaldb2.
Syntax:
psql -h target_server -p target_port -U username -d mymagicaldb -f /backups/conjuror.sql -v
Example:
psql -h 172.xx.xx.xx -p 5432 -U postgres -d mymagicaldb2 -f /backups/conjuror.sql
Cheers!
