Friday, August 07, 2009

Backing Up Postgresql

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

Restore: $psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

Backup a single postgres database

This example will backup wp database that belongs to user smartgeek, to the file mydb.sql

$ pg_dump -U smartgeek wp -f mydb.sql

It prompts for password, after authentication mydb.sql got created with create table, alter table and copy commands for all the tables in the wp database.

Backup all postgres databases

To backup all databases, list out all the available databases as shown below.

Login as postgres / psql user:

$ su postgres

List the databases:

$ psql -l

Backup all postgres databases using pg_dumpall:

You can backup all the databases using pg_dumpall command.

$ pg_dumpall > alldb.sql

Backup a specific postgres table

$ pg_dump –table products -U smartgeek shop -f onlytable.sql

To backup a specific table, use the -table TABLENAME option in the pg_dump command. If there are same table names in different schema then use the -schema SCHEMANAME option.

Restore a postgres database

$ psql -U smartgeek -d wp_devel -f mydb.sql

This restores the dumped database to the wp_devel database.

Backup a local postgres database and restore to remote server using single command:

$ pg_dump dbname | psql -h hostname dbname

Restore all the postgres databases

$ su postgres

$ psql -f alldb.sql

Restore a single postgres table

The following psql command installs the mytable in the smartgeek database.

$ psql -f mytable.sql smartgeek

No comments: