pgcopydb fail when more than one role in function acl

[Medzon888]

Hello, I got a problem when pgcopydb copy\clone\restore failed when one or more acl on function exists. Error is:

11:42:38.649 50111 NOTICE pgcmd.c:1065               /usr/bin/pg_restore -f /tmp/clone_blob8/schema/pre-out.list -l /tmp/clone_blob8/schema/schema.dump
11:42:38.775 50111 NOTICE dump_restore.c:731        Skipping already existing dumpId 12: SCHEMA 46906 - debezium_heartbeat postgres
11:42:38.776 50111 ERROR  pgcmd.c:1484              Failed to parse Archive TOC dumpId in: recision, OUT wal_records bigint, OUT wal_fpi bigint, OUT wal_bytes numeric, OUT jit_functions bigint, OUT jit_generation_time double precision, OUT jit_inlining_count bigint, OUT jit_inlining_time double precision, OUT jit_optimization_count bigint, OUT jit_optimization_time double precision, OUT jit_emission_count bigint, OUT jit_emission_time double precision) postgres
11:42:38.776 50111 ERROR  dump_restore.c:661        Failed to prepare the pg_restore list file, see above for details
11:42:38.776 50111 ERROR  dump_restore.c:186        Failed to prepare the pg_restore --use-list catalogs, see above for details
11:42:38.776 50111 ERROR  cli_clone_follow.c:596    Failed to prepare schema on the target database, see above for details
11:42:38.776 50111 ERROR  cli_clone_follow.c:504    Failed to clone source database, see above for details

I see that error is in pg_restore part and its for pre-out.list file. And a problem string is 46635; 0 0 ACL public FUNCTION pg_stat_statements(showtext boolean, OUT userid oid, OUT dbid oid, OUT toplevel boolean, OUT queryid bigint, OUT query text, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision, OUT temp_blk_read_time double precision, OUT temp_blk_write_time double precision, OUT wal_records bigint, OUT wal_fpi bigint, OUT wal_bytes numeric, OUT jit_functions bigint, OUT jit_generation_time double precision, OUT jit_inlining_count bigint, OUT jit_inlining_time double precision, OUT jit_optimization_count bigint, OUT jit_optimization_time double precision, OUT jit_emission_count bigint, OUT jit_emission_time double precision) postgres After a deep dive of investigation I found the reason. Im my first test case I was dump database to another test cluster with classic "pg_dump from | psql to" method. Its works. After that I was try to pgcopydb from test cluster(which I pgdump before) to another(clear cluster) and its works too. pre-out.list looks different:

46675; 0 0 ACL public TABLE pg_buffercache postgres
46676; 0 0 ACL public TABLE pg_stat_statements postgres
46677; 0 0 ACL public TABLE pg_stat_statements_info postgres

Well. I was check acl for the "old" cluster and for the "new" . For example for pg_stat_statements function:

select proacl from pg_proc where proname='pg_stat_statements' \gx

OLD: proacl | {=X/postgres,postgres=X/postgres,networkconfiguration_rw=X/postgres} NEW: proacl |

Ok. Then I grant execute on funtion to postgres in a new cluster and tryed to pgcopydb again- SUCCESS. Ok! Then I grant execute on funtion to networkconfiguration_rw as well in a new cluster and tryed to pgcopydb again- FAILED.. I was check pre-list and gotcha - same string as at OLD cluster.

Keys like no-acl or no-owner do not helps.

So looks like when in a postgreSQL cluster is more than one acl on a function - it can be a reason of fails because string can't be readed...? Also I think it can happen only for a long strings ... because of why stings before do not alert the issue and only on pg_stat_statements it does?

46624; 0 0 ACL public FUNCTION gen_random_bytes(integer) postgres
46625; 0 0 ACL public FUNCTION gen_random_uuid() postgres
46626; 0 0 ACL public FUNCTION gen_salt(text) postgres
46627; 0 0 ACL public FUNCTION gen_salt(text, integer) postgres
46628; 0 0 ACL public FUNCTION hmac(bytea, bytea, text) postgres
46629; 0 0 ACL public FUNCTION hmac(text, text, text) postgres
46630; 0 0 ACL public FUNCTION pg_buffercache_pages() postgres
46631; 0 0 ACL public FUNCTION pg_buffercache_summary(OUT buffers_used integer, OUT buffers_unused integer, OUT buffers_dirty integer, OUT buffers_pinned integer, OUT usagecount_avg double precision) postgres
46632; 0 0 ACL public FUNCTION pg_buffercache_usage_counts(OUT usage_count integer, OUT buffers integer, OUT dirty integer, OUT pinned integer) postgres
46633; 0 0 ACL public FUNCTION pg_relpages(relname regclass) postgres
46634; 0 0 ACL public FUNCTION pg_relpages(relname text) postgres
46635; 0 0 ACL public FUNCTION pg_stat_statements(showtext boolean, OUT userid oid, OUT dbid oid, OUT toplevel boolean, OUT queryid bigint, OUT query text, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision, OUT temp_blk_read_time double precision, OUT temp_blk_write_time double precision, OUT wal_records bigint, OUT wal_fpi bigint, OUT wal_bytes numeric, OUT jit_functions bigint, OUT jit_generation_time double precision, OUT jit_inlining_count bigint, OUT jit_inlining_time double precision, OUT jit_optimization_count bigint, OUT jit_optimization_time double precision, OUT jit_emission_count bigint, OUT jit_emission_time double precision) postgres
46636; 0 0 ACL public FUNCTION pg_stat_statements_info(OUT dealloc bigint, OUT stats_reset timestamp with time zone) postgres
46637; 0 0 ACL public FUNCTION pg_stat_statements_reset(userid oid, dbid oid, queryid bigint) postgres

Also in a pre-list there is no acl for networkconfiguration_rw user but it must be... because its exist in a dump file

^A^A^@^@^@^A^A^@^@^@^@^F^@^@^@public^A^A^@^@^@^A^A^@^@^@^@^H^@^@^@postgres^@^E^@^@^@false^@^C^@^@^@342^A^A^@^@^@^C^@^@^@^@^@^@^@^@^@+¶^@^@^@^@^@^@^@^@^A^@^@^@0^@^A^@^@^@0^@U^E^@^@FUNCTION pg_stat_statements(showtext boolean, OUT userid oid, OUT dbid oid, OUT toplevel boolean, OUT queryid bigint, OUT query text, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision, OUT temp_blk_read_time double precision, OUT temp_blk_write_time double precision, OUT wal_records bigint, OUT wal_fpi bigint, OUT wal_bytes numeric, OUT jit_functions bigint, OUT jit_generation_time double precision, OUT jit_inlining_count bigint, OUT jit_inlining_time double precision, OUT jit_optimization_count bigint, OUT jit_optimization_time double precision, OUT jit_emission_count bigint, OUT jit_emission_time double precision)^@^C^@^@^@ACL^@^A^@^@^@^@<86>^E^@^@GRANT ALL ON FUNCTION public.pg_stat_statements(showtext boolean, OUT userid oid, OUT dbid oid, OUT toplevel boolean, OUT queryid bigint, OUT query text, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision, OUT temp_blk_read_time double precision, OUT temp_blk_write_time double precision, OUT wal_records bigint, OUT wal_fpi bigint, OUT wal_bytes numeric, OUT jit_functions bigint, OUT jit_generation_time double precision, OUT jit_inlining_count bigint, OUT jit_inlining_time double precision, OUT jit_optimization_count bigint, OUT jit_optimization_time double precision, OUT jit_emission_count bigint, OUT jit_emission_time double precision) TO networkconfiguration_rw;

pre-list from old(problem) cluster pre-out.list.txt

How to replay the issue:

Install PostgreSQL cluster 
create a few databases
Find any functions or install pg_stat_statements extension
In a source:
grant execute  on function pg_stat_statements to postgres;
grant execute  on function pg_stat_statements to username;
pgcopydb copy db --debug

PostgreSQL version: 16.4 pgcopydb version: 17.1