Tales From A Lazy Fat DBA

Hi Guys,

One of my friend recently asked if there is any way to get DDL of all objects that exists inside a schema in PostgreSQL without using any third party tools, something similar like dbms_metadata what we have in Oracle. Though it sounds trivial, but it’s not and this indeed an interesting question. I mean if you have to collect DDL for an Index, View, constraint, trigger or a function you have predefined/in-build PostgreSQL functions (pg_get_*def) and it will be easy to get the structure, but what if you want DDL of a Table, sequence or a type (There few more) ?

You might be thinking of doing concatenations or by doing some coding, and yes that can be done but that will be tricky and complicated. So there isn’t any direct way of generating definitions of said objects, but here comes the PG_DUMP utility for the rescue, you can take the dump of the entire schema and redirect its output in to a SQL file and you can get all your DDLs from the dump file.

Let’s do some hands on, I am on EDB PostgreSQL Version 10.12.20.

[enterprisedb@fatdba ~]$ psql
psql.bin (10.12.20)
Type "help" for help.

enterprisedb=# set search_path=migr;
SET
enterprisedb=# \dtsvi+
                                                               List of relations
 Schema |            Name             |   Type   |    Owner     |         Table          |    Size    |              Description
--------+-----------------------------+----------+--------------+------------------------+------------+----------------------------------------
 migr   | audits                      | table    | enterprisedb |                        | 0 bytes    |
 migr   | audits_audit_id_seq         | sequence | enterprisedb |                        | 8192 bytes |
 migr   | audits_pkey                 | index    | enterprisedb | audits                 | 8192 bytes |
 migr   | bar                         | table    | enterprisedb |                        | 8192 bytes |
 migr   | bar_pk                      | index    | enterprisedb | bar                    | 8192 bytes |
 migr   | bigtab                      | table    | enterprisedb |                        | 944 kB     |
 migr   | bigtab1                     | table    | enterprisedb |                        | 112 kB     |
 migr   | bigtab1_vw                  | view     | enterprisedb |                        | 0 bytes    |
 migr   | foo                         | table    | enterprisedb |                        | 8192 bytes |
 migr   | foo_bar                     | table    | enterprisedb |                        | 8192 bytes |
 migr   | foo_pk                      | index    | enterprisedb | foo                    | 8192 bytes |
 migr   | i_mlog$_bar                 | index    | enterprisedb | mlog$_bar              | 8192 bytes |
 migr   | i_mlog$_foo                 | index    | enterprisedb | mlog$_foo              | 8192 bytes |
 migr   | mlog$_bar                   | table    | enterprisedb |                        | 8192 bytes | snapshot log for master table MIGR.BAR
 migr   | mlog$_foo                   | table    | enterprisedb |                        | 8192 bytes | snapshot log for master table MIGR.FOO
 migr   | person_address_details      | table    | enterprisedb |                        | 40 kB      |
 migr   | person_address_details_pkey | index    | enterprisedb | person_address_details | 16 kB      |
 migr   | person_info                 | table    | enterprisedb |                        | 40 kB      |
 migr   | person_info_pkey            | index    | enterprisedb | person_info            | 16 kB      |
 migr   | trigger_test                | table    | enterprisedb |                        | 8192 bytes |
(20 rows)
 

This is how you can generate DDL’s for your views, Indexes and others.

enterprisedb=# select pg_get_viewdef('bigtab1_vw'::regclass, true);
           pg_get_viewdef
------------------------------------
  SELECT bigtab1.id,               +
     bigtab1.created_date,         +
     bigtab1.lookup_id,            +
     bigtab1.data                  +
    FROM bigtab1                   +
   WHERE bigtab1.id > 950::numeric;
(1 row)
                           
enterprisedb=#  select pg_get_indexdef('person_address_details_pkey'::regclass);
                                                pg_get_indexdef
----------------------------------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX person_address_details_pkey ON migr.person_address_details USING btree (person_address_id)
(1 row)
 

Let’s take a pg_dump backup of this schema and will look out for table and other object definitions.

[enterprisedb@fatdba dumpddl]$ pg_dump --schema migr > migr_ddl.sql
[enterprisedb@fatdba dumpddl]$
[enterprisedb@fatdba dumpddl]$ ls -ltrh
total 728K
-rw-rw-r--. 1 enterprisedb enterprisedb 728K Nov 10 11:34 migr_ddl.sql
 

[enterprisedb@fatdba dumpddl]$ more migr_ddl.sql |grep -A 5 --color 'CREATE TABLE'
CREATE TABLE migr.audits (
    audit_id bigint NOT NULL,
    table_name character varying(255),
    transaction_name character varying(10),
    by_user character varying(30),
    transaction_date timestamp without time zone
--
CREATE TABLE migr.bar (
    foo numeric NOT NULL,
    bar numeric NOT NULL
);


--
CREATE TABLE migr.bigtab (
    id numeric(12,6),
    v1 character varying(10),
    padding character varying(50)
);

--
CREATE TABLE migr.bigtab1 (
    id numeric(10,0),
    created_date timestamp without time zone,
    lookup_id numeric(10,0),
    data character varying(50)
);
--
CREATE TABLE migr.foo (
    foo numeric NOT NULL
);


ALTER TABLE migr.foo OWNER TO enterprisedb;
--
CREATE TABLE migr.foo_bar (
    foo numeric,
    bar numeric,
    foo_rowid rowid,
    bar_rowid rowid
);
--
CREATE TABLE migr."mlog$_bar" (
    "m_row$$" character varying(255),
    "snaptime$$" timestamp without time zone,
    "dmltype$$" character varying(1),
    "old_new$$" character varying(1),
    "change_vector$$" bytea(255),
--
CREATE TABLE migr."mlog$_foo" (
    "m_row$$" character varying(255),
    "snaptime$$" timestamp without time zone,
    "dmltype$$" character varying(1),
    "old_new$$" character varying(1),
    "change_vector$$" bytea(255),
--
CREATE TABLE migr.person_address_details (
    person_address_id numeric(5,0) NOT NULL,
    person_id numeric(5,0),
    city character varying(15),
    state character varying(15),
    country character varying(20),
--
CREATE TABLE migr.person_info (
    person_id numeric(5,0) NOT NULL,
    first_name character varying(20),
    last_name character varying(20)
);

--
CREATE TABLE migr.trigger_test (
    id numeric NOT NULL,
    description character varying(50) NOT NULL
);

 

So, we finally got the DDL of tables and all objects inside the schema.

Hope It helped!
Prashant Dixit