Shared Databases

PostgreSQL

The IT center has a shared PostgreSQL cluster as well as a single-member test server for test databases.

Currently, PostgreSQL version 14 is in use. Please note that, for example, the Java connector must be sufficiently updated to ensure successful connectivity. The service requires client software to support scram-sha-256.

Ordering a New Database

You can order a database for shared use from PostgreSQL at: atk-tietokannat@helsinki.fi. Please specify at least the following in your order:

1. Database/database ID name (for example: sovellusx) and whether it is intended for the production cluster or the test server.
2. dIP address/IP address space from which the database is to be accessed. Known spaces include VDI and OpenShift, which should be mentioned here. Otherwise use the name of the of the server(s).
3. Database administrator (name and email address).

Connecting to the Database

Connection Restrictions

You can only connect to the database from the IP address or IP range specified when ordering the database. For example, if you requested the IP range to be Openshift, you can only connect to the database through applications running in Openshift.

If you need access to the database you can use this command to start a Pod in your namespace that will let you connect to the database using the psql-client

Connection String Format

The database connection string is in the following format:

postgres://<user>:<password>@<db-host>:5432/<database-name>?ssl=true

When defining the connection string, consider the following points:

• <user> is often the same as <database-name>. The correct value is usually mentioned in the confirmation email you receive when ordering the database.
• <database-name> is the name you requested for your database during the order process.
• <db-host> is possu.it.helsinki.fi in production and possu-test.it.helsinki.fi in the test environment.
• The database listens on port 5432.
• The database connection requires an SSL connection, so the parameter ssl=true must be included in the connection string.

Node.js Examples for Connection Strings

A good guide for using a PostgreSQL database in a Node.js application can be found in the node-postgres documentation. The easiest way to connect to the database is to use a Connection URI, which defines the entire connection string.

Example of a production connection string:

postgres://exampleapp:examplepassword1234@possu.it.helsinki.fi:5432/exampleapp?ssl=true

Example of a test environment connection string:

postgres://exampleapp:examplepassword1234@possu-test.it.helsinki.fi:5432/exampleapp?ssl=true

Java Examples for Connection Strings

If your application uses the Java Database Connectivity (JDBC) API to manage database connections, the connection string must start with the prefix jdbc. The connection strings are otherwise similar to the Node.js examples, but the beginning of the connection string is in the format:

jdbc:postgresql://...

Defining Nodes in the Connection String

The shared database operates on multiple nodes. If, for some reason, you want to specify the nodes to be used in the connection string, you can do so as follows:

jdbc:postgresql://possu-1-21.it.helsinki.fi:5432,possu-2-21.it.helsinki.fi:5432/KANTA?ssl=true&targetServerType=primary

Note that the parameter targetServerType=primary ensures that the connection is established to a node that allows write operations. Without this parameter, the connection might sometimes be established to a node that only allows read operations. This consideration is only necessary if the allowed nodes are defined as shown in this example.

Monthly Maintenance Breaks and Connection Impact

There is a maintenance break in the production cluster on the 2nd Wednesday of every month. During the break, it’s possible that some connections may temporarily fail.

In the test server, there is a maintenance break on the 2nd Tuesday of every month from 10:00 to 12:00. Connections to the test server may be disrupted for up to five minutes during server restart.

Backup

Backups of databases are taken daily onto tape. On the server, backups are kept for 7 days (for potentially quick restoration), while on tapes, they are kept for one month.

If you need restoration to a specific day, please contact the service address atk-tietokannat@helsinki.fi.