Tools for managing PostgreSQL databases.
- Vacuum and Vacuum freeze tasks
- WAL archiving and restoration
- pg gem
[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.
Backup all databases in a Postgres instance using
pg_basebackup. This assumes that
there are no additional tablespaces (see the documentation on
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
/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
--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.
The wal archiving scripts packaged in this gem are intended to serve as
wrappers for YOUR archiving mechanism. Changing the settings for WAL
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
archive_command = 'postmodern archive --path %p --filename %f'
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
restore will attempt to call a
see the examples directory for example local scripts.
Attribution & Thanks
Please see the attribution file for proper attribution and thanks.
- 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
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.