Обсуждение: How to upgrade postgres version 8 to 13
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.
Regards,
Shashidhar
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: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
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:
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
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