PostgreSQL
Overview
PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and on standards-compliance.
Supported Versions
| Dedicated | Dev |
|---|---|
v14.23 | v15.18 |
v15.18 | |
v16.14 | |
v17.10 | |
v18.4 |
Database Daily Backup and Retention
By default, Clever Cloud performs a free backup every day, with a retention of seven days. Retention and frequency can be customized by contacting Clever Cloud Support.
Each backup can be found in the add-on dashboard in the web console, along with the credentials.
Point In Time Recovery
The PostgreSQL add-on supports pgBackRest, enabling incremental backups and point-in-time data restoration. It’s not installed by default on a PostgreSQL add-on, if you need it, you can contact Clever Cloud Support.
Migrating from an old database
Some applications require a non-empty database to run properly. If you want to import your SQL dump, you can use several methods:
- WebGUI (Adminer)
- Command line tool for PostgreSQL administration like
psql - Any PostgreSQL client such as pgAdmin
Migrate your database
Clever Cloud provides an add-on Migration/Upgrade tool. You can access it from the Console, in the left menu when an add-on is selected. It allows to choose a higher plan, a new version or another deployment zone.
A migration process creates new instances, moves your data into it and stops the old ones if the process ended correctly. In case of a failure during migration, new instances are deleted and you go back to the original ones. The duration may vary depending on how much data your add-on has. Your database becomes read only for the entire duration.
If you want to restart your add-on or update to the last supported version of the current branch, migrate it to the same plan, version, zone.
Replication
You can add up to 2 replicas to an existing PostgreSQL (v12 or higher) database on Clever Cloud to enhance performance and reliability. They’re set up as read-only standby servers with full physical replication, in a different availability zone (AZ) or region if requested.
You can see if a database is a primary server or a replica from PostgreSQL dashboard in the Console. If a primary server isn’t available, you can promote a replica as a standalone server and link it to applications. This database will:
- Become a primary server (using
pg_ctl promotecommand internally) with read/write capabilities - Delete its replication link to the original primary server
Replication setup isn’t yet available from API or the Console
To create and configure PostgreSQL replicas, contact your sales representative or Clever Cloud support.
Direct access
A proxy serves all dedicated PostgreSQL databases. In some cases, this can add some latency between applications and their database. If this is an issue, you can generate a direct hostname and port for the add-on to bypass the proxy, using the “Generate direct hostname and port” button in the add-on dashboard.
Generating direct access adds new variables to the add-on’s environment, allowing connections without going through the proxy.
Direct access doesn’t persist after migrating a database. Manually re-generate direct host name and port after a migration to allow applications linked through direct access to connect to the database.
Such a limitation doesn’t exist when using proxy access.
Encryption at rest
Encryption at rest is available on PostgreSQL. You can find more information on the dedicated page
Note on shared databases
If you try to list the databases on the shared cluster, you will see the names of the databases of all other users of the cluster. This is normal behaviour. Rest assured, permissions are set properly, meaning no one but your user can read or write data to your database.
This referencing does not exist for dedicated databases.
Pgpool-II
Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.
You can read the official project page for more information.
You can learn more about Pgpool-II on the dedicated documentation page
Default extensions
PostgreSQL databases managed by Clever Cloud comes with these extensions:
| Extension | Description |
|---|---|
| adminpack | Administrative functions for PostgreSQL (not supported on PostgreSQL 17+) |
| autoinc | Functions for autoincrementing fields |
| btree_gin | Support for indexing common datatypes in GIN |
| btree_gist | Support for indexing common datatypes in GiST |
| citext | Data type for case-insensitive character strings |
| cube | Data type for multidimensional cubes |
| dblink | Connect to other PostgreSQL databases from within a database |
| dict_int | Text search dictionary template for integers |
| dict_xsyn | Text search dictionary template for extended synonym processing |
| earthdistance | Calculate great-circle distances on the surface of the Earth |
| file_fdw | Foreign-data wrapper for flat file access |
| fuzzystrmatch | Determine similarities and distance between strings |
| hstore | Data type for storing sets of (key, value) pairs |
| hypopg | Hypothetical indexes for PostgreSQL |
| insert_username | Functions for tracking who changed a table |
| intagg | Integer aggregator and enumerator (obsolete) |
| intarray | Functions, operators, and index support for 1-D arrays of integers |
| isn | Data types for international product numbering standards |
| lo | Large Object maintenance |
| ltree | Data type for hierarchical tree-like structures |
| moddatetime | Functions for tracking last modification time |
| pageinspect | Inspect the contents of database pages at a low level |
| pg_buffercache | Examine the shared buffer cache |
| pg_freespacemap | Examine the free space map (FSM) |
| pg_stat_statements | Track planning and execution statistics of all SQL statements executed |
| pg_trgm | Text similarity measurement and index searching based on trigrams |
| pgcrypto | Cryptographic functions |
| pgrowlocks | Show row-level locking information |
| pgstattuple | Show tuple-level statistics |
| pgvector | Vector data type and ivfflat and hnsw access methods |
| plcoffee | PL/CoffeeScript (v8) trusted procedural language (not supported on PostgreSQL 14+) |
| plls | PL/LiveScript (v8) trusted procedural language (not supported on PostgreSQL 14+) |
| plpgsql | PL/pgSQL procedural language |
| plv8 | PL/JavaScript (v8) trusted procedural language (not supported on PostgreSQL 16+) |
| postgis | PostGIS geometry and geography spatial types and functions |
| postgis_raster | PostGIS raster types and functions |
| postgis_tiger_geocoder | PostGIS tiger geocoder and reverse geocoder |
| postgis_topology | PostGIS topology spatial types and functions |
| postgres_fdw | Foreign-data wrapper for remote PostgreSQL servers |
| refint | Functions for implementing referential integrity (obsolete) |
| seg | Data type for representing line segments or floating-point intervals |
| sslinfo | Information about SSL certificates |
| tablefunc | Functions that manipulate whole tables, including crosstab |
| tcn | Triggered change notifications |
| unaccent | Text search dictionary that removes accents |
| uuid-ossp | Generate universally unique identifiers (UUIDs) |
| xml2 | XPath querying and XSLT |
On-demand extensions
In the Console’s Ticket Center, you can ask our support team to add any of these extensions for you:
| Extension | Description |
|---|---|
| pg_cron | Job scheduler for PostgreSQL |
| pg_ivm | Incremental view maintenance for PostgreSQL |
| pg_net | Enables asynchronous (non-blocking) HTTP/HTTPS requests with SQL |
| pg_partman | Extension to manage partitioned tables by time or ID |
| pg_repack | Reorganize tables in PostgreSQL databases with minimal locks |
| pgaudit | Provides detailed session and/or object audit logging via the standard PostgreSQL logging facility |
| pgsql-http | HTTP client for PostgreSQL |
| pgtap | Unit testing for PostgreSQL |
| rum | Provides an access method to work with a RUM index |
| timescaledb | Enables scalable inserts and complex queries for time-series data (Apache 2 Edition) |
Automatic vacuuming
Autovacuum is automatically enabled on PostgreSQL add-ons. The autovacuum will proceed when a given percentage of rows of a table will be updated/inserted/deleted. Usually this threshold is set to 20%.
pg_activity
If you want to use pg_activity on a PostgreSQL add-on, but you encounter the following error Exception: Must be run with database superuser privileges., you need to add the --rds flag when you start it.
🔑 Rights and permissions
Clever Cloud configures and maintains the PostgreSQL server. You have standard access to the database through the owner role, but some administrative operations and server settings aren’t available directly. This ensures optimal performance and security.
Authorized actions:
- Manage tables (create, delete…).
- Manage schemas.
- Manage indexes.
- Access information from pg_catalog (except pg_database on DEV plan).
- Access to basic maintenance operations such as VACUUM and ANALYZE.
The following actions aren’t available directly:
- Database administration (for example you won’t be able to create new databases).
- Users administration (you won’t be able to create other users than the one handled with our control plane, i.e. the base owner and read-only users).
- Server configuration update.
- Extensions installation.
- Replica creation.
- Backup frequency or retention control.
If your use case requires specific server parameters or one of these restricted operations, contact Clever Cloud support to discuss feasibility.
Did this documentation help you ?