I have an instance with only one database which contains 4 years of data (400 gigas). I name this instance inst_live I want to implement this following archiving strategy:
- create an archive instance (inst_archive) and copy the database from inst_live to inst_archive.
- all days, delete from inst_live all datas which are over two years
- replay inst_live transactions in inst_archive except the purge.
Implement a streaming replication from inst_live to inst_archive is a solution to maintain freshness data in inst_archive but I fear the purge applied in inst_live will be applied in inst_archive too.
Inst_live has to contain only 2 years of data Inst_archive has to contains 4 years of data.
Is anyone have any ideas please ?
I am using Postgresql 9.5 in Red Hat 7.1
Pglogical seems allowing logical replication based only on INSERT and UPDATE statements. Does anyone implement it in production? Is there tutorials other than official documentation? I have some trouble to follow step by step official documentation.
Does Slony allow logical replication based on INSERT and UPDATE only?