<?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 Matheus dos Santos on Medium]]></title>
        <description><![CDATA[Stories by Matheus dos Santos on Medium]]></description>
        <link>https://medium.com/@valentim.dba?source=rss-6ebe01756f42------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*SQP4RNV_nRL8BfnJOOmhBA.png</url>
            <title>Stories by Matheus dos Santos on Medium</title>
            <link>https://medium.com/@valentim.dba?source=rss-6ebe01756f42------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 04:31:28 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@valentim.dba/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[PostgreSQL Point-in-Time Recovery (PITR): The Complete Step-by-Step Guide to Data Resilience]]></title>
            <link>https://medium.com/@valentim.dba/postgresql-point-in-time-recovery-pitr-the-complete-step-by-step-guide-to-data-resilience-dd306ba55f4a?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/dd306ba55f4a</guid>
            <category><![CDATA[infrastructure]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[backup-and-restore]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Sat, 21 Feb 2026 23:59:05 GMT</pubDate>
            <atom:updated>2026-02-22T00:00:10.486Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SlmRC9rXXifOHo8Mk8daSA.jpeg" /></figure><p>Data is the lifeblood of any modern application, and as a Database Engineer, your most critical responsibility isn’t just storing that data — it’s ensuring you can recover it when things go wrong. While standard backups are great, <strong>Point-in-Time Recovery (PITR)</strong> is the “Time Machine” of the database world, allowing you to restore your database to a specific second, effectively undoing accidental deletions or corruption.</p><p>In this guide, we will walk through the entire lifecycle of a PostgreSQL recovery strategy: from performing a professional-grade full backup to executing a precise PITR restoration.</p><h3>Phase 1: Preparing the Infrastructure</h3><p>Before we can recover, we must have a solid foundation. We start by ensuring we have a database to back up and a structured environment for our backup files.</p><h3>1. Initializing the Database</h3><p>First, let’s create a dedicated database that will serve as our source for the full backup.</p><pre>-- Creating a database to test the full backup process<br>CREATE DATABASE full;</pre><h3>2. Executing the Full Backup</h3><p>We will use the pg_basebackup utility. This tool creates a binary copy of the database cluster. In the script below, we automate the directory creation and use specific flags to ensure the backup is compressed and includes all necessary transaction logs (WAL).</p><pre># Define the date and backup path<br>DATA=$(date +%F)<br>BACKUP_DIR=/data/backup/full/$DATA</pre><pre># Create the directory and ensure the postgres user has access<br>mkdir -p $BACKUP_DIR<br>chown postgres:postgres $BACKUP_DIR</pre><pre># Perform the backup<br># -Ft: Tar format | -z: Gzip compression | -D: Destination<br># -P: Progress reporting | -X stream: Include WAL files | -R: Recovery config<br>sudo -u postgres pg_basebackup -h localhost -U bkp_agent \<br>-Ft -z \<br>-D $BACKUP_DIR \<br>-P -X stream -R </pre><pre>if [ $? -eq 0 ]; then<br>  echo &quot;Backup completed successfully in $BACKUP_DIR&quot;<br>else<br>  echo &quot;Backup error!&quot;<br>fi</pre><p>The -X stream flag is vital here; it ensures that the WAL files generated during the backup process are included, providing a consistent snapshot.</p><h3>Phase 2: Configuring Continuous Archiving</h3><p>To achieve PITR, you need more than just a full backup; you need a continuous stream of Write-Ahead Logs (WAL). This requires modifying the postgresql.conf (or postgresql.auto.conf) to enable archiving.</p><h3>1. Vital Parameters for PITR</h3><p>These settings tell PostgreSQL to copy completed WAL segments to a secure location.</p><pre># Essential settings for continuous archiving<br>wal_level = replica<br>archive_mode = on<br>archive_command = &#39;test ! -f /data/backup/log/%f &amp;&amp; cp %p /data/backup/log/%f&#39;</pre><p>The archive_command checks if the file already exists before copying, preventing accidental overwrites.</p><h3>2. Generating Traffic and Validating</h3><p>After creating a new database for testing the PITR, we force a WAL switch to ensure the data is moved to our archive directory.</p><pre>-- Create a database specifically for PITR testing<br>CREATE DATABASE pitr;<br><br>-- Force the database to switch to a new WAL file<br>SELECT pg_switch_wal();</pre><p>You can validate that the archiving is working by listing the contents of your log directory:</p><pre>ls -lisah /data/backup/log/</pre><h3>Phase 3: The Restoration Procedure</h3><p>When disaster strikes, or when you need to recover to a specific timestamp, follow these steps with precision.</p><h3>1. Stopping the Service and Clearing Data</h3><p>Before restoring files, the database service must be stopped.</p><pre># Stop the PostgreSQL service<br>/usr/pgsql-18/bin/pg_ctl stop -D /var/lib/pgsql/18/data</pre><p>To ensure a clean restore, we move the current data directories out of the way. <strong>Always move instead of deleting</strong> — this provides a safety net if you need to reference the “corrupt” state later.</p><pre># Move the current data directory to a timestamped backup folder<br>mv /var/lib/pgsql/18/data /var/lib/pgsql/18/data_OLD_$(date +%F_%H%M)<br>mkdir /var/lib/pgsql/18/data<br><br># Repeat the process for your Tablespaces (Data and Index)<br>mv /data/tbsdata /data/tbsdata_OLD_$(date +%F_%H%M)<br>mkdir /data/tbsdata<br>mv /index/tbsidx /index/tbsidx_OLD_$(date +%F_%H%M)<br>mkdir /index/tbsidx</pre><h3>2. Extracting the Backup Files</h3><p>Now we extract the tarballs from our full backup into their respective locations. Note that the OIDs (like 16388) refer to specific tablespaces.</p><pre># Define variables for convenience<br>BACKUP_DIR=&quot;/data/backup/full/2026-02-21&quot;<br>PGDATA=&quot;/var/lib/pgsql/18/data&quot;<br><br># Extract the main cluster base<br>tar -xzf $BACKUP_DIR/base.tar.gz -C $PGDATA<br># Extract Tablespaces to their original paths<br>tar -xzf $BACKUP_DIR/16388.tar.gz -C /data/tbsdata<br>tar -xzf $BACKUP_DIR/16389.tar.gz -C /index/tbsidx<br># Extract WAL files into the pg_wal directory<br>mkdir -p $PGDATA/pg_wal<br>tar -xzf $BACKUP_DIR/pg_wal.tar.gz -C $PGDATA/pg_wal/</pre><h3>3. Setting Permissions</h3><p>PostgreSQL is strict about file permissions. The data directory should typically be owned by the postgres user with 700 permissions.</p><pre>chown -R postgres:postgres $PGDATA /data/tbsdata /index/tbsidx<br>chmod 700 $PGDATA /data/tbsdata /index/tbsidx</pre><h3>Phase 4: Point-in-Time Configuration</h3><p>This is the most critical step. We need to tell PostgreSQL how to find the archived logs and exactly when to stop the recovery.</p><pre># Define the restore command to fetch archived WALs<br>echo &quot;restore_command = &#39;cp /data/backup/log/%f %p&#39;&quot; &gt;&gt; $PGDATA/postgresql.auto.conf</pre><pre># Define the point in time you wish to recover to<br>echo &quot;recovery_target_time = &#39;2026-02-21 03:40:00&#39;&quot; &gt;&gt; $PGDATA/postgresql.auto.conf</pre><pre># Create the recovery signal file to trigger recovery mode on startup<br>touch $PGDATA/recovery.signal</pre><pre># Final permission check for config files<br>chown postgres:postgres $PGDATA/postgresql.auto.conf $PGDATA/recovery.signal<br>chmod 600 $PGDATA/postgresql.auto.conf</pre><h3>Phase 5: Bringing the Database Online</h3><p>Start the server and monitor the process.</p><pre>/usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data start</pre><h3>Validation</h3><p>To verify if the database is still in recovery mode or has finished, run:</p><pre>SELECT pg_is_in_recovery();</pre><p>A result of f (false) indicates that recovery is complete and the database is ready for connections. You should also inspect the logs to see the &quot;Archive recovery is complete&quot; message.</p><pre>2026-02-21 04:35:18.768 LOG: Archive recovery is complete<br>2026-02-21 04:35:18.826 LOG: Database system is ready to accept connections</pre><h3>Final Cleanup</h3><p>Once the restore is validated, remove the temporary recovery files to prevent confusion during future restarts.</p><pre>rm /var/lib/pgsql/18/data/backup_label.old<br>rm /var/lib/pgsql/18/data/tablespace_map.old</pre><p>Finally, remember to remove or comment out the recovery parameters in postgresql.auto.conf to return to normal operation.</p><h3>Conclusion: Building a Resilient Data Strategy</h3><p>Point-in-Time Recovery (PITR) is the ultimate safety net for any PostgreSQL administrator. While the setup requires meticulous attention to detail — specifically regarding WAL archiving and directory permissions — the peace of mind it provides is invaluable. By mastering PITR, you transition from simply “having backups” to possessing a robust <strong>Disaster Recovery Strategy</strong> capable of handling everything from accidental DROP TABLE commands to complex data corruption.</p><p>The key to a successful recovery is not just the backup itself, but the <strong>validation</strong> of the process. A backup strategy is only truly effective if it is regularly tested in a staging environment. By following the steps outlined in this guide, you ensure that your data remains resilient, your downtime is minimized, and your “time machine” is always ready for use.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dd306ba55f4a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Upgrade PostgreSQL 17 to 18 on Linux: A Complete Step-by-Step Guide]]></title>
            <link>https://medium.com/@valentim.dba/how-to-upgrade-postgresql-17-to-18-on-linux-a-complete-step-by-step-guide-2ff0f11e5592?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/2ff0f11e5592</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[infrastructure]]></category>
            <category><![CDATA[upgrade]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Wed, 18 Feb 2026 11:01:01 GMT</pubDate>
            <atom:updated>2026-02-18T11:01:01.594Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gcIoXLSqqQww2sDwSFOAog.jpeg" /></figure><p>Upgrading a major database version can often feel like a high-stakes operation. However, specifically in the PostgreSQL ecosystem, the tooling has matured significantly. With the release of PostgreSQL 18, many Data Engineers are looking to leverage new performance features and optimizations.</p><p>While there are several strategies to upgrade (such as logical replication or dump/restore), using pg_upgrade in &quot;link mode&quot; is widely considered the most efficient method for large datasets. It allows you to upgrade the cluster in minutes—regardless of data size—by modifying filesystem hard links rather than copying data.</p><p>In this guide, we will walk through an in-place upgrade from <strong>PostgreSQL 17.7</strong> to <strong>PostgreSQL 18.2</strong> on a Red Hat/CentOS-based system.</p><h3>Prerequisites and Current State</h3><p>Before touching any configuration, verify your current environment. This guide assumes you are running on an RPM-based Linux distribution (like RHEL, CentOS, or Fedora) with standard PGDG directory structures.</p><h3>Check Current Version</h3><p>First, confirm the version and data directory of your running instance.</p><pre># Check version<br>/usr/pgsql-17/bin/postgres --version<br># Output: postgres (PostgreSQL) 17.7</pre><pre># Verify extensions (optional but recommended)<br># Connect to your DB and run:<br>\dx</pre><h3>Verify Data Directories</h3><p>Ensure you know exactly where your data lives.</p><pre>SHOW data_directory;<br>/var/lib/pgsql/17/data</pre><h3>Step 1: The Golden Rule (Backups)</h3><p><strong>Never attempt a major version upgrade without a full backup.</strong> Even though pg_upgrade is reliable, filesystem errors or human error can happen. We will perform both a physical and a logical backup.</p><h3>Physical Backup with pg_basebackup</h3><p>This creates a binary copy of the cluster files.</p><pre>DATA=$(date +%F)<br>mkdir -p /data/backup/full/$DATA</pre><pre>pg_basebackup -h localhost -U backup_oper \<br>  -Ft -z \<br>  -D /data/backup/full/$DATA \<br>  -P -X stream</pre><ul><li><strong>-Ft -z</strong>: Formats as tar and compresses (gzip).</li><li><strong>-X stream</strong>: Streams WAL files to ensure the backup is consistent.</li></ul><h3>Logical Backup with pg_dumpall</h3><p>A logical dump allows you to restore individual objects if the binary upgrade fails catastrophically and you need to migrate to a new machine.</p><pre>pg_dumpall -U backup_oper -h localhost &gt; /data/dump/full/cluster_full.sql</pre><blockquote><strong><em>Crucial Note:</em></strong><em> The upgrade process </em><strong><em>does not</em></strong><em> migrate your configuration files (</em><em>postgresql.conf and </em><em>pg_hba.conf) automatically. Copy these files to a safe location now. You will need to manually reconcile your custom settings into the new PostgreSQL 18 config files later.</em></blockquote><h3>Step 2: Install the New Binaries</h3><p>With the data secured, we need to install the PostgreSQL 18 binaries alongside the existing version.</p><h3>Check Repository Availability</h3><pre>dnf search postgresql18</pre><h3>Stop the Current Service</h3><p>To prevent data changes during the installation and upgrade process, stop the PostgreSQL 17 service.</p><pre>sudo systemctl stop postgresql-17</pre><h3>Install PostgreSQL 18</h3><p>Install the server and the contrib package (which contains standard extensions).</p><pre>sudo dnf install postgresql18-server postgresql18 postgresql18-contrib</pre><h3>Step 3: Initialize the New Cluster</h3><p>Before we can merge data, the new PostgreSQL 18 cluster needs a blank data directory.</p><pre>/usr/pgsql-18/bin/initdb -D /var/lib/pgsql/18/data</pre><h3>Critical Warning: Data Checksums</h3><p>PostgreSQL 18 may handle default data checksums differently than your installation of PostgreSQL 17.</p><ul><li><strong>PostgreSQL 17</strong> often defaulted to checksums off (unless explicitly enabled).</li><li><strong>PostgreSQL 18</strong> may default to checksums on in certain package distributions.</li></ul><p>If there is a mismatch (e.g., Old Cluster is OFF, New Cluster is ON), pg_upgrade will fail. You must verify the state of your old cluster using pg_controldata and ensure you initialize the new cluster with the matching flag (e.g., adding --data-checksums or --no-data-checksums to the initdb command above).</p><p>Refer to the official documentation regarding checksums:</p><p><a href="https://www.postgresql.org/docs/17/checksums.html">https://www.postgresql.org/docs/17/checksums.html</a><br><a href="https://www.postgresql.org/docs/18/checksums.html">https://www.postgresql.org/docs/18/checksums.html</a></p><h3>Step 4: Consistency Check (Dry Run)</h3><p>Before performing the actual upgrade, run pg_upgrade with the --check flag. This validates compatibility between the two clusters without modifying any data.</p><p><strong>Ensure both clusters are stopped</strong> before running this command.</p><pre>/usr/pgsql-18/bin/pg_upgrade \<br>  --old-datadir=/var/lib/pgsql/17/data \<br>  --new-datadir=/var/lib/pgsql/18/data \<br>  --old-bindir=/usr/pgsql-17/bin \<br>  --new-bindir=/usr/pgsql-18/bin \<br>  --check</pre><p><strong>Expected Output:</strong></p><p>You should see a series of “ok” statuses checking versions, connection settings, user permissions, and libraries.</p><pre>Performing Consistency Checks<br>-----------------------------<br>Checking cluster versions                                     ok<br>Checking database connection settings                         ok<br>Checking database user is the install user                    ok<br>Checking for prepared transactions                            ok<br>Checking for contrib/isn with bigint-passing mismatch         ok<br>Checking for valid logical replication slots                  ok<br>Checking for subscription state                               ok<br>Checking data type usage                                      ok<br>Checking for objects affected by Unicode update               ok<br>Checking for not-null constraint inconsistencies              ok<br>Checking for presence of required libraries                   ok<br>Checking database user is the install user                    ok<br>Checking for prepared transactions                            ok<br>Checking for new cluster tablespace directories               ok<br><br>*Clusters are compatible*</pre><p>If you see <strong>Clusters are compatible</strong>, you are clear to proceed. If it fails, check the generated log files (usually pg_upgrade_internal.log) for specific errors, such as missing libraries or the checksum mismatch mentioned earlier.</p><h3>Step 5: Performing the Upgrade</h3><p>Now, we execute the upgrade using the --link flag.</p><h3>Why --link?</h3><p>By default, pg_upgrade copies files. If you have 2TB of data, this takes hours. The --link flag uses filesystem hard links, making the old and new cluster share the same underlying data blocks. The upgrade happens in seconds, regardless of database size.</p><p><strong>Double check that no Postgres processes are running:</strong></p><pre>ps aux | grep postgres</pre><p><strong>Execute the Upgrade:</strong></p><pre>/usr/pgsql-18/bin/pg_upgrade \<br>  --old-datadir=/var/lib/pgsql/17/data \<br>  --new-datadir=/var/lib/pgsql/18/data \<br>  --old-bindir=/usr/pgsql-17/bin \<br>  --new-bindir=/usr/pgsql-18/bin \<br>  --link</pre><p><strong>Successful Output:</strong></p><p>The process will perform consistency checks again, freeze the old cluster, and restore the schema to the new one.</p><pre>Performing Consistency Checks<br>-----------------------------<br>Checking cluster versions                                     ok<br>Checking database connection settings                         ok<br>Checking database user is the install user                    ok<br>Checking for prepared transactions                            ok<br>Checking for contrib/isn with bigint-passing mismatch         ok<br>Checking for valid logical replication slots                  ok<br>Checking for subscription state                               ok<br>Checking data type usage                                      ok<br>Checking for objects affected by Unicode update               ok<br>Checking for not-null constraint inconsistencies              ok<br>Creating dump of global objects                               ok<br>Creating dump of database schemas<br>                                                              ok<br>Checking for presence of required libraries                   ok<br>Checking database user is the install user                    ok<br>Checking for prepared transactions                            ok<br>Checking for new cluster tablespace directories               ok<br><br>If pg_upgrade fails after this point, you must re-initdb the<br>new cluster before continuing.<br><br>Performing Upgrade<br>------------------<br>Setting locale and encoding for new cluster                   ok<br>Analyzing all rows in the new cluster                         ok<br>Freezing all rows in the new cluster                          ok<br>Deleting files from new pg_xact                               ok<br>Copying old pg_xact to new server                             ok<br>Setting oldest XID for new cluster                            ok<br>Setting next transaction ID and epoch for new cluster         ok<br>Deleting files from new pg_multixact/offsets                  ok<br>Copying old pg_multixact/offsets to new server                ok<br>Deleting files from new pg_multixact/members                  ok<br>Copying old pg_multixact/members to new server                ok<br>Setting next multixact ID and offset for new cluster          ok<br>Resetting WAL archives                                        ok<br>Setting frozenxid and minmxid counters in new cluster         ok<br>Restoring global objects in the new cluster                   ok<br>Restoring database schemas in the new cluster<br>                                                              ok<br>Adding &quot;.old&quot; suffix to old &quot;global/pg_control&quot;               ok<br><br>If you want to start the old cluster, you will need to remove<br>the &quot;.old&quot; suffix from &quot;/var/lib/pgsql/17/data/global/pg_control.old&quot;.<br>Because &quot;link&quot; mode was used, the old cluster cannot be safely<br>started once the new cluster has been started.<br>Linking user relation files<br>                                                              ok<br>Setting next OID for new cluster                              ok<br>Sync data directory to disk                                   ok<br>Creating script to delete old cluster                         ok<br>Checking for extension updates                                ok<br><br>Upgrade Complete<br>----------------<br>Some statistics are not transferred by pg_upgrade.<br>Once you start the new server, consider running these two commands:<br>    /usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only<br>    /usr/pgsql-18/bin/vacuumdb --all --analyze-only<br>Running this script will delete the old cluster&#39;s data files:<br>    ./delete_old_cluster.sh</pre><h3>Step 6: Finalizing the Setup</h3><p>The binary upgrade is complete, but the cluster isn’t ready for production traffic yet.</p><h3>1. Update Configuration</h3><p>Remember the postgresql.conf and pg_hba.conf you backed up? Now is the time to migrate those settings into /var/lib/pgsql/18/data/.</p><ul><li><strong>Do not</strong> simply overwrite the files. New versions introduce new parameters.</li><li>Compare the files and apply your custom settings (memory, connections, autovacuum) to the new configuration files.</li></ul><h3>2. Start the New Service</h3><p>Enable and start the PostgreSQL 18 service.</p><pre>sudo systemctl enable postgresql-18<br>sudo systemctl start postgresql-18</pre><h3>3. Verify the Version</h3><pre># Connect and check version<br>select version();<br>-- PostgreSQL 18.2 on x86_64-pc-linux-gnu...</pre><h3>4. Optimize Statistics</h3><p>pg_upgrade does not copy optimizer statistics. Your new database will perform poorly until statistics are recalculated. The upgrade tool suggests a command to run immediately:</p><pre>/usr/pgsql-18/bin/vacuumdb --all --analyze-in-stages</pre><p>This runs a fast analyze first to make the DB usable, followed by a deeper analyze.</p><h3>Step 7: Cleanup</h3><p>Once you have verified that the application is connecting correctly, the data is intact, and performance is stable, you can remove the old cluster data.</p><p>Because we used --link mode, the old data directory is still technically occupying space (linked to the same inodes). To free up the &quot;old&quot; references:</p><pre>./delete_old_cluster.sh</pre><h3>Conclusion: Leveraging the Power of PostgreSQL 18</h3><p>Upgrading a core piece of infrastructure is a significant milestone for any data team. By leveraging the power of pg_upgrade in link mode, you have successfully navigated a major version migration with minimal downtime, transforming what used to be a high-stress, weekend-long maintenance window into a manageable, efficient task.</p><p>You are now running on the latest iteration of the world’s most advanced open-source relational database. With PostgreSQL 18, your environment is primed to take advantage of improved query performance, better resource management, and robust security features.</p><p><strong>Final Recommendations:</strong></p><ul><li><strong>Monitor Closely:</strong> For the next 48 hours, keep a close watch on your application logs and database metrics (CPU, I/O, and Memory).</li><li><strong>Review Query Plans:</strong> While the data is identical, the query planner in version 18 may behave differently. identifying and tuning slow queries early is key to a smooth transition.</li><li><strong>Stay Updated:</strong> Keep your minor versions patched (e.g., 18.2 to 18.3) to ensure continued stability and security.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2ff0f11e5592" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Boost PostgreSQL Performance: A Complete Guide to PgBouncer Connection Pooling]]></title>
            <link>https://medium.com/@valentim.dba/boost-postgresql-performance-a-complete-guide-to-pgbouncer-connection-pooling-0898dcc9e223?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/0898dcc9e223</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[pgbouncer]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[database-engineering]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Sun, 15 Feb 2026 23:18:15 GMT</pubDate>
            <atom:updated>2026-02-15T23:18:15.432Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p7SRsYZ9jcrxlyYh596BBA.png" /></figure><p>As database engineers, we often hit a specific ceiling with PostgreSQL. Your queries are optimized, your indexes are pristine, yet as your application scales and concurrent user counts rise, the database starts to choke. The culprit? Often, it’s not the data, it’s the connections.</p><p>PostgreSQL handles connections by forking a new process for every client. This is resource-intensive. When you have thousands of clients trying to connect simultaneously, the overhead of managing these processes can outweigh the actual query processing time.</p><p>Enter <strong>PgBouncer</strong>: a lightweight connection pooler for PostgreSQL. It maintains a pool of open connections to the database and reuses them for new clients, dramatically reducing overhead and resource consumption.</p><p>In this guide, we will walk through the installation, configuration, and benchmarking of PgBouncer to demonstrate how it stabilizes high-concurrency environments.</p><h3>Step 1: Installation</h3><p>We will be working in a Linux environment (specifically RHEL/Fedora-based, given the dnf package manager). The goal is to get the latest stable version of PgBouncer running.</p><p>First, update your repositories and install the package:</p><pre>sudo dnf install pgbouncer -y</pre><p>Once installed, verify the version to ensure you have the latest features (such as improved SCRAM support).</p><pre>[root@pg-server-02 pgbouncer]# pgbouncer --version<br>PgBouncer 1.25.1<br>libevent 2.1.12-stable<br>tls: OpenSSL 3.2.4 11 Feb 2025<br>systemd: yes</pre><h3>Step 2: Configuring the Pool</h3><p>The heart of PgBouncer lies in its configuration file, typically located at /etc/pgbouncer/pgbouncer.ini.</p><p>We need to define which databases to pool, how the pool behaves, and how authentication is handled. Below is a configuration optimized for a high-load environment using <strong>transaction pooling</strong>.</p><h3>Understanding the Configuration</h3><p>Edit your configuration file with the following settings:</p><pre>[databases]<br># Map the &#39;bench&#39; database to the local Postgres instance<br>bench = host=127.0.0.1 port=5432 dbname=bench</pre><pre>[users]</pre><pre>[pgbouncer]<br># Log locations<br>logfile = /var/log/pgbouncer/pgbouncer.log<br>pidfile = /var/run/pgbouncer/pgbouncer.pid</pre><pre># Network settings<br>listen_addr = localhost<br>listen_port = 6432</pre><pre># Pooling Strategy<br># &#39;transaction&#39; mode is ideal for web apps: the server connection is released<br># back to the pool as soon as a transaction commits.<br>pool_mode = transaction</pre><pre># Connection Limits<br>max_client_conn = 2000    # Total allowed connections to PgBouncer<br>default_pool_size = 150   # Connections kept open to the actual Postgres DB<br>reserve_pool_size = 20    # Emergency pool for bursts<br>reserve_pool_timeout = 5</pre><pre># Logging &amp; Monitoring<br>log_connections = 1<br>log_disconnections = 1<br>log_pooler_errors = 1</pre><pre># Authentication (SCRAM-SHA-256 is the modern standard)<br>auth_type = scram-sha-256<br>auth_file = /etc/pgbouncer/userlist.txt</pre><pre># Administrative Users<br>admin_users = postgres<br>stats_users = stats, postgres</pre><p><strong>Key Takeaway:</strong> The pool_mode = transaction is the game-changer here. It allows thousands of incoming client connections (max_client_conn = 2000) to share a much smaller number of actual database connections (default_pool_size = 150).</p><h3>Step 3: Secure Authentication Setup</h3><p>PgBouncer needs to know the user credentials to authenticate on behalf of the clients. However, for security, we should never store plain-text passwords. Since we are using scram-sha-256, we need to extract the specific hash from PostgreSQL&#39;s internal tables.</p><h3>1. Extract the Hash</h3><p>Log into your PostgreSQL instance and query the pg_authid table:</p><pre>SELECT rolname, rolpassword<br>FROM pg_authid<br>WHERE rolname = &#39;postgres&#39;;</pre><p>You will receive an output similar to this:</p><pre>rolname  |                                                              rolpassword<br>----------+---------------------------------------------------------------------------------------------------------------------------------------<br> postgres | SCRAM-SHA-256$4096:cPmbLkWnGF16YXoqlUCJ7w==$+vMOLfshCIzSGSkczTUBMo3cz6ydVH84UW59lEHP1Us=:lPcJeVFigsYWA5BOCN1uDbq3Fg+PR91enZHZ00Mr7j8=</pre><h3>2. Update the Auth File</h3><p>Now, open the authentication file configured in your pgbouncer.ini:</p><pre>sudo vi /etc/pgbouncer/userlist.txt</pre><p>Add the username and the hash you retrieved, enclosed in quotes. It should look exactly like this:</p><pre>&quot;postgres&quot; &quot;SCRAM-SHA-256$4096:cPmbLkWnGF16YXoqlUCJ7w==$+vMOLfshCIzSGSkczTUBMo3cz6ydVH84UW59lEHP1Us=:lPcJeVFigsYWA5BOCN1uDbq3Fg+PR91enZHZ00Mr7j8=&quot;</pre><h3>Step 4: Permissions and Security</h3><p>Since userlist.txt contains sensitive credential hashes, we must restrict access to it. Only the pgbouncer system user should be able to read it.</p><pre># Set ownership to the pgbouncer user/group<br>sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt<br><br># Restrict permissions so only the owner can read/write<br>sudo chmod 600 /etc/pgbouncer/userlist.txt</pre><h3>Firewall Configuration</h3><p>By default, PgBouncer listens on port <strong>6432</strong>. We need to ensure the firewall allows traffic on this port.</p><pre>sudo firewall-cmd --add-port=6432/tcp --permanent<br>sudo firewall-cmd --reload</pre><h3>Step 5: Service Start and Verification</h3><p>With everything configured, enable PgBouncer to start on boot and launch the service.</p><pre>sudo systemctl enable pgbouncer<br>sudo systemctl start pgbouncer</pre><p>Check the status to ensure it’s active and running:</p><pre>sudo systemctl status pgbouncer</pre><h3>Validating the Connection</h3><p>Before running benchmarks, manually verify that you can connect to the database <em>through</em> PgBouncer (port 6432) rather than directly to Postgres (port 5432).</p><pre>psql -h 127.0.0.1 -p 6432 -U postgres bench</pre><p><em>If you see the psql prompt, your pool is active.</em></p><h3>Step 6: Benchmarking — The Moment of Truth</h3><p>To visualize the impact, we use pgbench. We will simulate a high-load scenario with <strong>1000 concurrent clients</strong>.</p><h3>Test A: Direct Connection (No Pooling)</h3><p>Connecting 1000 clients directly to PostgreSQL often leads to high latency due to the OS process forking overhead.</p><pre>pgbench -c 1000 -j 16 -T 120 -P 1 bench | tee sem_pool_1000.log</pre><ul><li><strong>Transactions per second (TPS):</strong> ~183</li><li><strong>Average Latency:</strong> ~5345 ms</li></ul><h3>Test B: Via PgBouncer (Connection Pooling)</h3><p>Now, we run the exact same load, but directed at port 6432.</p><pre>pgbench -h 127.0.0.1 -p 6432 -U postgres -c 1000 -j 16 -T 120 -P 1 bench | tee com_pool_1000.log</pre><p><strong>Results:</strong></p><ul><li><strong>Transactions per second (TPS):</strong> ~239</li><li><strong>Average Latency:</strong> ~4086 ms</li></ul><h3>Conclusion</h3><p>Our benchmarks demonstrate a clear efficiency gain: a <strong>30% increase in throughput (TPS)</strong> and a reduction in average latency from <strong>5.3s to 4.0s</strong> under a load of 1000 concurrent clients.</p><p>However, the true value of PgBouncer isn’t just raw speed, it is <strong>stability</strong>.</p><p>PostgreSQL’s process-based architecture incurs significant overhead when forking new connections, often leading to resource exhaustion or FATAL: sorry, too many clients already errors during spikes. By implementing <strong>Transaction Pooling</strong> with secure SCRAM authentication, PgBouncer acts as a &quot;shock absorber.&quot; It queues excess requests and maintains a predictable load on the database.</p><p>For any high-traffic environment, a connection pooler is not just a performance tweak; it is a mandatory layer for building a scalable and robust infrastructure.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0898dcc9e223" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Turn Your PostgreSQL Database into a REST API with PostgREST]]></title>
            <link>https://medium.com/@valentim.dba/how-to-turn-your-postgresql-database-into-a-rest-api-with-postgrest-a11d6e46daf2?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/a11d6e46daf2</guid>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[api]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[rest-api]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Sun, 08 Feb 2026 11:02:00 GMT</pubDate>
            <atom:updated>2026-02-08T11:02:00.985Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*irIkaU0842tvq2j1vhr3Nw.png" /></figure><p>As developers, we often spend a disproportionate amount of time building “boilerplate” CRUD (Create, Read, Update, Delete) APIs just to expose data stored in our relational databases. What if you could skip the middleman?</p><p><strong>PostgREST</strong> is a standalone web server that turns your PostgreSQL database directly into a RESTful API. It detects your database schema and automatically creates endpoints, handling serialization and even authentication constraints defined in SQL.</p><p>In this guide, we will walk through a complete setup on a Linux environment (specifically Fedora, though applicable to most distributions). We will cover creating the necessary database roles, installing the binary, configuring the service, and verifying the endpoints.</p><p>Let’s get your API up and running.</p><h3>1. Setting the Foundation: Database Roles and Schema</h3><p>PostgREST interacts with your database using a specific security model. We don’t want to use a superuser; instead, we create a dedicated authenticator role and an anonymous role for public web requests.</p><p>First, let’s set up the roles. The postgrest_authenticator will be the role the software uses to connect to the database, while web_anon represents unauthenticated API users.</p><pre>-- Create the Authenticator role (used by the PostgREST service)<br>create role postgrest_authenticator noinherit login password &#39;Str0ngP4ssw0rd_2026&#39;;<br><br>-- Create the Anonymous role (used for public web requests)<br>create role web_anon nologin;<br><br>-- Grant the anonymous role to the authenticator so it can switch context<br>grant web_anon to postgrest_authenticator;</pre><p>Next, let’s create our database and a dedicated schema. Using a specific schema (like api) is a best practice. It isolates the tables you want to expose via REST from your internal system tables.</p><pre>-- Create Database and connect to it<br>create database WebApp;<br>\c webapp<br><br>-- Create a dedicated schema for the API<br>create schema api;</pre><h3>Creating a Test Table</h3><p>To verify that our API works, we need some data. Let’s create a simple ping table inside our new schema.</p><pre>create table api.ping (<br>  id serial primary key,<br>  message text not null<br>);<br><br>-- Insert a test record<br>insert into api.ping (message)<br>values (&#39;PostgREST is running&#39;);</pre><h3>Configuring Permissions</h3><p>This is a crucial step that is often overlooked. Creating the table isn’t enough; we must explicitly grant the web_anon role permission to access the schema and read the table. If you skip this, your API will return 401 or 403 errors.</p><p>SQL</p><pre>-- Allow usage of the schema<br>grant usage on schema api to web_anon;<br><br>-- Allow read access to the specific table<br>grant select on api.ping to web_anon;</pre><h3>2. Installing PostgREST</h3><p>One of the best features of PostgREST is that it is a single static binary. There are no complex dependencies or runtimes to install. We will download the tarball directly.</p><p><em>Note: In this example, we are using version 14.4.</em></p><pre># Navigate to a common source directory<br>cd /usr/local/src<br><br># Download the static executable (Linux x86-64)<br>wget https://github.com/PostgREST/postgrest/releases/download/v14.4/postgrest-v14.4-linux-static-x86-64.tar.xz<br># Extract the archive<br>tar -xf postgrest-v14.4-linux-static-x86-64.tar.xz<br># Make the binary executable<br>chmod +x postgrest<br># Clean up the archive file<br>rm postgrest-v14.4-linux-static-x86-64.tar.xz</pre><p>Verify the installation by checking the version:</p><pre>postgrest --version<br># Output: PostgREST 14.4</pre><h3>3. Configuration</h3><p>PostgREST requires a simple configuration file to know how to connect to your database and which schema to expose. We’ll store this in /etc/postgrest.</p><pre>mkdir -p /etc/postgrest<br>vi /etc/postgrest/postgrest.conf</pre><p>Paste the following configuration into the file. Pay close attention to the db-uri, as it contains the credentials we created in step 1.</p><pre># The standard connection string<br>db-uri = &quot;postgres://postgrest_authenticator:Str0ngP4ssw0rd_2026@localhost:5432/webapp&quot;<br># The schema we want to expose<br>db-schema = &quot;api&quot;<br># The role to use for unauthenticated requests<br>db-anon-role = &quot;web_anon&quot;<br># Network settings<br>server-host = &quot;0.0.0.0&quot;<br>server-port = 3000<br># Logging verbosity<br>log-level = &quot;info&quot;</pre><h3>4. Launching the Service</h3><p>Now we are ready to start the server. For a quick test or development setup, we can run it using nohup to keep the process running in the background even if we disconnect our terminal session.</p><p><em>Pro Tip: For a production environment, you should create a systemd service unit to manage the process.</em></p><pre>nohup postgrest /etc/postgrest/postgrest.conf &amp;</pre><h3>Verifying the Startup Logs</h3><p>Check the output (usually in nohup.out or the console) to ensure the connection was successful. You should see the schema cache being loaded:</p><pre>07/Feb/2026:23:26:58 -0300: Starting PostgREST 14.4...<br>07/Feb/2026:23:26:58 -0300: API server listening on 127.0.0.1:3000<br>07/Feb/2026:23:26:58 -0300: Successfully connected to PostgreSQL 17.7...<br>07/Feb/2026:23:26:58 -0300: Connection Pool initialized...<br>07/Feb/2026:23:26:58 -0300: Schema cache loaded...</pre><h3>5. Testing the API</h3><p>Now for the rewarding part. Let’s consume our API.</p><h3>Test via Terminal (cURL)</h3><p>First, let’s check the root endpoint. This returns the OpenAPI (Swagger) definition of your API automatically.</p><p>Bash</p><pre>curl -i http://127.0.0.1:3000/</pre><p><strong>Response:</strong></p><pre>HTTP/1.1 200 OK<br>Date: Sun, 08 Feb 2026 02:30:09 GMT<br>Server: postgrest/14.4<br>Content-Type: application/openapi+json; charset=utf-8</pre><p>Next, let’s query our ping table. Notice how the URL path /ping maps directly to the table name.</p><pre>curl http://127.0.0.1:3000/ping</pre><p><strong>Response:</strong></p><pre>[{&quot;id&quot;:1,&quot;message&quot;:&quot;PostgREST is running&quot;}]</pre><h3>Database Verification</h3><p>We can also verify the connection from the database side. By querying pg_stat_activity, we can see the PostgREST authenticator holding a connection to the pool.</p><pre>select pid, usename, state, client_addr<br>from pg_stat_activity<br>where usename = &#39;postgrest_authenticator&#39;;</pre><h3>6. Next Steps: Postman and Firewalls</h3><h3>Testing with Postman</h3><p>While curl is great for quick checks, tools like Postman are essential for robust API testing. When you point Postman to http://&lt;your-server-ip&gt;:3000/ping, you will receive a clean JSON array. PostgREST also supports filtering, sorting, and pagination via URL parameters out of the box (e.g., /ping?id=eq.1).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_4h3y9FibPZpDBj6FdY2HA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hYQ-ov_X4t5Phw81z0COCg.png" /></figure><h3>A Note on Firewalls</h3><p>If you are deploying this on a remote server, ensure your firewall permits traffic on port <strong>3000</strong>.</p><ul><li>On <strong>AWS/Azure/GCP</strong>, update your Security Groups to allow Inbound TCP 3000.</li><li>On <strong>Linux (firewalld)</strong>, run:</li></ul><pre>firewall-cmd --permanent --add-port=3000/tcp <br>firewall-cmd --reload</pre><p>By following these steps, you have successfully decoupled your API layer from your application logic, allowing your database to serve data directly, securely, and efficiently.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a11d6e46daf2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Restore a PostgreSQL Physical Backup: A Comprehensive Step-by-Step Guide]]></title>
            <link>https://medium.com/@valentim.dba/how-to-restore-a-postgresql-physical-backup-a-comprehensive-step-by-step-guide-4528a9b412d6?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/4528a9b412d6</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[backup]]></category>
            <category><![CDATA[backup-and-restore]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Mon, 19 Jan 2026 11:02:01 GMT</pubDate>
            <atom:updated>2026-01-19T11:02:01.038Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/848/1*cxnbnAdwVgOfdiqQPVFonA.jpeg" /></figure><p>In my previous post, we covered how to create a high-performance physical backup using pg_basebackup. But a backup is merely storage consumed until it is successfully tested and restored. When a server fails or data corruption occurs, your ability to execute a precise restoration process is what stands between a minor hiccup and a business catastrophe.</p><p>Physical restoration in PostgreSQL involves more than just moving files; it requires careful coordination of Data Directories, Tablespaces, and Write-Ahead Logs (WAL).</p><h3>Phase 1: Preparation and Cleanup</h3><p>Before beginning the restoration, the environment must be “sanitized.” You cannot restore into a directory that already contains data.</p><blockquote><strong><em>Pro Tip:</em></strong><em> If you have the storage space, renaming the old directory is always safer than a permanent deletion (</em><em>rm -rf).</em></blockquote><pre># 1. Stop the PostgreSQL service<br>/usr/pgsql-17/bin/pg_ctl stop -D /var/lib/pgsql/17/data<br><br># 2. Safety First: Rename existing directories to keep a rollback option<br>mv /var/lib/pgsql/17/data /var/lib/pgsql/17/data_OLD_$(date +%F_%H%M)<br>mv /data/tbs_data /data/tbs_data_OLD_$(date +%F_%H%M)<br>mv /data/tbs_index /data/tbs_index_OLD_$(date +%F_%H%M)<br><br># 3. Recreate clean directories for the restoration<br>mkdir /var/lib/pgsql/17/data<br>mkdir /data/tbs_data<br>mkdir /data/tbs_index</pre><h3>Phase 2: Restoring the Base Files</h3><p>The first step of the actual restoration is extracting the base.tar.gz. This file contains the configuration, system catalogs, and the vital mapping files.</p><pre># Define variables for efficiency<br>BACKUP_DIR=&quot;/data/backup/full/2026-01-18&quot; # Adjust to your backup date<br>PGDATA=&quot;/var/lib/pgsql/17/data&quot;<br><br># Extract the base backup to the PGDATA directory<br>tar -xzf $BACKUP_DIR/base.tar.gz -C $PGDATA</pre><h3>Phase 3: Tablespace Mapping and Extraction</h3><p>One of the most common mistakes during a restore is misaligning Tablespaces. Physical backups store tablespaces as separate .tar.gz files named after their OIDs (Object Identifiers). You <strong>must</strong> verify the mapping file before extracting them.</p><h3>Verify the OIDs</h3><p>Check the tablespace_map file extracted in the previous step:</p><pre>cat $PGDATA/tablespace_map</pre><p>Expected output:</p><p>16388 /data/tbs_data</p><p>16389 /data/tbs_index</p><p>Now that the locations are confirmed, extract each tablespace to its respective path:</p><pre># Extract Tablespaces based on the verified OIDs<br>tar -xzf $BACKUP_DIR/16388.tar.gz -C /data/tbs_data/<br>tar -xzf $BACKUP_DIR/16389.tar.gz -C /data/tbs_index/</pre><h3>Phase 4: Restoring the WAL (Write-Ahead Logs)</h3><p>The base.tar.gz creates a pg_wal directory, but it is empty. To make the database consistent and allow it to reach a &quot;ready&quot; state, you must restore the logs captured during the backup process.</p><pre># Ensure the directory exists<br>mkdir -p $PGDATA/pg_wal<br><br># Extract the WAL files directly into the pg_wal folder<br>tar -xzf $BACKUP_DIR/pg_wal.tar.gz -C $PGDATA/pg_wal/</pre><h3>Phase 5: Permissions and Recovery Configuration</h3><p>PostgreSQL is very strict about file permissions. If the ownership is incorrect, the service will refuse to start.</p><h3>1. Set Ownership and Permissions</h3><pre>chown -R postgres:postgres $PGDATA /data/tbs_data /data/tbs_index<br>chmod 700 $PGDATA /data/tbs_data /data/tbs_index</pre><h3>2. Configure Recovery Signals</h3><p>To trigger the recovery process, we need to create a recovery.signal file. We will also update postgresql.auto.conf to tell the engine where to find additional archived logs if we are performing a Point-in-Time Recovery (PITR).</p><pre># Set the restore command for PITR (fetches logs from your WAL archive)<br>echo &quot;restore_command = &#39;cp /archives/wal/%f %p&#39;&quot; &gt;&gt; $PGDATA/postgresql.auto.conf<br><br># Create the signal file to enter recovery mode<br>touch $PGDATA/recovery.signal<br># Apply permissions to config files<br>chown postgres:postgres $PGDATA/postgresql.auto.conf $PGDATA/recovery.signal<br>chmod 600 $PGDATA/postgresql.auto.conf</pre><h3>Phase 6: Service Start and Validation</h3><p>With everything in place, it’s time to bring the database back online.</p><pre>/usr/pgsql-17/bin/pg_ctl start -D $PGDATA</pre><h3>How to Verify Success</h3><p>Monitoring the logs is essential during recovery. Watch for these specific milestones:</p><ul><li><strong>Log Command:</strong> tail -f $PGDATA/log/postgresql-....log</li><li><strong>Success Indicator 1:</strong> consistent recovery state reached</li><li><strong>Success Indicator 2:</strong> database system is ready to accept connections</li></ul><p>Final Cleanup: Once the database is confirmed healthy, you can remove the leftover backup artifacts:</p><pre>rm $PGDATA/backup_label.old $PGDATA/tablespace_map.old</pre><h3>Conclusion</h3><p>You have now successfully navigated the complexities of a physical PostgreSQL restoration. By handling Tablespace OIDs correctly and ensuring WAL consistency, you’ve minimized the risk of data loss.</p><p>Mastering these manual steps is the first step toward building fully automated, resilient database architectures.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4528a9b412d6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Create a Complete Physical Backup in PostgreSQL Using pg_basebackup]]></title>
            <link>https://medium.com/@valentim.dba/how-to-create-a-complete-physical-backup-in-postgresql-using-pg-basebackup-142e286e24c3?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/142e286e24c3</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[backup]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Sun, 18 Jan 2026 21:30:47 GMT</pubDate>
            <atom:updated>2026-01-18T21:30:47.507Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/848/1*CH5eQdSx8jDnzQaZSlkIZQ.jpeg" /></figure><p>In the world of database administration, your recovery strategy is only as good as your latest backup. While logical dumps are useful for small datasets, enterprise-grade environments require a more robust approach. PostgreSQL offers the pg_basebackup utility, a powerful tool designed to create a consistent, physical &quot;base backup&quot; of a running database cluster. This method is the gold standard for setting up replication nodes or ensuring you have a foundation for Point-in-Time Recovery (PITR).</p><h3>Automating the Full Backup Process</h3><p>To streamline the backup workflow, we can use a shell script that handles directory organization and execution in one go. Below is a production-ready command structure to capture your entire database state.</p><h3>The Backup Script</h3><p>The following commands are executed in the terminal to initialize and run the backup process:</p><pre>DATA=$(date +%F)<br>mkdir -p /data/backup/full/$DATA<br>pg_basebackup -h localhost -p 64165 -U dba_bkp \<br>  -Ft -z \<br>  -D /data/backup/full/$DATA \<br>  -P -X stream</pre><h3>Step-by-Step Breakdown</h3><p>Let’s dissect each component of the script to understand how it ensures data integrity and operational efficiency.</p><h3>1. Dynamic Date Variable</h3><pre>DATA=$(date +%F)</pre><p>The script begins by capturing the current date in the <strong>YYYY-MM-DD</strong> format (e.g., 2026-01-18). Storing this in the DATA variable ensures that every backup is organized into a unique, timestamped folder, preventing accidental overwrites.</p><h3>2. Destination Directory Management</h3><pre>mkdir -p /data/backup/full/$DATA</pre><p>This command creates the target directory path. The -p flag is a best practice—it creates parent directories as needed and prevents the script from failing if the folder already exists.</p><h3>3. Executing pg_basebackup</h3><p>The core of the operation lies in the pg_basebackup utility. Unlike a simple file copy, this tool communicates with the PostgreSQL engine to ensure the backup is consistent even while data is being written.</p><p><strong>OptionTechnical Description</strong><strong>-h localhost</strong>Specifies the <strong>host</strong> where the PostgreSQL server is running.<strong>-p 64165</strong>Defines the specific <strong>network port</strong> the database is listening on.<strong>-U dba_bkp</strong>The <strong>username</strong> used for the connection. This user must have REPLICATION privileges.<strong>-Ft</strong>Sets the output format to <strong>tar</strong>. This bundles the database files into easy-to-manage archives.<strong>-z</strong>Enables <strong>gzip compression</strong>, significantly reducing the storage footprint and I/O load.<strong>-D [path]</strong>Sets the <strong>destination directory</strong> for the resulting backup files.<strong>-P</strong>Enables the <strong>progress meter</strong>, allowing DBAs to monitor the transfer in real-time.<strong>-X streamCritical:</strong> This streams the <strong>Write-Ahead Logs (WAL)</strong> during the backup. It ensures the backup is &quot;self-contained&quot; and consistent upon restoration.</p><h3>Essential Requirements &amp; Best Practices</h3><p>Before deploying this script, ensure your environment meets the following technical prerequisites:</p><ul><li>User Privileges: The database user (e.g., dba_bkp) must have the REPLICATION attribute. You can grant this using:</li><li>ALTER USER dba_bkp REPLICATION;</li><li><strong>Authentication (pg_hba.conf):</strong> Your PostgreSQL configuration must allow replication connections for your backup user from the local or remote IP.</li><li><strong>Storage Permissions:</strong> The system user executing the script must have <strong>write permissions</strong> for the /data/backup/ mount point.</li><li><strong>WAL Level:</strong> Ensure your postgresql.conf has wal_level set to replica or logical to support the streaming process.</li></ul><p>By utilizing pg_basebackup with these parameters, you move beyond simple snapshots into a professional backup strategy that guarantees data consistency and facilitates rapid disaster recovery.</p><h3>Conclusion and Next Steps</h3><p>Implementing a physical backup strategy with pg_basebackup is a transformative step for any data team. By automating this process, you move beyond simple snapshots into a professional framework that guarantees data consistency and prepares your infrastructure for high-availability scenarios.</p><p>However, a backup is only half of the story. You must know how to bring that data back to life when disaster strikes.</p><p>Stay tuned for my next post, where I will provide a comprehensive guide on the Restore Procedure, demonstrating exactly how to recover your database from these physical files and get your services back online in minutes.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=142e286e24c3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering MySQL Replication: Choosing Between Statement, Row, and Mixed Binary Logging]]></title>
            <link>https://medium.com/@valentim.dba/mastering-mysql-replication-choosing-between-statement-row-and-mixed-binary-logging-79805c508662?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/79805c508662</guid>
            <category><![CDATA[open-source]]></category>
            <category><![CDATA[database-replication]]></category>
            <category><![CDATA[mysql-server]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[replication]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Sat, 10 Jan 2026 23:17:34 GMT</pubDate>
            <atom:updated>2026-01-10T23:17:34.351Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nXWOE_kxZ65yon6jL4Tdbw.png" /></figure><p>In the world of high-availability databases, replication is the backbone of scalability and disaster recovery. If you are managing MySQL instances, understanding how data travels from your <strong>Source</strong> to your <strong>Replica</strong> is crucial for maintaining data integrity and system performance.</p><p>The heart of this process lies in the binlog_format. Choosing the right format determines not just how your data is copied, but how your database handles complex queries, triggers, and network bandwidth. In this guide, we will dive deep into the three primary types of MySQL replication and how to configure them for your environment.</p><h3>Understanding the Three Pillars of MySQL Replication</h3><h3>1. Statement-Based Replication (SBR)</h3><p>Statement-Based Replication is the “classic” approach. In this mode, the Source server writes the actual SQL statements (the queries themselves) to the binary log. The Replica then downloads these logs and re-executes the SQL commands.</p><ul><li><strong>The Advantage:</strong> It is incredibly efficient in terms of storage and network traffic. If you update 10 million rows with a single UPDATE statement, only that one string of text is sent over the wire.</li><li><strong>The Risk:</strong> It is non-deterministic. If your query uses functions like UUID(), USER(), or NOW(), the result on the Replica might differ from the Source, leading to data drift.</li></ul><h3>2. Row-Based Replication (RBR)</h3><p>Row-Based Replication shifts the focus from the <em>intent</em> (the query) to the <em>result</em> (the actual data). Instead of logging the SQL statement, the Source logs exactly which rows were changed and how.</p><ul><li><strong>The Advantage:</strong> This is the most robust and safest method. Since it copies the actual binary changes of each row, it eliminates issues with non-deterministic functions. It is the default setting in modern MySQL versions (5.7.7 and later).</li><li><strong>The Risk:</strong> It can generate massive log files. An UPDATE that touches millions of rows will record every single row change in the binary log, potentially causing network congestion.</li></ul><h3>3. Mixed-Based Replication (MBR)</h3><p>Mixed-Based Replication is the intelligent middle ground. By default, it uses Statement-Based replication to keep logs small and fast. However, if MySQL detects a query that is “unsafe” for SBR (such as a query containing non-deterministic functions), it automatically switches to Row-Based logging for that specific transaction.</p><h3>How to Configure Your Replication Strategy</h3><p>Changing the replication format requires modifying your MySQL configuration file (usually my.cnf on Linux or my.ini on Windows). Under the [mysqld] header, you can define the binlog_format variable.</p><h3>Setting up Statement-Based Replication (SBR)</h3><p>Use this if your application uses very simple, deterministic queries and you need to minimize network overhead.</p><pre># Edit your my.cnf file<br>binlog_format = STATEMENT</pre><p><em>This setting instructs the server to record only the SQL strings in the binary logs.</em></p><h3>Setting up Row-Based Replication (RBR)</h3><p>This is the recommended setting for most production environments to ensure 100% data consistency across clusters.</p><pre># Edit your my.cnf file<br>binlog_format = ROW</pre><p><em>This configuration records the specific changes to individual table rows, ensuring the Replica is a perfect mirror of the Source.</em></p><h3>Setting up Mixed-Based Replication (MBR)</h3><p>Choose this if you want the efficiency of SBR with the safety net of RBR when complex queries occur.</p><pre># Edit your my.cnf file<br>binlog_format = MIXED</pre><p><em>This enables a hybrid mode where MySQL dynamically decides the best logging format per statement.</em></p><h3>Applying the Changes</h3><p>Configuring the file is only the first step. Because the binlog_format is a fundamental system variable, you must restart the MySQL service to ensure the changes take effect across all threads.</p><p>On most modern Linux distributions using systemd, execute the following command:</p><pre>systemctl restart mysqld</pre><p><em>This command stops and starts the MySQL daemon, reloading the configuration file and initializing the new binary logging format.</em></p><h3>Pro-Tip: Checking the Current Status</h3><p>Before and after your changes, you can verify which mode your server is currently using by running this SQL command in your terminal:</p><pre>SHOW VARIABLES LIKE &#39;binlog_format&#39;;</pre><h3>Conclusion</h3><p>Selecting the right replication type is a balance between <strong>network performance</strong> and <strong>data reliability</strong>. For most modern workloads, <strong>Row-Based Replication (RBR)</strong> is the gold standard, but understanding <strong>SBR</strong> and <strong>Mixed</strong> modes gives you the flexibility to optimize for specific use cases.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=79805c508662" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Implementing MySQL Audit Log for Security Compliance and JSON Monitoring]]></title>
            <link>https://medium.com/@valentim.dba/implementing-mysql-audit-log-for-security-compliance-and-json-monitoring-c9615fe21f76?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/c9615fe21f76</guid>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[database-security]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[mysql-server]]></category>
            <category><![CDATA[database-server]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Fri, 09 Jan 2026 11:02:45 GMT</pubDate>
            <atom:updated>2026-01-09T11:02:45.618Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_X6b8VjJ2hS0ZgAmOEZytw.png" /></figure><p>Maintaining a robust security posture is non-negotiable for modern database administrators. As data breaches become more sophisticated, knowing exactly who did what and when in your database is critical. MySQL provides a powerful <strong>Audit Log Plugin</strong> that enables you to monitor server activity, ensuring compliance and enhancing forensic capabilities.</p><p>In this guide, we will walk through the professional implementation of the MySQL Audit Log, from initial installation on Linux to advanced log consumption using SQL functions.</p><h3>1. Initializing the Audit Log Plugin</h3><p>The MySQL Audit Log is often provided as a plugin that must be registered within the system. On Linux distributions, MySQL includes a helper script to automate the installation of the necessary filters and functions.</p><p>First, locate the installation script. Typically, for MySQL 9.0 and recent versions, it is found in the shared directory:</p><pre>/usr/share/mysql-9.0/audit_log_filter_linux_install.sql</pre><p>To install the plugin components, execute this script against your MySQL instance. This command pipes the SQL script into the mysql client, requiring administrative privileges:</p><pre>mysql -u root -p mysql &lt; /usr/share/mysql-9.0/audit_log_filter_linux_install.sql</pre><h3>2. Verifying the Installation</h3><p>Once the script has finished executing, you should verify that the plugin is active and running correctly. You can query the INFORMATION_SCHEMA to check the status of any plugin related to auditing:</p><pre>SELECT PLUGIN_NAME, PLUGIN_STATUS<br>FROM INFORMATION_SCHEMA.PLUGINS<br>WHERE PLUGIN_NAME LIKE &#39;audit%&#39;;</pre><p>If the installation was successful, you should see the audit_log plugin listed with an ACTIVE status.</p><h3>3. Hardening the Configuration</h3><p>By default, plugins can be uninstalled at runtime. For a secure production environment, you should enforce the presence of the audit log so it cannot be disabled without a server restart. This is achieved by modifying the my.cnf configuration file.</p><p>Add the following line to your configuration to make the plugin permanent:</p><pre>audit-log=FORCE_PLUS_PERMANENT</pre><p>After modifying the configuration file, restart the MySQL service to apply the hardening:</p><pre>systemctl restart mysqld</pre><h3>4. Optimizing Log Output for Analysis</h3><p>The default audit log format might not be ideal for modern log aggregators (like ELK or Splunk). Switching the format to <strong>JSON</strong> allows for easier parsing and integration with third-party monitoring tools.</p><p>Update your [mysqld] section in my.cnf:</p><pre>[mysqld]<br>audit_log_format=JSON</pre><p>Apply the changes by restarting the server once more:</p><pre>systemctl restart mysqld</pre><p>To confirm that the server is now outputting logs in the correct format, run the following command:</p><pre>SHOW VARIABLES LIKE &#39;audit_log_format&#39;;</pre><h3>5. Efficiently Reading Audit Logs</h3><p>MySQL provides built-in functions to read audit data directly via SQL, which is incredibly useful for quick inspections without leaving the terminal.</p><h3>Using Bookmarks for Incremental Reading</h3><p>Reading through millions of log entries is inefficient. To solve this, MySQL uses a “bookmark” system. The audit_log_read_bookmark() function generates a pointer to the current end of the log.</p><pre>SELECT audit_log_read_bookmark();</pre><p><em>This command creates a reference point, allowing you to track where your last reading ended.</em></p><h3>Reading New Entries</h3><p>To read only the events that have occurred since your last bookmark, pass the bookmark function as an argument to the reader. This is the standard method for incremental log consumption:</p><pre>SELECT audit_log_read(audit_log_read_bookmark());</pre><p><em>This query returns only the audit entries generated after the bookmark was set, preventing redundant data processing.</em></p><h3>Conclusion</h3><p>Implementing the MySQL Audit Log is a vital step in securing your data infrastructure. By enforcing the plugin’s presence, switching to JSON for better readability, and utilizing bookmarks for efficient log consumption, you create a transparent and accountable database environment.</p><p><strong>Further Reading &amp; Related Articles</strong></p><p>If you found this article helpful, you might be interested in my other technical posts on PostgreSQL and MySQL:</p><ul><li><a href="https://medium.com/@valentim.dba/how-to-prevent-brute-force-attacks-in-mysql-using-connection-control-2baa8f839a7f">How to Prevent Brute-Force Attacks in MySQL Using Connection Control</a></li><li><a href="https://medium.com/@valentim.dba/how-to-install-and-configure-pgaudit-on-postgresql-17-complete-guide-to-database-auditing-and-81ffe0ecd780">How to Install and Configure PGAudit on PostgreSQL 17: Complete Guide to Database Auditing and…</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c9615fe21f76" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Prevent Brute-Force Attacks in MySQL Using Connection Control]]></title>
            <link>https://medium.com/@valentim.dba/how-to-prevent-brute-force-attacks-in-mysql-using-connection-control-2baa8f839a7f?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/2baa8f839a7f</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[mysql-internals]]></category>
            <category><![CDATA[mysql-server]]></category>
            <category><![CDATA[security]]></category>
            <category><![CDATA[database-security]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Thu, 08 Jan 2026 23:49:13 GMT</pubDate>
            <atom:updated>2026-01-08T23:49:13.902Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*149Zj2_zxuXV_eBQXaLRmg.png" /></figure><h3>Securing MySQL Against Brute-Force Attacks: A Guide to the Connection Control Plugin</h3><p>In the modern landscape of database administration, security is not just a feature, it is a fundamental requirement. One of the most common threats to database availability and integrity is the brute-force attack, where malicious actors attempt to gain access through repetitive login attempts. For MySQL environments, the <strong>Connection Control Plugin</strong> serves as a vital first line of defense. By monitoring failed login attempts and introducing intentional delays, this plugin effectively mitigates Denial of Service (DoS) risks and protects your credentials from automated guessing.</p><h3>Core Functionalities</h3><p>The Connection Control Plugin operates by tracking connection failures per account and host. When a specific threshold is met, the plugin enforces a delay on subsequent connection attempts.</p><ul><li><strong>Failed Attempt Thresholding</strong>: You can define exactly how many unsuccessful attempts are permitted before the server begins to throttle the source.</li><li><strong>Dynamic Blocking Delays</strong>: Once the threshold is crossed, the plugin introduces a configurable latency (measured in milliseconds), making rapid-fire automated attacks computationally expensive and practically ineffective.</li></ul><h3>Essential Configuration Variables</h3><p>To tune the plugin to your specific security requirements, you will primarily interact with two variables:</p><ol><li><strong>connection_control_failed_connections_threshold</strong>: This defines the &quot;patience&quot; of your server. It sets the number of consecutive failed login attempts allowed before the server imposes a delay.</li><li><strong>connection_control_min_connection_delay</strong>: This specifies the minimum amount of time (in milliseconds) the server will wait before responding to a connection request once the threshold has been reached.</li></ol><h3>Step-by-Step Installation</h3><h3>1. Enabling the Plugin via Configuration File</h3><p>To ensure the plugin loads automatically whenever the MySQL service starts, add the following entry to your my.cnf or my.ini file under the [mysqld] header:</p><pre>[mysqld]<br>plugin-load-add=connection_control.so</pre><p><em>This line tells the MySQL server to locate and initialize the shared object library for connection control during the startup sequence.</em></p><h3>2. Runtime Installation</h3><p>If you prefer to install the plugin without restarting the database instance, you can execute the following SQL commands. It is recommended to install both the main plugin and the utility for monitoring failed attempts:</p><pre>INSTALL PLUGIN CONNECTION_CONTROL<br>  SONAME &#39;connection_control.so&#39;;</pre><pre>INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS<br>  SONAME &#39;connection_control.so&#39;;</pre><p><em>The first command activates the throttling logic, while the second enables the information schema tables required to monitor failed attempts.</em></p><h3>3. Enforcing Strict Availability</h3><p>For high-security environments, you can force the server to fail to start if the plugins are not available, preventing any “unprotected” uptime. Add these parameters to your configuration file:</p><pre>[mysqld]<br>connection-control=FORCE_PLUS_PERMANENT<br>connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT</pre><p><em>The </em><em>FORCE_PLUS_PERMANENT flag ensures that the plugins cannot be uninstalled at runtime and that the server requires them to function.</em></p><h3>Validating the Installation</h3><p>After installation, it is a best practice to verify that the plugins are active and running correctly. You can query the INFORMATION_SCHEMA to check their status:</p><pre>SELECT PLUGIN_NAME, PLUGIN_STATUS<br>FROM INFORMATION_SCHEMA.PLUGINS<br>WHERE PLUGIN_NAME LIKE &#39;connection%&#39;;</pre><p><em>A successful installation will return a status of </em><em>ACTIVE for both connection control components.</em></p><h3>Configuring Security Thresholds</h3><p>Once the plugin is active, you must define your security policy. In this example, we will set the server to start throttling after 4 failed attempts, with a 2-second delay.</p><h3>Permanent Configuration via my.cnf</h3><p>Add these lines to your configuration file for persistence across restarts:</p><pre>[mysqld]<br># Block the IP after 4 failed attempts<br>connection_control_failed_connections_threshold=4 </pre><pre># Apply a 2000ms (2 second) delay to subsequent attempts<br>connection_control_min_connection_delay=2000</pre><h3>Dynamic Configuration (No Restart Required)</h3><p>For MySQL 8.0 and later, you can use the SET PERSIST command to apply these changes immediately and ensure they survive a reboot:</p><pre>SET PERSIST connection_control_failed_connections_threshold = 4;<br>SET PERSIST connection_control_min_connection_delay = 2000;</pre><p><em>Using </em><em>SET PERSIST writes the configuration to the </em><em>mysqld-auto.cnf file, combining the flexibility of runtime changes with the reliability of permanent configuration.</em></p><h3>Conclusion</h3><p>Implementing the Connection Control Plugin is a “low-effort, high-impact” security practice. By simply introducing a small delay in response to failed logins, you can transform your MySQL instance from a vulnerable target into a resilient environment capable of withstanding automated credential attacks.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2baa8f839a7f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Unlock the Power of Graphs: A Step-by-Step Guide to Installing Neo4j Enterprise 5.26 LTS on Linux]]></title>
            <link>https://medium.com/@valentim.dba/unlock-the-power-of-graphs-a-step-by-step-guide-to-installing-neo4j-enterprise-5-26-lts-on-linux-a4bb8e481906?source=rss-6ebe01756f42------2</link>
            <guid isPermaLink="false">https://medium.com/p/a4bb8e481906</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[graph]]></category>
            <category><![CDATA[installation]]></category>
            <category><![CDATA[neo4j]]></category>
            <category><![CDATA[graphql]]></category>
            <dc:creator><![CDATA[Matheus dos Santos]]></dc:creator>
            <pubDate>Tue, 02 Dec 2025 11:02:27 GMT</pubDate>
            <atom:updated>2025-12-02T11:02:27.530Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/789/1*yjMtDbqGdO3My4_anY_5yQ.png" /></figure><p>In the era of interconnected data, graph databases have become essential for uncovering hidden patterns, powering recommendation engines, and managing complex knowledge networks. <strong>Neo4j</strong> remains the undisputed leader in this space.</p><p>For production environments and serious development, relying on a Long-Term Support (LTS) release is critical for stability and security. <strong>Neo4j 5.26</strong> is the current LTS version, offering the latest performance improvements alongside guaranteed support duration.</p><p>While Docker and Desktop versions exist, installing the <strong>Enterprise Edition</strong> directly on a Linux server gives you the ultimate control over resource management and configuration. This guide will walk you through setting up Neo4j Enterprise 5.26.0 on a Linux system (Ubuntu/Debian).</p><h3>1. Setting Up the Java Environment</h3><p>Neo4j is built on the Java Virtual Machine (JVM). For the Neo4j 5.x series, <strong>Java 17</strong> is the standard required runtime.</p><p>First, ensure your system’s package repository is up to date:</p><pre>sudo apt update</pre><h3>Installing the JDK</h3><p>It is crucial to install the correct Java version. While default-jdk might work on some systems, explicitly installing <strong>OpenJDK 17</strong> ensures compatibility and prevents startup errors associated with newer or older Java versions.</p><p>Run the following command:</p><pre>sudo apt install openjdk-17-jdk</pre><h3>Verification</h3><p>After installation, verify the version to ensure the environment is ready:</p><pre>java -version</pre><p><em>Look for output containing “openjdk version 17…”.</em></p><h3>2. Downloading and Extracting Neo4j 5.26 LTS</h3><p>We will install Neo4j in the user’s home directory. This keeps the installation contained and is ideal for development or isolated server setups.</p><p>Use wget to download the Neo4j Enterprise 5.26.0 release:</p><pre>wget -O neo4j-enterprise-5.26.0-unix.tar.gz &#39;https://neo4j.com/artifact.php?name=neo4j-enterprise-5.26.0-unix.tar.gz&#39;</pre><p>Once the download is complete, extract the archive and enter the directory:</p><pre>tar -xf neo4j-enterprise-5.26.0-unix.tar.gz<br>cd neo4j-enterprise-5.26.0</pre><h3>3. Configuration and Licensing</h3><p>Before the database can run, you must configure the environment variables and accept the license agreement. This is a mandatory step for the Enterprise Edition.</p><h3>Setting Environment Variables</h3><p>Defining NEO4J_HOME makes it easier to reference the installation path in scripts and commands. We also add the bin folder to your system PATH so you can run Neo4j commands from anywhere.</p><pre>export NEO4J_HOME=~/neo4j-enterprise-5.26.0<br>export PATH=$NEO4J_HOME/bin:$PATH</pre><blockquote><strong><em>Pro Tip:</em></strong><em> To make these variables permanent, append the lines above to your shell configuration file (e.g., </em><em>~/.bashrc or </em><em>~/.zshrc).</em></blockquote><h3>Accepting the License</h3><p>You must explicitly accept the license via the command line. Choose the option that matches your status:</p><p><strong>Option A: Commercial License</strong> (If you have a paid subscription)</p><pre>neo4j-admin server license --accept-commercial</pre><p><strong>Option B: Evaluation License</strong> (For testing and development)</p><pre>neo4j-admin server license --accept-evaluation</pre><h3>4. Troubleshooting: Managing Java Versions</h3><p>If you have multiple versions of Java installed (e.g., Java 8, 11, and 21), the system might default to an incompatible version, causing Neo4j to fail at startup.</p><p>To resolve this, force the system to use Java 17:</p><pre>sudo update-alternatives --config java</pre><p>An interactive menu will appear. Locate the selection number for <strong>java-17-openjdk</strong> and type it in.</p><h3>5. Starting the Neo4j Server</h3><p>With the configuration complete, you can now start the database instance.</p><pre>neo4j start</pre><p>The system will initialize the database engine. You should see a confirmation message indicating that the Neo4j Server is running (usually on localhost:7474).</p><h3>6. Accessing the Database via Cypher Shell</h3><p>While the browser interface is great for visualization, the <strong>Cypher Shell</strong> is the tool of choice for engineers who need to script database interactions or work directly from the terminal.</p><p>Launch the shell:</p><pre>cypher-shell</pre><h3>First Login and Security</h3><p>On your first connection, use the default credentials:</p><ul><li><strong>Username:</strong> neo4j</li><li><strong>Password:</strong> neo4j</li></ul><p>You will be immediately prompted to change this password. Ensure you choose a strong credential, as this is the root access to your data.</p><h3>7. Conclusion: Ready to Scale</h3><p>Congratulations! You have successfully installed and configured <strong>Neo4j Enterprise 5.26 LTS</strong>. By choosing the LTS version and setting up the environment manually, you have ensured a stable, production-ready foundation for your data projects.</p><p>Your database is now listening and ready for connections. The next logical step in your journey is to start modeling your domain.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a4bb8e481906" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>