Request for guidance: pg_tde WAL encryption behavior with pgBackRest PITR

PostgreSQL PG_TDE – Transparent Data Encryption (TDE)
1

Hello team :waving_hand:

We are currently testing pg_tde with pgBackRest on Percona Distribution for PostgreSQL 18, and we would really appreciate your guidance to better understand the expected and supported behavior around WAL encryption and PITR.

Our environment
PostgreSQL 18.1
Percona Server for PostgreSQL 18.1.1
pg_tde 2.1
pgBackRest 2.57
Patroni-based HA cluster

What we are trying to achieve

Our goal is to have:
:white_check_mark: Data-at-rest encryption using pg_tde
:white_check_mark: Encrypted backups
:white_check_mark: Point-in-Time Recovery (PITR) using pgBackRest

What we observed during testing

We ran multiple controlled tests with the following setup:

shared_preload_libraries = ‘pg_tde’
pg_tde.wal_encrypt = on

archive_command =
‘/usr/lib/postgresql/18/bin/pg_tde_archive_decrypt %f %p “pgbackrest --stanza=cluster_1 archive-push %%p”’

restore_command =
‘/usr/lib/postgresql/18/bin/pg_tde_restore_encrypt %f %p “pgbackrest --stanza=cluster_1 archive-get %%f %%p”’

Test flow

Insert data before backup
Take a full pgBackRest backup
Insert additional data
Perform pgBackRest PITR (target time before step 3)

Result we are seeing

Full backups restore correctly
WAL archiving and restore commands work

However, during PITR:

WAL replay does not stop at the target time, or PostgreSQL fails during recovery with WAL-related errors, if we disable/off WAL encryption and take backup:

pg_tde.wal_encrypt = off

PITR works consistently and as expected.

Our current understanding (please correct us if wrong)Based on our testing, it appears that:

pgBackRest PITR may not be compatible with WAL encryption at this time

However, we are not fully sure if this is:

An expected limitation
A configuration mistake on our side
Or something that requires a different workflow

Where we need help :folded_hands:
We would really appreciate clarification on:

2

Hi @asad121,

Your archive_command and restore_command wrappers look correct. I reproduced your setup (PG 18.1, pg_tde 2.1.1, pgBackRest 2.57) and found two issues that explain the PITR failures.

Issue 1: async archiving is incompatible with pg_tde

Check whether pgBackRest async archiving is enabled (grep -i archive-async /etc/pgbackrest/pgbackrest.conf). pg_tde does not support it. pg_tde_archive_decrypt decrypts WAL to a temp directory in /dev/shm/, and pgBackRest async mode rejects any path outside pg1-path:

ASSERT: [025]: absolute path '/dev/shm/pg_tde_archive.../archive_status'
is not in base path '/data/db'

WAL never reaches the archive, so backups time out waiting for segments that will never arrive. If async is on, set archive-async=n in [global]. This is a documented limitation: Limitations of pg_tde - Percona Transparent Data Encryption for PostgreSQL

Issue 2: pgBackRest overrides your restore_command wrapper

pgBackRest restore writes its own restore_command into postgresql.auto.conf. This is expected behavior, but it overrides your pg_tde_restore_encrypt wrapper because PostgreSQL uses last-value-wins for config entries. PG then reads plaintext WAL from the archive but expects encrypted WAL, producing:

invalid magic number 0DDA in WAL segment ...
FATAL: could not locate required checkpoint record

The fix is to pass the pg_tde wrapper via --recovery-option during restore:

pgbackrest --stanza=cluster_1 restore \
  --delta --type=time \
  --target='<your target time>' \
  --target-action=promote \
  --recovery-option=restore_command='/usr/lib/postgresql/18/bin/pg_tde_restore_encrypt %f %p "pgbackrest --stanza=cluster_1 archive-get %%f %%p"'

This tells pgBackRest to use your wrapper instead of its default. Alternatively, you can edit postgresql.auto.conf manually between restore and PG startup: remove the # Recovery settings generated by pgBackRest block and re-add with the pg_tde wrapper. With either approach, my PITR test correctly restored to the target time (100 rows inserted before backup, 50 after, got 100 back).

Additional checks

If neither issue above matches your setup, verify data checksums (SHOW data_checksums;). The pg_tde docs recommend disabling them when using pgBackRest with WAL encryption, and Patroni enables checksums by default. Also confirm no key rotation happened between backup and target time (SELECT * FROM pg_tde_principal_key_info();).

Since you’re running Patroni, how are you triggering the PITR restore? Patroni manages postgresql.auto.conf and restore_command on its own, which could interact with both the pgBackRest override and the pg_tde wrapper.

3

Hi Anderson,

Thank you very much for replying to my query and for setting up the lab environment to test this.

Yes, I had read in the Percona documentation that async archiving is incompatible, so I did not enable it. I also did not set any custom value for it.

Secondly, I had disabled checksum as well, so that should not be causing the issue.

I tested again using your suggested command. However, when I restarted percona-patroni, it restored again from WAL and did not perform PITR as expected.

After that, I manually started PostgreSQL using:

$sudo -iu postgres /usr/lib/postgresql/18/bin/pg_ctl -D /var/lib/postgresql/18/main start

With this manual start, PITR completed successfully.

Could you please confirm whether percona-patroni pushes or overrides any recovery-related parameters during restart that might affect PITR behavior?

For reference, below are the exact steps I performed:

$systemctl stop percona-patroni
$rm -rf /var/lib/postgresql/18/main/*
$sudo -iu postgres pgbackrest --stanza=cluster_1 restore --delta --type=time --target=“2026-02-21 21:45:05+05” --target-action=promote --recovery-option=restore_command=‘/usr/lib/postgresql/18/bin/pg_tde_restore_encrypt %f %p “pgbackrest --stanza=cluster_1 archive-get %%f %%p”’

$sudo -iu postgres /usr/lib/postgresql/18/bin/pg_ctl -D /var/lib/postgresql/18/main start

I would appreciate your clarification on this behavior.