Daniel Westermann

Enabling, disabling, and validating foreign key constraints in PostgreSQL

Constraints are in important concept in every realtional database system and they guarantee the correctness of your data. While constraints are essentials there are situations when it is required to disable or drop them temporarily. The reason could be performance(…)

A schema and a user are not the same in PostgreSQL

When people with an Oracle background attend our PostgreSQL DBA Essentials training there is always a bit of confusion about schemas and users. In Oracle a schema and(…)

Fun with arrays in PostgreSQL

As you might already know, PostgreSQL comes with many, many data types. What you might not know is, that you can create arrays over all this data types quite easily. Is that(…)

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(…)

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(…)

A simple Terraform script to create an AWS EC2 playground

When I started to write the blog about AWS SSM I quickly realized that I need a way to bring up and destroy my AWS EC2 playground in an easy and reproducible way. There are several(…)

Avoiding patching madness by using AWS SSM

As we have more and more customers either currently moving to AWS or already moved much, if not even all, of their workloads to AWS, one question pops up quite frequently: Now that we have so(…)

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(…)

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(…)