Thread: pg_dump estimation
is there a way to find out / calculate / estimate how big a pg_dump using plain text format for a DB will be ? I have this system with a 7.4 version and a DB that is over 60GB and I know that the admins have never done a vacuum there. The system only has about 20GB of free space so I don't want to take any chances of filling up the disk due to a pg_dump you know. thanks, Marcelo Linux/Solaris System Administrator pglists@zeroaccess.org http://www.zeroaccess.org
On Tue, Jun 24, 2008 at 11:13:14PM -0500, Marcelo Martins wrote: > is there a way to find out / calculate / estimate how big a pg_dump > using plain text format for a DB will be ? How about simply doing: pg_dump | wc -c Sam
Sam Mason wrote: > On Tue, Jun 24, 2008 at 11:13:14PM -0500, Marcelo Martins wrote: > >> is there a way to find out / calculate / estimate how big a pg_dump >> using plain text format for a DB will be ? >> > > How about simply doing: > > pg_dump | wc -c > > > Sam > > Hi Marcelo, If (using Sam's suggestion) the reported size of bytes is larger than (or even close to) the current free space, you may want to plug in an external drive (USB or otherwise) and mount it, then do the dump to it. If you intend to upgrade postgreSQL to 8.3.3, I think it may be advisable to first install postgreSQL 8.3.3 hopefully from source as you can have more control on where the executable will be placed (the idea is to avoid at least initially overwriting the current executables for postgres you have already deployed) use the --prefix=/your/path option to facilitate isolated installation. Then use pg_dump (or pg_dumpall) of the new postgreSQL installation to take the dump. In simpler words, use 8.3.x pg_dump to make a dump of a running instance of postgreSQL 7.x for later restore on postgreSQL 8.3.x. Allan.
In response to Allan Kamau <allank@sanbi.ac.za>: > Sam Mason wrote: > > On Tue, Jun 24, 2008 at 11:13:14PM -0500, Marcelo Martins wrote: > > > >> is there a way to find out / calculate / estimate how big a pg_dump > >> using plain text format for a DB will be ? > >> > > > > How about simply doing: > > > > pg_dump | wc -c > > > > > > Sam > > > > > Hi Marcelo, > > If (using Sam's suggestion) the reported size of bytes is larger than > (or even close to) the current free space, you may want to plug in an > external drive (USB or otherwise) and mount it, then do the dump to it. > > If you intend to upgrade postgreSQL to 8.3.3, I think it may be > advisable to first install postgreSQL 8.3.3 hopefully from source as you > can have more control on where the executable will be placed (the idea > is to avoid at least initially overwriting the current executables for > postgres you have already deployed) use the --prefix=/your/path option > to facilitate isolated installation. Then use pg_dump (or pg_dumpall) of > the new postgreSQL installation to take the dump. In simpler words, use > 8.3.x pg_dump to make a dump of a running instance of postgreSQL 7.x for > later restore on postgreSQL 8.3.x. Also, you can probably use CLUSTER to get the DB size down to something manageable. If you CLUSTER one table at a time, you can probably start with the smaller tables and free up enough room to CLUSTER the larger tables. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023