Re: Mysterious DB reset

On 03/07/2014 03:10 PM, Israel Brewster wrote:

>>> /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 :-)

Yea, a snake eating its tail.

>
>>
>>
>


--
Adrian Klaver
adrian.klaver@aklaver.com