PostgreSQL (Database service)

PostgreSQL is a high-performance, standards-compliant relational SQL database.

See the PostgreSQL documentation for more information.

Use a framework

If you use one of the following frameworks, follow its guide:

• Hibernate
• Jakarta EE
• Spring

Supported versions

* No High-Availability on Dedicated Gen 2.

Deprecated versions

The following versions are deprecated. They’re available, but they aren’t receiving security updates from upstream and aren’t guaranteed to work. They’ll be removed in the future, so migrate to one of the supported versions.

Relationship reference

Example information available through the PLATFORM_RELATIONSHIPS environment variable or by running platform relationships.

Note that the information about the relationship can change when an app is redeployed or restarted or the relationship is changed. So your apps should only rely on the PLATFORM_RELATIONSHIPS environment variable directly rather than hard coding any values.

{
    "username": "main",
    "scheme": "pgsql",
    "service": "postgresql",
    "fragment": null,
    "ip": "123.456.78.90",
    "hostname": "azertyuiopqsdfghjklm.postgresql.service._.eu-1.platformsh.site",
    "port": 5432,
    "cluster": "azertyuiopqsdf-main-afdwftq",
    "host": "postgresql.internal",
    "rel": "postgresql",
    "path": "main",
    "query": {
        "is_master": true
    },
    "password": "ChangeMe",
    "type": "postgresql:16",
    "public": false,
    "host_mapped": false
}

Usage example

1. Configure the service

To define the service, use the postgresql type:

# The name of the service container. Must be unique within a project.
<SERVICE_NAME>:
    type: postgresql:<VERSION>
    disk: 256

Note that changing the name of the service replaces it with a brand new service and all existing data is lost. Back up your data before changing the service.

2. Add the relationship

To define the relationship, use the following configuration:

# Relationships enable access from this app to a given service.
# The example below shows simplified configuration leveraging a default service
# (identified from the relationship name) and a default endpoint.
# See the Application reference for all options for defining relationships and endpoints.
relationships:
    <SERVICE_NAME>: 

You can define <SERVICE_NAME> as you like, so long as it’s unique between all defined services and matches in both the application and services configuration.

The example above leverages default endpoint configuration for relationships. That is, it uses default endpoints behind-the-scenes, providing a relationship (the network address a service is accessible from) that is identical to the name of that service.

Depending on your needs, instead of default endpoint configuration, you can use explicit endpoint configuration.

With the above definition, the application container now has access to the service via the relationship <RELATIONSHIP_NAME> and its corresponding PLATFORM_RELATIONSHIPS environment variable.

For PHP, enable the extension for the service:

# PHP extensions.
runtime:
    extensions:
        - pdo_pgsql

Example configuration

Service definition

# The name of the service container. Must be unique within a project.
postgresql:
    type: postgresql:16
    disk: 256

App configuration

# Relationships enable access from this app to a given service.
# The example below shows simplified configuration leveraging a default service
# (identified from the relationship name) and a default endpoint.
# See the Application reference for all options for defining relationships and endpoints.
relationships:
   postgresql: 

Use in app

To use the configured service in your app, add a configuration file similar to the following to your project.

package examples

import (
	"database/sql"
	"fmt"

	_ "github.com/lib/pq"
	psh "github.com/platformsh/config-reader-go/v2"
	libpq "github.com/platformsh/config-reader-go/v2/libpq"
)

func UsageExamplePostgreSQL() string {

	// Create a NewRuntimeConfig object to ease reading the Platform.sh environment variables.
	// You can alternatively use os.Getenv() yourself.
	config, err := psh.NewRuntimeConfig()
	checkErr(err)

	// The 'database' relationship is generally the name of the primary SQL database of an application.
	// It could be anything, though, as in the case here where it's called "postgresql".
	credentials, err := config.Credentials("postgresql")
	checkErr(err)

	// Retrieve the formatted credentials.
	formatted, err := libpq.FormattedCredentials(credentials)
	checkErr(err)

	// Connect.
	db, err := sql.Open("postgres", formatted)
	checkErr(err)

	defer db.Close()

	// Creating a table.
	sqlCreate := "CREATE TABLE IF NOT EXISTS PeopleGo (" +
		"id SERIAL PRIMARY KEY," +
		"name VARCHAR(30) NOT NULL," +
		"city VARCHAR(30) NOT NULL);"

	_, err = db.Exec(sqlCreate)
	checkErr(err)

	// Insert data.
	sqlInsert := "INSERT INTO PeopleGo(name, city) VALUES" +
		"('Neil Armstrong', 'Moon')," +
		"('Buzz Aldrin', 'Glen Ridge')," +
		"('Sally Ride', 'La Jolla');"

	_, err = db.Exec(sqlInsert)
	checkErr(err)

	table := "<table>" +
		"<thead>" +
		"<tr><th>Name</th><th>City</th></tr>" +
		"</thead>" +
		"<tbody>"

	var id int
	var name string
	var city string

	// Read it back.
	rows, err := db.Query("SELECT * FROM PeopleGo")
	if err != nil {
		panic(err)
	} else {
		for rows.Next() {
			err = rows.Scan(&id, &name, &city)
			checkErr(err)
			table += fmt.Sprintf("<tr><td>%s</td><td>%s</td><tr>\n", name, city)
		}
		table += "</tbody>\n</table>\n"
	}

	_, err = db.Exec("DROP TABLE PeopleGo;")
	checkErr(err)

	return table
}

package sh.platform.languages.sample;

import sh.platform.config.Config;
import sh.platform.config.MySQL;
import sh.platform.config.PostgreSQL;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.function.Supplier;

public class PostgreSQLSample implements Supplier<String> {

    @Override
    public String get() {
        StringBuilder logger = new StringBuilder();

        // Create a new config object to ease reading the Platform.sh environment variables.
        // You can alternatively use getenv() yourself.
        Config config = new Config();

        // The 'database' relationship is generally the name of primary SQL database of an application.
        // It could be anything, though, as in the case here here where it's called "postgresql".
        PostgreSQL database = config.getCredential("postgresql", PostgreSQL::new);
        DataSource dataSource = database.get();

        // Connect to the database
        try (Connection connection = dataSource.getConnection()) {

            // Creating a table.
            String sql = "CREATE TABLE JAVA_FRAMEWORKS (" +
                    " id SERIAL PRIMARY KEY," +
                    "name VARCHAR(30) NOT NULL)";

            final Statement statement = connection.createStatement();
            statement.execute(sql);

            // Insert data.
            sql = "INSERT INTO JAVA_FRAMEWORKS (name) VALUES" +
                    "('Spring')," +
                    "('Jakarta EE')," +
                    "('Eclipse JNoSQL')";

            statement.execute(sql);

            // Show table.
            sql = "SELECT * FROM JAVA_FRAMEWORKS";
            final ResultSet resultSet = statement.executeQuery(sql);
            logger.append("<ul>");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                logger.append(String.format("<li>id <code>%d</code> has a name of <code>%s</code>", id, name));
                logger.append("</li>");
            }
            logger.append("</ul>");
            statement.execute("DROP TABLE JAVA_FRAMEWORKS");
            return logger.toString();
        } catch (SQLException exp) {
            throw new RuntimeException("An error when execute PostgreSQL", exp);
        }
    }
}

const pg = require("pg");
const config = require("platformsh-config").config();

exports.usageExample = async function () {
    const credentials = config.credentials("postgresql");

    const client = new pg.Client({
        host: credentials.host,
        port: credentials.port,
        user: credentials.username,
        password: credentials.password,
        database: credentials.path,
    });

    client.connect();

    // Creating a table.
    await client.query(
        `CREATE TABLE IF NOT EXISTS People (
            id SERIAL PRIMARY KEY,
            name VARCHAR(30) NOT NULL,
            city VARCHAR(30) NOT NULL
        )`
    );

    // Insert data.
    await client.query(
        `INSERT INTO People (name, city)
        VALUES
            ('Neil Armstrong', 'Moon'),
            ('Buzz Aldrin', 'Glen Ridge'),
            ('Sally Ride', 'La Jolla');`
    );

    // Show table.
    const result = await client.query("SELECT * FROM People");

    // Drop table.
    await client.query("DROP TABLE People");

    const outputRows = result.rows
        .map(({ name, city }) => `<tr><td>${name}</td><td>${city}</td></tr>\n`)
        .join("\n");

    return `
    <table>
        <thead>
            <tr>
                <th>Name</th><th>City</th>
            </tr>
        </thhead>
        <tbody>
            ${outputRows}
        </tbody>
    </table>
    `;
};

<?php

declare(strict_types=1);

use Platformsh\ConfigReader\Config;

// Create a new config object to ease reading the Platform.sh environment variables.
// You can alternatively use getenv() yourself.
$config = new Config();

// The 'database' relationship is generally the name of primary SQL database of an application.
// It could be anything, though, as in the case here here where it's called "postgresql".
$credentials = $config->credentials('postgresql');

try {
    // Connect to the database using PDO.  If using some other abstraction layer you would
    // inject the values from $database into whatever your abstraction layer asks for.
    $dsn = sprintf('pgsql:host=%s;port=%d;dbname=%s', $credentials['host'], $credentials['port'], $credentials['path']);
    $conn = new \PDO($dsn, $credentials['username'], $credentials['password'], [
        // Always use Exception error mode with PDO, as it's more reliable.
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    ]);

    $conn->query("DROP TABLE IF EXISTS People");

    // Creating a table.
    $sql = "CREATE TABLE IF NOT EXISTS People (
      id SERIAL PRIMARY KEY,
      name VARCHAR(30) NOT NULL,
      city VARCHAR(30) NOT NULL
      )";
    $conn->query($sql);

    // Insert data.
    $sql = "INSERT INTO People (name, city) VALUES
        ('Neil Armstrong', 'Moon'),
        ('Buzz Aldrin', 'Glen Ridge'),
        ('Sally Ride', 'La Jolla');";
    $conn->query($sql);

    // Show table.
    $sql = "SELECT * FROM People";
    $result = $conn->query($sql);
    $result->setFetchMode(\PDO::FETCH_OBJ);

    if ($result) {
        print <<<TABLE
<table>
<thead>
<tr><th>Name</th><th>City</th></tr>
</thead>
<tbody>
TABLE;
        foreach ($result as $record) {
            printf("<tr><td>%s</td><td>%s</td></tr>\n", $record->name, $record->city);
        }
        print "</tbody>\n</table>\n";
    }

    // Drop table.
    $sql = "DROP TABLE People";
    $conn->query($sql);

} catch (\Exception $e) {
    print $e->getMessage();
}

import psycopg2
from platformshconfig import Config


def usage_example():
    # Create a new Config object to ease reading the Platform.sh environment variables.
    # You can alternatively use os.environ yourself.
    config = Config()

    # The 'database' relationship is generally the name of primary SQL database of an application.
    # That's not required, but much of our default automation code assumes it.' \
    database = config.credentials('postgresql')

    try:
        # Connect to the database.
        conn_params = {
            'host': database['host'],
            'port': database['port'],
            'dbname': database['path'],
            'user': database['username'],
            'password': database['password']
        }

        conn = psycopg2.connect(**conn_params)

        # Open a cursor to perform database operations.
        cur = conn.cursor()

        cur.execute("DROP TABLE IF EXISTS People")

        # Creating a table.
        sql = '''
                CREATE TABLE IF NOT EXISTS People (
                id SERIAL PRIMARY KEY,
                name VARCHAR(30) NOT NULL,
                city VARCHAR(30) NOT NULL
                )
                '''

        cur.execute(sql)

        # Insert data.
        sql = '''
                INSERT INTO People (name, city) VALUES
                ('Neil Armstrong', 'Moon'),
                ('Buzz Aldrin', 'Glen Ridge'),
                ('Sally Ride', 'La Jolla');
                '''

        cur.execute(sql)

        # Show table.
        sql = '''SELECT * FROM People'''
        cur.execute(sql)
        result = cur.fetchall()

        table = '''<table>
<thead>
<tr><th>Name</th><th>City</th></tr>
</thead>
<tbody>'''

        if result:
            for record in result:
                table += '''<tr><td>{0}</td><td>{1}</td><tr>\n'''.format(record[1], record[2])
            table += '''</tbody>\n</table>\n'''

        # Drop table
        sql = "DROP TABLE People"
        cur.execute(sql)

        # Close communication with the database
        cur.close()
        conn.close()

        return table

    except Exception as e:
        return e

Access the service directly

Access the service using the Platform.sh CLI by running platform sql.

You can also access it from your app container via SSH. From your relationship data, you need: username, host, and port. Then run the following command:

psql -U USERNAME -h HOST -p PORT

Using the values from the example, that would be:

psql -U main -h postgresql.internal -p 5432

Note that the information about the relationship can change when an app is redeployed or restarted or the relationship is changed. So your apps should only rely on the PLATFORM_RELATIONSHIPS environment variable directly rather than hard coding any values.

Exporting data

The easiest way to download all data in a PostgreSQL instance is with the Platform.sh CLI. If you have a single SQL database, the following command exports all data using the pg_dump command to a local file:

platform db:dump

If you have multiple SQL databases it prompts you which one to export. You can also specify one by relationship name explicitly:

platform db:dump --relationship postgresql

By default the file is uncompressed. If you want to compress it, use the --gzip (-z) option:

platform db:dump --gzip

You can use the --stdout option to pipe the result to another command. For example, if you want to create a bzip2-compressed file, you can run:

platform db:dump --stdout | bzip2 > dump.sql.bz2

It is also possible to generate the dump locally if you have the pg_dump command installed with platform tunnel:single. The command will first ask for the service and then will provide a prompt for the URI string that you can use. For example:

pg_dump -d postgresql://REPLACE_URI_FROM_OUTPUT -f dump.sql

Importing data

Make sure that the imported file contains objects with cleared ownership and IF EXISTS clauses. For example, you can create a DB dump with following parameters:

pg_dump --no-owner --clean --if-exists

The easiest way to load data into a database is to pipe an SQL dump through the platform sql command, like so:

platform sql < my_database_backup.sql

That runs the database backup against the SQL database on Platform.sh. That works for any SQL file, so the usual caveats about importing an SQL dump apply (for example, it’s best to run against an empty database). As with exporting, you can also specify a specific environment to use and a specific database relationship to use, if there are multiple.

platform sql --relationship postgresql -e BRANCH_NAME < my_database_backup.sql

Sanitizing data

To ensure people who review code changes can’t access personally identifiable information stored in your database, sanitize your preview environments.

Multiple databases

If you are using version 10, 11, 12, 13, or later of this service, it’s possible to define multiple databases as well as multiple users with different permissions. To do so requires defining multiple endpoints. Under the configuration key of your service there are two additional keys:

• databases: This is a YAML array listing the databases that should be created. If not specified, a single database named main is created.

  Note that removing a schema from the list of schemas on further deployments results in the deletion of the schema.

• endpoints: This is a nested YAML object defining different credentials. Each endpoint may have access to one or more schemas (databases), and may have different levels of permission for each. The valid permission levels are:

  • ro: Using this endpoint only SELECT queries are allowed.
  • rw: Using this endpoint SELECT queries as well as INSERT/UPDATE/DELETE queries are allowed.
  • admin: Using this endpoint all queries are allowed, including DDL queries (CREATE TABLE, DROP TABLE, etc.).

Consider the following illustrative example:

# The name of the service container. Must be unique within a project.
postgresql:
    type: "postgresql:16"
    disk: 2048
    configuration:
        databases:
            - main
            - legacy
        endpoints:
            admin:
                privileges:
                    main: admin
                    legacy: admin
            reporter:
                default_database: main
                privileges:
                    main: ro
            importer:
                default_database: legacy
                privileges:
                    legacy: rw

This example creates a single PostgreSQL service named postgresql. The server has two databases, main and legacy with three endpoints created.

• admin: has full access to both databases.
• reporter: has SELECT query access to the main database, but no access to legacy.
• importer: has SELECT/INSERT/UPDATE/DELETE access (but not DDL access) to the legacy database. It doesn’t have access to main.

If a given endpoint has access to multiple databases you should also specify which is listed by default in the relationships array. If one isn’t specified, the path property of the relationship is null. While that may be acceptable for an application that knows the name of the database it’s connecting to, automated tools like the Platform.sh CLI can’t access the database on that relationship. For that reason, defining the default_database property is always recommended.

Once these endpoints are defined, you need to expose them to your application as a relationship. Continuing with the above example, your relationships in .platform.app.yaml might look like:

relationships:
    database: 
        service: postgresql
        endpoint: admin
    reports: 
        service: postgresql
        endpoint: reporter
    imports:
        service: postgresql
        endpoint: importer

Each database is accessible to your application through the database, reports, and imports relationships. They’ll be available in the PLATFORM_RELATIONSHIPS environment variable and all have the same structure documented above, but with different credentials. You can use those to connect to the appropriate database with the specified restrictions using whatever the SQL access tools are for your language and application.

A service configuration without the configuration block defined is equivalent to the following default values:

# The name of the service container. Must be unique within a project.
postgresql:
    type: "postgresql:16"
    disk: 2048
    configuration:
        databases:
            - main
        endpoints:
            postgresql:
                default_database: main
                privileges:
                    main: admin

If you do not define database but endpoints are defined, then the single database main is created with the following assumed configuration:

# The name of the service container. Must be unique within a project.
postgresql:
    type: "postgresql:16"
    disk: 2048
    configuration:
        databases:
            - main
        endpoints: <your configuration>

Alternatively, if you define multiple databases but no endpoints, a single user main is created with admin access to each of your databases, equivalent to the configuration below:

# The name of the service container. Must be unique within a project.
postgresql:
    type: "postgresql:16"
    disk: 2048
    configuration:
        databases:
            - firstdb
            - seconddb
            - thirddb
        endpoints:
            main:
                firstdb: admin
                seconddb: admin
                thirddb: admin

Password generation

When you connect your app to a database, an empty password is generated for the database by default. This can cause issues with your app.

To generate real passwords for your database, define custom endpoints in your service configuration. For each custom endpoint you create, you get an automatically generated password, similarly to when you create multiple databases. Note that you can’t customize these automatically generated passwords.

After your custom endpoints are exposed as relationships in your app configuration, you can retrieve the password for each endpoint through the PLATFORM_RELATIONSHIPS environment variable within your application containers. The password value changes automatically over time, to avoid downtime its value has to be read dynamically by your app. Globally speaking, having passwords hard-coded into your codebase can cause security issues and should be avoided.

When you switch from the default configuration with an empty password to custom endpoints, make sure your service name remains unchanged. Failure to do so results in the creation of a new service, which removes any existing data from your database.

Restrict access to database replicas only

For security reasons, you can grant your app access to replicas instead of your actual database. To do so, when defining the relationship between your app and database, make sure you do the following:

1. Use the explicit endpoint syntax.
2. Add the -replica suffix to the name of the endpoint you want to use.

This results in the following configuration:

relationships:
    RELATIONSHIP_NAME:
        service: SERVICE_NAME
        endpoint: ENDPOINT_NAME-replica

For example, if you define a postgresql database as follows:

postgresql:
    type: "postgresql:16"
    disk: 2048
    configuration:
        databases:
            - main
            - legacy
        endpoints:
            admin:
                privileges:
                    main: admin
                    legacy: admin
            reporter:
                default_database: main
                privileges:
                    main: ro

To create a replica of the postgresql database and allow your app to connect to it through the admin endpoint with admin permissions, use the following configuration:

relationships:
  postgresql:
    service: postgresql
    endpoint: admin-replica

To create a replica of the postgresql database and allow your app to connect to it through the reporter endpoint with read-only permissions instead, use the following configuration:

relationships:
  postgresql:
    service: postgresql
    endpoint: reporter-replica

Service timezone

To change the timezone for the current session, run SET TIME ZONE TIMEZONE;.

Extensions

Platform.sh supports a number of PostgreSQL extensions. To enable them, list them under the configuration.extensions key in your .platform/services.yaml file, like so:

# The name of the service container. Must be unique within a project.
postgresql:
    type: "postgresql:16"
    disk: 2048
    configuration:
        extensions:
            - pg_trgm
            - hstore

In this case, you have pg_trgm installed, providing functions to determine the similarity of text based on trigram matching, and hstore providing a key-value store.

Available extensions

The following is the extensive list of supported extensions. Note that you can’t currently add custom extensions not listed here.

• address_standardizer - Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
• address_standardizer_data_us - For standardizing addresses based on US dataset example
• adminpack - administrative functions for PostgreSQL
• autoinc - functions for auto-incrementing fields
• bloom - bloom access method - signature file based index (requires 9.6 or higher)
• btree_gin - support for indexing common data types in GIN
• btree_gist - support for indexing common data types in GiST
• chkpass - data type for auto-encrypted passwords
• 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
• 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_prewarm - prewarm relation data (requires 9.6 or higher)
• pg_stat_statements - track execution statistics of all SQL statements executed
• pg_trgm - text similarity measurement and index searching based on trigrams
• pg_visibility - examine the visibility map (VM) and page-level visibility info (requires 9.6 or higher)
• pgcrypto - cryptographic functions
• pgrouting - pgRouting Extension (requires 9.6 or higher)
• pgrowlocks - show row-level locking information
• pgstattuple - show tuple-level statistics
• plpgsql - PL/pgSQL procedural language
• postgis - PostGIS geometry, geography, and raster spatial types and functions
• postgis_sfcgal - PostGIS SFCGAL 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
• timetravel - functions for implementing time travel
• tsearch2 - compatibility package for pre-8.3 text search functions (obsolete, only available for 9.6 and 9.3)
• tsm_system_rows - TABLESAMPLE method which accepts number of rows as a limit (requires 9.6 or higher)
• tsm_system_time - TABLESAMPLE method which accepts time in milliseconds as a limit (requires 9.6 or higher)
• unaccent - text search dictionary that removes accents
• uuid-ossp - generate universally unique identifiers (UUIDs)
• vector - Open-source vector similarity search for PostgreSQL 11+
• xml2 - XPath querying and XSLT

Notes

Could not find driver

If you see this error: Fatal error: Uncaught exception 'PDOException' with message 'could not find driver', this means you are missing the pdo_pgsql PHP extension. You need to enable it in your .platform.app.yaml (see above).

Upgrading

PostgreSQL 10 and later include an upgrade utility that can convert databases from previous versions to version 10 or later. If you upgrade your service from a previous version of PostgreSQL to version 10 or above, it upgrades automatically.

The utility can’t upgrade PostgreSQL 9 versions, so upgrades from PostgreSQL 9.3 to 9.6 aren’t supported. Upgrade straight to version 11 instead.

Upgrade to PostgreSQL 12 with the postgis extension

You can’t upgrade to PostgreSQL 12 with the postgis extension enabled. It involves a change to a major version that results in a failed deployment that requires support intervention to fix. Upgrading from 12 to a higher version is possible.

If you need to upgrade to version 12, follow the same steps recommended for downgrading:

1. Dump the database.
2. Remove the service.
3. Create a new service with PostgreSQL 12.
4. Import the dump to that service.