Thread: Issue with Restore dump with plpythonu, plpython3u installed on postgres16

On 9/2/24 04:37, Sam Son 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,

What versions of Postgres?

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

The best way I found to do this is install plpython3u in the old 
versions and convert your plpythonu functions to use plpython3u. Then 
you can drop plpythonu and do the dump/restore. This will also make you 
deal with the fact that Python2 != Python3 and your functions may need 
Python related changes as well.

> 
> *"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
adrian.klaver@aklaver.com

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 <sam89.g@gmail.com 
> <mailto:sam89.g@gmail.com>> 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
adrian.klaver@aklaver.com

On 9/3/24 09:37, Adrian Klaver wrote:
> 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.
> 

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
adrian.klaver@aklaver.com

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
adrian.klaver@aklaver.com

On 9/4/24 10:46, Sam Son wrote:
> Hi Adrian,
> 
> Thanks for your suggestions. I will try your modifications and do 
> benchmarking.

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



> 
> Thanks,
> Samson G


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.

> 
> Regards,
> Samson G

-- 
Adrian Klaver
adrian.klaver@aklaver.com