# PostgreSQL - Features

## Architecture <a href="#postgresqlarchitecture-architecture" id="postgresqlarchitecture-architecture"></a>

### Supported versions <a href="#postgresqlarchitecture-supportedversions" id="postgresqlarchitecture-supportedversions"></a>

Currently supported versions of PostgreSQL are : 10, 11, 12, 13, 14, 15, 16.

### Upgrade <a href="#postgresqlarchitecture-upgrade" id="postgresqlarchitecture-upgrade"></a>

To upgrade your PaaS PostgreSQL, please refer to this page: [postgresql-upgrade](https://academy.cegedim.cloud/databases/postgresql/postgresql-upgrade "mention")

### Topologies <a href="#postgresqlarchitecture-topologies" id="postgresqlarchitecture-topologies"></a>

cegedim.cloud supports two types of PostgreSQL deployments :

* **Single Instance** mode is providing a standard PostgreSQL instance
* **High Availability** is providing a multi-instances PostgreSQL instance, with improved resilience and scalability capabilities

### Regions <a href="#postgresqlarchitecture-regions" id="postgresqlarchitecture-regions"></a>

PostgreSQL is available on both cegedim.cloud's data center :

* EB4 (Boulogne-Billancourt, France)
* ET1 (Labège, France)

In some cases, when a second node is deployed (High availability), a secondary close by data center can also be used to ensure maximum resiliency :

* EB5 (Magny-les-Hameaux, France)
* ET2 (Balma, France)

### Resiliency <a href="#postgresqlarchitecture-resiliency" id="postgresqlarchitecture-resiliency"></a>

For High availability topology the PaaS is built to be DC resilient if it is possible.

Following , a sample of nodes placement:

<figure><picture><source srcset="https://835168969-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F2FXoHyOBZPpJv3UALn4V%2Fuploads%2Fgit-blob-1a6c2f7a5e21e9c91f58ecd38bfb7781aa9cbba2%2Fdark_eng%20(2).png?alt=media" media="(prefers-color-scheme: dark)"><img src="https://835168969-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F2FXoHyOBZPpJv3UALn4V%2Fuploads%2Fgit-blob-19cb760ba9983a88fe3fccb6e6cf07ea237c70ac%2Flight_eng%20(2).png?alt=media" alt="" width="525"></picture><figcaption><p>High availability topology</p></figcaption></figure>

### High Availability Diagram <a href="#postgresqlarchitecture-highavailabilitydiagram" id="postgresqlarchitecture-highavailabilitydiagram"></a>

<figure><picture><source srcset="https://835168969-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F2FXoHyOBZPpJv3UALn4V%2Fuploads%2Fgit-blob-eb922a664cda35c37da45dea4e8dbac79de48142%2Fdark_eng%20(16).png?alt=media" media="(prefers-color-scheme: dark)"><img src="https://835168969-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F2FXoHyOBZPpJv3UALn4V%2Fuploads%2Fgit-blob-43eb6e8c04aca298643e25866f94b287eb3632a3%2FPostgreSQL_light_eng%20(3).png?alt=media" alt="" width="563"></picture><figcaption><p>High availability diagram</p></figcaption></figure>

### Features <a href="#postgresqlarchitecture-features" id="postgresqlarchitecture-features"></a>

This section is to list which feature / capabilities are available to customer, and how to request / perform them :

<table data-header-hidden><thead><tr><th width="174"></th><th></th></tr></thead><tbody><tr><td><strong>Self Service</strong></td><td>Customer can perform action autonomously.</td></tr><tr><td><strong>On Request</strong></td><td>Customer can request for the action to be done to cegedim.cloud support team.</td></tr></tbody></table>

<table data-full-width="true"><thead><tr><th width="254">Feature</th><th width="155" data-type="checkbox">Self-service</th><th width="148" data-type="checkbox">On request</th><th>Comments</th></tr></thead><tbody><tr><td>SSH access</td><td>false</td><td>false</td><td>SSH access is disabled and reserved to cegedim.cloud administrators.</td></tr><tr><td>Change configuration file</td><td>false</td><td>true</td><td>On request via ticket. Only possible if it doesn't affect monitoring and resilience.</td></tr><tr><td>Install extension</td><td>true</td><td>false</td><td>PostgreSQL extensions can now be installed in self service using ITCare provided your deployment is in version 15 or higher. Otherwise, request ticket still applies.</td></tr></tbody></table>

## Extensions

It's possible to add functionality to PostgreSQL through so-called extensions. These extensions can add new types, additional functions for administrators and "classic" users alike, or even complete applications.

Some of these extensions are developed within the PostgreSQL project itself, so they keep pace with the evolution of the various PostgreSQL versions. You can find a list here. Others are developed by third-party companies and follow their own pace, like [Timescaledb](https://docs.timescale.com/) or [Postgis](https://postgis.net/), to name but the best-known.

Once the PostgreSQL PaaS has been provisioned, you can install some of these extensions through ITCare. Below is the list of extensions supported by PostgreSQL PaaS from version 15 onwards:

* [btree\_gist](https://www.postgresql.org/docs/15/contrib.html)
* [citext](https://www.postgresql.org/docs/15/contrib.html)
* [fuzzystrmatch](https://www.postgresql.org/docs/15/contrib.html)
* [hstore](https://www.postgresql.org/docs/15/contrib.html)
* [pg\_trgm](https://www.postgresql.org/docs/15/contrib.html)
* [pgcrypto](https://www.postgresql.org/docs/15/contrib.html)
* [postgis](https://postgis.net/)
* [tablefunc](https://www.postgresql.org/docs/15/contrib.html)
* [timescaledb](https://docs.timescale.com/)
* [unaccent](https://www.postgresql.org/docs/15/contrib.html)
* [uuid-ossp](https://www.postgresql.org/docs/15/contrib.html)
* [vector](https://github.com/pgvector/pgvector)
* [postgres\_fdw](https://www.postgresql.org/docs/15/contrib.html)
* [oracle\_fdw](https://github.com/laurenz/oracle_fdw)

Please note that the installation of certain extensions may require a restart of PostgreSQL and therefore cause your PostgreSQL PaaS to be unavailable.

## Security <a href="#postgresqlarchitecture-security" id="postgresqlarchitecture-security"></a>

### Authentication <a href="#postgresqlarchitecture-authentication" id="postgresqlarchitecture-authentication"></a>

Customer is provided with a role whom he chooses the password.

The password of this user is not stored nor saved by cegedim.cloud. Please be sure to save it in your own vault.

### Authorizations <a href="#postgresqlarchitecture-authorizations" id="postgresqlarchitecture-authorizations"></a>

The role provided to the customer has the following authorizations:

* LOGIN
* CREATEROLE
* CREATEDB

So, the customer may create dedicated application role and databases.

### Secured Transport <a href="#postgresqlarchitecture-securedtransport" id="postgresqlarchitecture-securedtransport"></a>

Secured transport is an option while provisioning and is available only from version 13 and above.

If secured transport is selected, TLS/SSL will be enabled for the PostgreSQL protocol and only a TLS connection from the clients will be accepted.

### Data location <a href="#postgresqlarchitecture-datalocation" id="postgresqlarchitecture-datalocation"></a>

All datas are stored in cegedim.cloud data centers on encrypted storage arrays.

### Passwords <a href="#postgresqlarchitecture-passwords" id="postgresqlarchitecture-passwords"></a>

This section list the password management :

<table data-full-width="true"><thead><tr><th width="239">Passwords</th><th width="217" data-type="checkbox">Stored by cegedim.cloud</th><th width="184" data-type="checkbox">Stored by Customer</th><th width="100" data-type="checkbox">Enforced</th><th>Hashing algorithm</th></tr></thead><tbody><tr><td><strong>dedicated customer</strong> account</td><td>false</td><td>true</td><td>false</td><td>SCRAM-SHA-256</td></tr><tr><td><strong>ANY</strong> other account</td><td>false</td><td>true</td><td>false</td><td>SCRAM-SHA-256</td></tr><tr><td><strong>cegedim.cloud</strong> account</td><td>true</td><td>false</td><td>true</td><td>SCRAM-SHA-256</td></tr><tr><td><strong>monitoring</strong> account</td><td>true</td><td>false</td><td>true</td><td>SCRAM-SHA-256</td></tr></tbody></table>

## Backup <a href="#postgresqlarchitecture-backup" id="postgresqlarchitecture-backup"></a>

If backup is enabled during provisioning (enabled by default for a Service of Production type), the following backup policies will apply :

<table data-full-width="true"><thead><tr><th>PostgreSQL version 11 and lower</th><th>PostgreSQL version 12 and higher</th></tr></thead><tbody><tr><td>Full dump every day retained for 14 days on Object Storage</td><td><ul><li>Full backup once a week.</li><li>Differential backups in between.</li><li>Write-ahead (WAL) logs are archived.</li></ul><p>Point-in-Time recovery is supported for 14 days on Object Storage</p></td></tr></tbody></table>

## Monitoring <a href="#postgresqlarchitecture-monitoring" id="postgresqlarchitecture-monitoring"></a>

As part of our Managed Databases offer, PostgreSQL is specifically monitored on top of the underlying system to ensure service uptime and performances.

The following key PostgreSQL indicators are monitored and tracked :

* Connections
* Memory usage
* Transaction id wrapparround
* Health status