Re: Database maintenance help

On Sat, 14 Dec 2002, Jesus Sandoval wrote:

> following:
> 1) Backup the database data, pg_dump is of no use because if the table design
> changed then the COPY table FROM stdin produced by pg_dump needs to be
> modified to accomodate the space for the new columns.

Actually, you can use pg_dump with some special options.  I handle schema
changes like this:

1) Back up the database with a normal pg_dump, just in case.
2) Back it up again with pg_dump "-a -D"  -- This produces a bunch of
   INSERT INTO... statements
3) Drop the database
4) Recreate the database with the new schema
5) Restore from the dump in step (2).  As long as all columns in the old
   schema are still present in the new schema, it works.  If you need to
   get rid of a column, then in step (4), create a database with the unneeded
   column, and then do a step (6) whereby you drop unneeded columns.

The only problem is that restoring from (2) is slow; so I use a script to
add a "BEGIN...COMMIT" wrapper around the dump to speed it up.

--
David.