WebFaction
Community site: login faq
1
3

How can I use the command line to dump my postgresql database to a file and later restore it in-place (as in overwrite the existing data)? This is for either backup purposes or for copying between my production and staging servers for testing.

I can't use the django management tools, such as dumpdata/loaddata because its difficult to clear out the database completely. (flush calls the post-syncdb routines which add data into the otherwise empty db) Also copying over table relationships can be problematic.

When I attempt to issue standard pg_dump / pg_restore commands I can't just drop the database and re-create it from the command line. I run into permissions issues since the database and public schema are owned by a superuser, not my db user.

asked 15 Sep '11, 01:53

Cole
43117
accept rate: 0%

edited 15 Sep '11, 02:29


Our docs on this are here,

http://docs.webfaction.com/user-guide/databases.html#export

Personally I use PHPpgadmin unless its a very large DB.

permanent link

answered 15 Sep '11, 02:04

johns
5.4k312
accept rate: 23%

edited 15 Sep '11, 02:04

Thanks. This gets me 90% of the way there. Its a bit of a challenge though to overwrite existing data. I clarified my post to reflect this aspect.

(15 Sep '11, 02:30) Cole

After some trial and error I found the following commands worked for me. Please let me know if there's anything wrong with this approach.


To dump and restore my database I used these commands:

pg_dump -U db_username -Fc db_name > dump.db
pg_restore -U db_username -n public -c -1 -d db_name dump.db
Pretty standard use of pg_dump. The trick is reading the archive back in correctly with pg_restore. The combination of "-n public" and "-c" clears all existing tables in the public schema before restoring the tables and data. Without "-n public," pg_restore will attempt to clear the whole db, including the public namespace which is owned by the superuser, not me. "-1" ensures that the restore takes place as a single transaction, and any error will cancel the whole operation.


For copying over the database between servers, I used the following command:

pg_dump -U db_username -Fc db_name | pg_restore -U staging_db_username -n public -c -1 -O -d staging_db_name
Basically its the previous two commands piped together using different databases. The one difference is the use of the "-O" command. Without it pg_restore will try restore the ownership of each table to the user that created it. In this case, since the tables are being copied to a different database, you don't want to copy over the ownership. -O strips that out.


The last thing I did was setup a .pgpass file so that my cron job could execute the commands above without being prompted for a password.

permanent link

answered 15 Sep '11, 02:17

Cole
43117
accept rate: 0%

There is nothing wrong with that method. Thank you for sharing the results with the community.

(15 Sep '11, 02:27) johns

@Cole - Thanks for the great post!

In my Django website, I installed the app "django-dbbackup", and was hoping it would be straightforward to FTP the dump of my database from my development machine (my MacBook Pro) to my WebFaction account, and use python manage.py dbrestore, but it gave me errors.

I believe it was the hint you gave about adding the flags -n public -c -1 were essential.

I did have an unrelated hiccup in my procedure. I discovered that my WebFaction postgres database didn't have all in indexes and contraints as my MacBook database. I had to manually add the missing indexes and constraints before the pg_restore command would execute without errors.

permanent link

answered 19 May '14, 18:37

stefan0
31138
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×909
×109
×36

question asked: 15 Sep '11, 01:53

question was seen: 42,959 times

last updated: 19 May '14, 18:37

WEBFACTION
REACH US
SUPPORT
AFFILIATE PROGRAM
LEGAL
© COPYRIGHT 2003-2019 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM