Infrastructure at your Service

All Posts By

Daniel Westermann

Daniel Westermann

About temp_tablespaces in PostgreSQL

By | Database Administration & Monitoring | One Comment

There often is a discussion if tablespaces should be used in PostgreSQL. People used to work with Oracle are often a bit surprised by this discussion as there is no way around using tablespaces in that technology. PostgreSQL does not force you to use tablespaces at all and I am pretty sure that there are plenty of PostgreSQL installations out there that do not use them and never even considered using them and that is…

Read More
Daniel Westermann

Collations in PostgreSQL – basics

By | Database Administration & Monitoring | No Comments

When you work with databases sooner or later you will need to get in touch witch collations. It might be that you never needed to think about it as initdb picked a value that just worked for you and nobody in your database ever tried to use a collation that is not available in your installation. Once someone is trying that or there is the requirement to use a specific collation from the beginning you…

Read More
Daniel Westermann

Adding PostgreSQL extensions without being super user?

By | Database Administration & Monitoring | 2 Comments

Usually, when you need to install a PostgreSQL extension you do this as superuser (or at least I am doing it like this). The downside of that is, of course, that a super user must be available once a new extension is required or that all the extensions are installed by default (e.g. in template1). Recently the question popped up internally if you can install extensions without being super user. The answer is: yes and…

Read More
Daniel Westermann

PostgreSQL 13: parallel vacuum for indexes

By | Database Administration & Monitoring | No Comments

Because of its implementation of MVCC PostgreSQL needs a way to cleanup old/dead rows and this is the responsibility of vacuum. Up to PostgreSQL 12 this is done table per table and index per index. There are a lot of parameters to fine tune auto vacuum but none of those allowed vacuum to run in parallel against a relation. The only option you had to allow auto vacuum to do more work in parallel was…

Read More
Daniel Westermann

Deploying your own PostgreSQL image on Nutanix Era – 2 – Deploying a new PostgreSQL VM

By | Database Administration & Monitoring | One Comment

In the last post I described how you can create your own PostgreSQL image in Nutanix Era. In Nutanix wording this is a “Software profile”. This profile can now be used to deploy PostgreSQL VMs with just a few clicks or by using the API. In this post we’ll look at how this can be done and if it is really as easy as Nutanix promises. We’ll be doing it by using the graphical console…

Read More
Daniel Westermann

Deploying your own PostgreSQL image on Nutanix Era

By | Database Administration & Monitoring | No Comments

Some days ago we had a very good training on Nutanix. Nutanix is a Hyper-converged infrastructure and that means that all is software driven and the system can be deployed on many hardware configurations. I will not go into the details of the system itself but rather look at one component/module which is called Era. Era promises to simplify database deployments by providing a clean and simple user interface (and an API) that provides deployment…

Read More
Daniel Westermann

Real time replication from Oracle to PostgreSQL using Data Replicator from DBPLUS

By | Database Administration & Monitoring | No Comments

I’ve done quite some real time logical replication projects in the past, either using Oracle Golden Gate or EDB replication server. Build in logical replication in PostgreSQL (which is available since PostgreSQL 10) can be used as well when both, the source and the target are PostgreSQL instances. While being at the DOAG conference and exhibition 2019 I got in contact with people from DBPLUS and they provide a product which is called “Data Replicator”….

Read More
Daniel Westermann

Enabling, disabling, and validating foreign key constraints in PostgreSQL

By | Database Administration & Monitoring | No Comments

Constraints are in important concept in every realtional database system and they guarantee the correctness of your data. While constraints are essentials there are situations when it is required to disable or drop them temporarily. The reason could be performance related because it is faster to validate the constraints at once after a data load. The reason could also be, that you need to load data and you do not know if the data is…

Read More
Daniel Westermann

A schema and a user are not the same in PostgreSQL

By | Database Administration & Monitoring | One Comment

When people with an Oracle background attend our PostgreSQL DBA Essentials training there is always a bit of confusion about schemas and users. In Oracle a schema and a user is a one to one relationship and there is no real distinction between a user and a schema. In PostgreSQL the situation is different: All the objects a user is creating are created in a specific schema (or namespace). Other users may or may not…

Read More