Postgres Pro
Downloads
Home   >   mailing lists

Thread: Excluding a table from pg_dump

Excluding a table from pg_dump

From
Kari Lavikka
Date:
Hi,

I think there has been some talk about this missing feature since 2001,
but anyway..

I have a database which contains about 500M rows and the nightly dump
takes about six hours. Size of the compressed dump is ~30GB.

The biggest tables are:
               relname            |  reltuples
---------------------------------+-------------
  comment_archive                 | 2.45995e+08
  comment                         | 1.68875e+08

Comments are messages written by users and they are "partitioned" into two
tables. New messages are always written to "comment" and once a month the
oldest messages are moved to "comment_archive". For performance reasons
neither of these tables have foreign keys.

Contents of "comment_archive" are quite static. It's frequently read but
rarely written. Backup each night is quite useless and it steals time from
other essential maintenance tasks.

The database contains several schemas and excluding "comment_archive" by
moving it to different schema doesn't sound very convenient. pg_dump
doesn't have an option to dump multiple schemas at once.

Are there any working "-X" patches for pg_dump or does anyone have other
possible solutions?

     |\__/|
     ( oo )    Kari Lavikka - tuner@bdb.fi - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
       ""

Re: Excluding a table from pg_dump

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> The database contains several schemas and excluding "comment_archive" by
> moving it to different schema doesn't sound very convenient. pg_dump
> doesn't have an option to dump multiple schemas at once.
>
> Are there any working "-X" patches for pg_dump or does anyone have other
> possible solutions?

The next version of Postgres (8.2) will have the ability to do everything
you want. It's active now in cvs, if you want to try it out. You can exclude
one or more tables with the -T flag, and can include or exclude schemas
with the -n and -N resepectively. Any of the four flags can be used multiple
times, and they all accept POSIX-style regular expressions as well.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200608150821
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFE4bxzvJuQZxSWSsgRAgYPAJ9qa/jE5oHY/DMOGNfuHsoVgiwf4gCgjnHW
FqZF5l51h4j/ul+dK7M90DE=
=VSi/
-----END PGP SIGNATURE-----

Re: Excluding a table from pg_dump

From
Ron St-Pierre
Date:
Greg Sabino Mullane wrote:
>> The database contains several schemas and excluding "comment_archive" by
>> moving it to different schema doesn't sound very convenient. pg_dump
>> doesn't have an option to dump multiple schemas at once.
>>
>> Are there any working "-X" patches for pg_dump or does anyone have other
>> possible solutions?
>>
>
> The next version of Postgres (8.2) will have the ability to do everything
> you want. It's active now in cvs, if you want to try it out. You can exclude
> one or more tables with the -T flag, and can include or exclude schemas
> with the -n and -N resepectively. Any of the four flags can be used multiple
> times, and they all accept POSIX-style regular expressions as well.
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> End Point Corporation
>

That's excellent news! I've needed this feature for a while now too.

Regards

Ron St.Pierre