Re: Seeking advice on database replication.

On Thu, Nov 11, 2010 at 3:05 PM, Demitri Muna
<thatsanicehatyouhave(at)mac(dot)com> wrote:
> 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).

Next time, post questions to the slony mailing list if you get stuck,
we're pretty helpful over there. also look at some of the "my first
replicated db" tutorials for it.
Slony, btw, supports replicating whatever you want to replicate. You
build a set with the tables in it and replicate that set. That set
can be almost any group of tables and sequences yuo want to define.

> Any advice would be greatly appreciated!

Well, I was gonna suggest slony, however...