Aborted pg_dump run empties existing archive file

On Thu, May 26, 2016 at 2:48 PM, Ken Winter <ken@sunward.org> wrote:

I just discovered that a run of pg_dump that gets aborted empties any
pre-existing backup file of the same name. It happens whether 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 for user "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 because of 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 software
should leave everything unchanged. So maybe this is a bug that needs to be
fixed?

~ Ken

...a run of pg_dump that gets aborted empties any pre-existing backup file

of the same name

This is standard behavior. By using the same output file name, you are
telling the O/S to overwrite/delete that file and replace with new
information.
Most DBA's usually specify a date(and/or time) as a suffix to prevent that
probleb and keep several versions of backup.

Not that it in this case, but it is considered good procedure to report the
PostgreSQL version and O/S when contacting this list for support.
Please consider that for future correspondence.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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 happens whether 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 for user "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 because of 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 software should 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general