rafaelma / pgbackman

PostgreSQL backup manager

PgBackMan

PostgreSQL backup manager

NOTE: This project is no longer maintained due to lack of free time to do so. The source code is still available and it was supported up to PostgreSQL 9.6.

PgBackMan is a tool for managing PostgreSQL logical backups created with pg_dump and pg_dumpall.

It is designed to manage backups from thousands of databases running in multiple PostgreSQL nodes, and it supports a multiple backup server topology.

PgBackMan is not a tool for managing PITR (Point in time recovery) backups. There are several other solutions that can be use for managing PITR backups, such as PITRTools, OmniPITR, and Barman.

The PgBackMan code is distributed under the GNU General Public License and it is written in Python and PL/PgSQL. It has been developed and tested by members of the Database Operations Group at the Center for Information Technology at the University of Oslo.

pgbackman's Issues

Create a pgbackman_check program

This program will check if all the elements, configuration files and access needed by pgbackman are in place.

This will help to debug error situations with pgbackman

Disk space reports

Generate reports of disk use to help with future planning.

Add exceptions field to register_backup_definitions

We need when defining backups for multiple databases ('#all_databases#' / '#databases_without_backup_definitions#') the possibility of excluding some databases from this operation.

An Exception field with a list of databases in a comma separated format should be enough to implement this.

More checks when upgrading the pgbackman database.

Implement some checks to find out if pgbackman_control, pgbackman_maintenance, pgbackman_alerts, pgbackman_dump, pgbackman_restore, crond or atd are running when an upgrade of the pgbackman database via the pgbackman shell is going to be executed.

Stop the database upgrade if they are running.

Create a Zabbix template for monitoring

Create a Zabbix template to monitor pgbackman.

• Check jobs queue with notifications waiting to be processed by pgbackman_control
• Autodiscovery all active backup definitions defined in a backup server
• Check status of last entry in the catalog for a backup definition
• Check if the a backup has been executed according to the backup definiition schedule

Add new command: show_databases_without_backup

Check for databases without backup definitions

Show restore jobs in progress

Add a new command to show restore jobs in progress

show_restores_in_progress

Define default backup server on inputs

Use the FQDN (or backup_server value if defined) of the backup server running the pgbackman shell as the default backup server value in all backup servers inputs

Automatic cloning between PgSQL nodes

Cloning of databases between PgSQL nodes. The target database will get deleted if it exists.

Refactor the code used to manage command line parameters

use argparse to manage parameters defined when running pgbackman

Any plans for Debian packages of new releases?

Only version 1.0 has a Debian package, but it would be great for newer versions, too.

Define multiple snapshots definitions in one command

We need to be able to define multiple snapshots with one register_snapshot_definition.
DBname should accept */all or a list with databases to get the snapshot defined

Add PgSQL node and database information to the log file

Add the possibility of adding the PgSQL node and database information to the log when running pgbackman_dump and pgbackman_restore. This will make it easier to identify the log line.

Backup definitions of type CLUSTER getting DELETE status

Backup definitions of type CLUSTER get automatically the status DELETED when pgbackman_maintenance processes the deletion of backup definitions for databases that have been deleted in a PgSQL node.

This happens because backup definitions of type CLUSTER get an empty dbname value and the algorithm used to find the bck_def to delete does not take this into account.

Problems restoring backups with several almost identical roles

This bug is a corner case that gets activated when restoring a database with several roles and one of these roles names can be found in other roles names. e.g. the role name "database_user" is included in the role "database_user_ro".

The problem is located in the function restore_roles(db) in pgbackman_restore and how it generates the roles to be created or alter during the restore job.

Refactor the code that generates output.

• We have to move out of database.py all code used to generate output data. cli.py will have the responsibility og generating CSV, JSON an formated-table output with information returned by database.py
• database.py have to be used only as an interface between cli and the database.

Automatic upgrade of the pgbackman database

Implement a check when starting pgbackman that refuse to start if the pgbackman database version is not synchronized with the pgbackman version installed in the pgbackman backup server.

If the database is no synchronized, ask if it should get upgraded. If the answer is yes, install all upgrades between database version and pgbackman version.

Write a doc section talking about the upgrade procedure.

Information about databases without a backup definition

Create a report that give us a list of databases from the PgSQL nodes registered in the system without a backup definition in PgBackMan.

Command: show_databases_without_backup_definitions

Update show_backup_catalog

Add search by status

Update show_pgbackman_config with more information

show more parameters with this command:

• pause_recovery_process_on_slave
• smtp_alerts
• alerts_check_interval
• smtp_server
• smtp_port
• smtp_ssl
• smtp_user
• smtp_from_address
• alerts_template

Add support for postgreSQL 9.6 on the PgSQL nodes

Add support for postgreSQL 9.6 on the PgSQL nodes

Implement the possibility of using colors when showing status information

red: ERROR
green: SUCCEEDED
green: DEFINED
yellow: WAITING
red: STOPPED
green: RUNNING

The use of colors will be controlled by a parameter in the config file.

Pausing / resuming recovery process on PgSQL nodes running as slaves.

By default postgreSQL will kill any backup running against a slave node if the recovery process is running and the backup is taking too long to finish because the database is large.

This behavior can be controlled with max_standby_archive_delay and max_standby_streaming_delay but to be sure we can finish running our backups we need to pause the recovery process when running the backup and resume it when we are done if the PgSQL node is a slave/standby node

CSV output format

Define the use of CSV output format instead of prettytable output.

This can be very handy when running the pgbackman shell in a non-interactive mode from the command line for further processing/parsing of the output

Define new commandline parameters

-c parameter to use pgbackman commands from the command line
-o output type [cvs,json]

Support postgreSQL 9.5

Add support for postgreSQL 9.5 running on the PgSQL nodes.

Update the documentation to version 1.2.0

More parameters when deleting a backup definition for a database

Use more search parameters when deleting a backup definition for a database.

We use PgSQL node + database today to find the definitions to delete. We can use also the "Backup code" attribute to delete only the registrations for a database in a node with a particular backup code.

Problems processing restore pending files

When a restore pending file is generated because pgbackman_restore cannot update the pgbackman metadatabase, the format of the file generated is not right.

At the same time pgbackman_maintenance has problems reading this file and updating the pgbackman database.

The problems are located here
pgbackman_restore: def register_restore_catalog()
pgbackman_maintenance:process_pending_restore_catalog_log_file()

Verification of backups

Check if the result of a restore is identical to the source database

Update the show_empty_backup_catalogs command

Add search by time period

Update pgbackman when a database is deleted

Stop all backup definitions for databases that have been deleted in the a pgSQL node.

This will be done by pgbackman_maintenance and all backup definitions for the deleted database will get the status DELETED for a period of time. After this period is over, all definitions and backups will be deleted regardless of the retention policies of the definition.

The parameter automatic_deletion_retention will define the period of time backups and definitions are kept in PgBackMan.

Change the original owner of the database when running a restore

We can change the name of the target database but not of the target owner, fix this.

We can use --role=ROLENAME and -O, --no-owner in the extra parameters field to define a new owner of the objects in the database. But the CREATE DATABASE command uses the original owner of the database, we have to change this if the user uses a --role / -O under the restoration of a backup.

Add more information to *_stats commands

Show information about snapshots and restores

Show snapshots in progress

Add a new command to show all snapshots jobs running and in progress

show_snapshots_in_progress

Missing GRANT ... TO ... GRANTED BY .... statements in the roles backup file

We do not get the sql statements needed if an user (owning or having privileges in the database we are backing up) got some grants from other roles. This information is only in the output from pg_dumpall and not from pg_dump.

Write documentation with information about monitoring pgbackman via Zabbix

Update the documentation with information about how to monitor pgbackman with zabbix and the use of pgbackman_zabbix_autodiscovery and pgbackman_status_info

Add exception field to register_snapshot_definition

We need when defining backups for multiple databases ('#all_databases#' / '#databases_without_backup_definitions#') the possibility of excluding some databases from this operation.

An Exception field with a list of databases in a comma separated format should be enough to implement this.

Change the default pg_dump / pg_restore format to directory

We need to use this format if we want to run pg_dump in parallel

Python3 support

Hi,

Have you plan to port pgbackman to python 3 ?

configuration instance has no attribute 'channels_check_interval'

When trying to run show_backman_config you get this error

File "/usr/lib/python2.6/site-packages/pgbackman/cli.py", line 2728, in do_show_pgbackman_config
x.add_row(['LISTEN/NOTIFY channel check interval:', str(self.conf.channels_check_interval) + ' sec.'])
AttributeError: configuration instance has no attribute 'channels_check_interval'

CSV and JSON output

Define the possibility of generating CSV and JSON output when running show_* pgbackman commands.

• In non-interactive modus one will have to use -o csv | json to activate CSV or JSON output.
• In interactive modus one will have to use the command set_output_format to define this.

Create a pgbackman_bulk program

This program will be used to run a bulk execution of pgbackman commands from a file.

This will help the user to script bulk updates and run many commands in an easy way

show_job_queues view does not show the right domain for pgsql_nodes

show_job_queues view does not show the right domain for pgsql_nodes.

Save command history between sessions

Save and load command history between interactive pgbackman sessions.
Save history in ~/.pgbackman/pgbackman_history

We need also to new parameters to activate/deactivate this feature and define the max number of lines saved.

Move backup definitions between backup servers.

Move backup definitions between backup servers by DefID, PgSQL node ID or DBname.

Create a ansible playbook to initialize pgbackman

This playbook will be used to initialize many of the things that are needed to start using pgbackman and to generate the pgpass and pg_hba.conf files needed by pgbackman.

We have to automate as much as possible so the user does not have to do this first configuration manually.

Add checksum from backup files to the catalog information

Generate a checksum for the backup files and saved in the database.
The problem with this functionality is that it can take time and resources to generate the checksum.

Problems when deleting a backup definition by dbname

The command delete_backup_definition_dbname will fail if we have a snapshot definition for the same pgsql_node:dbname with data in the backup catalog (table:backup_catalog).

The error message will be:

 ----------------------------------------------
 EXCEPTION:
 ----------------------------------------------
 MESSAGE: null value in column "def_id" violates not-null constraint 
 .........

The deletion of a backup definition should not delete snapshots data from the backup catalog.

Compression of cluster type backups

First of all, thanks for pgbackman. It looks very promising.

It would be great, if it's possible to compress cluster type backups (e.g piping them to gzip).

Or am I missing something and this is already possible?