Infrastructure at your Service

All Posts By

Daniel Westermann

Daniel Westermann

Creating simple extensions for PostgreSQL

By | Database Administration & Monitoring | No Comments

When you are using PostgreSQL you should already know that PostgreSQL comes with a set of extensions by default. It might be that you need to install an additional package if you installed PostgreSQL with a package manager to get those extensions. Usually it is called something with “contrib” in its name. There is also the PostgreSQL Extension Network which list a lot of external extensions that might be useful for you. If you can’t…

Read More
Daniel Westermann

What are these *.ready and *.done files for in PostgreSQL?

By | Database Administration & Monitoring | No Comments

When you run PostgreSQL workloads in production you must have a backup and restore implementation. Even for development instances, which are like production for the developers, a well-tested backup and restore procedure sometimes must be in place. Community PostgreSQL comes with pg_basebackup to help you with creating a full consistent backup of your PostgreSQL cluster. If you want to be able to do point in time recovery (PITR) you need to archive the WAL segments…

Read More
Daniel Westermann

New predefined roles for PostgreSQL 14

By | Database Administration & Monitoring | No Comments

Time is moving fast and PostgreSQL 14 is already in beta 2. PostgreSQL 14 will ship with a lot of new features and in this post will look at a smaller one: There are three new predefined roles: pg_read_all_data, pg_write_all_data and pg_database_owner. While it seems to be obvious what the first two roles are about, the third one might sound strange at the beginning. Let’s have a look.

Read More
Daniel Westermann

Why is the default postgres database slightly larger than template1?

By | Database Administration & Monitoring | No Comments

You probably know that a fresh initialized PostgreSQL cluster comes with three databases by default: template0, template1, and postgres. If you want to know why they are there, and what their purpose is, check this post. In this post we’ll look at something you may never have noticed: Why is the postgres database, even after a fresh initdb, slightly larger than template1 and template0?

Read More
Daniel Westermann

Getting started with Citus – Setting up a four node cluster

By | AWS, Database Administration & Monitoring | No Comments

One issue with traditional database systems like PostgreSQL is, that you cannot easily scale vertically. Of course you could add read replicas and offload read operations, but that either requires changes in the application, or putting something in front that understands the PostgreSQL dialect and automatically routes writes to the primary and spreads reads across one or more replicas (e.g. pgpool-II). But even if you have something in place, you need to deal with replications…

Read More
Daniel Westermann

PostgreSQL: set_config and current_setting

By | Database Administration & Monitoring | No Comments

PostgreSQL comes with a rich set of administrative functions for various use cases. There are functions to control parameters, there are functions for index maintenance, others are for recovery and or backup control and there is much more. In this post we’ll look at how you can use the build-in functions to set and get parameters without touching the configuration file (or setting parameters at the cluster, database and user level). In the posts that…

Read More
Daniel Westermann

Building a PostgreSQL EC2 AMI for testing the latest commits

By | AWS, Database Administration & Monitoring | No Comments

Testing the latest features or bug fixes in PostgreSQL can easily be done by pulling the latest commits from the git repository and compile PostgreSQL from source code. I am doing that frequently on my local sandbox VM but this becomes limited when I need more CPUs or more memory. This is where the public cloud really shines, as you can easily deploy new VMs, do your tests and get rid of it afterwards with…

Read More
Daniel Westermann

temBoard on SLES15 – 2 – The agent

By | Database Administration & Monitoring | No Comments

In the last post we’ve installed the temBoard repository and the UI. As this alone is not so much fun, we’ll be installing the temBoard agent on another SLES 15 machine in this post. We’ll end up with one PostgreSQL instance registered in the temBoard repository and this instance is constantly monitored. With the repository and the UI we had the requirement to use Python 2.7, but this is not anymore required for the temBoard…

Read More