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 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

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;