Getting pg_dump and pg_restore right for postgresl (9.3.10)

Question

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:

1. 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.

2. 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 the pg_dump command like so: pg_dump -U postgres -Fc db_name > db.dump? Any other practically useful flags would be of interest as well.

3. 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?

Answer 1

1. It's perfectly safe with especially with --serializable-deferrable however you're not answering the real question which is what do you do with data inserted while you're dumping and moving the database. The best way, in my opinion is the hard maintenance window.

2. I would use -c.

3. an inconstant backup or a failure to load because of a missing extension causes restoration errors, though I don't think you'll have that problem.

Since you know Azure supports 9.6 (they say so here), I would

1. back up the data directory
2. back up the database
3. upgrade to 9.6 locally
4. run it and make sure it works.

If it works, pg_dump and move move to Azure.