On Mar 6, 2014, at 1:25 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> On 03/06/2014 09:33 AM, Israel Brewster wrote:
>> For starters, this happened again this morning (no data prior to 4:45 am and sequence reset), so whatever is going
onappears to be reoccurring. Also, I forgot to mention if it is significant: this is running on slackware liunux 14.0
>>
>>
>> Also odd is that my cleanup script runs at 1am. I have records of there
>> being new data in the database up to 3:51am, but the oldest record
>> currently in the DB is from 4:45am (as specified by the default of now()
>> on the column). So I know records were added after my delete command
>> ran, but before this reset occurred.
>>
> A shot in the dark...
>
> Have you searched /etc/crontab, root's crontab, PostgreSQL's crontab and the crontabs of any automatic scripts that
connect.I'm not sure about Slackware but Red Hat and Centos run the cron.daily scripts at (wait for it...) just after
4am.
Good shot - you nailed it! I found a rouge script in /etc/crontab.daily that ran the following line at 4:40am:
/usr/local/pgsql/bin/pg_dump -h <domain name of localhost> -U tracking -cs | /usr/local/pgsql/bin/psql -U postgres
tracking
It must have been left over from before I got streaming replication up and working, when this box was still the backup
serverand not primary -i.e. the domain name in the first half wasn't for the local machine until I swapped machines.
Apparentlywhen you do a pg_dump with the -c flag from a server to itself, it does the clean before reading the data,
resultingin a new empty database. Thanks again for all the suggestions!
>
> Some of the default daily scripts like logrotate can have "side effects" like restarting the service that writes to
thelog file being rotated.
>
> Cheers,
> Steve
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
