gpbackup on metadata

[adnanhamdussalam]

Hi,

I am facing slowness while taking the gpbackup metadata only of the database it took 21 mins as same database size on GP side it is taking only 2 mins

PFB the log output :
timestamp key: 20260412011352
gpdb version: ache Cloudberry 2.0.0-incubating build 1
gpbackup version: 1.2.7-beta1+dev.7

database name: gpafiniti
command line: /usr/local/cloudberry-db/bin/./gpbackup --backup-dir /dbbackup/full_metadata/full_metadata_202604120100 --dbname mydb --metadata-only
compression: gzip
plugin executable: None
backup section: Metadata Only
object filtering: None
includes statistics: No
data file format: No Data Files
incremental: False

start time: Sun Apr 12 2026 01:13:52
end time: Sun Apr 12 2026 01:35:15
duration: 0:21:23

backup status: Success

Need your guidance on this issue.

[tuhaihe]

gpbackup version: 1.2.7-beta1+dev.7

Not sure about the source of gpbackup. Can try the latest 2.1.0-incubating.

[adnanhamdussalam]

Hi @tuhaihe,

Thank for the update I have change the backup utility to 2.1.0.

I have started the gpbackup from latest 2.1.0 but still it is taking time and found below query executing in the database and pg_attribute size is only 19GB but still it is slow

Kindly have a look .

./gpbackup --version
gpbackup version 2.1.0
[gpadmin@mycoordbin]$ ./gpbackup --dbname gpafiniti --backup-dir /dbbackup/Full_backup_GCR_47661/gpafiniti_metadata_$(date +%Y%m%d) --verbose --metadata-only
20260414:14:22:12 gpbackup:gpadmin:mycoord:364165-[DEBUG]:-Backup Command: [./gpbackup --dbname gpafiniti --backup-dir /dbbackup/Full_backup_GCR_47661/gpafiniti_metadata_20260414 --verbose --metadata-only]
20260414:14:22:12 gpbackup:gpadmin:mycoord:364165-[INFO]:-gpbackup version = 2.1.0
20260414:14:22:12 gpbackup:gpadmin:mycoord:364165-[DEBUG]:-Initializing 2 database connections
20260414:14:22:13 gpbackup:gpadmin:mycoord:364165-[INFO]:-Apache Cloudberry Version = PostgreSQL 14.4 (Apache Cloudberry 2.0.0-incubating build 1) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5.0.1), 64-bit compiled on Aug 28 2025 15:25:48
20260414:14:22:13 gpbackup:gpadmin:mycoord:364165-[INFO]:-Starting backup of database gpafiniti
20260414:14:22:13 gpbackup:gpadmin:mycoord:364165-[DEBUG]:-Validating Tables and Schemas exist in Database
20260414:14:22:13 gpbackup:gpadmin:mycoord:364165-[INFO]:-Backup Timestamp = 20260414142212
20260414:14:22:13 gpbackup:gpadmin:mycoord:364165-[INFO]:-Backup Database = gpafiniti
20260414:14:22:13 gpbackup:gpadmin:mycoord:364165-[DEBUG]:-Backup Parameters: {compression: gzip, plugin executable: None, backup section: Metadata Only, object filtering: None, includes statistics: No, data file format: No Data Files, incremental: False}
20260414:14:22:13 gpbackup:gpadmin:mycoord:364165-[INFO]:-Gathering table state information
20260414:14:22:13 gpbackup:gpadmin:mycoord:364165-[INFO]:-Acquiring ACCESS SHARE locks on tables
20260414:14:22:15 gpbackup:gpadmin:mycoord:364165-[INFO]:-Gathering additional table metadata
20260414:14:22:15 gpbackup:gpadmin:mycoord:364165-[DEBUG]:-Getting column definitions

SELECT a.attrelid,
a.attnum,
quote_ident(a.attname) AS name,
a.attnotnull,
a.atthasdef,
pg_catalog.format_type(t.oid,a.atttypmod) AS type,
coalesce(pg_catalog.array_to_string(e.attoptions, ','), '') AS encoding,
a.attstattarget,
CASE WHEN a.attstorage != t.typstorage THEN a.attstorage ELSE '' END AS storagetype,
coalesce('('||pg_catalog.pg_get_expr(ad.adbin, ad.adrelid)||')', '') AS defaultval,
coalesce(d.description, '') AS comment,
a.attgenerated,
ljl_unnest AS privileges,
CASE
WHEN a.attacl IS NULL THEN ''
WHEN array_upper(a.attacl, 1) = 0 THEN 'Empty'
ELSE ''
END AS kind,
coalesce(pg_catalog.array_to_string(a.attoptions, ','), '') AS options,
coalesce(array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM pg_options_to_table(attfdwoptions) ORDER BY option_name), ', '), '') AS fdwoptions,
CASE WHEN a.attcollation <> t.typcollation THEN quote_ident(cn.nspname) || '.' || quote_ident(coll.collname) ELSE '' END AS collation,
coalesce(sec.provider,'') AS securitylabelprovider,
coalesce(sec.label,'') AS securitylabel,
(a.attinhcount > 0) AS isinherited
FROM pg_catalog.pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_catalog.pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_catalog.pg_attribute_encoding e ON e.attrelid = a.attrelid AND e.attnum = a.attnum
LEFT JOIN pg_description d ON d.objoid = a.attrelid AND d.classoid = 'pg_class'::regclass AND d.objsubid = a.attnum
LEFT JOIN pg_collation coll ON a.attcollation = coll.oid
LEFT JOIN pg_namespace cn ON coll.collnamespace = cn.oid
LEFT JOIN pg_seclabel sec ON sec.objoid = a.attrelid AND sec.classoid = 'pg_class'::regclass AND sec.objsubid = a.attnum
LEFT JOIN LATERAL unnest(a.attacl) ljl_unnest ON a.attacl IS NOT NULL AND array_length(a.attacl, 1) != 0
WHERE n.nspname NOT LIKE 'pg_temp_%' AND n.nspname NOT LIKE 'pg_toast%' AND n.nspname NOT IN ('gp_toolkit', 'information_schema', 'pg_aoseg', 'pg_bitmapindex', 'pg_catalog', 'pg_ext_aux')
AND c.reltype <> 0
AND a.attnum > 0::pg_catalog.int2
AND a.attisdropped = 'f'
ORDER BY a.attrelid, a.attnum",0,,"COptTasks.cpp",267,

[tuhaihe]

hi @robertmu @woblerr any thoughts on this? Thanks!

[woblerr]

Hi
According to the comment, special optimizations were performed for GP 6 https://github.com/apache/cloudberry-backup/blob/main/backup/queries_table_defs.go#L329-L332

It looks like we need to pay attention to the performance of this code.

A patch solution is possible (a specific request from the issue is taken), but it should be tested on real data to confirm the perfomance improvement and correctness.

 	SELECT a.attrelid,
 		a.attnum,
 		quote_ident(a.attname) AS name,
 		a.attnotnull,
 		a.atthasdef,
 		pg_catalog.format_type(t.oid,a.atttypmod) AS type,
 		coalesce(pg_catalog.array_to_string(e.attoptions, ','), '') AS encoding,
 		a.attstattarget,
 		CASE WHEN a.attstorage != t.typstorage THEN a.attstorage ELSE '' END AS storagetype,
 		coalesce('('||pg_catalog.pg_get_expr(ad.adbin, ad.adrelid)||')', '') AS defaultval,
 		coalesce(d.description, '') AS comment,
 		a.attgenerated,
-		ljl_unnest AS privileges,
+		unnest(CASE
+			WHEN a.attacl IS NULL OR array_length(a.attacl, 1) IS NULL
+			THEN ARRAY[NULL::aclitem]
+			ELSE a.attacl
+		END) AS privileges,
 		CASE
 			WHEN a.attacl IS NULL THEN ''
 			WHEN array_upper(a.attacl, 1) = 0 THEN 'Empty'
 			ELSE ''
 		END AS kind,
 		coalesce(pg_catalog.array_to_string(a.attoptions, ','), '') AS options,
 		coalesce(array_to_string(ARRAY(SELECT option_name || ' ' || quote_literal(option_value) FROM pg_options_to_table(attfdwoptions) ORDER BY option_name), ', '), '') AS fdwoptions,
 		CASE WHEN a.attcollation <> t.typcollation THEN quote_ident(cn.nspname) || '.' || quote_ident(coll.collname) ELSE '' END AS collation,
 		coalesce(sec.provider,'') AS securitylabelprovider,
 		coalesce(sec.label,'') AS securitylabel,
 		(a.attinhcount > 0) AS isinherited
 	FROM pg_catalog.pg_attribute a
 		JOIN pg_class c ON a.attrelid = c.oid
 		JOIN pg_namespace n ON c.relnamespace = n.oid
 		LEFT JOIN pg_catalog.pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
 		LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
 		LEFT JOIN pg_catalog.pg_attribute_encoding e ON e.attrelid = a.attrelid AND e.attnum = a.attnum
 		LEFT JOIN pg_description d ON d.objoid = a.attrelid AND d.classoid = 'pg_class'::regclass AND d.objsubid = a.attnum
 		LEFT JOIN pg_collation coll ON a.attcollation = coll.oid
 		LEFT JOIN pg_namespace cn ON coll.collnamespace = cn.oid
 		LEFT JOIN pg_seclabel sec ON sec.objoid = a.attrelid AND sec.classoid = 'pg_class'::regclass AND sec.objsubid = a.attnum
-		LEFT JOIN LATERAL unnest(a.attacl) ljl_unnest ON a.attacl IS NOT NULL AND array_length(a.attacl, 1) != 0
 	WHERE n.nspname NOT LIKE 'pg_temp_%' AND n.nspname NOT LIKE 'pg_toast%' AND n.nspname NOT IN ('gp_toolkit', 'information_schema', 'pg_aoseg', 'pg_bitmapindex', 'pg_catalog', 'pg_ext_aux')
 		AND c.reltype <> 0
 		AND a.attnum > 0::pg_catalog.int2
 		AND a.attisdropped = 'f'
 	ORDER BY a.attrelid, a.attnum

[woblerr]

Hi @adnanhamdussalam

I created PR #94

It would be great if you could test the patch in your environment and come back with feedback.

In addition to the speed of the backup, check that the number of objects in gpbackup_report is the same or close (if some objects were created or deleted during the time between backups)

Thanks

[tuhaihe]

It has been fixed by #94, so close it. If not, welcome to reopen it. Thanks!