Is it possible to have additional writable tables to a postgresql slave (replicated by Skytools/londiste)

Question

I have a master postgres database M with tables M.A1, etc. I have a slave database S with tables M.A1, etc, populated and maintained by Skytools/londiste. Everything works great.

I don't know exactly how it works, since I am not the person who set up my Skytools instance. I have just read some pieces of documentation and interacted with it slightly.

I would like to add some auxiliary read/write tables to S: S.B1 . (I want to join against S.A1, and not add any extra load to M, which is why I want to install B1 on S. Is it possible to maintain this setup?

If I create a new table S.B1 on a Skytools/Londiste slave, will that interfere with replication of table A1?

Edit to add followup:

How safe would such a setup be, with respect to slave failures impacting the master?

I am not very concerned about replication lag or downtime on my analytics slave (but I would need a way to eventually recover without taking downtime on the master).

I am very concerned about a slave failure causing the master to grow its replication queue indefinitely and consuming HD/RAM/resources on the master. How would I mitigate that? Is there a way to set a tolerance so that the master just drops the slave connection if the slave falls too far behind?

Part 2

If I do get this set up working, I'll want to have a slave backup of S.B1 somewhere, in case S fails.

Is it possible to set up a secondary slave T, and configure Skytools/Londiste to replicate S.B1 to T.B1, while M.A1 is also replicating to S.A1?

What caveats are gotchas should I be concerned about?

Thank you so much for advice and pointers.

Answer 1

Firstly I would really suggest that you spend the time in understanding how skytools pgq and londiste work. It is not very complicated but the documentation is rather scant.

For your first question - yes you can have other tables on the slave which are not replicated from the master.

Your second question is a bit more involved and I am not sure if your requirement is entirely clear. Assuming the tables you want to replicate from the slave to a secondary slave are an entirely separate group from the tables you are replicating for the master to the initial slave then you could install pgq on the initial slave and londiste on the secondary slave , create a new queue and add those tables to that queue which you wish to replicate to the secondary slave.

You can't use skytools/Londiste for cascading replication e.g. master -> slave1 -> slave2 so it is not obvious what benefit you would get from the partial replication of data from one slave to another.

It would be simpler to have all the tables on the master and then just one queue for replication to a slave and then for resilience have a warm standby of the master see explanation for 8.4 from which you could do a point in time recovery if necessary and then rebuild the slave from a consistent master. Skytools has packages to help you with setting up warm standby/pitr.

If you cannot have all the tables on the master then you might be better to maintain a warm standby of the slave for pitr recovery but bear in mind you would probably have to resubscribe the tables replicated from the master after doing such a restore. This might be complicated if the slave tables you are joining to the master tables have foreign key constraints.

If you are postgres 9 there is streaming replication which may also serve but I have not used this.

Answer 2

Just to expand on the topic, if anyone reaches it, you can have multiple queues as Gavin suggested above, but also you can have cascading replication as of Skytools version 3 (March 2012). And indeed you can replicate any subset of tables, and you can even have tables renamed on destination, if needed.