Percona

Percona Postgres Distribution with TDE support

Percona Postgres is a patched Postgres kernel with pg_tde (Transparent Data Encryption) extension.

It is compatible with PostgreSQL 18.1, and available on all supported platforms in Pigsty.

Performance Test for Percona Transparent Data Encryption (TDE)

Get Started

install Pigsty’s with the pgtde config template.

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c pgtde     # use percona postgres kernel
./install.yml            # setup everything with pigsty

Configure

The following parameters need to be tuned to deploy a percona cluster:

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - { name: dbuser_meta ,password: DBUser.Meta   ,pgbouncer: true ,roles: [dbrole_admin   ] ,comment: pigsty admin user }
      - { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer  }
    pg_databases:
      - name: meta
        baseline: cmdb.sql
        comment: pigsty tde database
        schemas: [pigsty]
        extensions: [ vector, postgis, pg_tde ,pgaudit, { name: pg_stat_monitor, schema: monitor } ]
    pg_hba_rules:
      - { user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes' }
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    # Percona PostgreSQL TDE Ad Hoc Settings
    pg_packages: [ percona-main, pgsql-common ]  # install percona postgres packages
    pg_libs: 'pg_tde, pgaudit, pg_stat_statements, pg_stat_monitor, auto_explain'

Extensions

Percona Postgres has 80 available extensions, including pg_tde, pgvector, postgis, pgaudit, set_user, pg_stat_monitor,….

name	version	comment
hstore_plperlu	1.0	transform between hstore and plperlu
jsonb_plperl	1.0	transform between jsonb and plperl
intagg	1.1	integer aggregator and enumerator (obsolete)
pltcl	1.0	PL/Tcl procedural language
isn	1.3	data types for international product numbering standards
pgstattuple	1.5	show tuple-level statistics
postgis_topology-3	3.5.4	PostGIS topology spatial types and functions
postgis_raster	3.5.4	PostGIS raster types and functions
tsm_system_rows	1.0	TABLESAMPLE method which accepts number of rows as a limit
lo	1.2	Large Object maintenance
hstore_plperl	1.0	transform between hstore and plperl
ltree	1.3	data type for hierarchical tree-like structures
postgis_raster-3	3.5.4	PostGIS raster types and functions
postgis_topology	3.5.4	PostGIS topology spatial types and functions
pgrowlocks	1.2	show row-level locking information
address_standardizer_data_us-3	3.5.4	Address Standardizer US dataset example
uuid-ossp	1.1	generate universally unique identifiers (UUIDs)
postgis-3	3.5.4	PostGIS geometry and geography spatial types and functions
hstore_plpython3u	1.0	transform between hstore and plpython3u
postgis	3.5.4	PostGIS geometry and geography spatial types and functions
set_user	4.2.0	similar to SET ROLE but with added logging
postgis_tiger_geocoder-3	3.5.4	PostGIS tiger geocoder and reverse geocoder
jsonb_plperlu	1.0	transform between jsonb and plperlu
pg_surgery	1.0	extension to perform surgery on a damaged relation
xml2	1.2	XPath querying and XSLT
pg_stat_monitor	2.3	The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_statements. pg_stat_monitor provides aggregated statistics, client information, plan details including plan, and histogram information.
pg_tde	2.1	pg_tde access method
plpgsql	1.0	PL/pgSQL procedural language
address_standardizer-3	3.5.4	Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
tablefunc	1.0	functions that manipulate whole tables, including crosstab
hstore	1.8	data type for storing sets of (key, value) pairs
vector	0.8.1	vector data type and ivfflat and hnsw access methods
postgis_tiger_geocoder	3.5.4	PostGIS tiger geocoder and reverse geocoder
dblink	1.2	connect to other PostgreSQL databases from within a database
pltclu	1.0	PL/TclU untrusted procedural language
pg_trgm	1.6	text similarity measurement and index searching based on trigrams
sslinfo	1.2	information about SSL certificates
pg_stat_statements	1.12	track planning and execution statistics of all SQL statements executed
bool_plperlu	1.0	transform between bool and plperlu
cube	1.5	data type for multidimensional cubes
ltree_plpython3u	1.0	transform between ltree and plpython3u
amcheck	1.5	functions for verifying relation integrity
postgis_sfcgal	3.5.4	PostGIS SFCGAL functions
plpython3u	1.0	PL/Python3U untrusted procedural language
tsm_system_time	1.0	TABLESAMPLE method which accepts time in milliseconds as a limit
intarray	1.5	functions, operators, and index support for 1-D arrays of integers
btree_gist	1.8	support for indexing common datatypes in GiST
plperlu	1.0	PL/PerlU untrusted procedural language
fuzzystrmatch	1.2	determine similarities and distance between strings
bool_plperl	1.0	transform between bool and plperl
btree_gin	1.3	support for indexing common datatypes in GIN
pg_prewarm	1.2	prewarm relation data
pg_repack	1.5.3	Reorganize tables in PostgreSQL databases with minimal locks
citext	1.8	data type for case-insensitive character strings
pgcrypto	1.4	cryptographic functions
moddatetime	1.0	functions for tracking last modification time
plperl	1.0	PL/Perl procedural language
seg	1.4	data type for representing line segments or floating-point intervals
earthdistance	1.2	calculate great-circle distances on the surface of the Earth
unaccent	1.1	text search dictionary that removes accents
postgres_fdw	1.2	foreign-data wrapper for remote PostgreSQL servers
pg_logicalinspect	1.0	functions to inspect logical decoding components
tcn	1.0	Triggered change notifications
bloom	1.0	bloom access method - signature file based index
dict_int	1.0	text search dictionary template for integers
autoinc	1.0	functions for autoincrementing fields
address_standardizer_data_us	3.5.4	Address Standardizer US dataset example
postgis_sfcgal-3	3.5.4	PostGIS SFCGAL functions
jsonb_plpython3u	1.0	transform between jsonb and plpython3u
file_fdw	1.0	foreign-data wrapper for flat file access
pgaudit	18.0	provides auditing functionality
dict_xsyn	1.0	text search dictionary template for extended synonym processing
pg_walinspect	1.1	functions to inspect contents of PostgreSQL Write-Ahead Log
pg_buffercache	1.6	examine the shared buffer cache
refint	1.0	functions for implementing referential integrity (obsolete)
pg_freespacemap	1.3	examine the free space map (FSM)
insert_username	1.0	functions for tracking who changed a table
address_standardizer	3.5.4	Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
pg_visibility	1.2	examine the visibility map (VM) and page-level visibility info
pageinspect	1.13	inspect the contents of database pages at a low level