Migrating Data from Local PostgreSQL to Amazon RDS PostgreSQL Database

Password Manager Pro (PMP) 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 <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

  4. Create an Amazon RDS instance and a master user.
  5. Login 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

  8. 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 <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 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 PMP.
  15. Start the PMP service.

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

©2014, ZOHO Corp. All Rights Reserved.

Top