Subject: Re: Data migration from postgres 8.4 to 9.4
To: pgsql-performance@lists.postgresql.org
References: 
 <OFC9006B3F.4ED1F955-ON65258271.0025E96C-65258271.0026910D@tcs.com>
Cc: akshay.ballarpure@tcs.com
From: "Gunnar \"Nick\" Bluth" <gunnar.bluth.extern@elster.de>
Message-ID: <c28f4e1d-d674-92aa-1cd4-113dc15f1abb@elster.de>
Date: Mon, 16 Apr 2018 10:09:08 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.7.0
MIME-Version: 1.0
In-Reply-To: 
 <OFC9006B3F.4ED1F955-ON65258271.0025E96C-65258271.0026910D@tcs.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

Am 16.04.2018 um 09:03 schrieb Akshay Ballarpure:
> Hello,

Hi Akshay,

> I need help in using postgresql 8.4 data in postgres 9.4 version. Do I
> need to run any tool to achieve the same?

Yes. (-performance is probably the wrong place to ask though, please try
-general or -admin next time)

Please check the release notes before doing *any* upgrade, esp. when
skipping 4 major releases. They (among other very important information)
contain instructions how to upgrade:
https://www.postgresql.org/docs/current/static/release-9-4.html#id-1.11.6.48.4
You'll probably end up doing a pg_upgrade run (which is linked from the
above).

> 
> Steps i followed is ran postgresql 8.4 and 9.4, copied data from 8.4
> instance to 9.4 and try to start postgresql 9.4 but no luck, getting
> below error.
> 
> [root@ms-esmon esm-data]# su - postgres -c
> "/opt/rh/rh-postgresql94/root/usr/bin/postgres -D
> /var/ericsson/esm-data/postgresql-data/ 2>&1 &"
> [root@ms-esmon esm-data]# LOG:  skipping missing configuration file
> "/var/ericsson/esm-data/postgresql-data/postgresql.auto.conf"
> 2018-04-16 06:52:01.546 GMT  *FATAL*:  database files are incompatible
> with server
> 2018-04-16 06:52:01.546 GMT  *DETAIL*:  The data directory was
> initialized by PostgreSQL version 8.4, which is not compatible with this
> version 9.4.9.

That's exactly what's supposed to happen. The reasons are explained in
the pg_upgrade documentation.

BTW: Are you sure you want to go to 9.4? It is already rather outdated
and will go out of support "soon" (given that you're running 8.4, I have
to assume that your organisation requires quite some time to get an
upgrade cycle through the red band jungle). Unless you have very good
reasons not to, please consider going straight to 10, which will get you
almost 5 years of community support.

Best regards,
-- 
Gunnar "Nick" Bluth
DBA ELSTER
Extern im Auftrag der Hays AG

Tel:   +49 911/991-4665
Mobil: +49 172/8853339