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. Forybdumpschema
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 toybdumpschema
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
andREVOKE
statements). This option applies toybdumpschema
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 |
|
-p or --port |
YBPORT |
Destination server port number. Default: 5432 |
|
-U or --username |
YBUSER |
Database login username. No default. |
|
-W or --password |
YBPASSWORD |
Interactive prompt for the database user's password. No default. |
|
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';
...