PostgreSQL

How to backup and recover Bindplane when using PostgreSQL

When Bindplane is configured to use PostgreSQL as the storage backend, all data is stored in a database on the PostgreSQL system.

Tooling

The PostgreSQL ecosystem is rich with backup tools. This guide will focus on the simplest approach, pgdump. You can use your favorite backup tooling as an alternative to pgdump.

Prerequisites

• Command line access to the PostgreSQL database

Backup

Using the pg_dump command, export the database named bindplane to a file in the working directory.

pg_dump --file bindplane-$(date '+%Y-%m-%d_%H:%M:%S').pgsql --dbname bindplane

Alternatively, if your setup requires it, you may need to run it using sudo.

sudo -u postgres pg_dump --file bindplane-$(date '+%Y-%m-%d_%H:%M:%S').pgsql --dbname bindplane

NOTE

Depending on how Postgres was installed, you may need to specify the full path to pg_dump

Such as /usr/pgsql-16/bin/pg_dump

Once finished, a file with the date will exist in the working directory. For example, bindplane-2023-08-03_15:16:47.pgsql.

It is recommended that the exported database files be moved to a remote system, such as a backup server or a secure object storage service like Google Cloud Storage or Amazon S3.

Restore

To restore backup of the PostgreSQL database, use the following process:

1. Stop the server: sudo systemctl stop bindplane
2. Ensure the target database exists
3. Use psql to restore the backup: psql -d bindplane < bindplane-2023-08-03_15:16:47.pgsql
4. Start Bindplane: sudo systemctl start bindplane