Postmodern

Tools for managing PostgreSQL databases.

Vacuum and Vacuum freeze tasks
WAL archiving and restoration

Dependencies

libpq
pg gem

Installation

[sudo] gem install postmodern

As a system utility, this assumes that you are installing the gem into the system's ruby, however that is installed.

Usage

Backup

Backup all databases in a Postgres instance using pg_basebackup. This assumes that there are no additional tablespaces (see the documentation on pg_basebackup).

Usage: postmodern backup <options>

Creates a gzipped archive of a pg_basebackup, with file name:
  NAME.basebackup.CURRENTDATE.tar.gz

    -U, --user USER                  Postgres user (default: "postgres")
    -d, --directory DIRECTORY        Local directory to put backups (required)
    -H, --host HOST                  Host of database (eg: fqdn, IP) (required)
    -p, --port PORT                  Port of database (default: 5432)
    -n, --name NAME                  Name of backup (required)
        --pigz CONCURRENCY           Use pigz with concurrency CONCURRENCY
    -h, --help                       Show this message
        --version                    Show version

CURRENTDATE will be in the format YYYYMMDD, and will be the contents of the Postgres data directory of the backed-up instance. When restoring from these backups, note that it should be untarred directly into a new data directory. For instance, if Postgres on the restored hosts is configured with a data directory of /var/pgsql/data94, then the archive should be untarred within /var/pgsql/data94, not in the parent directory.

Vacuuming and Vacuum Freezing

Postmodern's vacuum scripts run table by table, with various constraints to limit the overhead of the process.

Usage: postmodern (vacuum|freeze) <options>
    -U, --user USER                  Defaults to postgres
    -p, --port PORT                  Defaults to 5432
    -H, --host HOST                  Defaults to 127.0.0.1
    -W, --password PASS

    -t, --timeout TIMEOUT            Halt after timeout minutes -- default 120
    -P, --pause PAUSE                Pause (minutes) after each table vacuum -- default 10
    -d, --database DB                Database to vacuum. Required.

    -r, --ratio RATIO                minimum dead tuple ratio to vacuum -- default 0.05
    -B, --tablesize BYTES            minimum table size to vacuum -- default 1000000
    -F, --freezeage AGE              minimum freeze age -- default 10000000
    -D, --costdelay DELAY            vacuum_cost_delay setting in ms -- default 20
    -L, --costlimit LIMIT            vacuum_cost_limit setting -- default 2000

    -h, --help                       Show this message
    -n, --dry-run                    Perform dry-run, do not vacuum.
        --version                    Show version

To run a vacuum:

postmodern vacuum -U postgres -p 5432 -d my_database

In order to run vacuum freeze:

postmodern freeze -U postgres -p 5432 -d my_database

These tasks are designed to be run regularly during a window of lower database activity. They vacuum or vacuum freeze each table that requires it (based on command line options). Before each operation, the scripts check to make sure they have not gone longer than --timeout seconds.

--pause is useful to allow I/O-bound replicas to catch up on replication before starting new vacuum events. When vacuuming large tables with many dead tuples, a lot of changes need to be sent to replicas. When using replicas with spinning disks, this can saturate the I/O of the disk array.

WAL archives

The wal archiving scripts packaged in this gem are intended to serve as wrappers for YOUR archiving mechanism. Changing the settings for WAL archiving in postgresql.conf or in recovery.conf require full restarts of PostgreSQL—using Postmodern, you can configure PostgreSQL once and swap in local scripts to do the actual work.

Usage: postmodern (archive|restore) <options>
    -f, --filename FILE              File name of xlog
    -p, --path PATH                  Path of xlog file
    -h, --help                       Show this message
        --version                    Show version

In postgresql.conf

archive_command = 'postmodern archive --path %p --filename %f'

In recovery.conf

restore_command = 'postmodern restore --path %p --filename %f'

By default these scripts will do nothing. With the presence of local scripts available in the path, the following variables will be exported to the environment and the local scripts called (with arguments preserved):

ENV['WAL_ARCHIVE_PATH'] = path
ENV['WAL_ARCHIVE_FILE'] = filename

Local scripts can be written in any language. They should be able access the relevant arguments either as $1, $2 or using the variables listed above.

archive will attempt to call a postmodern_archive.local script. restore will attempt to call a postmodern_restore.local script.

see the examples directory for example local scripts.

Attribution & Thanks

Please see the attribution file for proper attribution and thanks.

Contributing

Fork it ( http://github.com//postmodern/fork )
Create your feature branch (git checkout -b my-new-feature)
Commit your changes (git commit -am 'Add some feature')
Push to the branch (git push origin my-new-feature)
Create new Pull Request

Contributions will not be accepted without tests. What should be a feature and what a unit test is highly open to interpretation, however. In some cases, a unit test may be easier and acceptable. In general, at least one feature should be written for each new subcommand, even if it just runs --help.

If in doubt, open an issue. If you don't receive a response to an issue or a pull request, please mention one of the core committers of this gem in a comment to make sure it doesn't get swallowed in an email abyss.

wanelo/postmodern