Symptoms

An Odin Automation Management Node oss database dump was created with pg_dump or pg_dumpall utility.

When it gets restored with pg_restore, the process takes unreasonably huge amount of time (up to few days), depending on the amount of records in aps_resource_link table.

Cause

Since OA 7.0, there is an additional integrity check that verifies APS resource links consistency upon their modification. This check slows down the database restoration greatly.

Resolution

Use the following SQL query before backing up oss database:

INSERT INTO aps_disable_link_check_consistency VALUES (1);

Once the dump is created, delete the entry:

DELETE FROM aps_disable_link_check_consistency;

This will increase the speed of database restoration.

Alternatively, if the database backup operations are performed several times a day and it is not acceptable to disable consistency checks so frequently, it is possible to create the table before performing the restoration.

The action sequence is:

  1. Manually drop and recreate the database:

    DROP DATABASE oss;
    CREATE DATABASE oss;
    
  2. Create the table:

    CREATE UNLOGGED TABLE aps_disable_link_check_consistency (
        pk integer NOT NULL
    );
    
    ALTER TABLE public.aps_disable_link_check_consistency OWNER TO oss;
    
    INSERT INTO aps_disable_link_check_consistency VALUES (1);
    
  3. Restore the dump without '-Fc' option:

    # pg_restore -j 8 --disable-triggers -d oss osscore.dump
    

Internal content