pgcmp-dump

#!/bin/bash

# Optional configuration:
PGBINDIR=${PGBINDIR:-${PGBINDIR:-"/var/lib/postgresql/dbs/postgresql-9.1/bin"}}
PGCWORKDIR=${PGCWORKDIR:-"/tmp"}

PGDB=${DB:-"slonyregress1"}
PGHOST=${PGHOST:-"localhost"}
PGUSER=${PGUSER:-"postgres"}
PGPORT=${PGPORT:-"5432"}
URIC="postgresql://${PGUSER}@${PGHOST}:${PGPORT}/${PGDB}"
PGURI=${PGURI:-${URIC}}

PGCLABEL=${PGCLABEL:-`echo "${PGURI}" | sed 's/postgresql\:\/\///g' | sed 's/\//\:/g'`}

PGCMPOUTPUT=${PGCMPOUTPUT:-"${PGCWORKDIR}/${PGCLABEL}"}

echo "pgcmp-dump - extracting schema data from database PGURI=[${PGURI}]

Data file containing output: PGCMPOUTPUT[${PGCMPOUTPUT}]
Label: PGCLABEL=[${PGCLABEL}]

"

function extract_schema_data () {
    local PGCLABEL=$1
    local WHEN=`date`
    echo "-- extract_schema_data.sh {label=${PGCLABEL}}"
    echo "-- extracting schema information into file ${OUTFILE}"
    echo "-- based on libpq parameters: 
--   PGHOST=${PGHOST}
--   PGDATABASE=${PGDATABASE}
--   PGPORT=${PGPORT}
--   PGUSER=${PGUSER}
--   as at ${WHEN}"

# Pull data for major object types:

# BLANK is just a place-holder to establish the names and data types for the elements in the giant UNION query
    BLANK=" select 'header'::text as object_type, 'schema'::text as object_schema, 'name'::text as object_name, 'definition'::text as object_definition "

# Roles
    RQ=" select 'role', NULL::text, rolname, 'rolcreaterole:'|| (rolcreaterole::text) || ',rolcreatedb:' || (rolcreatedb::text) ||',rolcanlogin:'||(rolcanlogin::text)
from pg_catalog.pg_roles"

# Schemas
# a) Existence
    SCEQ="
select 'schema', schema_name, schema_name, schema_owner from information_schema.schemata"

# b) Permissions
    SCP="select object_type::text, object_schema::text, object_name::text, object_value::text from (with sp as (select 'schema permissions' as object_type, quote_ident(nspname) as object_schema, nspname as object_name, unnest(nspacl)::text as acl_breakdown, position('=' in unnest(nspacl)::text)::integer as acl_separator_pos from pg_catalog.pg_namespace)
select object_type, object_schema, object_name || ':' || coalesce(substr(acl_breakdown, 0, acl_separator_pos), '')::text as object_name, coalesce(substr(acl_breakdown, acl_separator_pos), '')::text as object_value from sp) as sp "
    

# Tables
    TEQ="
select case when table_type = 'BASE TABLE' then 'table'
            when table_type = 'VIEW' then 'view'
            else 'other relation' end,
quote_ident(table_schema),
quote_ident(table_schema) || '.' || quote_ident(table_name), NULL::text
from information_schema.tables"

    TOWN="
select case when c.relkind = 'r' then 'table owner'
            when c.relkind = 'v' then 'view owner' end, 
        quote_ident(n.nspname),
        quote_ident(n.nspname) || '.' || quote_ident(c.relname), r.rolname
from pg_class c, pg_namespace n, pg_roles r
where n.oid = c.relnamespace and r.oid = c.relowner
and c.relkind in ('v', 'r')"

    COLS="
select 'column', quote_ident(table_schema), quote_ident(table_schema)|| '.' || quote_ident(table_name) || '.' || quote_ident(column_name),
'type:' || data_type || 
',maxlen:' || coalesce(character_maximum_length::text, coalesce(character_octet_length::text, coalesce(numeric_precision::text, 'n/a'))) ||
',nullable:'||is_nullable || ',default:' ||  coalesce(column_default, 'NULL')
from information_schema.columns"

    TSTORAGE="
select 'options', quote_ident(n.nspname),
        quote_ident(n.nspname) || '.' || quote_ident(c.relname), 
        unnest(c.reloptions)
from pg_class c, pg_namespace n
where n.oid = c.relnamespace and c.reloptions is not null"

    TPERM="
select object_type::text, object_schema::text, object_name::text, object_value::text
from
(with tp as
  (select 
       case when c.relkind = 'r' then 'table permissions'
            when c.relkind = 'S' then 'sequence permissions'
            when c.relkind = 'v' then 'view permissions' end as object_type,
       quote_ident(n.nspname::text) as object_schema,
       quote_ident(n.nspname) || '.' || quote_ident(c.relname)::text as object_name,
       unnest(c.relacl)::text as perm,
       position('=' in unnest(c.relacl)::text)::integer as perm_separator
   from pg_class c, pg_namespace n, pg_roles r
   where n.oid = c.relnamespace and r.oid = c.relowner
     and c.relkind in ('v', 'r', 'S'))
  select object_type, object_schema, 
         quote_ident(object_name || ':' || coalesce(substr(perm, 0, perm_separator), '')::text) as object_name, 
         coalesce(substr(perm, perm_separator), '')::text as object_value from tp)
as tperms"

    TSEQ="
select 'sequence', quote_ident(sequence_schema), quote_ident(sequence_schema) || '.' || quote_ident(sequence_name), 
    'start:' || start_value::text || ',min:' || minimum_value::text || ',max:' || maximum_value::text || ',increment:' || increment::text || ',cycles:' || cycle_option
from information_schema.sequences"

    VDEF="
select 'view definition', quote_ident(table_schema), quote_ident(table_schema)|| '.' || quote_ident(table_name), view_definition
from information_schema.views"


    FCODE="
select 'function definition', quote_ident(r.specific_schema), quote_ident(r.specific_schema) || '.' || p.oid::regprocedure::text, r.routine_definition 
from information_schema.routines r, pg_catalog.pg_proc p 
where r.specific_name = p.proname || '_' || p.oid "

    FPERM1="
select 'function permissions', quote_ident(rp.specific_schema), p.oid::regprocedure::text || '-role:' || rp.grantee,
'privilege_type:' || rp.privilege_type || ',is_grantable:' || rp.is_grantable
from information_schema.routine_privileges rp, pg_proc p
where rp.specific_name = p.proname || '_' || p.oid"

    FOWNER="
select 'function owner', quote_ident(n.nspname), p.oid::regprocedure::text, r.rolname
from pg_catalog.pg_roles r, pg_catalog.pg_proc p, pg_catalog.pg_namespace n
where r.oid = p.proowner and n.oid = p.pronamespace"

    FLANGUAGE="
select 'function language', quote_ident(r.specific_schema), r.specific_schema || '.' || p.oid::regprocedure::text, r.external_language
from information_schema.routines r, pg_catalog.pg_proc p
where
    r.specific_name = p.proname || '_' || p.oid "

    FSECTYPE="
select 'function security type', quote_ident(r.specific_schema), r.specific_schema || '.' || p.oid::regprocedure::text, r.security_type
from information_schema.routines r, pg_catalog.pg_proc p
where 
    r.specific_name = p.proname || '_' || p.oid"

    FCONFIG="
select 'function config', n.nspname, p.oid::regprocedure::text, p.proconfig::text
from pg_catalog.pg_proc p, pg_catalog.pg_namespace n
where n.oid = p.pronamespace"

    TRIGGERS="
select 'trigger', quote_ident(t.trigger_schema), quote_ident(t.trigger_schema) || '.' || quote_ident(t.event_object_table) || '.' || quote_ident(t.trigger_name) ||'/'|| quote_ident(t.event_manipulation), 'action:' || t.action_statement ||',orientation:'||t.action_orientation || ',timing:' || t.action_timing
from information_schema.triggers t"

    CHECKCON="
select 'check constraint', quote_ident(n.nspname), quote_ident(n.nspname) || '.' || quote_ident(r.relname) || ':' || c.conname, pg_catalog.pg_get_constraintdef(c.oid) as consrc
from
   pg_catalog.pg_class r, pg_catalog.pg_namespace n, pg_catalog.pg_constraint c
where
   r.oid = c.conrelid and n.oid = c.connamespace and pg_catalog.pg_get_constraintdef(c.oid) is not null"

    INDEXES="
select 'index', quote_ident(schemaname), quote_ident(schemaname) || '.' || quote_ident(tablename) || ':' || quote_ident(indexname), indexdef
from pg_catalog.pg_indexes"

    FOREIGNKEYS="
select 'foreign key', quote_ident(constraint_schema), quote_ident(constraint_schema) || '.' || quote_ident(c.relname) || ':' || constraint_name,
unique_constraint_schema || '.' || unique_constraint_name || ',match:' || match_option || ',update_rule:' || update_rule || ',delete_rule:' || delete_rule
from pg_catalog.pg_class c, pg_catalog.pg_constraint pc, information_schema.referential_constraints rc, pg_catalog.pg_namespace n
where
   rc.constraint_schema = n.nspname and
   rc.constraint_name = pc.conname and
   pc.conrelid = c.oid and
   c.relnamespace = n.oid"

    TYPES="
select 'data type', quote_ident(n.nspname), quote_ident(n.nspname) || '.' || quote_ident(t.typname), 'length:'||t.typlen::text || ',byval:'||t.typbyval::text||',phystype:'||t.typtype||',category:'||t.typcategory||',ispreferred:'||t.typispreferred::text||',delimiter:'||t.typdelim||'input:'||t.typinput::text||'output:'||t.typoutput::text||'receive:'||t.typreceive::text||'send:'||t.typsend::text||'modin:'||t.typmodin::text||'modout:'||t.typmodout::text||'analyze:'||t.typanalyze::text||'align:'||t.typalign::text||'storage:'||t.typstorage::text||'notnull:'||t.typnotnull::text||'typmod:'||t.typtypmod::text||'ndims:'||t.typndims::text||'collation:'||t.typcollation::text
from pg_catalog.pg_type t, pg_namespace n
where t.typnamespace = n.oid"
    
    TYPEOWNERS="
select 'data type owner', quote_ident(n.nspname), quote_ident(n.nspname) || '.' || quote_ident(t.typname), u.usename
from pg_catalog.pg_type t, pg_namespace n, pg_user u
where 
   t.typnamespace = n.oid and u.usesysid = t.typowner"

    OPERATORS="
select 'operator', quote_ident(n.nspname), quote_ident(n.nspname) || '.' || quote_ident(t.oprname)|| '(' || quote_ident(tl.typname) || ',' || quote_ident(tr.typname) || ')',
'kind:'|| t.oprkind::text ||
',canmerge:'|| t.oprcanmerge::text ||
',canhash:'|| t.oprcanhash::text ||
',left:'|| t.oprleft::text ||
',right:'|| t.oprright::text ||
',result:'|| t.oprresult::text ||
',com:'|| t.oprcom::text ||
',negate:'|| t.oprnegate::text ||
',code:'|| t.oprcode::text ||
',rest:'|| t.oprrest::text ||
',join:'|| t.oprjoin::text
from pg_catalog.pg_operator t, pg_catalog.pg_namespace n, pg_catalog.pg_type tl, pg_catalog.pg_type tr
where 
t.oprnamespace = n.oid and tl.oid = t.oprleft and tr.oid = t.oprright"
    
    OPOWNERS="
select 'operator owner', quote_ident(n.nspname), quote_ident(n.nspname) || '.' || quote_ident(o.oprname), u.usename
from pg_catalog.pg_operator o, pg_namespace n, pg_user u
where 
   o.oprnamespace = n.oid and u.usesysid = o.oprowner"

    EVERYTHING="
copy ( with pgd_all_data (object_type, object_schema, object_name, object_definition)
      as (
${BLANK}
UNION ALL
${RQ} 
UNION ALL
${SCEQ}
UNION ALL
${SCP}
UNION ALL
${TEQ}
UNION ALL
${TOWN}
UNION ALL
${TSTORAGE}
UNION ALL
${TPERM}
UNION ALL
${COLS}
UNION ALL
${TSEQ}
UNION ALL
${VDEF}
UNION ALL
${FCODE}
UNION ALL
${FPERM1}
UNION ALL
${FOWNER}
UNION ALL
${FLANGUAGE}
UNION ALL
${FSECTYPE}
UNION ALL
${FCONFIG}
UNION ALL
${TRIGGERS}
UNION ALL
${CHECKCON}
UNION ALL
${INDEXES}
UNION ALL
${FOREIGNKEYS}
UNION ALL
${TYPES}
UNION ALL
${TYPEOWNERS}
UNION ALL
${OPERATORS}
UNION ALL
${OPOWNERS} )
select '${PGCLABEL}', object_schema, object_type, object_name, object_definition from pgd_all_data
   where object_schema not in (select nspname from pg_catalog.pg_namespace where nspname in ('pg_catalog','information_schema', 'pg_toast') or nspname like 'pg_temp_%' or nspname like 'pg_toast_temp_%')
)
to stdout;"

    echo "${EVERYTHING}"
}

extract_schema_data "${PGCLABEL}" | ${PGBINDIR}/psql -q -d "${PGURI}"  > ${PGCMPOUTPUT}
retcode=$?
if [ $retcode -ne 0 ]; then
    echo "Could not extract schema data for label ${PGCLABEL} from database PGDATABASE=[${PGURI}]"
    exit 1
else
    echo "Extracted schema data for [${PGURI}]"
    ls -l ${PGCMPOUTPUT}
fi