Enabling TDE on an existing EDB Postgres Advanced Server cluster

Enabling TDE on an EDB Postgres Advanced Server cluster

Create a new EDB Postgres Advanced Server cluster with TDE enabled and use pg_upgrade to transfer data from the existing source cluster to the new encrypted cluster.

Prepare your upgrade by performing a backup of the existing instance.
Create a new database server
- Create an empty directory for the new server and ensure enterprisedb owns it.
- Set the environment variables to export the wrap and unwrap commands for encryption.
- Initialize a server with encryption enabled.
- Change the default port, so the new server is available at another port.
- Start the database server.
- Connect to the database server and ensure it is functioning.
Upgrade to the encrypted server
- Stop both the source and the new server.
- Use pg_upgrade with --copy-by-block option to copy data from the source server to the new server. Specify the source and target bin and data directories.
- Start the new encrypted databaser server.
- Connect to the encrypted database server and ensure the data was transfered.
Clean up and delete the source server
- Clean up the database and its statistics.
- Remove the source EDB Postgres Advanced Server cluster with the script provided by pg_upgrade.

Worked example

Preparing your upgrade

Use pg_dumpall, pgBackRest, or Barman to create a backup of your unencrypted source server.

Creating an encrypted server

Create an empty directory for the new server:
```
mkdir /var/lib/edb-as/16/TDE 
```

Ensure the enterprisedb user owns the directory:

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

Set environment variables to export the wrap and unwrap commands:
```
export PGDATAKEYWRAPCMD='openssl enc -e -aes-128-cbc -pass pass:ok -out %p'
export PGDATAKEYUNWRAPCMD='openssl enc -d -aes-128-cbc -pass pass:ok -in %p'
```
Note
Alternatively, use the --key-unwrap-command=<command> and --key-wrap-command=<command> arguments when initializing the encrypted server to include the wrap and unwrap commands.
See Using initdb TDE options for more information on possible configurations.
Initialize the new server with encryption:
```
/usr/lib/edb-as/16/bin/initdb --data-encryption -D /var/lib/edb-as/16/TDE
```
This command initializes a CONFIG directory with all configuration files for the encrypted server.
Modify the port number in the configuration file of the encrypted instance. Uncomment the line with #port and change the port number. For example:
```
port                    5590
```

Start the encrypted server:

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

Connect to the server:
```
/usr/lib/edb-as/16/bin/psql -p 5590 edb
```
Note
If you're using two different Postgres versions, use the psql utility of the encrypted server. Otherwise, the system will attempt to use psql from the previous instance.
To ensure the new server is encrypted, check for TDE presence.

Upgrading to the encrypted server

Stop both servers:

/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

To test for incompatibilities, run the pg_upgrade command in check mode.
With -b and -B, specify the source and target BIN directories. With -d and -D, specify the source and target CONFIG directories. Include the --copy-by-block option.
```
/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
```
Note
The --check mode performs preliminary checks without executing the command.

To copy data from the source server to the target server, run the pg_upgrade command in normal mode:

/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

Restart the encrypted server:

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

Connect to the encrypted database server:
```
/usr/lib/edb-as/16/bin/psql -p 5590 edb
```
Perform a spot check to ensure the databases, tables, schemas, and resources you had in the unencrypted server are available in the new server. For example, list all databases:
```
\l
```
Connect to a database, for example hr, and search for existing tables:
```
\c hr 
SELECT * FROM dept;
```

Cleaning up after upgrade

After you verify that pg_upgrade encrypted the data successfully, perform a cleanup.

Clean up the database and its statistics:

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

Remove all data files of the unencrypted server with the script generated by pg_upgrade:
```
./delete_old_cluster.sh
```

← Prev

Creating a database cluster with TDE enabled

↑ Up

Transparent Data Encryption