Re: [GENERAL] Server Backup: pg_dump vs pg_dumpall
On 2009-07-20, APseudoUtopia 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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server Backup: pg_dump vs pg_dumpall
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server Backup: pg_dump vs pg_dumpall
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server Backup: pg_dump vs pg_dumpall
On Mon, Jul 20, 2009 at 4:23 PM, 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? > > 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server Backup: pg_dump vs pg_dumpall
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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.
