Обсуждение: Seeking advice on database replication.
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 possiblein postgresql. I have read a fair amount of documentation and was looking forward to PostgreSQL 9, but I don't thinkit 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 anotherdatabase, 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 needto 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 samedatabase (let's call it B.a), and replicate the tables from A into B.a. Is this at all possible? This promises to bea 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 thatdoesn'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 evenknow if it supports schema-level replication). Any advice would be greatly appreciated! Cheers, Demitri Center for Cosmology and Particle Physics New York University
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 ispossible in postgresql. I have read a fair amount of documentation and was looking forward to PostgreSQL 9, but I don'tthink 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 anotherdatabase, 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 needto 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 samedatabase (let's call it B.a), and replicate the tables from A into B.a. Is this at all possible? This promises to bea 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 thatdoesn'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'teven 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: > 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
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 revisitit and will certainly avail myself of the list. When I read that PG9 was going to support replication I just decidedto 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 myradar. Cheers, Demitri Center for Cosmology and Particle Physics New York University