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..
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.
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 eopng-server.service
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
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.
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 –
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 eopng-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 eopng-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