When managing PostgreSQL databases in production environments, one of the biggest challenges is identifying which queries and processes are consuming the most resources. Although PostgreSQL has great tools like pg_stat_statements, we often need historical… read more
How to enable Object and Session level Auditing in PostgreSQL
When you manage production databases, sooner or later the time comes when security and compliance requirements will knock on your door. The question is not whether you will need to record who did what, but how you will do it… read more
How to upgrade PostgreSQL to a newer version
Migrating PostgreSQL from an older version to a newer one is a critical maintenance process that requires care to ensure data integrity. In this article, we will look at the steps for a smooth upgrade of a database… read more
How to maintain Indexes and Statistics in a PostgreSQL database
As we have seen in previous articles, the smooth operation of a database is not only ensured by backup and recovery mechanisms, but also requires constant maintenance. Over time and the execution of daily transactions, tables and… read more
How to enable Data Masking in PostgreSQL
In this article we will see how we can implement data anonymization and masking in a PostgreSQL 14 database using the PostgreSQL Anonymizer extension. We will cover the installation process, setting up masking rules, as well as the specifics that… read more
How to do Bulk Export Import in PostgreSQL (pg_dump)
For those of us who come from the world of Oracle and Data Pump (expdp/impdp), in this article we will look at the corresponding PostgreSQL tools pg_dump and pg_restore. These tools allow us to run these processes with parallel threads,… read more
How to find Missing Indexes in PostgreSQL
Those of us who come from the world of SQL Server are used to the Missing Index DMVs (sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats), which give us the CREATE INDEX command ready along with Avg User Impact and Equality/Inequality/Include columns. In PostgreSQL, the default… read more
How to do Performance Testing in PostgreSQL with pgbench
pgbench is one of the most useful tools for PostgreSQL. It is used for benchmarking and stress testing the database, simulating multiple simultaneous transactions (TPS). In this article we will see how we can set it up, run the… read more
How to find alerts in PostgreSQL through Plain Text Logs with a query
As Database Administrators, we are used to reading alerts as normal tables from v$diag_alert_ext with simple SQL queries in environments like Oracle. In PostgreSQL, things are a little different, by default its logs are plain text… read more
