Migrating Data from Local PostgreSQL to Amazon RDS PostgreSQL Database

Password Manager Pro allows you to use Amazon RDS PostgreSQL as a backend database. By default, Password Manager Pro comes bundled with PostgreSQL that will function as your local backend database. Using Amazon RDS, you will be able to convert your on-premise database into an on-demand service. Password Manager Pro allows you to migrate all your data from the local PostgreSQL database to Amazon RDS PostgreSQL database with ease.

Advantages

Amazon RDS is a fully managed service that provides a high level of security for your PostgreSQL databases. Amazon RDS comes with a set of features such as dynamic scaling, easy and managed deployments, enhanced availability, enhanced monitoring, etc. Amazon RDS has an automated or manual backup feature that 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 Password Manager Pro.

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 <PMP_Installation_folder>\pgsql\bin directory. 
  2. Create a dump file using the following command:

    3. pg_dump dbname > pmp.sql

    Example: pg_dump.exe -U postgres -h 171.0.0.1 -p 2345 -W PassTrix > pmp.sql

  3. Update the database_params.conf file:
    • Open the database_params.conf file located in the <PMP_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 @passwordmanagerpro-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.cs2dykmjvxh3.us-east-1.rds.amazonaws.com -p 5432 -d test -f pmp.sql

  7. Once you have restored the dump in RDS PostgreSQL, verify if the service 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 pmp_key.key from local instance to EC2 instance.
  10. Now, make a copy of the below files under the <PMP_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 Password Manager Pro.
  15. Start the Password Manager Pro service.

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