<?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 Mydbops on Medium]]></title>
        <description><![CDATA[Stories by Mydbops on Medium]]></description>
        <link>https://medium.com/@mydbopsdatabasemanagement?source=rss-fd4010e3fc93------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*QIvgBhRJRox9A0HGkLKe0w.jpeg</url>
            <title>Stories by Mydbops on Medium</title>
            <link>https://medium.com/@mydbopsdatabasemanagement?source=rss-fd4010e3fc93------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 13:43:59 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@mydbopsdatabasemanagement/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[How MySQL Replication Filters Work]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/how-mysql-replication-filters-work-2bff9092e201?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/2bff9092e201</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[database-replication]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Tue, 17 Nov 2026 08:31:20 GMT</pubDate>
            <atom:updated>2026-11-17T08:31:20.066Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TkMbor1o3SpXJA1yiewv8Q.png" /></figure><p>Suppose a MySQL source has an imdb database, but a replica only needs the movies table. Where you filter matters. A filter on the source controls which changes enter its binary log. A filter on the replica controls which events that replica applies after receiving them. If the source never logs a change, none of its replicas can receive it through replication.</p><p>This guide uses imdb, imdb.movies, and imdb.actors to show what each filter matches. The snippets are separate my.cnf startup examples. Place each under [mysqld] on the server indicated.</p><h3>What MySQL Matches When It Filters by Database</h3><p>A database filter does not always match the database named in the table reference. MySQL looks at how the change was recorded:</p><ul><li><strong>Row events:</strong> MySQL checks the database that contains the changed table.</li><li><strong>Statement events:</strong> MySQL generally checks the default database selected with USE. A statement can therefore be evaluated against a different database from the one containing the table it changes.</li></ul><p>DDL such as CREATE TABLE and ALTER TABLE is logged as statements even when binlog_format=ROW. For CREATE DATABASE, ALTER DATABASE, and DROP DATABASE, MySQL checks the database named in the statement. See the <a href="https://dev.mysql.com/doc/refman/8.0/en/replication-rules-db-options.html">MySQL rules for database filters</a> for the evaluation sequence.</p><h3>Binary Log Filters on the Source</h3><p>Use these options to control what the source records in its binary log. If the goal is to give one replica a different subset of data while preserving the source’s binary log, use a filter on that replica.</p><h4>Include a database in the binary log</h4><pre>binlog-do-db=imdb</pre><p>For row events, this records changes to tables in imdb. For statement events, it generally records statements whose default database is imdb. To include more than one database, repeat the option once per database.</p><h4>Exclude a database from the binary log</h4><pre>binlog-ignore-db=imdb</pre><p>Used on its own, this excludes matching events from the source’s binary log. For row events, that means changes to tables in imdb. For statement events, the match generally depends on the default database. A statement issued from another default database can still be logged even if it modifies an imdb table.</p><h3>Replication Filters on the Replica</h3><p>Replica filters control which received events the replica applies. MySQL checks applicable database rules before table rules. If a database rule rejects an event, a table rule cannot bring it back. Likewise, passing the database check does not bypass any table rules that apply.</p><h4>Include or exclude a database</h4><p>To include events that match imdb:</p><pre>replicate-do-db=imdb</pre><p>To exclude events that match imdb:</p><pre>replicate-ignore-db=imdb</pre><p>These options use the same distinction between row and statement events. With statement events, naming imdb.movies in the statement does not make imdb the default database.</p><h4>Include or exclude an exact table</h4><p>To select imdb.movies:</p><pre>replicate-do-table=imdb.movies</pre><p>To exclude changes to imdb.actors:</p><pre>replicate-ignore-table=imdb.actors</pre><p>These examples match specific table names. The actors rule excludes that table on its own; other filters still determine what else the replica applies.</p><h4>Match tables by name pattern</h4><p>Use a wildcard filter when multiple table names share a prefix. This example selects tables in imdb whose names begin with movies, including movies itself:</p><pre>replicate-wild-do-table=imdb.movies%</pre><p>To exclude tables that match the same pattern:</p><pre>replicate-wild-ignore-table=imdb.movies%</pre><p>The wildcards work like those in SQL LIKE patterns: % matches zero or more characters, and _ matches one character.</p><h4>Apply changes under a different database name</h4><p>Use replicate-rewrite-db to map a database name on the source to a different name on the replica:</p><pre>replicate-rewrite-db=imdb-&gt;imdb_full</pre><p>For DML recorded as row events, MySQL checks the database that contains the modified table. For DML recorded as statements, it checks the current database selected with USE. The rewrite runs before other replica filters are evaluated, so the mapped name, imdb_full, matters when those filters check the event.</p><h3>Filter One Channel in Multisource Replication</h3><p>A multisource replica has a separate channel for each source. When two sources have the same database or table name, a global filter cannot express a rule intended for just one of those channels.</p><p>Suppose the second source uses a channel named source2. Put this option in the replica’s my.cnf to select imdb.movies on that channel:</p><pre>replicate-do-table=source2:imdb.movies</pre><p>The part before the colon is the configured channel name. The part after it is the table to match. This rule applies to source2; it does not change the first channel’s filters.</p><p>Binary log options change what the source records. Replica options change what the replica applies. Channel options let you make that decision separately for each source feeding the replica.</p><p>For help reviewing your MySQL replication setup, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact">contact us</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2bff9092e201" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Stop an Index Build in MongoDB]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/how-to-stop-an-index-build-in-mongodb-c6f30daf4c9e?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/c6f30daf4c9e</guid>
            <category><![CDATA[replicaset]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[database-indexing]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Mon, 16 Nov 2026 07:54:40 GMT</pubDate>
            <atom:updated>2026-11-16T07:54:40.755Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4pJFkNTGqtsbhZxUd3Dwww.png" /></figure><p>Stopping an index build and dropping a finished index can involve the same command. What matters is the state of the index when MongoDB receives it. If the build is still running, MongoDB attempts to abort it. If the build has already finished, the command can remove the completed index.</p><p>MongoDB 4.4 changed how this works on replica sets and sharded clusters by introducing simultaneous index builds across replica set members. To understand why the abort starts on the primary, it helps to see how the members start, coordinate, and finish a build.</p><h3>How MongoDB Stops an Index Build</h3><h4>Before MongoDB 4.4</h4><p>Older MongoDB releases used db.killOp(opid) to interrupt an index build. This is historical behavior. Administrators were advised against interrupting a replicated index build directly on a secondary. To remove the index after it finished, they could drop it on the primary and let secondaries replicate the drop.</p><h4>MongoDB 4.4 and later</h4><p>Use db.collection.dropIndex() or the dropIndexes command to stop a build. On a replica set, run the command on the <strong>primary</strong> and specify the exact index name. Use currentOp to identify the active build before issuing the command. Do not use db.killOp() to terminate index builds on replica sets or sharded clusters that use the simultaneous build process.</p><pre>db.collection.dropIndex(&quot;&lt;indexName&gt;&quot;)</pre><p>When the primary aborts an active build, it writes an abortIndexBuild entry to the oplog. Secondaries apply that entry and discard their work on the build. If the index finished between the status check and the drop command, the command can remove the completed index instead.</p><p>Dropping <strong>a different, already built index</strong> on the same collection is a separate case. Starting in MongoDB 5.2, MongoDB allows this while another index is building. Earlier versions can return BackgroundOperationInProgressForNamespace, so the drop may have to wait.</p><h3>How Replica Set Members Coordinate a Build</h3><h4>Feature compatibility requirements</h4><p>Simultaneous index builds were introduced in MongoDB 4.4. Each mongod in the replica set or sharded cluster must have featureCompatibilityVersion set to at least 4.4 to use this process. A MongoDB 4.4 deployment still running with FCV 4.2 uses the older sequence: the index builds on the primary first, then secondaries build it through replication. In a sharded cluster, the build runs on the shards that hold data for the collection.</p><h4>The build and commit sequence</h4><p>The primary starts the build and records a startIndexBuild entry in the oplog. Secondaries start their own builds after replicating that entry. As members become ready to commit, they report their readiness to the primary. The primary waits for the required <strong>commit quorum</strong> before it commits the build and records a commitIndexBuild entry. If the build is aborted instead, secondaries receive abortIndexBuild.</p><p>The quorum describes how many eligible members must be ready before the primary commits. It does not mean every secondary has finished and is ready to use the index when the command returns.</p><h4>Voting members and the default quorum</h4><p>The default quorum is votingMembers. It requires all voting members that store data to be ready. The majority setting is a separate option. An arbiter has an election vote but stores no data, so it does not count toward the index build quorum. A member with priority: 0 cannot become primary, but it can have either votes: 0 or votes: 1. Priority alone does not tell you whether that member votes.</p><h3>Change the Commit Quorum of an Active Build</h3><p>MongoDB 4.4 introduced setIndexCommitQuorum. The command changes how many members must report readiness for a build that is already running. setIndexCommitQuorum takes the <strong>collection name</strong>. The indexNames array must contain every index name associated with that one createIndexes() operation. It is not a list of unrelated builds to change together.</p><h4>Example: Change the quorum for two indexes</h4><p>Start a single build operation for two indexes on examples.people:</p><pre>db.getSiblingDB(&quot;examples&quot;).people.createIndexes([<br>  { name: 1 },<br>  { Address: 1 }<br>])</pre><p>MongoDB generates the names name_1 and Address_1 from the field names and ascending sort order. While that operation is still running, use a separate shell session to change its quorum from the default votingMembers to majority:</p><pre>db.getSiblingDB(&quot;examples&quot;).runCommand({<br>  setIndexCommitQuorum: &quot;people&quot;,<br>  indexNames: [&quot;name_1&quot;, &quot;Address_1&quot;],<br>  commitQuorum: &quot;majority&quot;<br>})</pre><p>The command also accepts a positive integer to specify a number of members and an optional comment to identify the operation in diagnostics. Changing the quorum adjusts when the build can commit. To cancel the build, use the drop command instead.</p><h3>What the Drop Command Locks</h3><p>On MongoDB 4.2 and later, dropping an index takes an exclusive lock on the target collection. Other operations on that collection wait until the command releases the lock. MongoDB releases before 4.2 took the exclusive lock at the database level and could also interrupt open queries on the collection. In current releases, the drop operation interrupts only queries using the index being dropped, which can include queries considering it during planning.</p><p>If the goal is to cancel the build, identify the exact index and use dropIndex() on the primary. If the goal is to let the build finish with a different readiness requirement, use setIndexCommitQuorum while the build is still active.</p><p>For help reviewing indexing behavior on your MongoDB deployment, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact">contact us</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c6f30daf4c9e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MongoDB Queryable Encryption: How to Search Encrypted Fields]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/mongodb-queryable-encryption-how-to-search-encrypted-fields-c85d9bfda508?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/c85d9bfda508</guid>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[encryption]]></category>
            <category><![CDATA[data-security]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Thu, 12 Nov 2026 06:40:23 GMT</pubDate>
            <atom:updated>2026-11-12T06:40:23.461Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gUcerkCJDmwRgTEVj4E42A.png" /></figure><p>Suppose a medical records application needs to find a patient by Social Security number. The application needs an exact match, but the database server does not need to see the number in plaintext. MongoDB Queryable Encryption lets the application encrypt that field before sending it to the server and still search for a matching record.</p><p>MongoDB stores the configured fields as randomized ciphertext. A client with access to the necessary keys can decrypt the results. The example below uses MongoDB 7.0 and PyMongo to show how to create an encrypted collection, insert a patient record, and look it up by an encrypted field.</p><h3>Automatic, Explicit, and Envelope Encryption</h3><h4>Automatic and explicit encryption</h4><p>With <strong>automatic encryption</strong>, the driver uses the collection’s encryption configuration to identify which fields and query values need encryption. Application code can use familiar methods such as insert_one() and find_one() without calling an encryption function for each value. Automatic encryption is available on MongoDB Enterprise and MongoDB Atlas.</p><p>With <strong>explicit encryption</strong>, the application calls the driver’s encryption API directly. MongoDB Community Edition supports explicit encryption, as do Enterprise and Atlas. Queryable Encryption requires a replica set or sharded cluster. Standalone deployments are unsupported.</p><h4>How envelope encryption protects the keys</h4><p>A Data Encryption Key, or DEK, encrypts the field value. A Customer Master Key, or CMK, encrypts the DEK. MongoDB stores the wrapped DEKs in a key vault collection.</p><p>This separation matters when a key is lost or deleted. Without the DEK and the ability to unwrap it, the application cannot decrypt the field values it protects. The example uses a local master key file for development. For production, use a supported key management service such as AWS KMS, Google Cloud KMS, or Azure Key Vault.</p><h3>Create a Queryable Encryption Collection with PyMongo</h3><p>Install PyMongo with its encryption dependencies:</p><pre>pip3 install &#39;pymongo[encryption]&#39;</pre><p>Download the Automatic Encryption Shared Library for your platform. The client uses this library to analyze operations and identify fields and query values that need encryption. The library path in the example is a placeholder. The local key file must already contain a valid 96 byte master key.</p><p>The patients collection has two protected fields. The application can search patientRecord.ssn by equality. The patientRecord.billing object is encrypted but has no query configuration, so the application can read it after decryption but cannot search on it.</p><pre>from bson.binary import STANDARD<br>from bson.codec_options import CodecOptions<br>from pymongo import MongoClient<br>from pymongo.encryption import ClientEncryption<br>from pymongo.encryption_options import AutoEncryptionOpts</pre><pre>uri = &quot;YOUR_MONGODB_CONNECTION_STRING&quot;<br>key_vault_namespace = &quot;encryption.__keyVault&quot;<br>database_name = &quot;medicalRecords&quot;<br>collection_name = &quot;patients&quot;</pre><pre>with open(&quot;./customer-master-key.txt&quot;, &quot;rb&quot;) as key_file:<br>    local_master_key = key_file.read()</pre><pre>kms_providers = {&quot;local&quot;: {&quot;key&quot;: local_master_key}}<br>client = MongoClient(uri)</pre><pre>encrypted_fields = {<br>    &quot;fields&quot;: [<br>        {<br>            &quot;path&quot;: &quot;patientRecord.ssn&quot;,<br>            &quot;bsonType&quot;: &quot;string&quot;,<br>            &quot;queries&quot;: [{&quot;queryType&quot;: &quot;equality&quot;}],<br>        },<br>        {<br>            &quot;path&quot;: &quot;patientRecord.billing&quot;,<br>            &quot;bsonType&quot;: &quot;object&quot;,<br>        },<br>    ]<br>}</pre><pre>with ClientEncryption(<br>    kms_providers,<br>    key_vault_namespace,<br>    client,<br>    CodecOptions(uuid_representation=STANDARD),<br>) as client_encryption:<br>    client_encryption.create_encrypted_collection(<br>        client[database_name],<br>        collection_name,<br>        encrypted_fields,<br>        &quot;local&quot;,<br>    )</pre><pre>auto_encryption_options = AutoEncryptionOpts(<br>    kms_providers,<br>    key_vault_namespace,<br>    crypt_shared_lib_path=&quot;/path/to/mongo_crypt_v1.so&quot;,<br>    crypt_shared_lib_required=True,<br>)</pre><pre>encrypted_client = MongoClient(<br>    uri,<br>    auto_encryption_opts=auto_encryption_options,<br>)<br>encrypted_collection = encrypted_client[database_name][collection_name]</pre><p>Configure Queryable Encryption when creating the collection. The setup above creates a new collection; it does not encrypt documents in an existing one. The insert and query examples below reuse encrypted_collection from this setup.</p><h3>What MongoDB Stores Alongside the Documents</h3><p>The encryption.__keyVault collection holds the wrapped DEKs. An entry contains a key identifier, encrypted keyMaterial, and information about the master key provider. In this example, that provider is local.</p><p>The encrypted collection also has two internal metadata collections, <strong>ESC</strong> and <strong>ECOC</strong>. MongoDB updates this metadata as documents with queryable encrypted fields are written. That extra work affects both storage and write performance. A field that only needs encryption, such as the billing object in this example, does not need to be configured for queries.</p><h3>Insert a Document with Encrypted Fields</h3><p>Use the client configured for automatic encryption to insert the sample patient record:</p><pre>patient_document = {<br>    &quot;patientName&quot;: &quot;Jon Doe&quot;,<br>    &quot;patientId&quot;: 12345678,<br>    &quot;patientRecord&quot;: {<br>        &quot;ssn&quot;: &quot;987-65-4320&quot;,<br>        &quot;billing&quot;: {<br>            &quot;type&quot;: &quot;Visa&quot;,<br>            &quot;number&quot;: &quot;4111111111111111&quot;,<br>        },<br>    },<br>}</pre><pre>encrypted_collection.insert_one(patient_document)</pre><p>The client encrypts the SSN and billing fields before sending the document. The patient name and ID remain unencrypted because they were not included in the collection’s encryption configuration.</p><h4>Comparing encrypted and unencrypted inserts</h4><p>In one test, inserting 100,000 documents with two encrypted fields took about <strong>18 minutes and 27.816 seconds</strong>. Inserting the same number into an unencrypted collection took about <strong>3.6 seconds</strong>. The test host ran Ubuntu 22.04 on two x86_64 CPU cores with 3.8 GB of memory.</p><p>Those timings describe that test environment. They show why it is useful to measure write performance when deciding which fields need encryption and which of those fields also need to be searchable.</p><h3>Query an Encrypted Field</h3><p>The client can use the original SSN value in an equality filter. It prepares the encrypted query, and MongoDB matches the record without receiving the SSN in plaintext. The client then decrypts the fields it has keys for.</p><pre>result = encrypted_collection.find_one({<br>    &quot;patientRecord.ssn&quot;: &quot;987-65-4320&quot;<br>})</pre><pre>print(result)</pre><h4>Supported operators and query behavior</h4><p>The MongoDB 7.0 equality configuration supports operators such as $eq, $ne, $in, and $nin, as well as logical operators such as $and, $or, $not, and $nor. A logical operator such as $or does not extend the types of comparisons an encrypted field supports.</p><p>Operator support also does not guarantee an efficient lookup. In the reported $ne example, totalKeysExamined was 0 and the operation scanned the documents. That was an observed result for that query, rather than a general rule about every query that uses $ne.</p><h4>Tuning the contention factor</h4><p>The contention factor trades write performance against the work required to find a value. The default is 8. A higher value can help inserts and updates when many writes target the same encrypted value, but it can also make searches more expensive.</p><p>If you need to specify a value, set it as an integer in the field definition when creating the collection. The following fragment illustrates the syntax; it does not change the collection created earlier.</p><pre>{<br>    &quot;path&quot;: &quot;patientRecord.ssn&quot;,<br>    &quot;bsonType&quot;: &quot;string&quot;,<br>    &quot;queries&quot;: [{&quot;queryType&quot;: &quot;equality&quot;, &quot;contention&quot;: 0}],<br>}</pre><h3>Backup and Operational Limitations</h3><p>A logical restore in the reported test failed when it tried to insert a document containing the reserved safeContent field. The error was Cannot insert a document with field name safeContent. That failure is a reason to verify the restore procedure for the encrypted collection rather than assuming a conventional dump and restore will work as expected.</p><p>Queryable Encryption also changes how you maintain and troubleshoot the collection. MongoDB 7.0 guidance calls for metadata compaction when the ECOC collection exceeds 1 GB. Some operations against encrypted collections are omitted from the slow query log and profiler, and some diagnostic fields are redacted. As a result, those tools may show less detail than they do for an unencrypted collection.</p><p>The main design choice is which sensitive fields actually need to be searchable. In the patient example, the application needs an exact match on the SSN, while it only needs to retrieve billing details after finding the record. Configuring the two fields differently keeps the required lookup available and avoids adding query metadata for a field the application never searches.</p><p>If you’re evaluating Queryable Encryption for your deployment, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact">contact us</a> to discuss your MongoDB requirements.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c85d9bfda508" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimistic and Pessimistic Locking in TiDB]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/optimistic-and-pessimistic-locking-in-tidb-8a72bd1bd590?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/8a72bd1bd590</guid>
            <category><![CDATA[database-transaction]]></category>
            <category><![CDATA[concurrency]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Thu, 12 Nov 2026 06:07:03 GMT</pubDate>
            <atom:updated>2026-11-12T06:07:03.694Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W0lr1-PfN3ZK7z1MEGmoYQ.png" /></figure><p>Two customers try to reserve the same concert seat. The booking system could hold the seat as soon as one customer selects it, or it could let both customers continue and check availability when they confirm. Either way, only one booking should succeed. What changes is when the system handles the conflict and what the second customer experiences.</p><p>TiDB offers a similar choice for concurrent transactions. In pessimistic mode, TiDB acquires locks as statements that need them execute. In optimistic mode, transactions proceed without taking those locks up front and check for write conflicts when they commit. Which mode fits depends on how often transactions modify the same rows and whether the application can retry a failed transaction.</p><h3>Pessimistic Locking in TiDB</h3><p>Think of a seat reservation that places a temporary hold on the seat. Anyone else trying to reserve it has to wait for that hold to clear. In TiDB, the hold is a lock on the relevant data, and it lasts until the transaction commits or rolls back.</p><h4>When TiDB acquires locks</h4><p>Starting a pessimistic transaction selects the mode. It does not lock rows by itself. Statements such as UPDATE and SELECT FOR UPDATE acquire locks as they execute. If another transaction tries to acquire a conflicting lock, it may have to wait. A plain SELECT can still read a snapshot without acquiring that lock.</p><p>Pessimistic mode became the default for newly created clusters in TiDB v3.0.8. Clusters upgraded from v3.0.7 or earlier retained their existing mode. TiDB supports both REPEATABLE READ and READ COMMITTED in pessimistic mode.</p><h4>Where pessimistic mode fits</h4><p>Pessimistic mode is useful when transactions frequently update the same rows. In financial transactions or order processing, for example, waiting for a lock can be preferable to completing the work and then having to retry it because a concurrent transaction committed first.</p><p>The tradeoff is that locks can make other transactions wait. If a transaction performs several reads and writes before it finishes, any locks it acquires early can be held for longer. That can reduce throughput on frequently updated rows. Deadlocks are also possible when transactions hold locks that each other needs. Longer transactions therefore make it especially important to weigh the benefit of acquiring locks early against the cost of holding them.</p><h3>Optimistic Locking in TiDB</h3><p>An online shopping cart offers a different analogy. Adding an item to the cart does not necessarily reserve it. At checkout, the system checks whether the item is still available. If another shopper bought the last unit first, checkout cannot proceed as planned.</p><h4>How commit detects conflicts</h4><p>An optimistic transaction does its work without acquiring pessimistic row locks up front. When the transaction attempts to commit, TiDB checks for conflicting writes. If another transaction has already committed an incompatible change to the same data, the commit can fail. The application then needs to handle that failure and retry the transaction if appropriate.</p><p>This shifts the cost of contention. Transactions that modify different rows can proceed without the overhead of acquiring pessimistic locks during execution. When many transactions update the same rows, however, repeated commit failures can mean doing the same work again.</p><h4>Where optimistic mode fits</h4><p>Optimistic mode can work well when updates are spread across different rows or tables, even if many transactions run at once. The key is how often their writes overlap. A workload with mostly reads and few overlapping writes may see little write contention, but optimistic mode does not inherently make plain reads faster.</p><p>The application also needs to tolerate a failed commit and be able to repeat the transaction when appropriate. That retry cost matters more as write conflicts become frequent. If transactions regularly compete for the same data, pessimistic mode may be a better fit.</p><h3>Setting the Transaction Mode</h3><p>The tidb_txn_mode system variable controls the default transaction mode. This statement sets pessimistic mode for explicit transactions in newly created sessions:</p><pre>SET GLOBAL tidb_txn_mode = &#39;pessimistic&#39;;</pre><p>To select pessimistic mode for one transaction, start it explicitly:</p><pre>BEGIN PESSIMISTIC;</pre><p>BEGIN OPTIMISTIC; selects optimistic mode for one transaction. Both forms take precedence over tidb_txn_mode for the transaction they start.</p><h3>How Snapshot Isolation Fits In</h3><p>Transaction mode and isolation level answer different questions. The mode determines how TiDB handles competing writes. The isolation level determines which versions of the data a transaction can see.</p><p>TiDB implements its default REPEATABLE READ isolation level using snapshot isolation. Multiversion concurrency control, or MVCC, lets ordinary reads use a consistent view of committed data even while other transactions change that data. Pessimistic transactions can still use these snapshot reads. When a statement needs to lock the latest committed version, such as SELECT FOR UPDATE, it performs a current read instead.</p><h3>Choosing Between the Two Modes</h3><p>When several transactions repeatedly update the same rows, pessimistic mode can reduce the amount of work that has to be repeated after a failed commit. The cost is lock acquisition and the time other transactions spend waiting. When overlapping writes are rare, optimistic mode avoids acquiring those locks during execution, but the application must be prepared for a commit to fail.</p><p>Start with the workload: how often do writes target the same rows, how long would those rows stay locked, and what would it cost to run the transaction again? Those answers help determine whether waiting for a lock or handling an occasional retry makes more sense.</p><p>If you’re evaluating transaction modes for a TiDB workload, explore <a href="https://www.mydbops.com/tidb/consulting-service">TiDB consulting from Mydbops</a> or <a href="https://www.mydbops.com/contact">contact us</a> to discuss your requirements.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8a72bd1bd590" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Converting a Physical Standby to a Logical Subscriber in PostgreSQL 17]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/converting-a-physical-standby-to-a-logical-subscriber-in-postgresql-17-00bd388dcc1e?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/00bd388dcc1e</guid>
            <category><![CDATA[logical-replication]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Wed, 11 Nov 2026 07:03:13 GMT</pubDate>
            <atom:updated>2026-11-11T07:03:13.079Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ABoeQ8NvD4phno6aFFHjTg.png" /></figure><p>Setting up logical replication on a large PostgreSQL database can take longer than creating the publication and subscription. The initial table copy has to finish, and the subscriber must also apply changes made on the publisher while that copy was running. On a busy system, that can mean more write ahead log, or WAL, to retain and more changes to apply before the subscriber catches up.</p><p>PostgreSQL 17 introduces pg_createsubscriber for cases where the target already has the data. It converts a physical standby into a logical subscriber and uses the data on that standby to skip the initial logical table copy. The utility still needs to synchronize the target at the point where physical recovery ends and logical replication takes over.</p><h3><strong>Why the Initial Copy Matters</strong></h3><p>In a conventional logical replication setup, you create a publication on the source and a subscription on the destination. When initial copying is enabled, table synchronization workers copy the published tables. Changes made during that process must also reach the subscriber.</p><p>The longer the copy takes, the longer the publisher may need to retain WAL required by the replication slots. On a server with a high write rate, this can put pressure on the volume holding pg_wal. The subscriber also has more work to do before it catches up.</p><p>If you already have a physical standby, its data provides the starting point for pg_createsubscriber. If you need to build a standby first, the question is whether a physical base backup and conversion make sense compared with copying the published tables directly.</p><h3><strong>Comparing the Two Methods in a 30 GB Lab</strong></h3><p>The lab used two PostgreSQL servers and a dataset of approximately 30 GB. It compared the time to take a physical base backup with the time spent on the initial logical table synchronization.</p><h4><strong>Physical base backup</strong></h4><p>The pg_basebackup run waited for a checkpoint, started streaming WAL in the background, and created a replication slot named slot1. The progress output reached 32340756 / 32340756 kB, covering one tablespace. After streaming finished, the utility synced the files to disk and completed the backup.</p><p>The physical base backup took about <strong>5 minutes</strong> in this lab.</p><h4><strong>Logical table synchronization</strong></h4><p>In the logical replication test, four table synchronization workers started for the my_sub subscription. They handled employers_1, employees, employers, and employees_1.</p><p>The workers started at around 10:10:17 UTC. The final worker, which was synchronizing employees, finished at 10:34:54.024 UTC. The initial logical synchronization took about <strong>24 minutes</strong> in this lab.</p><p>These are timings for the two initial data transfer methods under the demonstrated conditions. They do not compare the total time needed for every step in either setup. If you only need to replicate a few tables, copying those tables directly can be a better fit than building a physical standby that contains the whole cluster.</p><h3><strong>Before You Convert the Standby</strong></h3><p>In this lab, the primary was at 172.31.0.200, the standby was at 172.31.8.22, and the database selected for logical replication was test. Physical streaming replication was already configured. Run pg_createsubscriber on the standby host.</p><p>Before proceeding, check the following:</p><ul><li>The publisher, physical standby, and pg_createsubscriber binary must use the same PostgreSQL major version. The source and target data directories must have the same system identifier, and the source must be out of recovery.</li><li>The publisher must accept connections from the target, use wal_level=logical, and have enough replication slots and WAL senders. The publisher connection role must be able to create the publication and logical slot and perform the required replication operations.</li><li>The target must accept local connections. The local database role needs privileges to create subscriptions and use pg_replication_origin_advance().</li><li>On the target, max_replication_slots and max_logical_replication_workers must each be at least the number of databases being converted. max_worker_processes must be greater than that number.</li><li>Keep the schemas aligned during the conversion. Logical replication does not replicate DDL. The utility creates a publication for all tables in each selected database.</li></ul><p>While the standby is still running, confirm that it is in recovery:</p><pre>SELECT pg_is_in_recovery();</pre><p>The standby returned t in the lab. That confirms it is in recovery; it does not establish how far it has caught up with the primary. The two servers were in sync before the lab conversion.</p><h3><strong>Run the Dry Run with the Standby Stopped</strong></h3><p>The first dry run was attempted while the standby was still running. The utility reported:</p><pre>pg_createsubscriber: error: standby server is running<br>pg_createsubscriber: hint: Stop the standby server and try again.</pre><p>The lab used PostgreSQL 17 binaries under /usr/lib/postgresql/17/bin. On the standby host, check the running instance and stop it:</p><pre>/usr/lib/postgresql/17/bin/pg_ctl \<br>  -D /var/lib/postgresql/17/main status</pre><pre>/usr/lib/postgresql/17/bin/pg_ctl \<br>  -D /var/lib/postgresql/17/main stop</pre><p>The status output showed that the running instance used /etc/postgresql/17/main/postgresql.conf. Because this file is outside the data directory, pass it explicitly to the conversion utility.</p><p>Run the dry run as the PostgreSQL operating system user on the standby. The publisher password below is a placeholder.</p><pre>/usr/lib/postgresql/17/bin/pg_createsubscriber \<br>  --database=test \<br>  --pgdata=/var/lib/postgresql/17/main \<br>  --config-file=/etc/postgresql/17/main/postgresql.conf \<br>  --subscriber-port=5432 \<br>  --publisher-server=&#39;host=172.31.0.200 user=replicator password=YOUR_PASSWORD&#39; \<br>  --subscriber-username=postgres \<br>  --publication=demo_pub1 \<br>  --subscription=demo_sub1 \<br>  --dry-run</pre><p>The standby must be stopped before the command runs, but the utility starts it temporarily to carry out its checks. In the lab logs, PostgreSQL entered standby mode, reached a consistent recovery state, accepted local connections, and briefly streamed WAL before shutting down again.</p><p>The dry run returned exit status 0:</p><pre>echo $?</pre><p>That means the dry run completed successfully. It does not mean the standby has been converted or that the actual cutover is guaranteed to succeed.</p><h3><strong>Convert the Standby to a Logical Subscriber</strong></h3><p>With the standby stopped, rerun the same command without --dry-run:</p><pre>/usr/lib/postgresql/17/bin/pg_createsubscriber \<br>  --database=test \<br>  --pgdata=/var/lib/postgresql/17/main \<br>  --config-file=/etc/postgresql/17/main/postgresql.conf \<br>  --subscriber-port=5432 \<br>  --publisher-server=&#39;host=172.31.0.200 user=replicator password=YOUR_PASSWORD&#39; \<br>  --subscriber-username=postgres \<br>  --publication=demo_pub1 \<br>  --subscription=demo_sub1</pre><p>The utility coordinates the end of physical recovery with the starting position for logical replication. In the lab, recovery stopped at WAL location E/C3423C28, PostgreSQL selected timeline 2, and archive recovery completed. Those values describe the lab run; they are not values to put into the command.</p><p>This step changes the target’s role. After promotion, it no longer follows the source as a physical standby. If the conversion fails after promotion, you may need to rebuild the target before retrying. Any physical standby downstream of the converted node will also need to be rebuilt.</p><h3><strong>Start the Subscriber and Verify Replication</strong></h3><p>After the utility completes successfully, start the target using its normal configuration. The lab paths correspond to this pg_ctl command:</p><pre>/usr/lib/postgresql/17/bin/pg_ctl \<br>  -D /var/lib/postgresql/17/main \<br>  -o &quot;-c config_file=/etc/postgresql/17/main/postgresql.conf&quot; \<br>  start</pre><p>The lab startup log showed the logical replication apply worker for demo_sub1 starting. To confirm the replication objects, connect to test on the publisher and query the publication:</p><pre>SELECT pubname, puballtables<br>FROM pg_publication<br>WHERE pubname = &#39;demo_pub1&#39;;</pre><p>The lab showed puballtables = t, confirming that demo_pub1 publishes all tables in test.</p><p>Connect to test on the subscriber and check the subscription, its slot name, and the publication it follows:</p><pre>SELECT subname, subenabled, subslotname, subpublications<br>FROM pg_subscription<br>WHERE subname = &#39;demo_sub1&#39;<br>  AND subdbid = (<br>    SELECT oid FROM pg_database WHERE datname = current_database()<br>  );</pre><p>In the lab, demo_sub1 was enabled, its slot was named demo_sub1, and its publication list contained demo_pub1. Selecting these fields avoids printing the publisher connection string.</p><p>The lab then checked that a specific row existed on the subscriber:</p><pre>SELECT *<br>FROM employers_1<br>WHERE id = 456961;</pre><p>The query returned one row. On the <strong>lab publisher</strong>, the corresponding row was deleted:</p><pre>DELETE FROM employers_1<br>WHERE id = 456961;</pre><p>The publisher returned DELETE 1. After the change reached the subscriber, the same SELECT there returned zero rows. This verified that the subscriber applied the deletion for that row in employers_1.</p><h3><strong>Conclusion</strong></h3><p>pg_createsubscriber lets PostgreSQL 17 reuse the data on a physical standby when setting up logical replication. That can save an initial table copy, particularly when a suitable standby is already available. The decision depends on how much data you need to replicate and whether you can take the standby out of its physical replication role.</p><p>For help planning or validating a PostgreSQL replication change, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact">contact the team</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=00bd388dcc1e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MariaDB Backup and Recovery with MariaBackup]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/mariadb-backup-and-recovery-with-mariabackup-8bef03ab64ae?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/8bef03ab64ae</guid>
            <category><![CDATA[mariadb]]></category>
            <category><![CDATA[database-backup]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Wed, 11 Nov 2026 06:13:19 GMT</pubDate>
            <atom:updated>2026-11-11T06:13:19.561Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="backup-and-recovery-using-mariabackup" src="https://cdn-images-1.medium.com/max/1024/1*Tlfx72T56KxPdxyEgAbnJA.png" /></figure><p>A physical backup gives you a copy of the database files you need to recover from a hardware failure, a server crash, or an accidental change. With MariaBackup, recovery depends on more than taking the backup: you also need to prepare the files and, if you use incremental backups, apply them in the right order.</p><p>This walkthrough uses <strong>MariaDB 10.4.12</strong> on Linux. It starts with a full backup, adds two incremental backups, and restores the database through the second incremental. The commands use mariabackup, the executable name in the demonstrated environment. Current MariaDB documentation calls the tool <a href="https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/mariadb-backup-options">mariadb-backup</a>.</p><h3><strong>What MariaBackup Does</strong></h3><p>MariaBackup is an open source tool for taking physical backups of MariaDB. It was forked from Percona XtraBackup 2.3.8. MariaDB’s implementation of InnoDB page compression and transparent data encryption made compatibility with Percona XtraBackup a concern, even though the tools share their origins. The <a href="https://www.mydbops.com/blog/hot-backup-for-myrocks-rocksdb-using-percona-xtrabackup">MyRocks backup walkthrough with Percona XtraBackup</a> covers that tool separately.</p><p>MariaBackup supports hot backups of InnoDB and can also back up MyRocks, Aria, and MyISAM tables. It supports Linux and Windows and can serve as a State Snapshot Transfer method for MariaDB Galera Cluster.</p><p>The tool first appeared in MariaDB 10.1.23 and 10.2.7. It became generally available in MariaDB 10.1.26 and 10.2.10.</p><h3><strong>Full, Incremental, and Partial Backups</strong></h3><p>A <strong>full backup</strong> copies the database server’s files into a separate directory. It can be prepared and restored on its own, and it can also serve as the base for incremental backups.</p><p>An <strong>incremental backup</strong> captures changes relative to a specified base. For InnoDB, MariaBackup uses log sequence numbers, or LSNs, to identify pages that changed. The base can be the full backup or a previous incremental backup. In the example below, inc1 is based on the full backup, and inc2 is based on inc1.</p><p>A <strong>partial backup</strong> selects specific databases or tables. For the individual InnoDB table or partition recovery discussed here, the object must have its own tablespace file. A table stored in the shared system tablespace, ibdata1, cannot be recovered this way.</p><h3><strong>Recovering an Individual Table or Partition</strong></h3><p>A full backup can also provide the files needed to recover an eligible InnoDB table or partition without restoring the entire instance. Using --export with --prepare prepares the tablespace files for import.</p><p>The export is only the preparation step. You still need to import the tablespace into the destination table. The full instance restore later in this walkthrough uses --copy-back or --move-back instead.</p><h3><strong>Take a Full Backup</strong></h3><p>Run mariabackup with --backup and set --target-dir to an empty directory. The backup account in this example is mariabackup; the password below is a placeholder.</p><pre>mariabackup --backup \<br>  --target-dir=/home/vagrant/backup/ \<br>  --user=mariabackup \<br>  --password=&#39;&lt;backup-account-password&gt;&#39;</pre><p>The demonstrated run connected to MariaDB 10.4.12 and read files from /var/lib/mysql/. The log showed it loading the InnoDB configuration, listing tablespaces, scanning redo, and copying database files into the backup directory.</p><p>A few lines from the historical run show the server version, source directory, and redo position:</p><pre>Using server version 10.4.12-MariaDB<br>cd to /var/lib/mysql/<br>&gt;&gt; log scanned up to (27719740831)</pre><p>The resulting directory contained database folders and files such as ibdata1, backup-my.cnf, and aria_log.00000001. At this stage, the files have been copied, but the backup has yet to be prepared for restore.</p><h3><strong>Take the First Incremental Backup</strong></h3><p>After the database has changed, create the first incremental in a new directory. --target-dir specifies where the new backup goes. --incremental-basedir points to the backup that determines where the new incremental starts.</p><pre>mariabackup --backup \<br>  --target-dir=/home/vagrant/inc1/ \<br>  --incremental-basedir=/home/vagrant/backup/ \<br>  --user=mariabackup \<br>  --password=&#39;&lt;backup-account-password&gt;&#39;</pre><p>The historical run reported that it started from LSN 27719740822 and copied redo through LSN 27719740831. The log also recorded the end of the backup stage, the release of table locks, and the writing of backup metadata such as backup-my.cnf and xtrabackup_info. It finished with completed OK!.</p><p>The LSN values describe that run. The tool reads the starting position from the backup specified as the base, so you do not enter those numbers in the command.</p><h3><strong>Take the Second Incremental Backup</strong></h3><p>For the next backup, give the tool a new target directory and point --incremental-basedir to inc1:</p><pre>mariabackup --backup \<br>  --target-dir=/home/vagrant/inc2/ \<br>  --incremental-basedir=/home/vagrant/inc1/ \<br>  --user=mariabackup \<br>  --password=&#39;&lt;backup-account-password&gt;&#39;</pre><p>This creates a chain: the full backup, inc1, then inc2. Because inc2 starts from inc1, applying inc2 alone to the full backup would leave out the changes captured in the first incremental.</p><h3><strong>Prepare the Backup for Recovery</strong></h3><p>The files in a physical backup are copied at different times while the server is running. --prepare brings them into a consistent state for restore. When you supply --incremental-dir, preparation also applies the changes from that incremental to the base backup.</p><p>First prepare the full backup:</p><pre>mariabackup --prepare \<br>  --target-dir=/home/vagrant/backup/</pre><p>Then apply the first incremental to the full backup directory:</p><pre>mariabackup --prepare \<br>  --target-dir=/home/vagrant/backup/ \<br>  --incremental-dir=/home/vagrant/inc1/</pre><p>In the demonstrated run, the log identified the incremental’s starting LSN and noted that the target had already been prepared:</p><pre>incremental backup from 27719740822 is enabled.<br>This target seems to be already prepared.</pre><p>The log went on to show files and metadata being copied from inc1 into the base directory, then reported successful completion.</p><p>To recover through the second incremental, apply inc2 next:</p><pre>mariabackup --prepare \<br>  --target-dir=/home/vagrant/backup/ \<br>  --incremental-dir=/home/vagrant/inc2/</pre><p>Keep --target-dir pointed at the <strong>full backup directory</strong> for every prepare command. Only --incremental-dir changes. If you want to recover through inc1 instead, stop after applying it. The prepared files you restore are in /home/vagrant/backup/ in either case.</p><p>--apply-log-only is a legacy option and is not part of the MariaDB 10.4.12 sequence shown here. Use a backup utility version compatible with the MariaDB server that produced the backup.</p><h3><strong>Restore the Prepared Backup</strong></h3><p>Stop MariaDB before restoring. Confirm that the destination data directory is empty, along with any separately configured InnoDB directories that the restore will populate. The --target-dir argument below points to the <strong>prepared backup</strong>, not the destination data directory.</p><p>Use --copy-back to restore the data while keeping the prepared backup files:</p><pre>mariabackup --copy-back \<br>  --target-dir=/home/vagrant/backup/</pre><p>Alternatively, use --move-back to move those files into the server’s data directory. The moved files will no longer be in the backup directory:</p><pre>mariabackup --move-back \<br>  --target-dir=/home/vagrant/backup/</pre><p>Choose one restore method. After the restore, make sure the files belong to the account that runs MariaDB. On the Linux setup in this example, the account and group are both mysql, and the data directory is /var/lib/mysql/:</p><pre>chown -R mysql:mysql /var/lib/mysql/</pre><p>Once ownership is correct, start MariaDB.</p><h3><strong>Conclusion</strong></h3><p>A full backup provides the base for recovery. If you use incrementals, prepare that base and apply each incremental in order until you reach the backup you want to restore. Then restore the prepared directory. Use --copy-back if you want to retain the prepared files, or --move-back if you intend to relocate them.</p><p>For help planning MariaDB backups or validating a recovery procedure, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact">contact the team</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8bef03ab64ae" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Online Schema Changes on MySQL Tables with Existing Triggers]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/online-schema-changes-on-mysql-tables-with-existing-triggers-d457c93c619a?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/d457c93c619a</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[percona]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Thu, 05 Nov 2026 06:21:30 GMT</pubDate>
            <atom:updated>2026-11-05T06:21:30.871Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1JgNJ7gBPSQ4Ys4kpvTdMg.png" /></figure><p>Changing a table’s structure becomes more complicated when that table already has triggers. An online schema change tool must keep the original table and its replacement synchronized while preserving the application’s existing trigger behavior.</p><p>Percona Toolkit’s pt-online-schema-change, often shortened to pt-osc, uses triggers to capture changes during the migration. Support for multiple triggers in MySQL made it possible for the tool’s synchronization triggers to coexist with triggers already defined on the table.</p><p>The following walkthrough compares MySQL 5.6.32 and MySQL 5.7.19 using Percona Toolkit 3.0.6. These are historical examples that demonstrate the limitation and how --preserve-triggers addresses it.</p><h3><strong>How pt-online-schema-change Works</strong></h3><p>Rather than altering the original table directly, pt-osc creates a replacement table and applies the requested schema change to it.</p><p>It then creates AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the original table. These triggers synchronize incoming changes while existing rows are copied into the replacement.</p><p>After copying completes, the tool swaps the table names and performs cleanup. By default, that cleanup includes removing the old table.</p><p>This approach is useful when working with large tables in environments such as <a href="https://www.mydbops.com/blog/proxysql-series-percona-cluster-mariadb-cluster-galera-read-write-split">Percona Cluster</a>. However, table size alone does not establish a fixed boundary between Total Order Isolation, or TOI, and Rolling Schema Upgrade, or RSU.</p><p>For PXC, pt-osc requires InnoDB tables and wsrep_OSU_method=TOI. Preserving triggers does not eliminate the locking and workload considerations associated with copying data and swapping tables.</p><h3><strong>Why Existing Triggers Were a Limitation</strong></h3><p>Through MySQL 5.6, a table could not have multiple triggers with the same event and action time.</p><p>For example, two BEFORE UPDATE triggers on the same table were not allowed. A BEFORE UPDATE trigger and an AFTER UPDATE trigger could coexist because their action times differed.</p><p>This restriction complicated the use of <a href="https://www.mydbops.com/blog-tags/tools">MySQL administration tools</a> that needed to create their own triggers.</p><p>With the Toolkit version demonstrated here, pt-osc refused to operate on a table that already had triggers. Removing those triggers before the operation and recreating them afterward required carefully managing the period when their application logic would be absent.</p><h3><strong>The MySQL 5.6.32 Example</strong></h3><p>First, check the Toolkit version:</p><pre>pt-online-schema-change --version</pre><p>The demonstrated environment returned:</p><pre>pt-online-schema-change 3.0.6</pre><p>The schema change targeted the employees table in the mydbops database:</p><pre>pt-online-schema-change D=mydbops,t=employees,h=localhost \<br>  --user=root \<br>  --alter &quot;drop column test,add column test text&quot; \<br>  --no-version-check \<br>  --execute</pre><p>Because the table already had triggers, the tool stopped with this message:</p><pre>The table `mydbops`.`employees` has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers.</pre><p>The operation did not proceed to copying rows or replacing the table.</p><h3><strong>What Changed with MySQL 5.7</strong></h3><p>Beginning with MySQL 5.7.2, a table could have multiple triggers sharing the same event and action time. This allowed pt-osc’s synchronization triggers to coexist with application triggers.</p><p>Percona Toolkit 3.0.4 introduced the --preserve-triggers option. With a compatible MySQL version, this option allows pt-osc to preserve existing triggers as part of the schema change.</p><p>That capability is the relevant distinction in this <a href="https://www.mydbops.com/blog-tags/mysql-5-7">MySQL 5.7 example</a>. It should not be read as a current recommendation to upgrade to that historical release.</p><h3><strong>Running with — preserve-triggers</strong></h3><p>On MySQL 5.7.19, the demonstrated Toolkit version was again 3.0.6:</p><pre>pt-online-schema-change --version</pre><pre>pt-online-schema-change 3.0.6</pre><p>The schema change command included --preserve-triggers:</p><pre>pt-online-schema-change D=mydbops,t=employees,h=localhost \<br>  --user=root \<br>  --alter &quot;drop column test,add column test text&quot; \<br>  --no-version-check \<br>  --preserve-triggers \<br>  --execute</pre><p>The historical execution output showed the complete sequence:</p><pre>Operation, tries, wait:<br>  analyze_table, 10, 1<br>  copy_rows, 10, 0.25<br>  create_triggers, 10, 1<br>  drop_triggers, 10, 1<br>  swap_tables, 10, 1<br>  update_foreign_keys, 10, 1<br>Altering `mydbops`.`employees`...<br>Creating new table...<br>Created new table mydbops._employees_new OK.<br>Altering new table...<br>Altered `mydbops`.`_employees_new` OK.<br>2018-03-02T07:27:35 Creating triggers...<br>2018-03-02T07:27:35 Created triggers OK.<br>2018-03-02T07:27:35 Copying approximately 10777 rows...<br>2018-03-02T07:27:35 Copied rows OK.<br>2018-03-02T07:27:35 Adding original triggers to new table.<br>2018-03-02T07:27:35 Analyzing new table...<br>2018-03-02T07:27:35 Swapping tables...<br>2018-03-02T07:27:36 Swapped original and new tables OK.<br>2018-03-02T07:27:36 Dropping old table...<br>2018-03-02T07:27:36 Dropped old table `mydbops`.`_employees_old` OK.<br>2018-03-02T07:27:36 Dropping triggers...<br>2018-03-02T07:27:36 Dropped triggers OK.<br>Successfully altered `mydbops`.`employees`.</pre><p>This output documents the demonstrated run. Its timestamps and row count are not predictions for another environment.</p><h3><strong>Following the Operation Through Debug Output</strong></h3><p>The debug trace, captured with PTDEBUG=1, makes the individual stages easier to understand. The following excerpts preserve the SQL from that historical trace while omitting internal debug prefixes.</p><h4><strong>1. Create and Alter the Replacement Table</strong></h4><p>The tool inspected the original definition:</p><pre>SHOW CREATE TABLE `mydbops`.`employees`;</pre><p>It then created the replacement table:</p><pre>CREATE TABLE `mydbops`.`_employees_new` (<br>  `employeeNumber` int(11) NOT NULL,<br>  `lastName` varchar(50) DEFAULT NULL,<br>  `firstName` varchar(50) DEFAULT NULL,<br>  `extension` varchar(10) DEFAULT NULL,<br>  `email` varchar(100) DEFAULT NULL,<br>  `officeCode` varchar(10) DEFAULT NULL,<br>  `reportsTo` int(11) DEFAULT NULL,<br>  `jobTitle` varchar(50) DEFAULT NULL,<br>  `test` text,<br>  PRIMARY KEY (`employeeNumber`),<br>  KEY `reportsTo` (`reportsTo`),<br>  KEY `officeCode` (`officeCode`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1;</pre><p>The requested alteration was applied to this replacement:</p><pre>ALTER TABLE `mydbops`.`_employees_new`<br>  DROP COLUMN test,<br>  ADD COLUMN test text;</pre><p>At this stage, the original employees table remained in place.</p><h4><strong>2. Create Synchronization Triggers</strong></h4><p>The tool created three triggers on the original table to propagate changes to _employees_new.</p><p>The delete trigger removed the corresponding replacement row:</p><pre>CREATE TRIGGER `pt_osc_mydbops_employees_del`<br>AFTER DELETE ON `mydbops`.`employees`<br>FOR EACH ROW<br>DELETE IGNORE FROM `mydbops`.`_employees_new`<br>WHERE `mydbops`.`_employees_new`.`employeenumber`<br>      &lt;=&gt; OLD.`employeenumber`;</pre><p>The update trigger handled changes to the key and wrote the new row values:</p><pre>CREATE TRIGGER `pt_osc_mydbops_employees_upd`<br>AFTER UPDATE ON `mydbops`.`employees`<br>FOR EACH ROW<br>BEGIN<br>  DELETE IGNORE FROM `mydbops`.`_employees_new`<br>  WHERE !(OLD.`employeenumber` &lt;=&gt; NEW.`employeenumber`)<br>    AND `mydbops`.`_employees_new`.`employeenumber`<br>        &lt;=&gt; OLD.`employeenumber`;</pre><pre>  REPLACE INTO `mydbops`.`_employees_new`<br>    (`employeenumber`, `lastname`, `firstname`,<br>     `extension`, `email`, `officecode`,<br>     `reportsto`, `jobtitle`, `test`)<br>  VALUES<br>    (NEW.`employeenumber`, NEW.`lastname`, NEW.`firstname`,<br>     NEW.`extension`, NEW.`email`, NEW.`officecode`,<br>     NEW.`reportsto`, NEW.`jobtitle`, NEW.`test`);<br>END;</pre><p>The insert trigger copied incoming rows:</p><pre>CREATE TRIGGER `pt_osc_mydbops_employees_ins`<br>AFTER INSERT ON `mydbops`.`employees`<br>FOR EACH ROW<br>REPLACE INTO `mydbops`.`_employees_new`<br>  (`employeenumber`, `lastname`, `firstname`,<br>   `extension`, `email`, `officecode`,<br>   `reportsto`, `jobtitle`, `test`)<br>VALUES<br>  (NEW.`employeenumber`, NEW.`lastname`, NEW.`firstname`,<br>   NEW.`extension`, NEW.`email`, NEW.`officecode`,<br>   NEW.`reportsto`, NEW.`jobtitle`, NEW.`test`);</pre><p>These are tool generated statements from the debug trace, not a separate set of triggers to install manually.</p><h4><strong>3. Copy the Existing Rows</strong></h4><p>With synchronization in place, pt-osc copied the existing records in chunks.</p><p>The trace showed this prepared statement:</p><pre>INSERT LOW_PRIORITY IGNORE INTO<br>  `mydbops`.`_employees_new`<br>  (`employeenumber`, `lastname`, `firstname`,<br>   `extension`, `email`, `officecode`,<br>   `reportsto`, `jobtitle`, `test`)<br>SELECT<br>  `employeenumber`, `lastname`, `firstname`,<br>  `extension`, `email`, `officecode`,<br>  `reportsto`, `jobtitle`, `test`<br>FROM `mydbops`.`employees`<br>FORCE INDEX (`PRIMARY`)<br>WHERE ((`employeenumber` &gt;= ?))<br>  AND ((`employeenumber` &lt;= ?))<br>LOCK IN SHARE MODE<br>/*pt-online-schema-change 2845 copy nibble*/</pre><p>The ? markers represent bound parameters. For the chunk displayed in the trace, the lower boundary was 0 and the upper boundary was 2335.</p><p>While existing rows were copied, the synchronization triggers reflected incoming changes in the replacement table.</p><h4><strong>4. Preserve the Application Trigger</strong></h4><p>The demonstrated table had an existing BEFORE UPDATE trigger that recorded previous employee values in an audit table.</p><p>The historical trace showed that trigger being created on _employees_new:</p><pre>CREATE DEFINER=`root`@`%`<br>TRIGGER `mydbops`.`mydbops_employee_update`<br>BEFORE UPDATE ON _employees_new<br>FOR EACH ROW<br>BEGIN<br>  INSERT INTO employees_audit<br>  SET action = &#39;update&#39;,<br>      employeeNumber = OLD.employeeNumber,<br>      lastname = OLD.lastname,<br>      changedat = NOW();<br>END;</pre><p>The application trigger serves a different purpose from the temporary pt-osc triggers. It records audit information, while the tool’s triggers synchronize the two tables.</p><p>The sequence shown here belongs to Toolkit 3.0.6. Current documentation also describes attempting to copy existing triggers before row copying begins to verify that they remain compatible with the altered table.</p><p>Consequently, --preserve-triggers cannot make every schema change valid. Dropping a column that a preserved trigger references can prevent that trigger from working.</p><h4><strong>5. Swap the Tables and Clean Up</strong></h4><p>Before the swap, the trace showed the replacement table being analyzed:</p><pre>ANALYZE TABLE `mydbops`.`_employees_new`;</pre><p>The tool then renamed both tables in one statement:</p><pre>RENAME TABLE<br>  `mydbops`.`employees` TO `mydbops`.`_employees_old`,<br>  `mydbops`.`_employees_new` TO `mydbops`.`employees`;</pre><p>The replacement now had the original table name.</p><p>Cleanup removed the old table:</p><pre>DROP TABLE IF EXISTS `mydbops`.`_employees_old`;</pre><p>The trace also showed cleanup statements for the temporary synchronization triggers:</p><pre>DROP TRIGGER IF EXISTS<br>  `mydbops`.`pt_osc_mydbops_employees_del`;</pre><pre>DROP TRIGGER IF EXISTS<br>  `mydbops`.`pt_osc_mydbops_employees_upd`;</pre><pre>DROP TRIGGER IF EXISTS<br>  `mydbops`.`pt_osc_mydbops_employees_ins`;</pre><p>The application trigger was preserved on the replacement table. Removing the temporary synchronization triggers did not mean removing the application’s audit logic.</p><h3><strong>Conclusion</strong></h3><p>Existing triggers were a significant limitation for pt-osc on MySQL 5.6. Support for multiple triggers with the same event and action time, introduced in MySQL 5.7.2, enabled Toolkit’s --preserve-triggers functionality.</p><p>The walkthrough shows how the tool creates and alters a replacement table, synchronizes incoming changes, copies existing rows, preserves the application trigger, and swaps the tables.</p><p>The option addresses trigger preservation, but compatibility with the altered schema and the operational impact of the migration still matter.</p><p>For help planning MySQL schema changes, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact-us">contact the team</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d457c93c619a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why MySQL Doesn’t Always Use an Index]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/why-mysql-doesnt-always-use-an-index-e2563897316d?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/e2563897316d</guid>
            <category><![CDATA[query-optimization]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[database-performance]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Wed, 04 Nov 2026 07:19:20 GMT</pubDate>
            <atom:updated>2026-11-04T07:19:20.756Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="mysql-query-optimization-why-the-optimizer-skips-indexes" src="https://cdn-images-1.medium.com/max/1024/1*-wLcrEf4YNGgZsYkJ54agg.png" /></figure><p>You add an index, run EXPLAIN, and see key: NULL. That can mean MySQL estimated that another access method would cost less, or that the index didn’t support a direct lookup for the condition you wrote. The distinction matters because each case calls for a different response.</p><h3>When a Table Scan Looks Cheaper</h3><p>Consider the salaries table in the employees database. It has an index called idx_salary on the salary column. The following query asks for every column from rows where the salary exceeds 50000:</p><pre>EXPLAIN<br>SELECT *<br>FROM salaries<br>WHERE salary &gt; 50000\G</pre><p>In the example, possible_keys lists idx_salary, but key is NULL and type is ALL. MySQL identified the index as a candidate and chose a full table scan. The rows value of 815849 is the optimizer’s estimate of how many rows it would examine for that plan.</p><p>A separate query counts the rows that actually satisfy the condition:</p><pre>SELECT COUNT(*)<br>FROM salaries<br>WHERE salary &gt; 50000;</pre><p>That query returned 621322. With so many matching rows and a query that requests every column, the optimizer can estimate that scanning the table costs less than reading index entries and fetching the corresponding rows. The two figures describe different things, and neither is a timing measurement.</p><h3>How Cardinality and Statistics Affect the Plan</h3><p>Cardinality describes how many distinct values a column or index prefix has. When many rows share the same value, an index lookup for that value can still leave MySQL with a large number of rows to fetch. How useful the index is depends on the condition in the query and how the values are distributed.</p><p>To count the distinct nonnull salaries directly, run:</p><pre>SELECT COUNT(DISTINCT salary) AS cardinality<br>FROM salaries;</pre><p>If the index already exists, inspect its statistics:</p><pre>SHOW INDEX FROM salaries<br>WHERE Key_name = &#39;idx_salary&#39;;</pre><p>The Cardinality field in <a href="https://dev.mysql.com/doc/refman/8.0/en/show-index.html">SHOW INDEX</a> is an estimate. It can differ from the count returned by COUNT(DISTINCT salary).</p><p>Statistics help MySQL estimate how many rows each access method will read. The optimizer combines those estimates with its <a href="https://dev.mysql.com/doc/refman/8.0/en/cost-model.html">cost model</a> and the access methods available to the query. If the statistics are stale or inaccurate, refreshing them can improve the estimates the optimizer uses to compare plans.</p><h3>When the Predicate Uses a Function</h3><p>An index on birth_date stores date values. The following condition asks MySQL to find rows based on the month extracted from each date:</p><pre>EXPLAIN<br>SELECT emp_no, birth_date, first_name<br>FROM employees<br>WHERE MONTH(birth_date) = 9\G</pre><p>In the first example plan, possible_keys and key are both NULL, and the access type is ALL. The ordinary index on birth_date doesn’t provide a direct lookup for MONTH(birth_date) = 9.</p><p>After a functional index on MONTH(birth_date) is added, the example shows type: ref and key: functional_index for the same query. The functional index gives MySQL an access path for the expression. The ordinary birth_date index remains available for queries that can use the date values directly.</p><h3>What to Check Before Changing an Index</h3><p>A scan may be an appropriate choice for a small table. For a larger query, consider whether the existing indexes match the filters and joins, and whether the statistics reflect the data. In a query that joins several tables, the usefulness of an index also depends on the join strategy MySQL chooses.</p><p><a href="https://dev.mysql.com/doc/refman/8.0/en/index-hints.html">Index hints</a> can help you compare plans. Their effects differ: some restrict which indexes MySQL considers, while others can require a particular index when it applies. Evaluate the performance of the resulting plan before deciding whether to keep the hint.</p><h3>Conclusion</h3><p>When MySQL skips an index, start by asking whether the index supports the predicate. If it does, check whether the optimizer estimates that using it would cost more than scanning the table. The salary and birth date examples show why those two questions can lead to different answers.</p><p>For more technical walkthroughs, browse the <a href="https://www.mydbops.com/blog/">Mydbops blog</a>. For help reviewing MySQL execution plans or tuning slow queries, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact-us">contact the team</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e2563897316d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Install a Specific MySQL 8.0 Version Using Yum]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/how-to-install-a-specific-mysql-8-0-version-using-yum-781db1f5f65f?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/781db1f5f65f</guid>
            <category><![CDATA[yum]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Tue, 03 Nov 2026 10:55:32 GMT</pubDate>
            <atom:updated>2026-11-03T10:55:32.450Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="install-specific-version-of-mysql-8-using-yum" src="https://cdn-images-1.medium.com/max/1024/1*4Rf5owV_6KqOT5NcGtmbbQ.png" /></figure><p>When you’re reproducing a production issue, preparing a replica, or setting up an environment for disaster recovery or user acceptance testing, the MySQL patch version can matter. You may also need a specific release to match the compatibility requirements of a tool such as Percona XtraBackup.</p><p>Yum can resolve dependencies for you, which is useful when installing MySQL from RPM packages. But a plain install command normally selects the latest eligible package from the enabled repositories. To install a particular release, first select the right repository, then specify the server build you want.</p><h3><strong>Before You Start</strong></h3><p>The example uses MySQL 8.0.20 on CentOS 7, a historical setup. Use the example to understand how package selection works, and check which builds are available for your own distribution and architecture. These steps assume a fresh installation, access to the configured repositories, and sudo privileges.</p><h3><strong>Configure the MySQL 8.0 Repository</strong></h3><p>Download the repository setup RPM for your platform from the <a href="https://dev.mysql.com/downloads/repo/yum/">MySQL Yum Repository</a> page. Install the downloaded file, replacing the example filename with the actual one:</p><pre>sudo yum install ./mysql-repository-setup.rpm</pre><p>Check the configured MySQL repositories and their status:</p><pre>yum repolist all | grep mysql</pre><p>Current repository setup packages generally enable MySQL 8.4 by default. For this walkthrough, edit /etc/yum.repos.d/mysql-community.repo so that mysql80-community has enabled=1 and the other MySQL server series have enabled=0. Leave signature verification enabled. On EL8 systems where the distribution’s MySQL module masks Oracle’s packages, disable that module before listing or installing packages:</p><pre>sudo yum module disable mysql</pre><h3><strong>Find and Install the Required Build</strong></h3><p>Ask Yum to show all available builds of the server package:</p><pre>sudo yum --showduplicates list mysql-community-server</pre><p>Use the version, package release, and architecture from the listing. For example, if Yum lists the 8.0.20-1.el7 build for x86_64, install it with:</p><pre>sudo yum install mysql-community-server-8.0.20-1.el7.x86_64</pre><p>Specifying the full server package targets one build and lets Yum resolve its dependencies. If the build you need isn’t listed, this command cannot install it from the repositories you have configured.</p><p>If any available MySQL 8.0 patch release will do, omit the version. With only the MySQL 8.0 server repository enabled among the MySQL release series, Yum can select the latest eligible build it offers:</p><pre>sudo yum install mysql-community-server</pre><h3><strong>Start MySQL and Find the Temporary Password</strong></h3><p>Start the service:</p><pre>sudo systemctl start mysqld</pre><p>When MySQL initializes an empty data directory on first startup, it generates a temporary password for root@localhost. On the RPM setup used here, look for it in the MySQL error log:</p><pre>sudo grep &#39;temporary password&#39; /var/log/mysqld.log</pre><h3><strong>Sign In and Change the Root Password</strong></h3><p>Connect as root and enter the temporary password when prompted:</p><pre>mysql -u root -p</pre><p>Set a new password before running ordinary queries. A command such as SHOW SCHEMAS can return ERROR 1820 while the temporary password is still expired.</p><pre>ALTER USER &#39;root&#39;@&#39;localhost&#39;<br>  IDENTIFIED BY &#39;&lt;your-new-strong-password&gt;&#39;;</pre><p>Replace the placeholder with a password that meets the server’s password policy.</p><h3><strong>Conclusion</strong></h3><p>Selecting the MySQL 8.0 repository determines the release series. Specifying the full server package determines the exact build. Once MySQL starts and you replace the temporary root password, you can begin using the instance. For further reading, see how <a href="https://www.mydbops.com/blog/improving-query-performance-with-multi-valued-indexing-in-mysql-80">multi-valued indexes work in MySQL 8.0</a>.</p><p>For help with MySQL installation or configuration, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact-us">contact the team</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=781db1f5f65f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Managing PostgreSQL Logs: Configuration, Rotation, and Retention]]></title>
            <link>https://medium.com/@mydbopsdatabasemanagement/managing-postgresql-logs-configuration-rotation-and-retention-698b438291c9?source=rss-fd4010e3fc93------2</link>
            <guid isPermaLink="false">https://medium.com/p/698b438291c9</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[logging]]></category>
            <dc:creator><![CDATA[Mydbops]]></dc:creator>
            <pubDate>Thu, 29 Oct 2026 05:20:53 GMT</pubDate>
            <atom:updated>2026-10-29T05:20:53.330Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="mastering-postgresql-log-management" src="https://cdn-images-1.medium.com/max/1024/1*SnnNI1IY1jm2JET_8F6y9A.png" /></figure><p>PostgreSQL logs help explain what happened inside a database server. They provide information for investigating failed statements, connection problems, slow queries, and maintenance activity.</p><p>Their usefulness depends on configuration. Too little logging can leave an investigation without enough evidence. Excessive logging increases processing and storage requirements, making the important messages harder to find.</p><p>Effective <a href="https://www.mydbops.com/blog-tags/postgresql-log">PostgreSQL log management</a> therefore involves deciding what to record, where to send it, and how long to keep it. The parameter descriptions below follow PostgreSQL 16 behavior.</p><h3><strong>Understanding PostgreSQL Logs</strong></h3><p>Depending on the enabled settings, PostgreSQL logs can record:</p><ul><li>Connection attempts and session endings.</li><li>SQL statements and their durations.</li><li>Errors, warnings, and diagnostic messages.</li><li>Checkpoint and autovacuum activity.</li><li>Configuration and other server events.</li></ul><p>These records support several operational tasks.</p><h4><strong>Troubleshooting and Debugging</strong></h4><p>Error messages and their context help identify problems with queries, connections, and database operations.</p><p>PostgreSQL assigns severity levels to messages so administrators can distinguish diagnostic information from conditions that interrupt commands or sessions.</p><p>The main levels are:</p><ul><li><strong>DEBUG1 through DEBUG5:</strong> Diagnostic information with increasing levels of detail.</li><li><strong>INFO:</strong> Information implicitly requested by a user, such as verbose command output.</li><li><strong>NOTICE:</strong> Potentially useful information that does not indicate an error.</li><li><strong>WARNING:</strong> A likely problem that does not abort the current command.</li><li><strong>ERROR:</strong> An error that aborts the current command.</li><li><strong>LOG:</strong> Information of interest to administrators, including server maintenance activity.</li><li><strong>FATAL:</strong> An error that aborts the current session.</li><li><strong>PANIC:</strong> An error that causes all database sessions to abort.</li></ul><p>Slow statement logging is controlled by the relevant duration settings. A slow statement is not automatically classified as a WARNING.</p><h4><strong>Performance, Security, and System Health</strong></h4><p>Statement duration logs help identify slow queries and opportunities for optimization.</p><p>Connection and error records can support investigations into unauthorized access attempts and application behavior. Maintenance and configuration messages help administrators understand the server’s operational state.</p><p>The goal is to collect information that serves these needs while keeping the volume manageable.</p><h3><strong>Controlling Log Verbosity</strong></h3><p><a href="https://www.mydbops.com/blog-tags/logverbosity">Log verbosity</a> determines how much detail appears in the logs. PostgreSQL provides separate controls for message severity, error detail, and statement logging.</p><h4><strong>Selecting Message Levels</strong></h4><p>log_min_messages controls the minimum message level written to the server log.</p><p>Its values include:</p><pre>DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1,<br>INFO, NOTICE, WARNING, ERROR, LOG, FATAL, PANIC</pre><p>Choosing a more detailed threshold increases the number of messages recorded.</p><h4><strong>Choosing Error Detail</strong></h4><p>log_error_verbosity accepts three values:</p><ul><li>TERSE reduces the additional information accompanying a message.</li><li>DEFAULT provides the usual message detail.</li><li>VERBOSE adds information such as the SQLSTATE code and source code location.</li></ul><p>Increasing detail can help an investigation, but it also makes the output longer.</p><h4><strong>Selecting Statements to Log</strong></h4><p>log_statement controls statement logging:</p><ul><li>none disables this form of statement logging.</li><li>ddl selects data definition statements.</li><li>mod includes DDL and statements that modify data.</li><li>all selects the broadest statement logging option.</li></ul><p>Statement logging and duration logging serve related but different purposes. Configure them according to the information needed for analysis.</p><h3><strong>Key Logging Parameters</strong></h3><p>Several settings in postgresql.conf determine where logs go and which activities they capture.</p><h4><strong>Log Destinations and File Collection</strong></h4><p>log_destination selects the output destinations. Options include stderr, csvlog, and syslog. Windows also supports eventlog.</p><p>Multiple destinations can be separated by commas:</p><pre>log_destination = &#39;stderr,csvlog&#39;</pre><p>logging_collector captures messages sent to standard error and redirects them into PostgreSQL managed log files.</p><p>Logging to stderr does not itself require the collector. Without it, messages go wherever the server’s standard error has been directed. CSV logging requires the collector.</p><p>Changing logging_collector requires a server restart.</p><p>When collection is enabled, log_directory identifies the directory for log files, and log_filename defines their naming pattern.</p><h4><strong>Rotation Controls</strong></h4><p>log_rotation_age controls how long PostgreSQL uses an individual log file before opening another.</p><p>log_rotation_size controls when PostgreSQL opens another file because of the amount of log data written.</p><p>These settings control rotation. They do not establish an automatic deletion policy for older files.</p><h4><strong>Statement Duration and Session Activity</strong></h4><p>log_min_duration_statement records completed statements whose duration meets or exceeds the configured threshold.</p><p>Without an explicit unit, the value is interpreted in milliseconds. Setting it to 0 logs all statement durations; -1 disables this duration logging.</p><p>In PostgreSQL 16, log_connections records connection attempts and successful authentication and authorization. log_disconnections records session termination, including session duration.</p><p>log_lock_waits records waits that exceed deadlock_timeout while a session is trying to acquire a lock.</p><h4><strong>Truncation Behavior</strong></h4><p>log_truncate_on_rotation determines whether PostgreSQL overwrites an existing file with the same name during time based rotation.</p><p>It does not truncate files during server startup or size based rotation. When disabled, PostgreSQL appends to an existing filename instead.</p><p>This behavior matters when a filename pattern deliberately repeats.</p><h3><strong>Managing Rotation, Retention, and Truncation</strong></h3><p>More detailed logging increases disk usage. Without a suitable management policy, accumulated logs can consume available storage and disrupt database operations.</p><p>Rotation, retention, and truncation address different parts of this problem.</p><h4><strong>Log Rotation</strong></h4><p>Rotation moves logging to another file. PostgreSQL can trigger it according to file age or size.</p><p>The filename pattern determines which name the new file receives. Rotation alone does not archive, compress, or delete the previous file.</p><h4><strong>Log Retention</strong></h4><p>Retention determines how long completed log files remain available.</p><p>For a 30 day retention requirement, use a separate cleanup or archive policy that manages completed files according to that requirement.</p><p>Setting log_rotation_age to a long interval does not retain files for that period and then delete them. It changes how long PostgreSQL uses a file before rotating.</p><h4><strong>Log Truncation</strong></h4><p>Truncation can support a repeating filename scheme by overwriting a previously used file during time based rotation.</p><p>For example, %d represents the day of the month. A filename containing only that date component repeats by calendar day number, rather than enforcing an exact 30 day retention period.</p><p>Enable truncation only when overwriting those repeated filenames is the intended behavior. It is not a general cleanup mechanism for every old log file.</p><h3><strong>An Example for Daily Rotation</strong></h3><p>The following configuration enables collected text logs with timestamped filenames and daily time based rotation:</p><pre># Collect stderr messages into log files.<br>logging_collector = on<br>log_destination = &#39;stderr&#39;</pre><pre># Include the full date and time in each filename.<br>log_filename = &#39;postgresql-%Y-%m-%d_%H%M%S.log&#39;</pre><pre># Perform time based rotation daily.<br>log_rotation_age = &#39;1d&#39;</pre><pre># Append rather than truncate if a filename is reused.<br>log_truncate_on_rotation = off</pre><p>The configured log_rotation_size can trigger additional rotations before the daily interval.</p><p>This example does not implement 30 day retention. Older completed files remain until a separate retention process manages them.</p><p>If the collector is currently disabled, enabling it requires restarting PostgreSQL.</p><h3><strong>Analyzing Logs with pgBadger</strong></h3><p>Reading large log files manually can become difficult, particularly when investigating activity across many connections and queries.</p><p><a href="https://www.mydbops.com/blog-tags/pgbadger">pgBadger</a> analyzes PostgreSQL logs and produces reports containing summaries and graphs.</p><p>Its reports can cover:</p><ul><li>Connection activity.</li><li>Query distribution and frequently observed queries.</li><li>Query duration analysis.</li><li>Database and user statistics.</li><li>Error events.</li></ul><p>The available analysis depends on the information recorded in the input logs. Logging settings therefore influence what the resulting reports can show.</p><h3><strong>Conclusion</strong></h3><p>Useful PostgreSQL logging requires deliberate choices about message detail, statement capture, destinations, and storage management.</p><p>Rotation opens new files. Retention controls how long completed files remain available. Truncation overwrites reused filenames under specific conditions.</p><p>Keeping these responsibilities clear helps preserve useful diagnostic information while controlling disk usage. Tools such as pgBadger can then make the collected information easier to analyze.</p><p>For assistance with <a href="https://www.mydbops.com/open-source-database-management">database management</a> and <a href="https://www.mydbops.com/postgresql">PostgreSQL services</a>, visit <a href="https://www.mydbops.com/">Mydbops</a> or <a href="https://www.mydbops.com/contact-us">Contact Us</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=698b438291c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>