PostgreSQL preparation on Windows - Accounting Service - SSO

This page will describe how to install a very basic PostgreSQL Server RDBMS installation with a database for Ubisecure Accounting Service. In a clustered environment the same PostgreSQL installation is naturally used by all of the nodes.

Many details will vary on a per customer basis, so further tuning is left to the integrator. Clustered installation of PostgreSQL itself, backup, restore etc. are not in the scope of this document.

Following commands are just examples on how to install a basic PostgreSQL database. You might want to use your own commands.

Obtain and install PostgreSQL

PostgreSQL binaries and instructions on how to use platform specific package managers to download and install PostgreSQL, can be found at the following URL:
https://www.postgresql.org/download/

Having obtained PostgreSQL binaries, run the wizard or relevant binaries to install. The installation process is documented and maintained by your PostgreSQL vendor, so please follow their instructions to ensure the best possible configuration. We have tested Accounting Service with versions listed under System Recommendations and Supported Platforms / Relational Databases.

Create a database user

cd "C:\Program Files\PostgreSQL\12.5\bin" (your PostgreSQL installation directory)

.\psql.exe --username=postgres -c "CREATE USER <accounting.datasource.username in win32.config> WITH PASSWORD '<accounting.datasource.password in win32.config>'"

Replace <accounting.datasource.username in win32.config> with your setting (without angle brackets).

Replace <accounting.datasource.password in win32.config> with your setting (without angle brackets).

Enter postgres user password when requested.

Create the database

.\psql.exe --username=postgres -c "CREATE DATABASE accountingdb WITH OWNER = <accounting.datasource.username in win32.config> TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = pg_default CONNECTION LIMIT = -1;"
.\psql.exe --username=postgres -c "GRANT CONNECT, TEMPORARY ON DATABASE accountingdb TO public;"
.\psql.exe --username=postgres -c "GRANT ALL ON DATABASE accountingdb TO <accounting.datasource.username in win32.config>;"

Replace <accounting.datasource.username in win32.config> with your setting (without angle brackets).

You may choose a different database name instead of accountingdb and define it as part of accounting.datasource.url in win32.config.

Add PostgreSQL LC_COLLATE and LC_CTYPE settings if the default settings are not suitable for you. At the moment Accounting Service does not have any language dependent data that would require a specific locale but these settings are not trivial to change for an existing database.

Enter postgres user password when requested.