PostgreSQL Databases Backup & Restore with pg_dump command

February 11th, 2011 by Bhagwan Dass

Make sure you should conncete to your server via ssh command line.
Login as super user type the following command.

[root@linux10 ~]#su – postgres

Get list of database(s) to backup.

[postgres@linux10 ~$]#psql -l

List of databases

Name | Owner | Encoding | Collation | Ctype | Access privileges


postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |

mine | mine | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |

template0| postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres


template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres

: postgres=CTc/postgres

(4 rows)




Make a backup using pg_dump, pg_dump is a utility for backing up a PostgreSQL database. It dumps only one database at a time.

Dump a mine database:-

[postgres@linux10 ~$]#pg_dump mine > /opt/mine.out

To restore a mine database:-

[postgres@linux10 ~$]#psql -d mine -f mine.out

Or, If you have new server first create database then apply command:-

[postgres@linux10 ~$]#createdb mine
[postgres@linux10 ~$]#psql mine
[postgres@linux10 ~$]#psql -d mine -f mine.out

Second option is use to pg_dumpall command.It dumps (backs up) each database, and preserves cluster-wide data such as users and groups. You can use it as follows:-

[postgres@linux10 ~$]#pg_dumpall > /opt/all.db.out

To restore backup use the following command:

[postgres@linux10 ~$]#psql -f all.db.out postgres

Leave a Reply

  • © 2004-2015 Special Work & Technology Limited