Replicating data between Postgres DBs

I have a Postgres DB that is used by a chat application. The chat system often truncates these tables when they grow to big but I need this data copied to another Postgres database. I will not be truncating the tables in this DB.

How I can configure a few tables on the chat-system's database to replicate data to another Postgres database. Is there a quick way to accomplish this?

• Slony can replicate only select tables, but I'm not sure how it handles truncates, and it can be a pain to configure.
• You might also use something like pgpool to send copies of the insert statements to a second database.
• You might modify the source of your chat application to do two writes (one to each db) when a new record is created.
• You could just write a script in Perl/PHP/Python to read from one and write to another, then fire it by cron so that you're sure it gets run before truncation.

If you only copy a batch of rows every other day, you may be better off with a plain INSERT to a different schema in the same database or a different database in the same database cluster (you need something like dblink for that).

The safest / fastest solution in the same database would be a data-modifying CTE. Something along these lines:

WITH del AS (
    DELETE FROM tbl
    WHERE  <some condition>
    RETURNING *
    )
INSERT INTO backup.tbl
SELECT * FROM del;

For true replication consider these official sources:

• https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
• https://www.postgresql.org/docs/current/runtime-config-replication.html