Versions Compared

Old Version 7

changes.mady.by.user Jonas Lindberg Frellesen

Saved on

compared with

New Version 8

changes.mady.by.user Mathias Søby Jensen

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

The above example is for migrating the AuditTrail services database from version 3 to 4.

Migrate from Derby to PostgreSQL

The following page contains a guide to migrate from a Derby database to PostgreSQL database, for version 1.3:

Migrating from Derby to PostgreSQL

 Rough notes on migrating from Derby to PostgreSQL for most databases in the reference code. Assuming everything runs on Linux.

Migrating ReferencePillar or ChecksumPillar databases

Assuming Derby versions of both ChecksumDatabase and AuditTrailContributorDatabase should be migrated to a local PostgreSQL database.

Shut down the pillar before migrating, if it is running.

Setup

Install databases in PostgreSQL, with a super-user named 'bitmag' (and with 'bitmag' password - used in settings)

  • Run from linux console/terminal:
    • createuser -P -s -e bitmag
      • (write password (bitmag) twice)
    • createdb checksumdb
    • createdb auditdb
    • psql -d checksumdb -f /path/to/checksumDBCreation.sql
    • psql -d auditdb -f /path/to/auditContributorDBCreation.sql

Fix ReferenceSettings for the pillar

Replace existing AuditTrailContributerDatabase and ChecksumDatabase elements in PillarSettings in the ReferenceSettings with the following:

Code Block
    <AuditTrailContributerDatabase>
     <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:auditdb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </AuditTrailContributerDatabase>
    <ChecksumDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:checksumdb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </ChecksumDatabase>

Export from existing database

Use the derby tool 'ij' (assuming you are placed in the root installation folder for the pillar):

Code Block
java -cp lib/derby-10.10.1.1.jar:lib/derbytools-10.10.1.1.jar:lib/derbyclient-10.10.1.1.jar org.apache.derby.tools.ij

Then run the following commands to extract the content of the checksum database:

  • CONNECT 'jdbc:derby:/path/to/checksumdb'
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'CHECKSUMS', 'checksums', null, null, null);

And run the following command to extract the content of the AuditTrailContributor database:

  • CONNECT 'jdbc:derby:/path/to/auditcontributerdb';
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'AUDITTRAIL', 'audittrails', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'FILE', 'files', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'ACTOR', 'actors', null, null, null);

This should create the files: 'checksums', 'audittrails', 'files', and 'actors'.

Import the data to the PostgreSQL database

Run the 'psql' application on the konsole/terminale.

Log onto checksumdb and ingest data (and fix the automated sequences)

  • \c checksumdb
  • COPY checksums FROM '/path/to/checksums' with csv;
  • SELECT SETVAL('checksums_guid_seq', (SELECT MAX(guid) FROM checksums));

Log onto auditdb and ingest data (and fix the automated sequences)

  • \c auditdb
  • COPY actor FROM '/path/to/actors' with csv;
  • COPY file FROM '/path/to/files' with csv;
  • COPY audittrail FROM '/path/to/audittrails' with csv;

  • SELECT SETVAL('file_file_guid_seq', (SELECT MAX(file_guid) FROM file));

  • SELECT SETVAL('actor_actor_guid_seq', (SELECT MAX(actor_guid) FROM actor));

  • SELECT SETVAL('audittrail_sequence_number_seq', (SELECT MAX(sequence_number) FROM audittrail));

...

Migrating service databases

Assuming Derby versions of AuditTrailContributorDatabase for IntegrityService, AuditTrailServiceDatabase for AuditTrailService and AlarmDatabase for AlarmService should be migrated to a local PostgreSQL database.

We do not migrate the integrity database, since it automatically will be repopulated on first run of the CompleteIntegrityWorkflow, though we do create it.

Shut down the services before migrating, if they are running.

Setup

Install databases in PostgreSQL, with a super-user named 'bitmag' (and with 'bitmag' password - used in settings)

  • Run from linux console/terminal:
    • createuser -P -s -e bitmag
      • (write password (bitmag) twice)
    • createdb integritydb
    • psql -d integritydb -f /path/to/integrityDBCreation.sql
    • createdb auditdb
    • createdb integrity_auditdb
    • psql -d integrity_auditdb -f /path/to/auditContributorDBCreation.sql
    • createdb auditservicedb
    • psql -d auditservicedb -f /path/to/auditTrailServiceDBCreation.sql
    • createdb alarmdb
    • psql -d alarmdb -f /path/to/alarmServiceDBCreation.sql

Fix ReferenceSettings for IntegrityService

Code Block
    <IntegrityDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:integritydb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </IntegrityDatabase>

    ...
    <AuditTrailContributerDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:integrity_auditdb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </AuditTrailContributerDatabase>

Fix ReferenceSettings for AuditTrailService

Code Block
    <AuditTrailServiceDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:auditservicedb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </AuditTrailServiceDatabase>

Fix ReferenceSettings for AlarmService

Code Block
    <AlarmServiceDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:alarmdb</DatabaseURL>
      <Username>bitmag</Username>
      <Password>bitmag</Password>
    </AlarmServiceDatabase>

Export/Import data for AlarmService

Run in 'ij'

  • connect 'jdbc:derby:/path/to/alarmservicedb';
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'COMPONENT', 'alarm_component', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'ALARM', 'alarm_alarm', null, null, null);

That should make 2 files: 'alarm_component' og 'alarm_alarm'.
Run in 'psql':

  • \c alarmdb
  • COPY component FROM '/path/to/alarm_component' with csv;
  • COPY alarm FROM '/path/to/alarm_alarm' with csv;
  • SELECT SETVAL('alarm_guid_seq', (SELECT MAX(guid) FROM alarm));
  • SELECT SETVAL('component_component_guid_seq', (SELECT MAX(component_guid) FROM component));

Export/Import data for AuditTrailService

Run in 'ij'

  • connect 'jdbc:derby:/path/to/auditservicedb';
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'ACTOR', 'audit_actor', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'COLLECTION', 'audit_collection', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'CONTRIBUTOR', 'audit_contributor', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'FILE', 'audit_file', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'PRESERVATION', 'audit_preservation', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'AUDITTRAIL', 'audit_audittrail', null, null, null);

That should make 6 files: 'audit_actor', 'audit_collection', 'audit_contributor', 'audit_file', 'audit_preservation' og 'audit_audittrail'.
Run in 'psql':

  • \c auditservicedb
  • COPY actor FROM '/path/to/audit_actor' with csv;
  • COPY contributor FROM '/path/to/audit_contributor' with csv;
  • COPY collection FROM '/path/to/audit_collection' with csv;
  • COPY preservation FROM '/path/to/audit_preservation' with csv;
  • COPY file FROM '/path/to/audit_file' with csv;
  • COPY audittrail FROM '/path/to/audit_audittrail' with csv;

  • SELECT SETVAL('preservation_preservation_key_seq', (SELECT MAX(preservation_key) FROM preservation));

  • SELECT SETVAL('file_file_key_seq', (SELECT MAX(file_key) FROM file));

  • SELECT SETVAL('contributor_contributor_key_seq', (SELECT MAX(contributor_key) FROM contributor));

  • SELECT SETVAL('collection_collection_key_seq', (SELECT MAX(collection_key) FROM collection));

  • SELECT SETVAL('audittrail_audit_key_seq', (SELECT MAX(audit_key) FROM audittrail));

  • SELECT SETVAL('actor_actor_key_seq', (SELECT MAX(actor_key) FROM actor));

Export/Import data for AuditTrailContributor databasen for IntegrityService

Run in 'ij'

  • CONNECT 'jdbc:derby:/path/to/auditcontributerdb';
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'AUDITTRAIL', 'integrity_audit_audittrails', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'FILE', 'integrity_audit_files', null, null, null);
  • CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,  'ACTOR', 'integrity_audit_actors', null, null, null);

That should make 3 files: 'integrity_audit_audittrails', 'integrity_audit_files' og 'integrity_audit_actors'.

Run in 'psql':

  • \c integrity_auditdb
  • COPY actor FROM '/path/to/integrity_audit_actors' with csv;
  • COPY file FROM '/path/to/integrity_audit_files' with csv;
  • COPY collection FROM '/path/to/integrity_audit_collection' with csv;

  • SELECT SETVAL('file_file_guid_seq', (SELECT MAX(file_guid) FROM file));

  • SELECT SETVAL('actor_actor_guid_seq', (SELECT MAX(actor_guid) FROM actor));

  • SELECT SETVAL('audittrail_sequence_number_seq', (SELECT MAX(sequence_number) FROM audittrail));