Обсуждение: 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!
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
> 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