Infrastructure at your Service

Tag

PostgreSQL Archives - Blog dbi services

Franck Pachot

ROLLBACK TO SAVEPOINT;

By | Oracle | No Comments

By Franck Pachot . I love databases and, rather than trying to compare and rank them, I like to understand their difference. Sometimes, you make a mistake and encounter an error. Let’s take the following example: create table DEMO (n int); begin transaction; insert into DEMO values (0); select n “after insert” from DEMO; update DEMO set n=1/n; select n “after error” from DEMO; commit; select n “after commit” from DEMO; The “begin transaction” is…

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