postgresql 获取 table ddl 之一 pgddl

妖狐艹你老母 2022-09-12 11:45 9阅读 0赞

os: centos 7.6.1810  
db: postgresql 10

postgresql 没有 mysql 数据库 show create table 命令,所以查看表的定义就稍微麻烦一些。

本次先试下 pgddl

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.6.1810 (Core) 
    # 
    # yum list installed|grep -i postgre
    postgresql10.x86_64                10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-contrib.x86_64        10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-devel.x86_64          10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-docs.x86_64           10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-libs.x86_64           10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-odbc.x86_64           13.00.0000-1PGDG.rhel7              @pgdg10  
    postgresql10-plperl.x86_64         10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-plpython.x86_64       10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-plpython3.x86_64      10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-pltcl.x86_64          10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-server.x86_64         10.18-1PGDG.rhel7                   @pgdg10  
    postgresql10-tcl.x86_64            2.7.5-1.rhel7                       @pgdg10  
    postgresql10-test.x86_64           10.18-1PGDG.rhel7                   @pgdg10

# 下载安装 #

# cd /opt
    # wget https://github.com/lacanoid/pgddl/archive/refs/tags/0.22.tar.gz
    # tar -zxvf ./0.22.tar.gz
    # cd pgddl-0.22/
    
    # make PG_CONFIG=/usr/pgsql-10/bin/pg_config
    # make PG_CONFIG=/usr/pgsql-10/bin/pg_config install
    # make PG_CONFIG=/usr/pgsql-10/bin/pg_config installcheck

# su - postgres
    $ psql
    
    postgres=# select * from pg_available_extensions where name like '%ddl%';
     name | default_version | installed_version |         comment         
    ------+-----------------+-------------------+-------------------------
     ddlx | 0.22            |                   | DDL eXtractor functions
    (1 row)
    
    postgres=# create extension ddlx schema pg_catalog;

postgres=# create table tmp_t1 (
    id  int8 primary key,
    name varchar(100)
    )
    ;
    
    postgres=# select ddlx_script('tmp_t1'::regclass);
    
                      ddlx_script                   
    ------------------------------------------------
     BEGIN;                                        +
                                                   +
     /*                                            +
     DROP TABLE tmp_t1; -- !!!ATTENTION!!! --      +
     */                                            +
                                                   +
     -- Type: TABLE ; Name: tmp_t1; Owner: postgres+
                                                   +
     CREATE TABLE tmp_t1 (                         +
         id bigint NOT NULL,                       +
         name character varying(100)               +
     );                                            +
                                                   +
     COMMENT ON TABLE tmp_t1 IS NULL;              +
                                                   +
     ALTER TABLE tmp_t1 ADD CONSTRAINT tmp_t1_pkey +
       PRIMARY KEY (id);                           +
                                                   +
                                                   +
     END;                                          +
     
    (1 row)

查看 pg\_pathman 创建的 range 分区表,在输出上还有有些欠缺

postgres=# select ddlx_script('tmp_range'::regclass);
                                                                             ddlx_script                                                                         
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
     BEGIN;                                                                                                                                                     +
                                                                                                                                                                +
     /*                                                                                                                                                         +
     ALTER TABLE tmp_range_2 DROP CONSTRAINT pathman_tmp_range_2_check;                                                                                         +
     DROP SEQUENCE IF EXISTS tmp_range_seq;                                                                                                                     +
     DROP TABLE tmp_range_2; -- !!!ATTENTION!!! --                                                                                                              +
     ALTER TABLE tmp_range_4 DROP CONSTRAINT pathman_tmp_range_4_check;                                                                                         +
     DROP TABLE tmp_range_1; -- !!!ATTENTION!!! --                                                                                                              +
     DROP TABLE tmp_range_4; -- !!!ATTENTION!!! --                                                                                                              +
     ALTER TABLE tmp_range_1 DROP CONSTRAINT pathman_tmp_range_1_check;                                                                                         +
     DROP TABLE tmp_range_3; -- !!!ATTENTION!!! --                                                                                                              +
     ALTER TABLE tmp_range_3 DROP CONSTRAINT pathman_tmp_range_3_check;                                                                                         +
                                                                                                                                                                +
     DROP TABLE tmp_range; -- !!!ATTENTION!!! --                                                                                                                +
     */                                                                                                                                                         +
                                                                                                                                                                +
     -- Type: TABLE ; Name: tmp_range; Owner: postgres                                                                                                          +
                                                                                                                                                                +
     CREATE TABLE tmp_range (                                                                                                                                   +
         id integer,                                                                                                                                            +
         name text,                                                                                                                                             +
         insert_timestamp timestamp with time zone NOT NULL                                                                                                     +
     );                                                                                                                                                         +
                                                                                                                                                                +
     COMMENT ON TABLE tmp_range IS NULL;                                                                                                                        +
                                                                                                                                                                +
                                                                                                                                                                +
     -- DEPENDANTS                                                                                                                                              +
                                                                                                                                                                +
     ALTER TABLE tmp_range_3 ADD CONSTRAINT pathman_tmp_range_3_check                                                                                           +
       CHECK (insert_timestamp >= '2021-01-01 00:00:00+08'::timestamp with time zone AND insert_timestamp < '2022-01-01 00:00:00+08'::timestamp with time zone);+
     COMMENT ON CONSTRAINT pathman_tmp_range_3_check ON tmp_range_3 IS NULL;                                                                                    +
                                                                                                                                                                +
                                                                                                                                                                +
     -- Type: TABLE ; Name: tmp_range_3; Owner: postgres                                                                                                        +
                                                                                                                                                                +
     CREATE TABLE tmp_range_3 ()                                                                                                                                +
       INHERITS(tmp_range);                                                                                                                                     +
                                                                                                                                                                +
     COMMENT ON TABLE tmp_range_3 IS NULL;                                                                                                                      +
                                                                                                                                                                +
     ALTER TABLE tmp_range_3 ADD CONSTRAINT pathman_tmp_range_3_check                                                                                           +
       CHECK (insert_timestamp >= '2021-01-01 00:00:00+08'::timestamp with time zone AND insert_timestamp < '2022-01-01 00:00:00+08'::timestamp with time zone);+
                                                                                                                                                                +
                                                                                                                                                                +
     ALTER TABLE tmp_range_1 ADD CONSTRAINT pathman_tmp_range_1_check                                                                                           +
       CHECK (insert_timestamp >= '2019-01-01 00:00:00+08'::timestamp with time zone AND insert_timestamp < '2020-01-01 00:00:00+08'::timestamp with time zone);+
     COMMENT ON CONSTRAINT pathman_tmp_range_1_check ON tmp_range_1 IS NULL;                                                                                    +
                                                                                                                                                                +
                                                                                                                                                                +
     -- Type: TABLE ; Name: tmp_range_4; Owner: postgres                                                                                                        +
                                                                                                                                                                +
     CREATE TABLE tmp_range_4 ()                                                                                                                                +
       INHERITS(tmp_range);                                                                                                                                     +
                                                                                                                                                                +
     COMMENT ON TABLE tmp_range_4 IS NULL;                                                                                                                      +
                                                                                                                                                                +
     ALTER TABLE tmp_range_4 ADD CONSTRAINT pathman_tmp_range_4_check                                                                                           +
       CHECK (insert_timestamp >= '2018-01-01 00:00:00+08'::timestamp with time zone AND insert_timestamp < '2019-01-01 00:00:00+08'::timestamp with time zone);+
                                                                                                                                                                +
                                                                                                                                                                +
     -- Type: TABLE ; Name: tmp_range_1; Owner: postgres                                                                                                        +
                                                                                                                                                                +
     CREATE TABLE tmp_range_1 ()                                                                                                                                +
       INHERITS(tmp_range);                                                                                                                                     +
                                                                                                                                                                +
     COMMENT ON TABLE tmp_range_1 IS NULL;                                                                                                                      +
                                                                                                                                                                +
     ALTER TABLE tmp_range_1 ADD CONSTRAINT pathman_tmp_range_1_check                                                                                           +
       CHECK (insert_timestamp >= '2019-01-01 00:00:00+08'::timestamp with time zone AND insert_timestamp < '2020-01-01 00:00:00+08'::timestamp with time zone);+
                                                                                                                                                                +
                                                                                                                                                                +
     ALTER TABLE tmp_range_4 ADD CONSTRAINT pathman_tmp_range_4_check                                                                                           +
       CHECK (insert_timestamp >= '2018-01-01 00:00:00+08'::timestamp with time zone AND insert_timestamp < '2019-01-01 00:00:00+08'::timestamp with time zone);+
     COMMENT ON CONSTRAINT pathman_tmp_range_4_check ON tmp_range_4 IS NULL;                                                                                    +
                                                                                                                                                                +
                                                                                                                                                                +
     -- Type: TABLE ; Name: tmp_range_2; Owner: postgres                                                                                                        +
                                                                                                                                                                +
     CREATE TABLE tmp_range_2 ()                                                                                                                                +
       INHERITS(tmp_range);                                                                                                                                     +
                                                                                                                                                                +
     COMMENT ON TABLE tmp_range_2 IS NULL;                                                                                                                      +
                                                                                                                                                                +
     ALTER TABLE tmp_range_2 ADD CONSTRAINT pathman_tmp_range_2_check                                                                                           +
       CHECK (insert_timestamp >= '2020-01-01 00:00:00+08'::timestamp with time zone AND insert_timestamp < '2021-01-01 00:00:00+08'::timestamp with time zone);+
                                                                                                                                                                +
                                                                                                                                                                +
     -- Type: SEQUENCE ; Name: tmp_range_seq; Owner: postgres                                                                                                   +
                                                                                                                                                                +
     CREATE SEQUENCE tmp_range_seq;                                                                                                                             +
                                                                                                                                                                +
     COMMENT ON SEQUENCE tmp_range_seq IS NULL;                                                                                                                 +
                                                                                                                                                                +
     ALTER SEQUENCE tmp_range_seq                                                                                                                               +
      INCREMENT BY 1                                                                                                                                            +
      MINVALUE 1                                                                                                                                                +
      MAXVALUE 9223372036854775807                                                                                                                              +
      START WITH 1                                                                                                                                              +
      NO CYCLE;                                                                                                                                                 +
                                                                                                                                                                +
     ALTER TABLE tmp_range_2 ADD CONSTRAINT pathman_tmp_range_2_check                                                                                           +
       CHECK (insert_timestamp >= '2020-01-01 00:00:00+08'::timestamp with time zone AND insert_timestamp < '2021-01-01 00:00:00+08'::timestamp with time zone);+
     COMMENT ON CONSTRAINT pathman_tmp_range_2_check ON tmp_range_2 IS NULL;                                                                                    +
                                                                                                                                                                +
                                                                                                                                                                +
     END;                                                                                                                                                       +
     
    (1 row)

参考:  
https://github.com/lacanoid/pgddl