Personal blog of Yzmir Ramirez

Percona, a leading provider of open-source database software and services, announced the general availability of Percona Operator for PostgreSQL version 2. The solution is 100% open source and automates the deployment and management of PostgreSQL clusters on Kubernetes. Percona is also offering 24/7 support and managed services for paying customers.

As more and more organizations move their workloads to Kubernetes, managing PostgreSQL clusters in this environment can be challenging. Kubernetes provides many benefits, such as automation and scalability, but it also introduces new complexities when it comes to managing databases. IT teams must ensure high availability, scalability, and security, all while ensuring that their PostgreSQL clusters perform optimally.

Percona Operator for PostgreSQL version 2 simplifies the deployment and management of PostgreSQL clusters on Kubernetes. The solution automates tasks such as scaling, backup and restore, upgrades, and more, and supports Patroni-based PostgreSQL clusters. Additionally, Percona Operator for PostgreSQL version 2 includes expanded options for customizing backup and restore operations, improved monitoring and alerting capabilities, and support for PostgreSQL 15.

For organizations that need additional support and managed services, Percona is offering 24/7 support and managed services for paying customers. This includes access to a dedicated team of PostgreSQL experts who can help with everything from installation and configuration to ongoing management and support.

Percona’s commitment to open source ensures that the Percona Operator for PostgreSQL version 2 remains a flexible and customizable solution that can be tailored to meet the unique needs of any organization.

Below you will find a short FAQ about the new operator and a comparison to version 1.x.

What is better in version 2 compared to version 1?

Architecture

Operator SDK is now used to build and package the Operator. It simplifies the development and brings more contribution friendliness to the code, resulting in better potential for growing the community. Users now have full control over Custom Resource Definitions that Operator relies on, which simplifies the deployment and management of the operator.

In version 1.x, we relied on Deployment resources to run PostgreSQL clusters, whereas in 2.0 Statefulsets are used, which are the de-facto standard for running stateful workloads in Kubernetes. This change improves the stability of the clusters and removes a lot of complexity from the Operator.

Backups

One of the biggest challenges in version 1.x is backups and restores. There are two main problems that our users faced:

• Not possible to change the backup configuration for the existing cluster
• Restoration from backup to the newly deployed cluster required workarounds

In this version, both these issues are fixed. In addition to that:

• Run up to four pgBackrest repositories
• Bootstrap the cluster from the existing backup through Custom Resource
• Azure Blob Storage support

Operations

Deploying complex topologies in Kubernetes is not possible without affinity and anti-affinity rules. In version 1.x, there were various limitations and issues, whereas this version comes with substantial improvements that enable users to craft the topology of their choice.

Within the same cluster, users can deploy multiple instances. These instances are going to have the same data but can have different configurations and resources. This can be useful if you plan to migrate to new hardware or need to test the new topology.

Each PostgreSQL node can have sidecar containers now to provide integration with your existing tools or expand the capabilities of the cluster.

Will Percona still support v1?

Percona Operator for PostgreSQL version 1 moves to maintenance mode and will go End-of-Life after one year – June 2024. We are going to provide bug and security fixes but will not introduce new features and improvements.

Customers with a contract with Percona will still have operational support until Operator goes into EoL stage.

I’m running version 1 now; how can I upgrade to version 2?

We have prepared detailed guidelines for migrating from version 1 to version 2 with zero or minimal downtime. Please refer to our documentation.

The Percona Operator for PostgreSQL version 2 is available now, and we invite you to try it out for yourself. Whether you’re deploying PostgreSQL for the first time or looking for a more efficient way to manage your existing environment, Percona Operator for PostgreSQL has everything you need to get the job done. We look forward to seeing what you can do with it!

For more information, visit Percona Operator for PostgreSQL v2 documentation page. For commercial support, please visit our contact page.

 

Learn more about Percona Operator for PostgreSQL v2

The PostgreSQL Write-Ahead Log (WAL) is a recording location within the Postgres cluster, capturing all modifications made to the cluster’s data files before being written to the heap. During crash recovery, the WAL contains sufficient data for Postgres to restore its state to the point of the last committed transaction.

Use cases

There may arise circumstances where it becomes necessary to determine the numerical difference between the WAL files. For instance, when recovering from a significant delay or while configuring replication on a sizable database, the recovery process can be time-consuming as new WAL files are replayed. Initially, when setting up replication, the server may not permit login access. In such cases, calculating the disparity in the number of WAL files can provide an estimation of the recovery time, allowing for an assessment of the lag.

Another practical application for calculating the difference between WAL files is in the context of the archiver process. Determining the variance between WAL files makes it possible to estimate the number of remaining files that are yet to be archived.

To calculate the difference between two WAL files, let’s understand the WAL file name format.
The name format for PostgreSQL Write-Ahead Logs (WAL) files is TTTTTTTTXXXXXXXXYYYYYYYY, a 24-character hexadecimal representation of the LSN (Log Sequence Number) associated with the WAL record. The LSN is a unique identifier for each WAL record.

In format TTTTTTTTXXXXXXXXYYYYYYYY, ‘T’ is the timeline, ‘X’ is the high 32-bits from the LSN(Segment number), and ‘Y’ is the low 32-bits of the LSN.

For example, a WAL file name might look like this: “0000000100001234000000AB”.
Here’s a breakdown of the components in the example:

– “00000001”: This represents the timeline ID. It is usually 1 for the default timeline.
– “00012340”: This represents the WAL file number, indicating the sequential order of the WAL file within the timeline.
– “000000AB”: This is the hexadecimal representation of the segment file number.

We can determine the numerical difference between two WAL files with the below sql:

SELECT ABS(('x' || SUBSTRING(current_wal, 1, 8))::bit(32)::int - ('x' || SUBSTRING(old_wal, 1, 8))::bit(32)::int) +
       ABS(('x' || SUBSTRING(current_wal, 9, 8))::bit(32)::int*256 - ('x' || SUBSTRING(old_wal, 9, 8))::bit(32)::int*256) +
       ABS(('x' || SUBSTRING(current_wal, 17))::bit(32)::int - ('x' || SUBSTRING(old_wal, 17))::bit(32)::int)
FROM (select '000000330000006900000047' current_wal, '0000003200000069000000AB' old_wal) as wal_segs;

Let’s create a PostgreSQL function that facilitates the calculation of the numerical difference between two WAL files, making it more convenient to use to determine the variance between them.

CREATE OR REPLACE FUNCTION public.get_walfile_diff(current_wal text, old_wal text)
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
v_wal_diff numeric;
BEGIN
IF length(current_wal) != 24 OR length(old_wal) != 24 THEN
RAISE EXCEPTION 'Invalid wal file length';
END IF;
v_wal_diff := (
ABS(('x' || SUBSTRING(current_wal, 1, 8))::bit(32)::int - ('x' || SUBSTRING(old_wal, 1, 8))::bit(32)::int) +
ABS(('x' || SUBSTRING(current_wal, 9, 8))::bit(32)::int*256 - ('x' || SUBSTRING(old_wal, 9, 8))::bit(32)::int*256) +
ABS(('x' || SUBSTRING(current_wal, 17))::bit(32)::int - ('x' || SUBSTRING(old_wal, 17))::bit(32)::int)
);
RETURN v_wal_diff;
END;
$function$;

Examples

postgres=# SELECT public.get_walfile_diff('000000330000006900000047','000000330000006900000048');
 get_walfile_diff
------------------
                1
(1 row)
postgres=# SELECT public.get_walfile_diff('000000330000006900000047','0000003300000069000000AB');
 get_walfile_diff
------------------
              100
(1 row)

Overall, being able to calculate the numerical difference between WAL files contributes to effective management and understanding of Postgres database recovery/archiver processes.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.

 

Download Percona Distribution for PostgreSQL Today!

Before Percona Server for MongoDB 4.4 (PSMDB), the best practice to create an index was doing it in a rolling manner. Many folks used to create directly on Primary, resulting in the first index being created successfully on Primary and then replicated to Secondary nodes.

Starting from PSMDB 4.4, there was a new parameter commitQuorum introduced in the createIndex command. If you are not passing this parameter explicitly with the createIndex command, it will use the default settings on a replica set or sharded cluster and start building the index simultaneously across all data-bearing voting replica set members.

Below is the command used to create an index using commitQuorum as the majority:

db.getSiblingDB("acme").products.createIndex({ "airt" : 1 }, { }, "majority")

The above command will run the index create command on the majority of data-bearing replica set members. There are other options available too when using commitQuorum:

1. “Voting Members” – This is the default behavior when an index will be created on all data-bearing voting replica set members (Default). A “voting” member is any replica set member where votes are greater than 0.
2. “Majority” – A simple majority of data-bearing replica set members.
3. “<int>” – A specific number of data-bearing replica set members. Specify an integer greater than 0.
4. “Tag name” – A replica set tag name of a node is used.
   

Now we will see the scenarios of what happens when the index is created with the default and majority commitQuorum.

1. When all data-bearing replica set members are available, and the index is created with default commitQuorum, below are the details from the Primary and the Secondary nodes. Create index:

   rs1:PRIMARY> db.products.createIndex({ "airt" : 1 })

   Primary logs:

   {"t":{"$date":"2023-06-26T12:33:18.417+00:00"},"s":"I",  "c":"INDEX",    "id":20384,   "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: starting","attr":{"namespace":"acme.products","buildUUID":{"uuid":{"$uuid":"58f4e7bf-7b8f-4eb6-8de0-0ad774c4b51f"}},"properties":{"v":2,"key":{"airt":1.0},"name":"airt_1"},"method":"Hybrid","maxTemporaryMemoryUsageMB":200}}

   Secondary logs:

   {"t":{"$date":"2023-06-26T12:33:18.417+00:00"},"s":"I",  "c":"INDEX",    "id":20384,   "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: starting","attr":{"namespace":"acme.products","buildUUID":{"uuid":{"$uuid":"58f4e7bf-7b8f-4eb6-8de0-0ad774c4b51f"}},"properties":{"v":2,"key":{"airt":1.0},"name":"airt_1"},"method":"Hybrid","maxTemporaryMemoryUsageMB":200}}}

   Secondary logs:

   {"t":{"$date":"2023-06-26T12:33:28.445+00:00"},"s":"I",  "c":"INDEX",    "id":20384,   "ctx":"IndexBuildsCoordinatorMongod-0","msg":"Index build: starting","attr":{"namespace":"acme.products","buildUUID":{"uuid":{"$uuid":"58f4e7bf-7b8f-4eb6-8de0-0ad774c4b51f"}},"properties":{"v":2,"key":{"airt":1.0},"name":"airt_1"},"method":"Hybrid","maxTemporaryMemoryUsageMB":200}}

   We can see the above index was created simultaneously on all the data-bearing voting replica set members.

2. When one secondary is down, and the index is created with default commitQuorum, below are the details from the Primary and the Secondary nodes.
   
   Status of nodes:
   

   rs1:PRIMARY> rs.status().members.forEach(function (d) {print(d.name) + " " + print(d.stateStr)});
   127.0.0.1:27017
   PRIMARY
   localhost:27018
   SECONDARY
   localhost:27019
   (not reachable/healthy)
   rs1:PRIMARY>

   Index command:
   

   rs1:PRIMARY> db.products.createIndex({ "airt" : 1 })

   Replication status:
   

   rs1:PRIMARY> db.printSecondaryReplicationInfo()
   source: localhost:27018
           syncedTo: Mon Jun 26 2023 17:56:30 GMT+0000 (UTC)
           0 secs (0 hrs) behind the primary
   source: localhost:27019
           syncedTo: Thu Jan 01 1970 00:00:00 GMT+0000 (UTC)
           1687802190 secs (468833.94 hrs) behind the primary
   rs1:PRIMARY>

   Index status:
   

   rs1:PRIMARY> db.currentOp(true).inprog.forEach(function(op){ if(op.msg!==undefined) print(op.msg) })
   Index Build: draining writes received during build
   rs1:PRIMARY> Date()
   Mon Jun 26 2023 18:07:26 GMT+0000 (UTC)
   rs1:PRIMARY>

   CurrentOp:
   

   "active" : true,
         "currentOpTime" :"2023-06-26T19:04:33.175+00:00",
         "opid" : 329147,
     "lsid" : {
              "id" :UUID("dd9672f8-4f56-47ce-8ceb-31caf5e8baf8"),
              "uid": BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
               },
    "secs_running" : NumberLong(4214),
    "microsecs_running" : NumberLong("4214151233"),
    "op" : "command",
    "ns" : "acme.products",
    "command" : {
                 "createIndexes" : "products",
                 "indexes" : [
                                 {
                                  "key" : {
                                           "airt" : 1
                                           },
                                  "name" : "airt_1"
                                 }
                              ],
                  "lsid" : {
                  "id" :UUID("dd9672f8-4f56-47ce-8ceb-31caf5e8baf8")
                            },
                  "$clusterTime" : {
                                    "clusterTime" : Timestamp(1687801980, 1),
                                    "signature" : {
                                    "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                                    "keyId" : NumberLong(0)
                                           }
                                   },
                   "$db" : "acme"
                           }

   Logs from Primary node:
   

   {"t":{"$date":"2023-06-26T17:54:21.419+00:00"},"s":"I",  "c":"STORAGE",  "id":3856203, "ctx":"IndexBuildsCoordinatorMongod-1","msg":"Index build: waiting for next action before completing final phase","attr":{"buildUUID":{"uuid":{"$uuid":"46451b37-141f-4312-a219-4b504736ab5b"}}}}

    Logs from up-and-running Secondary node:
   

   {"t":{"$date":"2023-06-26T17:54:21.424+00:00"},"s":"I",  "c":"STORAGE",  "id":3856203, "ctx":"IndexBuildsCoordinatorMongod-1","msg":"Index build: waiting for next action before completing final phase","attr":{"buildUUID":{"uuid":{"$uuid":"46451b37-141f-4312-a219-4b504736ab5b"}}}}

   You can see above that when one node is down, and the index is created with default commitQuorum, the index command will keep running till that third data-bearing voting node comes up. Now we can check if the index is created on Primary or not:
   

   rs1:PRIMARY> db.products.getIndexes()
   [
           {
                   "v" : 2,
                   "key" : {
                           "_id" : 1
                   },
                   "name" : "_id_"
           },
           {
                   "v" : 2,
                   "key" : {
                           "airt" : 1
                   },
                   "name" : "airt_1"
           }
   ]
   rs1:PRIMARY>

   We can see the index is created, but you will not be able to use the above index as the index is not marked as completed.

   Below is the explain plan of a query, where we can see the query is doing COLLSCAN instead of IXSCAN:

   rs1:PRIMARY> db.products.find({"airt" : 1.9869362536440427}).explain()
   {
           "queryPlanner" : {
                   "plannerVersion" : 1,
                   "namespace" : "acme.products",
                   "indexFilterSet" : false,
                   "parsedQuery" : {
                           "airt" : {
                                   "$eq" : 1.9869362536440427
                           }
                   },
                   "queryHash" : "65E2F79D",
                   "planCacheKey" : "AA490985",
                   "winningPlan" : {
                           "stage" : "COLLSCAN",
                           "filter" : {
                                   "airt" : {
                                           "$eq" : 1.9869362536440427
                                   }
                           },
                           "direction" : "forward"
                   },
                   "rejectedPlans" : [ ]
           },
           "serverInfo" : {
                   "host" : "ip-172-31-82-235.ec2.internal",
                   "port" : 27017,
                   "version" : "4.4.22-21",
                   "gitVersion" : "be7a5f4a1000bed8cf1d1feb80a20664d51503ce"
   }

   Now I will bring up the third node, and we will see that index op will complete.
   

   Index status:
   

   rs1:PRIMARY> db.products.createIndex({ "airt" : 1 })
   {
           "createdCollectionAutomatically" : false,
           "numIndexesBefore" : 1,
           "numIndexesAfter" : 2,
           "commitQuorum" : "votingMembers",
           "ok" : 1,
           "$clusterTime" : {
                   "clusterTime" : Timestamp(1687806737, 3),
                   "signature" : {
                           "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                           "keyId" : NumberLong(0)
                   }
           },
           "operationTime" : Timestamp(1687806737, 3)
   }
   rs1:PRIMARY>

   Now will run the same query, and we can see index (IXSCAN) is getting used as the index was created successfully above:
   

   rs1:PRIMARY> db.products.find({"airt" : 1.9869362536440427}).explain()
   {
           "queryPlanner" : {
                   "plannerVersion" : 1,
                   "namespace" : "acme.products",
                   "indexFilterSet" : false,
                   "parsedQuery" : {
                           "airt" : {
                                   "$eq" : 1.9869362536440427
                           }
                   },
                   "queryHash" : "65E2F79D",
                   "planCacheKey" : "AA490985",
                   "winningPlan" : {
                           "stage" : "FETCH",
                           "inputStage" : {
                                   "stage" : "IXSCAN",
                                   "keyPattern" : {
                                           "airt" : 1
                                   },
                                   "indexName" : "airt_1",
                                   "isMultiKey" : false,
                                   "multiKeyPaths" : {
                                           "airt" : [ ]
                                   },
                                   "isUnique" : false,
                                   "isSparse" : false,
                                   "isPartial" : false,
                                   "indexVersion" : 2,
                                   "direction" : "forward",
                                   "indexBounds" : {
                                           "airt" : [
                                                   "[1.986936253644043, 1.986936253644043]"
                                           ]
                                   }
                           }
                   },
                   "rejectedPlans" : [ ]
           },
           "serverInfo" : {
                   "host" : "ip-172-31-82-235.ec2.internal",
                   "port" : 27017,
                   "version" : "4.4.22-21",
                   "gitVersion" : "be7a5f4a1000bed8cf1d1feb80a20664d51503ce"
           }

   Primary logs once the third node came up and the index was created successfully:
   

   {"t":{"$date":"2023-06-26T19:12:17.450+00:00"},"s":"I",  "c":"STORAGE",  "id":3856201, "ctx":"conn40","msg":"Index build: commit quorum satisfied","attr":{"indexBuildEntry":{"_id":{"$uuid":"46451b37-141f-4312-a219-4b504736ab5b"},"collectionUUID":{"$uuid":"a963b7e7-1054-4a5f-a935-a5be8995cff0"},"commitQuorum":"votingMembers","indexNames":["airt_1"],"commitReadyMembers":["127.0.0.1:27017","localhost:27018","localhost:27019"]}}}
   
   {"t":{"$date":"2023-06-26T19:12:17.450+00:00"},"s":"I",  "c":"STORAGE",  "id":3856204, "ctx":"IndexBuildsCoordinatorMongod-1","msg":"Index build: received signal","attr":{"buildUUID":{"uuid":{"$uuid":"46451b37-141f-4312-a219-4b504736ab5b"}},"action":"Commit quorum Satisfied"}}
   
   {"t":{"$date":"2023-06-26T19:12:17.451+00:00"},"s":"I",  "c":"INDEX",    "id":20345,   "ctx":"IndexBuildsCoordinatorMongod-1","msg":"Index build: done building","attr":{"buildUUID":{"uuid":{"$uuid":"46451b37-141f-4312-a219-4b504736ab5b"}},"namespace":"acme.products","index":"airt_1","commitTimestamp":{"$timestamp":{"t":1687806737,"i":2}}}}
   
   {"t":{"$date":"2023-06-26T19:12:17.452+00:00"},"s":"I",  "c":"STORAGE",  "id":20663,   "ctx":"IndexBuildsCoordinatorMongod-1","msg":"Index build: completed successfully","attr":{"buildUUID":{"uuid":{"$uuid":"46451b37-141f-4312-a219-4b504736ab5b"}},"namespace":"acme.products","uuid":{"uuid":{"$uuid":"a963b7e7-1054-4a5f-a935-a5be8995cff0"}},"indexesBuilt":1,"numIndexesBefore":1,"numIndexesAfter":2}}
   
   {"t":{"$date":"2023-06-26T19:12:17.554+00:00"},"s":"I",  "c":"INDEX",    "id":20447,   "ctx":"conn34","msg":"Index build: completed","attr":{"buildUUID":{"uuid":{"$uuid":"46451b37-141f-4312-a219-4b504736ab5b"}}}}
   
   {"t":{"$date":"2023-06-26T19:12:17.554+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn34","msg":"Slow query","attr":{"type":"command","ns":"acme.products","appName":"MongoDB Shell","command":{"createIndexes":"products","indexes":[{"key":{"airt":1.0},"name":"airt_1"}],"lsid":{"id":{"$uuid":"dd9672f8-4f56-47ce-8ceb-31caf5e8baf8"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1687801980,"i":1}},"signature":{"hash":{"$binary":{"base64":"AAAAAAAAAAAAAAAAAAAAAAAAAAA=","subType":"0"}},"keyId":0}},"$db":"acme"},"numYields":0,"reslen":271,"locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":3}},"FeatureCompatibilityVersion":{"acquireCount":{"r":1,"w":4}},"ReplicationStateTransition":{"acquireCount":{"w":5}},"Global":{"acquireCount":{"r":1,"w":4}},"Database":{"acquireCount":{"w":3}},"Collection":{"acquireCount":{"r":1,"w":1,"W":1}},"Mutex":{"acquireCount":{"r":3}}},"flowControl":{"acquireCount":3,"timeAcquiringMicros":7},"storage":{"data":{"bytesRead":98257,"timeReadingMicros":3489}},"protocol":"op_msg","durationMillis":4678530}}

   Above, you can see how much time it took to complete the index build; the op was running till the third node was down.

3. When one secondary is down, and the index is created with commitQuorum as the majority, below are the details from the Primary and the Secondary nodes. Status of nodes:

   rs1:PRIMARY> rs.status().members.forEach(function (d) {print(d.name) + " " + print(d.stateStr)});
   127.0.0.1:27017
   PRIMARY
   localhost:27018
   SECONDARY
   localhost:27019
   (not reachable/healthy)
   rs1:PRIMARY>

   Index command:
   

   rs1:PRIMARY> db.products.createIndex({ "airt" : 1 }, { }, "majority")
   {
           "createdCollectionAutomatically" : false,
           "numIndexesBefore" : 1,
           "numIndexesAfter" : 2,
           "commitQuorum" : "majority",
           "ok" : 1,
           "$clusterTime" : {
                   "clusterTime" : Timestamp(1687808148, 4),
                   "signature" : {
                           "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                           "keyId" : NumberLong(0)
                   }
           },
           "operationTime" : Timestamp(1687808148, 4)
   }
   rs1:PRIMARY>

   Logs from Primary node:
   

   {"t":{"$date":"2023-06-26T19:35:48.821+00:00"},"s":"I",  "c":"STORAGE",  "id":3856201, "ctx":"conn7","msg":"Index build: commit quorum satisfied","attr":{"indexBuildEntry":{"_id":{"$uuid":"5f8f75ee-aa46-42a6-b4c2-59a68fea47a7"},"collectionUUID":{"$uuid":"a963b7e7-1054-4a5f-a935-a5be8995cff0"},"commitQuorum":"majority","indexNames":["airt_1"],"commitReadyMembers":["127.0.0.1:27017","localhost:27018"]}}}
   
   {"t":{"$date":"2023-06-26T19:35:48.821+00:00"},"s":"I",  "c":"STORAGE",  "id":3856204, "ctx":"IndexBuildsCoordinatorMongod-3","msg":"Index build: received signal","attr":{"buildUUID":{"uuid":{"$uuid":"5f8f75ee-aa46-42a6-b4c2-59a68fea47a7"}},"action":"Commit quorum Satisfied"}}
   
   {"t":{"$date":"2023-06-26T19:35:48.822+00:00"},"s":"I",  "c":"INDEX",    "id":20345,   "ctx":"IndexBuildsCoordinatorMongod-3","msg":"Index build: done building","attr":{"buildUUID":{"uuid":{"$uuid":"5f8f75ee-aa46-42a6-b4c2-59a68fea47a7"}},"namespace":"acme.products","index":"airt_1","commitTimestamp":{"$timestamp":{"t":1687808148,"i":3}}}}
   
   {"t":{"$date":"2023-06-26T19:35:48.824+00:00"},"s":"I",  "c":"STORAGE",  "id":20663,   "ctx":"IndexBuildsCoordinatorMongod-3","msg":"Index build: completed successfully","attr":{"buildUUID":{"uuid":{"$uuid":"5f8f75ee-aa46-42a6-b4c2-59a68fea47a7"}},"namespace":"acme.products","uuid":{"uuid":{"$uuid":"a963b7e7-1054-4a5f-a935-a5be8995cff0"}},"indexesBuilt":1,"numIndexesBefore":1,"numIndexesAfter":2}}
   
   {"t":{"$date":"2023-06-26T19:35:48.923+00:00"},"s":"I",  "c":"INDEX",    "id":20447,   "ctx":"conn34","msg":"Index build: completed","attr":{"buildUUID":{"uuid":{"$uuid":"5f8f75ee-aa46-42a6-b4c2-59a68fea47a7"}}}}
   
   {"t":{"$date":"2023-06-26T19:35:48.923+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn34","msg":"Slow query","attr":{"type":"command","ns":"acme.products","appName":"MongoDB Shell","command":{"createIndexes":"products","indexes":[{"key":{"airt":1.0},"name":"airt_1"}],"commitQuorum":"majority","lsid":{"id":{"$uuid":"dd9672f8-4f56-47ce-8ceb-31caf5e8baf8"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1687808123,"i":1}},"signature":{"hash":{"$binary":{"base64":"AAAAAAAAAAAAAAAAAAAAAAAAAAA=","subType":"0"}},"keyId":0}},"$db":"acme"},"numYields":0,"reslen":266,"locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":3}},"FeatureCompatibilityVersion":{"acquireCount":{"r":1,"w":4}},"ReplicationStateTransition":{"acquireCount":{"w":5}},"Global":{"acquireCount":{"r":1,"w":4}},"Database":{"acquireCount":{"w":3}},"Collection":{"acquireCount":{"r":1,"w":1,"W":1}},"Mutex":{"acquireCount":{"r":3}}},"flowControl":{"acquireCount":3,"timeAcquiringMicros":7},"storage":{},"protocol":"op_msg","durationMillis":2469}}

   Above, we can see when one node is down, and we used commitQuorum as majority while creating the index, index op got completed as per expected behavior as two voting (majority) nodes were up and running.

So far, we have discussed how to use commitQuorum and when to use it. Now we will see a scenario when one node (voting) is down for any reason, and someone created an index with default commitQuorum. The op will keep running, and you want to kill the op.

I created the index with the default commitQuorum when one node is down.

Status of nodes:

rs1:PRIMARY> rs.status().members.forEach(function (d) {print(d.name) + " " + print(d.stateStr)});
127.0.0.1:27017
PRIMARY
localhost:27018
SECONDARY
localhost:27019
(not reachable/healthy)
rs1:PRIMARY>

CurrentOp:

"active" : true,
"currentOpTime" : "2023-06-26T21:27:41.304+00:00",
"opid" : 536535,
"lsid" : {
          "id" : UUID("dd9672f8-4f56-47ce-8ceb-31caf5e8baf8"),
          "uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
 },
 "secs_running" : NumberLong(264),
 "microsecs_running" : NumberLong(264345444),
 "op" : "command",
 "ns" : "acme.products",
 "command" : {
              "createIndexes" : "products",
              "indexes" : [
                      {
                           "key" : {
                                     "airt" : 1
                                    },
                           "name" : "airt_1"
                       }
                            ],
 "lsid" : {
               "id" : UUID("dd9672f8-4f56-47ce-8ceb-31caf5e8baf8")
           },
               "$clusterTime" : {
                                "clusterTime" : Timestamp(1687814589, 2),
                                "signature" : {
                                               "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                                 "keyId" : NumberLong(0)
                                 }
                                },
                                "$db" : "acme"
                        }

Now you need to kill the above opid to release the above op:

rs1:PRIMARY> db.killOp(536535)
{
        "info" : "attempting to kill op",
        "ok" : 1,
        "$clusterTime" : {
                "clusterTime" : Timestamp(1687815189, 2),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        },
        "operationTime" : Timestamp(1687815189, 2)
}
rs1:PRIMARY>
rs1:PRIMARY> db.products.createIndex({ "airt" : 1 })
{
        "operationTime" : Timestamp(1687815192, 2),
        "ok" : 0,
        "errmsg" : "operation was interrupted",
        "code" : 11601,
        "codeName" : "Interrupted",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1687815192, 2),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        }
}
rs1:PRIMARY>
rs1:PRIMARY> db.products.getIndexes()
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" } ]
rs1:PRIMARY>

Above, we can see when we killed the op, and the index creation op got killed.

Conclusion

We have seen how commitQuorum works while creating indexes from PSMDB 4.4. Still, the best practice is to create indexes in a rolling manner.

We recommend checking out our products for Percona Server for MongoDB, Percona Backup for MongoDB, and Percona Operator for MongoDB. We also recommend checking out our blog MongoDB: Why Pay for Enterprise When Open Source Has You Covered?

In the Postgres database, the application data can be organized in various ways using Postgres schemas. In the Postgres database cluster, whenever we create a new database, It gets created with the default schema called public schema. This blog post will discuss the Public Schema Security upgrade in PostgreSQL 15.

postgres=# create database d1;
CREATE DATABASE
postgres=#
postgres=# c d1
You are now connected to database "d1" as user "postgres".
d1=#
d1=# dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
 (1 row)

When we create a table without specifying the schema name, it gets created in the schema, which is set as per the search_path. By default, The first part of search_path defines the schema with the same name as the current user, and the second part of search_path refers to the public schema. 

postgres=# show search_path;
  search_path
-----------------
"$user", public
(1 row)

Postgres looks for the schemas sequentially according to the list mentioned in the search_path, so when we execute create table command, Postgres creates the table in the first schema mentioned in the search_path. If it is not present, it creates it in the following schema.

Similarly, if the schema name is not specified in the select query, Postgres will search for tables within the named schema according to the search_path.

Public Schema security upgrade in PostgreSQL 15

Up to Postgres 14, whenever you create a database user, by default, it gets created with CREATE and USAGE privileges on the public schema.

It means that until Postgres 14, any user can write to the public schema until you manually revoke the user’s create privilege on the public schema. 

Starting with PostgreSQL 15, the CREATE privilege on public schema is revoked/removed from all users except the database owner. 

In Postgres 15, now new users cannot create tables or write data to Postgres public schema by default. You have to grant create privilege to the new user manually. 

The usage privilege on the public schema for the new users is still present in Postgres 15, like in Postgres 14 and previous versions.

The example below shows that a new user (test1) can create a table in Postgres 14 without granting any privileges.

postgres=# conninfo
You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432".
postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=#
postgres=#
postgres=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)
postgres=#
postgres=# create table department (empID int);
CREATE TABLE
postgres=# insert into  department  values (10);
INSERT 0 1
postgres=#

The example below shows that Postgres 15 only allows new users (test1) to create tables by granting them create privileges on the public schema.

postgres=# conninfo
You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432".
postgres=# select version();
                                                version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# show search_path;
  search_path
-----------------
"$user", public
(1 row)
postgres=# create table department (empID int);
ERROR:  permission denied for schema public
LINE 1: create table department (empID int);

postgres=# conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=#
postgres=# grant create on schema public to test1;
GRANT
postgres=#

postgres=# conninfo
You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432".
postgres=# create table department (empID int);
CREATE TABLE
postgres=#

The following example shows that the usage privilege on the public schema for the new users is still present in Postgres 15, like in Postgres 14 and previous versions.

postgres=# conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=#
postgres=# create table transport ( vehicleno int);
CREATE TABLE
postgres=# insert into transport values (25);
INSERT 0 1
postgres=#
postgres=# grant select on transport to test1;
GRANT
postgres=# q
-bash-4.2$ psql -d postgres -U test1
Password for user test1:
psql (15.3)
Type "help" for help.
postgres=# select * from transport;
vehicleno
-----------
       25
(1 row)
postgres=#

Public schema ownership changes in PostgreSQL 15

In Postgres 14 and previous versions, by default, the public schema is owned by the bootstrap superuser (postgres), but from Postgres 15, ownership of the public schema has been changed to the new pg_database_owner role. It enables every database owner to own the database’s public schema. 

The below example shows the ownership changes between Postgres 14 and Postgres 15.

Postgres 14

postgres=# dn
 List of schemas
 Name  |  Owner
--------+----------
public | postgres
(1 row)

Postgres 15

postgres=# dn
    List of schemas
Name  |       Owner
--------+-------------------
public | pg_database_owner
(1 row)
postgres=#

Visit the links below for further details about the Postgres schemas.

https://www.postgresql.org/docs/15/ddl-schemas.html

https://www.postgresql.org/docs/release/15.0/

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.

 

Download Percona Distribution for PostgreSQL Today!

Recently, I was working on a very unfortunate case that revolved around diverging clusters, data loss, missing important log errors, and forcing commands on Percona XtraDB Cluster (PXC). Even though PXC tries its best to explain what happens in the error log, I can vouch that it can be missed or overlooked when you do not know what to expect.

This blog post is a warning tale, an invitation to try yourself and break stuff (not in production, right?).

TLDR:
Do you know right away what happened when seeing this log?

2023-06-22T08:23:29.003334Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_group.cpp:group_post_state_exchange():433: Reversing history: 171 -> 44, this member has applied 127 more events than the primary component.Data loss is possible. Must abort.

Demonstration

Using the great https://github.com/datacharmer/dbdeployer:

$ dbdeployer deploy replication --topology=pxc --sandbox-binary=~/opt/pxc  8.0.31

Let’s write some data

$ ./sandboxes/pxc_msb_8_0_31/sysbench oltp_read_write --tables=2 --table-size=1000 prepare

Then let’s suppose someone wants to restart node 1. For some reason, they read somewhere in your internal documentation that they should bootstrap in that situation. With dbdeployer, this will translate to:

$ ./sandboxes/pxc_msb_8_0_31/node1/stop
stop /home/yoann-lc/sandboxes/pxc_msb_8_0_31/node1

$ ./sandboxes/pxc_msb_8_0_31/node1/start --wsrep-new-cluster
......................................................................................................^C

It fails, as it should.

In reality, those bootstrap mistakes happen in homemade start scripts, puppet or ansible modules, or even internal procedures applied in the wrong situation.

Why did it fail? First error to notice:

2023-06-22T08:00:48.322148Z 0 [ERROR] [MY-000000] [Galera] It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .

Reminder: Bootstrap should only be used when every node has been double-checked to be down; it’s a manual operation. It fails here because it was not forced and because this node was not the last to be stopped in the cluster.

Good reflex: Connecting to other mysql and check for ‘wsrep_cluster_size’ and ‘wsrep_cluster_status’ statuses before anything.

mysql> show global status where variable_name IN ('wsrep_local_state','wsrep_local_state_comment','wsrep_local_commits','wsrep_received','wsrep_cluster_size','wsrep_cluster_status','wsrep_connected');

Do not: Apply blindly what this log is telling you to do.

But we are here to “fix” around and find out, so let’s bootstrap.

$ sed -i 's/safe_to_bootstrap: 0/safe_to_bootstrap: 1/' ./sandboxes/pxc_msb_8_0_31/node1/data/grastate.dat
$ ./sandboxes/pxc_msb_8_0_31/node1/start --wsrep-new-cluster
.. sandbox server started

At this point, notice that from node1, you have:

$ ./sandboxes/pxc_msb_8_0_31/node1/use -e "show global status where variable_name in ('wsrep_cluster_status', 'wsrep_cluster_size')"
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_size   | 1       |
| wsrep_cluster_status | Primary |
+----------------------+---------+

But from node2 and node3 you will have:

$ ./sandboxes/pxc_msb_8_0_31/node2/use -e "show global status where variable_name in ('wsrep_cluster_status', 'wsrep_cluster_size')"
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_size   | 2       |
| wsrep_cluster_status | Primary |
+----------------------+---------+

Looks fishy. But does your monitoring really alert you to this?

Let’s write some more data, obviously on node1, because why not? It looks healthy.

$ ./sandboxes/pxc_msb_8_0_31/node1/sysbench oltp_delete --tables=2 --table-size=1000 --events=127  run

127 will be useful later on.

Nightmare ensues

We are a few days later. You are still writing to your node. Some new reason to restart node1 comes. Maybe you want to apply a parameter.

$ ./sandboxes/pxc_msb_8_0_31/node1/restart
.............................................................................................................................................................^C

It fails?

Reviewing logs, you would find:

$ less sandboxes/pxc_msb_8_0_31/node1/data/msandbox.err
...
2023-06-22T08:23:29.003334Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_group.cpp:group_post_state_exchange():433: Reversing history: 171 -> 44, this member has applied 127 more events than the primary component.Data loss is possible. Must abort.
...

Voila, We find our “127” again.

Good reflex: Depends. It would need a post of its own, but that’s a serious problem.

Do not: Force SST on this node. Because it will work, and every data inserted on node1 will be lost.

What does it mean?

When forcing bootstrap, a node will always start. It won’t ever try to connect to other nodes if they are healthy. The other nodes won’t try to connect to the third one either; from their point of view, it just never joined, so it’s not part of the cluster.

When restarting the previously bootstrapped node1 in non-bootstrapped mode, that’s the first time they all see each other in a while.

Each time a transaction is committed, it is replicated along with a sequence number (seqno). The seqno is an ever-growing number. It is used by nodes to determine if incremental state transfer is possible, or if a node state is coherent with others.

Now that node1 is no longer in bootstrap mode, node1 connects to the other members. node1 shares its state (last primary members, seqno). The other nodes correctly picked up that this seqno looks suspicious because it’s higher than their own, meaning the node joining could have applied more transactions. It could also mean it was from some other cluster.

Because nodes are in doubt, nothing will happen. Node1 is denied joining and will not do anything. It won’t try to resynchronize automatically, and it won’t touch its data. Node2 and node3 are not impacted; they will be kept as is too.

How to proceed from there will depend as there are no general guidelines. Ideally, a source of truth should be found. If both clusters applied writes, that’s the toughest situation to be in, and it’s a split brain.

Note: seqno are just numbers. Having equal seqno does not actually guarantee that the underlying transactions applied are identical, but it’s still useful as a simple sanity check. If we were to mess around even more and apply 127 transactions on node2, or even modify seqno manually in grastate.dat, we could have “interesting” results. Try it out (not in production, mind you)!

Note: If you are unaware of bootstrapping and how to properly recover, check out the documentation.

Conclusion

Bootstrap is a last resort procedure, don’t force it lightly. Do not force SST right away if a node does not want to join either. You should always check the error log first.

Fortunately, PXC does not blindly let any node join without some sanity checks.

Minimize unexpected downtime and data loss with a highly available, open source MySQL clustering solution.

Download Percona XtraDB Cluster today

MySQL Shell is an advanced client and code editor for MySQL. In addition to the provided SQL functionality, similar to MySQL, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. The X DevAPI enables you to work with both relational and document data, and MySQL Shell 8.0 is highly recommended for use with MySQL Server 8.0 and 5.7.

MySQL Shell includes utilities for working with MySQL. To access the utilities from within MySQL Shell, use the util global object, which is available in JavaScript and Python modes, but not SQL mode. These are the utilities to take a backup; let’s see some basic commands.

• util.dumpTables – Dump one or more tables from single database
• util.dumpSchemas – Dump one or more databases
• util.dumpInstance – Dump full instance
• util.loadDump – Restore dump

1. Single table dump

The below command is to take a dump of the table sbtest1 from the sysbench database and store the backup on the destination directory sysbench_dumps. The utility will create the directory when the destination directory does not exist. By default, compression, and chunking are enabled. When chunking is enabled, the table dump will be spitted onto multiple files based on size. Dialect:”csv gives the extension of the dump file, and by default, the file will be created with the tsv (Table separated value) extension.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dialect:"csv"})
Acquiring global read lock
Global read lock acquired
Initializing - done
1 tables and 0 views will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Running data dump using 6 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
101% (1000.00K rows / ~986.40K rows), 317.96K rows/s, 63.91 MB/s
Dump duration: 00:00:03s
Total duration: 00:00:04s
Schemas dumped: 1
Tables dumped: 1
Data size: 198.89 MB
Rows written: 1000000
Bytes written: 198.89 MB
Average throughput: 60.96 MB/s
MySQL localhost JS >

These are the files created for the above dump command.

[root@centos12 sysbench_dumps]# ls -lrth
total 190M
-rw-r-----. 1 root root 869 Jun 21 13:08 @.json
-rw-r-----. 1 root root 240 Jun 21 13:08 @.sql
-rw-r-----. 1 root root 240 Jun 21 13:08 @.post.sql
-rw-r-----. 1 root root 231 Jun 21 13:08 sysbench.json
-rw-r-----. 1 root root 638 Jun 21 13:08 sysbench@sbtest1.json
-rw-r-----. 1 root root 474 Jun 21 13:08 sysbench.sql
-rw-r-----. 1 root root 789 Jun 21 13:08 sysbench@sbtest1.sql
-rw-r-----. 1 root root 1.5K Jun 21 13:08 sysbench@sbtest1.csv.idx
-rw-r-----. 1 root root 190M Jun 21 13:08 sysbench@sbtest1.csv
-rw-r-----. 1 root root 233 Jun 21 13:08 @.done.json
[root@centos12 sysbench_dumps]# pwd
/home/vagrant/sysbench_dumps

@.json	Complete information about dump options, servername, and username used for the dump and binlog file and position, etc.
@.sql, @.post.sql.	Shows server version and dump version details.
sysbench.json	Database and table details involved in the dump.
sysbench@sbtest1.json	Details about the table sbtest1, including column names, indexes, triggers, characterset, and partitions.
sysbench.sql	Create a statement for the database sysbench.
sysbench@sbtest1.sql	Create a statement for the table sbtest1.
@.done.json	End time of the dump and dump file size.
sysbench@sbtest1.csv	Table dump file.

 

2. Backup only table structure

Option ddlOnly:true is used to take only the table structures. The below command is to take the table structure of sbtest1 from the sysbench database and store it in the sysbench_dumps path.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, ddlOnly:true})

3. Dump only table data

Option dataOnly:true to take the dump of only data. The below command is to take table data of sbtest1 from the sysbench database and store it in the sysbench_dumps path.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true})

4. Dump only selected data

This “where”: {“databasename.tablename”: “condition”} option is used to take a dump of selected data. The below command is to take a dump of table sbtest1 from id 1 to 10.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1"], "sysbench_dumps", {"compression":"none", "threads":6 ,chunking:false, dataOnly:true, "where" : {"sysbench.sbtest1": "id between 1 and 10"}})

It’s also possible to take a dump of multiple tables with their conditions in a single command.

Syntax:

"where" : {"databasename1.tablename1": "condition for databasename1.tablename1", "databasename2.tablename2": "condition for databasename2.tablename2"}

The below command is to take a dump of table sbtest1 from id 1 to 10 and dump of sbtest2 from id 100 to 110.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1", "sbtest2"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true, "where" : {"sysbench.sbtest1": "id between 1 and 10", "sysbench.sbtest2": "id between 100 and 110"}})

5. Dump data from partitions

The option partitions is to take a dump from selected partitions.

Syntax:

"partitions" : {"db1.table1": ["list of partitions"],"db2.table1": ["list of partitions"]}

The below command is to take a dump from only partitions p1 and p2 and dump of sbtest2 table from partitions p4 and p5.

MySQL localhost JS > util.dumpTables("sysbench", ["sbtest1", "sbtest2"], "sysbench_dumps", {"compression":"none", "threads":6, chunking:false, dataOnly:true, "partitions" : {"sysbench.sbtest1": ["p1", "p2"],"sysbench.sbtest2": ["p4", "p5"]}})

6. Taking Schemas dump

When taking schemas dump, by default, events, triggers, and routines will be taken. Those are stored in the database_name.sql file. The below command is to take a dump of the percona and sakila databases.

MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {"compression":"none", "threads":6, chunking:false})

The below command is to skip the events, routines, and triggers.

MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {events:false, routines:false, triggers:false, "compression":"none", "threads":6, chunking:false})

We can also use these options to include and exclude the events, routines, and triggers

Syntax:

includeEvents   : [db1.include_event1,db2.include_event2...]
includeRoutines : [db1.include_procedure1,db2.include_function2...]
includeTriggers : [db1.include_trigger1,db2.include_trigger2...]

excludeEvents   : [db1.exclude_event1,db2.exclude_event2...]
excludeTriggers : [db1.exclude_trigger1,db2.exclude_trigger2...]
excludeRoutines : [db1.exclude_procedure1,db2.exclude_function2...]

7. Taking specified tables from different databases

Sometimes we may need to take selected tables from different schemas. We can achieve this using the option includeTables.

Syntax:

includeTables:["db1.table1", "db2.table2"....]

Below is the command to take a dump of table users from the percona database and a dump of the actor table from the sakila database.

MySQL localhost JS > util.dumpSchemas(["percona", "sakila"], "schema_dump", {includeTables:["percona.users", "sakila.actor"], "compression":"none", "threads":6, chunking:false})

8. Instance dump

The command  util.dumpInstance takes the dump of a complete instance and stores it in /backup/instance_dump path. The system databases (mysql, sys, information_schema, performance_schema) are excluded, and by default, a dump of all the users from the instance is taken and stored in the file @.users.sql. This user dump file has the create and grant statements of all the users.

MySQL localhost JS > util.dumpInstance("/backup/instance_dump", {"compression":"none", "threads":6, chunking:false})

Some more options in the instance dump.

users: false                                - Skip users dump
excludeUsers : [‘user1’,’user2’]            - Execute particular users
includeUsers : [‘user1’,’user2’].           - Include particular users
excludeSchemas : [“db1”,”db2”]              - Exclude particular schemas
includeSchemas : [“db1”,”db2”].             - Include particular schemas
excludeTables : [“db1.table1”,”db2.table2”] - Exclude particular tables
includeTables : [“db1.table1”,”db2.table2”] - Include particular tables

9. Restore the dump into a single database

The command util.loadDump is used to restore the dumps. The variable local_infile should be enabled to load the dumps.

Syntax :

util.loadDump("/path/of/the/dump", {options})

The below command is to restore the dump into database test_restore. When we need to restore on a different schema, we have to use this option schema: “test_restore”. Otherwise, it will be restored on the source schema where it was taken.

MySQL localhost SQL > create database test_restore;
Query OK, 1 row affected (0.3658 sec)
MySQL localhost SQL > js
Switching to JavaScript mode...
MySQL localhost JS > util.loadDump("/home/vagrant/schema_dump", {schema:"test_restore", progressFile: "progress.json", threads: 8, showProgress:true, resetProgress:true})

10. Restore the full instance dump and configure replication

Here, we just loaded the full instance dump from /home/vagrant/instance_dump path with eight parallel threads.

MySQL localhost JS > util.loadDump("/home/vagrant/instance_dump", {progressFile: "progress.json", threads: 8, showProgress:true, resetProgress:true})
Loading DDL and Data from '/home/vagrant/instance_dump' using 8 threads.
Opening dump...
Target is MySQL 8.0.33-25. Dump was produced from MySQL 8.0.32-24
NOTE: Load progress file detected for the instance but 'resetProgress' option was enabled. Load progress will be discarded and the whole dump will be reloaded.
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
8 thds loading / 100% (19.18 MB / 19.18 MB), 541.36 KB/s, 6 / 23 tables and partitions done
Recreating indexes - done
Executing common postamble SQL
23 chunks (100.00K rows, 19.18 MB) for 11 tables in 2 schemas were loaded in 11 sec (avg throughput 2.53 MB/s)
0 warnings were reported during the load.
MySQL localhost JS >

I got the binlog file and position from the file @.json and configured the replication.

[root@centos12 instance_dump]# cat @.json | grep -i binlog
"binlogFile": "centos12-bin.000006",
"binlogPosition": 760871466,
[root@centos12 instance_dump]#

MySQL localhost SQL > CHANGE REPLICATION SOURCE TO SOURCE_HOST="192.168.33.12", SOURCE_USER="bhuvan",SOURCE_PASSWORD="Bhuvan@123", SOURCE_LOG_FILE='centos12-bin.000006',SOURCE_LOG_POS=760871466;
Query OK, 0 rows affected, 2 warnings (0.1762 sec)

MySQL localhost SQL > START REPLICA;
Query OK, 0 rows affected (0.1156 sec)
MySQL localhost SQL > show replica statusG
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.33.12
Source_User: bhuvan
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: centos12-bin.000006
Read_Source_Log_Pos: 823234119
Relay_Log_File: centos11-relay-bin.000002
Relay_Log_Pos: 1152129
Relay_Source_Log_File: centos12-bin.000006
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 762023266
Relay_Log_Space: 62363195
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 718
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 100
Source_UUID: f46a1600-045e-11ee-809f-0800271333ce
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Waiting for dependent transaction to commit
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.1470 sec)

I hope the above examples help to understand the backup and restore using MySQL Shell. It has many advantages over native mysqldump. I personally feel that we are missing insert statements here, as we used to see the insert statements in dump files; apart from that, it looks good. Logical backup is good only when the dataset is small. When the dataset is big, the logical backup takes longer, and we have to go for physical backup using Percona XtraBackup.

Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server for MySQL and MySQL. It performs online non-blocking, tightly compressed, highly secure backups on transactional systems so that applications remain fully available during planned maintenance windows.

 

Download Percona XtraBackup

Percona Alerting was introduced in Percona Monitoring and Management (PMM) 2.31, and it brought a range of custom alerting templates that makes it easier to create Alert rules to monitor your databases.

In this article, we will go over how to set up Alerting in PMM running on Docker and receive notifications via emails when such alerts are triggered. We will also leverage Grafana’s notification templates to customize the alert emails to reduce noise.

Configuring PMM with Grafana SMTP

To configure SMTP for Grafana, we need to provide the following environment variables:

• GF_SMTP_ENABLED: When true, enables Grafana to send emails.
• GF_SMTP_HOST: Host address of your SMTP server.
• GF_SMTP_USER: Username for SMTP authentication.
• GF_SMTP_PASSWORD: Password for SMTP authentication
• GF_SMTP_SKIP_VERIFY: When true, verifies SSL for the SMTP server.
• GF_SMTP_FROM_ADDRESS: Email address to be used when sending out emails.
• GF_SMTP_FROM_NAME: Name to be used when sending out emails.

Create a .env file in your preferred working directory and add your SMTP credentials as follows:

GF_SMTP_ENABLED=true
GF_SMTP_HOST=smtp.gmail.com:587
GF_SMTP_USER=<YOUR_EMAIL@DOMAIN.COM>
GF_SMTP_PASSWORD=<YOUR_SMTP_PASSWORD>
GF_SMTP_SKIP_VERIFY=false
GF_SMTP_FROM_ADDRESS=<YOUR_EMAIL@DOMAIN.COM>
GF_SMTP_FROM_NAME=Percona Alerting

If you are using your Gmail’s SMTP credentials, as shown above, you will have to generate an app password and fill it in as the value of your $GF_SMTP_PASSWORD variable.

Next, start your PMM instance with the created .env file using the docker command below:

docker run --env-file=.env -p 443:443 -p 80:80 --name=pmm-alerting percona/pmm-server:2

Adding MongoDB services

With our PMM instance up and running, let us add a new MongoDB service for PMM to monitor. Start up a MongoDB container in Docker with the command below:

docker run -d -p 27017:27017 -e MONGO_INITDB_ROOT_USERNAME=admin -e MONGO_INITDB_ROOT_PASSWORD=admin --name mongo-pmm-alerting percona/percona-server-mongodb --bind_ip=0.0.0.0

Next, run the Docker command below to get the Host IP. This is needed as we will use it as our host address in the next step.

docker network inspect bridge -f '{{range .IPAM.Config}}{{.Gateway}}{{end}}'

In your PMM dashboard, go to the “Add Instance” page on PMM ($HOST/graph/add-instance) and click the MongoDB tile.

On the next page, fill in the details of your MongoDB instance. The Hostname is the Host IP we got from the docker command above, while the username and password are the values of MONGO_INITDB_ROOT_USERNAME and MONGO_INITDB_ROOT_PASSWORD we set while starting the Mongo container.

NB: Using the default root credentials is not recommended in production environments, and you can find details on setting up a user for PMM in the Percona documentation.

You can verify the newly added service on the PMM inventory page (at http://localhost/inventory), which should look like the one below: Don’t worry about the ‘N/A’ status of the newly added service, though, as it typically flips to ‘OK’ after a few health checks are performed.

Customizing emails with notification templates

By default, Alert emails from Grafana can get noisy as it includes all the alert labels in the subject line (among other things). To tone things down, we will create a new subject template for our own alerts. Head over to the Contact Points tab in the Alerting section and click the New Template button. In the New Template page, use pmm_subject as your template name and add the code below in the “Content” field:

{{ define "pmm_subject" }}
[{{ .Status | toUpper }}
{{ if eq .Status "firing" }}:{{ .Alerts.Firing | len }}
  {{ if gt (.Alerts.Resolved | len) 0 }}
    RESOLVED:{{ .Alerts.Resolved | len }}
  {{ end }}
{{ end }}]
{{ range .CommonLabels.SortedPairs -}}
  {{ if eq .Name "alertname" }}
    {{ .Value }}
  {{ end }}
  {{ if eq .Name "cluster" }}
    - {{ .Value }}
  {{ end }}
  {{ if eq .Name "service_name" }}
    - {{ .Value }}
  {{ end }}
{{ end }}
{{ end }}

In the template above, we define the name (using the same name from the template name field). We then extract the alert status (Firing or Resolved) as well as the number of alerts in each status. In the second section, we loop through a sorted list of the alert labels and extract the alert name, cluster name, and service name in order. That way, our subject line is in the form:

FIRING: 1 MongoDB is down - prod-cluster - mongo-eu-1

Where prod-cluster is our cluster name, and mongo-eu-1 is our service name.

To learn more about Grafana’s notification templates and Go templating generally, you can check out the Grafana documentation and the template package documentation, respectively. Our next step is to register the created template with Grafana. Click the edit icon beside the default contact point and update the email address(es) on the settings page.

Next, expand the optional settings and add the following text to the subject field:

{{ template "pmm_subject" . }}

This will register the template we created earlier and direct Grafana to use it as alerting email subject. At this point, you can go ahead and test your configuration using the “Test” button on the contacts point settings page.

To get a feel of a real alert, though, we will set up an Alert rule based on the Mongo down Alerting template that comes with PMM.

Alert Rules from Percona Alert Templates

PMM includes a set of default templates with common events and expressions for alerting. These templates can then be used as a basis for your alert rules (or even your own templates), and you can find them in the “Alert Rule Templates” tab. Our next step is to create an alert rule based on one of these templates, specifically, the “Mongo down” alerting template. Click the “+” icon beside the template name (highlighted below), and it will take you to the “New Alert rule page”.

Most of the fields are already pre-filled (since the rule is based on a template), and we only need to specify a Folder/Alerting Group. In our case, we will go with the “MongoDB” folder. Select that and click the “Save” button.

Our new alerting rule is now ready and should appear as “Normal” in the “Alert Rule” tab.

To trigger the alert specified in our rule from the last section, stop the MongoDB docker container and wait for 60 seconds (the default evaluation time set for the alert). The alert should now be firing on both your PMM dashboard and if your email was set up correctly, you’ll also get an email about it.

 

Conclusion

Percona Alerting templates further help simplify the process of setting up alerting for your environment. In this blog post, we’ve reviewed how to do that, for example, in a MongoDB setup. You can install PMM and further explore Alerting, or ask questions/give your feedback on the PMM forums.

 

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today

Today we are talking about Secrets Management with Dwayne McDaniel.

For show notes visit: www.talkingDrupal.com/405

Topics

• What is new with you?
• Secrets Management
• Why it is important
• Best practices
• Secrets in Drupal
• Common errors
• What does rotating mean
• Best way to rotate
• How often should you rotate
• Git Guardian

Resources

• Talking Drupal 187 – Dwayne McDaniel
• MidCamp
• GitKraken
• Git Guardian
• Lockr
• Hashicorp
• Uber key leak
• Toyota
• AstraZeneca
• Verizon dbir databreach
• Azure keyvault youtube video
• Have I Been Pwned
• State of secrets report
• Dark reading
• Repl.it
• Key Drupal Module
• Lockr Drupal Module
• akeyless

Hosts

Nic Laflin – www.nLighteneddevelopment.com @nicxvan John Picozzi – www.epam.com @johnpicozzi Sean T. Walsh – @seantwalsh

MOTW Correspondent

Martin Anderson-Clutz – @mandclu Security Review Automatically tests for many easy-to-make site configuration and setup mistakes that can make a Drupal site insecure.

We have faced different levels of corruption related to databases in PostgreSQL. Our colleague has written multiple blogs on the subject; please refer to the below links for more:

• The Ultimate Guide to Database Corruption: Part 1 – An Overview
• The Ultimate Guide to Database Corruption: Part 2 – B-Tree Index Corruption

In this blog, we will be discussing the scenario where a data file related to a table goes missing, maybe due to OS (hardware problem) or due to human interruption, which causes the deletion of some data file unintentionally at the OS level. Though it is not at all recommended to touch the
/data/base/ directory and go through files under this
/var/lib/postgresql/14/main/base/, however, sometimes it happens.

Our current database was running fine with the below structure:

List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
 percona   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 9561 kB | pg_default |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 8553 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +| 8401 kB | pg_default | unmodifiable empty database
           |          |          |         |         | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +| 8553 kB | pg_default | default template for new databases
           |          |          |         |         | postgres=CTc/postgres |         |            |
(4 rows)

Somehow we are getting the below error message in PostgreSQL logs:

2023-06-14 09:58:06.408 UTC [4056] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-06-14 09:58:06.412 UTC [4056] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-06-14 09:58:06.423 UTC [4057] LOG:  database system was shut down at 2023-06-14 09:58:04 UTC
2023-06-14 09:58:06.432 UTC [4056] LOG:  database system is ready to accept connections
2023-06-16 10:00:58.130 UTC [35062] postgres@percona ERROR:  could not open file "base/16384/16391": No such file or directory
2023-06-16 10:00:58.130 UTC [35062] postgres@percona STATEMENT:  select * from test limit 1;
2023-06-16 10:01:59.191 UTC [35224] postgres@percona ERROR:  could not open file "base/16384/16391": No such file or directory
2023-06-16 10:01:59.191 UTC [35224] postgres@percona STATEMENT:  select * from test limit 1;

Upon checking, we found it was due to one file (
base/16384/16391) being removed. So we need to check whether this
base/16384/16391  file is available in
/base location.

postgres@ip-172-xx-xx-xx:~/14/main$ ls -l base/16384/16391
ls: cannot access 'base/16384/16391': No such file or directory

Also, we can check at the DB level by using this SQL Query:

percona=# SELECT relid, relname FROM pg_catalog.pg_statio_user_tables
WHERE relid = '16391';
 relid | relname
-------+---------
 16391 | test
(1 row)

From the above, we have identified that the file for the table “test” with relid 16391 got deleted. We need to identify whether it was deleted manually by mistake or was due to hardware failure. 

In case of hardware failure, first, we need to fix the hardware issue or migrate our database to new hardware and then perform a restore, as mentioned below.

To restore, we can follow either of below approaches:

1. If the standby server is there and the issue does not persist on that, then we can fix the issue by taking logical backup from the standby server, dropping the table on primary, and recreating it using the backup taken.
2. Perform PITR if we have backup configured with proper archive backup.
3. Restore database “percona” if we have proper backup configured using pgBackRest backup.
4. If we have a table-level logical backup, then we can restore the “test” table backup, and our database will be in good shape.

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.

 

Download Percona Distribution for PostgreSQL Today!

Percona is a leading provider of unbiased, performance-first, open source database solutions that allow organizations to easily, securely, and affordably maintain business agility, minimize risks, and stay competitive, free from vendor lock-in.

Our Release Roundups showcase the latest Percona software updates, tools, and features to help you manage and deploy our software. It offers highlights, critical information, links to the full release notes, and direct links to the software or service itself to download.

Today’s post includes those releases and updates that have come out since June 12, 2023. Take a look!

Percona Distribution for MySQL (PS-based variation) 8.0.33

On June 15, 2023, Percona Distribution for MySQL (PS-based variation) 8.0.33 was released. It is the most stable, scalable, and secure open source MySQL distribution, with two download options: one based on Percona Server for MySQL and one based on Percona XtraDB Cluster. This release is focused on the Percona Server for MySQL-based deployment variation and is based on Percona Server for MySQL 8.0.33-25.

A list of the additional MyRocks variables, as well as improvements and bug fixes introduced by Oracle for MySQL 8.0.33, are in the release notes.

Download Percona Distribution for MySQL (PS-based variation) 8.0.33

Percona Server for MySQL 8.0.33-25

Percona Server for MySQL 8.0.33-25 was released on June 15, 2023. It includes all the features and bug fixes available in the MySQL 8.0.33 Community Edition in addition to enterprise-grade features developed by Percona.

Percona Server for MySQL is a freely available, fully compatible, enhanced, and open source drop-in replacement for any MySQL database. It provides superior and optimized performance, greater scalability and availability, enhanced backups, increased visibility, and instrumentation.

Download Percona Server for MySQL 8.0.33-25

 

That’s it for this roundup, and be sure to follow us on Twitter to stay up-to-date on the most recent releases! Percona is a leader in providing best-of-breed enterprise-class support, consulting, managed services, training, and software for MySQL, MongoDB, PostgreSQL, MariaDB, and other open source databases in on-premises and cloud environments, and is trusted by global brands to unify, monitor, manage, secure, and optimize their database environments.