----------------------------------------------- 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:isr...@frontierflying.com 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.kla...@aklaver.com> wrote: > On 03/07/2014 11:08 AM, Israel Brewster wrote: >> On Mar 6, 2014, at 1:25 PM, Steve Crawford <scrawf...@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 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@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> >> >> > > > -- > Adrian Klaver > adrian.kla...@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
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general