Am 2015-06-22 16:21, schrieb Rainer Leo:
we are still using PostgreSQL 9.0.2 on Windows Server.
Now we are migrating to Windows Server 2012 R2 and we
would like to migrate PostgreSQL at the same time to
the current version 9.4.4-1
Which is the best way to migrate the data?
1. pg_dump on the old server
2. pg_retore on the new server
3. pg_upgrade on the new server
Is this correct or is there a "best procedure" to do this?
You do either 1 + 2 OR 3. pg_upgrade is binary upgrade, where as
pg_dump + pg_restore is "logical" (dump data and schemal to SQL
instructions). If you go that way also check pg_dumpall for dumping
the globals.
Regards
Jan
Also, for 1+2 you would be advised to do the pg_dump/restore using
the
*new* binaries (9.4), things could get tricky otherwise...
Ziggy
Thanks for your help.
Using the 9.4 pg_dump on the old server did not work (missing
libintl-8.dll), so I used the 9.0 pg_dump.
pg_restore on the new server worked fine, BUT the perfomance is
lousy, for example a query that took 1732ms on the old server now
takes longer than 32000ms every time on 9.4
I tuned the postgres.conf exactly like the old one, except for more
RAM in some parameters.
Does this mean I have to install 9.4 on the old server so I can use
pg_upgrade?
That won't make a difference regarding resulting performance.
Did you run ANALYZE after pg_restore? Also, did you run the query more
than once? The new system is "cold" (caches are empty). It will take
some time (depends on your amount of data and RAM, etc) until everything
is properly loaded.
Did you compare EXPLAIN outputs on both systems (only makes sense after
running ANALYZE)?
Do the systems differ in any other way, especially storage?
Jan
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
