I'm to transfer a postgresql 9.3.10 DB (~55GB) from Azure VM to an AWS EC2. Being a novice, this question is about getting the process right. I've seen this post, and need to go a bit beyond that:
Is dumping the live production database on a busy server perfectly safe? Docs explain how pg_dump is non-blocking, so I'm inclined to think it is. In your experience, does
pg_dump
always go off without a hitch on production, or are there exceptions? My other (unpleasant) option is to shut down the server, creating a "hard" maintenance window.The docs mention using
--format
to control dump format.c
(custom) looks promising, but I found the description vague in terms of how to use it. Should I simply try thepg_dump
command like so:pg_dump -U postgres -Fc db_name > db.dump
? Any other practically useful flags would be of interest as well.A simple guide I've read recommends wrapping the whole restore into a single transaction:
psql -1 restored_database < backup_file
. That gives a binary outcome by failing in entirety if there are restoration errors. i) What could cause restoration errors? and ii) the restoration command I originally planned on using ispg_restore --verbose --clean --no-acl --no-owner -U myuser -d mydb latest.dump
. That looks right?
pg_dump -U postgres -Fc db_name > db.dump
- worked without a hitch, took ~15 mins, and I have a3.5G
output. So far so good.