How to upgrade postgres version 8 to 13

On 7/5/22 9:54 AM, shashidhar Reddy wrote:

Hello,

Could some please let me know how to upgrade postgresql version 8.2 to
13, as the database size is 20 TB, backup and restore is not possible.

This is going to need more information:

1) Are you looking to upgrade on same machine or between machines.

2) What are the hardware specs for the machine or machines?

3) If between machines:
a) The OS and version of each.
b) The network distance between them

4) What is the desired down time limit?

Regards,
Shashidhar

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/5/22 10:48 AM, shashidhar Reddy wrote:

Reply to list also

Ccing list

Hello Adrian,

Os is Ubuntu  not sure about the version I am looking for the low
downtime method it may be on same or different machine but same machine
is preferable

Hardware specs?

How low is low for downtime?

--
Adrian Klaver
adrian.klaver@aklaver.com

Down time 10 to 15 hrs
Hardware specs are also not sure for now as it is client machine.

On Tue, 5 Jul, 2022, 11:22 pm Adrian Klaver, <adrian.klaver@aklaver.com>
wrote:

On 7/5/22 10:56 AM, shashidhar Reddy wrote:

Down time 10 to 15 hrs
Hardware specs are also not sure for now as it is client machine.

This is going to be a difficult task for the following reasons:

1) pg_upgrade(https://www.postgresql.org/docs/current/pgupgrade.html)
only goes back to 8.4. So you will need to use other tools if you want
to emulate it.

2) 8.2 --> 13 represent 14 years of changes. In particular the implicit
type cast changes that happened in 8.3. You will need to testing to
determine whether what you are doing in 8.2 is going to work in 13. Also
why version 13?

3) The size of the data set.

In other words there is going to be a lot of preliminary work and
testing that is going to need to be done before you even start the
transfer. The first of that is determining what your setup is. My guess
is you will end up doing this in stages of using intervening Postgres
versions.

On Tue, 5 Jul, 2022, 11:22 pm Adrian Klaver, <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 7/5/22 10:48 AM, shashidhar Reddy wrote:

Reply to list also

Ccing list

Hello Adrian,

Os is Ubuntu  not sure about the version I am looking for the low
downtime method it may be on same or different machine but same

machine

is preferable

Hardware specs?

How low is low for downtime?

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Jul 5, 2022 at 11:05 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 7/5/22 10:56 AM, shashidhar Reddy wrote:

Down time 10 to 15 hrs
Hardware specs are also not sure for now as it is client machine.

This is going to be a difficult task for the following reasons:

In other words there is going to be a lot of preliminary work and
testing that is going to need to be done before you even start the
transfer. The first of that is determining what your setup is. My guess
is you will end up doing this in stages of using intervening Postgres
versions.

I would seriously try and make this work by performing, even if not
entirely consistent, a dump of the minimal subset of the database needed to
meet most of the day-to-day needs of the applications using the database.
Create a v13 (or whatever supported version you want really) database
schema from scratch (dump schema only restore or whatever you've got in
version/migration control). Load the data into it and make sure that
everything is working properly. Then, resync the newest stuff and go live
while incurring additional downtime for migrating the likely decade plus of
historical data that is out there probably generally unused. Setting up
partitions, or maybe even some separate archival database to be linked into
the new one via Foreign Data Wrappers, can also be considered.

David J.

I had a client that was stuck on 8.2 for a long time, when they finally
upgraded to 10, it took several weeks of testing to find things that needed
to be changed in scripts, functions and PHP programs. And even then we
were still finding type cast issues for another year in seldom-used SQL
code.

You need to do your advance work on this conversion.
--
Mike Nolan

Thank you all for the inputs, any one tried upgrading from 8.2 to 8.4 and
then used pg_upgrade to upgrade it to any higher version.

On Wed, 6 Jul, 2022, 12:01 am Michael Nolan, <htfoot@gmail.com> wrote:

On Tue, 2022-07-05 at 22:24 +0530, shashidhar Reddy wrote:

Could some please let me know how to upgrade postgresql version 8.2 to 13,
as the database size is 20 TB, backup and restore is not possible.

Then you will probably have to use Slony, and pray to a deity of your choice.

Yours,
Laurenz Albe