script/schema-fingerprint
Rama main · réplica de lectura del repositorio original.
#!/bin/sh
# SPDX-FileCopyrightText: 2026 GPForum Contributors
# SPDX-License-Identifier: BSD-3-Clause
set -eu
# A database's schema as one sorted line per fact, so two databases compare
# with diff or comm: schemas, relations with their options and grants, each
# column by its position among the live ones, constraints, indexes, triggers,
# sequences, types, comments, default privileges and settings, and every
# function's contract, settings, grants and the MD5 of its body. Month
# partitions are counted under their parent, so two databases made in
# different months still compare. ADR 0150 proved the 1.0 baseline with it.
usage() {
cat <<'USAGE'
Usage: script/schema-fingerprint [psql connection options] DBNAME
Prints the schema of DBNAME as sorted facts on standard output. The
connection options are psql's (-h HOST, -p PORT, -U USER); PGHOST,
PGPORT and PGUSER work too. Compare two databases with:
script/schema-fingerprint a > a.txt
script/schema-fingerprint b > b.txt
diff a.txt b.txt
USAGE
}
case "${1:-}" in
--help|'') usage; exit 0 ;;
esac
psql_bin=${PSQL:-psql}
"$psql_bin" -X -q -At -v ON_ERROR_STOP=1 "$@" <<'SQL' | LC_ALL=C sort
SET search_path = pg_catalog;
WITH ns AS (
SELECT oid, nspname FROM pg_namespace
WHERE nspname !~ '^pg_' AND nspname <> 'information_schema'
)
SELECT kind, name, regexp_replace(detail, E'\\s*\\n\\s*', ' ', 'g'), regexp_replace(extra, E'\\s*\\n\\s*', ' ', 'g') FROM (SELECT * FROM (
-- schemas
SELECT 'schema'::text, n.nspname::text, pg_get_userbyid(n.nspowner)::text, coalesce(n.nspacl::text, '')::text
FROM pg_namespace n JOIN ns USING (oid)
UNION ALL
-- extensions
SELECT 'extension', e.extname, e.extversion, x.nspname
FROM pg_extension e JOIN pg_namespace x ON x.oid = e.extnamespace
UNION ALL
-- relations (not partitions)
SELECT 'relation', ns.nspname || '.' || c.relname,
c.relkind::text || ' persistence=' || c.relpersistence::text
|| ' owner=' || pg_get_userbyid(c.relowner)
|| ' rls=' || c.relrowsecurity || '/' || c.relforcerowsecurity
|| ' replident=' || c.relreplident::text
|| ' partkey=' || coalesce(pg_get_partkeydef(c.oid), ''),
'options=' || coalesce(c.reloptions::text, '') || ' acl=' || coalesce(c.relacl::text, '')
FROM pg_class c JOIN ns ON ns.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p', 'v', 'm', 'S', 'f', 'c') AND NOT c.relispartition
UNION ALL
-- partitions, by parent and bound kind only, counted
SELECT 'partition', ns.nspname || '.' || p.relname,
CASE WHEN pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT' THEN 'default ' || c.relname
ELSE 'range' END,
'options=' || coalesce(c.reloptions::text, '') || ' count=' || count(*)
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid AND c.relkind IN ('r', 'p')
JOIN pg_class p ON p.oid = i.inhparent
JOIN ns ON ns.oid = p.relnamespace
GROUP BY 1, 2, 3, c.reloptions
UNION ALL
-- columns, by their position among live columns
SELECT 'column', ns.nspname || '.' || c.relname || '.' || lpad(
(row_number() OVER (PARTITION BY c.oid ORDER BY a.attnum))::text, 3, '0') || '.' || a.attname,
format_type(a.atttypid, a.atttypmod)
|| CASE WHEN a.attnotnull THEN ' not null' ELSE '' END
|| CASE WHEN a.attidentity <> '' THEN ' identity=' || a.attidentity::text ELSE '' END
|| CASE WHEN a.attgenerated <> '' THEN ' generated=' || a.attgenerated::text ELSE '' END
|| CASE WHEN a.attcollation <> 0 AND a.attcollation <> t.typcollation
THEN ' collate=' || (SELECT collname FROM pg_collation WHERE oid = a.attcollation) ELSE '' END
|| CASE WHEN a.attstorage <> t.typstorage THEN ' storage=' || a.attstorage::text ELSE '' END
|| CASE WHEN a.attcompression <> '' THEN ' compression=' || a.attcompression::text ELSE '' END
|| CASE WHEN a.attstattarget IS NOT NULL THEN ' stattarget=' || a.attstattarget ELSE '' END
|| CASE WHEN a.attoptions IS NOT NULL THEN ' options=' || a.attoptions::text ELSE '' END,
'default=' || coalesce(pg_get_expr(d.adbin, d.adrelid), '') || ' acl=' || coalesce(a.attacl::text, '')
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid AND c.relkind IN ('r', 'p', 'v', 'm', 'f', 'c') AND NOT c.relispartition
JOIN ns ON ns.oid = c.relnamespace
JOIN pg_type t ON t.oid = a.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attnum > 0 AND NOT a.attisdropped
UNION ALL
-- constraints (table and domain), not on partitions
SELECT 'constraint', ns.nspname || '.' || coalesce(c.relname, ty.typname) || '.' || k.conname,
k.contype::text || ' ' || pg_get_constraintdef(k.oid, true),
'deferrable=' || k.condeferrable || '/' || k.condeferred || ' valid=' || k.convalidated
|| ' noinherit=' || k.connoinherit
FROM pg_constraint k
LEFT JOIN pg_class c ON c.oid = k.conrelid
LEFT JOIN pg_type ty ON ty.oid = k.contypid
JOIN ns ON ns.oid = k.connamespace
WHERE coalesce(c.relispartition, false) = false
UNION ALL
-- indexes, not on partitions
SELECT 'index', ns.nspname || '.' || ic.relname,
pg_get_indexdef(ic.oid),
'options=' || coalesce(ic.reloptions::text, '') || ' clustered=' || x.indisclustered
|| ' valid=' || x.indisvalid
FROM pg_index x
JOIN pg_class ic ON ic.oid = x.indexrelid
JOIN pg_class tc ON tc.oid = x.indrelid
JOIN ns ON ns.oid = ic.relnamespace
WHERE NOT tc.relispartition
UNION ALL
-- index statistics targets
SELECT 'index-column', ns.nspname || '.' || ic.relname || '.' || a.attnum,
'stattarget=' || a.attstattarget, ''
FROM pg_attribute a
JOIN pg_class ic ON ic.oid = a.attrelid AND ic.relkind IN ('i', 'I') AND NOT ic.relispartition
JOIN ns ON ns.oid = ic.relnamespace
WHERE a.attstattarget IS NOT NULL
UNION ALL
-- triggers
SELECT 'trigger', ns.nspname || '.' || c.relname || '.' || g.tgname,
pg_get_triggerdef(g.oid, true), 'enabled=' || g.tgenabled::text
FROM pg_trigger g JOIN pg_class c ON c.oid = g.tgrelid JOIN ns ON ns.oid = c.relnamespace
WHERE NOT g.tgisinternal AND NOT c.relispartition
UNION ALL
-- sequences
SELECT 'sequence', ns.nspname || '.' || c.relname,
format_type(s.seqtypid, NULL) || ' start=' || s.seqstart || ' inc=' || s.seqincrement
|| ' min=' || s.seqmin || ' max=' || s.seqmax || ' cache=' || s.seqcache || ' cycle=' || s.seqcycle,
'owned_by=' || coalesce((SELECT d.refobjid::regclass || '.' || d.refobjsubid FROM pg_depend d
WHERE d.objid = c.oid AND d.classid = 'pg_class'::regclass
AND d.refclassid = 'pg_class'::regclass AND d.deptype IN ('a', 'i')), '')
FROM pg_sequence s JOIN pg_class c ON c.oid = s.seqrelid JOIN ns ON ns.oid = c.relnamespace
UNION ALL
-- extended statistics
SELECT 'statistics', ns.nspname || '.' || s.stxname, pg_get_statisticsobjdef(s.oid),
'target=' || coalesce(s.stxstattarget::text, '')
FROM pg_statistic_ext s JOIN ns ON ns.oid = s.stxnamespace
UNION ALL
-- policies
SELECT 'policy', c.relname || '.' || p.polname, p.polcmd::text || ' ' || coalesce(pg_get_expr(p.polqual, p.polrelid), ''),
coalesce(pg_get_expr(p.polwithcheck, p.polrelid), '')
FROM pg_policy p JOIN pg_class c ON c.oid = p.polrelid
UNION ALL
-- types that are not a table's row type
SELECT 'type', ns.nspname || '.' || t.typname, t.typtype::text,
CASE t.typtype::text
WHEN 'e' THEN (SELECT string_agg(enumlabel, ',' ORDER BY enumsortorder) FROM pg_enum WHERE enumtypid = t.oid)
WHEN 'd' THEN format_type(t.typbasetype, t.typtypmod) || ' notnull=' || t.typnotnull
ELSE '' END || ' acl=' || coalesce(t.typacl::text, '')
FROM pg_type t JOIN ns ON ns.oid = t.typnamespace
WHERE t.typtype IN ('e', 'd', 'r', 'm')
OR (t.typtype = 'c' AND (SELECT relkind FROM pg_class WHERE oid = t.typrelid) = 'c')
UNION ALL
-- functions: their contract
SELECT 'function', ns.nspname || '.' || p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')',
'returns ' || coalesce(pg_get_function_result(p.oid), '')
|| ' kind=' || p.prokind::text || ' lang=' || l.lanname
|| ' volatile=' || p.provolatile::text || ' parallel=' || p.proparallel::text
|| ' strict=' || p.proisstrict || ' definer=' || p.prosecdef || ' leakproof=' || p.proleakproof
|| ' cost=' || p.procost || ' rows=' || p.prorows
|| ' config=' || coalesce(p.proconfig::text, '')
|| ' owner=' || pg_get_userbyid(p.proowner),
'args=' || pg_get_function_arguments(p.oid) || ' acl=' || coalesce(p.proacl::text, '')
FROM pg_proc p JOIN ns ON ns.oid = p.pronamespace JOIN pg_language l ON l.oid = p.prolang
WHERE NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = p.oid AND d.deptype = 'e')
UNION ALL
-- functions: the hash of their body, so a changed body is one line
SELECT 'function-body', ns.nspname || '.' || p.proname || '(' || pg_get_function_identity_arguments(p.oid) || ')',
md5(coalesce(p.prosrc, '')), ''
FROM pg_proc p JOIN ns ON ns.oid = p.pronamespace
WHERE NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = p.oid AND d.deptype = 'e')
UNION ALL
-- default privileges
SELECT 'default-acl', pg_get_userbyid(a.defaclrole) || '.' || coalesce(n.nspname, '*') || '.' || a.defaclobjtype::text,
coalesce(a.defaclacl::text, ''), ''
FROM pg_default_acl a LEFT JOIN pg_namespace n ON n.oid = a.defaclnamespace
UNION ALL
-- comments
SELECT 'comment', d.classoid::regclass::text || ':' || coalesce(
CASE d.classoid
WHEN 'pg_class'::regclass THEN d.objoid::regclass::text
WHEN 'pg_proc'::regclass THEN d.objoid::regprocedure::text
WHEN 'pg_namespace'::regclass THEN (SELECT nspname FROM pg_namespace WHERE oid = d.objoid)
WHEN 'pg_type'::regclass THEN d.objoid::regtype::text
ELSE d.objoid::text END, '?') || '.' || d.objsubid,
md5(d.description), left(d.description, 60)
FROM pg_description d
WHERE d.classoid IN ('pg_class'::regclass, 'pg_proc'::regclass, 'pg_namespace'::regclass, 'pg_type'::regclass)
AND (d.classoid <> 'pg_namespace'::regclass OR d.objoid IN (SELECT oid FROM ns))
AND (d.classoid <> 'pg_class'::regclass OR d.objoid IN (SELECT c.oid FROM pg_class c JOIN ns ON ns.oid = c.relnamespace))
AND (d.classoid <> 'pg_proc'::regclass OR d.objoid IN (SELECT p.oid FROM pg_proc p JOIN ns ON ns.oid = p.pronamespace))
AND (d.classoid <> 'pg_type'::regclass OR d.objoid IN (SELECT t.oid FROM pg_type t JOIN ns ON ns.oid = t.typnamespace))
UNION ALL
-- database-level settings
SELECT 'db-setting', coalesce(r.rolname, '*'), array_to_string(s.setconfig, ','), ''
FROM pg_db_role_setting s LEFT JOIN pg_roles r ON r.oid = s.setrole
WHERE s.setdatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
) facts(kind, name, detail, extra)
ORDER BY 1, 2, 3, 4) sorted;
SQL