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