Disabling WAL archiving during pg_restore?

Question

Let's say I made a quick backup dump of my Postgres 9.3 DB through pg_dump before doing a large destructive migration and I discovered I want to undo it. No writes were performed against the DB in the meantime.

Say I run pg_restore -c -d mydb < foo.dump to load the dump back into the db. Assuming I have WAL-E set up to archive every 16mb of WAL, do I need to turn off archive_mode before performing the restore? It would not be super useful for me to archive the xlog as I'm writing the dump back into the DB, since I already have perfectly valid base backups and WAL segments archived for before the dump. Also there are serious consequences to performing xlog shipping as I'm restoring the dump, which get worse with the size of the dump.

Do you end up disabling archiving before a restore? Do you do anything else to speed things up? There's a discussion of restore performance in this post, but it doesn't cover archiving at all, unless I missed something.

Answer 1

You can't really turn WAL archiving on and off like that. WAL replay requires continuity.

If you turned WAL archiving off, then made changes, then turned it back on, the new WAL generated after turning it off and on again would be useless. They could not be applied to the DB, and you'll have to make a new base backup before you can resume WAL replay / PITR.

If you turn xlog shipping off during a restore, you'll want to purge your old base backup and WAL archives, then create a new base backup before resuming WAL shipping.