0

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?

Mridang Agarwalla
  • 43,201
  • 71
  • 221
  • 382
  • Did you consider partitioning the table? And why do you need to copy it to another DB? Wouldn't a different Schema be enough? –  Nov 29 '11 at 14:45
  • Perhaps you could use dblink and an AFTER INSERT-trigger? – plundra Nov 29 '11 at 14:48

2 Answers2

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.
Alex Howansky
  • 50,515
  • 8
  • 78
  • 98
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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228