agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Jan Lentfer <Jan.Lentfer@web.de>
To: pgsql-admin@postgresql.org
Subject: Re: pg_upgrade
Date: Mon, 22 Jun 2015 16:31:59 +0200
Message-ID: <ac7a0f96eda265e9e9bf2aac5a577c33@imap.lan.net> (raw)
In-Reply-To: <1102220903.20150622162153@workfile.de>
References: <1862034146.20150622111438@workfile.de>
<e84cbc3cee08d8569cd670860c2b1900@imap.lan.net>
<5587E6F9.50702@afilias.info>
<1102220903.20150622162153@workfile.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
view thread (13+ messages) latest in thread
Message-ID: <ac7a0f96eda265e9e9bf2aac5a577c33@imap.lan.net>
Permalink: ../ac7a0f96eda265e9e9bf2aac5a577c33@imap.lan.net/
Also on: postgresql.org/message-id/ac7a0f96eda265e9e9bf2aac5a577c33@imap.lan.net
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Jan.Lentfer@web.de
Subject: Re: pg_upgrade
In-Reply-To: <ac7a0f96eda265e9e9bf2aac5a577c33@imap.lan.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox