PostgreSQL replication for archiving

Question

I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice.

I currently use Bucardo to replicate in a Master Slave setup. This works fine except I want the Slave to retain all records ever written to it. The master is a smaller system which will need to have its tables truncated periodically. I don't want these truncates to be carried over to the Slave. I would think this would be a fairly common practice but have been stumped finding a solution that will work.

Could anyone point me in the right direction? The right direction doesn't necessarily need to involve using Bucardo either.

Thanks

Answer 1

Bucardo is log/trigger based as I understand it. This means it doesn't require that other tables are not accessed.

The simple solution seems to me to create a trigger which inserts rows in these tables to rows in archive tables when they are inserted or, in the alternative, deleted. This way everything works properly on Bucardo's side but you retain a copy of the full data elsewhere.

Actually a better option might be to move to an unreplicated partition on delete. That way you still get a consistent view of the data as if the delete never took place.

Additionally if you have trouble making Bucardo work, Slony would be an option.