Postgres Pro
Downloads
Home   >   mailing lists

Re: Is Postgres database server works fine if there is a change in system time? - Mailing list pgsql-hackers

From Prakash Itnal
Subject Re: Is Postgres database server works fine if there is a change in system time?
Date
Msg-id CAHC5u7_ULZxk3079zb=gTSn7ULC949KiN_btepyqmVd=TpUxtg@mail.gmail.com
Whole thread Raw
In response to Re: Is Postgres database server works fine if there is a change in system time?  (Craig Ringer <craig@2ndquadrant.com>)
List pgsql-hackers
Tree view
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@2ndquadrant.com> wrote:
On 17 June 2015 at 15:36, Prakash Itnal <prakash074@gmail.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

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: Insufficient locking for ALTER DEFAULT PRIVILEGES
Next
From: Andres Freund
Date:
Subject: Re: checkpointer continuous flushing