Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1slp9q-001PcZ-P4 for pgsql-general@arkaria.postgresql.org; Wed, 04 Sep 2024 12:30:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1slp9p-0091Nx-6C for pgsql-general@arkaria.postgresql.org; Wed, 04 Sep 2024 12:30:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1slnYi-007wfe-Ec for pgsql-general@lists.postgresql.org; Wed, 04 Sep 2024 10:48:20 +0000 Received: from mail-wr1-x42b.google.com ([2a00:1450:4864:20::42b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1slnYc-0006cA-BG for pgsql-general@lists.postgresql.org; Wed, 04 Sep 2024 10:48:20 +0000 Received: by mail-wr1-x42b.google.com with SMTP id ffacd0b85a97d-374ca7a10d4so1806802f8f.3 for ; Wed, 04 Sep 2024 03:48:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725446892; x=1726051692; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=aT8zeVQxGzBM08WntBDTsw9U28lKCV+l30Z9RYUnw+U=; b=RJNnQN19vZNeAAeAjqeLwijn4/EpDarhmg1fAX+rfUHBbM8olComcFwNA8huCancwv Dvf+E+XvuzB8J84c698xiA4jqzlG74RTfMx89z5YZcilHSwpxqoIM1TJXFAEvlY6U2kr ejxPPuadb3SoJoiMsovGK3LIS+NKhBt5GRI/aYqOQYKdt4Ct4z8yEk6LRlkl3Dc8M+Dt WfXgIsLCCXXObDa6eK46Jt4W5SYbstMtlEKVbQUSgn/1tVmR9XLiIrN1lW58TTOL2mgS /fGoDMpL7oBSG67nllDS0gQ+pLDrfRv0ASyYBGMv688wrYEiocDH4lDeYsYYCvn6kEOi vhWA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725446892; x=1726051692; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=aT8zeVQxGzBM08WntBDTsw9U28lKCV+l30Z9RYUnw+U=; b=b8MrKQL9xH+Yiv1lKpFK+oa5QkTZ6P+IrxvC/ftDo7BSi36XW+b14/A9X1fP8GzHGi I5xVasaxXsgy8c5pnSjIoJKZv8IYJZHOHEAqH2rTaagXmWlIzVypfgGRh/iNoV2+Vxmc 2OuBChpOuH2S2pIC7wc3FdO8RRFOllCdVPbWhYMGW0ilXN32UCqVrgz3+tKftDhBmJsG ElXlhI7+yoJ/4x20gt4IcnpLhFk8p6q4PwE74npU5b0JKDRmFiguQQIRS6+czf0vfPX9 xi+mnz8C5t1epkqxl0lCheBERp2MnwncWTko0HkBJsXLW9iBlqU/GYvnU1MCMbLysbwZ 4f6A== X-Forwarded-Encrypted: i=1; AJvYcCUVy7WtmqG9drlae5w2CRKgAYPplezTeAURSU8zjvdAPxI2QvCP2Czi+9+9AOzh1VWpaq3Qp2Moxy/uzsMI@lists.postgresql.org X-Gm-Message-State: AOJu0YwWX2EtGwz2U6jESwko5UjnLYqvwzFyMT72fiM9dJ4uuYzV87Fn s1fHhpDKRcT85TeefAIZX4TIU9lb8HZFWXctdPOoCxtU1ASJeiLwwd35vTolZfjxVpZeXZV+smP 4DwQUc1BTrJifwrRsC1YaY8mdR34= X-Google-Smtp-Source: AGHT+IEEEuuuFN2VCZxkCC7KvSCFMGHdm3x3pkArY8YtXLUrEARfoGsIp0KdRXejRJKvJH5hWvcotMuXlrLbtkMzOBY= X-Received: by 2002:adf:e98f:0:b0:374:c287:2afe with SMTP id ffacd0b85a97d-374c2872bf0mr9133988f8f.56.1725446891906; Wed, 04 Sep 2024 03:48:11 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sam Son Date: Wed, 4 Sep 2024 16:18:00 +0530 Message-ID: Subject: Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16 To: Adrian Klaver Cc: Muhammad Usman Khan , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001233db062148e94f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001233db062148e94f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Adrian Klaver wrote: > 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 > > --0000000000001233db062148e94f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Adrian, Muhammad,

Thanks for the quick resp= onse.

For new I cannot do changes=C2=A0in old version DB, since it i= s deployed remotely=C2=A0and i dont have any access. And it has to be done = from multiple servers.

As a work=C2=A0around I tried two solutions.<= div>
Solution 1:

After downloading and extracting the dump= , convert the pgdump file to sql file which is editable.=C2=A0

=C2=A0 = =C2=A0 pg_restore -f out_dump.sql dump.pgdump

Replace all the plpyth= onu references with plputhon3u.=C2=A0

Restore using the sql file.

=C2=A0 = =C2=A0 sudo -H -u postgres psql -p 5433 -d db_name < =C2=A0out_dump.sql<= /span>


Solution 2:

After downlo= ading and extracting the dump, get the list of items in dump (Schemas, tabl= es, table data, Index, functions, etc).

= =C2=A0 =C2=A0 pg_restore -l dum= p.pgdump > dump.txt

Delete all the function references which have p= lpython3u.=C2=A0

Create a sql file which has functions with plpython3u extensio= ns.

= Load the sql file to db

=C2=A0 =C2=A0 sudo -H -u postgres psql -p 5433 = -d db_name < func.sql

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

=C2=A0 =C2=A0 sudo -H -u postgres pg_restore -p 5433 -j 8 --disable-trigge= rs --no-privileges -L dump.txt -d db_name=C2=A0dump.pgdump=C2=A0


Question:

= Our database size is 500GB,=C2=A0

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.=C2= =A0

Kindly recommend what to choose, solution 1 or solution 2 or an= y other workaround to restore.=C2=A0=C2=A0


Thanks,
Samson G<= /p>


On Tue, Sep 3, 2024 at 10:31=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com> wro= te:
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 su= re
>> 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 <= br> > plpythonu is no longer available:
>
> https://www.postgresql.org/docs/15/plp= ython-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 <= br> been removed.


Adrian Klaver
adrian.klave= r@aklaver.com

--0000000000001233db062148e94f--