Migrating Data from Local PostgreSQL Database to an External PostgreSQL Database

The PAM360 application comes with PostgreSQL as its integrated backend database. Nonetheless, PAM360 also offers the functionality to migrate from the bundled local database to an external PostgreSQL database, thus enabling seamless data transfer. In this document, you will find a detailed explanation of the process involved in migrating your data between the local and external PostgreSQL databases.

Steps Required

Below are the steps to migrate data from the bundled PostgreSQL database to an external PostgreSQL database:

  1. Open the command prompt and navigate to the <PAM360_Installation_folder>\pgsql\bin directory.
  2. Create a dump file using the command - pg_dump dbname > pam360.sql. For example,
    1. In Windows - pg_dump.exe -U postgres -h 147.0.0.1 -p 2345 -W PassTrix > pam360.sql
    2. In Linux - ./pg_dump -U postgres -h 147.0.0.1 -p 2345 -W PassTrix > pam360.sql
  3. Create a database cluster in the PostgreSQL provided with the database owner's permission.
  4. Post the database creation, ensure that the extension 'pgcrypto' is available in the extension drop-down. If there is no such available extension, create it using the command - CREATE EXTENSION pgcrypto;
  5. Now, restore the dump file in the created database cluster using the following commands:
    1. For Windows - psql.exe -U admin -h 142.10.0.1 -p 5432 -d test -f pam360.sql
    2. For Linux - ./psql -U admin -h 142.10.0.1 -p 5432 -d test -f pam360.sql
  6. Execute the following commands:
    1. update task_input set admin_status = 4 where schedule_id in (select schedule_id from schedule where schedule_name like 'FWBackupSchedule');
    2. update DEFAULT_TASK_INPUT set variable_value = 'dump' where variable_name like 'backup.content.type';
  7. Now, make a copy of the below files under <PAM360_Installation_folder>\conf directory and rename them:
    1. customer-config.xml to customer-config_old.xml.
    2. database_params.conf to database_params_old.conf.
  8. Open the database_param.conf file and do the steps that follow:
    1. Replace localhost with the hostname of the PostgreSQL server running in an external database.
    2. Change the port number to the actual port number of the PostgreSQL instance in an external database.
    3. Set SSL=true, and provide the user details that were used to restore the dump file to the database.
    4. Update the db.password.encrypted to false.
    5. Now, save and close the file database_param.conf.

      6. Notes: The password given here will be in plain text at that instance and will be encrypted from the next consequent occurrence.

  9. Open customer-config.xml and search for startdbserver= true and change its value to false.
  10. Download the PostgreSQL root CA certificate. If you are unaware of creating an SSL for the PostgreSQL database, refer to this section to generate the required SSL certificates and validate the SSL connection.
  11. Import the root CA certificate into PAM360.
  12. Navigate to <PostgreSQL_Installation_folder>pgsql_installation\data and open the file pg_hba.conf:
    1. Add an entry at the end with the IP address of the machine/server on which the PAM360 is running.
      E.g., host all <user name> <ip address>/32 md5

      2. Notes: The above step applies only to users who use an external PostgreSQL enterprise database as their backend database. For users with cloud-based databases, the process will be performed in an automated process.

  13. Navigate to <PostgreSQL_Installation_folder>pgsql_installation\data and open the file postgresql.conf:
    1. Search for listen_addresses and provide the IP address of the server where PAM360 is running.
  14. Restart the PostgreSQL server.
  15. Start the PAM360 service.

Now you have successfully migrated your data from the local PostgreSQL database to the external PostgreSQL database.
Top