# PostgreSQL 16 Upgrade Management

PostgreSQL 16 strengthens security by enforcing the principle of least privilege. It introduces backward-incompatible changes to user, role, and privilege management, along with the following features:

* **Query Performance Optimization:** Parallelized `FULL` and `RIGHT OUTER` joins, incremental sorting for more queries, and performance gains in `COPY` operations support faster query execution and bulk data loading.
* **Privilege Security:** The `CREATEROLE` privilege is restricted; users with this privilege can no longer modify or escalate other roles unless they have explicit `ADMIN OPTION`. Thus, reduces the risk of unauthorized role modification.
* **Enhanced Monitoring:** `pg_stat_io` provides real-time input and output (I/O) performance metrics, helping to diagnose performance bottlenecks and tune I/O-intensive workloads.

For more information, refer to the [<mark style="color:blue;">PostgreSQL Documentation</mark>](https://www.postgresql.org/about/news/postgresql-16-released-2715/).

## Version upgrade

<code class="expression">space.vars.ionos\_cloud</code> offers a simplified upgrade to PostgreSQL 16 through the [<mark style="color:blue;">DCD</mark>](/cloud/databases/postgresql/how-tos/modify-cluster.md) or [<mark style="color:blue;">API</mark>](/cloud/databases/postgresql/api/v1-api/modify-cluster-attributes/upgrade-a-postgresql-cluster.md) and smoothly manages the [<mark style="color:blue;">Version upgrade considerations</mark>](#version-upgrade-considerations).

If you want to upgrade from an earlier PostgreSQL release to PostgreSQL 16 by yourself, you can migrate the data using `pg_dump`. For more information, refer to [<mark style="color:blue;">PostgreSQL Documentation</mark>](https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-MIGRATION).

{% hint style="info" %}
**Note:** Consider the [<mark style="color:blue;">actionable steps</mark>](#actionable-steps) required to manage roles, privileges, [<mark style="color:blue;">actionable steps</mark>](#actionable-steps-1) to manage role inheritance, and your existing cluster setup during a self-managed upgrade to PostgreSQL 16.
{% endhint %}

### Version upgrade considerations

You may encounter the following challenges when upgrading to PostgreSQL 16:

* [<mark style="color:blue;">Role and privilege management breakage</mark>](#role-and-privilege-management-breakage)
* [<mark style="color:blue;">Default role inheritance change</mark>](#default-role-inheritance-change)

#### Role and privilege management breakage

PostgreSQL 16 introduces a fundamental tightening of the `CREATEROLE` privilege, which can impact the existing administrative and deployment scripts.

| **v15 and earlier**                                                                                                                   | **v16**                                                                                           | **Incompatibilities**                                                                                                                                                                                                                                                                                                                           |
| ------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Users with `CREATEROLE` can modify, delete, or grant membership to virtually any non-superuser role and any user they did not create. | Only users with `ADMIN OPTION` on the target role can modify, delete, or add members to the role. | **Administrator User Lockout:** An administrator user who was previously able to manage all other roles because they had `CREATEROLE` can no longer perform administrative tasks such as `ALTER ROLE` or `DROP ROLE` on roles created by a superuser or by the administrator before the upgrade, because they lack the required `ADMIN OPTION`. |

#### Role constraints

With the upgrade to PostgreSQL 16, the `CREATEROLE` privilege now has restricted capabilities. Its ability to modify other roles is limited, resulting in the following constraints:

* **Cannot Modify or Drop Pre-existing Roles:** Administrative users, such as non-superuser administrators are unable to modify or drop roles created before the upgrade to PostgreSQL 16, as these roles do not automatically inherit the new `ADMIN OPTION`.
* **Cannot Grant Membership:** A `CREATEROLE` user can no longer add members to a role unless they were granted that role with the `ADMIN OPTION`. For example, `GRANT some_role TO admin_user WITH ADMIN OPTION`.

#### Actionable steps

The following lists the actions that you can perform while upgrading to PostgreSQL 16 to minimize the role restriction impacts:

1\. **Audit:** List roles with `CREATEROLE` privilege and identify the specific actions they perform.

2\. **Action:** Those roles that are used to grant or revoke role memberships, such as adding users to groups or to alter or drop other roles, grant the `ADMIN OPTION` on the roles they manage. This way, the roles can continue to perform the actions that were being performed before the upgrade.

#### Default role inheritance change

In PostgreSQL 16, the default behavior of role inheritance during the `GRANT` command is changed.

| **v15 and earlier**                                                                    | **v16**                                                                                                                                                                 | **Incompatibilities**                                                                                                                                                                                                                      |
| -------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Role membership grants were controlled solely by the granted role's `INHERIT` setting. | Role membership grants now have their own inheritance status set at `GRANT` time, with the ability to override the role's default using `WITH INHERIT` or `NO INHERIT`. | **Unexpected Privilege Loss:** If you relied on all members automatically inheriting privileges from a group role, you must explicitly use the `WITH INHERIT` clause in your GRANT scripts, or check if the roles' new default is correct. |

#### Actionable steps

The following lists the actions that you can take while upgrading to PostgreSQL 16 to minimize the role inheritance impacts:

1\. **Audit:** Identify roles using the `NO INHERIT` attribute, as their grants might lose the intended restriction.

2\. **Action:** Consider the following:

* Script `REVOKE` and `GRANT` operations to reapply memberships using the new explicit syntax.
* Enforce non-inheritance by adding `WITH INHERIT FALSE` to the re-grants for all affected memberships.
* Update provisioning tools to always use the explicit `WITH INHERIT TRUE/FALSE` syntax for clarity.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.ionos.com/cloud/databases/postgresql/overview/upgrade-maintenance/postgresql16-upgrade-management.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.