dbtoyaml - Database to YAML¶
dbtoyaml – extract the schema of a PostgreSQL database in YAML format
dbtoyaml [option...] dbname
dbtoyaml is a utility for extracting the schema of a
PostgreSQL database to a YAML formatted
specification. By default, the specification is output as a single
output stream, which can be redirected or explicitly sent to a file.
As an alternative, the
--multiple-files option allows you to break
down the specification into multiple files, one for each object (see
Multiple File Output).
Note that JSON is an official subset of YAML version 1.2, so the dbtoyaml output should also be compatible with JSON tools.
The output format is as follows:
schema public: owner: postgres privileges: - postgres: - all - PUBLIC: - all table t1: check_constraints: check_expr: (c2 > 123) columns: - c2 columns: - c1: not_null: true type: integer - c2: type: smallint - c3: default: 'false' type: boolean - c4: type: text foreign_keys: t1_c2_fkey: columns: - c2 references: columns: - c21 schema: s1 table: t2 owner: alice primary_key: t1_pkey: columns: - c1 schema s1: owner: bob privileges: - bob: - all - alice: - all table t2: columns: - c21: not_null: true type: integer - c22: type: character varying(16) owner: bob primary_key: t2_pkey: columns: - c21 privileges: - bob: - all - PUBLIC: - select - alice: - insert: grantable: true - delete: grantable: true - update: grantable: true - carol: grantor: alice privs: - insert
The above should be mostly self-explanatory. The example database has
two tables, named
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’).
columns: specifications directly under each table list each
column in that table, in the same order as shown by PostgreSQL. The
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
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 Reference for a list of supported objects.
Multiple File Output¶
--multiple-files option breaks down the output into
multiple files under a given root directory. The root is created if
it does not exist. The root directory name defaults to
in the system configuration file. The location of the root directory
defaults to the configuration item
repository.path or can be
specified using the –repository option (see Configuration
and Common Command Line Options for further details).
The first level contains
schema.<name>.yaml files and
<name> is the name of the corresponding objects and
<objtype> is the type of top-level (non-schema) object. Note that
non-schema refers to PostgreSQL extensions, casts, languages or
foreign data wrappers.
The second level, i.e., the
schema.<name> subdirectories contain
<objtype>.<name>.yaml files for each object in the particular
schema (but see below for caveats).
Object Name Conflicts¶
The names of PostgreSQL objects can include characters that are not allowed in filesystem object names. The most common example is the division operator (‘/’), but even table names can include non-alphanumeric characters, if the identifiers are quoted.
In addition, one can define two or more objects with the same base
name, e.g., function
foo(integer) and function
foo(text), or a
"My Table" and another named
"my table" or
"MY TABLE". On certain operating systems, i.e., Windows, it is not
possible to create two files in the same directory that differ only in
the case of their characters.
In order to deal with the aforementioned issues,
certain objects in common files and transforms object identifiers so
that they are suitable for use in files and directories. For example,
the information for all user-defined casts are written to the file
cast.yaml in the root directory. Functions with the same name but
different arguments are written to a single file, e.g.,
function.foo.yaml in the first example above. Identifiers are
also converted to all lowercase, non-alphanumeric characters
(excluding underscore) are converted to underscores and, by default,
schema object names are truncated to 32 characters.
If two object names, thus transformed, map to the same string, then
the objects’ information is written to the same file, e.g.,
table.my_table.yaml in the second example above. If you prefer to
change the default truncation length, please define the environment
PYRSEAS_MAX_IDENT_LEN to some integer value (up to 63).
Version Control and Dropped Objects¶
It is expected that the output of
dbtoyaml --multiple-files will
be placed under version control. Further invocations should then
update the files in the same directory tree. However, if an object is
dropped from the database
dbtoyaml would normally only output
files for new or changed objects–and thus keep the dropped object
file under version control. To deal with dropped objects,
-d outputs a special YAML “index” file, named
database.<dbname>.yaml in the root directory. When
-d is run a second time, it looks for this “index” file and if
found, proceeds to delete the previous run’s
.yaml files before
outputting new ones.
dbtoyaml accepts the following command-line arguments (in addition to the Common Command Line Options):
Specifies the name of the database whose schema is to extracted.
Extracts the schema to a two-level directory tree. See Multiple File Output above.
Extracts only a schema matching schema. By default, all schemas are extracted. Multiple schemas can be extracted by using multiple
-nswitches. Note that normally all objects that belong to the schema are extracted as well, unless excluded otherwise.
Does not extract schema matching schema. This can be given more than once to exclude several schemas.
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
-Oswitch suppresses all those lines.
NOTE: If you specify –no-owner, you will most likely also want to specify
--no-privileges. If the former is used without the latter the resulting YAML output will have privilege information without user data, which will cause errors if the YAML is then fed to yamltodb - YAML to Database.
Extract only tables matching table. Multiple tables can be extracted by using multiple
-tswitches. Note that selecting a table may cause other objects, such as an owned sequence, to be extracted as well
Do not extract tables matching table. Multiple tables can be excluded by using multiple
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
-xswitch suppresses all those lines.
See also the NOTE under
To extract a database called
moviesdb into a file:
dbtoyaml moviesdb > moviesdb.yaml
To extract only the schema named
dbtoyaml --schema=store moviesdb > moviesdb.yaml
To extract the tables named
dbtoyaml -t film -t genre moviesdb -o moviesdb.yaml
To extract objects, to standard output, except those in schemas
dbtoyaml -N product -N store moviesdb
To extract objects to a directory under version control:
dbtoyaml moviesdb -d movies/dbspec