Extraire la structure d'une base de données

Pour lister l'ensemble des schémas :

select distinct schemaname from pg_catalog.pg_statio_all_tables order by schemaname;

Pour afficher la liste des tables :

select schemaname, relname as tablename, description
       from pg_catalog.pg_statio_all_tables st
        join pg_catalog.pg_description on (relid = objoid and objsubid = 0)
        where schemaname in 
        ('public', 'schema1', 'schema2')
        order by schemaname, relname;

Pour afficher la liste des colonnes triées par schéma et par table :

with req as 
        (SELECT DISTINCT on ( schemaname, tablename, field) schemaname, pg_tables.tablename 
           ,attnum  
           ,pg_attribute.attname AS field
            ,format_type(pg_attribute.atttypid,NULL) AS "type"
            ,pg_constraint.conname AS "key"
             ,CASE pg_attribute.attnotnull
            WHEN FALSE THEN null
           ELSE 1
         END AS "notnull"
         ,(SELECT col_description(pg_attribute.attrelid,pg_attribute.attnum)) AS COMMENT
          ,pc2.conname AS ckey
         ,(SELECT pg_attrdef.adsrc
          FROM pg_attrdef
          WHERE pg_attrdef.adrelid = pg_class.oid
          AND   pg_attrdef.adnum = pg_attribute.attnum) AS def
        FROM pg_tables,
       pg_class
       JOIN pg_attribute
       ON pg_class.oid = pg_attribute.attrelid
       AND pg_attribute.attnum > 0
       LEFT JOIN pg_constraint
           ON pg_constraint.contype = 'p'::"char"
           AND pg_constraint.conrelid = pg_class.oid
           AND (pg_attribute.attnum = ANY (pg_constraint.conkey))
        LEFT JOIN pg_constraint AS pc2
           ON pc2.contype = 'f'::"char"
           AND pc2.conrelid = pg_class.oid
           AND (pg_attribute.attnum = ANY (pc2.conkey))
        WHERE pg_class.relname = pg_tables.tablename
        AND   pg_attribute.atttypid <> 0::OID
        and schemaname in 
        ('public', 'schema1', 'schema2')
       ORDER BY schemaname, tablename, field ASC)
        select * from req order by schemaname, tablename, attnum;