Seeking advice on database replication.

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

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:

Thanks Scott and Matthias for your replies!

On Nov 11, 2010, at 5:29 PM, Scott Marlowe wrote:

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.

At the time I remember trying to do my 'due diligence' before going to the list, but just got frustrated. I'm happy to revisit it and will certainly avail myself of the list. When I read that PG9 was going to support replication I just decided to put it off until then. Slony might be our best option.

On Nov 12, 2010, at 3:49 PM, Matthias Müller wrote:

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>

Thanks - I'll take a look at that as well. I'm not familiar at all with WAL processing, so it wasn't even an option on my radar.

Cheers,
Demitri

Center for Cosmology and Particle Physics
New York University