> ## Documentation Index > Fetch the complete documentation index at: https://developer.upsun.com/llms.txt > Use this file to discover all available pages before exploring further. # PostgreSQL (Database service) export const DynamicCodeBlock = ({language = 'yaml', filename, icon, lines, wrap, expandable, highlight, focus, children}) => { const STORAGE_KEY = 'upsun_versions_cache'; const COMPOSABLE_STORAGE_KEY = 'upsun_composable_cache'; const CACHE_TTL = 5 * 60 * 1000; const API_URL = 'https://meta.upsun.com/images'; const COMPOSABLE_API_URL = 'https://meta.upsun.com/composable'; const DEBUG_PREFIX = '[DynamicCodeBlock cache]'; const [versionData, setVersionData] = useState(null); const [versionError, setVersionError] = useState(false); const [composableData, setComposableData] = useState(null); const [composableError, setComposableError] = useState(false); useEffect(() => { const fetchData = async () => { let cachedData = null; let cachedEtag = null; if (typeof localStorage !== 'undefined') { try { const cached = localStorage.getItem(STORAGE_KEY); if (cached) { const parsed = JSON.parse(cached); cachedData = parsed?.data || null; cachedEtag = parsed?.etag || null; if (cachedData && Date.now() - parsed.timestamp < CACHE_TTL) { return cachedData; } } } catch (err) { console.error('Failed to load from cache:', err); } } const requestHeaders = cachedEtag ? { 'If-None-Match': cachedEtag } : {}; console.debug(`${DEBUG_PREFIX} revalidating`, { storageKey: STORAGE_KEY, hasCachedData: Boolean(cachedData), hasCachedEtag: Boolean(cachedEtag) }); const response = await fetch(API_URL, { headers: requestHeaders }); if (response.status === 304 && cachedData) { console.debug(`${DEBUG_PREFIX} revalidated (304)`, { storageKey: STORAGE_KEY }); if (typeof localStorage !== 'undefined') { try { const etag = response.headers.get('etag') || cachedEtag; localStorage.setItem(STORAGE_KEY, JSON.stringify({ data: cachedData, etag, timestamp: Date.now() })); } catch (err) { console.error('Failed to refresh cache metadata:', err); } } return cachedData; } if (!response.ok) throw new Error(`API request failed: ${response.statusText}`); const data = await response.json(); const etag = response.headers.get('etag'); console.debug(`${DEBUG_PREFIX} refreshed (200)`, { storageKey: STORAGE_KEY, etag }); if (typeof localStorage !== 'undefined') { try { localStorage.setItem(STORAGE_KEY, JSON.stringify({ data, etag, timestamp: Date.now() })); } catch (err) { console.error('Failed to cache data:', err); } } return data; }; fetchData().then(data => setVersionData(data)).catch(err => console.error('Failed to fetch version data:', err)); }, []); const findHighestVersion = versionsMap => { if (!versionsMap || Object.keys(versionsMap).length === 0) return null; const entries = Object.entries(versionsMap); const active = entries.filter(([, v]) => v.upsun && v.upsun.status === 'supported' || v.upsun && v.upsun.status === 'deprecated'); const candidates = active.length > 0 ? active : entries; let [highestName] = candidates[0]; for (let i = 1; i < candidates.length; i++) { const [currentName] = candidates[i]; const cp = currentName.split('.').map(Number); const hp = highestName.split('.').map(Number); for (let j = 0; j < Math.max(cp.length, hp.length); j++) { if ((cp[j] || 0) > (hp[j] || 0)) { highestName = currentName; break; } else if ((cp[j] || 0) < (hp[j] || 0)) { break; } } } return highestName; }; const getVersion = (lang, requestedVersion = 'latest') => { if (lang === 'composable') { if (!composableData || !composableData.versions || Object.keys(composableData.versions).length === 0) return null; if (requestedVersion && requestedVersion !== 'latest') { return (requestedVersion in composableData.versions) ? requestedVersion : null; } return findHighestVersion(composableData.versions); } if (!versionData) return null; const imageData = versionData[lang]; if (!imageData || !imageData.versions || Object.keys(imageData.versions).length === 0) { return null; } if (requestedVersion && requestedVersion !== 'latest') { return (requestedVersion in imageData.versions) ? requestedVersion : null; } return findHighestVersion(imageData.versions); }; let code = typeof children === 'string' ? children : String(children || ''); const codeLines = code.split('\n'); while (codeLines.length > 0 && codeLines[0].trim() === '') codeLines.shift(); while (codeLines.length > 0 && codeLines[codeLines.length - 1].trim() === '') codeLines.pop(); if (codeLines.length > 0) { const indents = codeLines.filter(line => line.trim().length > 0).map(line => line.match(/^[ \t]*/)[0].length); const minIndent = Math.min(...indents); code = codeLines.map(line => line.slice(minIndent)).join('\n'); } code = code.replace(/\{\{version:(.*?)\}\}/g, (match, params) => { const parts = params.split(':'); const lang = parts[0]; const ver = parts[1] || 'latest'; const isComposable = lang === 'composable'; const hasError = isComposable ? composableError : versionError; const dataReady = isComposable ? composableData !== null : versionData !== null; if (hasError) return '(unavailable)'; if (dataReady) { const resolvedVersion = getVersion(lang, ver); return resolvedVersion || match; } return '...'; }); const codeBlockProps = { language, ...filename && ({ filename }), ...icon && ({ icon }), ...lines !== undefined && ({ lines }), ...wrap !== undefined && ({ wrap }), ...expandable !== undefined && ({ expandable }), ...highlight && ({ highlight }), ...focus && ({ focus }) }; return {code}; }; export const VersionDeprecatedBlock = () => <>

Deprecated versions

The following versions are deprecated. They're available, but they don't receive security updates from upstream and aren't guaranteed to work. They'll be removed in the future – consider migrating to a supported version.

; export const VariableBlock = ({name}) => { return {name}; }; PostgreSQL is a high-performance, standards-compliant relational SQL database. See the [PostgreSQL documentation](https://www.postgresql.org/docs/9.6/index.html) for more information. The [example](#usage-example) provided later in this topic is specific to using only a **primary** database. For details about using read-only replicas to improve performance of read-heavy applications, see the [PostgreSQL read-only replication](/docs/add-services/postgresql/postgresql-readonly-replication) topic. ## Supported versions You can select the major version. But the latest compatible minor version is applied automatically and can’t be overridden. Patch versions are applied periodically for bug fixes and the like. When you deploy your app, you always get the latest available patches. * 18 * 17 * 16 * 15 * 14 You can't upgrade to PostgreSQL 12 with the `postgis` extension enabled. For more details, see how to [upgrade to PostgreSQL 12 with `postgis`](#upgrade-to-postgresql-12-with-the-postgis-extension). ## Retired versions The following versions have been retired and are no longer available. If your project uses a retired version, you must update to a [supported version](#supported-versions). * 13 * 12 * 11 * 10 * 9.6 * 9.5 * 9.3 ## Relationship reference For each service [defined via a relationship](#usage-example) to your application, Upsun automatically generates corresponding environment variables within your application container, in the `$_` format. Here is example information available through the [service environment variables](/docs/development/variables#service-environment-variables) themselves, or through the [`PLATFORM_RELATIONSHIPS` environment variable](/docs/development/variables/use-variables#use-provided-variables). You can obtain the complete list of available service environment variables in your app container by running `upsun ssh env`. Service connection details can change whenever your app restarts or redeploys. **To keep your connection stable, use [service environment variables](/docs/development/variables#service-environment-variables) rather than hard-coding values.** {` POSTGRESQL_USERNAME=main POSTGRESQL_SCHEME=pgsql POSTGRESQL_SERVICE=postgresql POSTGRESQL_FRAGMENT= POSTGRESQL_IP=123.456.78.90 POSTGRESQL_HOSTNAME=azertyuiopqsdfghjklm.postgresql.service._.eu-1.platformsh.site POSTGRESQL_PORT=5432 POSTGRESQL_CLUSTER=azertyuiopqsdf-main-afdwftq POSTGRESQL_EPOCH=0 POSTGRESQL_HOST=postgresql.internal POSTGRESQL_REL=postgresql POSTGRESQL_PATH=main POSTGRESQL_QUERY={'is_master': True} POSTGRESQL_PASSWORD=ChangeMe POSTGRESQL_TYPE=postgresql:{{version:postgresql:latest}} POSTGRESQL_PUBLIC=false POSTGRESQL_HOST_MAPPED=false ` } For some advanced use cases, you can use the [`PLATFORM_RELATIONSHIPS` environment variable](/docs/development/variables/use-variables#use-provided-variables). The structure of the `PLATFORM_RELATIONSHIPS` environment variable can be obtained by running `upsun relationships` in your terminal: {` { "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:{{version:postgresql:latest}}", "public": false, "host_mapped": false } ` } Here is an example of how to gather [`PLATFORM_RELATIONSHIPS` environment variable](/docs/development/variables/use-variables#use-provided-variables) information in a [`.environment` file](/docs/development/variables/set-variables#when-to-use-env-files): ```bash .environment theme={null} # Decode the built-in credentials object variable. export RELATIONSHIPS_JSON="$(echo "$PLATFORM_RELATIONSHIPS" | base64 --decode)" # Set environment variables for individual credentials. export APP_POSTGRESQL_HOST="$(echo "$RELATIONSHIPS_JSON" | jq -r '.postgresql[0].host')" ``` ## Usage example Use the steps and sample code below if your application will connect to a **primary** PostgreSQL database. For details about using read-only replicas to improve performance of read-heavy applications, see the [PostgreSQL read-only replication](/docs/add-services/postgresql/postgresql-readonly-replication) topic. ### 1. Configure the service To define the service, use the `postgresql` type: {` services: # The name of the service container. Must be unique within a project. : type: postgresql: ` } 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. Define the relationship To define the relationship, use the following configuration: {` applications: # The name of the app container. Must be unique within a project. : # 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: : ` } 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](/docs/configure-apps/image-properties/relationships) configuration for relationships. That is, it uses default endpoints behind the scenes, providing a [relationship](/docs/configure-apps/image-properties/relationships) (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](/docs/configure-apps/image-properties/relationships). With the above definition, the application container (`APP_NAME`) now has access to the service via the relationship `SERVICE_NAME` and its corresponding [service environment variables](/docs/development/variables#service-environment-variables). {` applications: # The name of the app container. Must be unique within a project. : # Relationships enable access from this app to a given service. # The example below shows configuration with an explicitly set service name and endpoint. # See the Application reference for all options for defining relationships and endpoints. relationships: : service: endpoint: postgresql ` } You can define `SERVICE_NAME` and `` as you like, so long as it's unique between all defined services and relationships and matches in both the application and services configuration. The example above leverages [explicit endpoint](/docs/configure-apps/image-properties/relationships) configuration for relationships. Depending on your needs, instead of explicit endpoint configuration, you can use [default endpoint configuration](/docs/configure-apps/image-properties/relationships). With the above definition, the application container now has [access to the service](#use-in-app) via the relationship `` and its corresponding [service environment variables](/docs/development/variables#service-environment-variables). For PHP, enable the [extension](/docs/languages/php/extensions) for the service: {` applications: # The name of the app container. Must be unique within a project. : # PHP extensions. runtime: extensions: - pdo_pgsql # 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: : ` } {` applications: # The name of the app container. Must be unique within a project. : # PHP extensions. runtime: extensions: - pdo_pgsql # Relationships enable access from this app to a given service. # The example below shows configuration with an explicitly set service name and endpoint. # See the Application reference for all options for defining relationships and endpoints. relationships: : service: endpoint: postgresql ` } ### Example configuration {` applications: # The name of the app container. Must be unique within a project. myapp: # PHP extensions. runtime: extensions: - pdo_pgsql # 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: services: # The name of the service container. Must be unique within a project. postgresql: type: postgresql:{{version:postgresql:latest}}` } {` applications: # The name of the app container. Must be unique within a project. myapp: # PHP extensions. runtime: extensions: - pdo_pgsql # Relationships enable access from this app to a given service. # The example below shows configuration with an explicitly set service name and endpoint. # See the Application reference for all options for defining relationships and endpoints. relationships: postgresql: service: postgresql endpoint: postgresql services: # The name of the service container. Must be unique within a project. postgresql: type: postgresql:{{version:postgresql:latest}}` } ### Use in app To use the configured service in your app, add a configuration file similar to the following to your project. {` applications: # The name of the app container. Must be unique within a project. myapp: # The location of the application's code. source: root: "myapp" # PHP extensions. runtime: extensions: - pdo_pgsql [...] # 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: services: # The name of the service container. Must be unique within a project. postgresql: type: postgresql:{{version:postgresql:latest}}` } {` applications: # The name of the app container. Must be unique within a project. myapp: # The location of the application's code. source: root: "myapp" # PHP extensions. runtime: extensions: - pdo_pgsql [...] # Relationships enable access from this app to a given service. # The example below shows configuration with an explicitly set service name and endpoint. # See the Application reference for all options for defining relationships and endpoints. relationships: postgresql: service: postgresql endpoint: postgresql services: # The name of the service container. Must be unique within a project. postgresql: type: postgresql:{{version:postgresql:latest}}` } This configuration defines a single application (`myapp`), whose source code exists in the `/myapp` directory.
`myapp` has access to the `postgresql` service, via a relationship whose name is [identical to the service name](#2-define-the-relationship) (as per [default endpoint](/docs/configure-apps/image-properties/relationships) configuration for relationships). From this, `myapp` can retrieve access credentials to the service through the [relationship environment variables](#relationship-reference). ```bash myapp/.environment theme={null} # Set environment variables for individual credentials. # For more information, please visit /docs/development/variables#service-environment-variables. export DB_CONNECTION="${POSTGRESQL_SCHEME}" export DB_USERNAME="${POSTGRESQL_USERNAME}" export DB_PASSWORD="${POSTGRESQL_PASSWORD}" export DB_HOST="${POSTGRESQL_HOST}" export DB_PORT="${POSTGRESQL_PORT}" export DB_DATABASE="${POSTGRESQL_PATH}" # Surface connection string variable for use in app. export DATABASE_URL="${DB_CONNECTION}://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_DATABASE}" ``` The above file — `.environment` in the `myapp` directory — is automatically sourced by Upsun into the runtime environment, so that the variable `DATABASE_URL` can be used within the application to connect to the service. Note that `DATABASE_URL`, and all Upsun [service environment variables](/docs/development/variables#service-environment-variables) like `POSTGRESQL_HOST`, are environment-dependent. Unlike the build produced for a given commit, they can’t be reused across environments and only allow your app to connect to a single service instance on a single environment. A file very similar to this is generated automatically for your when using the `upsun ify` command to [migrate a codebase to Upsun](/docs/get-started). ## Access the service directly Access the service using the Upsun CLI by running `upsun sql`. You can also access it from your app container via [SSH](/docs/development/ssh). From your [relationship data](#relationship-reference), you need: `POSTGRESQL_USERNAME`, `POSTGRESQL_HOST`, and `POSTGRESQL_PORT`. Then run the following command: ```bash theme={null} psql -U -h -p ``` Using the values from the [example](#relationship-reference), that would be: ```bash theme={null} psql -U main -h postgresql.internal -p 5432 ``` You can obtain the complete list of available service environment variables in your app container by running `upsun ssh env`. Service connection details can change whenever your app restarts or redeploys. **To keep your connection stable, use [service environment variables](/docs/development/variables#service-environment-variables) rather than hard-coding values.** ## Exporting data The easiest way to download all data in a PostgreSQL instance is with the Upsun CLI. If you have a single SQL database, the following command exports all data using the `pg_dump` command to a local file: ```bash theme={null} upsun db:dump ``` If you have multiple SQL databases it prompts you which one to export. You can also specify one by relationship name explicitly: ```bash theme={null} upsun db:dump --relationship postgresql ``` By default the file is uncompressed. If you want to compress it, use the `--gzip` (`-z`) option: ```bash theme={null} upsun 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: ```bash theme={null} upsun 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 `upsun 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: ```bash theme={null} 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: ```bash theme={null} pg_dump --no-owner --clean --if-exists ``` The easiest way to load data into a database is to pipe an SQL dump through the `upsun sql` command, like so: ```bash theme={null} upsun sql < my_database_backup.sql ``` That runs the database backup against the SQL database on Upsun. 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. {` upsun sql --relationship postgresql -e < my_database_backup.sql `} Importing a database backup is a destructive operation. It overwrites data already in your database. Taking a backup or a database export before doing so is strongly recommended. ## Sanitizing data To ensure people who review code changes can't access personally identifiable information stored in your database, [sanitize your preview environments](/docs/development/sanitize-db/postgresql). ## Set locale for database You can choose your locale when a database is created by setting locale-related variables. There are three ways to set a locale option, as detailed in the table below: | Name | Type | Default | Description | | ------------------- | -------- | --------- | -------------------------------------------------------------------------------- | | `default_ctype` | `string` | `C.UTF-8` | The default character classification. Affects any tables created after it's set. | | `default_collation` | `string` | `C.UTF-8` | The default collation rules. Affects any tables created after it's set. | | `default_charset` | `string` | `UTF8` | The default encoding character set. Affects any tables created after it's set. | ## Multiple databases Support for defining multiple databases and multiple users with different permissions is available in versions `10` and later of this service. 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: {` services: # The name of the service container. Must be unique within a project. postgresql: type: "postgresql:{{version:postgresql:latest}}" 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 Upsun 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 `.upsun/config.yaml` might look like: {` applications: # The name of the app container. Must be unique within a project. myapp: source: root: "/" [...] # Relationships enable access from this app to a given service. # The example below shows configuration with explicitly set service names and endpoints. # See the Application reference for all options for defining relationships and endpoints. relationships: database: service: postgresql endpoint: admin reports: service: postgresql endpoint: reporter imports: service: postgresql endpoint: importer services: # The name of the service container. Must be unique within a project. postgresql: type: "postgresql:{{version:postgresql:latest}}" 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` } Each database is accessible to your application through the `database`, `reports`, and `imports` relationships. They'll be available in the [service environment variables](/docs/development/variables#service-environment-variables) and all have the same structure documented [above](#relationship-reference), 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: {` services: # The name of the service container. Must be unique within a project. postgresql: type: "postgresql:{{version:postgresql:latest}}" 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: {` services: # The name of the service container. Must be unique within a project. postgresql: type: "postgresql:{{version:postgresql:latest}}" configuration: databases: - main endpoints: ` } 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: {` services: # The name of the service container. Must be unique within a project. postgresql: type: "postgresql:{{version:postgresql:latest}}" configuration: databases: - firstdb - seconddb - thirddb endpoints: main: firstdb: admin seconddb: admin thirddb: admin` } ## Password generation If your YAML file does not specify a `schema` and `endpoint` for a database service, no password is generated. Because your database is isolated on a private network and cannot be seen from the internet, you can omit a password without compromising security. This simplifies your workflow by removing the need to manage credentials, while container isolation ensures that only your application can access the data. If you prefer to have Upsun generate a password, you must define [`schemas` and custom `endpoints`](#1-configure-the-service) in the `services` configuration – see the example in the [multiple databases](#multiple-databases) section of this topic. For each custom endpoint that you define, Upsun generates a password. Note that you cannot customize these generated passwords. Make sure you don't change `services.`. **Changing the service name creates a new service, which removes existing data from your database.** After your custom endpoints are exposed as relationships in your [app configuration](/docs/configure-apps), you can retrieve the password for each endpoint through the [service environment variables](/docs/development/variables#service-environment-variables) within your [application containers](/docs/development/variables/use-variables#access-variables-in-your-app). Using this method to retrieve password credentials is considered a best practice: passwords might change over time, and using incorrect passwords results in application downtime. **Avoid using hard-coded passwords in your application (and code base), which can cause security issues.** ## Service timezone To change the timezone for the current session, run `SET TIME ZONE ;`. ## Extensions Upsun supports a number of PostgreSQL extensions. To enable them, list them under the `configuration.extensions` key in your `.upsun/config.yaml` file, like so: {` services: # The name of the service container. Must be unique within a project. postgresql: type: "postgresql:{{version:postgresql:latest}}" 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. | Extension | Description | Supported version(s) | | ------------------------------ | ------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- | | `address_standardizer` | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `address_standardizer_data_us` | For standardizing addresses based on US dataset example. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `adminpack` | Administrative functions for PostgreSQL (only available in versions less than 17). | 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `autoinc` | Functions for auto-incrementing fields. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `bloom` | Bloom access method - signature file based index (requires 9.6 or higher). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `btree_gin` | Support for indexing common data types in GIN. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `btree_gist` | Support for indexing common data types in GiST. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `chkpass` | Data type for auto-encrypted passwords. | 10.22, 10, 9.6, 9.5, 9.3 | | `citext` | Data type for case-insensitive character strings. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `cube` | Data type for multidimensional cubes. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `dblink` | Connect to other PostgreSQL databases from within a database. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `dict_int` | Text search dictionary template for integers. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `dict_xsyn` | Text search dictionary template for extended synonym processing. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `earthdistance` | Calculate great-circle distances on the surface of the Earth. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `file_fdw` | Foreign-data wrapper for flat file access. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `fuzzystrmatch` | Determine similarities and distance between strings. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `hstore` | Data type for storing sets of (key, value) pairs. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `insert_username` | Functions for tracking who changed a table. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `intagg` | Integer aggregator and enumerator (obsolete). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `intarray` | Functions, operators, and index support for 1-D arrays of integers. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `isn` | Data types for international product numbering standards. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `lo` | Large Object maintenance. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `ltree` | Data type for hierarchical tree-like structures. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `moddatetime` | Functions for tracking last modification time. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pageinspect` | Inspect the contents of database pages at a low level. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pg_buffercache` | Examine the shared buffer cache. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pg_freespacemap` | Examine the free space map (FSM). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pg_prewarm` | Prewarm relation data (requires 9.6 or higher). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pg_stat_statements` | Track execution statistics of all SQL statements executed. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pg_trgm` | Text similarity measurement and index searching based on trigrams. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pg_visibility` | Examine the visibility map (VM) and page-level visibility info (requires 9.6 or higher). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pgcrypto` | Cryptographic functions. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pgrouting` | pgRouting Extension (requires 9.6 or higher). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pgrowlocks` | Show row-level locking information. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `pgstattuple` | Show tuple-level statistics. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `plpgsql` | PL/pgSQL procedural language. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `postgis` | PostGIS geometry, geography, and raster spatial types and functions. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `postgis_sfcgal` | PostGIS SFCGAL functions. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `postgis_tiger_geocoder` | PostGIS tiger geocoder and reverse geocoder. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `postgis_topology` | PostGIS topology spatial types and functions. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `postgres_fdw` | Foreign-data wrapper for remote PostgreSQL servers. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.5, 9.3 | | `refint` | Functions for implementing referential integrity (obsolete). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `seg` | Data type for representing line segments or floating-point intervals. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `sslinfo` | Information about SSL certificates. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `tablefunc` | Functions that manipulate whole tables, including crosstab. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `tcn` | Triggered change notifications. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `timetravel` | | 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `tsearch2` | Compatibility package for pre-8.3 text search functions (obsolete, only available for 9.6 and 9.3). | 9.6, 9.3 | | `tsm_system_rows` | TABLESAMPLE method which accepts number of rows as a limit (requires 9.6 or higher). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `tsm_system_time` | TABLESAMPLE method which accepts time in milliseconds as a limit (requires 9.6 or higher). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `unaccent` | Text search dictionary that removes accents. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `uuid-ossp` | Generate universally unique identifiers (UUIDs). | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | | `vector` | Open-source vector similarity search for PostgreSQL 11+. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11 | | `xml2` | XPath querying and XSLT. | 18.3, 18, 17.9, 17, 16.13, 16, 15.17, 15, 14.22, 14, 13.23, 13, 12.22, 12, 11.22, 11, 10.22, 10, 9.6, 9.3 | You can't upgrade to PostgreSQL 12 with the `postgis` extension enabled. For more details, see how to [upgrade to PostgreSQL 12 with `postgis`](#upgrade-to-postgresql-12-with-the-postgis-extension). ## Large databases Upsun supports PostgreSQL databases of any size, including multi-terabyte deployments. The same service configuration applies whether your database holds gigabytes or tens of terabytes. Only the resources you allocate to it differ. ### Cloning at any size When you create a [preview environment](/docs/environments), Upsun clones the entire parent environment (including its database) at the storage layer. The clone completes in seconds to under a minute regardless of database size. A 10 TB database clones in roughly the same time as a 10 GB one. Service start-up time also stays largely the same with larger datasets on PostgreSQL. ### What drives performance Total disk size is rarely the limiting factor for query performance. What matters is the **active dataset** (or working set), the subset of rows, indexes, and pages your application actually reads and writes. A 10 TB database that serves queries against 5 GB of hot data can outperform a 50 GB database. The smaller database performs worse if it scans entire tables on every request. As long as the active dataset fits in PostgreSQL's [shared buffers](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS) and the operating system's page cache, queries are served from memory without touching disk. By contrast, queries that read most of a table become more expensive as the table grows. The same is true of indexes that effectively scan the whole table. ### Sizing for large datasets Memory is the lever that keeps the active dataset hot. PostgreSQL containers use the [`HIGH_MEMORY` container profile](/docs/manage-resources/adjust-resources#advanced-container-profiles) by default. Combined with [Guaranteed CPU](/docs/manage-resources/guaranteed-resources), you can allocate up to **512 GB of RAM** to a single PostgreSQL container. To work efficiently with a large database: * Size memory to fit the active dataset in shared buffers and the OS page cache. * Make sure queries hit indexes, and avoid sequential scans on large tables. * Watch index selectivity. An index that scans most of a large table on each query loads gigabytes into memory, evicting the active dataset. * Use [`EXPLAIN (ANALYZE, BUFFERS)`](https://www.postgresql.org/docs/current/sql-explain.html) to verify which indexes a query uses and how much memory it touches. * Run [`ANALYZE`](https://www.postgresql.org/docs/current/sql-analyze.html) after large data imports to refresh the planner's statistics. Disk size alone, even into the multi-terabyte range, does not determine database performance. It determines what data you can store. Memory and query patterns determine how fast you can serve it. ## Troubleshooting ### 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 `.upsun/config.yaml` ([see above](#1-configure-the-service)). ## 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. Make sure you first test your migration on a separate branch. Also, be sure to take a backup of your production environment **before** you merge this change.

Warning

Downgrading isn't supported. If you need to downgrade, dump to SQL, remove the service, recreate the service, and import your dump. ### 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.