Replication using WAL files

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]But someone is working on this for 8.4. Don't hold your breath, though. 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&amp;words=replication&amp;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&amp;words=replication&amp;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&amp;words=replication&amp;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)&lt;http://archives.postgresql.org/sydpug/2006-10/msg00001.php&gt;
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