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

PostgreSQL on Amazon RDS – Securing the beast

By | Database Administration & Monitoring | No Comments

In the last post I looked at how to bring up a PostgreSQL instance in the Amazon cloud. In this post I’ll look into how to restrict access to this instance to specific IP addresses to strengthen security. If you think about moving things to the cloud you for sure want to make the stuff accessible to restricted people or systems only. On the left side there is a “Security Groups” menu which sounds like…

 
Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Setting up the beast

By | Database Administration & Monitoring | No Comments

Today I had a look at PostgreSQL in Amazon RDS. Once you have an Amazon AWS account (this requires a valid credit card for verification) setup bringing up a PostgreSQL instance is pretty easy. The first 12 months are free but be sure to read about the restrictions and what is terminated by default and what is not. Once your AWS account is ready you can login to the management console which lists all the…

 
Read More
Daniel Westermann

How fast can you setup a standby database with PostgreSQL?

By | Database Administration & Monitoring | No Comments

While setting up a PostgreSQL infrastructure at customer today my colleague Pierre Sicot and I discussed on how time consuming it is to setup standby databases if you compare different vendors. Lets make a challenge out of this. This is the baseline: Install the product from scratch Create a master database Create a hot standby database (on the same host) The standby database needs to be open read only Create a table containing one record…

 
Read More
Daniel Westermann

Watching sql statements in PostgreSQL

By | Database Administration & Monitoring | No Comments

Back in 2012 I wrote a small blog post about watching the results of commands in Linux. Well, the same can be done in psql: (postgres@[local]:4448) [postgres] > create table t1 ( a int ); CREATE TABLE (postgres@[local]:4448) [postgres] > insert into t1 values ( generate_series ( 1, 10)); INSERT 0 10 (postgres@[local]:4448) [postgres] > select count(*) from t1; count ——- 10 (1 row) (postgres@[local]:4448) [postgres] > \watch Watch every 2s Mon Dec 21 07:34:35…

 
Read More
Daniel Westermann

Setting your cluster name in PostgreSQL 9.5

By | Database Administration & Monitoring | 2 Comments

PostgreSQL 9.5 introduces a new parameter which is called: cluster_name. So, what is this good for? Imagine you have two (or even more) PostgreSQL clusters running on the same host. When looking at the operating system processes this might look like this: postgres@oel7:/home/postgres/ [PG6] ps -ef | grep “postgres:” postgres 2325 2324 0 14:01 ? 00:00:00 postgres: logger process postgres 2327 2324 0 14:01 ? 00:00:00 postgres: checkpointer process postgres 2328 2324 0 14:01 ?…

 
Read More
Daniel Westermann

pg_dump: [archiver (db)] query failed: ERROR: schema “sys” does not exist

By | Database Administration & Monitoring, Database management | No Comments

As I could not find any information about this error when I faced it this is just a short hint: If you try to use pg_dump or pg_dumpall out of a PPAS installation to dump or dumpall a community version of PostgreSQL you probably get this: pg_dump: [archiver (db)] query failed: ERROR: schema “sys” does not exist pg_dump: [archiver (db)] query was: SET search_path = pg_catalog, sys, dbo The solution is quite easy but you…

 
Read More
Daniel Westermann

Parallel sequential scans are coming to PostgreSQL

By | Database Administration & Monitoring | No Comments

It didn’t made it into the upcoming PostgreSQL 9.5 release but it will probably be coming in 9.6: Parallel sequential scans. If you take a look at the development documentation some new parameters showed up: max_parallel_degree: The maximum degree of parallelism for an individual parallel operation. parallel_tuple_cost: Sets the planner’s estimate of the cost of transferring a tuple from a parallel worker process to another process. The default is 0.1 parallel_setup_cost: Sets the planner’s estimate…

 
Read More
Daniel Westermann

Monitoring tools for PostgreSQL – POWA

By | Database Administration & Monitoring, Database management | No Comments

The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer. For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor: pg_stat_statements (see last post) pg_stat_kcache: gathers statistics about reads and writes done by the file system layer pg_qualstats: gathers statistics of predicates found in where statements and join clauses btree_gist: provides GiST index operator…

 
Read More
Daniel Westermann

Monitoring tools for PostgreSQL – pgcluu

By | Database Administration & Monitoring | No Comments

The last posts introduced the logging system, pg_stat_statements and pg_activity. All of these can be used to monitor sql statements the PostgreSQL server is executing. In this post I’ll look into pgcluu: PostgreSQL Cluster utilization! This is a more complete monitoring solution as it is not only focused on sql statements but gives you information about the database cluster itself and other useful stuff. All you need to run pgcluu is a modern perl distribution…

 
Read More
Daniel Westermann

SQL Monitoring in PostgreSQL (3) – pg_activity

By | Database Administration & Monitoring | 7 Comments

The last posts looked at how the logging system and the pg_stat_statements extension can be used to monitor sql statements in PostgreSQL. This post will introduce pg_activity which is very similar to htop. There are some dependencies which need to be installed before we can start installing pg_activity. The first one is python. As I am on a redhat based distribution this is quite easy: [root@oel7 ~] yum install -y python Then we need to…

 
Read More