<head><title>PostgreSQL as database</title></head>
$!{decoration.custom.setAttribute("contentHeaderLogo", "../images/header-notebook.jpg")}
#set( $H = '#' )

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.


$H$H$H 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
<a href="https://docs.oracle.com/javase/7/docs/api/java/util/Properties.html#load(java.io.Reader)" target="_blank">properties-file</a>.
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.


$H$H$H 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.


$H$H$H$H Optional name-prefix/-suffix

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

	jdbc.dbNamePrefix = subshare_
	#jdbc.dbNameSuffix = _subshare


$H$H$H$H 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


$H$H$H$H 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.


$H$H$H 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.


$H$H$H$H 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!


$H$H$H$H 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!
