Applications Manager Database Migration Using SQL Dump

Applications Manager Database Migration Using SQL Dump

Step 1: Rename Directories

Notes
Skip this step if the directories are already renamed.
  1. Rename <AppManager_home>/working/pgsql to <AppManager_home>/working/pgsql_11.
  1. Rename <AppManager_home>/working/pgsql_15.5 to <AppManager_home>/working/pgsql.

Step 1.1: PostgreSQL Config Changes

Notes
Skip this step if Ddb.skipInstaller=true is used.
Ensure that the following properties are updated.
Windows:
InfoFile: <AppManager_home>/working/pgsql/share/postgresql.conf.sample
Search and replace the following:
Original Configuration
Updated Configuration
Description
#unix_socket_directories = '/tmp'
unix_socket_directories = '.'
Changed the Unix socket directory to the current directory.
include_dir = '../ext_conf'
include_dir = '../../ext_conf'
Adjusted path to external config directory.
#include_if_exists = '...'
include_if_exists = 'postgres_ext.conf'
Included a specific config file if it exists.
#log_min_duration_statement = -1
log_min_duration_statement = 60000
Enabled slow query logging (queries taking >60s).
#max_locks_per_transaction = 64
max_locks_per_transaction = 10000
Increased max locks per transaction.
#shared_buffers = 32MB
shared_buffers = 32MB
Uncommented without changing value (enabled explicitly).
InfoFile: <AppManager_home>/working/pgsql/share/pg_hba.conf.sample
Search and replace the following:
Original Configuration
Updated Configuration
Description
#host all all ::1/128 @authmethodhost@
host all all ::1/128 @authmethodhost@
Enabled local IPv6 connections for all users using specified auth method.
#host replication all 127.0.0.1/32 @authmethodhost@
host replication all 127.0.0.1/32 @authmethodhost@ 
Enabled replication access over IPv4 localhost using specified auth method.
#host replication all ::1/128 @authmethodhost@
host replication all ::1/128 @authmethodhost@
Enabled replication access over IPv6 localhost using specified auth method.

Linux:
The same changes apply to:
Info
  • <AppManager_home>/working/pgsql/share/postgresql/postgresql.conf.sample
  • <AppManager_home>/working/pgsql/share/postgresql/pg_hba.conf.sample
Additional config file:
InfoFile: <AppManager_home>/working/pgsql/ext_conf/00framework_ext.conf
Search and replace the following:
Original Configuration
Updated Configuration
Description
unix_socket_directories = '/tmp,../tmp/' 
unix_socket_directories = '.'
Changed the socket directory to the current directory for simplicity or isolation.

Step 2.1: Obtain PGPASSWORD for postgres User

Windows:
  1. Open Command Prompt (as Administrator)
  2. Navigate to <AppManager_home>/bin.
  3. Run:
Info
DBConfiguration.bat -u postgres echo %PGPASSWORD%
For base builds below v16670:
set PGPASSWORD=appmanager echo %PGPASSWORD%

Linux:
  1. Open the Terminal
  2. Navigate to <AppManager_home>/bin
  3. Run:
Info
. ./DBConfiguration.sh -u postgres echo $PGPASSWORD
For base builds below v16670:
export PGPASSWORD=appmanager echo $PGPASSWORD

Step 2.2: Invoke initPGSQL

Notes
Skip this step if <AppManager_home>/working/pgsql/data/amdb/base already exists
Windows:
  1. Create password.txt in <AppManager_home>/working/bin
  2. Paste the PGPASSWORD into this file
  3. Run:
    Info
    cd <AppManager_home>/working/bin initPGSQL.bat
  4. Copy postgres_ext.conf from:
    Info<AppManager_home>/working/pgsql_11/data/amdb → <AppManager_home>/working/pgsql/data/amdb
Linux:
  1. Same as Windows steps
  2. Instead of .bat, run:
    Info
    cd <AppManager_home>/working ./bin/initPGSQLDB.sh

Step 3: Verify & Update Port Numbers

  • Old PostgreSQL (v11) port: 15433
  • New PostgreSQL (v15.5) port: 15432
Update <AppManager_home>/working/pgsql_11/data/amdb/postgres_ext.conf to use port 15433

Step 3.1: Stop PostgreSQL Servers

Windows:
Info
cd <AppManager_home>\working\pgsql\bin pg_ctl.exe -w -D ..\data\amdb -o -p15432 stop -s -m fast cd <AppManager_home>\working\pgsql_11\bin pg_ctl.exe -w -D ..\data\amdb -o -p15433 stop -s -m fast
Linux:
Info
cd <AppManager_home>/working/pgsql/bin ./pg_ctl -w -D ../data/amdb -o -p15432 stop -s -m fast cd <AppManager_home>/working/pgsql_11/bin ./pg_ctl -w -D ../data/amdb -o -p15433 stop -s -m fast
As root:
Use su - postgres -c with full paths

Step 4: Start PostgreSQL Servers

Follow the same structure as Step 3.1, replacing stop with start.

Step 5: Connect to PostgreSQL v15.5

Info
cd <AppManager_home>/working/pgsql/bin psql -U postgres -p 15432 -h localhost
(Use psql.exe on Windows)

Step 6: Create amdb Database

Info
DROP DATABASE IF EXISTS amdb; CREATE DATABASE amdb; GRANT CONNECT ON DATABASE amdb TO public;
Create dbuser:
  1. Follow Step 2.1 to get PGPASSWORD for dbuser.
  2. Then run:
Info
CREATE USER dbuser WITH CREATEDB NOCREATEROLE LOGIN REPLICATION ENCRYPTED PASSWORD '<PGPASSWORD>';
  1. Switch to amdb and run:
Info
\c amdb
GRANT ALL ON SCHEMA public TO dbuser;
SET search_path=public;

CREATE EXTENSION "pgcrypto";
CREATE EXTENSION CITEXT;
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION amcheck;

CREATE TABLE AM_Postgres15MigrationStatus (
ID BIGSERIAL NOT NULL,
TABLENAME VARCHAR(255) NOT NULL,
STATUS INTEGER DEFAULT 2 NOT NULL,
TIME BIGINT,
COMMENTS TEXT,
PRIMARY KEY(TABLENAME) );
  1. Exit with \q.

Step 7: Perform SQL Dump and Restore

Windows:
Info
cd <AppManager_home>\working\pgsql_11\bin
pg_dump.exe -U postgres -p 15433 -h localhost -c -v -f <AppManager_home>\dump11.sql amdb

cd <AppManager_home>\working\pgsql\bin
psql.exe -U postgres -p 15432 -h localhost -d amdb -c -v -f <AppManager_home>\dump11.sql
Linux:
Info
cd <AppManager_home>/working/pgsql_11/bin ./pg_dump -U postgres -p 15433 -h localhost -c -v -f <AppManager_home>/dump11.sql amdb cd <AppManager_home>/working/pgsql/bin ./psql -U postgres -p 15432 -h localhost -d amdb -c -v -f <AppManager_home>/dump11.sql
As root:
Info
su - postgres -c "<AppManager_home>/working/pgsql_11/bin/pg_dump -U postgres -p 15433 -h localhost -c -v -f <AppManager_home>/dump11.sql amdb" su - postgres -c "<AppManager_home>/working/pgsql/bin/psql -U postgres -p 15432 -h localhost -d amdb -c -v -f <AppManager_home>/dump11.sql"

Step 8: Verify Data Migration

Connect to the database and run:
Info
SELECT TYPE,
COUNT(CASE WHEN DCSTARTED = 2 THEN DCSTARTED ELSE NULL END) AS CONFIGURED,
COUNT(CASE WHEN DCSTARTED != 2 THEN DCSTARTED ELSE NULL END) AS UNCONFIGURED,
COUNT(DCSTARTED) AS TOTAL
FROM AM_ManagedObject, AM_ManagedResourceType
WHERE AM_ManagedObject.TYPE = AM_ManagedResourceType.RESOURCETYPE AND TYPE != 'Network' AND TYPE NOT IN ('Ping Monitor','DNSMonitor','MAIL-server','LDAP Server','RBM','Telnet (EUM)') GROUP BY TYPE ORDER BY TYPE;

Step 9: Stop PostgreSQL Servers

Repeat Step 3.1.

Step 10: Start Applications Manager

Launch the server and confirm that all monitors, historical data, and configurations are intact.

      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                      Related Products