Generating DDL

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

The ybdumpschema tool (ybdmpsch on Windows clients) generates DDL for the following objects in the specified database:
  • Tables
  • Views
  • Schemas
  • Sequences
  • Stored procedures

The ybdumproles tool (ybdmprol on Windows clients) 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

Command-Line Options Environment Variable Description Example
-h or --host YBHOST Destination server host name. Default: localhost
-h test.ybsystem.io
export YBHOST=test.ybsystem.io
-p or --port YBPORT Destination server port number. Default: 5432
--port 5433
export YBPORT=5433
-U or --username YBUSER Database login username. No default.
-U bobr
export YBUSER=bobr
-W or --password YBPASSWORD Interactive prompt for the database user's password. No default.
--password
export YBPASSWORD=********

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;