1

We are using the same PostgreSQL 9.3 Server both for production and development.

So we would like to get the copy of the existing production database for the development purposes. To be precise, all the INSERT/UPDATE/DELETE events that come to production should be also placed into its copy. Reverse replication is not needed. How can we do that?

PS: Please take into account that full master-slave replication is not suitable. We have no opportunity to deploy one more PostgreSQL server at the moment.

UPD: pg_dump/pg_restore is not the case too, because this will crash all the updates done by developers in their database.

Vitaly Isaev
  • 5,392
  • 6
  • 45
  • 64
  • @a_horse_with_no_name, unfortunately this approach is not suitable too, because developers can make some updates in their copy. We need just to duplicate the UPDATE/INSERT events, not deploying the dumps – Vitaly Isaev Oct 15 '14 at 11:25
  • 2
    You can not have "full copy of the existing production database" and "updates done by developers in their database" at the same time. There is no simple way to resolve conflicts between prod and dev updates. – Ihor Romanchenko Oct 15 '14 at 11:30
  • BTW You may want to check postgre BDR: https://wiki.postgresql.org/wiki/BDR_User_Guide It will be available (not very stable) in 9.4 – Ihor Romanchenko Oct 15 '14 at 11:40
  • @IgorRomanchenko, thanks, all of that are good points... However still not suitable for us due to the updates in dev database that will be wiped out at the time of restore. Guess we need smth like cross database triggers... – Vitaly Isaev Oct 15 '14 at 11:43
  • How seriously do you need this? I never had the need to keep a 100% identical image and yet keep them separate. If it's so important, I think you'll need to work with triggers and stored procedures. – itsols Oct 15 '14 at 11:45
  • @itsols, we faced with a need of building a separate environment for the 'dataflow' programmers. They need to test their soft on the real amounts of data that come to the production database. Surely we also need to avoid any affections from the developers to the production database. – Vitaly Isaev Oct 15 '14 at 11:51

1 Answers1

1

Sounds like you are looking for a replication system like Slony or Bucardo

Slony uses triggers to replicate the data so that should work without much hassle here. And Bucardo uses NOTIFY to do pretty much the same.

For just a local setup I would recommend Slony but if you would like to offer the developers a local database (i.e. local machine) I would recommend Bucardo instead as it offers asynchronous replication.

Wolph
  • 78,177
  • 11
  • 137
  • 148