Re: Is Postgres database server works fine if there is a change in system time?

Hi,

The more detailed discussion is on other mail chain with subject line:
"Auto-vacuum
is not running in 9.1.12" I agree there is no issue if time shifts by small
amount. This issue is serious only if there is big time shifts.

In our product the database is deployed on remote network elements and
there will be thousands of such network elements. All these network
elements are synced with one or more centralized NTP servers. If due to
some issue by mistake if NTP shifts back in time and corrects itself then
most of our network elements goes for toss. Since we use limited disc space
and our db model is write-heavy (update SQLs), the disc starts getting full
and soon all services get interrupted. Restarting service is costly from
customer point-of-view. So our suggestion is to prevent a restart in such
situations.

With my little understanding and analysis I have shared the patch in other
mail chain. Please check and share your inputs.

For quick reference please find the scenarios below:

On Mon, Jun 22, 2015 at 8:02 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:

> On 17 June 2015 at 15:36, Prakash Itnal <prakash074(at)gmail(dot)com> wrote:
> > Hi,
> >
> > Currently we observed that certain postgres child process, for eg.
> > autovacuum worker, are not working as expected if there is a system time
> > change. So I wanted to know if postgres already supports system time
> changes
> > or not.
>
> In general, small time slews will have no effect on operations. If
> they do then that's likely a PostgreSQL bug.
>
> A large time discontinuity, where time jumps backwards or forwards by
> a large amount, could well cause issues. I would personally be
> inclined to restart the database server in these cases, or kill the
> autovaccum scheduler to force it to restart.
>
> In what way are the autovacuum workers "not working as expected" after
> a system time change? What was the system time change made? How was
> the change made? On what operating system and version?
>
> BTW, this seems to be posted on Stack Overflow too:
>
> http://stackoverflow.com/questions/30839163/system-time-change-impact-to-postgresql-database-processes
>
>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

--
Cheers,
Prakash