For storage of data Databases are needed for storing date in the various services some databases is needed. Currently the choice of database is between embedded Derby databases or Postgres PostgreSQL.
The databases are connected to through the Connection to the databases is done through JDBC connections, as defined in the ReferenceSettings.xml configuration file for the respective servicesservice.
Common for both types of databases, is that the needed database schemas schema (including migration) are included in the service distribution .tar-ball, in the 'sql' directory.
See the following for a guide to migrate from Derby to PostgreSQL.
Derby
For embedded Derby databases the JDBC driver of "org.apache.derby.jdbc.EmbeddedDriver" is used.
From Bitrepository 1 BitRepository v1.1 and onwards onward Derby databases will automatically be automatically created and migrated at the start-up startup of the respective service, provided that the service is configured to use a Derby database.
Upon service start-up it is attempted startup, it attempts to create a connection to the database, if this fails, a new database is created.
When a connection is obtained, the database schema is schema is checked to see if it needs migration, followed by the needed such a migration ( if needed).
...
PostgreSQL
For Postgres PostgreSQL databases the JDBC driver of "org.postgresql.Driver" is used.
In contrast to Derby databases, usage of Postgres PostgreSQL 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 PostgreSQL databases will require that the person installing the services are acquainted with installation, setup and maintenance of Postgres. As such this is not documented herePostgreSQL.
Database creation
The database schemas schema for Postgres PostgreSQL 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 (ie.eg. the integrity service), in which case there will be two SQL-files with the suffix 'Creation.sql'.
...
Migration of databases is slightly more complex. Schemas are The schema only provided provides for migrating from one version to the next (from N to N+1). So if it is needed to migrate migration from two version (from N to N+2 )is needed, the migration has to be done in two steps (from N to N+1, and from N+1 to N+2).
The migration schemas are named in the form:has the following naming convention
<databasename>Update<from-version>to<to-version>.sql
IE.eg. auditTrailServiceDBUpdate2to3.sql or auditTrailServiceDBUpdate3to4.sql.
To check the version of the database the 'tableversions' table in the given database can be queried. E.g. For the Audittrail service for the AuditTrail-Service database this could be done with :the following command
| Code Block |
|---|
psql -d auditservicedb -c "SELECT version FROM tableversions WHERE tablename='auditservicedb';" |
From the provided version number the correct migration script can then be applied with:
| Code Block |
|---|
psql -d auditservicedb -f /path/to/extracted/service/tarball/sql/postgres/auditTrailServiceDBUpdate3to4.sql |
The above example is for migrating the AuditTrail services -Services database from version 3 to 4., as is mentioned in the name of the script.
Migrate from Derby to PostgreSQL
...
| Anchor | ||||
|---|---|---|---|---|
|
The following are rough notes on migrating from Derby to PostgreSQL for most databases in the reference code. Assuming The guide assumes that everything runs on Linux.
Migrating ReferencePillar or ChecksumPillar databases
Assuming In the following, assume that the Derby versions of both ChecksumDatabase and AuditTrailContributorDatabase should needs to be migrated to a local PostgreSQL database.
Shut down Firstly, shutdown the pillar before migrating, if it is running before continuing.
Setup
Install the PostgreSQL database
- Install PostgreSQL.
Install databases in PostgreSQL, with a Create a super-user named 'bitmag' (and with 'bitmag' as password - (used in settings)
...
- (write password (bitmag) twice)
...
by running the following in the terminal
| Code Block |
|---|
createuser -P -s -e bitmag - When prompted, 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
...