Changing pg_dump default file format

On Thu, Nov 7, 2013 at 5:02 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

Hello,

For as long as I can remember I have heard complaints about the fact that
pg_dump doesn't use a backup format that is supported by the pg_restore
program. At every single talk that I give that talks about pg_dump, the
people in the majority of the audience agrees that this is rather silly and
a barrier to usability.

The only counter argument I have heard is that it adds an extra step for
developers to get the SQL that generates a database. That to me is a weak
argument as pg_dump is a production/admin too, first, not a developer tool.

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

The main reason for keeping plain I guess goes with the fact that we
sent it to stdout by default, which really doesn't lend itself to the
custom format.

I'm a bit worried about it breaking peoples scripts as well, if they
pipe the output. In particular when doing a partial dump of say a
sigle table or such, and in my experience that's mostly done in
pipe-mode, which would break.

Though I have to say I like the idea in general :)

Maybe we could consider changing the default format if -f is specified
to write it directly to a file. OTOH, hat might just make it
confusing....

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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

On 11/07/2013 11:02 AM, Joshua D. Drake wrote:

Hello,

For as long as I can remember I have heard complaints about the fact
that pg_dump doesn't use a backup format that is supported by the
pg_restore program. At every single talk that I give that talks about
pg_dump, the people in the majority of the audience agrees that this
is rather silly and a barrier to usability.

The only counter argument I have heard is that it adds an extra step
for developers to get the SQL that generates a database. That to me is
a weak argument as pg_dump is a production/admin too, first, not a
developer tool.

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

At least it gives you a nice message now instead of the rather cryptic
message it used to give:

[andrew@emma inst.5715]$ bin/pg_dump -f foo.sql
[andrew@emma inst.5715]$ bin/pg_restore foo.sql
pg_restore: [archiver] input file appears to be a text format dump.
Please use psql.

cheers

andrew

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

Joshua D. Drake <jd@commandprompt.com> wrote:

For as long as I can remember I have heard complaints about the fact
that pg_dump doesn't use a backup format that is supported by the
pg_restore program. At every single talk that I give that talks about
pg_dump, the people in the majority of the audience agrees that this is
rather silly and a barrier to usability.

The only counter argument I have heard is that it adds an extra step for
developers to get the SQL that generates a database. That to me is a
weak argument as pg_dump is a production/admin too, first, not a
developer tool.

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

-1 in the case that output goes to stdout.  It might make sense for
when -f is specified.  As a DBA, 90% of my usage of pg_dump was
piped to psql, with the rest split between data-only dumps for
individual tables and schema-only dumps.  I think I used -Fc about
twice -- before I figured out how to do PITR-style backups.

This, for example, is a valid and useful construct:

pg_dump test | psql test2

... or:

pg_dump -s test | psql test3

A lot of scripts will be broken by changing the default format for
piped output.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Magnus Hagander <magnus@hagander.net> writes:

On Thu, Nov 7, 2013 at 5:02 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

I'm a bit worried about it breaking peoples scripts as well, if they
pipe the output.

Whether they pipe the output or not, this will break people's scripts.
I agree it's a legacy behavior that we wouldn't choose again, but you
could say that about an awful lot of things including much of the SQL
standard. I think it's too late to consider this now.

regards, tom lane

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

On 11/07/2013 08:17 AM, Andrew Dunstan wrote:

At least it gives you a nice message now instead of the rather cryptic
message it used to give:

[andrew@emma inst.5715]$ bin/pg_dump -f foo.sql
[andrew@emma inst.5715]$ bin/pg_restore foo.sql
pg_restore: [archiver] input file appears to be a text format dump.
Please use psql.

cheers

True :) but still...

andrew

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

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

On 11/07/2013 08:21 AM, Kevin Grittner wrote:

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

-1 in the case that output goes to stdout. It might make sense for
when -f is specified. As a DBA, 90% of my usage of pg_dump was
piped to psql, with the rest split between data-only dumps for
individual tables and schema-only dumps. I think I used -Fc about
twice -- before I figured out how to do PITR-style backups.

PITR is wonderful, I use it all the time. It has exactly zero to do with
what we are talking about. We are talking about pg_dump and its
usefulness. Are you suggesting that we tell people to not use pg_dump
and instead use PITR?

This, for example, is a valid and useful construct:

pg_dump test | psql test2

... or:

pg_dump -s test | psql test3

pg_dump -s test -fp| psql test3

A lot of scripts will be broken by changing the default format for
piped output.

For the sake of sanity... really? You just proved my point. This isn't
about developers. Developers now how to code. They can change a simple
script. This is about production DBA/admins, the 98% of people using
postgresql.

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

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

On Thu, Nov 7, 2013 at 8:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Magnus Hagander <magnus@hagander.net> writes:

On Thu, Nov 7, 2013 at 5:02 PM, Joshua D. Drake <jd@commandprompt.com>

wrote:

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

I'm a bit worried about it breaking peoples scripts as well, if they
pipe the output.

Whether they pipe the output or not, this will break people's scripts.
I agree it's a legacy behavior that we wouldn't choose again, but you
could say that about an awful lot of things including much of the SQL
standard. I think it's too late to consider this now.

Just a thought: If it's the right thing to do, why not do it in small
steps?
For example, couldn't we deprecate the behavior on 9.4, and switch over in
9.5? By deprecate I mean a warning message on STDERR making it clear
they're relying on behavior that is scheduled to change.

I don't think there's any precedence in postgres or it's tooling of this
kind of
deprecation process, but it seems we need to define something like it.

Thoughts?

-H

On 11/07/2013 08:26 AM, Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

On Thu, Nov 7, 2013 at 5:02 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

I'm a bit worried about it breaking peoples scripts as well, if they
pipe the output.

Whether they pipe the output or not, this will break people's scripts.
I agree it's a legacy behavior that we wouldn't choose again, but you
could say that about an awful lot of things including much of the SQL
standard. I think it's too late to consider this now.

We have certainly done worse in the past. 8.3 comes to mind. I am trying
to consider the future here, as we continue to grow. Why leave thorns
for the sake of leaving thorns?

Note:

Perfectly happy to spit out a, "NOTICE: The default format of pg_dump
will be changing in release X" for a release.

Joshua D. Drake

regards, tom lane

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

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

On Thu, Nov 7, 2013 at 5:53 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 11/07/2013 08:21 AM, Kevin Grittner wrote:

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

-1 in the case that output goes to stdout. It might make sense for
when -f is specified. As a DBA, 90% of my usage of pg_dump was
piped to psql, with the rest split between data-only dumps for
individual tables and schema-only dumps. I think I used -Fc about
twice -- before I figured out how to do PITR-style backups.

PITR is wonderful, I use it all the time. It has exactly zero to do with
what we are talking about. We are talking about pg_dump and its usefulness.
Are you suggesting that we tell people to not use pg_dump and instead use
PITR?

For backups, absolutely.

That is, assuming you mean "pg_basebackup -x", and not full
archive_command/full pitr setup.

That's still a separate argument, of course.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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

On 11/07/2013 09:25 AM, Magnus Hagander wrote:

PITR is wonderful, I use it all the time. It has exactly zero to do with
what we are talking about. We are talking about pg_dump and its usefulness.
Are you suggesting that we tell people to not use pg_dump and instead use
PITR?

For backups, absolutely.

That is, assuming you mean "pg_basebackup -x", and not full
archive_command/full pitr setup.

That's still a separate argument, of course.

Please, for the love of God start another thread for that :P.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

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

On 11/07/2013 08:26 AM, Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

On Thu, Nov 7, 2013 at 5:02 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

I'm a bit worried about it breaking peoples scripts as well, if they
pipe the output.

Whether they pipe the output or not, this will break people's scripts.
I agree it's a legacy behavior that we wouldn't choose again, but you
could say that about an awful lot of things including much of the SQL
standard. I think it's too late to consider this now.

I don't agree that it's too late, personally. However, I do agree that
it would be problematic to change the default format since the default
is to send to STDOUT. If we wanted to change the default format, we
would need to make "filename" a required parameter.

If we wanted to change the defaults, I think it would be easier to
create a separate bin name (e.g. pg_backup) than to change the existing
parameters for pg_dump.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

On 11/07/2013 10:00 AM, Josh Berkus wrote:

On 11/07/2013 08:26 AM, Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

On Thu, Nov 7, 2013 at 5:02 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to -Fc.

I'm a bit worried about it breaking peoples scripts as well, if they
pipe the output.

Whether they pipe the output or not, this will break people's scripts.
I agree it's a legacy behavior that we wouldn't choose again, but you
could say that about an awful lot of things including much of the SQL
standard. I think it's too late to consider this now.

I don't agree that it's too late, personally. However, I do agree that
it would be problematic to change the default format since the default
is to send to STDOUT. If we wanted to change the default format, we
would need to make "filename" a required parameter.

If we wanted to change the defaults, I think it would be easier to
create a separate bin name (e.g. pg_backup) than to change the existing
parameters for pg_dump.

I am not opposed to that. Allow pg_dump to be what it is, and create a
pg_backup?

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats

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

On Thu, Nov 7, 2013 at 7:08 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On 11/07/2013 10:00 AM, Josh Berkus wrote:

On 11/07/2013 08:26 AM, Tom Lane wrote:

Magnus Hagander <magnus@hagander.net> writes:

On Thu, Nov 7, 2013 at 5:02 PM, Joshua D. Drake <jd@commandprompt.com>
wrote:

I humbly request on behalf of those who manage production postgresql
instances that we change the default backup file format from -Fp to
-Fc.

I'm a bit worried about it breaking peoples scripts as well, if they
pipe the output.

Whether they pipe the output or not, this will break people's scripts.
I agree it's a legacy behavior that we wouldn't choose again, but you
could say that about an awful lot of things including much of the SQL
standard. I think it's too late to consider this now.

I don't agree that it's too late, personally. However, I do agree that
it would be problematic to change the default format since the default
is to send to STDOUT. If we wanted to change the default format, we
would need to make "filename" a required parameter.

If we wanted to change the defaults, I think it would be easier to
create a separate bin name (e.g. pg_backup) than to change the existing
parameters for pg_dump.

I am not opposed to that. Allow pg_dump to be what it is, and create a
pg_backup?

If we want to go down that route, please let's pick a different name.
pg_backup is going to cause a *lot* of confusion in relation to
pg_basebackup for people.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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

On 2013-11-07 10:00:53 -0800, Josh Berkus wrote:

If we wanted to change the defaults, I think it would be easier to
create a separate bin name (e.g. pg_backup) than to change the existing
parameters for pg_dump.

Provided somebody is actually willing to put in the not inconsiderable
amount of work to make this smoother for users, something like pg_backup
would be a great idea. It should have an interface that allows to dump
global and local object and it should be able to dump multiple databases
in one invocation. Explaining users they have to use pg_dumpall for
global objects but that they also shouldn't dare to use it for anything
else is, well, difficult.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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

Josh Berkus <josh@agliodbs.com> writes:

If we wanted to change the defaults, I think it would be easier to
create a separate bin name (e.g. pg_backup) than to change the existing
parameters for pg_dump.

That might work. However, just to be contrary: what about the
functionality of pg_dumpall? We've heard repeated complaints about
pg_dump's lack of ability to dump multiple databases or global settings,
and just institutionalizing the existing -Fc format as the default
will make it harder not easier to fix that.

I'd be happier about inventing a pg_backup if it were a replacement for
pg_dumpall and not just a cosmetic wrapper.

regards, tom lane

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

On Thu, Nov 7, 2013 at 8:53 AM, Joshua D. Drake <jd@commandprompt.com>wrote:

On 11/07/2013 08:21 AM, Kevin Grittner wrote:

I humbly request on behalf of those who manage production postgresql

instances that we change the default backup file format from -Fp to -Fc.

-1 in the case that output goes to stdout. It might make sense for
when -f is specified. As a DBA, 90% of my usage of pg_dump was
piped to psql, with the rest split between data-only dumps for
individual tables and schema-only dumps. I think I used -Fc about
twice -- before I figured out how to do PITR-style backups.

PITR is wonderful, I use it all the time. It has exactly zero to do with
what we are talking about. We are talking about pg_dump and its usefulness.
Are you suggesting that we tell people to not use pg_dump and instead use
PITR?

That depends on what they are using it for. If they are using pg_dump to
do backups that are better done with PITR, then they should use PITR. When
I use pg_dump, it is because I want to see what it is that is getting
dumped--if I didn't want it be human readable, I'd use a binary backup
instead of a binary dump.

One of the things that first attracted me to PostgreSQL was that you use
the tool provided to dump the data, and what you get by default is
identifiably your data, rather than something that looks like it was
designed to lock you in to some proprietary tool and never let you out
again.

(Another thing that attracted me to it was that it didn't run around
renaming things between every release.)

This, for example, is a valid and useful construct:

pg_dump test | psql test2

... or:

pg_dump -s test | psql test3

pg_dump -s test -fp| psql test3

A lot of scripts will be broken by changing the default format for

piped output.

For the sake of sanity... really? You just proved my point. This isn't
about developers. Developers now how to code. They can change a simple
script. This is about production DBA/admins, the 98% of people using
postgresql.

I don't think that adding a command line option constitutes coding. If
they can't figure out how to do that for pg_dump, how are they ever going
to use pg_restore in the first place?

Cheers,

Jeff

On Thu, Nov 07, 2013 at 01:45:20PM -0500, Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

If we wanted to change the defaults, I think it would be easier to
create a separate bin name (e.g. pg_backup) than to change the
existing parameters for pg_dump.

That might work. However, just to be contrary: what about the
functionality of pg_dumpall? We've heard repeated complaints about
pg_dump's lack of ability to dump multiple databases or global
settings, and just institutionalizing the existing -Fc format as the
default will make it harder not easier to fix that.

I'd be happier about inventing a pg_backup if it were a replacement
for pg_dumpall and not just a cosmetic wrapper.

That the two are still separate is idiotic. If it were me waving this
wand, I'd see to it that -Fd (directory) rather than -Fc (custom), was
the default for pg_backup, as it makes the choice to use -j (>1 core)
trivial. I'd also make sure that the directory format could handle
the entirety of the backed-up database in some comprehensible way.
Maybe a globals/ and a databases/ directory up top...

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Joshua D. Drake <jd@commandprompt.com> wrote:

PITR is wonderful, I use it all the time. It has exactly zero to
do with what we are talking about. We are talking about pg_dump
and its usefulness. Are you suggesting that we tell people to not
use pg_dump and instead use PITR?

Yes.  (Well, actually, any of the WAL-based techniques.)  pg_dump
is not a good tool for primary backups, for many reasons.  At least
on databases over 50GB or so or which have more than a few data
modifications per second.  It is OK on a tiny 10GB database with
daily (or less frequent) batch modifications, but how often do you
see something like that?

This is about production DBA/admins, the 98% of people using
postgresql.

That is who I am thinking of.  A DBA team may have hundreds of
databases to manage, each with many scripts which have been running
nicely for years.  A change like this is bound to break some of
those crontab scripts they may not even remember they are running
-- like ones which dump a key table to INSERT statements to feed
into some other database product, which will now choke when it gets
a custom format file instead of the text file it has been getting
for years.  Requiring the DBA team to track all these scripts down
to add -Fp would be annoying, to put it mildly.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

If we wanted to change the defaults, I think it would be easier to
create a separate bin name (e.g. pg_backup) than to change the existing
parameters for pg_dump.

That might work. However, just to be contrary: what about the
functionality of pg_dumpall? We've heard repeated complaints about
pg_dump's lack of ability to dump multiple databases or global settings,
and just institutionalizing the existing -Fc format as the default
will make it harder not easier to fix that.

I'd be happier about inventing a pg_backup if it were a replacement for
pg_dumpall and not just a cosmetic wrapper.

Maybe we could provide a master controller program (pg_backup or
whatever name we agree on), which could receive commands much like
pg_ctl.

$ pg_backup --help
pg_backup is a backup handler program for PostgreSQL.

Usage:
pg_backup backup [-p PATTERN] DATABASE [...]
pg_backup restore [-f PATH]
pg_backup backup_globals
...

The "pattern" stuff would let you specify many databases in the command
line and have it dump each DB on a separate file, and perhaps do
strftime-style replacements, etc.

Initially we could have this just call pg_dump underneath, but
eventually it might grow superpowers of its own.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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