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.
1 | 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.
1 | 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
- Java Core Technology - Java RegEx, Java XML, Java I/O, Java Misc
- J2EE Frameworks - Hibernate, Spring 2.5, Spring MVC, Struts 1.x, Struts 2.x
- Build Tools - Maven, Archiva
- Unit Test - jUnit, TestNG
- Client Scripts - jQuery
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!
[...] Backup and restore database in postgresql [...]
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?
Recently, i wrote an article regarding incremental backup in PostgreSQL, may it help to you?
http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/