PostgreSQL

Dealing with corrupted system indexes in PostgreSQL

This is something you do not want to see on an important PostgreSQL system: postgres@centos8pg:/home/postgres/ [pgdev] psql psql: error: could not connect to server: FATAL: index "pg_class_oid_index" contains unexpected zero page at block 0 HINT: Please REINDEX it. The hint(…)

Some words about SOUG Day in Lausanne

Today I participate to SOUG Day which takes place in Lausanne at the "Centre Pluriculturel et social d’Ouchy".

Can there be orphaned data files in PostgreSQL?

PostgreSQL, as all other relational database systems, needs to persist data on disk, either by writing the writing the write ahead log or by synchronization the data files on disk whenever there is a checkpoint. When(…)

PostgreSQL 13 will allow to drop databases even when there are connections

One of the most popular blogs I've written about PostgreSQL was about the three databases that PostgreSQL creates by default (well, actually it is initdb that creates the databases). One of the(…)

Connection pooling with PgBouncer

Some of you may know the case: As soon as the number of users grow, the number of resource problems increases. Have you ever thought about using a connection pooler? Too complex, too much administration effort? In this post I(…)

pg_auto_failover: Setup and installation

When I attended PGIBZ 2019 earlier this year, I talked with Dimitri about pg_auto_failover and I promised to have a look at it. Well, almost half a year(…)

Patroni Operations – Changing Parameters

Sooner or later all of us have to change a parameter on the database. But how is this put into execution when using a Patroni cluster? Of course there are some specifics you have to consider. This post will give(…)

PostgreSQL 13 will come with partitioning support for pgbench

A lot of people use pgbench to benchmark a PostgreSQL instance and pgbench is also heavily used by the PostgreSQL developers. While declarative partitioning was introduced in PostgreSQL 10 there was no(…)

PostgreSQL check_function_bodies, what is it good for?

One of the probably lesser known PostgreSQL parameters is check_function_bodies. If you know Oracle, then you for sure faced "invalid objects" a lot. In PostgreSQL, by default, there is nothing like an invalid object. That implies(…)

Why you really should use peer authentication in PostgreSQL

It is always a bit of a surprise that many people do not know peer authentication in PostgreSQL. You might ask why that is important as initdb creates a default pg_hba.conf which(…)