> ## 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) 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. > [!NOTE] > > 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](https://developer.upsun.com/docs/add-services/postgresql/postgresql-readonly-replication.md) 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 other maintenance updates. By default, you get the latest available patches each time you deploy. If your project has a [maintenance window](https://developer.upsun.com/docs/core-concepts/build-deploy.md#maintenance-window) configured, Upsun Cloud applies patches only during that window. Upsun Cloud may still apply urgent or critical fixes outside of the maintenance window. * 18 * 17 * 16 * 15 * 14 > [!NOTE] > > 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). ## Deprecated versions The following versions are still available in your projects, but they're at their end of life and are no longer receiving security updates from upstream. *No deprecated versions.* To ensure your project remains stable in the future, switch to a [supported version](#supported-versions). ## 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 Cloud automatically generates corresponding environment variables within your application container, in the `$_` format. Here is example information available through the [service environment variables](https://developer.upsun.com/docs/development/variables#service-environment-variables) themselves, or through the [`PLATFORM_RELATIONSHIPS` environment variable](https://developer.upsun.com/docs/development/variables/use-variables#use-provided-variables). > [tab] Service environment 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](https://developer.upsun.com/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 > > [tab] `PLATFORM_RELATIONSHIPS` environment variable > > For some advanced use cases, you can use the [`PLATFORM_RELATIONSHIPS` environment variable](https://developer.upsun.com/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](https://developer.upsun.com/docs/development/variables/use-variables#use-provided-variables) information in a [`.environment` file](https://developer.upsun.com/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 > [!NOTE] > > 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](https://developer.upsun.com/docs/add-services/postgresql/postgresql-readonly-replication.md) 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: > [tab] Using default endpoints > > 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](https://developer.upsun.com/docs/configure-apps/image-properties/relationships.md) configuration for relationships. > That is, it uses default endpoints behind the scenes, providing a [relationship](https://developer.upsun.com/docs/configure-apps/image-properties/relationships.md) > (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](https://developer.upsun.com/docs/configure-apps/image-properties/relationships.md). > > 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](https://developer.upsun.com/docs/development/variables#service-environment-variables). > [tab] Using explicit endpoints > > 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](https://developer.upsun.com/docs/configure-apps/image-properties/relationships.md) configuration for relationships. > > Depending on your needs, instead of explicit endpoint configuration, > you can use [default endpoint configuration](https://developer.upsun.com/docs/configure-apps/image-properties/relationships.md). > > 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](https://developer.upsun.com/docs/development/variables#service-environment-variables). For PHP, enable the [extension](https://developer.upsun.com/docs/languages/php/extensions.md) for the service: > [tab] Using default endpoints > > 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: > \: > > [tab] Using explicit endpoints > > 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 > [tab] Using default endpoints > > 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}} > [tab] Using explicit endpoints > > 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. > [tab] Using default endpoints > > 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}} > [tab] Using explicit endpoints > > 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](https://developer.upsun.com/docs/configure-apps/image-properties/relationships.md) 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 Cloud 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 Cloud [service environment variables](https://developer.upsun.com/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 Cloud](https://developer.upsun.com/docs/get-started.md). ## Access the service directly Access the service using the Upsun Cloud CLI by running `upsun sql`. You can also access it from your app container via [SSH](https://developer.upsun.com/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](https://developer.upsun.com/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 Cloud 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 Cloud. 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 > [!NOTE] > > 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](https://developer.upsun.com/docs/development/sanitize-db/postgresql.md). ## 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 Cloud 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](https://developer.upsun.com/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 Cloud 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 Cloud generates a password. Note that you cannot customize these generated passwords. > [!NOTE] > > 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](https://developer.upsun.com/docs/configure-apps), you can retrieve the password for each endpoint through the [service environment variables](https://developer.upsun.com/docs/development/variables#service-environment-variables) within your [application containers](https://developer.upsun.com/docs/development/variables/use-variables.md#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 Cloud 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. ### Update extensions Extensions listed under `configuration.extensions` are owned by the database owner role. Users connecting through an endpoint with `admin` privileges can manage them. If you [define multiple endpoints](#multiple-databases), `ro` and `rw` endpoints can't update extensions. Existing extensions aren't updated automatically when a newer version becomes available. During a PostgreSQL major version upgrade, extensions are only updated to the latest version supported by your previous PostgreSQL version. For example, after upgrading from PostgreSQL 13 to 18, `pg_trgm` stays at version 1.5 even though version 1.6 is available. This lets you decide when to update, since a new extension version can change query plans or behavior. Test extension updates on a preview environment before updating production. To check whether an update is available, run the following query with `upsun sql` using an `admin` endpoint (for example `upsun sql --relationship `): ```sql theme={null} SELECT name, installed_version, default_version FROM pg_available_extensions WHERE installed_version IS NOT NULL; ``` If `installed_version` is lower than `default_version`, update the extension: ```sql theme={null} ALTER EXTENSION pg_trgm UPDATE; ``` You can only update to versions shipped with the PostgreSQL service image. > [!NOTE] > > * Only extensions listed explicitly in `configuration.extensions` are owned by the database owner role. > Extensions installed automatically as dependencies (such as `cube` for `earthdistance`) aren't. > To manage a dependency, add it to your `configuration.extensions` list. > * Some extensions, such as `postgis` and `postgres_fdw`, aren't trusted by PostgreSQL. > They're owned by the database owner role like other listed extensions, but updating them requires superuser privileges, > so you can't run `ALTER EXTENSION ... UPDATE` on them yourself. > They're only updated during PostgreSQL major version upgrades. ### Available extensions The following is the extensive list of supported extensions. Note that you can't currently add custom extensions not listed here.
ExtensionDescriptionSupported versions
`address_standardizer`Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`address_standardizer_data_us`For standardizing addresses based on US dataset example.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`adminpack`Administrative functions for PostgreSQL (only available in versions less than 17).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`autoinc`Functions for auto-incrementing fields.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`bloom`Bloom access method - signature file based index (requires 9.6 or higher).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`btree_gin`Support for indexing common data types in GIN.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`btree_gist`Support for indexing common data types in GiST.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`chkpass`Data type for auto-encrypted passwords.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`citext`Data type for case-insensitive character strings.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`cube`Data type for multidimensional cubes.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`dblink`Connect to other PostgreSQL databases from within a database.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`dict_int`Text search dictionary template for integers.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`dict_xsyn`Text search dictionary template for extended synonym processing.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`earthdistance`Calculate great-circle distances on the surface of the Earth.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`file_fdw`Foreign-data wrapper for flat file access.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`fuzzystrmatch`Determine similarities and distance between strings.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`hstore`Data type for storing sets of (key, value) pairs.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`insert_username`Functions for tracking who changed a table.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`intagg`Integer aggregator and enumerator (obsolete).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`intarray`Functions, operators, and index support for 1-D arrays of integers.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`isn`Data types for international product numbering standards.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`lo`Large Object maintenance.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`ltree`Data type for hierarchical tree-like structures.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`moddatetime`Functions for tracking last modification time.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pageinspect`Inspect the contents of database pages at a low level.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pg_buffercache`Examine the shared buffer cache.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pg_freespacemap`Examine the free space map (FSM).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pg_prewarm`Prewarm relation data (requires 9.6 or higher).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pg_stat_statements`Track execution statistics of all SQL statements executed.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pg_trgm`Text similarity measurement and index searching based on trigrams.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pg_visibility`Examine the visibility map (VM) and page-level visibility info (requires 9.6 or higher).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pgcrypto`Cryptographic functions.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pgrouting`pgRouting Extension (requires 9.6 or higher).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pgrowlocks`Show row-level locking information.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`pgstattuple`Show tuple-level statistics.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`plpgsql`PL/pgSQL procedural language.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`postgis`PostGIS geometry, geography, and raster spatial types and functions.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`postgis_sfcgal`PostGIS SFCGAL functions.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`postgis_tiger_geocoder`PostGIS tiger geocoder and reverse geocoder.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`postgis_topology`PostGIS topology spatial types and functions.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`postgres_fdw`Foreign-data wrapper for remote PostgreSQL servers.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`refint`Functions for implementing referential integrity (obsolete).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`seg`Data type for representing line segments or floating-point intervals.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`sslinfo`Information about SSL certificates.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`tablefunc`Functions that manipulate whole tables, including crosstab.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`tcn`Triggered change notifications.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`timetravel`Trigger-based functions to record and query the history of row changes over time.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`tsearch2`Compatibility package for pre-8.3 text search functions (obsolete, only available for 9.6 and 9.3).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`tsm_system_rows`TABLESAMPLE method which accepts number of rows as a limit (requires 9.6 or higher).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`tsm_system_time`TABLESAMPLE method which accepts time in milliseconds as a limit (requires 9.6 or higher).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`unaccent`Text search dictionary that removes accents.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`uuid-ossp`Generate universally unique identifiers (UUIDs).18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`vector`Open-source vector similarity search for PostgreSQL 11+.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
`xml2`XPath querying and XSLT.18.31817.91716.131615.171514.221413.231312.221211.221110.22109.69.59.3
> [!NOTE] > > 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 Cloud 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](https://developer.upsun.com/docs/environments.md), Upsun Cloud 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](https://developer.upsun.com/docs/manage-resources/adjust-resources#advanced-container-profiles) by default. Combined with [Guaranteed CPU](https://developer.upsun.com/docs/manage-resources/guaranteed-resources.md), 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. > [!NOTE] > > 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] > > 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. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.