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.
By Franck Pachot . In a previous post I started this series of “Recovery in the ☁” with the Oracle Autonomous database. My goal is to explain the recovery procedures, especially the Point-In-Time recovery procedures because there is often confusion, which I tried to clarify in What is a database backup (back to the basics). And the terms used in…
One the really cool things with PostgreSQL is, that you have plenty of choices when it comes to tooling. For high availability we usually go with Patroni, but there is also pg_auto_failover and this will be the topic of this post. Because of the recent announcement around CentOS we’ll go with Debian this time. What is already prepared is the…
Since PostgreSQL 9.3 there is the possibility to create materialized views in PostgreSQL. PostgreSQL 9.4 (one year later) brought concurrent refresh which already is a major step forward as this allowed querying the materialized view while it is being refreshed. What still is missing are materialized views which refresh themselves, as soon as there are changed to the underlying tables.…
PostgreSQL is already very fast with loading loading large amounts of data. You can follow this post to get some recommendations for loading data as fast as possible. In addition you can create unlogged tables, but this on the table level and not the whole cluster. With this patch there will be another option: wal_level=none. With this, only minimal WAL…
On a previous blog post, we saw how to create an account on the Oracle OCI using the Oracle Cloud Free Tier offer and then how to instal MySQL Server on the Compute instance. Some weeks later, the new MySQL Database Service (MDS) was out and I can show you now how to install and configure it. We are talking…
Declarative partitioning was introduced in PostgreSQL 10 and since then has improved quite much over the last releases. Today almost everything is there what you would expect from such a feature: You can partition by range, list and hash Attaching and detaching partitions Foreign keys Sub-partitioning Indexing and constrains on partitions Partition pruning What is missing, is the possibility to…
A PostgreSQL instance (or cluster) can contain many databases, three of them (template0, template1 and postgres) are there by default. Over the last years we trained many people on PostgreSQL Essentials and there have been mainly two points that needed more clarification when it comes to catalogs and the postgres default database: Does the postgres default database define the catalog…
It is a common misunderstanding that VACUUM FULL saves you from running out of disk space if you already have space pressure. Running a VACUUM FULL temporarily requires at least double the space, as the table (and the indexes on the table) get completely re-written. PostgreSQL 14 will probably come with a solution for that as this patch introduces the…
In the last post we had a look at an interesting patch (schema variables) that is currently waiting to be committed for PostgreSQL 14. Another patch, that currently is in the same state is named [PATCH] Add features to pg_stat_statements. As this does not give much information what the patch is about, here a short summary: When you reach the…
Almost all of us, who already installed Enterprise DB Tools on a Linux server know the procedure. You need to add the EDB Repository to your server or your Red Hat Satellite configuration and after that you can easily install the tools you need. But what happens, if you are not able to add the repository? The answer is really…
One of the bits you need to solve when you migrate from Oracle to PostgreSQL is this: In the Oracle database there are PL/SQL packages and some of those have package variables defined. PostgreSQL does not know the concept of a package but you can use schemas to group your PL/pgSQL functions and procedures. When it comes to packages variables…
Before PostgreSQL 11 there was no possibility to work with procedures in PostgreSQL, only functions have been supported. Since PostgreSQL 11, procedures are supported and many waited for that because procedures also brought transaction control (commit/rollback), which is not possible with functions. Next year, when PostgreSQL 14 will be released there will also be support for OUT parameters. Currently only…
