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?

  • I did a production env pg_dump -U postgres -Fc db_name > db.dump - worked without a hitch, took ~15 mins, and I have a 3.5G output. So far so good. Commented Sep 28, 2017 at 21:17

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

  • Regarding 1) yep, of course it's going to be a hard stop ultimately. I just want to practice right now. And I'm moving away from Azure to AWS actually. Commented Sep 28, 2017 at 21:18
  • Btw, quite honestly, although I wrote it (because it worked for me a few months ago), I'm still not certain I need the --no-acl and --no-owner flags in pg_restore. Could you shed light on the flags as well in your answer? Commented Sep 28, 2017 at 21:22
  • 1
    I have no idea what to tell you beyond what the docs already say. I don't think you need them. Certainly Azure doesn't document it learn.microsoft.com/en-us/azure/postgresql/… Commented Sep 28, 2017 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.