<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Kerian Amin N on Medium]]></title>
        <description><![CDATA[Stories by Kerian Amin N on Medium]]></description>
        <link>https://medium.com/@ngukerian?source=rss-51dd4e621844------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*ny6D1ZvvUhZrAO2V</url>
            <title>Stories by Kerian Amin N on Medium</title>
            <link>https://medium.com/@ngukerian?source=rss-51dd4e621844------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 11:31:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@ngukerian/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[When WAL Archiving Failed and PostgreSQL Started Eating Its Own Headroom]]></title>
            <link>https://medium.com/@ngukerian/when-wal-archiving-failed-and-postgresql-started-eating-its-own-headroom-dbccb0ffa048?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/dbccb0ffa048</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[performance-tuning]]></category>
            <category><![CDATA[database-reliability]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[disaster-recovery]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Mon, 01 Jun 2026 02:37:44 GMT</pubDate>
            <atom:updated>2026-06-01T02:37:44.459Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FYSLHCZ-8xkRqg_ItrbC3Q.png" /></figure><p>At 01:37, the database was still online, replication was still moving, and the application team had no obvious customer-facing alarms. The first meaningful signal came from storage: <em>`pg_wal`</em> was growing too fast, backup freshness had stalled, and the archive path had stopped making forward progress.</p><p>This is one of the more dangerous PostgreSQL production failures because it does not begin as an outage. It begins as silent loss of recovery posture. Then, if writes continue and WAL cannot be archived successfully, the primary starts retaining segments in <em>`pg_wal`</em> until storage pressure becomes the next incident.</p><h4>Production symptoms</h4><p>The first symptoms were subtle:<br>- Backup jobs stopped advancing.<br>- WAL volume on the primary grew beyond its normal working range.<br>- Noisy but non-fatal archive errors appeared in the PostgreSQL logs.<br>- The database looked healthy enough to fool anyone who only checked query latency.</p><h4>What I checked first</h4><pre>SELECT archived_count,<br>       last_archived_wal,<br>       last_archived_time,<br>       failed_count,<br>       last_failed_wal,<br>       last_failed_time,<br>       stats_reset<br>FROM pg_stat_archiver;</pre><p>Then I checked the archiver configuration itself.</p><pre>SHOW archive_mode;<br>SHOW archive_command;<br>SHOW wal_level;</pre><p>For a pgBackRest-based system, the command usually looks something like this:</p><p><em>conf<br>archive_command = ‘pgbackrest — stanza=prod archive-push %p’</em></p><h4>What failed</h4><p>The immediate failure was that <em>`archive_command`</em> stopped completing successfully, which means PostgreSQL could not mark finished WAL files as safely archived.</p><h4>Immediate mitigation</h4><p>I would do the following in order:<br>1. Repair the archive destination or pgBackRest path.<br>2. Confirm the archive command can succeed outside the failing production loop.<br>3. Force a WAL switch to trigger a new archival attempt.<br>4. Re-check <em>`pg_stat_archiver`</em> for forward progress.</p><pre>SELECT pg_switch_wal();</pre><h4>Dangerous shortcut to avoid</h4><p>One dangerous edge case with async pgBackRest archiving is queue overflow behavior. If <em>`archive-push-queue-max`</em> is exceeded in some async configurations, it can acknowledge WAL as archived and then drop it, which breaks PITR continuity and requires a new base backup to restore full recoverability.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dbccb0ffa048" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[When Disk Filled and PostgreSQL Started Fighting for Breath]]></title>
            <link>https://medium.com/@ngukerian/when-disk-filled-and-postgresql-started-fighting-for-breath-2683b2c6fee0?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/2683b2c6fee0</guid>
            <category><![CDATA[database-engineering]]></category>
            <category><![CDATA[sre]]></category>
            <category><![CDATA[database-reliability]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database-administration]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Mon, 25 May 2026 06:14:58 GMT</pubDate>
            <atom:updated>2026-05-25T06:27:51.302Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gpu8xeI8c0F74bhzmLM-3Q.png" /></figure><p>At 14:08, the primary did not fail cleanly. It started shedding small symptoms first: slower commits, checkpoint pressure, then failed writes and log messages about insufficient space. By the time the disk alert fired, the real problem was no longer “storage is high” but “PostgreSQL can no longer safely keep writing WAL and dirty pages.”</p><p>This is the failure mode that catches teams off guard because the table data itself is often still intact. The danger is that a filled WAL disk can force a panic shutdown, and a filled data disk can block useful activity long before anyone expects the instance to be considered unhealthy.</p><h4>What I saw first</h4><p>The first clue was a gradual rise in filesystem usage, then an inflection where <em>`pg_wal`</em> started consuming the remaining free space much faster than normal.</p><h4>What I checked</h4><pre>df -h<br>du -sh $PGDATA/pg_wal</pre><p>Inside PostgreSQL, I checked WAL and checkpoint behavior:</p><pre>SHOW checkpoint_timeout;<br>SHOW max_wal_size;<br>SHOW min_wal_size;<br>SHOW wal_compression;</pre><p>If the archive path was involved, I also inspected <em>`pg_stat_archiver`</em> immediately.</p><pre>SELECT archived_count,<br>       last_archived_wal,<br>       last_archived_time,<br>       failed_count,<br>       last_failed_wal,<br>       last_failed_time<br>FROM pg_stat_archiver;</pre><h4>The immediate fix</h4><p>The first priority is to create breathing room without damaging recoverability. The safe move is to move or delete unrelated large files, expand the filesystem, or attach additional storage so PostgreSQL can continue writing WAL. If there is a clearly broken archive pipeline, I would fix that before forcing more checkpoints or attempting aggressive cleanup.</p><pre>SELECT pg_switch_wal();</pre><h4>Lessons for high-write systems</h4><p>- A disk-full PostgreSQL incident is often a WAL problem before it is a data problem.<br>- <em>`pg_wal`</em> growth is expected, but uncontrolled growth is an outage in progress.<br>- Archiving failures are dangerous because they prevent safe WAL recycling.<br>- Disk alarms should be early, not reactive.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2683b2c6fee0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[When One Long Transaction Blocked Everything Else]]></title>
            <link>https://medium.com/@ngukerian/when-one-long-transaction-blocked-everything-else-583774fea3ed?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/583774fea3ed</guid>
            <category><![CDATA[production-incident]]></category>
            <category><![CDATA[database-performance]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[lockcontention]]></category>
            <category><![CDATA[postgresdba]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Mon, 18 May 2026 06:06:16 GMT</pubDate>
            <atom:updated>2026-05-18T06:17:21.481Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rCBySCj-xL4vqTLJzIcImQ.png" /></figure><p>At 10:41, the incident did not look like a global outage. It looked like a random mix of timeouts, slow writes, and a few angry dashboards. The real problem was a single long-running transaction that held a lock long enough to create a queue behind it, and that queue started dragging unrelated application flows into the same failure.</p><p>By 10:46, it was obvious this was not a connection shortage and not a CPU issue. The database had plenty of sessions, but many of them were waiting on one blocking transaction. That is a very different failure mode: the backend is alive, but forward progress is serialized behind a lock holder.</p><h4>What I saw first</h4><p>The symptom pattern was:<br>- Update requests timing out.<br>- A few <em>`SELECT … FOR UPDATE`</em> paths hanging behind them.<br>- Connection pools filling with sessions that were technically alive but doing no useful work.<br>- Application retries making the queue worse instead of better.</p><h4>The checks I ran</h4><pre>SELECT pid,<br>       usename,<br>       state,<br>       wait_event_type,<br>       wait_event,<br>       now() - query_start AS duration,<br>       left(query, 120) AS query<br>FROM pg_stat_activity<br>WHERE state &lt;&gt; &#39;idle&#39;<br>ORDER BY duration DESC;</pre><p>Then I moved to <em>`pg_locks`</em> and the blocking relationship itself.</p><pre>SELECT blocked.pid AS blocked_pid,<br>       blocked_activity.usename AS blocked_user,<br>       blocking.pid AS blocking_pid,<br>       blocking_activity.usename AS blocking_user,<br>       blocked_activity.query AS blocked_query,<br>       blocking_activity.query AS blocking_query<br>FROM pg_locks blocked<br>JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked.pid<br>JOIN pg_locks blocking ON (<br>  blocking.locktype = blocked.locktype<br>  AND blocking.database IS NOT DISTINCT FROM blocked.database<br>  AND blocking.relation IS NOT DISTINCT FROM blocked.relation<br>  AND blocking.page IS NOT DISTINCT FROM blocked.page<br>  AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple<br>  AND blocking.transactionid IS NOT DISTINCT FROM blocked.transactionid<br>  AND blocking.pid &lt;&gt; blocked.pid<br>  AND blocking.granted<br>)<br>JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking.pid<br>WHERE NOT blocked.granted;</pre><h4>Root cause</h4><p>The blocker was a transaction that stayed open far too long while holding a row-level lock on a hot table. Once that lock was in place, every other session touching the same rows or higher-level object had to wait.</p><h4>The immediate fix</h4><p>The immediate response was to terminate the blocker, not the victims.</p><pre>SELECT pg_terminate_backend(&lt;blocking_pid&gt;);</pre><h4>The prevention work</h4><p>The durable fix is mostly about reducing lock hold time:<br>- Keep transactions short.<br>- Never hold a transaction open across network calls or application pauses.<br>- Set <em>`lock_timeout`</em> so waiting statements fail fast instead of clogging the queue.<br>- Use <em>`statement_timeout`</em> as a broader guardrail for runaway statements.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=583774fea3ed" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[When a Connection Storm Took PostgreSQL Offline]]></title>
            <link>https://medium.com/@ngukerian/when-a-connection-storm-took-postgresql-offline-988f536719cd?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/988f536719cd</guid>
            <category><![CDATA[sre]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[scalability]]></category>
            <category><![CDATA[performance-tuning]]></category>
            <category><![CDATA[database-engineering]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Mon, 11 May 2026 01:39:07 GMT</pubDate>
            <atom:updated>2026-05-11T01:39:07.531Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3z2U8nIdxgdK9QuzzsNCLw.png" /></figure><p>At 09:03, the first alert was not “database down.” It was application timeouts and a sharp spike in connection failures. By 09:05, PostgreSQL was still alive, but it was no longer able to absorb new client sessions at the rate the application was creating them.</p><p>The pattern was classic connection storm behavior: one service restarted, its workers all reconnected at once, and two other services followed the same pattern because they had similar retry logic and no backoff discipline. Within minutes, the server hit its practical connection ceiling, and every new session had to fight for a slot.</p><h4>What I saw first</h4><p>The symptoms were noisy but specific:<br>- <em>`too many connections` </em>errors.<br>- Spiky application latency across unrelated endpoints.<br>- <em>`pg_stat_activity`</em> filled with a high count of short-lived or idle sessions.<br>- The database CPU was not the main story at first; the bigger issue was process and connection churn.</p><h4>Why PostgreSQL hates connection storms</h4><p>PostgreSQL uses one backend process per connection, and <em>`max_connections`</em> is a real hard limit. When the application opens too many connections too quickly, the server has no graceful way to invent more backend capacity on the fly.</p><p>That is why “just raise max_connections” is usually the wrong instinct. In real production systems, the better answer is usually connection pooling and tighter client behavior, not a bigger hard cap.</p><h4>The checks I ran</h4><pre>SELECT state,<br>       usename,<br>       application_name,<br>       count(*)<br>FROM pg_stat_activity<br>GROUP BY state, usename, application_name<br>ORDER BY count(*) DESC;</pre><p>Then I compared the live count to <em>`max_connections`</em>.</p><pre>SELECT<br>  (SELECT count(*) FROM pg_stat_activity) AS current_connections,<br>  (SELECT setting::int FROM pg_settings WHERE name = &#39;max_connections&#39;) AS max_connections,<br>  (SELECT setting::int FROM pg_settings WHERE name = &#39;superuser_reserved_connections&#39;) AS reserved_connections;</pre><h4>Root cause</h4><p>The root cause was a broken pooling strategy on the application side. One service was effectively opening direct sessions instead of reusing a bounded pool, and another had a PgBouncer deployment in session pooling mode, which does not reduce backend usage the way transaction pooling does.</p><h4>The immediate fix</h4><p>The immediate response was to free slots by terminating the worst offenders and stop the retry storm.</p><pre>SELECT pg_terminate_backend(pid)<br>FROM pg_stat_activity<br>WHERE state = &#39;idle&#39;<br>  AND usename = &#39;problematic_user&#39;<br>  AND pid != pg_backend_pid();</pre><h4>The durable fix</h4><p>The durable fix was to stop treating PostgreSQL like an elastic socket pool:<br>- put PgBouncer in transaction pooling mode for OLTP traffic,<br>- cap application pool sizes aggressively,<br>- add exponential backoff and jitter to reconnect logic,<br>- and set a default <em>`statement_timeout`</em> so sessions do not sit around forever.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=988f536719cd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[When a High-Update Table Turned into a Bloat Factory]]></title>
            <link>https://medium.com/@ngukerian/when-a-high-update-table-turned-into-a-bloat-factory-aff7fc9738c6?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/aff7fc9738c6</guid>
            <category><![CDATA[autovacuum]]></category>
            <category><![CDATA[mvcc]]></category>
            <category><![CDATA[database-performance]]></category>
            <category><![CDATA[scalability]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Mon, 04 May 2026 02:14:06 GMT</pubDate>
            <atom:updated>2026-05-04T02:14:06.942Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*of0Cq8sBdteZKgj-bYzZTw.png" /></figure><p>At 08:17, the first symptom was not a crash. It was a slow shift in behavior: point lookups on `<em>accounts</em>` that used to return in under 10 ms started taking 200 to 800 ms, then spiked over a second during peak traffic. CPU was not the loudest signal at first; I saw rising buffer churn, increasing heap fetches, and the planner leaning harder on indexes that should have been cheap.</p><p>By 08:24, the application team was reporting elevated latency on customer reads and a measurable increase in I/O. The scary part was that nothing “looked broken” in the obvious sense. Autovacuum was running, the table was online, and there was no single runaway query dominating the top of `<em>pg_stat_statements</em>`. What was broken was the physical shape of the table itself.</p><h4>What I saw first</h4><p>The production symptom set was classic high-bloat behavior:<br>- Slow reads on a table with frequent updates.<br>- More heap pages touched per query.<br>- Index scans doing extra work because the underlying heap had become sparse and fragmented.<br>- Autovacuum activity that existed, but clearly could not keep up with the write rate.</p><p>The table in question was a multi-billion-row `<em>accounts</em>` table with a heavy update pattern: balance flags, last_seen timestamps, KYC state, and risk metadata were all updated repeatedly. In MVCC terms, each UPDATE didn’t overwrite a row in place; it created a new version and left the old one behind as a dead tuple until VACUUM could clean it up.</p><h4>Why MVCC hurt here</h4><p>MVCC is what keeps PostgreSQL concurrency sane, but it also means a busy update table can become a graveyard of dead row versions if cleanup lags. The old tuple versions still occupy heap space, still have to be skipped during scans, and still contribute to table and index bloat until vacuum reclaims them.</p><p>That mattered because the planner was now paying for all the dead weight. Sequential scans had more pages to read, index scans had more heap visits, and cache efficiency dropped because the hot working set no longer fit neatly in memory.</p><h4>The checks I ran</h4><p>I started with `<em>pg_stat_user_tables</em>` and `<em>pg_stat_all_tables</em>`, because I wanted to know whether the table was actually churning or whether I was just seeing a noisy query pattern.</p><pre>SELECT relname,<br>       n_live_tup,<br>       n_dead_tup,<br>       n_tup_ins,<br>       n_tup_upd,<br>       n_tup_del,<br>       last_autovacuum,<br>       last_autoanalyze<br>FROM pg_stat_user_tables<br>WHERE relname = &#39;accounts&#39;;</pre><p>The pattern was ugly: `<em>n_tup_upd</em>` was enormous, `<em>n_dead_tup</em>` was high, and `<em>last_autovacuum</em>` was not keeping pace with the rate of change.</p><p>I also captured execution plans before and after the slowdown with `<em>EXPLAIN (ANALYZE, BUFFERS)</em>`.</p><pre>EXPLAIN (ANALYZE, BUFFERS)<br>SELECT account_id, balance, status<br>FROM accounts<br>WHERE account_id = $1;</pre><p>Before the regression, the query touched a small number of buffers and completed quickly. After the bloat accumulated, the same plan had to read far more heap pages, and the buffer hit pattern got worse even though the SQL text had not changed.</p><h4>Why autovacuum missed it</h4><p>Autovacuum did not fail; it just lost the race. On a huge, frequently updated table, the default thresholds can be too conservative for the workload. The table was updated continuously, dead tuples accumulated faster than vacuum cycles completed, and the next cleanup cycle arrived too late to prevent bloat from compounding.</p><h4>The remediation</h4><p>The response had four parts:<br>1. Run a manual `<em>VACUUM (ANALYZE)</em>` on the worst table.<br>2. Lower autovacuum thresholds for that table.<br>3. Reduce fillfactor<em> </em>so updates have room to stay on the same page more often.<br>4. Revisit partitioning, because a table this hot should not have all write churn concentrated in one giant heap forever.</p><pre>ALTER TABLE accounts SET (<br>  fillfactor = 80,<br>  autovacuum_vacuum_scale_factor = 0.05,<br>  autovacuum_vacuum_threshold = 5000,<br>  autovacuum_analyze_scale_factor = 0.02<br>);</pre><h4>Lessons for high-write systems</h4><p>- A fast update path can create a slow read path if vacuum cannot keep up.<br>- `<em>n_dead_tup</em>` is a signal, not a curiosity.<br>- `<em>EXPLAIN (ANALYZE, BUFFERS)</em>` tells you whether the slowdown is really physical.<br>- Large, hot tables often need custom autovacuum settings instead of defaults.<br>- Fillfactor is part of the design on update-heavy tables.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=aff7fc9738c6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[“Understanding FSM and VM in PostgreSQL: How Free Space and Visibility Maps Optimize Performance”]]></title>
            <link>https://medium.com/@ngukerian/understanding-fsm-and-vm-in-postgresql-how-free-space-and-visibility-maps-optimize-performance-2f2ac32b8113?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/2f2ac32b8113</guid>
            <category><![CDATA[dba]]></category>
            <category><![CDATA[database-internals]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[performance-tuning]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Thu, 26 Jun 2025 20:24:22 GMT</pubDate>
            <atom:updated>2025-08-06T13:51:44.107Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4Nciqh4q-xpH2y56Jk_4OQ.png" /></figure><h3>📘 Scenario: You’re a DBA Diagnosing Bloat and Planning Vacuum Strategies</h3><p>Imagine you’re a PostgreSQL DBA managing a growing OLTP system. Recently, you’ve noticed <strong>table bloat</strong>, increasing autovacuum activity, and storage usage climbing disproportionately. Before throwing more storage at the problem, you want to dig into PostgreSQL’s physical storage structures specifically:</p><ul><li><strong>FSM (Free Space Map)</strong></li><li><strong>VM (Visibility Map)</strong></li></ul><p>These files are crucial in helping PostgreSQL manage space efficiently and avoid unnecessary I/O, but they’re often misunderstood or overlooked.</p><p>Let’s break down <strong>what they do</strong>, <strong>why they’re important</strong>, and <strong>how to identify and locate</strong> them in your system.</p><h4>🧠 What Are FSM and VM in PostgreSQL?</h4><h4>🧩 1. Free Space Map (FSM)</h4><ul><li><strong>Purpose</strong>: Keeps track of <strong>free space</strong> available in data pages.</li><li><strong>Use Case</strong>: Helps the database <strong>find free space</strong> during INSERT or UPDATE operations without scanning the whole table.</li><li><strong>File Suffix</strong>: .fsm</li><li><strong>Impact</strong>: Efficient reuse of space reduces bloat and speeds up inserts.</li></ul><h4>🕶️ 2. Visibility Map (VM)</h4><ul><li><strong>Purpose</strong>: Tracks which pages have tuples that are <strong>all visible</strong> to all active transactions.</li><li><strong>Use Case</strong>: Helps <strong>VACUUM</strong> and <strong>index-only scans</strong> skip unnecessary pages.</li><li><strong>File Suffix</strong>: .vm</li><li><strong>Impact</strong>: Accelerates VACUUM and enables <strong>index-only scans</strong>, improving performance.</li></ul><h4>🧭 Where Are FSM and VM Files Stored?</h4><h4>Default Location</h4><ul><li>In the pgdata/base/&lt;db_oid&gt;/ directory.</li><li>File names follow this format: &lt;relfilenode&gt;.fsm or &lt;relfilenode&gt;.vm.</li></ul><h4>Tablespace Location</h4><ul><li>If a table is stored in a <strong>custom tablespace</strong>, files are in:</li></ul><pre>&lt;tablespace_location&gt;/PG_&lt;major_version&gt;_&lt;catalog_version&gt;/tblspc_oid/&lt;relfilenode&gt;.fsm or .vm</pre><h4>🔍 How to Identify FSM and VM for a Table</h4><p>Here’s how to find and inspect FSM and VM files for a specific table:</p><h4>🔧 Step 1: Get the OID of the Database</h4><pre>SELECT oid, datname FROM pg_database WHERE datname = &#39;your_db&#39;;</pre><h4>🔧 Step 2: Get the relfilenode and reltablespace of the Table</h4><pre>SELECT relname, relfilenode, reltablespace<br>FROM pg_class<br>WHERE relname = &#39;your_table&#39;;</pre><p>relfilenode: base name of the file (without suffix)</p><p>reltablespace:</p><ul><li>0 means default tablespace (under pgdata/base/&lt;db_oid&gt;/)</li><li>Otherwise, use pg_tablespace to find path</li></ul><h4><em>🔧 Step 3: Determine Path</em></h4><p>For default tablespace:</p><pre>cd $PGDATA/base/&lt;db_oid&gt;/<br><br>ls -l &lt;relfilenode&gt;.fsm &lt;relfilenode&gt;.vm</pre><p>For custom tablespace:</p><pre>SELECT spcname, oid FROM pg_tablespace;</pre><p>Then:</p><pre>cd /your/tablespace/location/PG_&lt;major_version&gt;_&lt;catalog_version&gt;/&lt;db_oid&gt;/<br><br>ls -l &lt;relfilenode&gt;.fsm &lt;relfilenode&gt;.vm</pre><h4>📊 Why Should You Care?</h4><pre>Metric               FSM Helps With             VM Helps With<br><br>Insert Speed         ✅ (reuses pages)          ❌<br><br>Vacuum Efficiency    ❌                         ✅ (skips clean pages)<br><br>Index-Only Scans     ❌                         ✅<br><br>Space Reuse          ✅                         ❌<br><br>Bloat Control        ✅ (indirectly)           ✅ (by reducing unnecessary VACUUMs)</pre><h4>🧼 Bonus: How FSM/VM Can Get Outdated</h4><ul><li>FSM and VM are <strong>not WAL-logged</strong>, so they may get <strong>out-of-sync</strong> after a crash.</li><li>Running a <strong>VACUUM FULL</strong> or REINDEX can rebuild them cleanly.</li></ul><h4>✅ Summary</h4><p>Understanding FSM and VM is critical for:</p><ul><li>Diagnosing bloat</li><li>Improving performance</li><li>Reducing I/O during common operations</li></ul><p>By learning how to locate and interpret these files, you get a behind-the-scenes view of how PostgreSQL optimizes space and visibility.</p><h4>🚀 Pro Tip:</h4><p>Consider enabling <strong>pageinspect</strong> and <strong>pgstattuple</strong> extensions to analyze page-level statistics and verify what FSM and VM reflect at runtime.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2f2ac32b8113" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Demystifying OIDs in PostgreSQL: How to Identify Objects in Tablespaces Using oid2name]]></title>
            <link>https://medium.com/@ngukerian/demystifying-oids-in-postgresql-how-to-identify-objects-in-tablespaces-using-oid2name-7ef33794f256?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/7ef33794f256</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[backend]]></category>
            <category><![CDATA[databasetips]]></category>
            <category><![CDATA[open-source-tools]]></category>
            <category><![CDATA[oid]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Thu, 26 Jun 2025 19:38:30 GMT</pubDate>
            <atom:updated>2025-06-26T19:38:30.828Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JAMcT0VkQQD3OIjvUO0mJA.png" /></figure><p><em>“What is this </em><em>16893 file in my tablespace?&quot;. </em>If you’ve ever asked that question while managing PostgreSQL storage — you’re not alone.</p><p>PostgreSQL stores objects like tables and indexes using internal <strong>Object Identifiers (OIDs)</strong> — which means that in your data directory or tablespaces, filenames aren’t descriptive. They’re just numbers.</p><p><em>In this post, I’ll break down</em></p><ul><li>What OIDs are and why they matter.</li><li>How to map them to actual tables and indexes.</li><li>How to use the oid2name utility for real-world troubleshooting and object mapping.</li></ul><h3>📌 What Are OIDs in PostgreSQL?</h3><p><strong>OIDs (Object Identifiers)</strong> are unique identifiers automatically assigned to many internal PostgreSQL objects such as</p><ul><li>Tables</li><li>Indexes</li><li>Sequences</li><li>Functions</li><li>Types</li></ul><p>PostgreSQL stores data on disk using these OIDs. For example, inside the data directory (or a custom tablespace), files are named like this</p><pre>16384<br><br>16893</pre><p><em>But </em><strong><em>which file corresponds to which table or index?</em></strong><em> That’s where </em><em>oid2name comes in.</em></p><h3>🔧 Meet oid2name: Your OID Decoder</h3><p>oid2name is a command-line utility that <strong>translates OIDs into readable object names</strong>, using system catalog data.</p><p>It’s perfect for</p><ul><li>Investigating tablespace file contents.</li><li>Identifying large or orphaned objects.</li><li>Debugging storage issues.</li><li>Mapping physical files to logical objects.</li></ul><p><strong><em>Note:</em></strong><em> You must have access to the server and PostgreSQL tools to run </em><em>oid2name.</em></p><h4>🔍 Real-World Use Case: Mapping Files in a Tablespace</h4><p>Let’s say your PostgreSQL server is running low on disk space. You check the tablespace directory and find a bunch of files named with numbers. You want to know</p><p>“Which tables or indexes are these, and do I need them?”.</p><p><em>Here’s the step-by-step approach.</em></p><h4>✅ Step 1: Find Tablespace Location</h4><p>Run this query to list all tablespaces and their paths</p><pre>SELECT spcname, pg_tablespace_location(oid)<br>FROM pg_tablespace;</pre><p><em>This shows where each tablespace is physically located.</em></p><h4>✅ Step 2: Identify Database OID</h4><p>Each database has an OID, which is used to name subdirectories under tablespaces.</p><pre>SELECT oid, datname<br>FROM pg_database;</pre><h3>✅ Step 3: Explore the Filesystem</h3><p>Navigate into the tablespace and find the folder for your database OID</p><pre>cd /var/lib/postgresql/15/main/pg_tblspc/&lt;tablespace_oid&gt;/PG_15_202307181/db_oid/<br>ls -l</pre><p><em>Here you’ll see files like</em></p><pre>16893<br>16901<br>16901_fsm<br>16901_vm</pre><p><em>These are relation files (tables, indexes, etc.).</em></p><h4>✅ Step 4: Use oid2name to Decode Objects</h4><p>Run the tool</p><pre>oid2name -d your_database_name -s -f</pre><p><em>Example output</em></p><pre>OID     Name           Tablespace   File Node<br>---------------------------------------------<br>16893   sales_data     pg_default   16893<br>16901   sales_idx      pg_default   16901</pre><p><em>Now you can confidently say</em><br>✔️ 16893 → sales_data table<br>✔️ 16901 → sales_idx index</p><h4>✅ Step 5: Confirm Object Type (Optional)</h4><p>If you want to dig deeper into the object type</p><pre>SELECT relname, relkind<br>FROM pg_class<br>WHERE oid = 16893;</pre><p>Result interpretation</p><ul><li>&#39;r&#39; = Table</li><li>&#39;i&#39; = Index</li><li>&#39;S&#39; = Sequence</li></ul><h3>🧼 Bonus: Detecting Orphaned Files</h3><p>Sometimes, after crashes or bugs, PostgreSQL may leave files behind that no longer correspond to existing catalog entries. These can consume space unnecessarily.</p><p>To detect them:</p><ol><li>List files in the filesystem.</li><li>Use oid2name and pg_class to check if they are still valid.</li><li>Proceed carefully if considering deletion — always back up!</li></ol><h3>🔐 Important Tips</h3><ul><li>Never delete OID files manually unless you’re 100% sure.</li><li>Use oid2name only on systems you have full access to.</li><li>Prefer working on backups when analyzing orphaned or unknown files.</li><li>Large tables may have segment files like 16893.1, 16893.2, etc.</li></ul><h3>🧠 Conclusion</h3><p>PostgreSQL’s use of OIDs makes it efficient but opaque. Tools like oid2name <strong>open the black box</strong>, allowing DBAs and engineers to:</p><ul><li>Map file storage to real data.</li><li>Investigate issues.</li><li>Monitor disk usage with clarity.</li></ul><p>If you’re working with PostgreSQL storage, OIDs aren’t just internals — they’re essential tools for observability and control.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7ef33794f256" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unlocking the Power of Tablespaces in PostgreSQL]]></title>
            <link>https://medium.com/@ngukerian/unlocking-the-power-of-tablespaces-in-postgresql-63447d4a7ff4?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/63447d4a7ff4</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[databaseoptimizationincms]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[performance-tuning]]></category>
            <category><![CDATA[dba-tips]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Thu, 26 Jun 2025 18:51:14 GMT</pubDate>
            <atom:updated>2025-06-26T18:51:14.874Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k8AlRFDOLUMQk2rn8Y3W-g.png" /></figure><h4><strong>How to Create a Custom Tablespace, Point Databases to It, and Set It as the Default for All Future Databases</strong></h4><p><em>In this guide, I’ll show you</em></p><ul><li>💡 What tablespaces are and why they matter</li><li>🛠️ How to create one from a new mount point (outside pgdata)</li><li>⚙️ How to set it as the <strong>default tablespace</strong> for all new databases using template1</li></ul><h4>📦 What Is a Tablespace in PostgreSQL?</h4><p>A <strong>tablespace</strong> in PostgreSQL is a location on the filesystem where the actual data files for tables, indexes, and databases can reside.</p><h4>💡 Why use tablespaces?</h4><ul><li>You can place your data on <strong>faster disks</strong> (e.g., SSDs).</li><li>You get <strong>isolation</strong>: separate different projects or tenants.</li><li>You gain <strong>flexibility</strong> in disk layout and I/O management.</li><li>It simplifies <strong>storage planning</strong> for high-throughput workloads.</li></ul><p><em>By default, PostgreSQL uses </em><em>pg_default, which resides in the </em><em>PGDATA directory. But you don’t have to be limited by that!</em></p><h4>🧭 Goal: Use a Custom Tablespace for All Future Databases</h4><p>We’ll go through:</p><ol><li>Creating a new directory/mount point.</li><li>Defining the tablespace.</li><li>Creating a database using it.</li><li>Setting the tablespace as default for all future DBs by altering template1.</li></ol><h4>🔧 Step 1: Prepare a New Mount Point</h4><p>✅ Use this if you have a faster disk (SSD, EBS volume, etc.).</p><p>⚠️ PostgreSQL must have full access to this directory.</p><pre>sudo mkdir -p /mnt/pg_tblspc/my_custom_ts<br><br>sudo chown postgres:postgres /mnt/pg_tblspc/my_custom_ts</pre><p><em>Optional: mount a fast volume to it</em></p><pre>sudo mount /dev/sdb1 /mnt/pg_tblspc/my_custom_ts</pre><h4>🏗️ Step 2: Create the Tablespace in PostgreSQL</h4><p><em>Log in as the </em><em>postgres user</em></p><pre>psql -U postgres</pre><p><em>Create the tablespace</em></p><pre>CREATE TABLESPACE my_custom_ts LOCATION &#39;/mnt/pg_tblspc/my_custom_ts&#39;;</pre><p><em>To view existing tablespaces</em></p><pre>\db</pre><h4>🧪 Step 3: Create a Database That Uses It</h4><pre>CREATE DATABASE testdb TABLESPACE my_custom_ts;<br><br>\c testdb</pre><p><em>Create a test table</em></p><pre>CREATE TABLE test_table(id SERIAL PRIMARY KEY, name TEXT);<br><br>INSERT INTO test_table(name) VALUES (&#39;Hello Tablespace!&#39;);</pre><p><em>You should see new files under </em><em>/mnt/pg_tblspc/my_custom_ts/.</em></p><h4>🧬 Step 4: Alter template1 to Use the New Tablespace</h4><p>By default, PostgreSQL copies template1 when creating new databases. So we’ll recreate template1 using our custom tablespace.</p><h4>⚠️ Warning</h4><p>This will affect <strong>all future databases</strong> created without a specific TABLESPACE clause.</p><p><strong>Steps:</strong></p><ol><li>Connect to a different DB (postgres) — not template1</li></ol><pre>psql -U postgres -d postgres</pre><p>2. Drop and recreate template1 using the new tablespace</p><pre>UPDATE pg_database SET datistemplate = FALSE WHERE datname = &#39;template1&#39;;<br><br>DROP DATABASE template1;<br><br>CREATE DATABASE template1 WITH TEMPLATE = template0 TABLESPACE = my_custom_ts;<br><br>UPDATE pg_database SET datistemplate = TRUE WHERE datname = &#39;template1&#39;;</pre><h4>✅ Test: Create a New Database</h4><pre>CREATE DATABASE newdb;</pre><p><em>Check its tablespace</em></p><pre>SELECT datname, spcname<br>FROM pg_database d<br>LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid<br>WHERE datname = &#39;newdb&#39;;</pre><p><em>Result should show </em><em>my_custom_ts.</em></p><h4>🔄 Optional: Revert to pg_default</h4><p>Want to switch back?</p><pre>UPDATE pg_database SET datistemplate = FALSE WHERE datname = &#39;template1&#39;;<br><br>DROP DATABASE template1;<br><br>CREATE DATABASE template1 WITH TEMPLATE = template0 TABLESPACE = pg_default;<br><br>UPDATE pg_database SET datistemplate = TRUE WHERE datname = &#39;template1&#39;;</pre><h4>🎯 Conclusion</h4><p>Setting up a custom default tablespace in PostgreSQL gives you:</p><ul><li>Better control over storage performance</li><li>Isolation between datasets or environments</li><li>Flexibility for future scaling</li></ul><p>If you’re running workloads on different tiers of storage, or simply want to maximize speed — this technique is a must-have in your PostgreSQL toolkit.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=63447d4a7ff4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Demystifying PostgreSQL MVCC]]></title>
            <link>https://medium.com/@ngukerian/demystifying-postgresql-mvcc-f42fbfa9b227?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/f42fbfa9b227</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[mvcc]]></category>
            <category><![CDATA[concurrency]]></category>
            <category><![CDATA[database-internals]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Tue, 24 Jun 2025 22:23:45 GMT</pubDate>
            <atom:updated>2025-06-25T14:37:06.657Z</atom:updated>
            <content:encoded><![CDATA[<p>(Multi‑Version Concurrency Control)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WlLW2-mlac7OGveZ6T-rMA.png" /></figure><p>“Readers don’t block writers, and writers don’t block readers.”</p><p>That single line is the promise of MVCC, and it’s why PostgreSQL can serve many concurrent sessions without pause locks. But how does it actually work? Let’s lift the hood.</p><p><strong>1. One Logical Row, Many Physical Versions</strong></p><p>PostgreSQL never overwrites a row in‑place. Every UPDATE or DELETE produces a brand‑new tuple (row version) and silently marks the old one as dead.<br><em>Internally, each physical tuple carries two hidden system columns</em></p><pre>Column             Meaning<br><br>xmin               Transaction ID (XID) that created the tuple<br><br>xmax               Transaction ID that deleted or superseded the tuple (NULL if still &quot;alive&quot;)</pre><p><em>Think of xmin/xmax as “valid‑from” and “valid‑until” timestamps — only using monotonically increasing transaction IDs instead of wall‑clock time.</em></p><p><strong>2. Snapshots: Your Personal Time Machine</strong></p><p><em>When a transaction starts (implicit or explicit, see §4), PostgreSQL takes a snapshot</em></p><pre>* xmin of the snapshot - the smallest still‑active XID when the snapshot is taken.<br><br>* xmax of the snapshot - one more than the largest XID that was already assigned at start time.<br><br>* active XID list - the set of transactions that were in progress at the snapshot&#39;s birth.</pre><p><em>With that information the executor can decide row‑by‑row</em></p><pre>A tuple is visible to me if<br><br>tuple.xmin ≤ snapshot.xmax             - it was already born<br><br>AND<br><br> tuple.xmin NOT IN snapshot.active     - it was committed<br><br>AND<br><br> (tuple.xmax IS NULL                   - not deleted<br><br> OR tuple.xmax &gt; snapshot.xmax         - deleted later (in my future)<br><br> OR tuple.xmax IN snapshot.active)     - deleter is still running</pre><p><em>The key idea: every query inside the transaction sees the exact same snapshot. Your data freezes in time, no matter how long the transaction lives.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3BmzvtIGIKhHNQCCOlUlAQ.png" /><figcaption>Fig: Explanatory sketch of MVCC</figcaption></figure><p><strong>3. Walking Through an Example</strong></p><p><em>Below is a distilled version</em></p><pre>ID       Name(logical)       xmin       xmax       Status       Created by<br><br>1        Alice               99         100        dead         Txn 99<br><br>1        Alice Smith         100        101        dead         Txn 100<br><br>1        Alice Johnson       101        NULL       live         Txn 101</pre><p><em>Timeline</em></p><pre>T1───Txn 100 begins───T2───Txn 101 begins───T3───Txn 102 begins───►</pre><pre>Transaction           Snapshot window               What it reads for ID=1<br><br>Txn 100 (xid=100)     xmax = 100 (sees ≤ 100)       Alice (xmin 99)<br><br>Txn 101 (xid=101)     xmax = 101 (sees ≤ 101)       Alice Smith (xmin 100)<br><br>Txn 102 (xid=102)     xmax = 102 (sees ≤ 102)       Alice Johnson (xmin101)</pre><p><em>No blocking, no waits — just different worlds rendered from the same table.</em></p><p><strong>4. Implicit vs. Explicit Transactions</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/664/1*s_2xWJbl4eUavdGzTA33aw.png" /></figure><p><em>🔹 Tip: Even in an implicit transaction, you can force consistent reads across multiple statements with START TRANSACTION ISOLATION LEVEL REPEATABLE READ;.</em></p><p><strong>5. Dead Tuples, VACUUM, and bloat</strong></p><p><em>Dead tuples linger until VACUUM (auto or manual) marks the space reusable.</em><br><em>They are not “garbage” in the sense of corruption — they’re proof that MVCC happened.</em></p><pre>Too many dead tuples ⇒ table &amp; index bloat ⇒ slower I/O<br><br>Monitor with pg_stat_user_tables.dead_tup<br><br>Use VACUUM (VERBOSE) for insight; VACUUM FULL or CLUSTER to reclaim space physically</pre><p><strong>6. Gotchas &amp; Best Practices</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/658/1*xZLutLb_qwIFVqR1ysMoOg.png" /></figure><p><strong>7. Quick Demo (try it yourself)</strong></p><pre>-- Session A<br>BEGIN;<br>UPDATE users SET name = &#39;Alice Smith&#39; WHERE id = 1;  -- XID 100<br>-- keep the transaction open<br><br>-- Session B (new psql window)<br>BEGIN;<br>SELECT * FROM users WHERE id = 1;                    -- sees original &#39;Alice&#39;<br>COMMIT;<br><br>-- Session C<br>BEGIN;<br>UPDATE users SET name = &#39;Alice Johnson&#39; WHERE id = 1;-- waits? NO – creates 3rd version<br>COMMIT;<br><br>-- Session A finally<br>COMMIT;  -- may fail with serialization if higher ISO level</pre><p><em>Notice: no blocking reads, versions proliferate, and subsequent readers each get a consistent view.</em></p><p><strong>Key Takeaways</strong></p><pre>1.  MVCC relies on row versions plus snapshot isolation - not row locks - for consistency.<br><br>2.  Every transaction, implicit or explicit, owns a snapshot window defined by XIDs.<br><br>3.  xmin / xmax columns encode row‑level visibility rules.<br><br>4.  Dead tuples are normal; VACUUM is the janitor.<br><br>5.  Keep transactions short, monitor bloat, and trust PostgreSQL to handle concurrency gracefully.</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f42fbfa9b227" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Securing PostgreSQL with SSL/TLS: A Comprehensive Implementation Guide]]></title>
            <link>https://medium.com/@ngukerian/securing-postgresql-with-ssl-tls-a-comprehensive-implementation-guide-9b210be21c3f?source=rss-51dd4e621844------2</link>
            <guid isPermaLink="false">https://medium.com/p/9b210be21c3f</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[ssl]]></category>
            <category><![CDATA[database-security]]></category>
            <category><![CDATA[tech-tips]]></category>
            <category><![CDATA[security]]></category>
            <dc:creator><![CDATA[Kerian Amin N]]></dc:creator>
            <pubDate>Tue, 24 Jun 2025 20:21:34 GMT</pubDate>
            <atom:updated>2025-06-24T20:21:34.402Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Nt0D1MoCKx-bhy2X3eczzA.png" /></figure><p>To implement SSL/TLS encryption for PostgreSQL, follow this battle-tested procedure based on industry best practices and real-world deployment scenarios. This guide covers certificate generation, server configuration, client setup, and troubleshooting.</p><p><strong>🔄 Connection Flow: What Actually Happens</strong></p><pre>1. Client connects with sslmode=verify-full (e.g. psql or pgAdmin)<br><br>2. Server sends its server.crt and certificate chain<br><br>3. Client checks:<br> **Is this cert signed by my root.crt?<br><br> **Does CN or SAN match the hostname I requested?<br><br>4. If valid, PostgreSQL checks pg_hba.conf for a hostssl rule<br><br>5. Then authentication (SCRAM/MD5 or cert) runs inside the encrypted tunnel</pre><p>All actions are performed by the Postgres user on the database server.</p><p><strong>🔐 Step 1: Server Certificate Creation</strong><br><em>Create SAN configuration (san.cnf)</em></p><pre>cat &gt; san.cnf &lt;&lt;EOF<br>[ req ]<br>default_bits = 2048<br>prompt = no<br>default_md = sha256<br>req_extensions = req_ext<br>distinguished_name = dn<br><br>[ dn ]<br>CN = yourdb.yourdomain.local<br><br>[ req_ext ]<br>subjectAltName = @alt_names<br><br>[ alt_names ]<br>DNS.1 = yourdb.yourdomain.local<br>IP.1 = your.server.ip.address</pre><p><em>SAN ensures hostname/IP validation during handshake</em></p><p>📄 <strong>Step 2: Certificate Authority Setup</strong><br><em>Generate Root CA and Key (Valid 10 years)</em></p><pre>openssl req -new -nodes -text -keyout root.key -out root.csr \<br> -subj &quot;/CN=YourOrg-RootCA&quot;<br><br>chmod og-rwx root.key<br><br>openssl x509 -req -in root.csr -text -days 3650 \ <br> -extensions v3_ca -extfile /etc/ssl/openssl.cnf \<br> -signkey root.key -out root.crt</pre><p><em>Root CA acts as the trust anchor for all certificates.</em></p><p><strong>Generate server certificate</strong></p><pre>openssl req -new -nodes -newkey rsa:2048 -keyout server.key \<br> -out server.csr -config san.cnf<br><br>chmod og-rwx server.key<br><br>openssl x509 -req -in server.csr -CA root.crt -CAkey root.key \<br> -CAcreateserial -out server.crt -days 365 -extensions req_ext \<br> -extfile san.cnf</pre><p><strong>Verify the date</strong></p><pre>openssl x509 -in server.crt -noout -text | grep -A2 &quot;Validity&quot;</pre><p>🛠<strong> Step 3: PostgreSQL Configuration</strong><br><em>PostgreSQL Step: Initialize the cluster</em></p><pre>pg_ctl initdb -D ${PGDATA} </pre><p><em>Copy certificates to data directory</em></p><pre>cp server.key server.crt root.crt ${PGDATA}/<br><br>chown postgres:postgres ${PGDATA}/{server.*,root.crt}<br><br>chmod 600 /pgdata/16/sutadev/server.key<br><br>chmod 644 /pgdata/16/sutadev/server.crt /pgdata/16/sutadev/root.crt</pre><p><em>Enable SSL in postgresql.conf</em></p><pre>sed -i &quot;s/^#listen_addresses =.*/listen_addresses = &#39;*&#39;/&quot; &gt;&gt; ${PGDATA}/postgresql.conf<br><br>sed -i &quot;s/^#ssl =.*/ssl = on/&quot; ${PGDATA}/postgresql.conf<br><br>echo &quot;ssl_cert_file = &#39;server.crt&#39;&quot; &gt;&gt; ${PGDATA}/postgresql.conf<br><br>echo &quot;ssl_key_file = &#39;server.key&#39;&quot; &gt;&gt; ${PGDATA}/postgresql.conf<br><br>echo &quot;ssl_ca_file = &#39;root.crt&#39;&quot; &gt;&gt; ${PGDATA}/postgresql.conf<br><br>echo &quot;log_line_prefix =&#39;%t [%p]: user=%u db=%d client=%h ssl=%r &#39;&quot; &gt;&gt; ${PGDATA}/postgresql.conf<br><br>***sed -i &quot;s|^#log_statement = . *|log_statement = &#39;all&#39;|&quot; &gt;&gt; ${PGDATA}/postgresql.conf*** (Optional)</pre><p><em>Enforce SSL in pg_hba.conf</em></p><pre>echo &quot;hostssl all all 0.0.0.0/0 scram-sha-256&quot; &gt;&gt; ${PGDATA}/pg_hba.conf</pre><p>🔍 <strong>Step 4: Validation and Testing</strong><br><em>Server-side checks</em></p><pre>pg_ctl -D ${PGDATA} restart<br><br>ss -tunlp | grep 5432 # Should show TLSv1.2/1.3<br><br>tail -f ${PGDATA}/log/postgresql.log | grep SSL</pre><p><em>Client connection in database</em></p><pre>psql &quot;host=yourdb.yourdomain.local dbname=postgres \ <br>  user=postgres sslmode=verify-full&quot;</pre><p><em>Verify-full ensures both certificate validity and hostname matching.</em></p><p>🖥 <strong>Step 5: Application Integration</strong><br><em>pgAdmin4 configuration</em></p><pre>Place root.crt on client machine<br><br>chmod 600 ~/PATH/toPostgreSQL/root.crt</pre><p><em>Create new server connection</em></p><pre>Host: yourdb.yourdomain.local<br><br>SSL Mode: verify-full<br><br>Root Certificate: path/to/root.crt</pre><p><em>JDBC connection string</em></p><pre>jdbc:postgresql://yourdb.yourdomain.local:5432/dbname?ssl=true&amp;sslmode=verify-full</pre><p>🚨 <strong>Troubleshooting Common Issues</strong><br><em>Host resolution failures</em></p><pre>LOG: could not accept SSL connection: host not resolved</pre><p><strong>Solutions</strong></p><pre>Use IP address instead of hostname in connection strings<br><br>Add DNS entry to client&#39;s /etc/hosts (or Windows hosts file): your.server.ip.address yourdb.yourdomain.local<br><br>Verify SAN includes both DNS and IP in server certificate</pre><p><em>Certificate errors</em></p><pre>FATAL: could not load server certificate file &quot;server.crt&quot;</pre><p><strong>Diagnosis</strong></p><pre>Verify file permissions: chmod 600 server.key<br><br>Confirm paths in postgresql.conf match actual locations<br><br>Check certificate validity: openssl x509 -in server.crt -noout -text</pre><p>🔄<strong> Certificate Rotation Procedure</strong><br><em>Zero-downtime rotation</em></p><pre>pg_ctl -D ${PGDATA} stop<br><br>mv ${PGDATA}/server.* ${PGDATA}/root.crt /backup/<br><br>cp new_{server.key,server.crt,root.crt} ${PGDATA}/<br><br>chown postgres:postgres ${PGDATA}/{server.*,root.crt}<br><br>chmod 600 ${PGDATA}/server.key<br><br>pg_ctl -D ${PGDATA} start</pre><p><em>Test connections immediately after rotation</em></p><p>📝 <strong>Best Practices</strong><br><em>Logging Configuration</em></p><pre>echo &quot;log_line_prefix = &#39;%t [%p]: user=%u db=%d client=%h ssl=%r&#39;&quot; \<br> &gt;&gt; ${PGDATA}/postgresql.conf</pre><p>Logs SSL negotiation status for auditing</p><pre>Annual Rotation: Set calendar reminders for certificate expiration<br><br>Client Education: Distribute root.crt via secure channels with usage instructions<br><br>SAN Validation: Always include both DNS and IP in server certificates</pre><p>This implementation provides military-grade encryption for PostgreSQL while maintaining operational flexibility. Regular certificate rotation and access logging ensure ongoing compliance with federal security requirements. For production environments, consider integrating with enterprise PKI systems for automated certificate management.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9b210be21c3f" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>