2 min read
PostgreSQL is undoubtedly one of the most popular and efficient open-source relational database management system powering millions of applications.
These days a database backup mechanism is essential for any project onboard. Luckily PostgreSQL comes with built-in utilities for creating and restoring backups to make our life simpler.
PostgreSQL allows a great degree of flexibility for backup and restoration of data, In this article one by one we will go through the most used utilities.
The pg_dump
utility is used for backing up data of a single database or table in PostgreSQL.
pg_dump -U db_user -W -F t db_name > /path/to/your/file/dump_name.tar
Here we used the following options:
-W
will force pg_dump
to prompt for a password before connecting to the server.-F
is used to specify the format of the output file, which can be one of the following:
p
– plain-text SQL scriptc
– custom-format archived
– directory-format archivet
– tar-format archiveIn case you encounter the following error.
pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL: Peer authentication failed for user "postgres"
Please follow this guide - How To Fix – FATAL: Peer authentication failed for user “postgres” Error
Similarly, pg_restore
utility is used for restoring a database from a dump created by pg_dump
.
pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user
The -C
flag is for creating a database before restoring data into it.
A tar backup may cause problems while restoring the dump to a different version of PostgreSQL. Therefore I always recommend taking backups in-plane SQL format.
pg_dump -U db_user database_name > path/to/backup.sql
SQL files can be restored using psql
as follows.
psql -U db_user < path/to/backup.sql
Postgres also allow us to backup specific tables instead of the entire database.
pg_dump -U db_user -d db_name -t table_name > path/to/backup.sql
psql -U db_user -d db_name < path/to/backup.sql
We can also backup all the database belonging to a user using the pg_dumpall
utility.
pg_dumpall -U db_user > path/to/backup.sql
psql -U db_user < path/to/backup.sql
pg_dump -h host_name -p port_number db_name > path/to/backup.sql
psql -h host_name -p port_number db_name < path/to/backup.sql
pg_dump -U db_user -d db_name | gzip > path/to/backup.sql.gz
gunzip -c path/to/backup.sql.gz | psql -U db_user -d db_name
WEB DEVELOPMENT
Latest from djangocentral
2 min read
2 min read