----------------------------------------------- Israel Brewster Computer Support Technician II Era Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x7293 -----------------------------------------------
BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:israel@xxxxxxxxxxxxxxxxxx TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
On Mar 7, 2014, at 12:07 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > On 03/07/2014 11:08 AM, Israel Brewster wrote: >> On Mar 6, 2014, at 1:25 PM, Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> 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 on appears 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 server 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 the data, 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 to output of the dump to psql. So basically you created a plain text dump file and fed it back to the server and the dump file 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 it was only executed at all because it was piped to psql, I wasn't claiming otherwise :-) However, the sequence of events clearly is: 1) pg_dump outputs "clean" commands. The pipe to psql runs these, deleting the tables/data/etc. 2) pg_dump outputs the rebuild commands, which are piped to psql, which rebuilds the tables/etc 3) pg_dump tries to dump the data, but there is none (other than the default starting data in the sequences) due to steps 1 and 2 already having been executed (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 the log file being rotated. >>> >>> Cheers, >>> Steve >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> >> >> > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
