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 1slu64-001r8i-CL for pgsql-general@arkaria.postgresql.org; Wed, 04 Sep 2024 17:47:12 +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 1slu62-00CxHq-Pm for pgsql-general@arkaria.postgresql.org; Wed, 04 Sep 2024 17:47:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1slu62-00CxHN-83 for pgsql-general@lists.postgresql.org; Wed, 04 Sep 2024 17:47:10 +0000 Received: from mail-wm1-x333.google.com ([2a00:1450:4864:20::333]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1slu5w-00098z-5h for pgsql-general@lists.postgresql.org; Wed, 04 Sep 2024 17:47:09 +0000 Received: by mail-wm1-x333.google.com with SMTP id 5b1f17b1804b1-428e0d184b4so56837365e9.2 for ; Wed, 04 Sep 2024 10:47:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725472022; x=1726076822; 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=quBmaANQZknSnPMC3nEgFFTsH71b6lLpRpCnhubjuFc=; b=V9qE0h6UxZErgWFvGHIdZKuOiKr8NQfrPBPeLtyELNH4qm8o9Y02w1dquuzC8vNUOd j4kteMRCF29f5Tn+cxdY7dqgZauAzJHpz3Z1fJlBTVDbhJ+3g0EqaCOzmE0oQ8wyi9cq fq2dx69KeIeDAvg5DlsrEU5EPuFjN51dRBNuVQ5e1hLxHRQ6P7Fwtcd7xQhStFW6N5az l2py5f/ot5Stcs4vXrDDwAYlLIDq9VOmIQfJQayWpC/rkKuu/qBU7PveIgBYTmTOHCA9 7ICmhCY6WWOKIbG9SiSdltxj6sPV2/Yl6SFlfRQQCHlnX05IwI1lxGa7UDfDAB7jhAuR JUzw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725472022; x=1726076822; 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=quBmaANQZknSnPMC3nEgFFTsH71b6lLpRpCnhubjuFc=; b=pox4m86bkeYcaVtDiwURnH+xzdKvvPtDBlKYs/cuIsZqiK/uqDVMWdsFI0oa9stsLj raRDfPMxM1XY3xnOPB+0LuHTtEVIpVynsqNZ+VIHfaMicGSyG7iNWCFVnuahVGLG0j7e qaSl29gZkuGdi1zTzfoY3hrW+adraHxAtb5dALjy8Nai6A+XCy4CxXNQyrV2R6ORin+M wVzzWy2AogmtN1/AoMPfn6pQXLoV3Of5wqJ0BsjkVCQUeUIOYmMk3jhRVCYHaINT0HIJ wx+cFf9KtmcTwkusmj36sH7zDuOCq/MS9D3V2akLJksxznUX55NheBLZ917VZMMEdGvp +gKw== X-Forwarded-Encrypted: i=1; AJvYcCVv61Yw3Sn6uuepaawX8NXXGNlJ4eqlh2CYBpNYyjXUPlWtN16qA0RZU1PThVjE41Iy5KEDF7dkkPYyFGeN@lists.postgresql.org X-Gm-Message-State: AOJu0YwQ+hOCkHXERvpdyYGzX6AHhHG11cwMI0gNUgm75uCDA+0cp1vh lwF34QRUymCWABVA9lkOZiJ5EARQKFUBeVCfh8h01lUgw7v+4lqxSrJ4xy5xHT1wv/YetRGfCvw 4A/+FmSz62EC7/D/YJ9uT7J+ZGo8LLgBj4MM= X-Google-Smtp-Source: AGHT+IGV7wE0rLDtwPDSv+HyjQrXNDw6f6oB8EWGW2A/gaqsVeW7KduqLDpHEPLDrH3sfMB7XGeIJCZaL3Oop6GsKBg= X-Received: by 2002:a5d:6345:0:b0:371:c518:6f54 with SMTP id ffacd0b85a97d-374bf169505mr10192306f8f.29.1725472022156; Wed, 04 Sep 2024 10:47:02 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sam Son Date: Wed, 4 Sep 2024 23:16:50 +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="000000000000f3620f06214ec258" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f3620f06214ec258 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Adrian Klaver 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 multipl= e > > 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 dum= p > > (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 > > --000000000000f3620f06214ec258 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
=
On 9/4/24 03:48, Sam Son = wrote:
> Hi=C2=A0Adrian, Muhammad,
>
> Thanks for the quick response.
>
> For new I cannot do changes=C2=A0in old version DB, since it is deploy= ed
> remotely=C2=A0and i dont have any access. And it has to be done from m= ultiple
> servers.
>
> As a work=C2=A0around 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.
>
> *=C2=A0 =C2=A0 pg_restore -f out_dump.sql dump.pgdump*
>
> Replace all the plpythonu references with plputhon3u.
>
> Restore using the sql file.
>
> *=C2=A0 =C2=A0 sudo -H -u postgres psql -p 5433 -d db_name < =C2=A0= 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 ...=C2=A0 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 ...=C2=A0 --schema

pg_dump -a ...=C2=A0 --data only

>
>
> *Solution 2:*
>
> After downloading and extracting the dump, get the list of items in du= mp
> (Schemas, tables, table data, Index, functions, etc).
>
> *=C2=A0 =C2=A0 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.klave= r@aklaver.com

--000000000000f3620f06214ec258--