Infrastructure at your Service

PostgreSQL Archives - Blog dbi services

Daniel Westermann

Inheriting super user privileges over a role automatically in PostgreSQL

By | Database Administration & Monitoring | No Comments

In a recent project at a customer where we synchronize the users and group out of Active Directory we hit a little issue I was not aware of before. Suppose you have created a role in PostgreSQL, you made that role a superuser and then granted that role to another role. What happens when you login using the other role? Will you have the super user privileges by default? Sounds confusing, I know, so lets…

Read More
Daniel Westermann

EDB containers for OpenShift 2.3 – PEM integration

By | Database Administration & Monitoring | No Comments

A few days ago EnterpriseDB announced the availability of version 2.3 of the EDB containers for OpenShift. The main new feature in this release is the integration of PEM (Postgres Enterprise Manager), so in this post we’ll look at how we can bring up a PEM server in OpenShift. If you did not follow the lats posts about EDB containers in OpenShift here is the summary: Deploying EDB containers in MiniShift/OpenShift Scaling the EDB containers…

Read More
Mouhamadou Diaw

Masking Data With PostgreSQL

By | Database Administration & Monitoring, Database management, Postgres | 4 Comments

I was searching a tool for anonymizing data in a PostgreSQL database and I have tested the extension pg_anonymizer. PostgreSQL_anonymizer is a set of SQL functions that remove personally identifiable values from a PostgreSQL table and replace them with random-but-plausible values. The goal is to avoid any identification from the data record while remaining suitable for testing, data analysis and data processing. In this blog I am showing how this extension can be used. I…

Read More
Daniel Westermann

When we do a pg_dump and right afterwards truncate a table which is in the dump, what happens?

By | Database Administration & Monitoring | 2 Comments

Being at customers is always the best way to learn. Today while discussing that pg_dump will always produce a consistent dump because it uses the “repeatable read” isolation level this question came up: What happens when we dump a database and while the dump is running we truncate a table in that database? Does that block? Well, the answer is in the documentation: pg_dump is a utility for backing up a PostgreSQL database. It makes…

Read More
Daniel Westermann

The size of Oracle Home: from 9GB to 600MB – What about PostgreSQL?

By | Database Administration & Monitoring | 2 Comments

A recent blog post from Franck and a tweet around that topic is the inspiration for this blog post, thanks Jan for requesting :). In short it is about how small you can get the binaries. Is that important? At least when it comes to Docker images it might get important as you usually try make the image as small as possible. Well, comparing PostgreSQL and Oracle in that field is unfair as Oracle comes…

Read More
Daniel Westermann

Bringing up your customized PostgreSQL instance on Azure

By | Database Administration & Monitoring | No Comments

The Azure cloud becomes more and more popular so I gave it try and started simple. The goal was to provision a VM, compiling and installing PostgreSQL and then connecting to the instance. There is also a fully managed PostgreSQL service but I wanted to do it on my own just to get a feeling about the command line tools. Here is how I’ve done it.

Read More
Daniel Westermann

Backing up and restoring EDB containers in MiniShift/OpenShift

By | Database Administration & Monitoring | 4 Comments

The last blogs in the series are already some days old: Setting up MiniShift, Deploying EDB containers in MiniShift/OpenShift, Customizing PostgreSQL parameters in EDB containers in MiniShift/OpenShift, Scaling the EDB containers in MiniShift/OpenShift, EDB Failover Manager in EDB containers in Minishift/OpenShift and EDB Failover Manager in EDB containers in Minishift/OpenShift – Failovers. What is missing is how you can backup and restore instances running in this container deployment and that is the topic of this…

Read More
Daniel Westermann

When does PostgreSQL create the table and index files on disk?

By | Database Administration & Monitoring | No Comments

A question that pops up from time to time is: When we create a table or an index in PostgreSQL are the files on disk created immediately or is this something that happens when the first row is inserted? The question mostly is coming from Oracle DBAs because in Oracle you can have deferred segment creation. In PostgreSQL there is no parameter for that so lets do a quick test.

Read More
Daniel Westermann

Syncing Active Directory users and groups to PostgreSQL

By | Database Administration & Monitoring | No Comments

A lot of companies use Active Directory to manage their users and groups. What most of this companies also want to do is to manage their database users and groups in Active Directory. PostgreSQL comes with ldap/kerberos authentication by default but does not provide anything that helps with managing users and groups in an external directory. And even for the authentication the user already needs to be existent in PostgreSQL. One tool you might want…

Read More