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.
When PostgreSQL 18 was released last year one of the major features was the introduction of the asynchronous I/O subsystem. The main configuration parameter for this was (and still is) io_method, which can be “worker” (the default), io_uring or sync (the old behavior). If you opted for “workers” the number of those workers is controlled by “io_workers” and the default…
When PostgreSQL 18 introduced the ability to verify tar based (and compressed) backups with pg_verifybackup there was one limitation: The verification of the WAL files in the tars (or compressed files) had to be skipped (–no-parse-wal) because pg_waldump in that version of PostgreSQL is not able to cope with that (and pg_waldump is used by pg_verifybackup). This will change with…
Usually we do not see many foreign data wrappers being used by our customers. Most of them use the foreign data wrapper for Oracle to fetch data from Oracle systems. Some of them use the foreign data wrapper for files but that’s mostly it. Only one (I am aware of) actually uses the foreign data wrapper for PostgreSQL which obviously…
Since PostgreSQL 18 was released last year checksums are enabled by default when a new cluster is initialized. This also means, that you either need to explicitly disable that when you upgrade from a previous version of PostgreSQL or you need to enable this in the old version of PostgreSQL you want to upgrade from. The reason is, that pg_upgrade…
PostgreSQL already comes with plenty of system information functions to reconstruct the commands to create various objects, e.g. constraints or indexes. Starting with PostgreSQL 19 more functions will be available, namely those: As the names imply they can be used to recreate the commands to create a database, a role, or a tablespace. To see what they do lets create…
PostgreSQL already has impressive support for working with data in json format. If you look at the jsonb data type and all the built-in functions and operators you can use, there is so much you can do with it by default. Starting with PostgreSQL 19 there is one feature more when it comes to working with data in json format.…
Before PostgreSQL 19 you had two commands to completely rewrite a table: Either you can use the “vacuum full” or the “cluster” command to achieve this. Both operations are blocking and the table cannot be used until those operations complete. This can easily be verified with the following simple test cases: The same is true for the “cluster” command: Starting…
In our performance tuning workshop, especially when attendees have an Oracle background, one question for sure pops up every time: How can I use optimizer hints in PostgreSQL. Up until today there are three answers to this: Well, now we need to update the workshop material because this was committed for PostgreSQL 19 yesterday. The feature is not called “hints”…
In the lasts posts in this series we’ve looked at Fujitsu’s distribution of PostgreSQL (here and here) and EnterpriseDB’s distribution of PostgreSQL (here and here) which both come with support for TDE (Transparent Data Encryption). A third player is Cybertec with it’s Cybertec PostgreSQL EE distribution of PostgreSQL and this is the distribution we’re looking at in this and the…
Usually the requests we get are around getting data from Oracle into PostgreSQL, but sometimes also the opposite is true and so it happened recently. Depending on the requirements, usually real time vs. delayed/one-shot, there are several options when you want to read from Oracle into PostgreSQL. One common of way of doing this is to use the foreign data…
One of the limitation of pg_dumpall up to PostgreSQL version 18 is, that you can only dump in text mode. While pg_dump can dump in various formats (plain,custom,tar and directory) since ages. pg_dump was always limited to text mode. This will change with PostgreSQL 19 because this was committed recently. The default is still plain text but now you have…
In the last post we’ve looked at how you you get EDB Postgres Extended server installed on your system. In contrast to the version provided by Fujitsu, TDE needs to be enabled when you initialize a new cluster, so lets do this and see how we can proceed from there on. When we’ve initialized a new cluster in the last…
