PostgreSQL
Overview
PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and on standards-compliance.
Supported Versions
| Dedicated | Dev |
|---|---|
v11.22 | v15 |
v12.19 | |
v13.15 | |
v14.12 | |
v15.7 |
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 for Premium customers.
Each backup can be found in the add-on dashboard in the web console, along with the credentials.
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
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 |
| 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 |
| plls | PL/LiveScript (v8) trusted procedural language |
| plpgsql | PL/pgSQL procedural language |
| plv8 | PL/JavaScript (v8) trusted procedural language |
| 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 |
| pgtap | Unit testing for PostgreSQL |
| 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
Add-ons are managed services, meaning that users have standard access to the database (role owner). Some operations like databases and users creation, as well as some settings modifications aren’t available by default. This ensures optimal performances and security for managed services as configured by Clever Cloud.
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.
If you think your system might require more advanced administrative access, contact Clever Cloud support to explain your use case, and we will work with you to find a solution.
Here is the list of actions that you won’t be able to perform:
- 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.
- Back-up frequency or retention control.
Ask Clever Cloud support if you want to perform one of these actions.