Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.
We’ve already briefly looked at Fujitsu Enterprise Postgres (here and here) when it comes to TDE (Transparent Data Encryption) in PostgreSQL. In this and the next post we’ll look at EDB’s distribution of PostgreSQL and how TDE can be configured and used in his commercial flavor of PostgreSQL. For getting EDB Postgres Extended Server installed you need to create an…
In the last post we did the setup of Fujitsu Enterprise Postgres so we’re now ready to look at how TDE is implemented in this distribution of PostgreSQL. The unit of encryption in this version of PostgreSQL is a tablespace but before we can encrypt anything we need to create a master encryption key. The location where the keystore gets…
While TDE (Transparent Data Encryption) is considered a checklist feature in some parts of the PostgreSQL community this topic comes up all over again. The same is true with our customers, it doesn’t matter at all if it technically makes sense to have TDE or not, some just must have it for reasons outside of their control, mostly due to…
In the last post we’ve looked at the basics when it comes to row locking in PostgreSQL and how that looks like in pg_locks. In this post we’ll look at transaction IDs and virtual transaction IDs in the area of locking. We’ll start with the same simple table as in the previous post: The same test as last time, two…
Sometimes it is necessary to troubleshoot locks in PostgreSQL to figure out what is going on in the system. While locks are important and necessary in a database system, too many locks can also be an issue, performance wise and resource consumption wise. There are several levels of locks, e.g. table level and row level locks. Depending on what kind…
In the last post we’ve deployed Forgejo on FreeBSD 15. In this post we’re going to do something with it and that is: We’ll create a new organization, a new repository, and finally we want to create a simple action. An “Action” is what GitLab calls a pipeline. Creating a new organization is just a matter of a few clicks:…
In recent customer projects I had less to do with PostgreSQL but more with reviewing infrastructures and give recommendations about what and how to improve. In all of those projects GitLab is used in one way or the other. Some only use it for managing their code in Git and work on issues, others use pipelines to build their stuff,…
Logical replication in PostgreSQL got a lot of features and performance improvements over the last releases. It was introduced in PostgreSQL 10 back in 2017, and PostgreSQL 9.6 (in 2016) introduced logical decoding which is the basis for logical replication. Today logical replication is really mature and from my point of view only two major features are missing: DDL replication…
When you go for replication and you don’t use synchronous replication there is always a window when data written on the primary is not yet available in the replica. This is known as “replication lag” and can be monitored using the pg_stat_replication catalog view. A recent commit to PostgreSQL 19 implements a way to wait for data to be visible…
Two nice little improvements have been committed for PostgreSQL 19. The first one is about logging the duration of automatic analyze while the second one is about displaying the current search_path in psql’s prompt. Lets start with the improvement for psql. As you probably know, the default prompt in psql looks like this: While I do not have an issue…
In the last post we’ve learned the basics for migrating a sample procedure from PL/SQL to PL/pgSQL. In this post we’ll actually start with migrating the code. The first step in the original procedure was to iterate over the foreign key constraints of the user’s tables and to disable them: If you try this as it is in PL/pgSQL this…
Migrating from one database system to another is not an easy task. It might be easy if you only need to migrate the data, but as soon as you have business logic in the database this can become a lot work quite fast. When you want to migrate from Oracle to PostgreSQL this means rewriting all your PL/SQL code into…
