Backup & Restore Database in PostgreSQL (pg_dump,pg_restore)

Here i demostrate how to backup and restore dabatase in PostgreSQL

1)Backup data with pg_dump

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f 
"/usr/local/backup/10.70.0.61.backup" old_db

To list all of the available options of pg_dump , please issue following command.

pg_dump -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name

2) Restore data with pg_restore

pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v 
"/usr/local/backup/10.70.0.61.backup"

To list all of the available options of pg_restore , please issue following command.

pg_restore -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • http://virendra.me Virendra Rajput

    Thanks, for the examples. I used it to create a automated backup script that, runs every night and uploads the DB backup, to one of my Dropbox folders.

    https://github.com/bkvirendra/Dropbox_db_backup

  • http://programmertutorial.blogspot.com ketua rw

    Thank you for your nice article..

  • Rafael

    Hi, there!
    I tried this on Windows 7. I created some testDB, with one table and lines, after did pg_dump like above, then I deletd testDB, did pg_restore like the example,

    no warning or error messages on cmd when executing both the commands, but the database does not get restored, nothing happens

    what’s going on ??

    • Max

      you need to dropdb dbname first, then createdb dbname and only then pg_restore dbname

  • http://morris2gould.terapad.com/index.cfm?fa=contentNews.newsDetails&newsID=656221&from=list Stanley Plattsmier

    I found your blog in the “Related Blogs” section of another blog. You write excellent content. I have your blog bookmarked so I’ll definitely come back later. Keep up the amazing work!

  • Pingback: Solution Hacker - Common DBA jobs()

  • Matt

    How would you do large databased. Lets say your database is 5 gig. Here is what I do and it takes a long time and I worry some day I may not have enough space:
    1. Backup the database using pretty much the same as you do in step 1
    2. I want to change the schema in the backup so I vi this file which is about 5.5 gigs. This is done because I comment out a few things in the file and also put a commit at the bottom of the backup or it will not save when I restore.
    3. I then to the restore and all is fine

    So my question is with #2 above. The files are getting large and to change the schema takes a long time and soon I may not have enought disk space to do this. Any ideas on how to overcome this?