0

I have two databases with tables that I want to sync. I don't want to sync any other table. I'm using Postgres-BDR to do that.

Those tables are part of replication set common. There are some circumstances where other tables share a name across nodes (but are NOT in common), and a node will call DROP TABLE and then CREATE TABLE. Even though those tables aren't part of the common replication set, these commands are still replicated to the other nodes, causing the other node to lose all of its data in its table and then create an empty table.

How can I stop this? I only want commands that affect common to be replicated to the other nodes.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
ABeard89
  • 911
  • 9
  • 17

1 Answers1

0

Nevermind, I found it. It's available with bdr.skip_ddl_replication.

I just put bdr.skip_ddl_replication = on in postgresql.conf, restarted the server, and BOOM! Works like a charm.

EDIT

It would be prudent of me to point out that the documentation warns that this option could break database replication if used improperly. But since I'll be VERY tightly controlling the table schema, it shouldn't cause any problems.

ABeard89
  • 911
  • 9
  • 17
  • `BOOM!` is right. Use with extreme care, or boom is exactly what will happen. If you want to submit a patch that makes table creation/drop/alter replication set aware that'd be something we'd consider accepting. So far it hasn't been a priority. – Craig Ringer Apr 06 '17 at 11:53
  • Something like that would be great, but unfortunately I'm not quite savvy enough to write that myself. I only know enough to be useful/dangerous. Hopefully I can avoid the latter on this project. – ABeard89 Apr 06 '17 at 12:53
  • Clearly, this option isn't intended to be in regular use. So either my use-case is irregular, or I'm just going about it the wrong way. I'm trying to sync user-related info across separate instances of an e-commerce web app. Synced tables will absolutely never be altered like this outside of maintenance times, but non-synced tables could be. In those cases, it would be bad for changes to affect other instances' table structure. If you don't mind my picking your brain @CraigRinger , is this an irregular way to use BDR, or do I simply need to rethink this? – ABeard89 Apr 06 '17 at 13:01
  • 1
    I think pglogical would be a better fit for this. – Craig Ringer Apr 07 '17 at 00:37
  • @CraigRinger (Thank you so much for your time. I really appreciate it.) Since multiple instances of the web app need to make changes to the shared data and have access to all of it, doesn't that mean I need multi-master? Or is that just newb-ish thinking? – ABeard89 Apr 07 '17 at 01:05
  • Yes, in that case you'd need MM. pglogical has limited MM support. I don't know if it's enough for your use. So yeah, maybe use bdr and replication sets. Why not just let all the tables exist across all the nodes though, and filter their contents if necessary? – Craig Ringer Apr 07 '17 at 01:40
  • @CraigRinger I thought pglogical didn't support MM at all. If it can do MM even a little, it might be enough. As for letting all tables coexist, since each instance of the app keeps vital info about itself in certain tables, I didn't think I could allow all tables to sync. Also the app uses a plugin system which lets each plugin create its own table. And if a plugin was removed from one instance I didn't want the other instances' data to be affected. I don't know anything yet about filtering though. So I'll research pglogical's MM support and content filters. – ABeard89 Apr 07 '17 at 01:52
  • @CraigRinger Thanks again for your time. You've really helped me out. – ABeard89 Apr 07 '17 at 01:55
  • 1
    Whether you use pglogical or BDR you'll need to make sure you have replication sets configured so you don't try to sync contents of tables that don't exist on the other end. – Craig Ringer Apr 07 '17 at 02:22
  • @CraigRinger Ok. So I'm creating a common rep set by adding specific tables to it. Those tables are guaranteed to exist and have the same structure forever. I setup my nodes to subscribe to that rep set. Other tables' data don't seem to sync, just as expected. – ABeard89 Apr 07 '17 at 02:36
  • @CraigRinger Reading up on pglogical, and it looks like you're right. Pglogical might be a better fit. It seems lighter than BDR, doesn't require a patched server, leaves DDL syncing up to the admin (which is preferable in this case), and includes just enough MM support to work, using the same rep set model. I guess the biggest catch for MM is having to manually manage each leg of the pub-sub network. But that's really not a big deal. I'll take your advice and see if I can switch over to a pglogical setup instead. – ABeard89 Apr 07 '17 at 02:43
  • At some point I want to "re-base" BDR on top of pglogical, so it uses the same low level facilities and protocol and just adds smarter conflict handling, node management, etc to pglogical. – Craig Ringer Apr 07 '17 at 07:01
  • @CraigRinger That sounds intense. I wish I were experienced enough to be able to help, because that sounds beyond interesting. I'm sure that would be a welcome sight to the DB world. Best of luck to you! – ABeard89 Apr 07 '17 at 07:39