# Three questions about Percona Postgresql **URL:** \ **Category:** PG\_TDE – Transparent Data Encryption (TDE)\ **Created:** [April 29, 2025, 5:49am UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766 "2025-04-29T05:49:17Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![kevin310007](https://avatars.discourse-cdn.com/v4/letter/k/ee7513/32.png) [@kevin310007](https://forums.percona.com/u/kevin310007)\ **Post date:** [April 29, 2025, 5:49am UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766/1 "2025-04-29T05:49:17Z")
# 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? ---
**Author:** ![Alastair\_Turner](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/alastair_turner/32/13347_2.png) [@Alastair\_Turner](https://forums.percona.com/u/Alastair_Turner)\ **Post date:** [April 29, 2025, 9:49am UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766/2 "2025-04-29T09:49:05Z")
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 ---
**Author:** ![Robert\_Bernier](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/robert_bernier/32/1367_2.png) [@Robert\_Bernier](https://forums.percona.com/u/Robert_Bernier)\ **Post date:** [April 29, 2025, 7:39pm UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766/3 "2025-04-29T19:39:02Z")
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 ---
**Author:** ![kevin310007](https://avatars.discourse-cdn.com/v4/letter/k/ee7513/32.png) [@kevin310007](https://forums.percona.com/u/kevin310007)\ **Post date:** [April 30, 2025, 3:17am UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766/4 "2025-04-30T03:17:30Z")
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 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! ---
**Author:** ![Robert\_Bernier](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/robert_bernier/32/1367_2.png) [@Robert\_Bernier](https://forums.percona.com/u/Robert_Bernier)\ **Post date:** [May 1, 2025, 2:34pm UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766/5 "2025-05-01T14:34:34Z")
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. ---
**Author:** ![kevin310007](https://avatars.discourse-cdn.com/v4/letter/k/ee7513/32.png) [@kevin310007](https://forums.percona.com/u/kevin310007)\ **Post date:** [May 2, 2025, 8:21am UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766/6 "2025-05-02T08:21:29Z")
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): ```auto [root@vbox keyring]# ls tde_key tde_key_new ``` ```auto [root@vbox keyring]# pwd /usr/local/pgsql/keyring ``` ```auto postgres=# SELECT pg_tde_principal_key_info(); (my-master-key,file-provider,1,"2025-04-14 14:25:18.656093+08") ``` ```auto 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. ```auto postgres=# select pg_tde_change_key_provider_file('file-provider','/usr/local/pgsql/keyring/tde_key_new'); 1 ``` ```auto postgres=# SELECT pg_tde_principal_key_info(); (my-master-key,file-provider,1,"2025-04-14 14:25:18.656093+08") ``` ```auto 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. ```auto postgres=# SELECT pg_tde_add_key_provider_file('file-provider-new', '/usr/local/pgsql/keyring/tde_key_new'); 2 ``` ```auto postgres=# SELECT pg_tde_set_principal_key('my-master-key','file-provider-new','true'); t ``` ```auto postgres=# SELECT pg_tde_principal_key_info(); (my-master-key,file-provider-new,2,"2025-05-02 15:54:09.893777+08") ``` ```auto 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? ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/5/1/5100df59f543b8afd7bf85df3236a10f974a9030.png) I think this info is very important right? ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/d/d/dd0aa2d136c0a17b537e5773268b0180e87e2c90.png) 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? ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/b/d/bdc159af4dae472e0f5b784e57d85b89905579d4.png) Thx! ---
**Author:** ![kevin310007](https://avatars.discourse-cdn.com/v4/letter/k/ee7513/32.png) [@kevin310007](https://forums.percona.com/u/kevin310007)\ **Post date:** [May 5, 2025, 8:04am UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766/7 "2025-05-05T08:04:21Z")
Hi Alastair: Apologies for the delay. I try to edit the table.sql that I exported from community postgresql16.8. From ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/c/a/cafec518f5209aaa9a1b3c09b9c1c49271fe924b.png) To ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/6/3/6335e68938c2b70ac3c709eb419bfa7ad9a52e5b.png) Then I import it to the percona postgresql. ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/d/b/db84abc9e6948716ab9b16b1d156a51f12796e82.png) Connect to database and check the pg168\_table is successful imported. And I check the filepath ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/2/e/2e3563b77aaa96ba785b1f755b7f9affa9b30669.png) I think this is work. It’s encrypted! ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/3/a/3a48a171e6037c9d56594e142f829c3da262c1f0.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/e/6/e6bc35478bd2e78f5e8085df5f38947908b2f2bd.png) From your suggestion: ```auto * 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? ---
**Author:** ![Breno\_Baima](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/breno_baima/32/22870_2.png) [@Breno\_Baima](https://forums.percona.com/u/Breno_Baima)\ **Post date:** [June 22, 2026, 5:32pm UTC](https://forums.percona.com/t/three-questions-about-percona-postgresql/37766/8 "2026-06-22T17:32:45Z")
Hi Kevin, > [@kevin310007](#): > > I think this is work. It’s encrypted! In that step, there is an easy way to check if the table is encrypted by the following query: `SELECT pg_tde_is_encrypted('pg168_table');` > It seems I can upgrade community postgresql 16.8 to percona postgresql17 through pg\_upgrade? Yes, it is possible. You need to install the Percona packages and run the pg\_upgrade as usual. I’d add only one note. In future upgrades, if you are not using pg\_tde yet and start using it, you need to perform the upgrade with _ **pg\_tde\_upgrade;** _ see the [documentation page for more details](https://docs.percona.com/pg-tde/command-line-tools/pg-tde-upgrade.html?h=pg_tde_upgr#pg_tde_upgrade). Below you can see an exemple which a perform the upgrade from PostgreSQL 16 to Percona PostgreSQL 17. ```auto [root@node0 /]# dnf list --installed *postgresql* Installed Packages percona-postgresql17.x86_64 1:17.10-1.el9 @ppg-17-release-x86_64 percona-postgresql17-libs.x86_64 1:17.10-1.el9 @ppg-17-release-x86_64 percona-postgresql17-server.x86_64 1:17.10-1.el9 @ppg-17-release-x86_64 postgresql16.x86_64 16.14-2PGDG.rhel9.8 @pgdg16 postgresql16-libs.x86_64 16.14-2PGDG.rhel9.8 @pgdg16 postgresql16-server.x86_64 16.14-2PGDG.rhel9.8 @pgdg16 [postgres@node0 ~]$ /usr/pgsql-17/bin/pg_upgrade --old-bin=/usr/pgsql-16/bin --new-bin=/usr/pgsql-17/bin --old-data=/var/lib/pgsql/16/data --new-data=/var/lib/pgsql/17/data Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for contrib/isn with bigint-passing mismatch ok Checking data type usage ok Checking for not-null constraint inconsistencies ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade ------------------ Setting locale and encoding for new cluster ok Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_xact to new server ok Setting oldest XID for new cluster ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/members ok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster ok Copying user relation files ok Setting next OID for new cluster ok Sync data directory to disk ok Creating script to delete old cluster ok Checking for extension updates ok Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade. Once you start the new server, consider running: /usr/pgsql-17/bin/vacuumdb --all --analyze-in-stages Running this script will delete the old cluster's data files: ./delete_old_cluster.sh ```