N Kaushik

How to do backup and restore in Postgresql

July 27, 2021

How to take backup in Postgresql:

Whenever I start to work on a new Database system, the first thing I check is how to take a backup and how to restore it. If your application catches bug, you can fix it and release one new version. But if your data is lost and you don’t have any backup, say goodbye to everything that you did (and maybe to your job as well :/ )

Postgresql provides one simple command line utility for backup/restore. It is called pg_dump. You can take backup of your database and restore it using pg_restore.

pg_dump can take consistent backups and even if the database is used concurrently, it makes consistent backup. pg_dump doesn’t block any other read-write operation.

We can save it as a .sql or in any other format.

Syntax of pg_dump:

pg_dump is defined as like below:

pg_dump [connection-option...] [option...] [dbname]

You can check all options available in the official doc

Example:

Let’s take a simple example of how to use pg_dump:

pg_dump db_name > db.sql

This will take a backup of db_name database to a file db.sql.

We can load this data to a database newdb using pg_restore as like below:

pg_restore -d newdb db.sql

Note that, pg_dump can dump only single database.


Subscribe to my Newsletter

© 2021, nkaushik