Thread: PostgreSQL upgrade server A -> server B

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!

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

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