Thread: Server Backup: pg_dump vs pg_dumpall
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.
Thanks for any advice.
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.
Thanks for any advice.
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: > 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.
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)