Tuesday, 8 September 2009

The best way to duplicate Postgres database

I had to copy Postgres database from a development server to the live server and I found this command line based method the most convenient:

$ pg_dump -h myhost.com -U username --clean --oids --verbose mydatabase 2>dump.log | psql -h livehost.com -U username mylivedatabase>>dump.log 2>&1
$ tail -f dump.log

I couldn't do this in a 'single transaction' mode because I don't have superuser privileges.
So I filtered all informative an error messages to follow the process. Database (schem + data) dump with redirection right into another databse proved to be the quickest as no disk space was required for dump files.

No comments: