Generating DDL ​

You can generate DDL for a specific database by using the ybdumpschema and ybdumproles client tools. To generate DDL for a specific database object, use the DESCRIBE command with the WITH DDL or ONLY DDL option.

The ybdumpschema tool generates DDL for the following objects in the specified database:

• Tables
• Views
• Schemas
• Sequences
• Stored procedures

The ybdumproles tool generates DDL for database users and roles.

You can run these tools from the directory on the client system where the ybtools were installed (/usr/bin on Linux platforms). Use the --help option to see the command parameters for each tool:

$ **ybdumpschema --help
**Generate the DDL to re-create the objects in a database
Usage:
  ybdumpschema [OPTION]... [DBNAME]
  -d, --dbname=DBNAME      database to re-create
  -f, --file=FILENAME      output file name
  -h, --host=HOSTNAME      database server host
  -O, --owner              object ownership
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt
  -x, --privileges         object privileges (grant/revoke)
  -?, --help               show this help, then exit

$ **ybdumproles --help**
Generate the DDL to re-create the roles in a database cluster
Usage:
  ybdumproles [OPTION]...
  -d, --dbname=CONNSTR     connect using connection string
  -f, --file=FILENAME      output file name
  -h, --host=HOSTNAME      database server host
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt
  -?, --help               show this help, then exit

ybdumpschema and ybdumproles Options ​

Most of the options for these two tools are the same.

-d, --dbname=DBNAME

Specify the name of a single database on the host system. The value of the environment variable YBDATABASE is used if you do not specify a database. For ybdumpschema this is the name of the database where you want to generate DDL.

-d, --dbname=CONNSTR

For ybdumproles, specifies a database connection string. This client connects to several databases; therefore, the database name in the connection string is ignored.

-f, --file=FILENAME

Specify a file name or a path to a file where you want to send the DDL output. Make sure you are running as a user with write permissions on that file and location. If you do not specify a file name, the output prints to stdout.

-h, --host=HOSTNAME

Specify the name of the host system where the database resides. See the following table.

-O, --owner

Generate DDL for object ownership (ALTER TABLE...OWNER TO statements). This option applies to ybdumpschema only.

-p, --port=PORT

See the following table.

-U, --username=NAME

Database username. See the following table.

-w, --no-password

Do not prompt for the database user's password.

-W, --password

Prompt for the database user's password. The value of the environment variable YBPASSWORD is read if no password is supplied at the command line. See the following table.

-x, --privileges

Generate DDL for privileges on objects (GRANT and REVOKE statements). This option applies to ybdumpschema only.

-?, --help

Return help text for all of the options.

Database Connection Parameters ​

ybdumpschema Examples ​

Export DDL for a database named ybdb1 to a file named ybdb1.ddl:

$ **ybdumpschema -d ybdb1 -f /home/yb100/ybdb1.ddl -U yellowbrick
**Password: 
$

Send DDL for the premdb database to stdout:

$ **ybdumpschema -d premdb -U bobr**
Password: 

SET client_encoding = 'LATIN9';
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

--
-- Name: awayteam; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE awayteam (
   atid smallint,
   name character varying(30)
)
DISTRIBUTE REPLICATE;
...

Generate DDL for object permissions (in addition to all other DDL):

$ **ybdumpschema -d premdb -x -U bobr**
...
--
-- Name: public; Type: ACL; Schema: -; Owner: yb100
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM yb100;
GRANT ALL ON SCHEMA public TO yb100;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- Name: match; Type: ACL; Schema: public; Owner: yb100
--

REVOKE ALL ON TABLE match FROM PUBLIC;
REVOKE ALL ON TABLE match FROM yb100;
GRANT ALL ON TABLE match TO yb100;
GRANT SELECT ON TABLE match TO bobr;
...

ybdumproles Example ​

$ ybdumproles --file=dumproles.txt
$ more dumproles.txt | grep bobr
CREATE ROLE bobr;
ALTER ROLE bobr WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN;

Parent topic:Database Administration