Some information about the cluster:

Version: postgres 9.5

Size: /postgres/data$ du -hs . 854G

OS: Ubuntu 14.04 LTS

Storage: Raid10 SSD's

I have a master-slave PostgreSQL 9.5 cluster running in a datacenter using standard postgres replication features (streaming replication and wal-shipping). My goal is to build a second "slave" server in our office to be able to reproduce bugs that occured in production. As postgres hot-standby slaves only support readonly queries, my approach would be to nightly recreate the slave and start it stand-alone with write access. The data from last night would be sufficient for 95% of the support tickets and I am fully aware that all changes to these databases made during the day would be lost.. ;)

As the cluster is quite large, I'm not sure what would be the best way to implement this.

I'm thinking about a cronjob running nightly on the office slave:

  1. master_datacenter: pg_start_backup

  2. rsync postgres data directory from slave_live_datacenter to slave_lastnight_office

  3. master_center: pg_stop_backup();

  4. rsync wal files from slave_live_datacenter to slave_lastnight_office

  5. start slave_lastnight_office (in live writable mode, not slave/recovery mode)

Would it be possible to skip the rsync part and only update slave_lastnight_office to the state of last night with the newer wal and pitr? I guess not, as the changes during the day would make the replaying of the new wal impossible?

  • The words "nightly recreate the slave" (840+GB) sound ominous to your network. No targeted replication would help? – LAFK says Reinstate Monica May 21 '16 at 14:01
  • @little-ancient-forest-kami Yeah, nightly is probably too much, I guess weekly would be sufficient too. Not a very elegant solution nevertheless. I don't see how I could accomplish targeted replication when the cluster changes during the day/week (because of the required write access) – Toni Jun 2 '16 at 12:31
  • Say I got a spare server in the office with a lot of cheap but slow disk space. Would it be practical to create a second (cascading) slave on this machine, only transfering the changes to the database cluster. It then should be possible to nightly either clone this readonly vm (to change it to read-write) or to automatically create a second writeable slave on ssd? – Toni Jun 16 '16 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.