Database Setup Guide


There exists 3 different Database Systems that are supported by PowerFolder: MSSQL, PostgreSQL, MySQL.

You are free to choose which database you want to use.

Install MSSQL Server

Microsoft provides a guide for installing SQL Server. Generally the guide provides the most basic steps to install SQL Server. A few changes need to be applied during the installation:

  • Configuring the database collation
  • Enabling mixed-mode authentication

Configuring the database collation

Microsoft SQL Server collation settings required by PowerFolder Server

It is important that you configure the following database collation options during the installation of SQL Server:

  • Collation: Latin1_General
  • Case-sensitive
  • Accent-sensitive

So the final database engine should result in: Latin1_General_CS_AS


Enabling mixed-mode authentication

Microsoft SQL Server support Windows authentication based logins, however for PowerFolder Server it is necessary to create a separate user and password which can access the PowerFolder Server database. During the installation of SQL Server it is possible to enable mixed-mode authentication, which supports external logins with dedicated users created within the SQL Server.

Create a SQL Server database and user

  1. Download/install SQL Server Management Studio to connect to the database server.
  2. Right-click on Security > Logins to create a new user.
  3. Right-click on Databases to create a new database and assign the new user, we've just created as an owner of the new database.

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the Microsoft SQL Server:

  1. Download the JDBC Driver 6.0 for SQL Server (.exe for Windows and for Linux the .tar.gz file).
  2. Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
  3. Extract the JDBC Driver 6.0.
  4. After extraction go in the extracted folder and under sub-folder jre8 you will find out the file sqljdbc42.jar.
  5. Move sqljdbc42.jar from the package downloaded into the /libs sub-directory created in the step before.
  6. Restart PowerFolder Server.

(Warnung) Please note: When running on Linux, please make sure you are using the current start/stop script for PowerFolder Server, which is called PowerFolder-Server.sh and can be download here. Before the release of version PowerFolder Server 8.x we provided a start/stop script, which doesn't include .jar files in the /libs sub-directory, when (re)starting PowerFolder Server. This is important!

Setup the database connection

Once we added the driver for JDBC driver to connect to the SQL Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

  1. Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the SQL Server database and user you performed in step three of this guide.

    hibernate.connection.url=jdbc:sqlserver://localhost:1433;databaseName=powerfolderdb;
    hibernate.connection.username=powerfolderuser
    hibernate.connection.password=powerfolderpass
    hibernate.dialect=de.dal33t.powerfolder.server.db.sql.SQLAddNVarCharDialect
    hibernate.c3p0.max_size=500
    
    

    (Info) The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

  2. Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.



Install MySQL Server 

MySQL Servers

PowerFolder provides support for:

  • Percona XtraDB Cluster 8.0 & 5.7
  • MySQL Server 8.0 / 5.X
  • Percona MySQL Cluster 8.0


The way to install MySQL Server depends on the operating system where you are installing it. In the official MySQL documentation there are MySQL Server Installation Guides for almost all operating systems available:

MySQL Configuration Settings required by PowerFolder Server

[mysqld]
...
character-set-server=utf8
...

It is important that you configure your MySQL Server to use UTF-8 for encoding and InnoDB as storage engine as well raising the connection limit.

Configure those requirement in the MySQL Server's my.cnf configuration file:

  • Setting the default character set to UTF-8:
  • Setting the default storage engine to InnoDB:
[mysqld]
...
default-storage-engine=INNODB
...
  • Increasing the the connection/error and long query limits:
[mysqld]
...
max_connections                 = 3000
max_connect_errors              = 3000
long_query_time                 = 5
...
  • Increasing the InnoDB buffer pool size
[mysqld]
...
innodb-buffer-pool-size			= 2757M
...


(Warnung) Please note that you need to restart the MySQL Server service for the changes to take effect.

Create a MySQL Database and User

  1. Use your MySQL super user (the "root" account) to login with the "mysql" command line tool:

    mysql -u root -p
  2. Create an empty database and a dedicated user for PowerFolder server:

    CREATE DATABASE powerfolder;
    CREATE USER 'powerfolderuser'@'localhost' IDENTIFIED BY 'powerfolderpass';
  3. Grant privileges to access the new database and a password for that user (MySQL 5):

    GRANT ALL PRIVILEGES ON powerfolder.* TO 'powerfolderuser'@'localhost' IDENTIFIED BY 'powerfolderpass';

    (Info) Replace "powerfolderuser" and "powerfolderpass" with a username and password of your choice. If PowerFolder Server is not running on the same server as your MySQL database server, replace "localhost" with the hostname or IP address of the PowerFolder server.

  4. Grant privileges to access the new database (MySQL 8):

    GRANT ALL PRIVILEGES ON powerfolder.* TO 'powerfolderuser'@'localhost';

Setting UTC time zone in MYSQL database

In the Sommer and Winter time changes there comes issues in the database connections and the communication between the PowerFolder-Server and the database can also be affected if following changes are not carried out:

  1. Direct your database server so that it can recognize the timezone

    mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
  2. Set now the UTCin the MYSQL console

    SET GLOBAL time_zone = UTC;
  3. Set the time zone as UTC also in the MYSQL-Configuration file

    default-time-zone = 'UTC'

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the MySQL Database Server:

  1. Download the v8.0.28 Connector/J for MySQL. (MySQL 8)
  2. Download the v5.1.47 JDBC Database Driver for MySQL. (MySQL v5.X)
  3. Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
  4. Extract the mysql-connector-java-<version>-bin.jar (where <version> is the actual release number) from the package downloaded into the /libs sub-directory created in the step before.
  5. Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the MySQL Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

  1. Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the MySQL Database and User you performed in step three of this guide.

    hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder?serverTimezone=UTC&useLegacyDatetimeCode=false 
    hibernate.connection.username=powerfolderuser
    hibernate.connection.password=powerfolderpass
    hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
    hibernate.c3p0.max_size=500
    
    * Without SSL:
    hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder?serverTimezone=UTC&useLegacyDatetimeCode=false&useSSL=false

    (Info) The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

  2. Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.


(warning) After the changes in the MYSQL-Console, Configuration and PowerFolder.config please restart the MSQL-Service and PowerFolder-Server also.



Install PostgreSQL Server

The way to install PostgreSQL Server depends on the operating system where you are installing it. In the official PostgreSQL documentation there are guides for the installation available.

Please make sure that the PostgreSQL Server will listen on the hostname and port, which you are going to configure later on in the PowerFolder configuration. Furthermore you've to allow the user created below to access the database created below in the PostgreSQL network configuration.

Create a PostgreSQL Database and User

  1. Use your PostgreSQL super user (the "postgres" account) to login with the "psql" command line tool:

    psql -h localhost -p 5432 -U postgres
  2. Create a new user account:

    CREATE USER powerfolderuser WITH PASSWORD 'powerfolderpass';

    (warning) You might need to use backslashes for escaping special characters in the password.
    (warning) Replace "powerfolderuser" and "powerfolderpass" with a username and password of your choice.
     

  3. Create an empty database:

    CREATE DATABASE powerfolder;
  4. Create a user privileged to access the new database and a password for that user:

    GRANT ALL PRIVILEGES ON DATABASE powerfolder to powerfolderuser;

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the PostgreSQL Database Server:

  1. Download the JDBC Database Driver for PostgreSQL.
  2. Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
  3. Move the postgresql-<version>.jdbc41.jar (where <version> is the actual release number) to the /libs sub-directory created in the step before.
  4. Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the PostgreSQL Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

  1. Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the PostgreSQL Database and User you performed in step three of this guide.

    hibernate.connection.url=jdbc:postgresql://localhost:5432/powerfolder
    hibernate.connection.username=powerfolderuser
    hibernate.connection.password=powerfolderpass
    hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
    hibernate.c3p0.max_size=500

    (lightbulb) The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

  2. Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

We recommend you to install and use the above stated three databases, some of our customers are also using MariaDB and thats why we are taking the database in our documentation, it means that we are unable to support this database till is marked from QA-Department as tested and supported.


Install MariaDB Server

MariaDB v10.7.3 GA

PowerFolder server supports the new MariaDB community or enterprise edition. We prefer to use the GA (general avaialabilty) version and not the development builds. 


Depending on systems the MariaDB can be installed using following instructions:


Please follow the steps as stated in the documentation and you can also repeat the steps of installation of MySQL for installing a MariaDB Server.

After upgrading to PFS v14 the coding / charset of MariaDB 10.3 should be changed to default colation utf8mb4_general_ci, so that the database can open a connection and set the foriegn key.


collation-server = utf8mb4_general_ci
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
SET FOREIGN_KEY_CHECKS=0 

Setup the MariaDB Connector/J

PowerFolder Server needs to be loaded with the correct MariaDB Connector/J to establish a connection with the MySQL Database Server:

  1. Download the java file MariaDB Connector/J (v 2.7.3)
  2. Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
  3. Extract the mariadb-java-client-<version> (where <version> is the actual release number) from the package downloaded into the /libs sub-directory created in the step before.
  4. Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the MariaDB Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

  1. Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the Maria Database and User you performed in step three of this guide.

    hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder
    hibernate.connection.username=powerfolderuser
    hibernate.connection.password=powerfolderpass
    hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
    hibernate.c3p0.max_size=500
  2. (info) The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

  3. Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

(warning) After the changes in the MYSQL-Console, Configuration and PowerFolder.config please restart the MSQL-Service and PowerFolder-Server also.

Overview: