Replicating data between Postgres DBs

Question

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?

Did you consider partitioning the table? And why do you need to copy it to another DB? Wouldn't a different Schema be enough? — user330315
– user330315, Commented Nov 29, 2011 at 14:45

Alex Howansky · Accepted Answer · 2011-11-29 14:29:51Z

2

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.

answered Nov 29, 2011 at 14:29

Alex Howansky

54k8 gold badges85 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2022-01-18 07:06:36Z

1

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:

edited Jan 18, 2022 at 7:06

answered Nov 29, 2011 at 15:49

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Replicating data between Postgres DBs

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related