Product Documentation

Appearance

All platforms

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 OptionsEnvironment VariableDescriptionExample
-h or --hostYBHOSTDestination server host name. Default: localhost
-h test.ybsystem.io



export YBHOST=test.ybsystem.io


-p or --portYBPORTDestination server port number. Default: 5432
--port 5433



export YBPORT=5433


-U or --usernameYBUSERDatabase login username. No default.
-U bobr



export YBUSER=bobr


-W or --passwordYBPASSWORDInteractive 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';
...