The pgModeler command-line interface, or simply pgmodeler-cli
, encapsulates the most important pgModeler operations like export, import, diff, and other auxiliary features. The idea behind the CLI is to enable the user to perform a set of operations over a model or database without the need to do the tasks in the graphical interface. This approach can be handy when dealing with software deployment, for instance.
To exemplify a software deployment using the CLI, let's say you need to export a model to a database server during the installation of Java-based software through the Apache Ant tool. You can easily integrate the pgmodeler-cli
command to execute the export process taking a database model file as an input parameter in the middle of the deployment process like this:pgmodeler-cli -ed -if demo.dbm -ca local-db
.
The command-line interface has dozens of parameters that can be combined to optimize operations to be executed. One important thing to observe is that the CLI accepts short and long parameters and they can appear in any order. The options must be separated from their values (when accepted) by a blank space. All options are described below:
pgModeler command line interface. Version 1.1.2 - 20240314.c780133d0 Qt 6.6.2 PostgreSQL Database Modeler Project - pgmodeler.io Copyright 2006-2024 Raphael Araújo e SilvaUsage: pgmodeler-cli [OPTIONS] This CLI tool provides several operations over models and databases without the need to perform them in the pgModeler's graphical interface. All available options are described below. Operation mode options: -ef, --export-to-file Exports the input model to SQL script file(s). -ep, --export-to-png Exports the input model to a PNG image. -es, --export-to-svg Exports the input model to a SVG file. -ec, --export-to-dict Exports the input model to a data dictionary in HTML format. -ed, --export-to-dbms Exports the input model directly to a PostgreSQL server. -lc, --list-conns Lists the available connections in file connections.conf. -im, --import-db Import a database to an output file. -df, --diff Compares a model and a database or two databases generating the SQL script to sync the latter in relation to the first. -fm, --fix-model Tries to fix the structure of the input model file to make it loadable again. -cc, --create-configs Creates the pgModeler's configuration folder and files in the user's local storage. -mt, --dbm-mime-type [ACTION] Handles the DBM file association to pgModeler binaries. The ACTION can be [install | uninstall]. -h, --help Shows this help menu. General options: -if, --input [FILE] Input model file (.dbm). This is mandatory for export and model fix operations. -id, --input-db [DBNAME] Input database name. This is mandatory for import operation. -of, --output [FILE|DIRECTORY] Output file or directory. This is mandatory for fixing models or exporting to SQL, HTML, PNG, or SVG. -v, --pgsql-ver Force the PostgreSQL syntax to the specified version when generating SQL code. The version string must be in the form of [major].[minor], e.g., 16.0. -s, --silent Silent execution. Only critical messages and errors are shown during the process. SQL file export options: -sp, --split The SQL file is generated per object. The files will be named in such a way to reflect the correct creation order of the objects. -ds, --dependencies Includes the object's dependencies SQL code in the generated file. (Only for split mode) -cs, --children Includes the object's children SQL code in the generated file. (Only for split mode) PNG and SVG export options: -sg, --show-grid Draws the grid in the exported image. -sl, --show-delimiters Draws the page delimiters in the exported image. -pp, --page-by-page Each page will be exported in a separated image. (Only for PNG images) -oc, --override-bg-color Don't use the original canvas color in the exported image, instead, a white background is used. (Only for PNG images) -zf, --zoom [FACTOR] Applies a zoom (in percent) before export to an image. Accepted zoom interval: 10-500 (Only for PNG images) Data dictionary export options: -sp, --split The data dictionaries are generated in separated files inside the specified output directory. -ni, --no-index Avoids the generation of the index that is used to help navigate through the data dictionary. DBMS export options: -ir, --ignore-duplicates Ignores errors related to duplicate objects that eventually exist in the server. -ic, --ignore-error-codes [CODES] Ignores additional errors by their codes. A comma-separated list of alphanumeric codes should be provided. -dd, --drop-database Drop the database before executing an export process. -ff, --force Forces the termination of all connections to the target database before dropping it. This option is ignored when exporting to PostgreSQL 12 or below. -do, --drop-objects Runs the DROP commands attached to objects in which SQL code is enabled. -sm, --simulate Simulates an export process by executing all steps but undoing any modification in the end. -tn, --use-tmp-names Generates temporary names for database, roles, and tablespaces when in simulation mode. Connection options: -ca, --conn-alias [ALIAS] Connection configuration alias to be used. -H, --host [HOST] PostgreSQL host in which a task will operate. -p, --port [PORT] PostgreSQL host listening port. -u, --user [USER] PostgreSQL username. -w, --passwd [PASSWORD] PostgreSQL user password. -D, --initial-db [DBNAME] Connection's initial database. Database import options: -ie, --ignore-errors Ignores all errors and tries to create as many as possible objects. -is, --import-sys-objs Imports built-in system objects. This option causes the model to bloat due to the importing of unneeded objects. -ix, --import-ext-objs Imports extension objects. This option causes the model to bloat due to the importing of unneeded objects. -cl, --comments-as-aliases Use objects comments as aliases. This option is takes effect on objects graphically represented in the database model. -fo, --filter-objects [FILTER] Makes the import process retrieve only those objects matching the filter(s). The FILTER must be in the form type:pattern:mode. -om, --only-matching Makes only objects matching the provided filter(s) to be imported. Those not matching filter(s) are discarded. -mn, --match-by-name Makes the objects matching to be performed over their names instead of their signature ([schema].[name]). -fc, --force-children [OBJECTS] Forces the importing of children objects related to tables/views/foreign tables matched by the filter(s). The OBJECTS is a comma-separated list types. -d, --debug-mode Runs the import in debug mode printing all queries executed on the server. Diff options: -ct, --compare-to [DBNAME] The database used in the comparison. All the SQL code generated is applied to it. -pd, --partial Switches to the partial diff operation. A set of object filters should be provided using the import option --filter-objects. -ff, --force Forces a full diff if the provided filters were not able to retrieve objects for a partial diff operation. -st, --start-date Matches all database model objects in which the modification date starts on the specified date. (Only for partial diff) -et, --end-date Matches all database model objects in which the modification date ends on the specified date. (Only for partial diff) -sd, --save Saves the generated diff code to the output file. -ad, --apply Applies the generated diff code on the database server. -np, --no-preview Don't preview the generated diff code before applying it to the server. -dc, --drop-cluster-objs Drop cluster-level objects like roles and tablespaces. -rv, --revoke-perms Revokes permissions already set on the database. New permissions configured in the input model are still applied. -dm, --drop-missing Drops missing objects. Generates DROP commands for objects that are present in the input model but not in the compared database. -fd, --force-drop-cols Forces the drop of missing columns and constraints. Causes only columns and constraints to be dropped, other missing objects aren't removed. -rn, --rename-db Renames the destination database when the names of the involved databases are different. -nd, --no-cascade Don't drop objects in cascade mode. -ns, --no-sequence-reuse Don't reuse sequences on serial columns. Drop the old sequence assigned to a serial column and creates a new one. -nf, --force-recreate-objs Forces recreating the objects. Instead of an ALTER command, the DROP and CREATE commands are used to create new versions of the objects. -nu, --only-unmodifiable Recreates only the unmodifiable objects. These objects are the ones that can't be changed via the ALTER command. Model fix options: -ft, --fix-tries [NUMBER] Model fix tries. When reaching the maximum count, the invalid objects will be discarded. File association options: -sw, --system-wide The file association to DBM files will be applied on a system-wide level instead of to the current user only. -ff, --force Forces the mime type installation or uninstallation. Config files creation options: -mo, --missing-only Copies only missing configuration files to the user's local storage. -ff, --force Forces the recreation of all configuration files. This option implies the backup of the current settings. Plugins options: -lp, --list-plugins List the available plugins. -ip, --ignore-faulty Ignore the errors of plugins that failed to be loaded. SDBM export plug-in options: -xs, --export-sdbm Runs the export operation over a split database model (.sdbm) SDBM fix plug-in options: -fs, --fix-sdbm Runs the database model fix operation over a split model file (.sdbm) Split model plug-in options: -ls, --load-sdbm The input database model must be loaded as split model file (.sdbm) ** The FILTER value in the --filter-objects option has the form type:pattern:mode. * The section `type' is the type of object to be filtered and accepts the following values (invalid types ignored): > any, schema, table, view, aggregate, cast, collation, constraint > conversion, domain, eventtrigger, extension, foreigndatawrapper, foreignserver, foreigntable, function > index, language, opclass, operator, opfamily, policy, procedure, role > rule, sequence, tablespace, transform, trigger, usermapping * The special type `any' allows writing a single filter that applies to all object types. * The section `pattern' is the text pattern that is matched against the objects' names. * The section `mode' is the way the pattern is matched. This one accepts two values: > `wildcard' causes the pattern to be used as a wildcard string while matching objects' names. > `regexp' causes the pattern to be treated as a Perl-like regular expression while matching objects' names. * The option --force-children takes effect only when used with --only-matching and will avoid discarding children of matched tables. Other tables eventually imported that are dependencies of the matched objects will have their children discarded. The comma-separated list of table children objects accepts the values: > constraint, index, policy, rule, trigger > Use the special keyword `all' to force all children objects. * NOTES: all comparisons during the filtering process are case-insensitive. Using the filtering options may cause the importing of additional objects due to the automatic dependency resolution. ** The diff process allows the usage of all options related to the import operation. It also accepts the following export operation options: `--ignore-duplicates', `--ignore-error-codes' ** The partial diff operation will always force the options --only-matching and --force-children = all for more reliable results. * The options --start-date and --end-date accept the ISO8601 date/time format: yyyy-MM-dd hh:mm:ss ** When running the diff using two databases (--input-db and --compare-to) there's the option to specify two separated connections/aliases. If only one connection is set, then it will be used to import the input database as well as retrieve the database used in the comparison. A second connection can be specified by appending a 1 to any connection configuration parameter listed above. This causes the connection to be associated to --compare-to exclusively.
Parameter | Description |
---|---|
-ef, --export-to-file |
Export the input model to a sql script file. |
-ep, --export-to-png |
Export the input model to a png image. |
-es, --export-to-svg |
Export the input model to an svg file. |
-ec, --export-to-dict |
Export the input model to a data directory in HTML format. |
-ed, --export-to-dbms |
Export the input model directly to a PostgreSQL server. |
-lc, --list-conns |
List available connections in file connections.conf. |
-im, --import-db |
Import a database to an output file. |
-df, --diff |
Compares a model and a database or two databases generating the SQL script to sync the latter in relation to the first. |
-fm, --fix-model |
Try to fix the structure of the input model file in order to make it loadable again. |
-cc, --create-configs |
Create the pgModeler's configuration folder and files in the user's local storage. |
-mt, --dbm-mime-type [ACTION] |
Handles the file association to .dbm files. The ACTION can be install or uninstall . |
-h, --help |
Show this help menu. |
Parameter | Description |
---|---|
-if, --input [FILE] |
Input model file (.dbm). This is mandatory for export and fix operations. |
-id, --input-db [DBNAME] |
Input database name. This is mandatory for import operation. |
-of, --output [FILE] |
Output file. This is mandatory for fixing model or exporting to file, png or svg. |
-v, --pgsql-ver |
Force the PostgreSQL version syntax when generating SQL code. |
-s, --silent |
Silent execution. Only critical messages and errors are shown during the process. |
Parameter | Description |
---|---|
-sp, --split |
The SQL file is generated per object. The files will be named in such a way to reflect the correct creation order of the objects. |
-ds, --dependencies |
Includes the object's dependencies SQL code in the generated file. (Only for split mode). |
-cs, --children |
Includes the object's children SQL code in the generated file. (Only for split mode). |
Parameter | Description |
---|---|
-sg, --show-grid |
Draws the grid in the exported image. |
-sl, --show-delimiters |
Draws the page delimiters in the exported image. |
-pp, --page-by-page |
Each page will be exported in a separate png image. (Only for PNG images) |
-oc, --override-bg-color |
Don't use the original canvas color in the exported image, instead, a white background is used. (Only for PNG images) |
-zf, --zoom [FACTOR] |
Applies a zoom (in percent) before export to a png image. Accepted zoom interval: 5-500 (Only for PNG images) |
Parameter | Description |
---|---|
-sp, --split |
The data dictionaries are generated in separate files inside the selected output directory. |
-ni, --no-index |
Avoids the generation of the index that is used to help navigate through the data dictionary. |
Parameter | Description |
---|---|
-ir, --ignore-duplicates |
Ignores errors related to duplicated objects that eventually exist in the server. |
-ic, --ignore-error-codes [CODES] |
Ignores additional errors by their codes. A comma-separated list of alphanumeric codes should be provided. |
-dd, --drop-database |
Drop the database before executing an export process. |
-do, --drop-objects |
Runs the DROP commands attached to SQL-enabled objects. |
-sm, --simulate |
Simulates an export process by executing all steps but undoing any modification in the end. |
-tn, --use-tmp-names |
Generates temporary names for database, roles, and tablespaces when in simulation mode. |
Parameter | Description |
---|---|
-ca, --conn-alias [ALIAS] |
Connection configuration alias to be used. |
-H, --host [HOST] |
PostgreSQL host in which a task will operate. |
-p, --port [PORT] |
PostgreSQL host listening port. |
-u, --user [USER] |
PostgreSQL username. |
-w, --passwd [PASSWORD] |
PostgreSQL user password. |
-D, --initial-db [DBNAME] |
Connection's initial database. |
Parameter | Description |
---|---|
-ie, --ignore-errors |
Ignore all errors and try to create as many as possible objects. |
-is, --import-sys-objs |
Import system built-in objects. This option causes the model to bloat due to the importing of unneeded objects. |
-ix, --import-ext-objs |
Import extension objects. This option causes the model to bloat due to the importing of unneeded objects. |
-cl, --comments-as-aliases |
Use objects comments as aliases. This option takes effect on objects graphically represented in the database model. |
-fo, --filter-objects [FILTER] |
Causes the import process to import only those objects matching the filter(s). The FILTER should be in the form type:pattern:mode. |
-om, --only-matching |
Causes only objects matching the provided filter(s) to be imported. Those not matching filter(s) are discarded. |
-mn, --match-by-name |
Causes the objects matching to be performed over their names instead of their signature ([schema].[name]). |
-fc, --force-children [OBJECTS] |
Forces the importing of children objects related to tables/views/foreign tables matched by the filter(s). The OBJECTS is a comma-separated list of types. |
-d, --debug-mode |
Run import in debug mode printing all queries executed in the server. |
Parameter | Description |
---|---|
-ct, --compare-to [DBNAME] |
The database used in the comparison. All the SQL code generated is applied to it. |
-pd, --partial |
Toggles the partial diff operation. A set of objects filters should be provided using the import option --filter-objects. |
-ff, --force |
Forces a full diff if the provided filters were not able to retrieve objects for a partial diff operation. |
-st, --start-date |
Matches all database model objects in which modification date starts on the specified date. (Only for partial diff) |
-et, --end-date |
Matches all database model objects in which modification date ends in the specified date. (Only for partial diff) |
-sd, --save |
Save the generated diff code to the output file. |
-ad, --apply |
Apply the generated diff code on the database server. |
-np, --no-preview |
Don't preview the generated diff code when applying it to the server. |
-dc, --drop-cluster-objs |
Drop cluster level objects like roles and tablespaces. |
-rv, --revoke-perms |
Revoke permissions already set on the database. New permissions configured in the input model are still applied. |
-dm, --drop-missing |
Drop missing objects. Generates DROP commands for objects that are present in the input model but not in the compared database. |
-fd, --force-drop-cols |
Force the drop of missing columns and constraints. Causes only columns and constraints to be dropped, other missing objects aren't removed. |
-rn, --rename-db |
Rename the destination database when the names of the involved databases are different. |
-nd, --no-cascade |
Don't drop or truncate objects in cascade mode. |
-ns, --no-sequence-reuse |
Don't reuse sequences on serial columns. Drop the old sequence assigned to a serial column and creates a new one. |
-nf, --force-recreate-objs |
Force the recreating of objects. Instead of an ALTER command a DROP and CREATE commands are used to create a new version of the objects. |
-nu, --only-unmodifiable |
Recreate only the unmodifiable objects. These objects are the ones that can't be changed via ALTER command. |
Parameter | Description |
---|---|
-ft, --fix-tries [NUMBER] |
Model fix tries. When reaching the maximum count the invalid objects will be discarded. |
Parameter | Description |
---|---|
-sw, --system-wide |
The file association to .dbm files will be applied at a system-wide level instead of to the current user. |
-ff, --force |
Forces the mime type install or uninstall. |
Parameter | Description |
---|---|
-mo, --missing-only |
Copy only missing configuration files to the user's local storage. |
-ff, --force |
Forces the recreation of all configuration files. This option implies the backup of the current settings. |
Parameter | Description |
---|---|
-lp, --list-plugins |
List the available plug-ins. |
-ip, --ignore-faulty |
Ignore the errors of plug-ins that failed to be loaded. |
Despite the usefulness of the command line interface there are two important limitations in the tool which need to be described. The first one is that only one operation is supported per execution, in other words, the parameters --export-to-file
, --export-to-png
, --export-to-svg
, --export-to-dbms
, --import-db
, --diff
, --fix-model
and --dbm-mime-type
can't appear in the same command call. The second limitation is that in any operation that produces an output file based upon the input model you are obligated to specify the input and output files and these files should have different names or the process will be aborted.
Below we have some command samples, an important note to be given is that on any operation that involves the connection to a database server, you can either use the parameter --conn-alias
alone or specify the entire connection configuration using the rest of the connection options (see above).
Export a model to the server using a connection alias:
pgmodeler-cli --export-to-dbms --input demo.dbm --conn-alias local-db
Export a model to the server using the connections options:
pgmodeler-cli --export-to-dbms --input demo.dbm --host localhost --user myuser --passwd mypass --initial-db mydb
Export a model to PNG file applying a zoom factor of 200%:
pgmodeler-cli --export-to-png --input demo.dbm --output demo.png --zoom 200
Export a model to SQL script forcing the generation of code to PostgreSQL 9.6:
pgmodeler-cli --export-to-file --input demo.dbm --output demo.sql --pgsql-ver 9.6
Import a database ignoring errors and in debug mode:
pgmodeler-cli --import-db --input-db demodb --output demo_imported.dbm --conn-alias local-db --ignore-errors --debug-mode
Diff a model and a database applying the changes on the server:
pgmodeler-cli --diff --input demo.dbm --compare-to demodb --conn-alias local-db --apply-diff
Diff two databases living on different servers using connection aliases:
pgmodeler-cli --diff --input-db pagila --compare-to demodb --conn-alias local-db --conn-alias1 remote-db --apply-diff
Diff two databases living on different servers using connection parameters and saving the diff to a file:
pgmodeler-cli --diff --input-db pagila --compare-to demodb --output diff.sql -H localhost -u postgres -w postgres -D postgres -H1 localhost -u1 postgres -w1 postgres -D1 postgres --save-diff
Perform a partial diff by using objects modified in the model since June, 1st:
pgmodeler-cli --diff --partial --input my_model.dbm --compare-to my_database --conn-alias local-db --start-date 2020-06-01 --apply
Perform a partial diff between two databases and filter all tables and their children residing in the schema schema_a
:
pgmodeler-cli --diff --partial --input-db my_dev_db --compare-to my_prod_db --conn-alias local-db --apply --filter-objects "table:schema_a.*:wildcard" --force-children all
Apache Ant