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:

  1. create an archive instance (inst_archive) and copy the database from inst_live to inst_archive.
  2. all days, delete from inst_live all datas which are over two years
  3. 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?


Are there any deletions other than the purges occuring on inst_live? If not, you could use logical replication to replicate just the inserts and updates over. This would require v10 to do it easily. It can be done in 9.5, but you would need to create your own plugin as logical replication had only a skeletal implementation in-core in that version.

If you tell us why you need to do this (security? Conserve disk space?) it might inspire more answers.

  • Deletions occurs only through the purge. Logical replication with Insert and Update subscription is exactly the need. As you know, it is only implement in v10. Can you please tell me how could it be implement in v9.5? Thanks a lot! The inst_live has to be purged because its size is too high (400gb) and the project team works with only 2 years old datas. Archive is used for BI and audit. – Mika Mar 3 '18 at 16:52
  • Sorry, I don't know enough to help for 9.5, I just know it is possible. If the doc (postgresql.org/docs/9.5/static/logicaldecoding.html) isn't enough to go on, you might need paid support/consulting. (But I would think the time and money would be better spent overcoming the hurdles that are keeping you on 9.5) – jjanes Mar 4 '18 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.