Infrastructure at your Service

All Posts By

Daniel Westermann

Daniel Westermann

PostgreSQL 12: Control when generic plans are used

By | Database Administration & Monitoring | No Comments

When you are using prepared statements in PostgreSQL you might get a custom or a generic plan. Custom plans come with overhead of re-planning while generic plans avoid re-planning of the statement. A soon as you get a generic plan that plan will be used for the lifetime of your session and there are situations when you do not want to see this as a generic plan might be more expensive than a custom plan….

Read More
Daniel Westermann

Securely store passwords in PostgreSQL

By | Database Administration & Monitoring | No Comments

Every application somehow needs to deal with passwords. Some use external authentication methods such as ldap, others us the the framework the database already provides and create users and roles. But it is also not uncommon that applications implement their own concept for managing users. If an application does this it should be done the right way and passwords should never be stored in plain text in the database. PostgreSQL comes with a handy extension…

Read More
Daniel Westermann

Can you start two (or more) PostgreSQL instances against the same data directory?

By | Database Administration & Monitoring | No Comments

As PostgreSQL does not know the concept of running multiple instances against the same files on disk (e.g. like Oracle RAC) it should not be possible to start two or more instances against the same data directory. If that would work the result can only be corruption. In this post we will look at how PostgreSQL is detecting that and what mechanism are build in to avoid the situation of having multiple instances working against…

Read More
Daniel Westermann

Creating PostgreSQL users with a PL/pgSQL function

By | Database Administration & Monitoring | No Comments

Sometimes you might want to create users in PostgreSQL using a function. One use case for this is, that you want to give other users the possibility to create users without granting them the right to do so. How is that possible then? Very much the same as in Oracle you can create functions in PostgreSQL that either execute under the permission of the user who created the function or they run under the permissions…

Read More
Daniel Westermann

Bringing up an OpenShift playground in AWS

By | Application integration & Middleware, Database Administration & Monitoring | No Comments

Before we begin: This is in no way production ready, as the title states. In a production setup you would put the internal registry on a persistent storage, you would probably have more than one master node and you would probably have more than on compute node. Security is not covered at all here. This post is intended to quickly bring up something you can play with, that’s it. In future posts will explore more…

Read More
Daniel Westermann

PostgreSQL 12: Explain will display custom settings, if instructed

By | Database Administration & Monitoring | No Comments

How many times did you try to solve a performance issue but have not been able to reproduce the explain plan? Whatever you tried you always got a different result. Lets say you managed to get a dump of the database in question, got all the PostreSQL parameters the same, gathered statistics but still you do not manage to get the same plan as the one who reported the issue. What could be a potential…

Read More
Daniel Westermann

PostgreSQL 12: Copying replication slots

By | Database Administration & Monitoring | No Comments

The concept of replication slots was introduced in PostgreSQL 9.4 and was created to prevent a primary instance to delete WAL that a replica still needs to apply. That could happen when you have a network interruption or the replica was down for another reason. With replication slots you can prevent that at the downside that your master could fill up your disk if the interruption is too long. This concept of a “physical replication…

Read More
Daniel Westermann

The EDB filter log extension

By | Database Administration & Monitoring | No Comments

This is another post dedicated to EnterpriseDB Postgres. Sometimes you may want to get specific messages not getting logged to the server’s logfile or audit records. That might be specific error codes or, even more important, passwords you specify when you create users. EDB comes with a solution for that by providing an extension which is called EDB Filter Log. Lets see how you can install, and even more important, how to use that extension.

Read More