Subject: Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16

From: Muhammad Usman Khan <[email-masked]>
Date: Tue, 3 Sep 2024 21:25:17 +0500

Hi,
You need to install plpython3u on your new server and also make sure to use
python3. You need to make changes to your functions also to make them
compatible with python3

On Tue, 3 Sept 2024 at 19:31, Sam Son <[email-masked]> wrote:

> Hi Team,
>
> I am working on a project where we have applications run on old versions
> of postgres, we are using *plpythonu* in all Functions extension,
>
> Now we have upgraded postgres to latest which does not support plpythonu
> anymore, So we are facing an issue while restoring dump which has
> plpythonu, but latest postgres support only plpython3u.
>
> *"pg_restore: error: could not execute query: ERROR:  language "plpythonu"
> does not exist"*
>
> How to change plpythonu to plpython3u in the dump file before pg_restore.
> or Does postgres have any option to handle directly via config.
>
> Thanks in Advance
>
> Thanks,
> Samson G
>

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 3 Sep 2024 09:37:02 -0700

On 9/3/24 09:25, Muhammad Usman Khan wrote:
> Hi,
> You need to install plpython3u on your new server and also make sure to 
> use python3. You need to make changes to your functions also to make 
> them compatible with python3

That only works if the dump from the old server is outputting 
plpython3u. If the plpythonu is the only version installed on the old 
server then that is what will be in the dump file. As of Postgres 15 
plpythonu is no longer available:

https://www.postgresql.org/docs/15/plpython-python23.html

This means if you are moving to 15+ you can't have any references to 
plpythonu.

> 
> 
> On Tue, 3 Sept 2024 at 19:31, Sam Son <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     Hi Team,
> 
>     I am working on a project where we have applications run on old
>     versions of postgres, we are using *plpythonu* in all Functions
>     extension,
> 
>     Now we have upgraded postgres to latest which does not support
>     plpythonu anymore, So we are facing an issue while restoring dump
>     which has plpythonu, but latest postgres support only plpython3u.
> 
>     *"pg_restore: error: could not execute query: ERROR:  language
>     "plpythonu" does not exist"*
> 
>     How to change plpythonu to plpython3u in the dump file before
>     pg_restore. or Does postgres have any option to handle directly via
>     config.
> 
>     Thanks in Advance
> 
>     Thanks,
>     Samson G
> 

-- 
Adrian Klaver
[email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 3 Sep 2024 10:01:21 -0700

On 9/3/24 09:37, Adrian Klaver wrote:
>> You need to install plpython3u on your new server and also make sure 
>> to use python3. You need to make changes to your functions also to 
>> make them compatible with python3
> 

In previous post I should have added:

https://www.postgresql.org/docs/15/release-15.html

Remove server-side language plpython2u and generic Python language 
plpythonu (Andres Freund)

Python 2.x is no longer supported. While the original intent of 
plpythonu was that it could eventually refer to plpython3u, changing it 
now seems more likely to cause problems than solve them, so it's just 
been removed.

Adrian Klaver

---

From: Sam Son <[email-masked]>
Date: Wed, 4 Sep 2024 16:18:00 +0530

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.

*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*

*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.

Create a sql file which has functions with plpython3u extensions.

Load the sql file to db

*    sudo -H -u postgres psql -p 5433 -d db_name < func.sql*

Now restore the dump with only the items in the edited dump.txt file
(Functions with plpython3u extensions removed).

*    sudo -H -u postgres pg_restore -p 5433 -j 8 --disable-triggers
--no-privileges -L dump.txt -d db_name dump.pgdump *

*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.

Thanks,
Samson G

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 4 Sep 2024 08:04:45 -0700

> 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.
> 

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

> Delete all the function references which have plpython3u.

I'm guessing you meant plpythonu above.

> Kindly recommend what to choose, solution 1 or solution 2 or any other 

Personally I would go with solution 1 with the modifications I suggested.

---

From: Sam Son <[email-masked]>
Date: Wed, 4 Sep 2024 23:16:50 +0530

Hi Adrian,

Thanks for your suggestions. I will try your modifications and do
benchmarking.

Thanks,
Samson G

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 4 Sep 2024 15:20:53 -0700

> Thanks for your suggestions. I will try your modifications and do 

I got to thinking and realized I missed an important part about 
separating the schema and data restores using the -s and -a arguments. 
This is best explained here:

https://www.postgresql.org/docs/current/app-pgrestore.html

--section=sectionname

     Only restore the named section. The section name can be pre-data, 
data, or post-data. This option can be specified more than once to 
select multiple sections. The default is to restore all sections.

     The data section contains actual table data as well as large-object 
definitions. Post-data items consist of definitions of indexes, 
triggers, rules and constraints other than validated check constraints. 
Pre-data items consist of all other data definition items.

With the modification I suggested the -s argument will result in:

-s
--schema-only

     Restore only the schema (data definitions), not data, to the extent 
that schema entries are present in the archive.

     This option is the inverse of --data-only. It is similar to, but 
for historical reasons not identical to, specifying --section=pre-data 
--section=post-data.

The issue being it includes post-data definitions as in:

"Post-data items consist of definitions of indexes, triggers, rules and 
constraints other than validated check constraints. "

That means when you restore the output of pg_restore -a the above items 
will be in place and will run. Among other things if there are trigger 
functions using plpython3u and said functions are not Python3 valid they 
will fail. You might also get warnings like:

"
pg_dump: warning: there are circular foreign-key constraints on this table:
pg_dump: detail: equipment
pg_dump: hint: You might not be able to restore the dump without using 
--disable-triggers or temporarily dropping the constraints.
pg_dump: hint: Consider using a full dump instead of a --data-only dump 
to avoid this problem.
"

You might be better off using something like:

pg_restore ... --section=pre-data  -f ddl_defs.sql
Search/replace ddl_defs.sql
psql ... -f ddl_defs.sql
pg_restore ... --section=data
pg_restore ... --section=post-data

---

From: Sam Son <[email-masked]>
Date: Fri, 6 Sep 2024 18:28:00 +0530

Thanks Adrian,

I have tried your previous workaround and it worked fine. I will try this
approach as well.

Regards,
Samson G

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 6 Sep 2024 12:58:58 -0700

On 9/6/24 05:58, Sam Son wrote:
> Thanks Adrian,
> 
> I have tried your previous workaround and it worked fine. I will try 
> this approach as well.

If the previous solution(-s/-a) works then my update should work also. I 
offered it as possible solution should you run into out of sequence 
issues using -s/-a.