Postgresql – Fastest tool or technique for occasionally syncing Postgres 9.6 databases

postgresql

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.

Initially, restore a full dump to staging server.
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.

Best Answer

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,

Shut down the database,
Restore the data directory.
Copy over the wal files from the archive into ./pg_wal
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,

Related Solutions

Postgres 9.6 – How to Tune for One-Time Import

I found that the following changes to postgres.conf yielded the best results so far:

archive_mode            = off       # old: on
autovacuum              = off       # old: on
commit_delay            = 100000    # old: 0
maintenance_work_mem    = 1GB       # old: 512MB
max_wal_size            = 10GB      # old: 1GB
synchronous_commit      = off       # old: on
wal_level               = minimal   # old: archive
work_mem                = 1GB       # old: 8MB

Also I had quite trouble getting the most out of a single pgloader invocation to migrate all the table. This would have been the most desired way, because pgloader that way also re-creates all the FK between the imported tables.

But the problems I was facing (pgloader hanging, not achieving the performance I was aiming for in terms of total time) I settled with this approach:

run a separate pgloader instance for every individual table, in parallel, inside tmux
using tmux wait-for the longest import migration
run a separate *.sql script for (almost each) table for post-processing (add FKs, add triggers), again in parallel inside a tmux
run vacuumdb --jobs=24 --analyze

To fit this purpose, the machine in question had been pimped:

14 cores (instead of 8)
64GB RAM (instead of 32GB; but they will eventually stay)

Also, for each individual pgloader invocation I had a seperate loader config with individual batch size configuration. The default batch size of 25000 wouldn't work for the bigger tables.

In the end, for my purpose and workload, this cut down the time

from 4h38m (1h11m importing, 1h41m adding FKs, 1h22 vacuuming)
to 2h8m6s (74m30s importing, 25m50s adding FKs, 27m44s vacuuming)

(Actually, the latest numbers are slightly below 2h even, but I don't have more detailed stats for them)

PostgreSQL 9.6 – GRANT Requirements for Stored Procedure

You have three options. You can grant update on the table to the role. You can grant update on the table to the user. Or you can do neither of those and create the function with the "SECURITY DEFINER" option.

In the last case, then the user will be able to update the table via the function, but will not be able to update the table outside of the function.

Best Answer

Related Solutions

Postgres 9.6 – How to Tune for One-Time Import

PostgreSQL 9.6 – GRANT Requirements for Stored Procedure

Related Question