Thread: Can WAL files be shipped to multiple servers?

I am working on shipping WAL files, can WAL files do one-to-many shipping? The target PG instances are running in the recovery mode waiting for the WAL files.

also, once the target PG database receives WAL files and update its own database, can I run pg_dump to dump all the data when it is in recovery mode?

Thanks guys.

On Fri, Jul 23, 2010 at 18:29, Steeles <steeles@gmail.com> wrote:
> I am working on shipping WAL files, can WAL files do one-to-many shipping?
> The target PG instances are running in the recovery mode waiting for the WAL
> files.

Yes. Just copy the files to multiple machines, the slaves are
completely independent.


> also, once the target PG database receives WAL files and update its own
> database, can I run pg_dump to dump all the data when it is in recovery
> mode?

No.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Thanks for the quick reply.

so if I switch target database from recovery mode to normal mode and do pg_dump to backup, then switch it back to recovery mode.

Will it break the relationship between source and target? Do I need to re-configure it in order to receive the WAL files?

On Fri, Jul 23, 2010 at 18:36, Steeles <steeles@gmail.com> wrote:
> Thanks for the quick reply.
>
> so if I switch target database from recovery mode to normal mode and do
> pg_dump to backup, then switch it back to recovery mode.

You can't switch back.

Well, you can - by setting up the replication again from scratch,
meaning from a new base backup. That also means that if the master
crashes while you're backing up your slave, it will be out of date.


> Will it break the relationship between source and target? Do I need to
> re-configure it in order to receive the WAL files?

Yes.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 07/23/2010 09:31 AM, Magnus Hagander wrote:
> On Fri, Jul 23, 2010 at 18:29, Steeles<steeles@gmail.com>  wrote:
>> I am working on shipping WAL files, can WAL files do one-to-many shipping?
>> The target PG instances are running in the recovery mode waiting for the WAL
>> files.
>
> Yes. Just copy the files to multiple machines, the slaves are
> completely independent.
>
>
>> also, once the target PG database receives WAL files and update its own
>> database, can I run pg_dump to dump all the data when it is in recovery
>> mode?
>
> No.
>
>

Just curious does this restriction also apply to Hot Standby in 9.0?

--
Adrian Klaver
adrian.klaver@gmail.com

Steeles wrote:
> also, once the target PG database receives WAL files and update its
> own database, can I run pg_dump to dump all the data when it is in
> recovery mode?

Not while it's in recovery mode.  If you upgrade to the soon to be
released PostgreSQL 9.0, it's possible to bring the server up in Hot
Standby mode, which will allow pg_dump to execute against the standby.
Note that you have to be careful to tune the standby for this, as
pg_dump will act like a really long running transaction and there's a
risk of it being canceled if activity on the master conflicts with what
it's trying to dump.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Excerpts from Steeles's message of vie jul 23 12:36:41 -0400 2010:
> Thanks for the quick reply.
>
> so if I switch target database from recovery mode to normal mode and do
> pg_dump to backup, then switch it back to recovery mode.

The only way to switch back is to have a prior filesystem-level snapshot
before you put it in normal mode, and restore to that snapshot after
pg_dump is complete.