PostgreSQL

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

Versions#

We currently support PostgreSQL versions 15, 14, 13 and 12.

We regularly update our service templates to the latest point releases and try to offer new major releases of as soon as possible.

Performance Classes#

In order to fulfill your requirements for your PostgreSQL workloads, we offer four performance classes - Standard (max. 4GB RAM), High (max. 16GB RAM), Insane (max. 32GB RAM) and Ultra (max. 64GB RAM).

CPU Limits & Autoscaling#

Our autoscaling Platform Services will scale according to the load of your application.

The minimum CPU usage is 1, and the maximum is 32. The maximum CPU cores available can be set anywhere in between, and the database will scale up to the maximum cores you set, if nothing is set then the 32 core limit will be used.

The amount of cores in use can be easily seen in the metrics of the running Platform Service.

Storage Limits & Autoscaling#

Our autoscaling Platform Services will scale according to the load of your application. The maximum possible storage is 1TB. The current usage can been seen within the platform services metrics, and previous accumulated usage can bee seen within the Usage Dashboard.

Service Access#

Once your PaaS service has been successfully created you can have the possibility to view the Service Access section within the detailed view for your service.

You have the option to include the credentials, password and/or the port. Using this section you can easily set, generate and copy several variants:

• Connection String
• Connection Parameters
• Flags
• PHP Data Object (PDO)
• Data Source Name (DSN)

PostgreSQL Extensions#

Postgres comes with a host of default extensions. In addition to this set, we have added a few extensions that have been requested frequently and to provide certain features. To see the list of all extensions currently available on your Platform Service, execute the following SQL statement:

SELECT * FROM pg_available_extensions;

Additional Extensions#

Aside from the default list, we added these additional extensions:

• pgaudit and pgauditlogtofile. These are available as part of the pgaudit feature.
• postgis
• pgvector

Unless further explained every extension is either already active or can be activated on demand.

pgaudit#

pgaudit needs a few service parameters to be activated. The required parameters are:

If all of these parameters exist when updating or creating a Platform Service the necessary database statements to activate the pgaudit extensions are executed automatically.

There are a lot of ways to tune the logging parameters. However this is not done via service parameters, but via the ALTER SYSTEM facility that is provided by postgres itself. To see a list of current parameters and their values run this statement:

SELECT name, setting, unit, context, vartype, source FROM pg_settings WHERE name LIKE 'pgaudit%';"

The ALTER SYSTEM facility in postgres can be used like this:

ALTER SYSTEM SET pgaudit.log TO 'WRITE,DDL';

This specific command would instruct pgaudit to log only WRITE and DDL statements (like INSERT, UPDATE, ALTER TABLE, etc.). After executing any ALTER SYSTEM statements the database server needs to reload its configuration, which can be achieved by issuing the following statement:

SELECT pg_reload_conf();

This way any pgaudit setting can be changed, even when pgaudit is disabled temporarily; the settings will persist. More documentation on all the settings and the extensions itself can be found on the projects own website (documentation).

Logs from pgaudit are stored in an Object Storage. The access parameters to it are required to activate the plugin.

Once the pgaudit_log_rotation_frequency has been reached, the log file, at this point residing in local storage and so far unavailable externally, will be moved to the Object Storage.

Once the upload is complete it will be removed from internal storage and available for download.

Creating A WORM Compatible Bucket With Object Expiration#

WORM (write-once, read-many) compliant storage can be created via the S3-API. WORM-compliance needs to be enabled when creating the bucket. Be aware that WORM protected files cannot be deleted until the WORM duration has expired.

Setting it to 3650d means that objects will remain in the bucket for at least 10 years. During that time the bucket cannot be deleted either.

Here’s an example on how to create the bucket with the open source minio-client (mc) command line utility. It will setup a new, WORM-enabled bucket with a 90 day retention period and an expiration policy of 90 days:

# Set up the gridscale object storage connection with mc
mc alias set gridscale https://gos3.io ACCESSKEY SECRETKEY

# Create a new WORM-compliant bucket
mc mb --with-lock gridscale/postgres-logs

# Set the desired default WORM duration for objects to 90 days
mc retention set --default COMPLIANCE 90d gridscale/postgres-logs

# Activate the object expiration policy
mc ilm add --expiry-days 90 gridscale/postgres-logs

This setup will prevent any change to objects in this bucket for 90 days and remove files after that time expires. See the respective documentation about WORM-compatible buckets (S3 Object Lock) and the minio command line client (mc) for more details. There’s more documentation on object lock configuration over here.