dbaugment - Augment a database

Name

dbaugment – Augment a PostgreSQL database in predefined ways

Synopsys

dbaugment [option...] dbname [spec]

Description

dbaugment is a utility for augmenting a PostgreSQL database with various standard attributes and procedures, such as automatically maintained audit columns. The augmentations are defined in a YAML-formatted spec file.

The specification file format is as follows:

augmenter:
  columns:
    modified_date:
      not_null: true
      type: date
schema public:
  table t1:
    audit_columns: default
  table t3:
    audit_columns: modified_only

The specification file lists each schema, and within it, each table to be augmented. Under each table the following values are recognized:

  • audit_columns: This indicates that audit trail columns are to be added to the table, e.g., a timestamp column recording when a row was last modified.

The first section of the specification file, under the augmenter header, lists configuration information. This is in addition to the built-in configuration objects (see Predefined Database Augmentations).

dbaugment first reads the database catalogs. It also initializes itself from predefined configuration information. dbaugment then reads the specification file, which may include additional configuration objects, and outputs a YAML file, including the existing catalog information together with the desired enhancements. The YAML file is suitable for input to yamltodb to generate the SQL statements to implement the changes.

Options

dbaugment accepts the following command-line arguments (in addition to the Common Command Line Options):

dbname

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

spec

Location of the file with the augmenter specifications. If this is omitted, the specification is read from the program’s standard input.

Examples

To augment a database called moviesdb according to the specifications in the file moviesbl.yaml:

dbaugment moviesdb moviesbl.yaml

To add a column named updated to table public.film to hold the date and time each row was inserted or updated, create a YAML specification file, say film.yaml as follows:

augmenter:
  columns:
    modified_timestamp:
      name: updated
schema public:
  table film:
    audit_columns: modified_only

Then run the following command to generate the resulting database specification, alter the table and create the needed trigger and function.

dbaugment moviesdb film.yaml | yamltodb moviesdb -u

Table Of Contents

Previous topic

Predefined Database Augmentations

Next topic

dbtoyaml - Database to YAML

This Page