Infrastructure at your Service
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…
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…
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.
This Blog is a follow up uf one of my older Blogs and my Article at heise.de: Blog at dbi-services.com Article at heise.de For RHEL 8 at its clones like AlmaLinux, CentOS, Oracle Linux and Rocky Linux i have written a shell script to automated recurring setups. This is used for a cloud project using own virtual machines. The script automates the steps i have described at my article at heise.de. The script has an…
You can already find several blog posts around PostgreSQL protected by Patroni on our blog. They all have in common, that the setup is on a flavor of Linux. As I got impressed by FreeBSD and played with it during the last weeks (some blog posts here), I wanted to know if Patroni runs well on FreeBSD. Well, here are the results …
By Franck Pachot . This follows the previous post about the Oracle Cloud ARM compute shape just announced on May 25th. The processor is ARM v8.2 with LSE (atomic instructions) and PostgreSQL can benefit from it (see Dramatical Effect of LSE Instructions for PostgreSQL on Graviton2 Instances). I have installed GCC 11 in the previous post, on a Oracle Linux 7.9 image with comes with GCC 7. If you installed the Ubuntu 20.4 image, you…
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?
By Franck Pachot . In a previous post I’ve compiled PostgreSQL with GCC 7 and GCC 11 with the default options and checked that the ARM v8.2 features were used. However it may not be so simple: PostgreSQL ./configure defines gcc flags to produce a binary that is compatible with older versions of ARM. This means that it may not use the LSE feature introduced in ARM v8.1 With this, the default compilation (-march=armv8-a) doesn’t…
I believe most people use pg_ctl nowadays to start, stop, or restart a PostgreSQL instance. You can do all these tasks without using pg_ctl, but you’ll notice in a minute that pg_ctl really is your friend. Even if you’re using pg_ctl already, is is good to know what happens in the background and this is the scope of this post.
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…
