Infrastructure at your Service

Tag

PostgreSQL Archives - Blog dbi services

Daniel Westermann

Dealing with German “Umlaute” in PostgreSQL’s full text search

By | Database Administration & Monitoring | No Comments

PostgreSQL comes with build-in Full Text Search and you can do quite amazing stuff with it. A question that popped up during one of the last PostgreSQL for developers workshop was: How can I deal with German “Umlaute” such as “ä”, “ö” and “ü” in such a way, that I can search for e.g. “Schnösel” and the result will give me “Schnösel” as well as “Schnoesel”? One way to deal with that would be to…

Read More
Daniel Westermann

Cluster level encryption for PostgreSQL 14

By | Database Administration & Monitoring | No Comments

The discussions how and why TDE (Transparent data encryption) should be implemented in PostgreSQL goes back several years. You can have a look at these two more recent threads to get an idea on how much discussion happened around that feature: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS) Re: Internal key management system Finally an essentials part for that infrastructure was committed and I am sure, many people have waited for…

Read More
Julia Gugel

Running two Patroni on one host using an existing etcd

By | Database Administration & Monitoring | No Comments

Have you ever asked yourself, how to create a second Patroni PostgreSQL cluster on an existing server using the existing etcd? My first idea was to study the documentation of Patroni, but unfortunately without big success. This post should help to identify the changes you have to do on the hosts to run two parallel Patroni clusters using an existing etcd.

Read More
Daniel Westermann

pg_auto_failover: Failover and switchover scenarios

By | Application integration & Middleware | No Comments

In the last post we had a look at the installation and setup of pg_auto_failover. We currently have one primary cluster and two replicas synchronizing from this primary cluster. But we potentially also have an issue in the setup: The monitor is running beside the primary instance on the same node and if that nodes goes down the monitor is gone. What happens in that case and how can we avoid that? We also did…

Read More
Franck Pachot

Recovery in the ☁ with Google Cloud SQL (PostgreSQL)

By | Cloud, Postgres | No Comments

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 managed cloud services or documentation is not very clear, not always the same, and sometimes…

Read More
Daniel Westermann

Easy failover and switchover with pg_auto_failover

By | Database Administration & Monitoring | No Comments

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 PostgreSQL installation (version 13.1), but nothing else. We start from scratch to see, if “is…

Read More
Daniel Westermann

Incremental materialized view maintenance for PostgreSQL 14?

By | Database Administration & Monitoring | No Comments

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. This might change with PostgreSQL 14, as this patch is in active development (at least…

Read More
Daniel Westermann

Even faster data loading with PostgreSQL 14? wal_level=none

By | Database Administration & Monitoring | No Comments

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 is written, but of course at the cost of losing durability. If the cluster crashes…

Read More
Daniel Westermann

PostgreSQL 14: Automatic hash and list partitioning?

By | Database Administration & Monitoring | No Comments

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 let PostgreSQL create partitions automatically. With this patch this will finally be possible for hash…

Read More
Daniel Westermann

The PostgreSQL shared/global catalog

By | Database Administration & Monitoring | No Comments

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 and somehow is the master database? What exactly is in the global catalog? In this…

Read More