Infrastructure at your Service

All posts by Daniel Westermann

Daniel Westermann
Senior Consultant and Technology Leader Open Infrastructure

Daniel Westermann has more than 10 years of experience in management, engineering and optimization of databases and infrastructures. He is specialized in Oracle Technologies as Performance Optimization and Tuning, Standardization, Backup & Recovery, in High Avaibility solutions as Oracle Real Application Clusters (RAC), Oracle Data Guard, Oracle Grid Infrastructure, as well as in storage technologies as Oracle Automatic Storage Management (ASM). Daniel Westermann is Oracle Certified Professional 10g/11g/12c. He is also a PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. Prior to dbi services, Daniel Westermann was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper & Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel Westermann holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery, telecommunications.
Daniel Westermann

Developer GUI tools for PostgreSQL

By | Database Administration & Monitoring | No Comments

There was a recent thread on the PostgreSQL general mailing list asking for GUI tools for PostgreSQL. This is question we get asked often at customers so I though it might be good idea to summarize some of them in a blog post. When you know other tools than the ones listed here which look promising, let me know so I can add them. There is a list of tools in the PostgreSQL Wiki as…

 
Read More
Daniel Westermann

A wonderful PostgreSQL feature: default privileges

By | Database Administration & Monitoring | No Comments

Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the user which owns the objects creates new objects? Then you will need to grant those to the second user as…

 
Read More
Daniel Westermann

Setting up default parameters for roles in PostgreSQL

By | Database Administration & Monitoring | No Comments

As you might know you can set various parameters on the session level in PostgreSQL by using the “set” command: postgres=# \h set Command: SET Description: change a run-time parameter Syntax: SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | ‘value’ | DEFAULT } SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT } This allows a session to adjust parameters at runtime and…

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 17 – Identifying a blocking session

By | Database Administration & Monitoring | No Comments

One single blocking session in a database can completely halt your application so identifying which session is blocking other sessions is a task you must be able to perform quickly. In Oracle you can query v$session for getting that information (blocking_session, final_blocking_session). Can you do the same in PostgreSQL? Yes, you definitely can, lets go.  

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 16 – DDL triggers

By | Database Administration & Monitoring | No Comments

A question I received recently from a customer: Oracle gives you the possibility to create DDL triggers. DDL triggers fire (as the name implies) when DDL events occur. Can we do that in PostgreSQL? Yes, this feature is called event triggers. There is a nice list in the documentation which lists for which DDLs an event trigger can fire: Event Trigger Firing Matrix. Lets go …  

 
Read More
Daniel Westermann

What are typed tables in PostgreSQL?

By | Database Administration & Monitoring | No Comments

While reading the PostgreSQL documentation for “CREATE TABLE” I came across this: “OF type_name: Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed table is tied to its type; for example the table will be dropped if the type is dropped (with DROP TYPE … CASCADE).” Sounds interesting, lets have a look.  

 
Read More
Daniel Westermann

Did you ever wonder what PostgreSQL is executing in the background when you use the psql shortcuts?

By | Database Administration & Monitoring | No Comments

When you work with PostgreSQL you are probably using psql and when you use psql you are probably using one or more of the shortcuts psql is providing. These shortcuts provide a quick and convenient way to get meta data out of the PostgreSQL catalog which safes you from a lot of typing and typos. The only issue with that is that it hides the statements which are executed to get the meta data so…

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 15 – invisible indexes

By | Database Administration & Monitoring | 2 Comments

It has been quite a while since the last post in this series. Today we’ll look at what you know from Oracle as: Invisible indexes. In case you wonder what they might be useful for: Imagine you want to test if an index would benefit one or more queries without affecting the production workload. In other words: Wouldn’t it be cool to create an index but somehow tell the optimizer not to use it for…

 
Read More
Daniel Westermann

Summer, autumn and winter: a lot of conferences ahead

By | Database Administration & Monitoring | No Comments

It is hot in Europe, it is summer, enjoy, but technology moves fast so you have the chance to already prepare for the next conferences. The IT Tage 2017 will happen the 11th to 14th of December this year and we are happy to be there again. This event covers a wide range of topics and we will be there again covering and talking about: Stéphane Haby, SQL Server 2017: Überblick Stéphane Haby, SQL Server:…

 
Read More