Occasionally I need to sync latest data and schema from production to staging db server. What tools or techniques do you recommend if syncing speed is a priority? The sync doesn't need to be in realtime.

Some inputs:

  • Postgres version is 9.6
  • DB size is less than 50GB
  • A full dump and restore takes about one hour. I want to beat this.

The staging db server is used for QA purpose. Whenever new code is merged, the staging app as well as the staging db will be updated. New code is merged several times a day.

One solution I'm thinking about: use something similar to the checkpoint concept.

  1. Initially, restore a full dump to staging server.
  2. Whenever a new code is merged:

    2.1 Restore the staging db state to the latest check point
    2.2 Get the latest state from production
    2.3 Save a new checkpoint

Is it a good direction, and could I use the WAL as the "checkpoint" here? Thank you.

  • pg_basebackup is probably faster than a dump & restore
    – user1822
    Jan 18, 2019 at 8:15

2 Answers 2


Occasionally I need to sync latest data and schema from production to staging db server. What tools or techniques do you recommend if syncing speed is a priority? The sync doesn't need to be in realtime.

Well, normally in such a schemas you have to wipe the stating. You didn't say that explicitly but it's critical to your question. If you have people writing to staging and developing with staging then you can't just update it and assume that only the master wrote to it. In such a case your own method is to wipe and reload.

In order to that faster, you can simply, back up the data-directory or use pg_basebackup. Then when you need to reset the database,

  1. Shut down the database,
  2. Restore the data directory.
  3. Copy over the wal files from the archive into ./pg_wal
  4. Restart the server.

Note you'll have to keep around all of the WAL logs since your last base backup.

This is pretty well documented in the PostgreSQL 11 docs here,

  1. Shut down and blow away the old stage.
  2. Move the pre-staged directory to take the place of the blown away one.
  3. Startup that directory, have it run through recovery, and open. At this point, the people who need to use stage can go on their merry way, and the next steps happen in the background.
  4. Recreate the new pre-staged directory by copying over (or untarring) the most recent base backup.
  5. create a new recovery.conf in the new pre-staged directory to tell it how to run through recovery once you get back to step 3 again. e.g. restore_command from a wal archive
  6. Wait until someone needs a fresh version of stage, and then go to step 1.

Of course the first time through all of this, you have to start at step 4, because the pre-stage doesn't yet exist until the first time it is created. Moving an entire directory, if you move it within a filesystem, is usually very fast even if the directory contains a lot of data. So steps 1-3 can be done very fast, often less than 30 seconds. Step 4 is slow, but no one has to wait for it, it happens in the background. Of course if you want to start a new step 1 before previous step 4 finishes, you have to wait.

Once step 3 becomes annoyingly slow, it is time to take a new base backup so that less WAL needs to be replayed.

A variant of this is that not only do you pre-stage the server, but you actually have it up and running as a warm standby on a variant port number. Then all you need to do is stop the old stage server, promote the pre-staged one, change its port number back to the main one, and bounce it to pick up that change. It could take less than a second, if the promotion checkpoint is fast enough. You can have stage_a and stage_b, where you are using one of them while the other is being rebuilt.

Of course to do this, the stage server needs to have enough space for two full copies of the database. And you have to have a wal archive, although it would be possible to modify this to use streaming with a carefully chosen value of wal_keep_segments.

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.