Skip site navigation (1) Skip section navigation (2)

Re: Database maintenance help

From: Jesus Sandoval <meli(at)mzt(dot)megared(dot)net(dot)mx>
To: "David F(dot) Skoll" <dfs(at)roaringpenguin(dot)com>
Cc: jesus(at)merkatek(dot)com, pgsql-admin(at)postgresql(dot)org
Subject: Re: Database maintenance help
Date: 2002-12-15 01:14:39
Message-ID: 3DFBD77F.43CA3333@mzt.megared.net.mx (view raw or flat)
Thread:
Lists: pgsql-admin
"David F. Skoll" escribió:

> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Ok, thanks

I had no clue about where to begin with this. I'll try it.

btw, thanks for your contributions to the open source comunity, I'm actually using
the ppp with my ADSL Modem, and found your ssh presentation very interesting, I'm
using it to reach an internal net from a ISP provider (practical magic with ssh).

Jesus Sandoval


In response to

pgsql-admin by date

Next:From: Tom LaneDate: 2002-12-15 05:27:17
Subject: Re: Fixing stale locks
Previous:From: Chris WatersDate: 2002-12-14 18:44:04
Subject: Fixing stale locks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group