Modified pg_dump & new pg_restore need testing...

Sounds like something that is a suitable replacement for what we have now
... I particularly like the 'compressed output' *but* is there some way,
for the paranoid, of doing an uncompressed format
(--dont-compress?)? Then again, how does this handle someone wanting to
dump the database for reloading into a non-PgSQL database?

On Fri, 30 Jun 2000, Philip Warner wrote:

If anyone is interested in being sent the current sources for the new
pg_dump & pg_restore, please let me know.

The utilities now seem to work, but need testing.

The basic idea is to use pg_dump to dump an *entire* database, and then use
pg_restore to choose what is restored.

The salient features are as follows:

- pg_dump still used to dump database; all output is via new interface
(virtually all of the pg_dump code is changed, but not the logic). The
changes are relatively minor, all the same.

- the '-c' option is not used in pg-dump: it now dumps the commands to
delete the schema, and it is up to the user of pg_restore to decide if they
are output.

- the default output file format is a custom format with compressed
sections (the data dumps). It is NOT a text file.

- pg_restore reads the backup file and, depending on the options chosen,
produces a script (to stdout) that can be sent to psql.

- by default pg_restore outputs the schema/data in the order it was sent
from pg_dump, but the --oid flag will send the output in order of
increasing OID, and the --rearrange flag will put all 'non-parental' (??)
items at the end, after the data. (eg. indexes, acls, triggers etc).
Needless to say that the best results com from using both of these options.

- If the -c (clear) option is chosen in pg_restore, it also dumps the
'drop' commands in reverse order at the start of the script. This *should*
make it more reliable than dumping them when the item is defined. It also
means that triggers can be dropped.

- The --toc option shows a summary of the restore operation that would be
performed if the --toc were not there.

Please send me an email if you are interested and have the time to test them.

Thanks,

Philip Warner.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

At 13:53 30/06/00 -0300, The Hermit Hacker wrote:

Sounds like something that is a suitable replacement for what we have now
... I particularly like the 'compressed output' *but* is there some way,
for the paranoid, of doing an uncompressed format
(--dont-compress?)? Then again, how does this handle someone wanting to
dump the database for reloading into a non-PgSQL database?

There will be; you can specify -z0 on the pg_dump, but it still has binary
junk in the output. I will add a '--plain-text, -p' option to dump it into
text. You can also use pg_restore to dump the file to text.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

At 13:53 30/06/00 -0300, The Hermit Hacker wrote:

Sounds like something that is a suitable replacement for what we have now
... I particularly like the 'compressed output' *but* is there some way,
for the paranoid, of doing an uncompressed format
(--dont-compress?)? Then again, how does this handle someone wanting to
dump the database for reloading into a non-PgSQL database?

As suggested I have now added a '--plain-text, -p' option to pg_dump to
dump it into text. Needless to say the output can not be used by pg_restore
(although, I suppose, it is feasible). This also supports the -Z flag, to
dump in gzip format.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

[ Charset ISO-8859-1 unsupported, converting... ]

Philip Warner writes:

As suggested I have now added a '--plain-text, -p' option to pg_dump
to dump it into text.

The *default* action of pg_dump should be to write plain text and to
standard output. Changing that will certainly make a lot of people very
unhappy.

Agreed. We need a enable-compress switch.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Philip Warner writes:

As suggested I have now added a '--plain-text, -p' option to pg_dump
to dump it into text.

The *default* action of pg_dump should be to write plain text and to
standard output. Changing that will certainly make a lot of people very
unhappy.

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

At 23:23 1/07/00 +0200, Peter Eisentraut wrote:

As suggested I have now added a '--plain-text, -p' option to pg_dump
to dump it into text.

The *default* action of pg_dump should be to write plain text and to
standard output. Changing that will certainly make a lot of people very
unhappy.

Good point; I'll make it the default. The -Z {0..9] switch and -F {p|c|f}
switches can be used to select output compression levels and output format.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

- the default output file format is a custom format with compressed
sections (the data dumps). It is NOT a text file.

Can this be turned off, or made to be feature, that you can turn on ?
Imho most dumps will be piped to a locally
optimized compressor (a tape, a storage manager, lzop, ...) anyway,
thus most of the time a backup would compress twice.

Andreas

At 09:56 3/07/00 +0200, Zeugswetter Andreas SB wrote:

- the default output file format is a custom format with compressed
sections (the data dumps). It is NOT a text file.

Can this be turned off, or made to be feature, that you can turn on ?
Imho most dumps will be piped to a locally
optimized compressor (a tape, a storage manager, lzop, ...) anyway,
thus most of the time a backup would compress twice.

Yes. The default is now plain text for compatibility with the original
pg_dump (and with pg_dumpall). But by going to plain text output you gain
none of the features of pg_restore (reordering, selection etc).

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

As suggested I have now added a '--plain-text, -p' option to pg_dump
to dump it into text.

The *default* action of pg_dump should be to write plain text and to
standard output. Changing that will certainly make a lot of
people very unhappy.

Imho the default should be text for anything except data. The data should
imho
be in a format similar to a binary cursor. I say similar, because this
format should
probably be converted to a network byte order, so you can restore on another
hardware. Imho calling type output and input functions during backup/restore
is a substantial overhead that would best be avoided.

I agree, that the output should not be compressed by default.

Andreas

At 10:18 3/07/00 +0200, Zeugswetter Andreas SB wrote:

Imho the default should be text for anything except data. The data should
imho
be in a format similar to a binary cursor. I say similar, because this
format should
probably be converted to a network byte order, so you can restore on another
hardware. Imho calling type output and input functions during backup/restore
is a substantial overhead that would best be avoided.

Have a look at the utilities. I have attempted to make the custom format
transportable (at least as transportable as pg_dump). If anyone has a 64
bit machine with weird byte order I'd love them to test restoring a backup
made on another machine. If it does not work, I am happy to make it work
(at least as well as pg_dump works).

It just occurred to me that I may be missing something...do you mean you
would prefer to avoid the 'COPY' commands as the backup technique? If so, I
think that's best left to somebody else (or me, but not now, and probably
not until the WAL is implemented).

Also, defining your own output format is pretty easy; the new code defines
a top level interface (for pg_dump and pg_restore) and an archiver
interface (for output file formats), if you really want a different format.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

Imho the default should be text for anything except data. The data
should imho be in a format similar to a binary cursor. I say similar,
because this format should probably be converted to a network byte
order, so you can restore on another hardware. Imho calling type
output and input functions during backup/restore is a substantial
overhead that would best be avoided.

I think this would be an extremely *bad* idea. One of the principal
functions of pg_dump is to provide a portable representation of data;
that is, portable across machines and across Postgres versions (where
the internal representation of data may change!).

Not only should binary data representation not be the default, IMHO
we shouldn't offer it as an option either. Otherwise dbadmins will
promptly shoot themselves in the foot with it. Do you want to field
the support calls saying "help, I already blew away my 7.n installation
but 7.n+1 won't read my pg_dump backup"?

regards, tom lane

Philip, where did we leave this?

If anyone is interested in being sent the current sources for the new
pg_dump & pg_restore, please let me know.

The utilities now seem to work, but need testing.

The basic idea is to use pg_dump to dump an *entire* database, and then use
pg_restore to choose what is restored.

The salient features are as follows:

- pg_dump still used to dump database; all output is via new interface
(virtually all of the pg_dump code is changed, but not the logic). The
changes are relatively minor, all the same.

- the '-c' option is not used in pg-dump: it now dumps the commands to
delete the schema, and it is up to the user of pg_restore to decide if they
are output.

- the default output file format is a custom format with compressed
sections (the data dumps). It is NOT a text file.

- pg_restore reads the backup file and, depending on the options chosen,
produces a script (to stdout) that can be sent to psql.

- by default pg_restore outputs the schema/data in the order it was sent
from pg_dump, but the --oid flag will send the output in order of
increasing OID, and the --rearrange flag will put all 'non-parental' (??)
items at the end, after the data. (eg. indexes, acls, triggers etc).
Needless to say that the best results com from using both of these options.

- If the -c (clear) option is chosen in pg_restore, it also dumps the
'drop' commands in reverse order at the start of the script. This *should*
make it more reliable than dumping them when the item is defined. It also
means that triggers can be dropped.

- The --toc option shows a summary of the restore operation that would be
performed if the --toc were not there.

Please send me an email if you are interested and have the time to test them.

Thanks,

Philip Warner.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

At 21:01 10/10/00 -0400, Bruce Momjian wrote:

Philip, where did we leave this?

In CVS. ;-).

The longer answer is that it's been in CVS for a while, and I have a
version for 7.0.2 which I have also been using for a while. Various people
have tested it, but not as many or as much as I would like (or lots of
people have tested it with no problems, which seems unlikely).

The docs for pg_dump are with Thomas and I am about to start working on an
unrelated bug in the way pg_dump handles sequences. When that's done, I
will document pg_restore. Hopefully, all in time for beta...

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/