HOT standby with ONLY WAL shipping?

On 06/12/14 10:01, CS_DBA wrote:

We would like to setup a hot standby server with a forced delay.
Is it possible to setup a hot standby based ONLY on WAL shipping and NOT
use streaming replication?

Yes it is. Though, I assume you mean 'forced max sync period' as
opposed to 'forced delay'. Transfers will happen as WAL's fill or
the timeout expires.

The slave will also be read-only.

Here's a setup for every 10 mins (600 secs).

On master (postgresql.conf):
wal_level = hot_standby
archive_mode = on
archive_command = '<your_archive_command>'
archive_timeout = 600 # Or whatever timeout you want

On slave (postgresql.conf):
hot_standby = on

On slave (recovery.conf):
standby_mode = 'on'
restore_command = '<your_restore_command>'
archive_cleanup_command = '<your_cleanup_command>'

I don't recall if a full re-sync is required prior to changing the
wal_level or whether simply restarting both servers will suffice.

Usual caveats and disclaimers apply. :-)

HTH,
Bosco.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Jun 13, 2014 at 1:01 AM, CS_DBA <cs_dba@consistentstate.com> wrote:

Is it possible to setup a hot standby based ONLY on WAL shipping and NOT
use streaming replication?

If I am not wrong then what you are looking for is Archive Shipping​.
Any specific reason why you want to do that?

Anyways, you can use pg_receivexlog as well for setting up archives or for
setting up a replica. But if you are trying to avoid some other issue
w.r.t. streaming mechanism in PostgreSQL (which I assume will be very
specific to your environment), then this may not be very helpful as
pg_receivexlog too works on same streaming protocol and needs wal-sender
processes.

Best Regards,

*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

M: *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com

*[image: icons]*

[image: Email patch] <http://www.ashnik.com/&gt;

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).

On Thu, Jun 12, 2014 at 1:01 PM, CS_DBA <cs_dba@consistentstate.com> wrote:

Hi All;

We would like to setup a hot standby server with a forced delay.
Is it possible to setup a hot standby based ONLY on WAL shipping and NOT
use streaming replication?

Thanks in advance

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The omnipitr tool can provide the delayed slave feature you're looking for.
https://github.com/omniti-labs/omnipitr/

Look into the --recovery-delay (-w) feature of the omnipitr-restore command
which is used in the recovery.conf of the slave
https://github.com/omniti-labs/omnipitr/blob/master/doc/omnipitr-restore.pod

Whether a slave is a hot standby is not determined by whether you're using
streaming replication or WAL replay. It is determined by setting the
parameter "hot_standby" on the slave and ensuring the master has a minimum
"wal_level" of "hot_standby" as well. So both streaming and wal replay
slaves can be hot standbys.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com