Re: Seeking advice on database replication.

I don't know slony that much. I used WAL processing. But since 9.0 I prefer
Hot-Streaming replication. This link is a good starting point - besides the
documentation.

<http://it.toolbox.com/blogs/database-soup/5-minutes-to-binary-
replication-41873>

A solution for your problem might be a second database on the replicated db-
server that is working in normal mode (not hot standby) and is filled up by the
replicated database, which works in read-only mode. PG can easily handle
multiple clusters that contain 80MB on one "normal" server.

Matthias

Am Donnerstag, 11. November 2010, um 23:05:06 schrieb Demitri Muna:
> Hello,
>
> I am interested in sharing/replicating data between different databases,
> and I'd like to ask if what I'd like to do is possible in postgresql. I
> have read a fair amount of documentation and was looking forward to
> PostgreSQL 9, but I don't think it will do for me what I want.
>
> I have an astronomical database at one site, let's call it A. At my own
> institution (across the country), I have another database, B. I want to
> replicate all of the tables of A into a read-only copy in B, in as close
> to real-time as possible. The time isn't a critical factor here - if it's
> delayed by even an hour, I'm ok with that. Tables in B will need to JOIN
> against tables from A. The total size of A is ~80MB and grows slowly.
>
> After reading the documentation for PG9's replication, it seems I cannot do
> this since it only supports replicating a cluster. It appears that I'd
> want to put the tables in B into one schema, the tables from A into
> another schema in the same database (let's call it B.a), and replicate the
> tables from A into B.a. Is this at all possible? This promises to be a
> very powerful tool for us, but I don't know how best to accomplish this.
>
> Further, I'd like A to be replicated to several institutions. Again, this
> is not a real-time operation, but something that doesn't require user
> intervention is ideal.
>
> I tried to run Slony-I last year, but found it to be very frustrating and
> never got it to work. (In retrospect, I don't even know if it supports
> schema-level replication).
>
> Any advice would be greatly appreciated!
>
> Cheers,
> Demitri
>
> Center for Cosmology and Particle Physics
> New York University