Re: db not dumping properly, or at least not restoring

Hi Kirk,

How's it going?

You can use pg_dump on the local host to access a db on a remote host, & as the output is just SQL, pipe this directly intp a psql command, thus replicating/migrating a database.

One note, if you are doing this with a PostGIS db, I find it works better to create an empty target db with PostGIS installed first, then let the constraints on PostGIS objects prevent the old PostGIS being installed in the new db. Or you can copy over the old PostGIS & use the PostGIS upgrade SQL.

Cheers,

Brent

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Kirk Wythers <kwythers(at)umn(dot)edu> 10/17/09 4:15 PM >>>
On Oct 16, 2009, at 4:51 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
wrote:

> On Fri, Oct 16, 2009 at 11:25 AM, Kirk Wythers <kwythers(at)umn(dot)edu>
> wrote:
>
>> Any ideas what the problem could be here?
>
> Use the pg_dump from the target (i.e. newer) pgsql. I.e. if going
> from 8.3.8 to 8.4.1, use the pg_dump that comes with 8.4.1 to dump the
> 8.3.8 database.
>
Can I assume that this is even more critical if gong from 8.2 to 8.4?

> I usually just do it like so:
>
> (First migrate accounts:)
> pg_dumpall --globals -h oldserver | psql -h newserver postgres

I'm a little confused here. Are you saying to used the network
connections between thetwo servers and to pipe the dumpall directly to
the psql load?

> (then each database:)
> createdb -h newserver dbname

Then create new databases on the the new server to match the. The
names from the old server?

> pg_dump -h oldserver dbname | psql -h newserver dbname
> (repeat as needed, save output for error messages)

Then dump each database individually and pipe the dump to the psql load?

These two procedures seem to duplicate the goal? Or am I mosaic
something?

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.