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

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 <sam89.g@gmail.com> 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.

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

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

On Tue, Sep 3, 2024 at 10:31 PM Adrian Klaver <adrian.klaver@aklaver.com>
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
adrian.klaver@aklaver.com

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

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

Thanks Adrian,

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

Regards,
Samson G

On Thu, Sep 5, 2024 at 3:50 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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