PostgreSQL is a popular open-source relational database management system used for web applications, business intelligence, and other data-intensive applications. A critical aspect of managing a PostgreSQL database is ensuring data protection by having a backup and restore strategy in place.
In this article, we will provide a step-by-step guide on how to backup and restore a PostgreSQL database.
1. Backup Single Database
- Enter the following command to create a backup file:
pg_dump my_database > my_database.sql
Replace my_database with the name of the database to be backed up.
- Restore a single database from backup in PostgreSQL. Just use “psql” command to restore PostgreSQL database.
psql my_database < my_database.sql
2. Backup All Databases
- Backup all databases in PostgreSQL using
pg_dumpall > alldbs.sql
- Restore: all database backup using following command.
psql < alldbs.sql
3. Backup Single Table
- Backup: a single table named mytable from mydb database.
pg_dump -d mydb -t table_1 > mydb-table_1.sql
- Restore: single table backup to database. Make sure your backup file contains only single table backup which you want to restore.
psql mydb < mydb-table_1.sql
4. Compressed Backup
- Backup PostgreSQL database in compressed format. Use gzip command line utility to accept piped backup data and make archive file.
pg_dump mydb | gzip > mydb.sql.gz
- Restore database from compressed backup file directly. Here gunzip will extract backup file and send data to psql command via pipe interface.
gunzip -c mydb.sql.gz | psql mydb
5. Split Backup in Multiple Files and Restore
- Backup: PostgreSQL database and split backup in multiple files of specified size. It helps us to backup a large database and transfer to other host easily. As per below example it will split backup files of 100mb in size.
pg_dump mydb | split -b 100m – mydb.pql
- Restore: database backup from multiple splited backup files.
cat mydb.sql* | psql mydb
- Backup: database in compressed splited files of specified size.
pg_dump mydb | gzip | split -b 100m – mydb.sql.gz
- Restore: database from multiple files of compressed files.
cat mydb.sql.gz* | gunzip | psql mydb
In this article, we provided a step-by-step guide on how to backup and restore a PostgreSQL database using the pg_dump and psql command-line utilities. It is important to have a backup and restore strategy in place to ensure data protection and minimize the risk of data loss. By following this guide, you can ensure that your PostgreSQL database is backed up regularly and can be restored quickly and efficiently in the event of data loss.
por que me sale (bash: jampy db.bk: permised denied
Great post. Articles that have meaningful and insightful comments are more enjoyable, at least to me. It’s interesting to read what other people thought and how it relates to them or their clients, as their perspective could possibly help you in the future.
Nice blog help me lot. There i found an interesting article, giving brief description on restoring a single table from the backup in sql server. You may also have a look:
Hope! you also like this article.
Nice tips but I prefer to make PostgreSQL backup with the help of 3rd party tools like Postgresql-Backup http://postgresql-backup.com/
Great tool! Thanks!
Compressed Backup and Restore Database process helped me a lot. Thanks