Data migration from postgres 8.4 to 9.4

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Data migration from postgres 8.4 to 9.4

akshay.ballarpure
Hello,
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?

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.


With Best Regards
Akshay
Ericsson OSS MON
Tata Consultancy Services
Mailto: [hidden email]
Website:
http://www.tcs.com
____________________________________________
Experience certainty.        IT Services
                       Business Solutions
                       Consulting
____________________________________________

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

Reply | Threaded
Open this post in threaded view
|

Re: Data migration from postgres 8.4 to 9.4

Gunnar "Nick" Bluth
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

Reply | Threaded
Open this post in threaded view
|

RE: Data migration from postgres 8.4 to 9.4

akshay.ballarpure
In reply to this post by akshay.ballarpure
Thank you for detailed info. much appreciated. May i know how to install pg_upgrade ?


With Best Regards
Akshay




From:        "Albin, Lloyd P" <[hidden email]>
To:        Akshay Ballarpure <[hidden email]>
Date:        04/16/2018 08:38 PM
Subject:        RE: Data migration from postgres 8.4 to 9.4




Akshay ,

There are several Official ways to upgrade PostgreSQL.

1) Use pg_upgrade (Faster) Postgres 8.4 to Postgres 9.4. Use the Postgres 9.4 version of pg_upgrade.

https://www.postgresql.org/docs/9.4/static/pgupgrade.html

2) Dump and Restore your database into a new server (Slower) Postgres 8.4 to Postgres 9.4 Use pg_dump with pg_restore or pg_dumpall with psql from Postgres 9.4 against your Postgres 8.4 Server. You need to use this method if you wish to change your initdb settings, such as the default encoding, turn on checksums, etc.

https://www.postgresql.org/docs/9.4/static/app-pgdump.html
https://www.postgresql.org/docs/9.4/static/app-pgrestore.html
https://www.postgresql.org/docs/9.4/static/app-pg-dumpall.html

3) Swap out the binaries. This can only be done using the same Postgres version (8.4.x or 9.4.x or 10.x) This means that you can upgrade from 9.4.9 to 9.4.12 by just swapping out the binaries.

4) Unofficially you can use things like slony, etc to do a live migration without downtime.

Lloyd




From: Akshay Ballarpure [[hidden email]]
Sent:
Monday, April 16, 2018 12:03 AM
Subject:
Data migration from postgres 8.4 to 9.4


Hello,
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?


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.


With Best Regards
Akshay
Ericsson OSS MON
Tata Consultancy Services
Mailto: [hidden email]
Website:
http://www.tcs.com
____________________________________________
Experience certainty.        IT Services
                      Business Solutions
                      Consulting
____________________________________________

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

Reply | Threaded
Open this post in threaded view
|

RE: Data migration from postgres 8.4 to 9.4

Ahmed, Nawaz

Hi,

 

pg_upgrade does not need installation, it comes with a PostgreSQL installation.

 

You will find it in  the bin directory of your 9.4 PostgreSQL installation.

 

 

Best Regards,

 

Nawaz Ahmed
Software Development Engineer

Fujitsu Australia Software Technology Pty Ltd

14 Rodborough Road, Frenchs Forest NSW 2086, Australia
T +61 2 9452 9027
[hidden email]
fastware.com.au


 

From: Akshay Ballarpure [mailto:[hidden email]]
Sent: Tuesday, 17 April 2018 10:24 PM
To: Albin, Lloyd P <[hidden email]>; [hidden email]; [hidden email]
Subject: RE: Data migration from postgres 8.4 to 9.4

 

Thank you for detailed info. much appreciated. May i know how to install pg_upgrade ?


With Best Regards
Akshay




From:        "Albin, Lloyd P" <[hidden email]>
To:        Akshay Ballarpure <[hidden email]>
Date:        04/16/2018 08:38 PM
Subject:        RE: Data migration from postgres 8.4 to 9.4





Akshay ,

There are several Official ways to upgrade PostgreSQL.

1) Use pg_upgrade (Faster) Postgres 8.4 to Postgres 9.4. Use the Postgres 9.4 version of pg_upgrade.
https://www.postgresql.org/docs/9.4/static/pgupgrade.html

2) Dump and Restore your database into a new server (Slower) Postgres 8.4 to Postgres 9.4 Use pg_dump with pg_restore or pg_dumpall with psql from Postgres 9.4 against your Postgres 8.4 Server. You need to use this method if you wish to change your initdb settings, such as the default encoding, turn on checksums, etc.
https://www.postgresql.org/docs/9.4/static/app-pgdump.html
https://www.postgresql.org/docs/9.4/static/app-pgrestore.html
https://www.postgresql.org/docs/9.4/static/app-pg-dumpall.html

3) Swap out the binaries. This can only be done using the same Postgres version (8.4.x or 9.4.x or 10.x) This means that you can upgrade from 9.4.9 to 9.4.12 by just swapping out the binaries.

4) Unofficially you can use things like slony, etc to do a live migration without downtime.

Lloyd



From: Akshay Ballarpure [[hidden email]]
Sent:
Monday, April 16, 2018 12:03 AM
Subject:
Data migration from postgres 8.4 to 9.4


Hello,
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?


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.



With Best Regards
Akshay
Ericsson OSS MON
Tata Consultancy Services
Mailto: [hidden email]
Website:
http://www.tcs.com
____________________________________________
Experience certainty.        IT Services
                      Business Solutions
                      Consulting
____________________________________________

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

Disclaimer

The information in this e-mail is confidential and may contain content that is subject to copyright and/or is commercial-in-confidence and is intended only for the use of the above named addressee. If you are not the intended recipient, you are hereby notified that dissemination, copying or use of the information is strictly prohibited. If you have received this e-mail in error, please telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9452 9000 or by reply e-mail to the sender and delete the document and all copies thereof.


Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly transmit a virus within an email communication, it is the receiver’s responsibility to scan all communication and any files attached for computer viruses and other defects. Fujitsu Australia Software Technology Pty Ltd does not accept liability for any loss or damage (whether direct, indirect, consequential or economic) however caused, and whether by negligence or otherwise, which may result directly or indirectly from this communication or any files attached.


If you do not wish to receive commercial and/or marketing email messages from Fujitsu Australia Software Technology Pty Ltd, please email [hidden email]