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 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.
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/
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: BACKUP: # 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 RESTORE: # 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
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. ***