public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sam Son <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Muhammad Usman Khan <[email protected]>
Cc: [email protected]
Subject: Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16
Date: Wed, 4 Sep 2024 23:16:50 +0530
Message-ID: <CACW-z_W2cf9O4ZJyPLYugBatBf3SDpQTPj2JSGD-9C8VH8N9ZA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CACW-z_UwPK8RP68ijs5Frn4+iREOSrN99-KFH6g0v3pFVVF1+w@mail.gmail.com>
<CAPnRvGu-7B7J1i624KcJ-V2nUkuqGLsDT4Ae=EGbAPXJ=8A4fQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CACW-z_Xunr6cKL89h-uOE1sfvJ0E0thL+5k6NwT+B-PNq_=_=g@mail.gmail.com>
<[email protected]>
Hi Adrian,
Thanks for your suggestions. I will try your modifications and do
benchmarking.
Thanks,
Samson G
On Wed, Sep 4, 2024 at 8:34 PM Adrian Klaver <[email protected]>
wrote:
> On 9/4/24 03:48, Sam Son wrote:
> > Hi Adrian, Muhammad,
> >
> > Thanks for the quick response.
> >
> > For new I cannot do changes in old version DB, since it is deployed
> > remotely and i dont have any access. And it has to be done from multiple
> > servers.
> >
> > As a work around I tried two solutions.
>
> Both of which depend on the plpythonu functions running with plpython3u,
> in other words that they are Python3 compatible. Have you verified that?
>
> >
> > *Solution 1:*
> >
> > After downloading and extracting the dump, convert the pgdump file to
> > sql file which is editable.
> >
> > * pg_restore -f out_dump.sql dump.pgdump*
> >
> > Replace all the plpythonu references with plputhon3u.
> >
> > Restore using the sql file.
> >
> > * sudo -H -u postgres psql -p 5433 -d db_name < out_dump.sql*
>
> I would suggest working on the schema portion separate from the data:
>
> pg_restore -s -f out_dump_schema.sql dump.pgdump*
>
> Do your search and replace, restore to database and then:
>
> pg_restore -a ... dump.pgdump*
>
> Where -a is data only.
>
> In fact if you have control of the pg_dump break it into two parts:
>
> pg_dump -s ... --schema
>
> pg_dump -a ... --data only
>
> >
> >
> > *Solution 2:*
> >
> > After downloading and extracting the dump, get the list of items in dump
> > (Schemas, tables, table data, Index, functions, etc).
> >
> > * pg_restore -l dump.pgdump > dump.txt*
> >
> > Delete all the function references which have plpython3u.
>
> I'm guessing you meant plpythonu above.
>
>
> > *Question:*
> >
> > Our database size is 500GB,
> >
> > Do we see any performance impact using solution 1. Since solution 1 is
> > using sql file load and solution 2 is using pg_restore directly.
> >
> > Kindly recommend what to choose, solution 1 or solution 2 or any other
> > workaround to restore.
>
> Personally I would go with solution 1 with the modifications I suggested.
>
> >
> >
> > Thanks,
> > Samson G
> >
>
>
> --
> Adrian Klaver
> [email protected]
>
>
view thread (9+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16
In-Reply-To: <CACW-z_W2cf9O4ZJyPLYugBatBf3SDpQTPj2JSGD-9C8VH8N9ZA@mail.gmail.com>
* 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