Migrating PAM360 Database from PostgreSQL to Amazon Aurora (PostgreSQL Compatible) RDS

PAM360 uses PostgreSQL as the default backend database, and it comes bundled with the product. However, you can migrate from the on-premise database into an on-demand service using Amazon RDS. With this, you can migrate all your data from the local PostgreSQL database to the Amazon RDS PostgreSQL database with ease.

Amazon Web Services (AWS) Relational Database Service (RDS) is a managed service that makes it easy to set up, operate, and scale a relational database in the cloud. It supports several database engines, including Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and Microsoft SQL Server.

Benefits of using Amazon RDS Instance

  • Simplifies the database administration tasks such as automated backups, patching, and scaling.
  • Ensure minimal downtime and automatic failover, along with dynamic scaling to compute storage resources to meet demand.
  • Optimized storage options and read replicas enhance performance.
  • Automated backups, snapshots, and multi-region deployments ensure data durability and availability.

Here, in this document, you will learn about migrating from PostgreSQL database to Amazon Aurora RDS (PostgreSQL Compatible)

1. Creating an Amazon Aurora (PostgreSQL Compatible) RDS Instance

Refer to the RDS instance creation section in this document to know more about creating an Amazon Aurora (PostgreSQL compatible) RDS instance in AWS.

2. Migrating PAM360 Database from PostgreSQL to Amazon Aurora (PostgreSQL Compatible) RDS

The following are the high-level steps to migrate data from bundled PostgreSQL to Amazon Aurora (PostgreSQL Compatible) RDS. Perform them on the respective with the required administrator/superuser privileges.

  1. Open the command prompt and navigate to the <PAM360 Installation Directory>\pgsql\bin folder.
  2. Create a dump file using the command - pg_dump dbname > pam360.sql
    For example: pg_dump.exe -U postgres -h 147.0.0.1 -p 2345 -W PassTrix > pam360.sql
  3. Use a database connectivity tool like pgAdmin and register the created RDS instance with the available inputs.
  4. Create a new database in the RDS instance for PAM360.
  5. Now, restore the dump file in the created database using the command - psql -U <MasterUsername> -h <RDSInstanceEndpoint> -p <Port> -d <DatabaseName> -f pam360.sql.
    For example: psql.exe -U admin -h postgresrestore.cs2vdnfhjxh3.us-east-1.rds.amazonaws.com -p 5432 -d test -f pam360.sql
  6. Once you have restored the dump in the Aurora (PostgreSQL compatible) database, verify if the services work by installing the necessary extensions, such as 'pgcrypto'. To do so, execute the below command - CREATE EXTENSION pgcrypto;
  7. Next, execute the following commands:
    • update task_input set admin_status = 4 where schedule_id in (select schedule_id from schedule where schedule_name like 'FWBackupSchedule');
    • update DEFAULT_TASK_INPUT set variable_value = 'dump' where variable_name like 'backup.content.type';
  8. Make copies of the following files under <PAM360 Installation Directory>\conf directory and rename them:
    1. customer-config.xml to customer-config_old.xml.
    2. database_params.conf to database_params_old.conf.
  9. Ensure that the pam360_key.key is accessible by the PAM360 application. If you have hosted your PAM360 application in AWS, move the key to the EC2 instance where it is reachable to the PAM360 application.
  10. From the <PAM360 Installation Directory>\conf folder, open the database_params.conf file in a text editor and perform the following actions:
    1. Update the Master username in the username field and the Master password in the password field.
    2. Update the localhost detail in the URL with the copied Endpoint value.
    3. Update the port number to the actual port number of the PostgreSQL instance in AWS. By default, the PostgreSQL compatible RDS database runs on port 5432.
    4. Search for db.password.encrypted=true and change it as db.password.encrypted=false
  11. Open customer-config.xml in a text editor, search for startdbserver= true and change its value to false.
  12. Download the PostgreSQL RDS region's Root CA and import it into PAM360 using the importCert.bat command.
  13. Make a copy of server.xml present inside <PAM360 Installation Directory>\conf directory and make the following changes:
    1. Search for the keyword keystorepass= and change the keystorepass to passtrix, so it should be keystorepass="passtrix".
    2. Also, change the entry keystorePassEncrypted="true" to keystorePassEncrypted="false".
  14. Now, start the PAM360 service.

You have now successfully migrated data from PostgreSQL Database to Amazon RDS PostgreSQL Database.
Top