Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 9 Next »

For storage of data in the various services some databases is needed. Currently the choice of database is between embedded Derby databases or Postgres.

The databases are connected to through the JDBC connections defined in ReferenceSettings.xml configuration file for the respective services.

Common for both types of databases, is that the needed database schemas (including migration) are included in the service distribution tar-ball, in the 'sql' directory.

Derby

For embedded Derby databases the JDBC driver of "org.apache.derby.jdbc.EmbeddedDriver" is used.

From Bitrepository 1.1 and onwards Derby databases will be automatically created and migrated at the start-up of the respective service, provided that the service is configured to use a Derby database.

Upon service start-up it is attempted to create a connection to the database, if this fails, a new database is created.

When a connection is obtained, the database schema is checked to see if it needs migration, followed by the needed migration (if needed).

Postgres

For Postgres databases the JDBC driver of "org.postgresql.Driver" is used.

In contrast to Derby databases, usage of Postgres databases will require manual work, as the databases are not automatically created nor migrated. Attempting to start a service on a database with an old schema will make the service log message that the database is of the wrong version and shutdown.

Using of Postgres for databases will require that person installing the services are acquainted with installation, setup and maintenance of Postgres. As such this is not documented here.

Database creation

The database schemas for Postgres is found in the respective service tar-ball under the 'sql/postgres' directory. The full database schema for the given release has the suffix 'Creation.sql'. It should be noted that certain services needs two databases (i.e. the integrity service), in which case there will be two SQL-files with the suffix 'Creation.sql'. 

    <IntegrityDatabase>
      <DriverClass>org.postgresql.Driver</DriverClass>
      <DatabaseURL>jdbc:postgresql:integritydb</DatabaseURL>
      <Username>user</Username>
      <Password>pass</Password>
    </IntegrityDatabase>

Assuming the above configuration for the IntegrityDatabase the database schema can be loaded with the following command:

psql -d integritydb -f /path/to/extracted/service/tarball/sql/postgres/integrityDBCreation.sql

Depending on setup, a username, password, port number etc. may be needed.

Migration

Migration of databases is slightly more complex. Schemas are only provided for migrating from one version to the next (from N to N+1). So if it is needed to migrate from two version (from N to N+2), the migration has to be done in two steps (from N to N+1, and N+1 to N+2).

The migration schemas are named in the form:

<databasename>Update<from-version>to<to-version>.sql

I.e. auditTrailServiceDBUpdate2to3.sql or auditTrailServiceDBUpdate3to4.sql.

To check the version of the database the 'tableversions' table in the given database can be queried. For the Audittrail service database this could be done with:

psql -d auditservicedb -c "SELECT version FROM tableversions WHERE tablename='auditservicedb';"

From the provided version number the correct migration script can be applied with:

psql -d auditservicedb -f /path/to/extracted/service/tarball/sql/postgres/auditTrailServiceDBUpdate3to4.sql

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

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

    <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