Bug 1459652 - [RFE] Provide ability to backup/restore the Ansible Database as Technote
[RFE] Provide ability to backup/restore the Ansible Database as Technote
Product: Red Hat CloudForms Management Engine
Classification: Red Hat
Component: Documentation (Show other bugs)
Unspecified Unspecified
high Severity high
: GA
: 5.8.1
Assigned To: Chris Budzilowicz
Suyog Sainkar
: FutureFeature
: 1419017 1456787 (view as bug list)
Depends On:
  Show dependency treegraph
Reported: 2017-06-07 13:31 EDT by John Hardy
Modified: 2018-02-26 19:47 EST (History)
10 users (show)

See Also:
Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2017-07-14 11:50:08 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)

  None (edit)
Description John Hardy 2017-06-07 13:31:25 EDT
Please provide written details to how to perform a backup and restore of the embedded Ansible database.
Comment 3 Yuri Rudman 2017-06-09 10:56:44 EDT
To back-up embedded Ansible and MIQ Databases:
pg_dumpall -c --lock-wait-timeout=60000 --quote-all-identifiers > miq-dump.sql

To restore:
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.
Comment 4 Nick Carboni 2017-06-09 11:13:31 EDT
I think if we're changing tools it might be better to use something like pg_basebackup [1] this is the tool that is more commonly used to take a low-level backup of a database.

Additionally there are entire products [2][3] 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.

[1] https://www.postgresql.org/docs/9.5/static/app-pgbasebackup.html
[2] https://github.com/pgbackrest/pgbackrest
[3] http://www.pgbarman.org/
Comment 5 Yuri Rudman 2017-06-12 17:19:49 EDT
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
mkdir /var/opt/rh/rh-postgresql95/lib/pgsql/data
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
Comment 7 Andrew Dahms 2017-06-15 01:39:28 EDT
Assigning to Chris for review.
Comment 8 Yuri Rudman 2017-07-05 14:05:11 EDT
*** Bug 1456787 has been marked as a duplicate of this bug. ***
Comment 17 Andrew Dahms 2018-02-26 19:47:48 EST
*** Bug 1419017 has been marked as a duplicate of this bug. ***

Note You need to log in before you can comment on or make changes to this bug.