Re: Mysterious DB reset - Mailing list pgsql-general

-----------------------------------------------
Israel Brewster
Computer Support Technician II
Era Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7250 x7293
-----------------------------------------------



On Mar 7, 2014, at 12:07 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> On 03/07/2014 11:08 AM, Israel Brewster wrote:
>> 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
backupserver and not primary -i.e. the domain name in the first half wasn't for the local machine until I swapped
machines.Apparently when you do a pg_dump with the -c flag from a server to itself, it does the clean before reading
thedata, resulting in a new empty database. Thanks again for all the suggestions! 
>
> No, -c just tells pg_dump to output clean commands and really only has meaning when you do a plain text dump as
above.pg_dump does not clean the data from the running server on its own. What did the cleaning was immediately piping
tooutput of the dump to psql. So basically you created a plain text dump file and fed it back to the server and the
dumpfile included commands to clean out objects. If you had not used -c you would have gotten a bunch of duplicate
<something>errors. 

Right, but I ended up with no data. So what I was getting at was that because I had the -c, which output clean
commands,those clean commands were executed before pg_dump dumped the data. You are absolutely correct in saying that
itwas only executed at all because it was piped to psql, I wasn't claiming otherwise :-) However, the sequence of
eventsclearly is: 1) pg_dump outputs "clean" commands. The pipe to psql runs these, deleting the tables/data/etc. 2)
pg_dumpoutputs the rebuild commands, which are piped to psql, which rebuilds the tables/etc 3) pg_dump tries to dump
thedata, but there is none (other than the default starting data in the sequences) due to steps 1 and 2 already having
beenexecuted (by the pipe to psql) on the same database that pg_dump is running on. 

Sorry for not being clear :-)

>
>
>>
>>>
>>> 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
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general