PostgreSQL

Overview

PostgreSQL is an object-relational database management system (ORDBMS) with an emphasis on extensibility and on standards-compliance.

Supported Versions

DedicatedDev
v11.22v15
v12.19
v13.15
v14.12
v15.7
⚠️
Important: Dev plans are free and solely for testing purposes. They don’t provide the same guarantees or SLAs as dedicated plans.

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:

  1. WebGUI (Adminer)
  2. Command line tool for PostgreSQL administration like psql
  3. 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
pgvectorVector 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_geocoderPostGIS 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:

ExtensionDescription
pg_cronJob scheduler for PostgreSQL
pg_ivmIncremental view maintenance for PostgreSQL
pgtapUnit testing for PostgreSQL
timescaledbEnables scalable inserts and complex queries for time-series data (Apache 2 Edition)
⚠️
On-demand extensions aren’t available for DEV plans.

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.

Last updated on