Home Documentation Tracker Download

PostgreSQL as database (instead of Derby)

By default, subshare uses Derby, a small embedded database. Derby does not require any configuration at all and is therefore a very easy solution.

However, with extremely large repositories, Derby might get to its limits and you might want to switch to an external database-server. We thus also support PostgreSQL as alternative to Derby.

Install PostgreSQL-server

On an Ubuntu or another Debian-based system, you can install the PostgreSQL-server with the administration-tool pgAdmin via the following commands:

sudo apt install postgresql postgresql-contrib pgadmin3

sudo -u postgres psql postgres

\password postgres

In this example, I use the password ‘my_pg_pw’, but of course, you should enter a strong and secure password.

But be careful with special characters – some characters need escaping in the properties-file. In order to avoid problems with wrong escape-chars in the properties-files, it is recommended that the password contains only the following characters:

  • A…Z
  • a…Z
  • 0…9
  • +-*/,.;:=’"~?[]()&%$

You must definitely not use the character \ (backslash)! This is an escape-character.

Configure subshare

subshare must know how to connect to the PostgreSQL-server. Therefore, you have to provide the following information in your ${user.home}/.subshare/subshare.properties:

jdbc.hostName = localhost
jdbc.userName = postgres
jdbc.password = my_pg_pw

The user specified here must have the permissions to create databases. In case the creation & initialisation of a database fails, it tries to drop it, hence giving it the permission to drop a database might make sense. But if you don’t want this for safety reasons, create-database-permission is all that’s essentially needed.

Optional name-prefix/-suffix

You can optionally configure a prefix or a suffix (or both) for the database-names:

jdbc.dbNamePrefix = subshare_
#jdbc.dbNameSuffix = _subshare

Optional sys-DB

When creating a new database, the user first needs to connect to a system-database, since the new database does not yet exist. By default, when not specifying any database in the JDBC-URL, the user connects to the database with the same name, e.g. the user “postgres” connects to the database “postgres”.

If this is not possible, you want to specify another initial database via the following setting:

jdbc.sysdbName = EXAMPLE_DB

Force usage of PostgreSQL

If you set the above correctly, subshare automatically uses PostgreSQL. If, however, the database cannot be accessed, e.g. because of some wrong data, it falls back to Derby.

In order to enforce the usage of PostgreSQL, you might want to add the following setting:

databaseAdapter.name = postgresql

Note: You do not need to restart any subshare-process. Changes to this configuration become visible to existing processes when you save the configuration-file.

Migrate an existing repository-database

When you switch to PostgreSQL using the above settings, this has an effect only on newly created databases! This means, your existing subshare-repositories are not automatically migrated.

You can migrate an existing repository after you configured the above by deleting the file dbmigrate.deleteToRun in the repository’s .subshare-repo/-folder.

Important: After the migration, the repository still stays locked until you delete the file dbmigrate.status.properties, which was created during the migration. The idea is to give you the possibility to manually check the result. At this point, you may still undo the migration and switch back to the previously used database. The migration creates backup-files which you may restore (e.g. you’ll find a subshare-persistence.properties.dbmigrate_k929skee.bak which contains your original persistence-configuration).

Important: Never manually modify the file dbmigrate.status.properties! Only delete it, when the migration is complete and you want to activate the repository, again.

Logging

In order to monitor the progress of the migration, you can control the log-level in your ${user.home}/.subshare/logback.*.xml. On a workstation, the relevant file is ${user.home}/.subshare/logback.localserver.xml (unless you use fancy settings preventing a local-server-process to be spawned).

Add or update the following elements:

<logger name="co.codewizards.cloudstore.local.db" level="DEBUG" />
<logger name="org.subshare.local.db" level="DEBUG" />

Important: You should not set it to TRACE or ALL unless you’re a developer and know what you’re doing. The migration of a productive database likely causes millions of log-lines, if you set the log-level finer than DEBUG. You do not want this! Either use INFO or DEBUG.

With at least the log-level INFO being active, you’ll see a log-message like this, as soon as the migration starts:

06:20:29.643 [main] INFO  c.c.c.l.d.DatabaseMigrater - migrateIfNeeded: localRoot='/home/mn/...path...': sourceDatabaseAdapterName == 'derby' != targetDatabaseAdapterName == 'postgresql' :: Starting migration, now!

When is it done?

The migration of a database is completely done, when you see the following log-message (requires log-level INFO being active):

06:20:31.524 [main] INFO  c.c.c.l.d.DatabaseMigrater - migrateIfNeeded: localRoot='/home/mn/...path...': Migration complete!

Also, the file dbmigrate.deleteToRun (which you deleted to start the migration) is recreated.

Additionally, the file dbmigrate.status.properties in the repository’s .subshare-repo/-folder contains the following line, as soon as the work is complete:

MIGRATION_COMPLETE=MIGRATION_COMPLETE *** *** *** Please delete this file now. *** *** ***

Important: Never manually modify the file dbmigrate.status.properties! You may read it. You should finally delete it. But you must not modify it!
Documentation
About
Releases