Vacuum (Full) not working || pg_tde

PostgreSQL PG_TDE – Transparent Data Encryption (TDE)
1

Hello Team,

When i execute normal vacuum it is working fine. but when try to do vacuum (full) table_name. this is creating high load and server going in crash recovery and after cancelling that statement server is ready to accept connection I can see in the log .

3 logical cores on server
RHEL Linux 8 version
PostgreSQL 16 version
RAM is 15GB
table size in 3257 MB

below is the log I can see when DB goes to recovery mode .

image
image1363×547 28.2 KB

2

Hello @amulla,
Please provide all of the steps/commands you used to get to this point. Also, a same schema/table would be helpful. If there is a bug somewhere, we need all the commands you used in order to reproduce your issue.

3

Sure @matthewb . Thanks for your comment i will update this in sometime . with same schema and table

4

@amulla and when did you install/update pg_tde last time?
We had a memory leak issue that might affect vacuum full and we’ve fixed it. Can you try the last nightly build (see latest packages in Assets) ?

5

Thanks for your help @Andrew_Pogrebnoi I will download latest and test on it. I will download from Percona repository for RPM installation instead of source installation as am getting issues while installing through make install .

6

Hello @Andrew_Pogrebnoi ,

Could you please let me know which file we should use for community PostgreSQL 16 version from below setups ?

image
image1214×328 20.7 KB

7

Hello Team,

I am getting below error when i try to execute vacuum
test=# VACUUM (ANALYZE,VERBOSE) players;
ERROR: could not find function “pg_tdeam_handler” in file “/usr/pgsql-16/lib/pg_tde.so”

i used 2nd number setup(pgtde-pgdg16.tar.gz) and copied files to extention directory and lib dire.

8

Hello @amulla

As pg_tde is still in development and only published for testing we made the decision that we do not guarantee that direct upgrades between different nightly/alpha/beta versions will work.

From the error message, I think you were using an old (before even the beta version) binary, and unfortunately upgrade from that to the newest version doesn’t work directly. One of the major changes since then is that the AM handler was renamed from “pg_tde” to “tde_heap_basic”, and that’s causing the error message you see.

To upgrade to the latest release, you have to use DROP EXTENSION pg_tde (CASCADE) / CREATE EXTENSION pg_tde

If you want to keep your tables/data during the upgrade, you can unencrypt them using ALTER TABLE <X> SET ACCESS METHOD heap, and after drop/create extension you can re-encrypt them using ALTER TABLE <x> SET ACCESS METHOD tde_heap_basic.

9

Thanks for your help on this . i was unable to drop extension it was throwing below error so i created another brand new db and created there extension and tested full vacuum which is running find now

test=# DROP EXTENSION pg_tde;
ERROR:  cannot drop extension pg_tde because other objects depend on it
DETAIL:  table players depends on access method pg_tde
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
test=# DROP EXTENSION pg_tde CASCADE;;
NOTICE:  drop cascades to table players
ERROR:  could not find function "pg_tdeam_handler" in file "/usr/pgsql-16/lib/pg_tde.so"
test=# DROP EXTENSION pg_tde CASCADE;
NOTICE:  drop cascades to table players
ERROR:  could not find function "pg_tdeam_handler" in file "/usr/pgsql-16/lib/pg_tde.so"
test=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 pg_tde  | 1.0     | public     | pg_tde access method
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)