PostgreSQL upgrade server A -> server B

On Apr 26, 2007, at 20:09 , CAJ CAJ wrote:

Upgrading large postgres databases (100GB+) takes awfully long time
when doing dump/restore. I was wondering if this process can be
optimized by directly dumping to a new version of Pg database
directly on another server without having to dump to the filesystem
and then restore it.

From the fine documentation in the section entitled "Migration
Between Releases"

http://www.postgresql.org/docs/8.2/interactive/migration.html

The least downtime can be achieved by installing the new server in
a different directory and running both the old and the new servers
in parallel, on different ports. Then you can use something like

pg_dumpall -p 5432 | psql -d postgres -p 6543

to transfer your data.

Hope that helps.

Michael Glaesemann
grzm seespotcode net

Upgrading large postgres databases (100GB+) takes awfully long time
when doing dump/restore. I was wondering if this process can be
optimized by directly dumping to a new version of Pg database
directly on another server without having to dump to the filesystem
and then restore it.

From the fine documentation in the section entitled "Migration
Between Releases"

http://www.postgresql.org/docs/8.2/interactive/migration.html

The least downtime can be achieved by installing the new server in
a different directory and running both the old and the new servers
in parallel, on different ports. Then you can use something like

pg_dumpall -p 5432 | psql -d postgres -p 6543

to transfer your data.

Hope that helps.

Gee thanks... I guess i didn't RTFM!

see pg_migrator project which could help you.

Oleg
On Thu, 26 Apr 2007, CAJ CAJ wrote:

Hello,

Forgive me if this has been discussed before (or if it sounds absurd)

Upgrading large postgres databases (100GB+) takes awfully long time when
doing dump/restore. I was wondering if this process can be optimized by
directly dumping to a new version of Pg database directly on another server
without having to dump to the filesystem and then restore it.

pg_dump on new server might look something like,
pg_dump <options> -h <old server> -h <new_server> dbname

or can it be used as-is by piping it?

pg_dump <options> -h <old server> dbname | pg_restore

Thanks!

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On 27.04.2007 03:09, CAJ CAJ wrote:

Forgive me if this has been discussed before (or if it sounds absurd)

Upgrading large postgres databases (100GB+) takes awfully long time when
doing dump/restore. I was wondering if this process can be optimized by
directly dumping to a new version of Pg database directly on another server
without having to dump to the filesystem and then restore it.

pg_dump on new server might look something like,
pg_dump <options> -h <old server> -h <new_server> dbname

or can it be used as-is by piping it?

pg_dump <options> -h <old server> dbname | pg_restore

Maybe consider using slony to get your data over as well..

--
Regards,
Hannes Dorbath