Postgresql backup and restore

When doing some development, we need a fast backup and restore for database data in postgresql server.

The package provide an easy way to do the backup and restore in 1 command.

You only need the username and password + the database name to be working on.

Here are the backup command signature :

Backup : $pg_dump -U {user-name} {source_db} -f {dumpfile.sql}

Restore: $psql -U {user-name} -d {destination_db} -f {dumpfile.sql}

So this command is straighforward. I dump a 1000 records in 5 seconds.

Also note that the privilege and db owner is follow in the dump file. You must prepare the exact username and database, with no table in it.
This is because the generated dump database not include a drop table command.


But how if we want to backup all database ? Of course we can do it to.

Backup all postgres databases :

We can backup all databases in postgres using pg_dumpall command .

To do the backup run this command :

$pg_dumpall > all.sql

We also can verify if all database is backed up using a grep command :

$grep "^[\]connect" all.sql
\connect blog
\connect facebook
\connect mytweet

What if we want to backup a specific postgres table ? hell yeah you can .

Backup a specific postgres table

The command are :

$pg_dump --table production -U acongbebo -f onlyproduction.sql

Postgresql is a powerfull database you can imagine. support for GIS database already included.

Posted in: postgresql