pgmoneta

Appearance

Getting started with pgmoneta ​

First of all, make sure that pgmoneta is installed and in your path by using pgmoneta -?. You should see

pgmoneta 0.19.1
  Backup / restore solution for PostgreSQL

Usage:
  pgmoneta [ -c CONFIG_FILE ] [ -u USERS_FILE ] [ -d ]

Options:
  -c, --config CONFIG_FILE Set the path to the pgmoneta.conf file
  -u, --users USERS_FILE   Set the path to the pgmoneta_users.conf file
  -A, --admins ADMINS_FILE Set the path to the pgmoneta_admins.conf file
  -d, --daemon             Run as a daemon
      --offline            Run in offline mode
  -V, --version            Display version information
  -?, --help               Display help
pgmoneta 0.19.1
  Backup / restore solution for PostgreSQL

Usage:
  pgmoneta [ -c CONFIG_FILE ] [ -u USERS_FILE ] [ -d ]

Options:
  -c, --config CONFIG_FILE Set the path to the pgmoneta.conf file
  -u, --users USERS_FILE   Set the path to the pgmoneta_users.conf file
  -A, --admins ADMINS_FILE Set the path to the pgmoneta_admins.conf file
  -d, --daemon             Run as a daemon
      --offline            Run in offline mode
  -V, --version            Display version information
  -?, --help               Display help

If you don't have pgmoneta in your path see Install pgmoneta on how to compile and install pgmoneta in your system.

Configuration ​

Lets create a simple configuration file called pgmoneta.conf with the content

[pgmoneta]
host = *
metrics = 5001

base_dir = /home/pgmoneta

compression = zstd

storage_engine = local

retention = 7

log_type = file
log_level = info
log_path = /tmp/pgmoneta.log

unix_socket_dir = /tmp/

[primary]
host = localhost
port = 5432
user = repl
wal_slot = repl
[pgmoneta]
host = *
metrics = 5001

base_dir = /home/pgmoneta

compression = zstd

storage_engine = local

retention = 7

log_type = file
log_level = info
log_path = /tmp/pgmoneta.log

unix_socket_dir = /tmp/

[primary]
host = localhost
port = 5432
user = repl
wal_slot = repl

In our main section called [pgmoneta] we setup pgmoneta to listen on all network addresses. We will enable Prometheus metrics on port 5001 and have the backups live in the /home/pgmoneta directory. All backups are being compressed with zstd and kept for 7 days. Logging will be performed at info level and put in a file called /tmp/pgmoneta.log. Last we specify the location of the unix_socket_dir used for management operations and the path for the PostgreSQL command line tools.

Next we create a section called [primary] which has the information about our PostgreSQL instance. In this case it is running on localhost on port 5432 and we will use the repl user account to connect, and the Write+Ahead slot will be named repl as well.

The repl user must have the REPLICATION role and have access to the postgres database, so for example

CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD 'secretpassword';
CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD 'secretpassword';

and in pg_hba.conf

local   postgres        repl                                   scram-sha-256
host    postgres        repl           127.0.0.1/32            scram-sha-256
host    postgres        repl           ::1/128                 scram-sha-256
host    replication     repl           127.0.0.1/32            scram-sha-256
host    replication     repl           ::1/128                 scram-sha-256
local   postgres        repl                                   scram-sha-256
host    postgres        repl           127.0.0.1/32            scram-sha-256
host    postgres        repl           ::1/128                 scram-sha-256
host    replication     repl           127.0.0.1/32            scram-sha-256
host    replication     repl           ::1/128                 scram-sha-256

The authentication type should be based on postgresql.conf's password_encryption value.

Then, create a physical replication slot that will be used for Write-Ahead Log streaming, like

SELECT pg_create_physical_replication_slot('repl', true, false);
SELECT pg_create_physical_replication_slot('repl', true, false);

Alternatively, configure automatically slot creation by adding create_slot = yes to [pgmoneta] or corresponding server section

We will need a user vault for the repl account, so the following commands will add a master key, and the repl password

pgmoneta-admin master-key
pgmoneta-admin -f pgmoneta_users.conf user add
pgmoneta-admin master-key
pgmoneta-admin -f pgmoneta_users.conf user add

We are now ready to run pgmoneta.

See Configuration for all configuration options.

Running ​

We will run pgmoneta using the command

pgmoneta -c pgmoneta.conf -u pgmoneta_users.conf
pgmoneta -c pgmoneta.conf -u pgmoneta_users.conf

If this doesn't give an error, then we are ready to do backups.

pgmoneta is stopped by pressing Ctrl-C (^C) in the console where you started it, or by sending the SIGTERM signal to the process using kill <pid>.

Run-time administration ​

pgmoneta has a run-time administration tool called pgmoneta-cli.

You can see the commands it supports by using pgmoneta-cli -? which will give

pgmoneta-cli 0.19.1
  Command line utility for pgmoneta

Usage:
  pgmoneta-cli [ -c CONFIG_FILE ] [ COMMAND ]

Options:
  -c, --config CONFIG_FILE                        Set the path to the pgmoneta.conf file
  -h, --host HOST                                 Set the host name
  -p, --port PORT                                 Set the port number
  -U, --user USERNAME                             Set the user name
  -P, --password PASSWORD                         Set the password
  -L, --logfile FILE                              Set the log file
  -v, --verbose                                   Output text string of result
  -V, --version                                   Display version information
  -F, --format text|json|raw                      Set the output format
  -C, --compress none|gz|zstd|lz4|bz2             Compress the wire protocol
  -E, --encrypt none|aes|aes256|aes192|aes128     Encrypt the wire protocol
  -s, --sort asc|desc                             Sort result (for list-backup)
      --cascade                                   Cascade a retain/expunge backup
  -?, --help                                      Display help

Commands:
  backup                   Backup a server
  list-backup              List the backups for a server
  restore                  Restore a backup from a server
  verify                   Verify a backup from a server
  archive                  Archive a backup from a server
  delete                   Delete a backup from a server
  retain                   Retain a backup from a server
  expunge                  Expunge a backup from a server
  encrypt                  Encrypt a file using master-key
  decrypt                  Decrypt a file using master-key
  compress                 Compress a file from a server
  decompress               Decompress a file from a server
  annotate                 Annotate a backup with comments
  ping                     Check if pgmoneta is alive
  shutdown                 Shutdown pgmoneta
  status [details]         Status of pgmoneta, with optional details
  conf <action>            Manage the configuration, with one of subcommands:
                           - 'reload' to reload the configuration
                           - 'ls' to print the configurations used
                           - 'get' to obtain information about a runtime configuration value
                           - 'set' to modify a runtime configuration value
  clear <what>             Clear data, with:
                           - 'prometheus' to reset the Prometheus statistics
pgmoneta-cli 0.19.1
  Command line utility for pgmoneta

Usage:
  pgmoneta-cli [ -c CONFIG_FILE ] [ COMMAND ]

Options:
  -c, --config CONFIG_FILE                        Set the path to the pgmoneta.conf file
  -h, --host HOST                                 Set the host name
  -p, --port PORT                                 Set the port number
  -U, --user USERNAME                             Set the user name
  -P, --password PASSWORD                         Set the password
  -L, --logfile FILE                              Set the log file
  -v, --verbose                                   Output text string of result
  -V, --version                                   Display version information
  -F, --format text|json|raw                      Set the output format
  -C, --compress none|gz|zstd|lz4|bz2             Compress the wire protocol
  -E, --encrypt none|aes|aes256|aes192|aes128     Encrypt the wire protocol
  -s, --sort asc|desc                             Sort result (for list-backup)
      --cascade                                   Cascade a retain/expunge backup
  -?, --help                                      Display help

Commands:
  backup                   Backup a server
  list-backup              List the backups for a server
  restore                  Restore a backup from a server
  verify                   Verify a backup from a server
  archive                  Archive a backup from a server
  delete                   Delete a backup from a server
  retain                   Retain a backup from a server
  expunge                  Expunge a backup from a server
  encrypt                  Encrypt a file using master-key
  decrypt                  Decrypt a file using master-key
  compress                 Compress a file from a server
  decompress               Decompress a file from a server
  annotate                 Annotate a backup with comments
  ping                     Check if pgmoneta is alive
  shutdown                 Shutdown pgmoneta
  status [details]         Status of pgmoneta, with optional details
  conf <action>            Manage the configuration, with one of subcommands:
                           - 'reload' to reload the configuration
                           - 'ls' to print the configurations used
                           - 'get' to obtain information about a runtime configuration value
                           - 'set' to modify a runtime configuration value
  clear <what>             Clear data, with:
                           - 'prometheus' to reset the Prometheus statistics

This tool can be used on the machine running pgmoneta to do a backup like

pgmoneta-cli -c pgmoneta.conf backup primary
pgmoneta-cli -c pgmoneta.conf backup primary

A restore would be

pgmoneta-cli -c pgmoneta.conf restore primary <timestamp> /path/to/restore
pgmoneta-cli -c pgmoneta.conf restore primary <timestamp> /path/to/restore

To stop pgmoneta you would use

pgmoneta-cli -c pgmoneta.conf stop
pgmoneta-cli -c pgmoneta.conf stop

Check the outcome of the operations by verifying the exit code, like

echo $?
echo $?

or by using the -v flag.

If pgmoneta has both Transport Layer Security (TLS) and management enabled then pgmoneta-cli can connect with TLS using the files ~/.pgmoneta/pgmoneta.key (must be 0600 permission), ~/.pgmoneta/pgmoneta.crt and ~/.pgmoneta/root.crt.

Administration ​

pgmoneta has an administration tool called pgmoneta-admin, which is used to control user registration with pgmoneta.

You can see the commands it supports by using pgmoneta-admin -? which will give

pgmoneta-admin 0.19.1
  Administration utility for pgmoneta

Usage:
  pgmoneta-admin [ -f FILE ] [ COMMAND ]

Options:
  -f, --file FILE         Set the path to a user file
  -U, --user USER         Set the user name
  -P, --password PASSWORD Set the password for the user
  -g, --generate          Generate a password
  -l, --length            Password length
  -V, --version           Display version information
  -?, --help              Display help

Commands:
  master-key              Create or update the master key
  user <subcommand>       Manage a specific user, where <subcommand> can be
                          - add  to add a new user
                          - del  to remove an existing user
                          - edit to change the password for an existing user
                          - ls   to list all available users
pgmoneta-admin 0.19.1
  Administration utility for pgmoneta

Usage:
  pgmoneta-admin [ -f FILE ] [ COMMAND ]

Options:
  -f, --file FILE         Set the path to a user file
  -U, --user USER         Set the user name
  -P, --password PASSWORD Set the password for the user
  -g, --generate          Generate a password
  -l, --length            Password length
  -V, --version           Display version information
  -?, --help              Display help

Commands:
  master-key              Create or update the master key
  user <subcommand>       Manage a specific user, where <subcommand> can be
                          - add  to add a new user
                          - del  to remove an existing user
                          - edit to change the password for an existing user
                          - ls   to list all available users

In order to set the master key for all users you can use

pgmoneta-admin -g master-key
pgmoneta-admin -g master-key

The master key must be at least 8 characters.

Then use the other commands to add, update, remove or list the current user names, f.ex.

pgmoneta-admin -f pgmoneta_users.conf user add
pgmoneta-admin -f pgmoneta_users.conf user add

Next Steps ​

Next steps in improving pgmoneta's configuration could be

  • Update pgmoneta.conf with the required settings for your system
  • Enable Transport Layer Security v1.2+ (TLS) for administrator access

See Configuration for more information on these subjects.

Tutorials ​

There are a few short tutorials available to help you better understand and configure pgmoneta:

Closing ​

The pgmoneta community hopes that you find the project interesting.

Feel free to

All contributions are most welcome !

Please, consult our Code of Conduct policies for interacting in our community.

Consider giving the project a star on GitHub if you find it useful. And, feel free to follow the project on Twitter as well.