Синхронизация двух баз данных по запросу

с остановкой postgres копировать на SSD диске - видимо выше такой скорости копирования не достичь. либо в RAM
через бекап долго.

ZFS snapshot + zfs send/zfs rollback/zfs receive

Версии Postgresql ясен пень желательно одинаковые на обеих нодах. На время создания снапшота Postgres на рабочем сервере придется остановить. Зато потом можно откатываться к этому состоянию на тестинге хоть до усрачки.

pg_start_backup()/pg_stop_backup() и не надо ничего останавливать.

Репликацию же можно паузить и резюмить.

Есть мнение, что если у него там НЕ read-only реплика - получится каша.

как приводить тестовую базу в актуальное состояние (данные и таблицы как в рабочей) как можно быстрее,

Остановка и rsync или что-то подобное --- один вариант.

при этом репликация не подходит, так как в актуальное состояние её нужно приводить именно по желанию пользователя.

А вы уверены? Смотрите (из https://www.postgresql.org/docs/current/static/app-pgrewind.html):

The result is equivalent to replacing the target data directory with the source one. Only changed blocks from relation files are copied; all other files are copied in full, including configuration files. The advantage of pg_rewind over taking a new base backup, or tools like rsync, is that pg_rewind does not require reading through unchanged blocks in the cluster. This makes it a lot faster when the database is large and only a small fraction of blocks differ between the clusters.

The use of pg_rewind is not limited to failover, e.g. a standby server can be promoted, run some write transactions, and then rewinded to become a standby again.

Т.е. это прямо ваш случай (причём, делать он это может с работающего сервера-источника).

Сам я не пробовал, но похоже, что (при наличии архива WAL) должно получиться.

Кто мешает делать это из бэкапа (не снапшота) barman/wal-e/wal-g?

На время создания снапшота Postgres на рабочем сервере придется остановить.

Пришло время тащить btrfs в продакшен. Там и инкрементальные снапшоты можно пересылать

Базы на разных серверах находятся, просто может есть что то что копирует различие...

Ну тут еще проблема, что с тестовой базы тоже происходят изменения и как это воспримет pg_rewind из документации не понятно.

Эм, собственно zfs send скопирует тебе данные снапшота - то есть различия

Ну тут еще проблема, что с тестовой базы тоже происходят изменения и как это воспримет pg_rewind из документации не понятно.

Ещё раз: a standby server can be promoted, run some write transactions, and then rewinded to become a standby again.

Мне кажется, что делать надо как-то так:

• Создаёте архив WAL (он может понадобиться pg_rewind, да и вообще, он у вас и так должен быть для recovery production).
• Создаёте standby (это будет ваша тестовая база) копированием production (primary).
• Превращаете этот standby в обычный r/w сервер (это т.н. точка расхождения timelines, т.е. на primary всё ещё первая, а на тестовом кластере станет, например, вторая).
• Что-то делаете на тестовой базе (меняете данные).
• Когда нужно обновить тестовую базу:
  • Останавливаете тестовую базу.
  • Применяете pg_rewind (он отматывает до точки расхождения timelines, т.е. standby у вас опять на первой).
  • Дальше запускаете standby в режиме recovery, он «догоняет» primary.
  • Превращаете этот standby в обычный r/w сервер.
  • ... а дальше см. выше ...
  • Profit!

Если попробуете и (не)получится, вы не могли бы отписаться? ;)