Adding encryption and upgrading with pg_upgrade

Use pg_upgrade to upgrade an already TDE-enabled Enterprise Postgres (Oracle Compatible) or Enterprise Postgres cluster, rotate its encryption keys, or copy an unencrypted cluster's data into a new, TDE-enabled one — all while staying on the same Postgres distribution. TDE is built into both distributions, so there's no separate package to install. Because TDE only encrypts data as it's written, you can't turn it on for a running cluster: pg_upgrade creates the new, encrypted cluster and copies your data into it, alongside any version upgrade you're performing at the same time.

Note

To create a new TDE-enabled cluster instead of upgrading an existing one, see Creating a new TDE-enabled cluster on self-managed infrastructure. To change distributions instead of staying on the same one, see Migrating from PostgreSQL to Enterprise Postgres. For all supported scenarios, see Choosing a path.

Before you begin

Enabling TDE always involves choosing how to protect the generated data encryption key. See Protecting the data encryption key to compare your options, then follow Choosing a key protection mechanism to implement your choice.
Review the initdb TDE options to ensure you have all information required for initializing a TDE-enabled database cluster.
Review Limitations to understand limitations and changes in the handling of PostgreSQL utilities when you enable TDE.
Back up the source database server — a physical backup (for example with pgBackRest or Barman) or a logical backup (for example with pg_dumpall).

Migrating data

Migrate data and objects from an existing database server with pg_upgrade. The examples use Enterprise Postgres (Oracle Compatible) 16 running on Ubuntu 22.04. A similar workflow applies to other versions of Enterprise Postgres (Oracle Compatible) and Enterprise Postgres. The location of the bin and config directories differs depending on your operating system and the Postgres version.

Note

If you're migrating from community PostgreSQL to Enterprise Postgres (Oracle Compatible) or Enterprise Postgres (changing the Postgres distribution) while enabling TDE, see Migrating from PostgreSQL instead.

Create an empty directory for the new, target server:

mkdir <target_data_directory>

For example:

mkdir /var/lib/edb-as/16/TDE

Ensure the database system user owns the directory:

sudo chown <user> <target_data_directory>
sudo chgrp <user> <target_data_directory>

For example:

sudo chown enterprisedb /var/lib/edb-as/16/TDE
sudo chgrp enterprisedb /var/lib/edb-as/16/TDE

Export the wrap and unwrap commands to secure the encryption key, using the method you chose when choosing a key protection mechanism:
```
export PGDATAKEYWRAPCMD='<wrap-command> -out "%p"'
export PGDATAKEYUNWRAPCMD='<unwrap-command> -in "%p"'
```
For example, using a passphrase with OpenSSL:
```
export PGDATAKEYWRAPCMD='openssl enc -e -aes-128-cbc -pbkdf2 -pass pass:<password> -out "%p"'
export PGDATAKEYUNWRAPCMD='openssl enc -d -aes-128-cbc -pbkdf2 -pass pass:<password> -in "%p"'
```
Alternatively, skip the environment variables and pass the commands directly to initdb with --key-wrap-command=<command> and --key-unwrap-command=<command>.
Initialize the target server with --data-encryption enabled:
```
<bin_directory>/initdb --data-encryption -D <target_data_directory>
```
For example:
```
/usr/lib/edb-as/16/bin/initdb --data-encryption -D /var/lib/edb-as/16/TDE
```
Include other initdb TDE options as required. If you want to reuse the data encryption key from the existing cluster instead of generating a new one, use the --copy-key-from=<file> option instead.
Since the source and target servers run side by side during the migration, change the target server's port. Uncomment the #port line in its configuration file and set it to an unused port. For example:
```
port                    5590
```

Start the target server:

<bin_directory>/pg_ctl -D <target_data_directory> start

For example:

/usr/lib/edb-as/16/bin/pg_ctl -D /var/lib/edb-as/16/TDE -l logfile start

Verify that TDE is enabled on the target server by querying the data_encryption_version column returned by the pg_control_init function:
```
select data_encryption_version from pg_control_init();
```
Output
```
 data_encryption_version
-------------------------
                       1
(1 row)
```
A value of 0 means TDE isn't enabled. Any nonzero value reflects the version of TDE in use.

Stop both the source and the target server:

<bin_directory>/pg_ctl -D <source_data_directory> stop
<bin_directory>/pg_ctl -D <target_data_directory> stop

For example:

/usr/lib/edb-as/16/bin/pg_ctl -D /var/lib/edb-as/16/non-TDE stop
/usr/lib/edb-as/16/bin/pg_ctl -D /var/lib/edb-as/16/TDE stop

Optionally, test for incompatibilities by running pg_upgrade in check mode first. With -b and -B, specify the source and target bin directories. With -d and -D, specify the source and target data directories.

<bin_directory>/pg_upgrade -b <source_bin_directory> -B <target_bin_directory> \
  -d <source_data_directory> -D <target_data_directory> --copy-by-block --check

For example:

/usr/lib/edb-as/16/bin/pg_upgrade -b /usr/lib/edb-as/16/bin -B /usr/lib/edb-as/16/bin \
  -d /var/lib/edb-as/16/non-TDE -D /var/lib/edb-as/16/TDE --copy-by-block --check

--check mode performs preliminary checks without executing the command.

Use pg_upgrade with the --copy-by-block option to copy data from the source server to the target server:

<bin_directory>/pg_upgrade -b <source_bin_directory> -B <target_bin_directory> \
  -d <source_data_directory> -D <target_data_directory> --copy-by-block

For example:

/usr/lib/edb-as/16/bin/pg_upgrade -b /usr/lib/edb-as/16/bin -B /usr/lib/edb-as/16/bin \
  -d /var/lib/edb-as/16/non-TDE -D /var/lib/edb-as/16/TDE --copy-by-block

Include other TDE pg_upgrade options as required.

Start the new, encrypted database server:

<bin_directory>/pg_ctl -D <target_data_directory> start

For example:

/usr/lib/edb-as/16/bin/pg_ctl -D /var/lib/edb-as/16/TDE start

Connect to the encrypted database server and ensure the data was transferred. For example, connect and list all databases:
```
/usr/lib/edb-as/16/bin/psql -p 5590 edb
```
```
\l
```
Connect to a database, for example hr, and check for existing tables:
```
\c hr
SELECT * FROM dept;
```

Perform any required cleanup operations:

<bin_directory>/vacuumdb --all --analyze-in-stages

For example:

/usr/lib/edb-as/16/bin/vacuumdb --all --analyze-in-stages

Remove the old cluster's data files with the script pg_upgrade generated:
```
./delete_old_cluster.sh
```

Adding encryption and upgrading with pg_upgrade

Note

Before you begin

Migrating data

Note

← Prev

↑ Up

Next →