Thread: how to delay sync by a set time, hour or day?
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 thks, jzs
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
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
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.
> 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