TDE implementation in oracle 19c and how to change tde algorithm in oracle 19c

 

Transparent Data Encryption (TDE) in Oracle 19c secures "data at rest" by encrypting tablespaces or columns. It works by storing Master Encryption Keys in an external Keystore (Wallet), which transparently decrypts data for authorized users and applications

Implementing TDE in Oracle 19c involves a straightforward sequence of setting up the keystore location, creating the keystore, setting the master key, and encrypting the data. 

Steps to configure keystore using WALLET_ROOT and TDE_CONFIGURATION parameter are below,  1. Set WALLET_ROOT parameter alter system set wallet_root='<full path of wallet' scope=spfile;  2. shutdown and startup database shutdown immediate; startup;    3. Set TDE_CONFIGURATION parameter   *KEYSTORE_CONFIGURATION=FILE means using softwae keystore. alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID='*';  4. Create keystore administer key management create keystore identified by <password>;    5. Open keystore administer key management set keystore open identified by <password>;    6. Set key inside keystore administer key management set key identified by <password> with backup; Note: If you were using SQLNET.ENCRYPTION_WALLET_LOCATION parameter, you have to copy previous wallet named ewallet.p12 before executing Step 6.

For More Details

How to Configure Keystore and TDE Encryption Key for United Mode (Doc ID 2586100.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/get-started.html

Use Case

mkdir -p /wallet/TRAINING/wallet/tde

SQL> alter system set wallet_root='/wallet/EASYDB/wallet' scope=spfile;

SQL> show parameter wallet_root

NAME                                 TYPE

------------------------------------ --------------------------------

VALUE

------------------------------

wallet_root                          string

/wallet/EASYDB/wallet

SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;

System altered.

SQL> show parameter tde_configuration

NAME                                 TYPE

------------------------------------ --------------------------------

VALUE

------------------------------

tde_configuration                    string

KEYSTORE_CONFIGURATION=FILE

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/wallet/EASYDB/wallet/tde' identified by Uco12345;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/wallet/EASYDB/wallet/tde' identified by Uco12345;

keystore altered.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC

------------------------------ -------------------- --------- -------- ---------

    CON_ID

----------

FILE

/wallet/EASYDB/wallet/tde/

NOT_AVAILABLE                  UNKNOWN              SINGLE    NONE     UNDEFINED

         0

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE identified by Uco12345;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE identified by Uco12345 WITH BACKUP;

keystore altered.

SQL>  SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC

------------------------------ -------------------- --------- -------- ---------

    CON_ID

----------

FILE

/wallet/EASYDB/wallet/tde/

OPEN                           PASSWORD             SINGLE    NONE     NO

         0

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE identified by Uco12345;

keystore altered.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC

------------------------------ -------------------- --------- -------- ---------

    CON_ID

----------

FILE

/wallet/EASYDB/wallet/tde/

OPEN                           AUTOLOGIN            SINGLE    NONE     NO

         0

SQL> DESC V$ENCRYPTION_WALLET;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 WRL_TYPE                                           VARCHAR2(20)

 WRL_PARAMETER                                      VARCHAR2(4000)

 STATUS                                             VARCHAR2(30)

 WALLET_TYPE                                        VARCHAR2(20)

 WALLET_ORDER                                       VARCHAR2(9)

 KEYSTORE_MODE                                      VARCHAR2(8)

 FULLY_BACKED_UP                                    VARCHAR2(9)

 CON_ID                                             NUMBER

SQL> SELECT CON_ID,WALLET_TYPE,WRL_PARAMETER,STATUS FROM V$ENCRYPTION_WALLET;

    CON_ID WALLET_TYPE

---------- --------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS

------------------------------

         0 AUTOLOGIN

/wallet/EASYDB/wallet/tde/

OPEN

SQL> COL WRL_PARAMETER FOR A30

SQL> SET LINE 300 PAGES 300

SQL>

SQL>

SQL> /

    CON_ID WALLET_TYPE          WRL_PARAMETER                  STATUS

---------- -------------------- ------------------------------ ------------------------------

         0 AUTOLOGIN            /wallet/EASYDB/wallet/tde/   OPEN

SQL>

set linesize 250 pagesize 250

column name format a40

column masterkeyid_base64 format a60

select  name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64  FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#);

select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;

select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

ENCRYPTION

OWNER      = EASYUSER

TABLENAME  = EASYTABLET

TABLESPACE = EASY_MASTER1

1) CREATE A NEW TABLESPACE WITH ENCRYPTION

create tablespace EASY_MASTER1_TDE datafile '+EASYDATA_DG2' size 8G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);

2) MOVE THE TABLE TO NEW ENCYPTED TABLESPACE

alter table EASYUSER.EASYTABLET_BKP  move tablespace EASY_MASTER1_TDE;

3) REBUILD THE INDEXES

alter index EASYUSER.IX_ENTITYDOC_PREPROID_BANKID_NEW rebuild tablespace EASY_MASTER1_TDE;

4) To check TDE Tablespace

QUERY:

SQL> select a.TS#,a.NAME, b.ENCRYPTIONALG, b.STATUS from v$tablespace a,v$encrypted_tablespaces b where a.TS#=b.TS#;

       TS# NAME                           ENCRYPT STATUS

---------- ------------------------------ ------- ----------

       241 EASY_MASTER1                    AES256  NORMAL

       242 EASY_MASTER2                    AES256  NORMAL

       499 CUSTOM_TDE                     AES256  NORMAL

SQL> select a.TS#,a.NAME, b.ENCRYPTIONALG, b.STATUS from v$tablespace a,v$encrypted_tablespaces b where a.TS#=b.TS# and a.NAME='CUSTOM_TDE';

       TS# NAME                           ENCRYPT STATUS

---------- ------------------------------ ------- ----------

       499 CUSTOM_TDE                     AES256  NORMAL

How to change tde algorithm in oracle 19c

Changing the Transparent Data Encryption (TDE) algorithm in Oracle 19c depends on whether you are modifying it for an entire tablespace, a specific column, or setting a new default for the database

1. Change Algorithm for an Existing Tablespace 

To change the algorithm for a tablespace that is already encrypted, you can use the Online Encryption feature. This allows you to re-key the tablespace with a new algorithm without taking it offline

ALTER TABLESPACE <tablespace_name> ENCRYPTION ONLINE USING 'AES256' REKEY;

Supported Algorithms: AES128, AES192, AES256, 3DES168, ARIA128, ARIA192, and ARIA256.

Default: If no algorithm is specified, it defaults to AES128

2. Change Algorithm for a Specific Column

You can modify the algorithm for an encrypted column by using the ALTER TABLE statement.

ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT USING 'AES256');

Note: Column encryption typically defaults to AES192

3. Change the Database Default Algorithm

If you want all future encrypted tablespaces to use a specific algorithm by default (e.g., changing from the standard AES128 to AES256), you can set a hidden initialization parameter

ALTER SYSTEM SET "_TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM" = 'AES256' SCOPE = BOTH;

Requirement: This parameter was integrated into the April 2021 Release Update (19.11) and later; older 19c versions may require Patch 30398099.

Timing: This must be set before the first SET KEY operation or the creation of the keystore to be fully effective for future operations

4. Verify the Current Algorithm

To confirm which algorithm is currently being used, query the dynamic performance views: 

SELECT tablespace_name, encrypted, status FROM dba_tablespaces; -- For detailed encryption info: SELECT * FROM v$encrypted_tablespaces;

For the Database Key

SELECT encryptionalg FROM v$database_key_info;

Question :how to troubleshoot TDE auto login issue

When troubleshooting an Oracle Transparent Data Encryption (TDE) auto-login issue (e.g., wallet/keystore closed or database restart failures), check the items below to pinpoint and resolve the problem

1. Check Keystore Status and Errors

Connect to the database as a user with SYSKM or ADMINISTER KEY MANAGEMENT privileges and check your wallet status:

SELECT WRL_TYPE, WRL_PARAMETER, STATUS, WALLET_TYPE FROM V$ENCRYPTION_WALLET;

Status is CLOSED: The auto-login file (cwallet.sso) is missing, corrupt, or unreadable by the Oracle software owner.

Error ORA-28365: wallet is not open: The auto-login file is not taking effect upon startup, requiring a manual password open

2. Verify Wallet Directory and Permissions

Ensure the sqlnet.ora file points to the correct directory and that permissions are properly set:

Path check: Open $ORACLE_HOME/network/admin/sqlnet.ora and verify the ENCRYPTION_WALLET_LOCATION directory parameter points to the correct wallet path.

File existence: Check that both ewallet.p12 (the password store) and cwallet.sso (the auto-login store) exist in this directory.

Permission check: Ensure the oracle OS user has read, write, and execute permissions on both files

3. Recreate the Auto-Login Wallet

If the .sso file is corrupt, generate a new auto-login file using the orapki command-line utility

orapki wallet create -wallet /path/to/your/wallet -auto_login_local

4. Check for Hidden sqlnet.ora Formatting Issues

Oracle databases are highly sensitive to formatting mistakes in configuration files: [1]

• Make sure there are no extra spaces or typos before the ENCRYPTION_WALLET_LOCATION line in the sqlnet.ora file.
• Ensure all parentheses in the parameter string are properly closed

5. Validate the Master Key

If the password-protected wallet (ewallet.p12) was moved or recreated, the auto-login file will not sync if it was generated before the master key was set. To fix this, you will need to open the password wallet and set/rotate the key again

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY your_wallet_password WITH BACKUP;

For more Details

https://docs.oracle.com/cd/F75778_01/English/install_upgrade_planning/analytics_security/83967.htm#subhead_cannotautocreatewalletora6

PostgreSQL Interview question And Answer 2026

 Question : How upgrade in postgresql

Upgrading PostgreSQL typically refers to moving between major versions (e.g., v16 to v17) to access new features and performance improvements

Popular Upgrade Methods

• pg_upgrade (Recommended): This is the most efficient method as it performs an in-place upgrade without needing a full data dump and restore. Using the --link option can make this process even faster by creating hard links to existing data files.
• Dump and Restore: Involves using pg_dumpall to export the entire database to a script and then importing it into the new version. This is safer but can be very slow for large datasets.
• Logical Replication: Used for upgrades with minimal downtime. You set up a new server with the target version and replicate data from the old server until they are in sync before switching traffic

General Upgrade Checklist

Regardless of the method, follow these critical steps:

1. Backup Data: Always create a full backup of your database before starting any upgrade process.
2. Pre-Upgrade Check: Run pg_upgrade --check to identify potential incompatibilities, such as missing extensions or checksum issues, before performing the actual migration.
3. Update Extensions: After the core database is upgraded, you must manually update extensions using the ALTER EXTENSION ... UPDATE command.
4. Analyze and Maintenance: Once the upgrade is complete, run vacuumdb --all --analyze-in-stages to regenerate vital optimizer statistics for the new version

Cloud Platforms

If you are using a managed service, the process is often simplified:

• AWS RDS/Aurora: Major version upgrades can be initiated directly through the AWS Management Console or CLI.
• Azure Database: Use the "Upgrade" button in the Azure Portal to perform an in-place upgrade.
• DigitalOcean: Managed databases offer an "Upgrade Now" option in the control panel after a compatibility check

For More Details

https://www.postgresql.org/docs/current/pgupgrade.html

https://www.postgresql.org/docs/current/upgrading.html

Question : what is replication in postgresql

PostgreSQL replication is the process of copying data from a primary database server to one or more replica servers. This setup is primarily used for high availability, load balancing (offloading read queries), and disaster recovery

Main Types of Replication

PostgreSQL offers two primary built-in methods for replicating data: [1, 2]

• Streaming (Physical) Replication: This method replicates the entire database cluster byte-by-byte by sending Write-Ahead Log (WAL) records from the primary to the standby server.
  • Best for: Creating exact clones, failover/disaster recovery, and simple read-scaling.
  • Limitation: It replicates everything; you cannot select specific tables, and the standby must be on the same major version of PostgreSQL.
• Logical Replication: This method replicates data at the object level (e.g., individual tables) by decoding WAL records into logical changes.
• Best for: Consolidating multiple databases, replicating between different major versions, or sharing only specific subsets of data.
• Concept: Uses a "Publisher" (the source) and "Subscriber" (the receiver) model

Performance & Consistency Modes

Both physical and logical replication can operate in two modes: [1, 2]

• Asynchronous (Default): The primary commits transactions without waiting for confirmation from replicas. It is faster but carries a small risk of data loss if the primary fails before logs are sent.
• Synchronous: The primary waits for at least one (or more) replicas to confirm they have received the data before finishing a commit. This ensures zero data loss but increases latency for write operations

Comparison Table

Feature [1, 2, 3, 4, 5, 6, 7]	Physical (Streaming)	Logical
Granularity	Entire Database Cluster	Specific Tables
Data Format	Byte-by-byte (Binary)	Decoded SQL changes
Cross-Version Support	No (Versions must match)	Yes
Primary Use Case	Disaster Recovery / HA	Data Migration / Integration
Writes on Replica	No (Read-only standby)	Yes (Subscriber can be writable

Advanced Topologies

• Cascading Replication: A standby server acts as a source for other standby servers to reduce the network load on the primary.
• Active-Active: While not natively fully supported "out of the box" for all scenarios, extensions like pgactive allow multiple nodes to accept writes simultaneously and sync asynchronously

For More Details

https://www.geeksforgeeks.org/postgresql/postgresql-replication/

Question: what is table partition in PostgreSQL

In PostgreSQL, table partitioning is a data management technique that splits a logically large table into smaller physical pieces called partitions. This improves query performance through "partition pruning"—where the database scans only relevant segments—and simplifies maintenance by allowing you to drop entire partitions instead of running expensive DELETE operations

Types of Partitioning

Modern PostgreSQL (v10+) primarily uses Declarative Partitioning, which supports three main strategies

Range Partitioning: Divides data into ranges defined by a key column (e.g., partitioning by date or numeric ID). Bounds are typically inclusive at the lower end and exclusive at the upper end.

List Partitioning: Groups data by explicitly listing which key values appear in each partition (e.g., partitioning by country code or status).

Hash Partitioning: Distributes rows across a fixed number of partitions using a hash of the partition key, which is useful for balancing data evenly when no natural range or list exists

Basic Implementation Steps

To set up a partitioned table, follow this standard workflow

1) Create the Parent Table: Define the structure and specify the strategy using PARTITION BY.

CREATE TABLE orders ( order_id int, order_date date not null ) PARTITION BY RANGE (order_date);

2) Define Child Partitions: Create individual tables that "belong" to the parent for specific value ranges.

CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

3) Insert Data: You insert data directly into the parent table (orders), and PostgreSQL automatically routes the rows to the correct child partition based on the order_date.

Key Benefits & Best Practices

• Performance: Queries with filters on the partition key can skip unrelated partitions, drastically reducing I/O.
• Data Retention: Instead of DELETE FROM table WHERE date < '2023-01-01', you can simply DROP TABLE old_partition, which is near-instant and avoids table bloat.
• Management Tools: For automated partition creation and maintenance, the pg_partman extension is the industry-standard tool.
• Rule of Thumb: Only partition when a table exceeds the physical memory (RAM) of the database server or grows beyond 10–20 GB

For More Details

https://www.postgresql.org/docs/current/ddl-partitioning.html

https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.storage.partition.html

Question: what is type of index in PostgreSQL

PostgreSQL provides several built-in index types, each designed for specific data structures and query patterns. By default, PostgreSQL uses the B-tree index

Core Index Types

• B-Tree: The most common and default index type. It is optimized for data that can be sorted and handles equality and range queries (\(<, \leq, =, \geq, >\)) efficiently.
• Hash: Best for simple equality comparisons (\(=\)). They are generally not as flexible as B-trees but can be faster for exact matches on high-cardinality columns.
• GiST (Generalized Search Tree): An infrastructure for many different indexing strategies. It is commonly used for geometric data types, full-text search, and "nearest-neighbor" queries.
• SP-GiST (Space-Partitioned GiST): Optimized for non-balanced data structures (like quadtrees or radix trees) and data with natural clustering, such as IP addresses or spatial data.
• GIN (Generalized Inverted Index): Designed for composite values where a single row may contain multiple keys. It is the go-to choice for JSONB, arrays, and full-text search.
• BRIN (Block Range Index): Stores summaries (min/max values) for blocks of data. It is extremely space-efficient for very large tables where data is naturally ordered (e.g., a created_at timestamp)

Specialized Indexing Techniques

Beyond the basic types, you can apply these techniques to any compatible index method:

• Expression Indexes: Built on the result of a function or expression rather than just a column value (e.g., lower(email)).
• Partial Indexes: Covers only a subset of a table's rows based on a WHERE clause, saving space and improving speed for specific filtered queries.
• Multicolumn Indexes: Defined on multiple columns. While B-tree and GiST support this, the effectiveness depends on the order of columns in the query.
• Unique Indexes: Enforces that no two rows have the same value. Currently, only B-tree indexes can be declared as unique in PostgreSQL

For More Details

https://www.postgresql.org/docs/current/indexes-types.html

https://www.postgresql.org/docs/current/indexes.html

Question : step by step migration from oracle to postgreSQL

Migrating from Oracle to PostgreSQL involves a structured lifecycle that spans assessment, schema conversion, and data transfer. Most successful migrations utilize specialized tools like Ora2Pg or cloud-native services to handle the significant syntactic and functional differences between the two systems

1. Assessment and Planning

Inventory your source environment to identify the scale and complexity of the migration.

Inventory Objects: List all schemas, tables, indexes, and PL/SQL packages.

Analyze Dependencies: Identify external application dependencies and third-party integrations.

Compatibility Check: Review data types and PL/SQL code that lack direct PostgreSQL counterparts.

Strategy Selection: Decide between a one-time bulk load (for smaller datasets) or incremental replication (CDC) to minimize downtime

2. Schema Conversion

This step translates Oracle-specific structures into PostgreSQL-compatible syntax. [1, 2]

• Use Automated Tools: Tools like Ora2Pg or the AWS Schema Conversion Tool (SCT) can automate the conversion of tables, views, and sequences.
• Manual Refactoring: Hand-convert complex PL/SQL objects—such as triggers, stored procedures, and packages—into PL/pgSQL.
• Formatting Rules: Note that Oracle defaults to uppercase object names, while PostgreSQL defaults to lowercase; apply transformation rules to ensure consistency

3. Data Migration

Move the actual data rows from the source to the target destination. [1]

• Disable Constraints: Temporarily drop foreign keys and secondary indexes on the target PostgreSQL instance to speed up data loading.
• Initial Load: Use tools like COPY (via Ora2Pg) or AWS DMS to perform the initial bulk data transfer.
• Sync Deltas: For large databases, use Change Data Capture (CDC) to keep the target in sync with the live Oracle source until cutover

4. Application Refactoring and Testing

Modify the application layer to interact with the new database. [1]

• Query Updates: Update SQL queries to account for syntax differences, such as removing the DUAL table or replacing ROWNUM with LIMIT.
• Functional Testing: Perform side-by-side validation to ensure schema integrity and identical query results.
• Performance Tuning: Adjust PostgreSQL parameters like shared_buffers and rebuild indexes to match Oracle performance benchmarks

5. Cutover and Post-Migration

The final transition to the live PostgreSQL environment. [1, 2]

• Final Sync: Stop write operations on the Oracle source and migrate the last delta of changed data.
• Switch Traffic: Update application connection strings to point to the PostgreSQL instance.
• Monitoring: Monitor system performance and logs closely during the initial hours of production.
• Cleanup: Re-enable triggers and recreate any dropped constraints or indexes

For More Details

https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.html

Question :How to perform major upgrade in postgresql

To perform a major upgrade in PostgreSQL, you must either migrate your data using a dump/restore process or use the pg_upgrade tool for an in-place migration. Major versions are not backward-compatible at the data directory level, so a simple binary swap is not sufficient

1. Using pg_upgrade (Recommended for Speed)

This is the fastest method, especially for large databases, as it can use hard links to avoid copying data files

Install New Version: Install the new PostgreSQL binaries alongside the old version.

Initialize New Cluster: Use initdb to create a new, empty data directory for the target version.

Stop Both Servers: Ensure neither the old nor the new PostgreSQL service is running.

Run Pre-Check: Execute pg_upgrade --check to identify potential incompatibilities, such as missing extensions or incompatible data types.

Perform Upgrade: Run the pg_upgrade command, specifying the old and new binary and data directories.

• Pro Tip: Use the --link flag to complete the process in minutes by creating hard links rather than copying files.

Post-Upgrade Tasks: Start the new server and run the generated analyze_new_cluster.sh script to rebuild database statistics for optimal performance

2. Logical Migration (Dump and Restore)

This method is more flexible (e.g., when moving to a different OS) but takes longer as it exports and imports all data. [1, 2]

1. Backup: Create a full backup using pg_dumpall (ideally using the pg_dumpall version from the new PostgreSQL release).
2. Shutdown: Stop the old PostgreSQL service.
3. Restore: Initialize the new cluster, start it, and import your data using psql -f outputfile

3. Managed Services (AWS RDS / Azure / Google Cloud) [1]

If you are using a cloud-managed service, the process is simplified through built-in automation:

• AWS RDS: Use the "Modify" button in the console to select a new engine version. RDS will handle the pg_upgrade process and extension updates automatically.
• Azure Database for PostgreSQL: Select "Upgrade" from the server overview. It performs an in-place upgrade using pg_upgrade and automatically takes a snapshot for safety.
• Google Cloud SQL: Supports in-place upgrades but requires checking extension compatibility (e.g., upgrading pg_squeeze first)

E;ssential Safety Checks

• Backups: Always take a manual snapshot or full backup immediately before starting.
• Extensions: Update extensions using ALTER EXTENSION ... UPDATE after the major upgrade is complete.
• Disk Space: Ensure you have at least 10–20% free storage available for logs and temporary metadata

For More Details

https://www.postgresql.org/docs/current/pgupgrade.html

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html

https://www.postgresql.org/docs/current/upgrading.html

Question : Matrics configuration in monitoring tool in postgresql

Configuring metrics for PostgreSQL monitoring typically involves enabling internal extensions, adjusting server parameters, and integrating with external collectors

1. Essential In-Database Extensions

To track detailed query and resource performance, you must enable specific PostgreSQL extensions: [1]

• pg_stat_statements: Essential for tracking execution statistics of all SQL statements (e.g., query runtime, frequency, and resource usage).
• pg_buffercache: Used to examine the shared buffer cache in real-time, helping you monitor cache hit ratios.
• auto_explain: Useful for logging execution plans of slow queries automatically

2. Key Server Parameters for Monitoring

Adjust these in your postgresql.conf file to ensure the database exposes the necessary data:

• shared_preload_libraries: Add 'pg_stat_statements' to this list and restart the server.
• track_activities: Set to on to monitor current commands being executed by any server process.
• track_counts: Set to on to collect statistics on database activity.
• log_duration: Set to on to record the duration of every completed statement.
• track_io_timing: Enables monitoring of block I/O read and write times

3. Core Metrics to Monitor

Focus on these four categories for a comprehensive health check:

• Throughput & Performance: Query latency, buffer cache hit ratio (aim for >95%), and transaction commits vs. rollbacks.
• Resource Utilization: CPU usage, memory (cached vs. dirty), disk I/O, and storage capacity.
• Connections: Total active, idle, and idle-in-transaction connections to prevent resource leaks.
• Reliability: Replication lag, WAL (Write Ahead Log) generation rates, and vacuum/analyze statistics

4. Common Monitoring Tools & Integration

Tool Type [1, 2, 3, 4, 5, 6, 7]	Examples	Configuration Approach
Open Source	Prometheus + postgres_exporter	Uses a dedicated exporter to scrape metrics from pg_stat views and expose them to Prometheus.
Enterprise SaaS	Datadog, New Relic, Sematext	Requires installing an agent on the host that connects to Postgres via a dedicated monitoring user.
Cloud Managed	AWS RDS Insights, Azure Metrics	Pre-configured metrics provided by the cloud vendor; accessible via their native consoles.
Specialized	pgwatch2, pgBadger	pgwatch2 is a self-contained monitoring tool; pgBadger is a log analyzer for detailed performance reports.

For more Details

https://www.postgresql.org/docs/current/monitoring.html

Question : User permission in postgresql

PostgreSQL manages permissions through a unified system of roles, which act as both users and groups. By default, a newly created user has no access to data and must be explicitly granted privileges for every database object they need to interact with

1. The Core Concept: Roles

In PostgreSQL, there is no technical difference between a "user" and a "group"—both are roles. [1]

• Users: Roles with the LOGIN attribute.
• Groups: Roles without the LOGIN attribute, used to group permissions together.
• Superusers: Roles with the SUPERUSER attribute bypass all permission checks

2. Managing Privileges (GRANT & REVOKE)

Privileges are assigned to roles to control what they can do with specific objects like databases, schemas, and tables. [1]

• Common Table Privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER.
• Schema Privileges: USAGE (to access objects within) and CREATE (to create new objects within).
• Database Privileges: CONNECT, CREATE, and TEMPORARY. 

Common SQL Examples:

Common SQL Examples:

sql

-- Grant read-only access to a table
GRANT SELECT ON table_name TO user_name;

-- Grant full access to all tables in a schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_name;

-- Revoke a privilege
REVO

KE UPDATE ON table_name FROM user_name;


3. Role Inheritance
Instead of granting permissions to every individual user, it is best practice to grant permissions to a "group" role and then make users members of that group. 
Users will automatically inherit the permissions of the group

GRANT group_role TO user_name;

4. Default Privileges

By default, permissions only apply to objects that already exist. To ensure a user gets permissions on future objects created in a schema, you must use ALTER DEFAULT PRIVILEGES




ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO user_name;

5. Essential Commands for Inspection [1]
You can use psql meta-commands to check current permissions: [1]
\du: List all roles and their global attributes.
\z or \dp: List access privileges for tables, views, and sequences

For More Details

https://www.postgresql.org/docs/current/ddl-priv.html
https://www.postgresql.org/docs/8.1/user-manag.html
https://www.postgresql.org/docs/current/sql-grant.html
https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/

Question: What is use of xcat and multiplexact

Based on your query, there are two distinct topics here: xCAT (an infrastructure management tool) and MultiXact (a PostgreSQL internal mechanism).

1. xCAT (Extreme Cluster Administration Toolkit)

xCAT is not part of PostgreSQL itself, but rather an open-source tool used for managing high-performance computing (HPC) clusters. [1, 2, 3, 4]

• Use in PostgreSQL: xCAT can use a PostgreSQL database as its backend to store cluster configuration data (nodes, networks, images) instead of the default SQLite.
• Advantage: Using PostgreSQL enables advanced xCAT features like Service Nodes, allowing the management node to handle larger clusters more efficiently through centralized, scalable storage.
• Setup: The pgsqlsetup command is used to configure this database

2. MultiXact (Multi-Transaction Identifier)

MultiXact is a crucial component of PostgreSQL's concurrency control system, specifically for managing row-level locks. [1, 2]

• Use Case: When multiple transactions need to lock the same row simultaneously (e.g., several users trying to update the same record at the same time, or heavy foreign key usage), a single xmin or xmax field in the row header cannot hold all transaction IDs.
• How it works: PostgreSQL replaces the individual transaction ID in the row's xmax field with a MultiXact ID. This ID points to a separate, immutable data structure (stored in pg_multixact) that tracks all transaction IDs that are currently holding a lock on that row.

Key Functions:

• Row-Level Sharing: It allows SELECT FOR SHARE to be used by multiple concurrent users.
• Foreign Key Consistency: It enables high-volume inserts on child tables while maintaining foreign key locks on a parent table.

Performance Impact: High MultiXact activity can create performance issues (contention) if many transactions are waiting on the same MultiXact lock

Summary Table

Feature [1, 2, 3]	What is it?	Primary Use Case in Postgres
xCAT	Cluster Management Software	Using Postgres as the backend DB for managing large HPC clusters.
MultiXact	PostgreSQL Internal Mechanism	Managing multiple concurrent locks on a single row (e.g., hot rows, foreign keys).

Question :What is impact while performing vacuum full in postgresql

VACUUM FULL in PostgreSQL provides maximum space reclamation by rewriting tables to disk and returning free space to the OS, but it acquires an ACCESS EXCLUSIVE lock, which blocks all concurrent reads (SELECT) and writes. It is slow, highly I/O intensive, and requires extra disk space (up to \(2\times\) the table size) to rebuild table indexes

Key Impacts of VACUUM FULL:

• Total Table Lock: The table is inaccessible for all applications for the duration of the command.
• High Resource Usage: It causes heavy disk I/O, which can cripple performance for the rest of the database.
• Table Size Reduction: Physically removes dead tuples and defragments data, shrinking the table to its smallest possible size.
• Index Rebuilding: All indexes on the table are rebuilt, which is time-consuming.

When to Use:
Only for significant bloat, such as after deleting, updating, or inserting a massive amount of data in a maintenance window. [1]

Alternatives for Production:
To avoid downtime, use pg_repack to reclaim space without locking the table

For More Details

https://www.postgresql.org/docs/current/sql-vacuum.html

https://www.postgresql.org/docs/current/routine-vacuuming.html

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Autovacuum_Monitoring.Resolving_Performance.html

Question: How does PITR work in RDS in postgresql

Point-in-Time Recovery (PITR) in Amazon RDS for PostgreSQL works by combining automated daily snapshots with continuous transaction log (WAL) backups to restore your database to any specific second within your retention period

How the Process Works

1. Snapshot Baseline: RDS identifies the most appropriate daily automated snapshot taken before your requested recovery time.
2. Instance Provisioning: A new DB instance is created using this snapshot. It does not overwrite your existing database.
3. WAL Replay: RDS automatically retrieves the Write-Ahead Logs (WAL) stored in S3 and replays them sequentially onto the new instance until it reaches the exact second you specified.

Key Technical Details

• Log Frequency: Transaction logs are uploaded to Amazon S3 every 5 minutes.
• Latest Restorable Time: Because of this upload frequency, the "Latest Restorable Time" is typically within the last 5 minutes of current time.
• Retention Period: You can configure this window for up to 35 days.
• Performance Impact: Replaying transaction logs can take time depending on the volume of changes between the daily snapshot and your recovery point

Prerequisites for PITR

To use PITR, you must have Automated Backups enabled (set to a retention period greater than 0 days). You can initiate a restore via the Amazon RDS Management Console or the AWS CLI

For More Details

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html

https://docs.aws.amazon.com/aws-backup/latest/devguide/point-in-time-recovery.html

How do you perform Performance Tuning in PostgreSQL

PostgreSQL performance tuning is a multi-layered process that involves optimizing database configuration, query design, and system maintenance. It typically follows these core pillar

1. Database Configuration Tuning

You can adjust system-level parameters in postgresql.conf to better utilize your hardware

shared_buffers: Set this to 25%–40% of total system RAM. It determines how much memory is dedicated to caching frequently accessed data.

work_mem: Allocated per sort or hash operation. For OLTP workloads, start with 16MB–64MB; for analytical workloads, increase this to 128MB+ to prevent slow disk-based sorting.

effective_cache_size: Set this to 50%–75% of total RAM. It doesn't allocate memory but helps the query planner estimate how much data is likely cached by the OS.

random_page_cost: If using SSDs, lower this from the default 4.0 to 1.1 to reflect the faster random access speed compared to HDDs.

checkpoint_completion_target: Increase this to 0.9 to spread disk writes over a longer period and avoid "write storms"

2. Query and Index Optimization

Slow queries are often the primary cause of performance issues. [1]

• EXPLAIN ANALYZE: Prepend this to any query to see its execution plan, identify bottlenecks (like sequential scans), and view actual run times.
• Index Strategy:
  • Create B-tree indexes for frequently joined or filtered columns.
  • Use specialized indexes like GIN for full-text search and JSONB, or GiST for spatial data.
  • Drop unused or duplicate indexes to reduce write overhead.
• Query Refactoring: Replace subqueries with JOIN operations and avoid SELECT * to reduce unnecessary data retrieval

3. Maintenance and Monitoring

• VACUUM and ANALYZE: Regularly run these (or rely on Autovacuum) to reclaim storage space from "dead tuples" and update table statistics used by the query planner.
• pg_stat_statements: Enable this module to track execution statistics for all SQL statements executed on the server, allowing you to find the "top 10" most resource-intensive queries.
• Connection Pooling: Use tools like PgBouncer if your application requires hundreds of concurrent connections, as native PostgreSQL connections have high overhead. [1, 2, 3]

For a quick starting point based on your specific hardware, tools like PGTune can generate an optimized configuration file automatically.

For more details

https://www.postgresql.org/docs/current/performance-tips.html

Question : If a table is dropped in PostgreSQL RDS and affecting production, how will you handle it?

If a table is dropped in a production PostgreSQL RDS instance, the primary recovery method is to use Point-in-Time Recovery (PITR) to restore the data to a new temporary instance and then move the missing table back to production

Immediate Action Steps

1. Identify the Drop Time: Determine the exact timestamp just before the DROP TABLE command was executed.
2. Perform Point-in-Time Recovery (PITR):
• Navigate to the Amazon RDS Console and select your original instance.
• Under Actions, choose Restore to Point in Time.
• Select a restorable time slightly before the table was dropped.
• This process creates a new, separate DB instance containing the dropped table.
3. Extract the Dropped Table:
   • Connect to the newly restored instance using standard tools like pg_dump.
   • Run a command to export only the specific table:
     pg_dump -h [restored-instance-endpoint] -t [table_name] [database_name] > table_restore.sql.
4. Restore to Production:
   • Import the .sql file back into your original production instance:
     psql -h [production-instance-endpoint] [database_name] < table_restore.sql.
5. Verify and Cleanup: Ensure all indexes, constraints, and permissions for the restored table are correct. Once verified, delete the temporary restored RDS instance to avoid unnecessary costs

Alternative Recovery Methods

• Delayed Read Replica: If you have a delayed read replica configured, you can pause replication before the DROP statement reaches the replica and extract the table from there.
• AWS Backup: If you use AWS Backup, you can restore from a specific recovery point directly through the AWS Backup console

Question : What will you do if WAL corruption happens while starting the database? in PostgreSQL

If Write-Ahead Log (WAL) corruption occurs while starting a PostgreSQL database, it usually prevents the server from starting because it cannot replay the logs to achieve a consistent state. [1]

Here is a structured, step-by-step approach to handle this situation, ranging from safe restoration methods to last-resort recovery. 

1. Immediate Safety Steps

• Stop PostgreSQL Immediately: Stop the service to prevent further damage: sudo systemctl stop postgresql.
• Backup the Data Directory: Make a full file-system-level copy of the entire data directory (PGDATA) before trying anything else. This provides a fallback if recovery attempts make the corruption worse. [1, 2, 3, 4, 5]

2. Best Approach: Restore from Backup (PITR)

If you have regular backups (e.g., using pgBackRest or pg_basebackup), this is the safest and preferred method. 

• Restore the last valid full backup.
• Perform Point-in-Time Recovery (PITR) to replay the WAL logs up to the moment just before the corruption

3. Last Resort: Using pg_resetwal [1]

If no backups are available, you can use the pg_resetwal utility (known as pg_resetxlog in PostgreSQL 9.6 and earlier) to forcibly reset the WAL logs and make the database start. [1, 2]

Warning: This method skips normal crash recovery and may lead to data inconsistencies or loss of recent transactions. [1]

Steps to use pg_resetwal:

1. Locate the utility: It is usually located in /usr/lib/postgresql/<version>/bin/.
2. Run as postgres user: Run the command as the database system user.
3. Perform the reset:

pg_resetwal /var/lib/pgsql/data # If the command complains about a missing pg_control file, use the -f (force) option: pg_resetwal -f /var/lib/pgsql/data ```

1. Start PostgreSQL: sudo systemctl start postgresql.

4. Post-Recovery Action

After using pg_resetwal, the database is in a fragile state. [1, 2]

• Dump Data Immediately: Use pg_dumpall to export the data.
• Rebuild the Cluster: Re-initialize the cluster using initdb and import the dumped data. Do not continue using the database where pg_resetwal was run.

Summary of Causes

• fsync=off: A sudden power loss with this setting enabled is a common cause.
• Hardware Failure: Failing disks or file system issues.
• Disk Space Exhaustion: pg_wal directory filling up, resulting in partial writes

Question : Explain WAL corruption and recovery approach

Write-Ahead Logging (WAL) is the cornerstone of PostgreSQL reliability, ensuring ACID compliance by recording changes sequentially to disk before they are applied to the actual data files. WAL corruption occurs when these log files are damaged or lost, preventing the database from ensuring data integrity during restart

Understanding WAL Corruption

WAL corruption usually manifests as an inability to start the PostgreSQL server, often accompanied by error messages in the logs indicating CRC errors, invalid WAL records, or missing files.

Common Causes:

• Hardware Failures: Disk corruption or malfunctioning storage controllers.
• Forced Power Loss: Power outages occurring while the operating system is flushing WAL data to disk.
• Full Disk: If the pg_wal directory becomes full, PostgreSQL cannot write new logs and may crash, potentially damaging the current segment.
• File System Issues: Inconsistent file systems or faulty storage subsystems

Symptoms:

• PostgreSQL fails to start.
• Logs show PANIC: could not locate required WAL record or invalid record length. [1, 2, 3, 4]

WAL Recovery Approach

PostgreSQL is designed to handle "normal" crashes (e.g., OS failure) automatically via Crash Recovery, which replays WAL records from the last checkpoint. However, in cases of actual WAL corruption, the recovery approach depends on the severity

1. Standard Crash Recovery (Automatic)

Upon starting after a crash, PostgreSQL automatically:

• Identifies the last successful CHECKPOINT.
• Replays WAL logs (REDO phase) from that point forward, reapplying changes to the data files.
• Ensures that only fully committed transactions are reflected in the database.

2. Handling Missing/Corrupted WAL Files

If a WAL file needed for recovery is corrupted or missing, the server cannot start. The options are: [1, 2]

• Restore from Backup (Recommended): Use a base backup taken previously and replay the archived WAL files using Point-in-Time Recovery (PITR) to a moment just before the corruption occurred.
• pg_resetwal (Last Resort): If no backups exist and the data must be salvaged, use pg_resetwal to manually clear the WAL files.
• WARNING: This tool bypasses normal crash recovery, resulting in potential data loss or inconsistency.
• Usage: pg_resetwal -f /path/to/data/directory.
• Aftermath: Run VACUUM FULL or REINDEX immediately afterward to identify potential corruption within tables

Proactive WAL Corruption Prevention

• Enable Data Checksums: Run initdb --data-checksums to detect corruption early.
• Monitor WAL Disk Usage: Ensure the pg_wal partition does not fill up.
• Regular Backups: Use tools like pgBackRest to take continuous backups and archive WAL files for PITR.
• Use Reliable Storage: Use redundant hardware and ensure fsync is enabled to guarantee WAL data is written to stable storage

For More Details

hhttps://www.postgresql.org/docs/current/wal-intro.html

https://dev.to/mohhddhassan/how-postgresql-wal-actually-works-and-why-everything-depends-on-it-2g81

Question: ? How do Security Groups and Subnets help in PostgreSQL in RDS

In Amazon RDS for PostgreSQL, Security Groups and Subnets work together to create a layered defense system that controls who can reach your database and how it is isolated within your network

Question: How to troubleshooting in postgresql

Troubleshooting PostgreSQL generally falls into three main categories: connection issues, performance bottlenecks, and resource management. 

1. Connection Issues

If you cannot connect to your database, follow these steps to isolate the cause:

• Verify the Service Status: Check if the PostgreSQL server is actually running using commands like pg_ctl status or ps aux | grep postgres.
• Check Port Accessibility: Ensure the server is listening on the correct port (default is 5432). Use Telnet or Nmap to verify if the port is open and reachable.
• Review pg_hba.conf: This file controls host-based authentication. Ensure your client's IP address and chosen authentication method (e.g., md5 or scram-sha-256) are permitted.
• Listen Address: Verify that postgresql.conf has the listen_addresses parameter set to '*' or the specific IP you are trying to reach, rather than just localhost

2. Performance & Slow Queries

If queries are running slowly, use these built-in tools to identify bottlenecks: [1]

• Analyze Query Plans: Use the EXPLAIN ANALYZE command to see the execution plan and identify where the database is spending time.
• Check for Table Bloat: Heavily updated tables can become "bloated" with dead rows. Use VACUUM or ANALYZE to reclaim space and update statistics for the query planner.
• Enable pg_stat_statements: This PostgreSQL extension tracks execution statistics of all SQL statements, allowing you to find the most time-consuming queries across your entire system.
• Identify Locks: Use the pg_locks and pg_stat_activity views to find "zombie" sessions or long-running transactions that are blocking other operations

3. Resource & System Errors

Physical constraints can often cause the database to fail or stop accepting writes:

• Out of Disk Space: If the disk is full, PostgreSQL cannot write Write-Ahead Logs (WAL), which can lead to service failure. Monitor the pg_wal directory specifically.
• Memory Issues: "Out of Memory" (OOM) errors occur when queries exceed settings like work_mem or when the OS OOM killer terminates the process due to total system exhaustion.
• Log Inspection: Always check the PostgreSQL error logs for specific error codes (e.g., 53300 for too many connections) to get a clear reason for a crash or rejection

For More Details

https://www.site24x7.com/learn/postgres-troubleshooting-guide.html

https://www.postgresql.org/docs/7.0/trouble.htm