Last updated 3rd June 2021
Objective
PostgreSQL is a high-performance, standards-compliant relational SQL database.
See the PostgreSQL documentation for more information.
Supported versions
Grid |
---|
9.6 |
10 |
11 |
12 |
13 |
Upgrading to PostgreSQL 12 using the postgis
extension is not currently supported. Attempting to upgrade with this extension enabled will result in a failed deployment that will require support intervention to fix.
See the Upgrading to PostgreSQL 12 with postgis
section below for more details.
Deprecated versions
The following versions are available but are not receiving security updates from upstream, so their use is not recommended. They will be removed at some point in the future.
Grid |
---|
9.3 |
Relationship
The format exposed in the $PLATFORM_RELATIONSHIPS
environment variable:
{
"username": "main",
"scheme": "pgsql",
"service": "postgresql12",
"ip": "169.254.114.234",
"hostname": "zydalrxgkhif2czr3xqth3qkue.postgresql12.service._.eu-3.platformsh.site",
"cluster": "rjify4yjcwxaa-master-7rqtwti",
"host": "postgresql.internal",
"rel": "postgresql",
"path": "main",
"query": {
"is_master": true
},
"password": "main",
"type": "postgresql:12",
"port": 5432
}
Usage example
In your .platform/services.yaml
add:
dbpostgres:
type: postgresql:13
disk: 256
Add a relationship to the service in your .platform.app.yaml
:
relationships:
postgresdatabase: "dbpostgres:postgresql"
You will need to use
postgresql
type when defining the service```yaml
.platform/services.yaml
service_name: type: postgresql:version disk:256 ```
and the endpoint
postgresql
when defining the relationship```yaml
.platform.app.yaml
relationships: relationship_name: “service_name:postgresql” ```
Your
service_name
andrelationship_name
are defined by you, but we recommend making them distinct from each other.
For PHP, in your .platform.app.yaml
add:
runtime:
extensions:
- pdo_pgsql
You can then use the service in a configuration file of your application with something like:
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 Web PaaS 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 := \x60
CREATE TABLE IF NOT EXISTS PeopleGo (
id SERIAL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL);\x60
_, err = db.Exec(sqlCreate)
checkErr(err)
// Insert data.
sqlInsert := \x60
INSERT INTO PeopleGo(name, city) VALUES
('Neil Armstrong', 'Moon'),
('Buzz Aldrin', 'Glen Ridge'),
('Sally Ride', 'La Jolla');\x60
_, err = db.Exec(sqlInsert)
checkErr(err)
table := \x60<table>
<thead>
<tr><th>Name</th><th>City</th></tr>
</thead>
<tbody>\x60
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 Web PaaS 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);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
logger.append(String.format("the JAVA_FRAMEWORKS id %d the name %s ", id, name));
logger.append('\n');
}
statement.execute("DROP TABLE JAVA_FRAMEWORKS");
return logger.toString();
} catch (SQLException exp) {
throw new RuntimeException("An error when execute PostgreSQL", exp);
}
}
}
<?php
declare(strict_types=1);
use Platformsh\ConfigReader\Config;
// Create a new config object to ease reading the Web PaaS 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,
// So we don't have to mess around with cursors and unbuffered queries by default.
]);
$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 Web PaaS 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
Exporting data
The easiest way to download all data in a PostgreSQL instance is with the WebPaas CLI. If you have a single SQL database, the following command will export all data using the pg_dump
command to a local file:
webpaas db:dump
If you have multiple SQL databases it will prompt you which one to export. You can also specify one by relationship name explicitly:
webpaas db:dump --relationship database
By default the file will be uncompressed. If you want to compress it, use the --gzip
(-z
) option:
webpaas 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:
webpaas db:dump --stdout | bzip2 > dump.sql.bz2
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 webpaas sql
command, like so:
webpaas sql < my_database_backup.sql
That will run the database backup against the SQL database on Web PaaS. That will work for any SQL file, so the usual caveats about importing an SQL dump apply (e.g., 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.
webpaas sql --relationship database -e master < my_database_backup.sql
Importing a database backup is a destructive operation. It will overwrite data already in your database. Taking a backup or a database export before doing so is strongly recommended.
Multiple databases
If you are using version 13
or later of this service it is 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 namedmain
will be created.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 onlySELECT
queries are allowed.rw
: Using this endpointSELECT
queries as well asINSERT
/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:
dbpostgres:
type: postgresql:13
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 dbpostgres
. The server will have two databases, main
and legacy
with three endpoints created.
admin
: has full access to both databases.reporter
: hasSELECT
query access to themain
database, but no access tolegacy
.importer
: hasSELECT
/INSERT
/UPDATE
/DELETE
access (but not DDL access) to thelegacy
database. It does not have access tomain
.
If a given endpoint has access to multiple databases you should also specify which will be listed by default in the relationships array. If one isn't specified, the path
property of the relationship will be null
. While that may be acceptable for an application that knows the name of the database it's connecting to, automated tools like the Web PaaS CLI will not be able to access the database on that relationship. For that reason, defining the default_database
property is always recommended.
Once these endpoints are defined, you will 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: "dbpostgres:admin"
reports: "dbpostgres:reporter"
imports: "dbpostgres:importer"
Each database will be 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:
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
will be created with the following assumed configuration:
configuration:
databases:
- main
endpoints: <your configuration>
Alternatively, if you define multiple databases but no endpoints, a single user main
will be created with admin
access to each of your databases, equivalent to the configuration below:
configuration:
databases:
- firstdb
- seconddb
- thirddb
endpoints:
main:
firstdb: admin
seconddb: admin
thirddb: admin
Extensions
Web PaaS supports a number of PostgreSQL extensions. To enable them, list them under the configuration.extensions
key in your services.yaml
file, like so:
db:
type: postgresql:12
disk: 1025
configuration:
extensions:
- pg_trgm
- hstore
In this case you will 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 cannot 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 - Address Standardizer US dataset example
- adminpack - administrative functions for PostgreSQL
- autoinc - functions for autoincrementing fields
- bloom - bloom access method - signature file based index (requires 9.6 or higher)
- btree_gin - support for indexing common datatypes in GIN
- btree_gist - support for indexing common datatypes 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)
- xml2 - XPath querying and XSLT
Upgrading to PostgreSQL 12 using the postgis
extension is not currently supported. Attempting to upgrade with this extension enabled will result in a failed deployment that will require support intervention to fix.
See the Upgrading to PostgreSQL 12 with postgis
section below for more details.
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 simply 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 (by modifying the services.yaml
file) the upgrader will run automatically.
The upgrader does not work to upgrade to PostgreSQL 9 versions, so upgrades from PostgreSQL 9.3 to 9.6 are not supported. Upgrade straight to version 11 instead.
Make sure you first test your migration on a separate branch.
Be sure to take a backup of your master environment before you merge this change.
Downgrading is not supported. If you want, for whatever reason, to downgrade you should dump to SQL, remove the service, recreate the service, and import your dump.
Upgrading to PostgreSQL 12 with the postgis
extension
Upgrading to PostgreSQL 12 using the postgis
extension is not currently supported. Attempting to upgrade with this extension enabled will result in a failed deployment that will require support intervention to fix.
If you need to upgrade, you should follow the same steps recommended for performing downgrades: dump the database, remove the service, recreate the service with PostgreSQL 12, and then import the dump to that service.
Did you find this guide useful?
Please feel free to give any suggestions in order to improve this documentation.
Whether your feedback is about images, content, or structure, please share it, so that we can improve it together.
Your support requests will not be processed via this form. To do this, please use the "Create a ticket" form.
Thank you. Your feedback has been received.