psql 

Описание pg_class
~~~~~~~~~~~~~~~~~


        => \d pg_class
              Table "pg_catalog.pg_class"
             Column     |   Type    | Modifiers 
        ----------------+-----------+-----------
         relname        | name      | not null
         relnamespace   | oid       | not null
         reltype        | oid       | not null
         reloftype      | oid       | not null
         relowner       | oid       | not null
         relam          | oid       | not null
         relfilenode    | oid       | not null
         reltablespace  | oid       | not null
         relpages       | integer   | not null
         reltuples      | real      | not null
         relallvisible  | integer   | not null
         reltoastrelid  | oid       | not null
         relhasindex    | boolean   | not null
         relisshared    | boolean   | not null
         relpersistence | "char"    | not null
         relkind        | "char"    | not null
         relnatts       | smallint  | not null
         relchecks      | smallint  | not null
         relhasoids     | boolean   | not null
         relhaspkey     | boolean   | not null
         relhasrules    | boolean   | not null
         relhastriggers | boolean   | not null
         relhassubclass | boolean   | not null
         relispopulated | boolean   | not null
         relreplident   | "char"    | not null
         relfrozenxid   | xid       | not null
         relminmxid     | xid       | not null
         relacl         | aclitem[] | 
         reloptions     | text[]    | 
        Indexes:
            "pg_class_oid_index" UNIQUE, btree (oid)
            "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
            "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
        


Подробное описание pg_tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~


        => \d+ pg_tables
                        View "pg_catalog.pg_tables"
           Column    |  Type   | Modifiers | Storage | Description 
        -------------+---------+-----------+---------+-------------
         schemaname  | name    |           | plain   | 
         tablename   | name    |           | plain   | 
         tableowner  | name    |           | plain   | 
         tablespace  | name    |           | plain   | 
         hasindexes  | boolean |           | plain   | 
         hasrules    | boolean |           | plain   | 
         hastriggers | boolean |           | plain   | 
        View definition:
         SELECT n.nspname AS schemaname,
            c.relname AS tablename,
            pg_get_userbyid(c.relowner) AS tableowner,
            t.spcname AS tablespace,
            c.relhasindex AS hasindexes,
            c.relhasrules AS hasrules,
            c.relhastriggers AS hastriggers
           FROM pg_class c
             LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
             LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
          WHERE c.relkind = 'r'::"char";
        


Таблицы и представления на pg_stat
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


        => \dtv pg_stat*
                              List of relations
           Schema   |            Name             | Type  |  Owner   
        ------------+-----------------------------+-------+----------
         pg_catalog | pg_stat_activity            | view  | postgres
         pg_catalog | pg_stat_all_indexes         | view  | postgres
         pg_catalog | pg_stat_all_tables          | view  | postgres
         pg_catalog | pg_stat_archiver            | view  | postgres
         pg_catalog | pg_stat_bgwriter            | view  | postgres
         pg_catalog | pg_stat_database            | view  | postgres
         pg_catalog | pg_stat_database_conflicts  | view  | postgres
         pg_catalog | pg_stat_replication         | view  | postgres
         pg_catalog | pg_stat_sys_indexes         | view  | postgres
         pg_catalog | pg_stat_sys_tables          | view  | postgres
         pg_catalog | pg_stat_user_functions      | view  | postgres
         pg_catalog | pg_stat_user_indexes        | view  | postgres
         pg_catalog | pg_stat_user_tables         | view  | postgres
         pg_catalog | pg_stat_xact_all_tables     | view  | postgres
         pg_catalog | pg_stat_xact_sys_tables     | view  | postgres
         pg_catalog | pg_stat_xact_user_functions | view  | postgres
         pg_catalog | pg_stat_xact_user_tables    | view  | postgres
         pg_catalog | pg_statio_all_indexes       | view  | postgres
         pg_catalog | pg_statio_all_sequences     | view  | postgres
         pg_catalog | pg_statio_all_tables        | view  | postgres
         pg_catalog | pg_statio_sys_indexes       | view  | postgres
         pg_catalog | pg_statio_sys_sequences     | view  | postgres
         pg_catalog | pg_statio_sys_tables        | view  | postgres
         pg_catalog | pg_statio_user_indexes      | view  | postgres
         pg_catalog | pg_statio_user_sequences    | view  | postgres
         pg_catalog | pg_statio_user_tables       | view  | postgres
         pg_catalog | pg_statistic                | table | postgres
         pg_catalog | pg_stats                    | view  | postgres
        (28 rows)
        


Функции на pg_tablespace
~~~~~~~~~~~~~~~~~~~~~~~~


        => \df pg_catalog.pg_tablespace*
                                           List of functions
           Schema   |          Name           | Result data type | Argument data types |  Type  
        ------------+-------------------------+------------------+---------------------+--------
         pg_catalog | pg_tablespace_databases | SETOF oid        | oid                 | normal
         pg_catalog | pg_tablespace_location  | text             | oid                 | normal
         pg_catalog | pg_tablespace_size      | bigint           | name                | normal
         pg_catalog | pg_tablespace_size      | bigint           | oid                 | normal
        (4 rows)
        


Текст запросов
~~~~~~~~~~~~~~

Можно перезапустить psql с ключом -E, а можно и так:

        => \set ECHO_HIDDEN on

        => \d+ pg_views
        ********* QUERY **********
        SELECT c.oid,
          n.nspname,
          c.relname
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ '^(pg_views)$'
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 2, 3;
        dba1_03_install_lab.sh dba1_04_psql_lab.sh dba1_05_databases_lab.sh dba1_06_tablespaces_lab.sh dba1_07_system_catalog_lab.sh dba1_08_objects_lab.sh dba1_09_roles_lab.sh dba1_10_schemas_lab.sh dba1_11_privileges_lab.sh dba1_12_configuration_lab.sh dba1_13_authentication_lab.sh dba1_14_monitoring_lab.sh dba1_15_maintenance_lab.sh dba1_16_logical_backup_lab.sh dba1_17_physical_backup_lab.sh lib params pipe_1_in pipe_1_out
        
        ********* QUERY **********
        SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoids, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
        , c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident
        FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
        WHERE c.oid = '11167';
        dba1_03_install_lab.sh dba1_04_psql_lab.sh dba1_05_databases_lab.sh dba1_06_tablespaces_lab.sh dba1_07_system_catalog_lab.sh dba1_08_objects_lab.sh dba1_09_roles_lab.sh dba1_10_schemas_lab.sh dba1_11_privileges_lab.sh dba1_12_configuration_lab.sh dba1_13_authentication_lab.sh dba1_14_monitoring_lab.sh dba1_15_maintenance_lab.sh dba1_16_logical_backup_lab.sh dba1_17_physical_backup_lab.sh lib params pipe_1_in pipe_1_out
        
        ********* QUERY **********
        SELECT a.attname,
          pg_catalog.format_type(a.atttypid, a.atttypmod),
          (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
           FROM pg_catalog.pg_attrdef d
           WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
          a.attnotnull, a.attnum,
          (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
           WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
          NULL AS indexdef,
          NULL AS attfdwoptions,
          a.attstorage,
          CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget, pg_catalog.col_description(a.attrelid, a.attnum)
        FROM pg_catalog.pg_attribute a
        WHERE a.attrelid = '11167' AND a.attnum > 0 AND NOT a.attisdropped
        ORDER BY a.attnum;
        dba1_03_install_lab.sh dba1_04_psql_lab.sh dba1_05_databases_lab.sh dba1_06_tablespaces_lab.sh dba1_07_system_catalog_lab.sh dba1_08_objects_lab.sh dba1_09_roles_lab.sh dba1_10_schemas_lab.sh dba1_11_privileges_lab.sh dba1_12_configuration_lab.sh dba1_13_authentication_lab.sh dba1_14_monitoring_lab.sh dba1_15_maintenance_lab.sh dba1_16_logical_backup_lab.sh dba1_17_physical_backup_lab.sh lib params pipe_1_in pipe_1_out
        
        ********* QUERY **********
        SELECT pg_catalog.pg_get_viewdef('11167'::pg_catalog.oid, true);
        dba1_03_install_lab.sh dba1_04_psql_lab.sh dba1_05_databases_lab.sh dba1_06_tablespaces_lab.sh dba1_07_system_catalog_lab.sh dba1_08_objects_lab.sh dba1_09_roles_lab.sh dba1_10_schemas_lab.sh dba1_11_privileges_lab.sh dba1_12_configuration_lab.sh dba1_13_authentication_lab.sh dba1_14_monitoring_lab.sh dba1_15_maintenance_lab.sh dba1_16_logical_backup_lab.sh dba1_17_physical_backup_lab.sh lib params pipe_1_in pipe_1_out
        
        ********* QUERY **********
        SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))
        FROM pg_catalog.pg_rewrite r
        WHERE r.ev_class = '11167' AND r.rulename != '_RETURN' ORDER BY 1;
        dba1_03_install_lab.sh dba1_04_psql_lab.sh dba1_05_databases_lab.sh dba1_06_tablespaces_lab.sh dba1_07_system_catalog_lab.sh dba1_08_objects_lab.sh dba1_09_roles_lab.sh dba1_10_schemas_lab.sh dba1_11_privileges_lab.sh dba1_12_configuration_lab.sh dba1_13_authentication_lab.sh dba1_14_monitoring_lab.sh dba1_15_maintenance_lab.sh dba1_16_logical_backup_lab.sh dba1_17_physical_backup_lab.sh lib params pipe_1_in pipe_1_out
        
                       View "pg_catalog.pg_views"
           Column   | Type | Modifiers | Storage  | Description 
        ------------+------+-----------+----------+-------------
         schemaname | name |           | plain    | 
         viewname   | name |           | plain    | 
         viewowner  | name |           | plain    | 
         definition | text |           | extended | 
        View definition:
         SELECT n.nspname AS schemaname,
            c.relname AS viewname,
            pg_get_userbyid(c.relowner) AS viewowner,
            pg_get_viewdef(c.oid) AS definition
           FROM pg_class c
             LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE c.relkind = 'v'::"char";
        

        => \q
