Migrate SAFEQ Cloud bundled PostgreSQL database to external PostgreSQL database

This guide can also be useful when you are migrating SAFEQ Cloud from single primary server to high-availability cluster of primary servers which can be reliably utilized with minimum downtime duration and to avoid single point of failure..

Apart from database migration, if you also require to migrate from SAFEQ Cloud single server to cluster server setup, please go through the article on how-to – Configure a primary server cluster

All steps from the section Migrate database from existing SAFEQ Cloud server to external database onward need to be performed in one go. It is recommended that you allocate a migration window during non-business hours to perform these tasks. For migration of the database, the recommendation is to have a standard 4 hours migration window.

Prerequisites/requirements for migration:

Before getting started, ensure that the below prerequisites are met -

PostgreSQL database configured on the database server which will be used as an external database server. The following database details are needed for SAFEQ Cloud server:
- Database host name or IP address
- Database port,
- Database username
- Database password
Make sure that all necessary ports are open for communication between SAFEQ Cloud and external database. Refer to Ports and Protocols section.
Take full snapshot backup of existing SAFEQ Cloud server before proceeding with migration or take a complete backup of /opt/safeqcloud
Have a Rollback plan in place in-case any issue comes up during migration.

Migrate database from existing SAFEQ Cloud server to external database

In this step we will describe how to migrate bundled PostgreSQL database from existing SAFEQ Cloud server to an external PostgreSQL database server.

Before starting this step, ensure that communication to existing SAFEQ Cloud is stopped in order to avoid any data loss.
One way to do this is to SSH into SAFEQ Cloud server and stop SAFEQ Cloud service by typing in following command – sudo systemctl stop hcp-server.service

Contact Y Soft (support@ysoft.com) to get default db password if you do not know already

SSH into SAFEQ Cloud server and follow below steps to perform database migration -

1. Take backup of “eopng_config” database from the SAFEQ Cloud server using “pg_dump” utility. Run this command from terminal -
PGPASSWORD="<db_password>" /opt/safeqcloud/pgsql/bin/pg_dump -f /tmp/eopng_config_backup.dump -v -F t -b -c -C -h localhost -U postgres -p 7295 eopng_config
This will create backup file eopng_config_backup.dump in /tmp folder.

2. Take backup of “eopng_documents” database from the SAFEQ Cloud server. Run this command from terminal -
PGPASSWORD="<db_password>" /opt/safeqcloud/pgsql/bin/pg_dump -f /tmp/eopng_documents_backup.dump -v -F t -b -c -C -h localhost -U postgres -p 7295 eopng_documents
This will create backup file eopng_documents_backup.dump in /tmp folder.

3. Restore “eopng_config” database on external database server using “pg_restore” utility. Run this command from terminal –
PGPASSWORD="external_db_password" /opt/safeqcloud/pgsql/bin/pg_restore -C -v /tmp/eopng_config_backup.dump -h <external database server IP> -U postgres -p 5432 -d postgres
This will create database eopng_config on external PostgreSQL database server and migrate all data to it from dump file eopng_config_backup.dump.
**Note – you can use -n public switch in restore command if you want to restore only public schema (where SAFEQ Cloud data is)

4. Restore “eopng_documents” database on external database server using “pg_restore” utility. Run this command from terminal –
PGPASSWORD="external_db_password" /opt/safeqcloud/pgsql/bin/pg_restore -C -v /tmp/eopng_documents_backup.dump -h <external database server IP> -U postgres -p 5432 -d postgres
This will create database eopng_documents on external PostgreSQL database server and migrate all data to it from dump file eopng_documents_backup.dump.
**Note – you can use -n public switch in restore command if you want to restore only public schema (where SAFEQ Cloud data is)

5. Verify both databases have been migrated successfully.
Note – You can connect to external DB tools like DB Visualizer or Tableplus to verify contents of database

Change SAFEQ Cloud configuration to point to migrated external database

Before starting this step, ensure that full backup of SAFEQ Cloud folders /opt/safeqcloud/conf and var/lib/eopng has been taken.

1. In `applications.conf` file at location /opt/safeqcloud/conf change following JDBC settings for database schema config and documents to use migrated external database. Also update username and password as applicable.
You can specify password in plain text (for example: password = “mypass”). It will be re-encrypted after SAFEQ Cloud server restart.

CODE

url="jdbc:postgresql://<external_db_address>:5432/eopng_config"
url="jdbc:postgresql://<external_db_address>:5432/eopng_documents"

Below shown is example of database schema when connecting to external database –

CODE

config {
               password="<db_user_password>"
                poolsize=10
                timeout=30000
                url="jdbc:postgresql://<external_db_address>:5432/eopng_config"
                username=<db_user>
            }
documents {
                password="<db_user_password>"
                poolsize=10
                timeout=30000
                url="jdbc:postgresql://<external_db_address>:5432/eopng_documents"
                username=<db_user>
            }

2. Restart SAFEQ Cloud service. Type in following command – sudo systemctl restart hcp-server.service

3. Login to SAFEQ Cloud web UI and perform sanity checks to verify systems and health-checks are working as expected. Once you are satisfied with migration

Verification

After migration, perform sanity checks to verify systems and health-checks are working as expected. Recommendation is to -
1. Perform CRUD (create, read, update and delete) operations from SAFEQ Cloud Web UI to verify operations with external database is working as expected
2. Verify as applicable that the connections from SAFEQ Cloud gateways, SAFEQ Cloud Clients, embedded clients, Y Soft Mobile App are operational after migration

Rollback

If because of any reason the migration is not successful or if you encounter issues which needs troubleshoot time beyond migration window, rollback to use existing SAFEQ Cloud.

p(banner tip).If you have taken the snapshot backup of SAFEQ Cloud server before starting the migration procedures ,then please revert it so that you dont have to follow the below steps

1. Restore “application.conf” file from the backup taken before performing tasks in section 3
2. Restart SAFEQ Cloud service. Use command sudo systemctl restart hcp-server.service
3. Verify SAFEQ Cloud works as expected
When root cause analysis has been done for the issue encountered during migration and sufficient mitigation steps have been taken, try again from section 3