Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.
With the just released version of 17 of PostgreSQL, copy got the “ON_ERROR” switch. This is very useful if you load something which contains rows that cannot be inserted into the target table. Before that, copy would just stop and error out. The only option was to fix the data and then copy again. Using on “ON_ERROR”, copy can be…
While PostgreSQL 17 was released just a couple of days ago, the development of PostgreSQL 18 is already going on and the first patches got committed. One of them addresses a limitation with pg_verifybackup: Currently (up to version 17) it can only verify backups created with pg_basebackup which are in “plain” format. This means, backups taken either in “tar” or…
The PostgreSQL optimizer relies on accurate statistics for finding and executing the most efficient plan for a given statement. Wrong or bad statistics usually lead to sub optimal execution plans and this usually is bad performance wise. This is not only true with PostgreSQL, all databases systems which use a cost based optimizer rely on statistics. Usually, you don’t need…
While pg_dump must not be considered a backup tool (please use physical backups for this), it is widely used to dump and reload data. One of the main advantages is, that you can do this across major versions of PostgreSQL. Other advantages are the plenty of options you have for dumping data out of a PostgreSQL database: Only the schema,…
Now that we know how we can benchmark a CloudNativePG deployment, it is time to look at how we can connect external applications to the PostgreSQL cluster. Usually, applications run in the same Kubernetes cluster and can directly talk to our PostgreSQL deployment, but sometimes it is required to also connect with external applications or services. By default, this does…
In this blog I will show you how you can setup a basic MariaDB Galera Cluster on Debian 11 (bullseye).If you wish to have a more general overview first you can have a look here: https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ For this we have 3 servers: galera-cluster-1a/b/c. The following steps will first be executed on my first node, galera-cluster-1a still these steps have to…
Now that we’ve configured our storage in the last post, it is time to benchmark the system. One popular tool when it comes to benchmarking PostgreSQL is pgbench. This can be used in our setup as well, and if you have installed the CloudNativePG kubectl plugin this is quite easy to use. Of course the cluster needs to be already…
This is the next post in the series about CloudNativePG (the previous one are here, here, here, here, here and here). In this post we’ll look at storage, and if you ask me, this is the most important topic when it comes to deploying PostgreSQL on Kubernetes. In the past we’ve seen a lot of deployments which used NFS as…
By now, if you followed the previous posts (here, here, here, here and here), we know quite a bit about how to use CloudNativePG to deploy a PostgreSQL cluster and how to get detailed information about the deployment. What we’ll look at in this post is how you can leverage this deployment to scale the cluster up and down. This…
As we’re getting more and more familiar with CloudNativePG, now it’s time to get more information about our cluster, either for monitoring or troubleshooting purposes. Getting information about the general state of our cluster can be easily done by using kubectl. For listing the global state of our cluster, you can do: As we’ve seen in the previous posts (here,…
Now that we know how we can configure the PostgreSQL instance and access control, we’re going to look at what you can do when it comes to PostgreSQL extensions with CloudNativePG. As we have a running cluster, let’s see what we have available: This are the PostgreSQL standard extensions plus pg_audit and pg_vector but nothing more by default. To use…
In the last post we’ve created a more detailed version of the cluster definition for CloudNativePG. This gave us a PostgreSQL cluster with enabled data checksum, the correct locale, an increased size of the WAL segments and the possibility to run custom statements or scripts after initdb. What we didn’t touch at all, was the configuration of PostgreSQL, and this…
