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_dumpalways 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
--formatto control dump format.
c(custom) looks promising, but I found the description vague in terms of how to use it. Should I simply try the
pg_dumpcommand 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 is
pg_restore --verbose --clean --no-acl --no-owner -U myuser -d mydb latest.dump. That looks right?