Child pages
  • Migrating from Derby to PostgreSQL
Skip to end of metadata
Go to start of metadata

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:

    <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):

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

    <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

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

Fix ReferenceSettings for AlarmService

    <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));

 

 

 

 

  • No labels