Three questions about Percona Postgresql

[kevin310007]

Q1:

I already install Community PG16.8 at Oracle linux 9.5 and with some tables. I want to use tde_heap function to encrypt my tables. here are my setup below:

1. Install Percona Postgresql 17.4.1
2. Use pg_dump to export my PG16.8 tables
3. Import to Percona Postgresql17.4.1
4. Do alter table “table-name” set access method tde_heap for each table

It seems work, but if I have a mount of data and tables, what is the best way to do that?

Q2:

Every time when I rotate the tde_key, is it necessary to restart the Percona Postgresql service?

Q3:

When I want to change from on-premises to public cloud, is there any Percona Postgresql original services on public cloud (GCP/AWS/Azure) can be subscribed? Or I just need to install the linux OS and Percona Postgresql on it then import the data?

[Alastair_Turner]

Hi Kevin

Thanks for giving pg_tde a go.

There are two ways you could optimise the data transfer and encryption process in Q1:

Firstly - you could write the data only once. Changing the access method rewrites all the data in the table, so you are currently writing it out twice - once when you import and once when you encrypt. If you loaded the schema first, then changed the access method of the target table before importing the data, you would only be writing the data to disk once.

If you’re copying all the data over from your old system, you could also avoid exporting and importing, but it’s a bit more of process.

• Build a new Postgres 16.x system on your target OS
• Set the new system up as a replica of the current 16.8
• Wait for it to catch up
• Break the replication link
• Install Percona Distribution for PostgreSQL 17
• Upgrade to 17 with pg_upgrade
• Change the access method on those tables which you want to encrypt

[Robert_Bernier]

Hi, answering some of your questions:

want to use tde_heap function to encrypt my tables.

Seems you’ve answered that question pretty well

when I rotate the tde_key, is it necessary to restart the Percona Postgresql service?

Depends, can you provide example steps how you rotate the key?

is there any Percona Postgresql original services on public cloud (GCP/AWS/Azure) can be subscribed?

Nope, not at this time

Hope this helps

[kevin310007]

Hi Robert:

Thx for your comment first. According to your question：

Depends, can you provide example steps how you rotate the key?

My step of rotate tde_key are below:

1. backup the old key and set the permission
   1.1 cp tde_key tde_key.bak-20250430[quote=“Robert_Bernier, post:3, topic:37766, full:true”]
   1.2 chown mypguser:mypguser tde_key.bak-20250430
   1.3 chmod 600 tde_key.bak-20250417

2. generate new key and set the permission
   2.1 openssl rand -out tde_key_new 292
   2.2 chown mypguser:mypguser tde_key_new
   2.3 chomd 600 tde_key_new

3. connect to DB and change the key
   3.1 /opt/pgdistro/percona-postgresql17/bin/psql -d postgres

   3.2 SELECT pg_tde_add_key_provider_file(‘file-provider-new’, ‘/usr/local/pgsql/keyring/tde_key_new’);

   3.3 SELECT pg_tde_set_principal_key(‘tde_key_new’,‘file-provider-new’,‘true’);

In this situation, is it necessary to restart the Percona Postgresql service or would you give me some examples that should be restart or not to restart?

Thx!

[Robert_Bernier]

Hi,
Apologies for the delay. Coincidentally, I’ve been working on courseware for pg_tde.

First off: generating a new key using openssl won’t work, you’ll have two choices; either copy the new key under a new name and use it or add another provider altogether.

However you create your 2nd key you can rotate the key using this function:

– update provider, keep the provider name but point it to the new key
select *
from
pg_tde_change_key_provider_file
(
‘my-provider’,
‘/var/lib/postgresql/tde_key_new’
);

Validate the change using these two functions:

– validate
select * from pg_tde_list_all_key_providers();
select * from pg_tde_principal_key_info();

Although it should work, the old key is still in RAM so for the time being a server restart guarantees it picks up the new key location. Usually, I run a query on pre-existing tables in order to confirm everything worked out.

One last caveat: remember keyrings are good for figuring out how to work with pg_tde but for production purposes you “must” use either external files or KMIP. The whole point of TDE is to prevent someone who has direct access to the data cluster itself from stealing it. Having a keying file locally installed on the file system kinda defeats the purpose.

Hope this helps.

[kevin310007]

Hi Robert:
I think I have a little confuse about the relationship between the key provider and the principal key.
According to your description " you’ll have two choices; either copy the new key under a new name and use it or add another provider altogether.", I will try to describe my opinion, if I am wrong, please correct me. Thx!

If this is my statement below(two keys):

[root@vbox keyring]# ls
tde_key  tde_key_new

[root@vbox keyring]# pwd
/usr/local/pgsql/keyring

postgres=# SELECT pg_tde_principal_key_info();
 (my-master-key,file-provider,1,"2025-04-14 14:25:18.656093+08")

postgres=# SELECT pg_tde_list_all_key_providers();
 (1,file-provider,file,"{""type"" : ""file"", ""path"" : ""/usr/local/pgsql/keyring/tde_key""}")

==============
The first choice:
Just like what you said, I can use this command to rotate my key.

postgres=# select pg_tde_change_key_provider_file('file-provider','/usr/local/pgsql/keyring/tde_key_new');
                               1

postgres=# SELECT pg_tde_principal_key_info();
 (my-master-key,file-provider,1,"2025-04-14 14:25:18.656093+08")

postgres=# SELECT pg_tde_list_all_key_providers();
 (1,file-provider,file,"{""type"" : ""file"", ""path"" : ""/usr/local/pgsql/keyring/tde_key_new""}")

==============
The Second choice:
I need to create a new key provider to link with the tde_key_new.

postgres=# SELECT pg_tde_add_key_provider_file('file-provider-new', '/usr/local/pgsql/keyring/tde_key_new');
                            2

postgres=# SELECT pg_tde_set_principal_key('my-master-key','file-provider-new','true');
 t

postgres=# SELECT pg_tde_principal_key_info();
 (my-master-key,file-provider-new,2,"2025-05-02 15:54:09.893777+08")

postgres=# SELECT pg_tde_list_all_key_providers();
 (1,file-provider,file,"{""type"" : ""file"", ""path"" : ""/usr/local/pgsql/keyring/tde_key""}")
 (2,file-provider-new,file,"{""type"" : ""file"", ""path"" : ""/usr/local/pgsql/keyring/tde_key_new""}")

When I need rotate the “key”, it seems should be the key under the /keyring path, not the my-master-key, right?

What is the situation that I need to change the principal key?

I think this info is very important right?

If I want to use it in my environment, it seems I need to copy the tde_key to another server to store it? Would you give me a example?

Thx!

[kevin310007]

Hi Alastair:
Apologies for the delay. I try to edit the table.sql that I exported from community postgresql16.8.
From

To

Then I import it to the percona postgresql.

Connect to database and check the pg168_table is successful imported. And I check the filepath

I think this is work. It’s encrypted!

From your suggestion:

* Build a new Postgres 16.x system on your target OS
* Set the new system up as a replica of the current 16.8
* Wait for it to catch up
* Break the replication link
* Install Percona Distribution for PostgreSQL 17
* Upgrade to 17 with pg_upgrade
* Change the access method on those tables which you want to encrypt

It seems I can upgrade community postgresql 16.8 to percona postgresql17 through pg_upgrade?