Feedback-ID: i76984098:Fastmail
Message-ID: <61af3fbe-40db-4900-9144-380a5b305c74@aklaver.com>
Date: Wed, 4 Sep 2024 15:20:53 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Issue with Restore dump with plpythonu, plpython3u installed on
 postgres16
To: Sam Son <sam89.g@gmail.com>
Cc: Muhammad Usman Khan <usman.k@bitnine.net>,
 pgsql-general@lists.postgresql.org
References: <CACW-z_UwPK8RP68ijs5Frn4+iREOSrN99-KFH6g0v3pFVVF1+w@mail.gmail.com>
 <CAPnRvGu-7B7J1i624KcJ-V2nUkuqGLsDT4Ae=EGbAPXJ=8A4fQ@mail.gmail.com>
 <f53c8d83-bf1b-4c04-a2ed-3ae5116aa582@aklaver.com>
 <a1ead53f-8348-4dbd-b9f1-6e9c4f2633f0@aklaver.com>
 <CACW-z_Xunr6cKL89h-uOE1sfvJ0E0thL+5k6NwT+B-PNq_=_=g@mail.gmail.com>
 <c35bd12e-a362-4089-9e80-a0696b3e9844@aklaver.com>
 <CACW-z_W2cf9O4ZJyPLYugBatBf3SDpQTPj2JSGD-9C8VH8N9ZA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CACW-z_W2cf9O4ZJyPLYugBatBf3SDpQTPj2JSGD-9C8VH8N9ZA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/61af3fbe-40db-4900-9144-380a5b305c74%40aklaver.com>
Precedence: bulk

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