Re: Aborted pg_dump run empties existing archive file

Ken Winter <ken@sunward.org> writes:

> I just discovered that a run of pg_dump that gets aborted empties any pre-existing backup file of the same name.  It
happenswhether the run was deliberately canceled 
> by the user or just failed because of a bad password (as in the example below). 
>
> ~/dba$ pg_dump --host=localhost -U ken  --format=c --file=mess_development.bak --no-owner --no-privileges
mess_development
> Password: <CORRECT PASSWORD ENTERED>
>
> ~/dba$ ls -l mess_development.bak
> -rw-rw-r-- 1 ken ken 87070 May 26 14:20 mess_development.bak
>
> ~/dba$ pg_dump --host=localhost -U ken  --format=c --file=mess_development.bak --no-owner --no-privileges
mess_development
> Password: <BAD PASSWORD ENTERED>
> pg_dump: [archiver (db)] connection to database "mess_development" failed: FATAL:  password authentication failed
foruser "ken" 
> FATAL:  password authentication failed for user "ken"
>  
> ~/dba$ ls -l mess_development.bak
> -rw-rw-r-- 1 ken ken 0 May 26 14:21 mess_development.bak
>
> ~/dba$
>
> This is troubling because I can imagine a disaster scenario where one loses one's previous backup and, perhaps
becauseof a forgotten password, can't generate a new 
> one.  (This scenario hasn't happened to me yet, but seems like it could.)  In general, a failed run of a piece of
softwareshould leave everything unchanged.  So maybe 
> this is a bug that needs to be fixed?

Fix it by not overwriting your existing backup each time.

Yes, it could probably not trunc the file until at least getting a valid
DB connection so this aspect could probably be improved.

You are running a pretty old Pg version.  I vaguely remember discussion
about pg_dump or some other utility truncing files a bit prematurely but
it was long ago.

But it can't know beyond that point if something else is going to cause
the backup to abort.

And I would not expect pg_dump to create a new file alongside your old
file just in case since this can so easily be implemented in whatever
framework you are launching pg_dump with.

HTH



> ~ Ken
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800