how to delay sync by a set time, hour or day?

On Wed, Nov 19, 2014 at 8:37 AM, John Smith <jayzee.smith@gmail.com> wrote:

i want to setup a standby slave that listens 24/7 but only syncs when told to.

setting "pause_at_recovery_target (boolean)" in recovery.conf on slave
doesn't help since docs say it only "specifies whether recovery should
pause when the recovery target is reached."

also "recovery_target = 'immediate'" is the only value allowed.

i want to delay sync by a set time, hour or day.

how?
http://www.postgresql.org/docs/9.3/static/recovery-target-settings.html

Aren't pg_xlog_replay_pause and pg_xlog_replay_resume what you are looking for?
http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL
--
Michael

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

On 11/19/14, Michael Paquier <michael.paquier@gmail.com> wrote:

On Wed, Nov 19, 2014 at 8:37 AM, John Smith <jayzee.smith@gmail.com> wrote:

i want to setup a standby slave that listens 24/7 but only syncs when told
to.

setting "pause_at_recovery_target (boolean)" in recovery.conf on slave
doesn't help since docs say it only "specifies whether recovery should
pause when the recovery target is reached."

also "recovery_target = 'immediate'" is the only value allowed.

i want to delay sync by a set time, hour or day.

how?
http://www.postgresql.org/docs/9.3/static/recovery-target-settings.html

Aren't pg_xlog_replay_pause and pg_xlog_replay_resume what you are looking
for?
http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL
--
Michael

can i use "pg_start_backup('name');" and "pg_stop_backup();"? how
would they work ie if i stop backup, make changes to master, copy
master's /data/ to slave and restart backup, slave should catch-up
with master, right?

is this the same if i use "pg_xlog_replay_pause()" and
"pg_xlog_replay_resume()".

when would i use "pg_stop_backup();" over "pg_xlog_replay_pause()",
aside from user restrictions.

thanks, jzs

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

On 20 Nov 2014 06:11, "John Smith" <jayzee.smith@gmail.com> wrote:

On 11/19/14, Michael Paquier <michael.paquier@gmail.com> wrote:

On Wed, Nov 19, 2014 at 8:37 AM, John Smith <jayzee.smith@gmail.com>

wrote:

i want to setup a standby slave that listens 24/7 but only syncs when

told

to.

setting "pause_at_recovery_target (boolean)" in recovery.conf on slave
doesn't help since docs say it only "specifies whether recovery should
pause when the recovery target is reached."

also "recovery_target = 'immediate'" is the only value allowed.

i want to delay sync by a set time, hour or day.

how?
http://www.postgresql.org/docs/9.3/static/recovery-target-settings.html

Aren't pg_xlog_replay_pause and pg_xlog_replay_resume what you are

looking

for?

http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL

--
Michael

can i use "pg_start_backup('name');" and "pg_stop_backup();"? how
would they work ie if i stop backup, make changes to master, copy
master's /data/ to slave and restart backup, slave should catch-up
with master, right?

is this the same if i use "pg_xlog_replay_pause()" and
"pg_xlog_replay_resume()".

when would i use "pg_stop_backup();" over "pg_xlog_replay_pause()",
aside from user restrictions.

These are two different functions with different purpose. pg_stop_backup
marks the stop of a backup process (and not the recovery or restore
process). It makes postgres flush the wal to archives and reset other stuff
that pg_start_backup would have done in order to mark the begining of a
backup process. What you are probably trying to do is stop at a point
during recovery- either while restoring from a backup or during a recovery
on a standby db. If later is your case then use the suggested
pg_xlog_replay_pause.