Server Backup: pg_dump vs pg_dumpall

APseudoUtopia wrote:

I'm writing a backup script. Right now, I only have one database on my
postgresql server. I'm deciding if I should use pg_dump or pg_dumpall
when backing up the server. As far as I can tell, pg_dumpall cannot
compress the dumps automatically and it only dumps data in the
standard SQL text file format. This means that I would not be able to
use pg_restore to selectively restore the database, correct?

What is *NOT* included in a pg_dump that IS in pg_dumpall (Other than
all the databases)? Things like user-defined functions and datatypes?
Roles? Views?

I was leaning towards pg_dumpall, but then I realized that it only
dumps in the standard SQL text file format, and it cannot be
compressed automatically.

Things that span all databases in your cluster (like your roles) do not
get saved with pg_dump. So yes, if you only have one database,
pg_dumpall is probably what you should be using. You can always pipe the
output of pg_dumpall to your compression program of choice.

Hi --

I'm writing a backup script. Right now, I only have one database on my postgresql server. I'm
deciding if I should use pg_dump or pg_dumpall when backing up the server. As far as I can tell,
pg_dumpall cannot compress the dumps automatically and it only dumps data in the standard SQL
text file format. This means that I would not be able to use pg_restore to selectively restore the
database, correct?

What is *NOT* included in a pg_dump that IS in pg_dumpall (Other than all the databases)?
Things like user-defined functions and datatypes? Roles? Views?

The pg_dumpall gets users and groups that are not dumped in the single database versions; I used
to use pg_dump on each of several databases on a given server and then pg_dumpall with suitable
parameters to get only those bits and nothing else. User defined stuff, views etc. are all in a given
database so they will be in the pg_dump.

HTH,

Greg Williamson

On Mon, Jul 20, 2009 at 4:23 PM, APseudoUtopia<apseudoutopia@gmail.com> wrote:

Hey,

I'm writing a backup script. Right now, I only have one database on my
postgresql server. I'm deciding if I should use pg_dump or pg_dumpall when
backing up the server. As far as I can tell, pg_dumpall cannot compress the
dumps automatically and it only dumps data in the standard SQL text file
format. This means that I would not be able to use pg_restore to selectively
restore the database, correct?

What is *NOT* included in a pg_dump that IS in pg_dumpall (Other than all
the databases)? Things like user-defined functions and datatypes? Roles?
Views?

I was leaning towards pg_dumpall, but then I realized that it only dumps in
the standard SQL text file format, and it cannot be compressed
automatically.

I use pg_dump on databases and pg_dumpall --globals to get the user
accounts and such.

APseudoUtopia wrote:

Hey,

I'm writing a backup script. Right now, I only have one database on my
postgresql server. I'm deciding if I should use pg_dump or pg_dumpall
when backing up the server. As far as I can tell, pg_dumpall cannot
compress the dumps automatically and it only dumps data in the
standard SQL text file format. This means that I would not be able to
use pg_restore to selectively restore the database, correct?

What is *NOT* included in a pg_dump that IS in pg_dumpall (Other than
all the databases)? Things like user-defined functions and datatypes?
Roles? Views?

views are part of a database, so they are included, so are user defined
functions. whats not included are, primarily, roles/users.

I was leaning towards pg_dumpall, but then I realized that it only
dumps in the standard SQL text file format, and it cannot be
compressed automatically.

pgdumpall .... | gzip > dumpfile.sql.gz

John R Pierce wrote:

APseudoUtopia wrote:
...

I was leaning towards pg_dumpall, but then I realized that it only
dumps in the standard SQL text file format, and it cannot be
compressed automatically.

pgdumpall .... | gzip > dumpfile.sql.gz

That deals with compression. But if you want to use the new
parallel-restore feature in 8.4 pg_restore which can be *way* faster if
you have multiple cores available then you will need to backup using
pg_dump with the custom format (-Fc).

Cheers,
Steve

On 2009-07-20, APseudoUtopia <apseudoutopia@gmail.com> wrote:

--001636c5b16936e279046f2a9776
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Hey,

I'm writing a backup script. Right now, I only have one database on my
postgresql server. I'm deciding if I should use pg_dump or pg_dumpall when
backing up the server. As far as I can tell, pg_dumpall cannot compress the
dumps automatically and it only dumps data in the standard SQL text file
format. This means that I would not be able to use pg_restore to selectively
restore the database, correct?

What is *NOT* included in a pg_dump that IS in pg_dumpall (Other than all
the databases)? Things like user-defined functions and datatypes? Roles?
Views?

roles mostly AIUI

I was leaning towards pg_dumpall, but then I realized that it only dumps in
the standard SQL text file format, and it cannot be compressed
automatically.

you can pipe its product through your favourite stream compressor (lzma, bzip2,
gzip etc)