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

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
  -v, --verbose            verbose mode
  -w, --no-password        never prompt for password
  -W, --password           force password prompt
  -x, --privileges         object privileges (grant/revoke)
  -V, --version            show version information and exit
  -?, --help               show this help, then exit

$ ybdumproles --help
Generate the DDL to re-create the roles in a database
Usage:
  ybdumproles [OPTION]...
  -d, --dbname=DBNAME      database to re-create
  -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
  -V, --version            show version information and exit
  -?, --help               show this help, then exit

ybdumpschema and ybdumproles Options

Most of the options for these two tools are the same; ybdumpschema has a few additional options.

-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.
-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.
-v, --verbose: Verbose mode, which prints messages to stderr as the tool processes various object types and settings.
-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;
...

In this example, ybdumpschema runs against an empty database, yellowbrick_test, with stdout redirected to /dev/null. The only information printed to the terminal is the verbose output, which is sent to stderr.

$ ybdumpschema -d yellowbrick_test -v > /dev/null
ybdumpschema: reading extensions
ybdumpschema: identifying extension members
ybdumpschema: reading schemas
ybdumpschema: reading user-defined tables
ybdumpschema: reading user-defined functions
ybdumpschema: reading user-defined types
ybdumpschema: reading procedural languages
ybdumpschema: reading user-defined aggregate functions
...
ybdumpschema: saving encoding = LATIN9
ybdumpschema: saving standard_conforming_strings = on
ybdumpschema: saving database definition
ybdumpschema: creating SCHEMA "public"
ybdumpschema: creating COMMENT "SCHEMA public"
ybdumpschema: setting owner and privileges for DATABASE "yellowbrick_test"
ybdumpschema: setting owner and privileges for SCHEMA "public"
ybdumpschema: setting owner and privileges for COMMENT "SCHEMA public"

ybdumproles Example

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

$ ybdumproles -d premdb -h yb102 -U yellowbrick
Password: 
--
-- Yellowbrick database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'LATIN9';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE bobr;
ALTER ROLE bobr WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'md50324e3faf22a8ae63c13ce2a97670f45';
...