Migrating Data from Local PostgreSQL to Amazon RDS PostgreSQL Database

PAM360 comes bundled with PostgreSQL that will function as your local backend database. However, PAM360 also allows you to convert your 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.

Advantages

Amazon RDS provides a high level of security for your PostgreSQL databases. Amazon RDS comes with a set of features such as easy and managed deployments, dynamic scaling, enhanced availability and durability, enhanced monitoring, automated backups, etc. The automated or manual backup feature of Amazon RDS enables point-in-time recovery of your PostgreSQL database instance.

This document explains in detail the process of migrating your data from the local PostgreSQL database to the Amazon RDS PostgreSQL database in PAM360.

Steps Required

Steps to migrate data from bundled PostgreSQL to Amazon RDS PostgreSQL are as follows:

  1. Open the Command Prompt and navigate to the <PAM360_Installation_folder>\pgsql\bin directory.
  2. Create a dump file using the following command:

    3. pg_dump dbname > pam360.sql

    Example: pg_dump.exe -U postgres -h 147.0.0.1 -p 2345 -W PassTrix > pam360.sql

  3. Update the database_params.conf file:
    • Open the database_params.conf file located in the <PAM360_Installation_folder>\conf directory.
    • Locate the configuration file and fetch the DB username and port.

    Note: The database password in the database_params.conf file will be encrypted, which you need to decrypt before use. For any assistance, contact our support team @pam360-support@manageengine.com.


  4. Create an Amazon RDS instance and a master user.
  5. Log in as master user in Amazon RDS and create a database.
  6. Now, restore the dump file in the created database.

    7. Example: psql.exe -U admin -h postgresrestore.cs2vdnfhjxh3.us-east-1.rds.amazonaws.com -p 5432 -d test -f pam360.sql

  7. Once you have restored the dump in RDS PostgreSQL, verify if the services works by installing the necessary extensions, such as 'pgcrypto'. To do so, execute the below command.

    8. CREATE EXTENSION pgcrypto;

  8. 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';
  9. Replace the pam360_key.key from local instance to EC2 instance.
  10. Now, make a copy of the below files under the <PAM360_Installation_folder>\conf directory and rename them:
    • customer-config.xml to customer-config_old.xml.
    • database_params.conf to database_params_old.conf.
  11. Open the database_param.conf file.
    • Replace localhost with the hostname of the PostgreSQL server running in AWS.
    • Change the port number to the actual port number of the PostgreSQL instance in AWS.
    • Set SSL=true and provide the user details that were used to restore the dump file to the database.
  12. Open customer-config.xml and search for startdbserver= true and change its value to false.
  13. Download PostgreSQL root CA.
  14. Import the root .pem file into PAM360.
  15. Start the PAM360 service.

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