Обсуждение: Replication using WAL files
Hi list,
I'm a newbie for postgresql replication
This is my requirement.
I have 2 servers on which I need to have data replicated. The master server should serve for read/write queries and the 2nd server is used mainly for
research queries(read-only queries) and so it doesn't have to be up-to-date. The read queries should be able to be sent to both servers so load-balancing is possible. In case the master server fails the 2nd server should be able to take over(fair-over support).
Is it possible to implement this thro' WAL shipping?
I tried the 'Continuous Archiving and PITR' in my test database and it works fine. But this set-up is only for a warm standby server...right?! Is it possible to make both the servers work asynchronously, while the primary server takes up all write queries and the read queries can be sent to both primary and 2nd server .And can the 2nd server be updated using the WAL files (WAL shipping) of the primary server asynchronously (while both the servers are up)?
Hope I explained my requirement clearly.
Please advice
Thanks
josh
I'm a newbie for postgresql replication
This is my requirement.
I have 2 servers on which I need to have data replicated. The master server should serve for read/write queries and the 2nd server is used mainly for
research queries(read-only queries) and so it doesn't have to be up-to-date. The read queries should be able to be sent to both servers so load-balancing is possible. In case the master server fails the 2nd server should be able to take over(fair-over support).
Is it possible to implement this thro' WAL shipping?
I tried the 'Continuous Archiving and PITR' in my test database and it works fine. But this set-up is only for a warm standby server...right?! Is it possible to make both the servers work asynchronously, while the primary server takes up all write queries and the read queries can be sent to both primary and 2nd server .And can the 2nd server be updated using the WAL files (WAL shipping) of the primary server asynchronously (while both the servers are up)?
Hope I explained my requirement clearly.
Please advice
Thanks
josh
On Dec 7, 2007 9:52 AM, Josh Harrison <joshques@gmail.com> wrote: > I tried the 'Continuous Archiving and PITR' in my test database and it works > fine. But this set-up is only for a warm standby server...right?! Is it > possible to make both the servers work asynchronously, while the primary > server takes up all write queries and the read queries can be sent to both > primary and 2nd server .And can the 2nd server be updated using the WAL > files (WAL shipping) of the primary server asynchronously (while both the > servers are up)? PITR currently is for HA only, not replication. Check out slony. merlin
On 12/7/07, Josh Harrison <joshques@gmail.com> wrote: > I have 2 servers on which I need to have data replicated. The master server > should serve for read/write queries and the 2nd server is used mainly for > research queries(read-only queries) and so it doesn't have to be up-to-date. ... > Is it possible to implement this thro' WAL shipping? No. At the moment [1] the WAL shipping system does not permit you to query the slave. There are a few options: http://pgfoundry.org/search/?type_of_search=soft&words=replication&Search=Search As well as this: http://bucardo.org/ [1] But someone is working on this for 8.4. Don't hold your breath, though. Alexander.
On 12/7/07, Josh Harrison < joshques@gmail.com> wrote:...
> I have 2 servers on which I need to have data replicated. The master server
> should serve for read/write queries and the 2nd server is used mainly for
> research queries(read-only queries) and so it doesn't have to be up-to-date.> Is it possible to implement this thro' WAL shipping?No. At the moment [1] the WAL shipping system does not permit you to
query the slave. There are a few options:
http://pgfoundry.org/search/?type_of_search=soft&words=replication&Search=Search
As well as this:
http://bucardo.org/
[1] But someone is working on this for 8.4. Don't hold your breath, though.
Thanks for the info. Just to clarify, So at the moment for WAL shipping to work the 2nd server should only be a stand-by server and not a slave(cannot be queried) ?
In your experience, which other replication system (slony,pgpool etc) is better suited for my requirement?
Thanks again
josh
On 12/7/07, Josh Harrison < joshques@gmail.com> wrote:...
> I have 2 servers on which I need to have data replicated. The master server
> should serve for read/write queries and the 2nd server is used mainly for
> research queries(read-only queries) and so it doesn't have to be up-to-date.> Is it possible to implement this thro' WAL shipping?No. At the moment [1] the WAL shipping system does not permit you to
query the slave. There are a few options:
http://pgfoundry.org/search/?type_of_search=soft&words=replication&Search=Search
As well as this:
http://bucardo.org/
[1] But someone is working on this for 8.4. Don't hold your breath, though.
Thanks for the info. Just to clarify, So at the moment for WAL shipping to work the 2nd server should only be a stand-by server and not a slave(cannot be queried) ?
In your experience, which other replication system (slony,pgpool etc) is better suited for my requirement?
Thanks again
josh
Hi,
I read all the documentations and got a bit confused.
1. I have a primary server which archives the WAL files to the secondary servers's directory(some directory).
2. The recovery.conf in the secondary server is set to copy these files from this directory.
3. I take a base backup and feed it to the secondary server and start the secondary server.
4. The secondary server now contains all the base backuped data.
5. I set up a trigger (as in this demo http://archives.postgresql.org/sydpug/2006-10/msg00001.php)
I created/inserted some table/data in the primary server, 'touch' trigger in sec server and the archived files are reflected in the sec server's database now and the sec database is up (recovery.conf becomes recovery.done) and I can query the secondary as normal.
My question is that will any future changes in the primary server gets reflected in the secondary server or is it just a 1-time show? If no, is it possible to have a set-up like that (secondary server getting asychronous updates from master thro' WAL files......I guess "Hot standby using WAL files") ?
Sorry if my question sounds silly.
Thanks for all your help
josh
My question is
On Dec 7, 2007, at 11:49 AM, Josh Harrison wrote: > > > > > On 12/7/07, Josh Harrison < joshques@gmail.com> wrote: > > I have 2 servers on which I need to have data replicated. The > master server > > should serve for read/write queries and the 2nd server is used > mainly for > > research queries(read-only queries) and so it doesn't have to be > up-to-date. > ... > > Is it possible to implement this thro' WAL shipping? > > No. At the moment [1] the WAL shipping system does not permit you to > query the slave. There are a few options: > > http://pgfoundry.org/search/? > type_of_search=soft&words=replication&Search=Search > > As well as this: > > http://bucardo.org/ > > [1] But someone is working on this for 8.4. Don't hold your breath, > though. > > > Thanks for the info. Just to clarify, So at the moment for WAL > shipping to work the 2nd server should only be a stand-by server > and not a slave(cannot be queried) ? > In your experience, which other replication system (slony,pgpool > etc) is better suited for my requirement? > > Thanks again > josh > > > Hi, > I read all the documentations and got a bit confused. > 1. I have a primary server which archives the WAL files to the > secondary servers's directory(some directory). > 2. The recovery.conf in the secondary server is set to copy these > files from this directory. > 3. I take a base backup and feed it to the secondary server and > start the secondary server. > 4. The secondary server now contains all the base backuped data. > 5. I set up a trigger (as in this demo http:// > archives.postgresql.org/sydpug/2006-10/msg00001.php) > I created/inserted some table/data in the primary server, 'touch' > trigger in sec server and the archived files are reflected in the > sec server's database now and the sec database is up (recovery.conf > becomes recovery.done) and I can query the secondary as normal. > > > My question is that will any future changes in the primary server > gets reflected in the secondary server or is it just a 1-time show? > If no, is it possible to have a set-up like that (secondary server > getting asychronous updates from master thro' WAL files......I > guess "Hot standby using WAL files") ? Right, once you've brought the secondary server out of standby it will no longer replay any changes from the primary server. However, it is possible to stop and restart the standby as long as you don't trigger it to come out of recovery mode. Using this you can stop the standby make a copy of the standby's cluster directory, restart the standby and then start up this third copy in normal operations mode. How feasible this is is determined by how long it takes to make a copy of your cluster directory. Here's a general outline of what I'm talking about using rsync: pg_ctl stop -D .../standby_cluster_dir/ rsync -a --delete .../standby_cluster_dir/* .../read_db/ rm .../read_db/recovery.conf # or, just rename it pg_ctl start -D .../standby_cluster_dir/ pg_ctl start -D .../read_db/ You may want different rsync flags. If you can use zfs then you're even better off as you can replace the rsync with zfs snapshotting. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com