URL: https://it.desy.de/e5/e76922/e329231/e339469/index_eng.html
.
Breadcrumb Navigation
PostgreSQL databases for productive services
Contact
E-Mail: postgres.service@desy.de
General information
We offer the following setup for databases hosted by us.
PostgreSQL
Cluster
We provide the operation of a PostgreSQL cluster with a primary and a replica server.
- The primary server is used for read and write requests.
- The replica server can be used for read-only requests.
We do not provide logic for the distribution of write or read transactions. This must be implemented by the customer.
PostgreSQL Version
The currently supported versions of PostgreSQL can be found in the following table.
| PostgreSQL Version | Unterstuetzt |
|---|---|
| <= 14 | ❌ |
| 15 | ✔️ |
| 16 | ✔️ |
We will inform you in advance of an upcoming switch to a newer PostgreSQL version so that applications can be adjusted accordingly.
PostgreSQL configuration
Currently, the parameters of PostgreSQL correspond to the standard set for the version.
Customized parameters by us are:
shared_buffers
effective_cache_size
maintenance_work_mem
wal_buffers
effective_io_concurrency
work_mem
tcp_keepalives_count
tcp_keepalives_idle
tcp_keepalives_interval
Connection Pooling
We currently do not offer a solution for connection pooling.
This must be implemented by the customer.
The maximum connections correspond to the current standard (100).
Performance
Transactions Per Second
The following values show the maximum TPS (transactions per second) we can achieve.
The test was conducted under the following conditions:
- Usage of the tool "pgbench" to measure the possible TPS
- The size of the database was 1.5GB with ten million entries, created with pgbench (pgbench -i -s 100)
- pgbench was run on a VM within the internal network
- The connection to the database was via an F5 alias
- The result corresponds to the average of five minutes of the benchmark runtime
| Clients | TPS |
|---|---|
| 1 | 180 |
| 10 | 1400 |
| 50 | 1800 |
| 90 | 1600 |
Hosts
One VM for a PostgreSQL primary.
One VM for a PostgreSQL replica.
These run in the XEN virtualization environment.
Ubuntu is used as the operating system.
The VMs currently have 2 virtual CPUs and 8GB of RAM as standard.
Installation and configuration are managed via Puppet.
Customer login to the hosts is not permitted.
We will not install additional software requested by the customer on the hosts.
Connection
The connection to the database is exclusively via an F5 alias.
This ensures the connection to the current PostgreSQL primary server.
Failover
Our deployed software monitors the current state of the cluster.
In the event of a failure of the current primary server, an automatic failover occurs.
The replica server is switched to primary.
The failover usually takes place in under a minute.
During this time, connection interruptions may occur.
Response Times Outside Working Hours
Outside of normal working hours, we respond to failures on the next working day.
Encryption
The data transport path can be encrypted via SSL.
The server-side configuration is set up by default.
The client only needs to enable encryption.
Server-side data encryption is not currently implemented.
Authentication
We currently offer database authentication via basic auth (username + password).
It is also possible to offer certificate authentication. This must be done via GEANT certificates.
Limiting access to the database via host IPs is possible.
Backups
Automatic backups of the database are created and stored for 14 days.
We restore data from the backup.
Monitoring
Server, database, and backups are monitored with the Icinga 2 service by the IT Monitoring team.
Logging
PostgreSQL logs are stored and sent to the central IT-Elasticsearch cluster.
Access to the log files can be provided there.
Our Responsibilities
We currently take care of the following tasks:
- Condition of the virtual machines
- Condition of the database
- Setup of database users
- Restoration of backups
- Informing the customer about SQL errors that could cause DB outages
- Updating certificates for encrypted transport
- Applying PostgreSQL updates (minor and major upgrades)
Your Responsibilities
The following tasks are currently the customer's responsibility:
- Migrating data from other database solutions
- Resolving SQL application errors
- Ensuring compatibility with newer PostgreSQL versions
- Providing a maintenance window per quarter for system and software maintenance (times when the database will not be accessible)
- Promptly providing maintenance windows for critical security updates
If you wish to access the database from the DMZ, you must request a firewall clearance from IT-NOC. Please contact noc@desy.de by email.
If personal data is stored/processed in the database, you must inform the D4 group.
For more information, visit https://datenschutz.desy.de
Contact D4: https://datenschutz.desy.de/kontakt/index_ger.html
In case of changes to our services, we will inform you.
