PGXN

PostgreSQL Extension Network

Contents

dbtoyaml - Database to YAML

Name

dbtoyaml -- extract the schema of a PostgreSQL database in YAML format

Synopsys

dbtoyaml [option...] dbname

Description

dbtoyaml is a utility for extracting the schema of a
PostgreSQL database to a YAML formatted
specification. Note that JSON is an official
subset of YAML version 1.2, so the dbtoyaml output should
also be compatible with JSON tools.

The above should be mostly self-explanatory. The example database has
two tables, named t1 and t2, the first --owned by user
'alice'-- in the public schema and the second --owned by user
'bob'-- in a schema named s1 (also owned by 'bob').
The columns: specifications directly under each table list each
column in that table, in the same order as shown by PostgreSQL. The
specifications primary_key:, foreign_keys: and
check_constraints: define PRIMARY KEY, FOREIGN KEY and CHECK
constraints for a given table. Additional specifications (not shown)
define unique constraints and indexes.

User 'bob' has granted all privileges to 'alice' on the s1 schema.
On table t2, he also granted SELECT to PUBLIC; INSERT, UPDATE and
DELETE to 'alice' with GRANT OPTION; and she has in turn granted
INSERT to user 'carol'.

dbtoyaml currently supports extracting information about
nearly all types of PostgreSQL database objects. See api-ref
for a list of supported objects.

Options

dbtoyaml accepts the following command-line arguments (in
addition to the cmdargs):

dbname

Specifies the name of the database whose schema is to extracted.

-n schema, ---schema= schema

Extracts only a schema matching schema. By default, all schemas
are extracted. Multiple schemas can be extracted by using multiple
-n switches. Note that normally all objects that belong to the
schema are extracted as well, unless excluded otherwise.

-N schema, ---exclude-schema= schema

Does not extract schema matching schema. This can be given more
than once to exclude several schemas.

-O, ---no-owner

Do not output object ownership information. By default, as seen
in the sample output above, database objects (schemas, tables,
etc.) that can be owned by some user, are shown with an "owner:
username" element. The -O switch suppresses all those
lines.

-t table, ---table= table

Extract only tables matching table. Multiple tables can be
extracted by using multiple -t switches. Note that selecting
a table may cause other objects, such as an owned sequence, to be
extracted as well

-T table, ---exclude-table= table

Do not extract tables matching table. Multiple tables can be
excluded by using multiple -T switches.

-x, ---no-privileges

Do not output access privilege information. By default, as seen
in the sample output above, if specific GRANTs have been issued on
various objects (schemas, tables, etc.), the privileges are shown
under each object. The -x switch suppresses all those lines.

Examples

To extract a database called moviesdb into a file:

dbtoyaml moviesdb > moviesdb.yaml

To extract only the schema named store:

dbtoyaml --schema=store moviesdb > moviesdb.yaml

To extract the tables named film and genre:

dbtoyaml -t film -t genre moviesdb -o moviesdb.yaml

To extract objects, to standard output, except those in schemas
product and store: