Red Hat Bugzilla – Bug 1459652
[RFE] Provide ability to backup/restore the Ansible Database as Technote
Last modified: 2018-02-26 19:47:48 EST
Please provide written details to how to perform a backup and restore of the embedded Ansible database.
To back-up embedded Ansible and MIQ Databases:
pg_dumpall -c --lock-wait-timeout=60000 --quote-all-identifiers > miq-dump.sql
psql -f miq-dump.sql postgres
NOTE: user should be PostgreSQL superuser
there should be no connections to DB (SFME should be stopped)
More detailed description on 'pg_dumpall' utility could be found here: https://www.postgresql.org/docs/9.5/static/app-pg-dumpall.html.
I think if we're changing tools it might be better to use something like pg_basebackup  this is the tool that is more commonly used to take a low-level backup of a database.
Additionally there are entire products  which are designed to solve backup and restore for postgres databases. It might be worth experimenting with those so that we can recommend them as an easier way to keep track of backups.
The instructions that follow will perform a full binary backup of the entire DB server. This includes all databases, users and roles, replication slots, etc. This new process is far superior to the existing mechanism in the appliance console because that only backs up the vmdb_production DB. The result of this is a full replacement of the PG data directory on the target DB server.
I think it makes sense to remove the backup/restore option from the appliance console menu once this is published.
Example of binary backup from a remote server and restoring it to the same server using pg_basebackup:
# creates backup from a remote server using root role and store it as zipped tar inside miq-backup directory
pg_basebackup -x -h <ip@ of DB server> -U root -Ft -z -D miq-backup
# copy back-up to target VM
scp miq-backup/base.tar.gz root@<ip@ of DB server>:/var/www/miq
# ssh to target vm and stop EVM and PostgreSQL servers
ssh root@<ip@ of DB server>
systemctl stop evmserverd
systemctl stop $APPLIANCE_PG_SERVICE
# unzip backup tar to clean data directory
mv /var/opt/rh/rh-postgresql95/lib/pgsql/data /var/opt/rh/rh-postgresql95/lib/pgsql/data.backup
tar -xzf /var/www/miq/base.tar.gz -C /var/opt/rh/rh-postgresql95/lib/pgsql/data
# correct permissions
chown postgres:postgres /var/opt/rh/rh-postgresql95/lib/pgsql/data
chmod 700 /var/opt/rh/rh-postgresql95/lib/pgsql/data
# start PostgresSQL and SFME servers
systemctl start $APPLIANCE_PG_SERVICE
systemctl start evmserverd
Assigning to Chris for review.
*** Bug 1456787 has been marked as a duplicate of this bug. ***
*** Bug 1419017 has been marked as a duplicate of this bug. ***