Infrastructure at your Service

All Posts By

Daniel Westermann

Daniel Westermann

PostgreSQL 14: Add the number of de-allocations to pg_stat_statements?

By | Database Administration & Monitoring | No Comments

In the last post we had a look at an interesting patch (schema variables) that is currently waiting to be committed for PostgreSQL 14. Another patch, that currently is in the same state is named [PATCH] Add features to pg_stat_statements. As this does not give much information what the patch is about, here a short summary: When you reach the maximum number of allowed statements to track, pg_stat_statements will de-allocate entries to free space for…

Read More
Daniel Westermann

Will PostgreSQL14 finally come with schema variables?

By | Database Administration & Monitoring | No Comments

One of the bits you need to solve when you migrate from Oracle to PostgreSQL is this: In the Oracle database there are PL/SQL packages and some of those have package variables defined. PostgreSQL does not know the concept of a package but you can use schemas to group your PL/pgSQL functions and procedures. When it comes to packages variables there is no easy solution currently. Tip: You can add your own parameters in #postgresql…

Read More
Daniel Westermann

PostgreSQL 14 will support OUT parameters for procedures

By | Database Administration & Monitoring | No Comments

Before PostgreSQL 11 there was no possibility to work with procedures in PostgreSQL, only functions have been supported. Since PostgreSQL 11, procedures are supported and many waited for that because procedures also brought transaction control (commit/rollback), which is not possible with functions. Next year, when PostgreSQL 14 will be released there will also be support for OUT parameters. Currently only IN, INOUT and VARIADIC are supported. This makes PostgreSQL’s procedures more compatible to Oracle’s implementation…

Read More
Daniel Westermann

Loading data from S3 to AWS RDS for PostgreSQL

By | AWS, Database Administration & Monitoring | One Comment

AWS RDS for PostgreSQL comes with an extension that allows you to fetch data from AWS S3 and to write back data to AWS S3. The use case for this is obvious: Either you use other AWS services that write data to S3 and you want to further process that data in PostgreSQL, or you want other AWS services to consume data from PostgreSQL by providing that data in S3. Let’s have a look at…

Read More
Daniel Westermann

Getting started with Exasol – Distribution keys

By | AWS, Database Administration & Monitoring, Database management | No Comments

After the previous posts about Exasol, which have been more around installation, administration, backup & restore, loading data and general notes around transactions and sessions we’ll now go into more details how Exasol manages data. Here is the list of the previous posts: Getting started with Exasol – Setting up an environment Getting started with Exasol – Loading data from PostgreSQL Getting started with Exasol – Some words about indexes and transactions Getting started with…

Read More
Daniel Westermann

Getting started with Exasol – A multi node cluster in AWS

By | AWS, Database Administration & Monitoring | No Comments

As of now, all the previous posts worked with the Community Edition of Exasol. While that is great for getting started quickly (the Community Edition comes with all the features, except multi node support and a limit of 200GB of data) there are some limitations: Can you not really test how data is distributed across cluster nodes because there is only one. You do not have a real access to the operating system as the…

Read More
Daniel Westermann

Getting started with Exasol – Backup and restore (2)

By | Database Administration & Monitoring | No Comments

In the previous post we did the groundwork for being able to backup the Exasol database to an S3 bucket. We learned that you can do full level 0 backups and of top of that you can do incremental backups, that refer to each other. A restore of the full backup worked fine and pretty fast. In this post we’ll restore both incremental backups and check if the result is what we expect. In addition…

Read More
Daniel Westermann

Getting started with Exasol – Backup and restore (1)

By | Database Administration & Monitoring | No Comments

Now that we have a system with a proper network setup we can take a look at how backup and restore works in Exasol. Without being able to restore and recover your database, you for sure cannot put a system into production, so backup and restore always is a major topic to understand (with any system that holds data you do not want to lose). When it comes to backup and restore, this is what…

Read More
Daniel Westermann

Getting started with Exasol – Installation from ISO

By | Database Administration & Monitoring | No Comments

Initially I wanted to write about Backup and Restore of Exasol databases but I quickly ran into issues, and this is the reason why this post is coming first. When you import the Exasol Community OVA as described in the first post, the VM is running with host only adapters and you can not connect to anything outside the Exasol system. This is however required if you want to store the backups outside the cluster….

Read More
Daniel Westermann

Getting started with Exasol – Sessions and auditing

By | Database Administration & Monitoring | No Comments

This is the fourth post in the series about Exasol and this time it is about sessions and auditing. If you are interested in the previous posts you can find them here: Getting started with Exasol – Setting up an environment Getting started with Exasol – Loading data from PostgreSQL Getting started with Exasol – Some words about indexes and transactions

Read More