PostgreSQL Archives | credativ®

In diesem Beitrag beschäftigen wir uns mit dem hochverfügbaren Betrieb von PostgreSQL in einer Kubernetes-Umgebung. Ein Thema, dass für viele unserer PostgreSQL Anwender sicher von besonderem Interesse ist.

Gemeinsam mit unserem Partnerunternehmen MayaData, demonstrieren wir Ihnen nachfolgend die Einsatzmöglichkeiten und Vorteile des äußerst leistungsfähigen Open Source Projektes – OpenEBS

OpenEBS ist ein frei verfügbares Storage Management System, dessen Entwicklung von MayaData unterstützt und begleitet wird.

Wir bedanken uns ganz besonders bei Murat-Karslioglu von MayaData und unserem Kollegen Adrian Vondendriesch für diesen interessanten und hilfreichen Beitrag, den die Kollegen aufgrund der internationalen Zusammenarbeit diesmal natürlich in englischer Sprach verfasst haben.

PostgreSQL anywhere — via Kubernetes with some help from OpenEBS and credativ engineering

by Murat Karslioglu, OpenEBS and Adrian Vondendriesch, credativ

Introduction

If you are already running Kubernetes on some form of cloud whether on-premises or as a service, you understand the ease-of-use, scalability and monitoring benefits of Kubernetes — and you may well be looking at how to apply those benefits to the operation of your databases.

PostgreSQL remains a preferred relational database, and although setting up a highly available Postgres cluster from scratch might be challenging at first, we are seeing patterns emerging that allow PostgreSQL to run as a first class citizen within Kubernetes, improving availability, reducing management time and overhead, and limiting cloud or data center lock-in.

There are many ways to run high availability with PostgreSQL; for a list, see the PostgreSQL Documentation. Some common cloud-native Postgres cluster deployment projects include Crunchy Data’s, Sorint.lab’s Stolon and Zalando’s Patroni/Spilo. Thus far we are seeing Zalando’s operator as a preferred solution in part because it seems to be simpler to understand and we’ve seen it operate well.

Some quick background on your authors:

  • OpenEBS is a broadly deployed OpenSource storage and storage management project sponsored by MayaData.
  • credativ is a leading open source support and engineering company with particular depth in PostgreSQL.

In this blog, we’d like to briefly cover how using cloud-native or “container attached” storage can help in the deployment and ongoing operations of PostgreSQL on Kubernetes. This is the first of a series of blogs we are considering — this one focuses more on why users are adopting this pattern and future ones will dive more into the specifics of how they are doing so.

At the end you can see how to use a Storage Class and a preferred operator to deploy PostgreSQL with OpenEBS underlying

If you are curious about what container attached storage of CAS is you can read more from the Cloud Native Computing Foundation (CNCF) here.

Conceptually you can think of CAS as being the decomposition of previously monolithic storage software into containerized microservices that themselves run on Kubernetes. This gives all the advantages of running Kubernetes that already led you to run Kubernetes — now applied to the storage and data management layer as well. Of special note is that like Kubernetes, OpenEBS runs anywhere so the same advantages below apply whether on on-premises or on any of the many hosted Kubernetes services.

PostgreSQL plus OpenEBS

PostgreSQL plus OpenEBS
We have seen joint users adopting OpenEBS as a substrate to PostgreSQL for a variety of reasons. A few that jump out include:

Consistency in underlying disk or cloud volume management:

One of the most annoying things about setting up a system to run PostgreSQL — even if it is on Kubernetes — is configuring the underlying disks and storage systems as needed. With a solution like OpenEBS, you specify via storage classes how you want the underlying systems configured and OpenEBS with the help of Kubernetes ensures that the system delivers the storage capacity that is needed and that it is configured as you need it. An example of such a storage class is shared below. This automation can remove a source of human error and definitely removes a source of human annoyance.

Thin provisioning and on-demand expansion:

Now that you have turned over to OpenEBS the provisioning and management of the underlying storage hardware and services, you just have to tell it the amount of storage you need for your PostgreSQL and then everything will work out well, right? Well actually knowing how much data your PostgreSQL instance or instances will consume is pretty tricky — and arguably somewhat impossible as it is beyond your control.

Here OpenEBS can also help because it supports both thin provisioning and on the fly pool expansion. The thin provisioning allows you to claim more space than you actually can provisioning — this then allows your PostgreSQL to scale in the usage of space without interruption by allowing for adding more storage to the running system without the need to stop the database.

Thin provisioning though is not a good idea if there is not also on the fly expansion of the underlying capacity for perhaps obvious reasons — as the PostgreSQL expands you want to make sure it can claim space as needed otherwise at some point you’ll have to interrupt operations and again perform manual tasks. OpenEBS helps here as well — if configured to do so it can expand its underlying pools, whether these are of physical disks, underlying storage systems, or storage services from a cloud. The capacity of the pool can be expanded on demand simply by adding more disks to the cStor pool.

The cStor architecture also supports the resizing of a provisioned volume on the fly and this will be fully automated as of OpenEBS 0.9. Via these enhancements, volumes, as well as underlying pools, will be able to scale automatically on any cloud providing K8s support.

In addition to reducing the risk and hassle of manual operations, the combination of thin provisioning and on-demand scaling can reduce costs because you don’t over-provision capacity to achieve performance for example, which reduces unnecessary cloud service spending and can increase average utilization of usage of your hardware as well.

Disaster recovery and migration:

With a solution like OpenEBS, your storage classes can also include back-up schedules — and these can be easily managed either via Kubectl or via the free to use MayaOnline. Again these storage classes can be applied on a per container basis which is quite a bit of granularity and control by each team running their PostgreSQL.

Additionally, we are working together to add tighter integration with PostgreSQL to this per snapshot based workload, per container back-up capability, which is called DMaaS by MayaData and OpenEBS. With this additional integration, an option will be added to the storage classes and to OpenEBS to flush active transactions before taking the snapshot. The additional integration of storage snapshots in conjunction with Write Ahead Log (WAL) archiving will provide additional PITR functionality. DMaaS leverages the open source Velero from Heptio and marries it to the COW based capabilities of the cStor OpenEBS engine to deliver extremely efficient backups and migrations.

With DMaaS backups taken to one location can be recovered from another. This can prove useful for a variety of use cases including the use of relatively ephemeral clusters as a part of a rolling upgrade for example of an environment. Additionally, the same capability can be used to move workloads from one Kubernetes environment to another thereby reducing lock-in.

Snapshots and clones for development and troubleshooting:

DBAs have been using snapshots and clones for a long time to assist in troubleshooting and to enable teams to develop and test against a read-only copy of production data. For example, via OpenEBS you can easily use Kubernetes to invoke a snapshot and then promote that snapshot to a clone and then spawn a container from that clone. You now can do anything you want with that container and the data set contained within it, and of course, destroy it when you are done.

One use case that clones can support is improved reporting. For example, let’s say you do computationally expensive analytical queries and build roll-up queries for monthly reports. It is simple with OpenEBS to clone the underlying OLTP system, allowing you to work on a static copy of your database, thereby removing load from your production DBs and ensuring you have a verifiable source of information for those reports.

Closing the loop with per workload visibility and optimization:

In addition to the benefits of using OpenEBS, there are additional benefits from using MayaOnline for the management of stateful workloads. We may address these in future blogs examining common day 2 operations and optimization of your PostgreSQL on Kubernetes.

Running Postgres-Operator on OpenEBS

PostgreSQL with OpenEBS persistent volumes

Software Prerequisites

Install OpenEBS

  1. If OpenEBS is not installed in your K8s cluster, this can be done from here. If OpenEBS is already installed, go to the next step.
  2. Connect to MayaOnline (Optional): Connecting the Kubernetes cluster to MayaOnline provides good visibility of storage resources. MayaOnline has various support options for enterprise customers.

Configure cStor Pool

  1. If cStor Pool is not configured in your OpenEBS cluster, this can be done from here. As PostgreSQL is a StatefulSet application, it requires a single storage replication factor. If you prefer additional redundancy you can always increase the replica count to 3.
    During cStor Pool creation, make sure that the maxPools parameter is set to >=3. If a cStor pool is already configured, go to the next step. Sample YAML named openebs-config.yaml for configuring cStor Pool is provided in the Configuration details below.

openebs-config.yaml

#Use the following YAMLs to create a cStor Storage Pool.
# and associated storage class.
apiVersion: openebs.io/v1alpha1
kind: StoragePoolClaim
metadata:
 name: cstor-disk
spec:
 name: cstor-disk
 type: disk
 poolSpec:
 poolType: striped
 # NOTE — Appropriate disks need to be fetched using `kubectl get disks`
 #
 # `Disk` is a custom resource supported by OpenEBS with `node-disk-manager`
 # as the disk operator
# Replace the following with actual disk CRs from your cluster `kubectl get disks`
# Uncomment the below lines after updating the actual disk names.
 disks:
 diskList:
# Replace the following with actual disk CRs from your cluster from `kubectl get disks`
# — disk-184d99015253054c48c4aa3f17d137b1
# — disk-2f6bced7ba9b2be230ca5138fd0b07f1
# — disk-806d3e77dd2e38f188fdaf9c46020bdc
# — disk-8b6fb58d0c4e0ff3ed74a5183556424d
# — disk-bad1863742ce905e67978d082a721d61
# — disk-d172a48ad8b0fb536b9984609b7ee653
 — -

Create Storage Class

  1. You must configure a StorageClass to provision cStor volume on a cStor pool. In this solution, we are using a StorageClass to consume the cStor Pool which is created using external disks attached on the Nodes. The storage pool is created using the steps provided in the Configure StoragePool section. In this solution, PostgreSQL is a deployment. Since it requires replication at the storage level the cStor volume replicaCount is 3. Sample YAML named openebs-sc-pg.yaml to consume cStor pool with cStorVolume Replica count as 3 is provided in the configuration details below.

openebs-sc-pg.yaml

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: openebs-postgres
  annotations:
    openebs.io/cas-type: cstor
    cas.openebs.io/config: |
      - name: StoragePoolClaim
        value: "cstor-disk"
      - name: ReplicaCount
        value: "3"       
provisioner: openebs.io/provisioner-iscsi
reclaimPolicy: Delete
---

Launch and test Postgres Operator

  1. Clone Zalando’s Postgres Operator.
git clone https://github.com/zalando/postgres-operator.git
cd postgres-operator

Use the OpenEBS storage class

  1. Edit manifest file and add openebs-postgres as the storage class.
nano manifests/minimal-postgres-manifest.yaml

After adding the storage class, it should look like the example below:

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: acid-minimal-cluster
  namespace: default
spec:
  teamId: "ACID"
  volume:
    size: 1Gi
    storageClass: openebs-postgres
  numberOfInstances: 2
  users:
    # database owner
    zalando:
    - superuser
    - createdb
 
# role for application foo
    foo_user: []
 
#databases: name->owner
  databases:
    foo: zalando
  postgresql:
    version: "10"
    parameters:
      shared_buffers: "32MB"
      max_connections: "10"
      log_statement: "all"

Start the Operator

  1. Run the command below to start the operator
kubectl create -f manifests/configmap.yaml # configuration
kubectl create -f manifests/operator-service-account-rbac.yaml # identity and permissions
kubectl create -f manifests/postgres-operator.yaml # deployment

Create a Postgres cluster on OpenEBS

Optional: The operator can run in a namespace other than default. For example, to use the test namespace, run the following before deploying the operator’s manifests:

kubectl create namespace test
kubectl config set-context $(kubectl config current-context) — namespace=test
  1. Run the command below to deploy from the example manifest:
kubectl create -f manifests/minimal-postgres-manifest.yaml

2. It only takes a few seconds to get the persistent volume (PV) for the pgdata-acid-minimal-cluster-0 up. Check PVs created by the operator using the kubectl get pv command:

$ kubectl get pv
NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE
pvc-8852ceef-48fe-11e9–9897–06b524f7f6ea 1Gi RWO Delete Bound default/pgdata-acid-minimal-cluster-0 openebs-postgres 8m44s
pvc-bfdf7ebe-48fe-11e9–9897–06b524f7f6ea 1Gi RWO Delete Bound default/pgdata-acid-minimal-cluster-1 openebs-postgres 7m14s

Connect to the Postgres master and test

  1. If it is not installed previously, install psql client:
sudo apt-get install postgresql-client

2. Run the command below and note the hostname and host port.

kubectl get service — namespace default |grep acid-minimal-cluster

3. Run the commands below to connect to your PostgreSQL DB and test. Replace the [HostPort] below with the port number from the output of the above command:

export PGHOST=$(kubectl get svc -n default -l application=spilo,spilo-role=master -o jsonpath="{.items[0].spec.clusterIP}")
export PGPORT=[HostPort]
export PGPASSWORD=$(kubectl get secret -n default postgres.acid-minimal-cluster.credentials -o ‘jsonpath={.data.password}’ | base64 -d)
psql -U postgres -c ‘create table foo (id int)’

Congrats you now have the Postgres-Operator and your first test database up and running with the help of cloud-native OpenEBS storage.

Partnership and future direction

As this blog indicates, the teams at MayaData / OpenEBS and credativ are increasingly working together to help organizations running PostgreSQL and other stateful workloads. In future blogs, we’ll provide more hands-on tips.

We are looking for feedback and suggestions on where to take this collaboration. Please provide feedback below or find us on Twitter or on the OpenEBS slack community.

In den letzten Tagen berichteten verschiedene Quellen von einer angeblich schwereren Sicherheitslücke in PostgreSQL. Mittlerweile wurde genannter Artikel ebenfalls analog zu den untenstehenden Ausführungen korrigiert.

Konkret geht es um ein ungeprüftes Ausführen von Executable Code im Kontext des PostgreSQL Dienstes mit Hilfe des Befehls

COPY <table> FROM|TO PROGRAM

Die Details hierzu werden in CVE-2019-9193 beschrieben.

Stellungnahme und Erläuterung

Diese Darstellung ist unter sachlicher Betrachtung allerdings keine Sicherheitslücke im eigentlichen Sinn. Vielmehr handelt es sich bei dem betroffenen COPY-Kommando um eine privilegierte Datenbankoperation, die unter allen Umständen entweder Superuserberechtigungen (in der Regel z.B. der Benutzer postgres) oder Mitgliedschaft in der Datenbankrolle pg_execute_server_program erfordert. In dieser Hinsicht enthält der im Link angegebene CVE auch einen Fehler, denn die Rolle pg_read_server_files ist nicht ausreichend, um als unprivilegierter Datenbankbenutzer überhaupt dieses Kommando ausführen zu können.

Gegeben sind in den folgenden Beispielen die Rollen bernd und test, wobei bernd eine Datenbankrolle mit Superuserprivileg ist und test lediglich das LOGIN-Privileg besitzt (also ein normaler Datenbankbenutzer).

bernd@db=# \duS bernd
                                   List of roles
 Role name │                         Attributes                         │ Member of 
───────────┼────────────────────────────────────────────────────────────┼───────────
 bernd     │ Superuser, Create role, Create DB, Replication, Bypass RLS │ {}
 
bernd@db=# \duS test
           List of roles
 Role name │ Attributes │ Member of 
───────────┼────────────┼───────────
 test      │            │ {}

Im Folgenden wird nun versucht, als Benutzer bernd die Ausgabe des Linux-Kommandos echo in eine Tabelle zu schreiben:

bernd@db=# CREATE TABLE test(val text);
CREATE TABLE
bernd@db=# SELECT current_role;
 current_role 
--------------
 bernd
(1 row)
bernd@db=# COPY test FROM PROGRAM '/bin/echo hallo welt';
COPY 1
bernd@db=# SELECT * FROM test;
    val     
------------
 hallo welt
(1 row)

Das hat funktioniert, da die Superuserprivilegien des Datenbankrolle bernd dies explizit erlauben. Anders sieht dies mit der unprivilegierten Rolle test aus:

test@db=> SELECT current_role;
 current_role 
--------------
 test
(1 row)
test@db=> COPY test FROM PROGRAM 'echo hallo welt';
FEHLER:  nur Superuser oder Mitglieder von pg_execute_server_program können COPY mit externen Programmen verwenden
HINT:  Jeder kann COPY mit STDOUT oder STDIN verwenden. Der Befehl \copy in psql funktioniert auch für jeden.

Das Ausführen von COPY FROM PROGRAM ist der Rolle test nicht gestattet. Man kann es jetzt wie im CVE beschrieben mit der Mitgliedschaft in der Rolle pg_read_server_files versuchen:

bernd@db=# GRANT pg_read_server_files TO test;
 
bernd@db=# SET ROLE test;
 
test@db=> COPY test FROM PROGRAM 'echo hallo welt';
FEHLER:  nur Superuser oder Mitglieder von pg_execute_server_program können COPY mit externen Programmen verwenden
HINT:  Jeder kann COPY mit STDOUT oder STDIN verwenden. Der Befehl \copy in psql funktioniert auch für jeden.

Auch das ist offensichtlich nicht ausreichend. Mit der Mitgliedschaft pg_execute_server_program kommt man dann jedoch an das Ziel:

bernd@db=# GRANT pg_execute_server_program TO test;
 
bernd@db=# SET ROLE test;
 
test@db=> COPY test FROM PROGRAM 'echo hallo welt';
 
test@db=> SELECT * FROM test;
    val     
────────────
 hallo welt
 hallo welt
(2 rows)

Normale Datenbankrollen mit entsprechend fehlenden Berechtigungen sind folglich nicht in der Lage, dieses Kommando auszuführen. Dies ist das Standardverhalten und benötigt keine entsprechenden Änderungen an der Datenbankkonfiguration. Nur Superuser der PostgreSQL-Instanz besitzen standardmäßig die Berechtigung für das erfolgreiche Ausführen von COPY TO|FROM PROGRAM. Dieser Sachverhalt ist auch entsprechend in der PostgreSQL Dokumentation beschrieben.

Das mit COPY TO|FROM angegebene Executable wird immer im Kontext des Betriebssystem-Benutzers ausgeführt, unter dem die jeweilige PostgreSQL-Instanz läuft (in der Regel eigentlich immer postgres). D.h. die Ausführung des Executable unterliegt immer dem Berechtigungskontexts dieses Benutzers. Dieser ist in der Regel eher eingeschränkt, da PostgreSQL bspw. als root nicht gestartet werden kann. Das heißt, beliebige Zugriffe sind somit ausgeschlossen. Folgendes Beispiel versucht nun mit der Rolle test die Datei /etc/shadow mit den Passwörtern des Betriebssystems zu lesen:

test@db=> COPY test FROM PROGRAM 'cat /etc/shadow';
FEHLER:  Programm »cat /etc/shadow« fehlgeschlagen
DETAIL:  Kindprozess hat mit Code 1 beendet

Dies schlägt fehl, denn der angesprochene Berechtigungskontext des Betriebsystem-Benutzers, unter dem die PostgreSQL-Instanz ausgeführt wird, hat keine Zugriffsberechtigung auf /etc/shadow.

Zusammenfassung

Es ist nicht möglich als Standard-Datenbankrolle ohne entsprechend erteilte Berechtigungen ohne weiteres die angebliche Sicherheitslücke wie im CVE beschrieben auszunutzen. Datenbankrollen müssen entgegen der empfohlenen Praxis entweder über SUPERUSER Privilegien verfügen oder explizit die Berechtigung der Rolle pg_execute_server_program erhalten. Daher ist es wichtig bei PostgreSQL Installationen beim Anlegen und Verteilen von Berechtigungen entsprechend sorgfältig vorzugehen. Der Blogartikel des Core Committee Mitglieds Magnus Hagander (in englischer Sprache) beinhaltet ebenfalls eine entsprechende Erläuterung zum Sachverhalt und empfiehlt sich zur weiteren Lektüre.

An dieser Stelle soll explizit von der Verwendung von Datenbankrollen mit SUPERUSER Privileg für Anwendungen oder Nutzern abgeraten werden. Superuser verfügen noch über viel weitreichendere Berechtigungen als COPY TO|FROM PROGRAM. Sie können beliebige Shared Libraries laden, beliebige Objekte ändern und beliebige Konfigurationsänderungen vornehmen. Daher ist die Verwendung von derart privilegierten Rollen außerhalb des Administrationskontextes dringenst zu vermeiden.

Patroni ist eine Hochverfügbarkeitslösung für PostgreSQL mit einem Fokus auf Container-Technologie und Kubernetes. Die bisher vorhandenen Debian-Pakete mussten bislang aufwendig von Hand konfiguriert werden und haben sich nicht in die Distribution integriert. Für das bald erscheinende Debian 10 „Buster“ wurde Patroni nun von credativ in das Debian Standard PostgreSQL-Framework integriert und erlaubt einen einfachen Aufbau von Patroni-Clustern unter Debian.

Aufgrund der Verwendung eines externen „Distributed Consensus Store“ (DCS) wie Etcd, Consul oder Zookeeper kann Patroni zuverlässig Leader-Election und automatisierte Failover durchführen. Dazu kommen planbare Switchover und eine einfache Änderung der Cluster-weiten Konfiguration. Es bietet außerdem eine REST-Schnittstelle, mit der z.B. via HAProxy ein Load-Balancing aufgebaut werden kann. Auf Grund dieser Vorteile hat Patroni in letzter Zeit das früher häufig verwendete Pacemaker als Open Source Projekt der Wahl für die Herstellung einer PostgreSQL-Hochverfügbarkeit abgelöst.

Viele unserer Kunden verwenden allerdings PostgreSQL auf Debian- oder Ubuntu-Systemen. Hier hat sich Patroni bisher nicht gut in das System integriert. So verwendete es nicht das postgresql-common Framework und wird nicht wie übliche Instanzen in pg_lsclusters angezeigt.

Integration in Debian

In Zusammenarbeit mit dem Patroni-Hauptentwickler Alexander Kukushkin von Zalando ist es nun gelungen, das Debian Patroni-Paket weitgehend in das postgresql-common-Framework zu integrieren. Dies geschah sowohl durch Änderungen in Patroni, als auch durch zusätzliche Programme im Debian-Paket. Die aktuelle Version 1.5.5 von Patroni, die alle diese Änderungen enthält, ist nun auch in Debian „Buster“ (testing) verfügbar und kann für den Aufbau von Patroni-Clustern unter Debian verwendet werden.

Zur Verfügung stehen die Pakete auch auf apt.postgresql.org und sind damit auch unter Debian 9 „Stretch“ und Ubuntu 18.04 „Bionic Beaver“ LTS benutzbar. Außerdem kann so jede beliebige PostgreSQL-Version von 9.4 bis 11 verwendet werden.

Wichtigster Punkt ist hierbei die automatische Erstellung einer geeigneten Patroni-Konfiguration mit dem Befehl pg_createconfig_patroni. Der Aufruf erfolgt analog zu pg_createcluster mit der gewünschten Major-Version und dem Instanz-Namen als Parameter:

pg_createconfig_patroni 11 test

Dieser Aufruf erstellt eine Datei /etc/patroni/11-test.yml, wobei die Konfiguration für das DCS aus der Datei /etc/patroni/dcs.yml verwendet wird, die entsprechend dem lokalen Setup angepasst werden muss. Der Rest der Konfiguration entstammt dem Template /etc/patroni/config.yml.in, welches von sich aus lauffähig ist, vom Nutzer aber auch an die eigenen Bedürfnisse angepasst werden kann. Anschließend kann die Patroni-Instanz analog zu regulären PostgreSQL-Instanzen via systemd gestartet werden:

systemctl start patroni@11-test

Cluster-Aufbau in wenigen Schritten

Ein einfacher 3-Knoten Patroni-Cluster kann also mit den wenigen folgenden Befehlen erstellt und gestartet werden, wobei die drei Knoten pg1, pg2 und pg3 als Hostnamen angenommen werden, sowie dass es eine lokale Datei dcs.yml für die DCS-Konfiguration gibt:

for i in pg1 pg2 pg3; do ssh $i 'apt -y install postgresql-common'; done
for i in pg1 pg2 pg3; do ssh $i 'sed -i "s/^#create_main_cluster = true/create_main_cluster = false/" /etc/postgresql-common/createcluster.conf'; done
for i in pg1 pg2 pg3; do ssh $i 'apt -y install patroni postgresql'; done
for i in pg1 pg2 pg3; do scp ./dcs.yml $i:/etc/patroni; done
for i in pg1 pg2 pg3; do ssh @$i 'pg_createconfig_patroni 11 test' && systemctl start patroni@11-test'; done

Danach kann man den Status des Patroni-Clusters folgendermaßen ansehen:

ssh pg1 'patronictl -c /etc/patroni/11-patroni.yml list'
+---------+--------+------------+--------+---------+----+-----------+
| Cluster | Member |    Host    |  Role  |  State  | TL | Lag in MB |
+---------+--------+------------+--------+---------+----+-----------+
| 11-test |  pg1   | 10.0.3.111 | Leader | running |  1 |           |
| 11-test |  pg2   | 10.0.3.41  |        | stopped |    |   unknown |
| 11-test |  pg3   | 10.0.3.46  |        | stopped |    |   unknown |
+---------+--------+------------+--------+---------+----+-----------+

Man sieht, dass eine Leader Election durchgeführt wurde und pg1 der Primary wurde. Dieser hat seine Instanz mit dem Debian-spezifischen pg_createcluster_patroni Programm erstellt, welches im Hintergrund pg_createcluster aufruft. Darauf klonen sich die anderen beiden Instanzen vom Primary mit dem pg_clonecluster_patroni Programm, welches eine Instanz mit pg_createcluster erstellt und dann einen Standby via pg_basebackup vom Primary erstellt. Danach sind alle Knoten im Status running:

+---------+--------+------------+--------+---------+----+-----------+
| Cluster | Member |    Host    |  Role  |  State  | TL | Lag in MB |
+---------+--------+------------+--------+---------+----+-----------+
| 11-test |  pg1   | 10.0.3.111 | Leader | running |  1 |         0 |
| 11-test |  pg2   | 10.0.3.41  |        | running |  1 |         0 |
| 11-test |  pg3   | 10.0.3.46  |        | running |  1 |         0 |
+---------+--------+------------+--------+---------+----+-----------+

Die altbekannten Debian postgresql-common Befehle funktionieren ebenfalls:

ssh pg1 'pg_lsclusters'
Ver Cluster Port Status Owner    Data directory                 Log file
11  test    5432 online postgres /var/lib/postgresql/11/test    /var/log/postgresql/postgresql-11-test.log

Failover-Verhalten

Wenn der Primary Knoten abrupt abgeschaltet wird, wird sein Leader Token nach einiger Zeit auslaufen und Patroni dann einen Failover mit anschließender erneuter Leader Election durchführen:

+---------+--------+-----------+------+---------+----+-----------+
| Cluster | Member |    Host   | Role |  State  | TL | Lag in MB |
+---------+--------+-----------+------+---------+----+-----------+
| 11-test |  pg2   | 10.0.3.41 |      | running |  1 |         0 |
| 11-test |  pg3   | 10.0.3.46 |      | running |  1 |         0 |
+---------+--------+-----------+------+---------+----+-----------+
[...]
+---------+--------+-----------+--------+---------+----+-----------+
| Cluster | Member |    Host   |  Role  |  State  | TL | Lag in MB |
+---------+--------+-----------+--------+---------+----+-----------+
| 11-test |  pg2   | 10.0.3.41 | Leader | running |  2 |         0 |
| 11-test |  pg3   | 10.0.3.46 |        | running |  1 |         0 |
+---------+--------+-----------+--------+---------+----+-----------+
[...]
+---------+--------+-----------+--------+---------+----+-----------+
| Cluster | Member |    Host   |  Role  |  State  | TL | Lag in MB |
+---------+--------+-----------+--------+---------+----+-----------+
| 11-test |  pg2   | 10.0.3.41 | Leader | running |  2 |         0 |
| 11-test |  pg3   | 10.0.3.46 |        | running |  2 |         0 |
+---------+--------+-----------+--------+---------+----+-----------+

Sobald der alte Primary erneut gestartet wird, kehrt er als Standby in den Cluster-Verbund zurück:

+---------+--------+------------+--------+---------+----+-----------+
| Cluster | Member |    Host    |  Role  |  State  | TL | Lag in MB |
+---------+--------+------------+--------+---------+----+-----------+
| 11-test |  pg1   | 10.0.3.111 |        | running |    |   unknown |
| 11-test |  pg2   | 10.0.3.41  | Leader | running |  2 |         0 |
| 11-test |  pg3   | 10.0.3.46  |        | running |  2 |         0 |
+---------+--------+------------+--------+---------+----+-----------+
[...]
+---------+--------+------------+--------+---------+----+-----------+
| Cluster | Member |    Host    |  Role  |  State  | TL | Lag in MB |
+---------+--------+------------+--------+---------+----+-----------+
| 11-test |  pg1   | 10.0.3.111 |        | running |  2 |         0 |
| 11-test |  pg2   | 10.0.3.41  | Leader | running |  2 |         0 |
| 11-test |  pg3   | 10.0.3.46  |        | running |  2 |         0 |
+---------+--------+------------+--------+---------+----+-----------+

Falls dies aufgrund von zusätzlichen Transaktionen in seiner alten Zeitleiste nicht möglich ist, wird er neu erstellt. Im Falle von sehr großen Datenmengen kann auch pg_rewind verwendet werden, hierfür muss allerdings ein Passwort für den postgres-Nutzer gesetzt und reguläre Datenbank-Verbindungen (im Gegensatz zu Replikations-Verbindungen) zwischen den Cluster-Knoten erlaubt werden.

Weitere Instanzen erstellen

Es ist ferner möglich weitere Instanzen mit pg_createconfig_patroni zu erstellen, dabei kann man entweder einen PostgreSQL Port explizit mit der --port-Option angeben oder pg_createconfig_patroni den nächsten freien Port (wie von pg_createcluster bekannt) nehmen lassen:

for i in pg1 pg2 pg3; do ssh $i 'pg_createconfig_patroni 11 test2 && systemctl start patroni@11-test2'; done
ssh pg1 'patronictl -c /etc/patroni/11-test2.yml list'
+----------+--------+-----------------+--------+---------+----+-----------+
| Cluster  | Member |       Host      |  Role  |  State  | TL | Lag in MB |
+----------+--------+-----------------+--------+---------+----+-----------+
| 11-test2 |  pg1   | 10.0.3.111:5433 | Leader | running |  1 |         0 |
| 11-test2 |  pg2   |  10.0.3.41:5433 |        | running |  1 |         0 |
| 11-test2 |  pg3   |  10.0.3.46:5433 |        | running |  1 |         0 |
+----------+--------+-----------------+--------+---------+----+-----------+

Ansible Playbook zum Download

Zur einfachen Erstellung eines 3-Wege Patroni Clusters haben wir auch ein Ansible-Playbook auf Github erstellt. Dieses automatisiert die Installation und Einrichtung von PostgreSQL und Patroni auf den drei Knoten, sowie eines DCS-Servers auf einem vierten Knoten.

Unterstützung

Falls Sie Unterstützung bei PostgreSQL, Patroni, PostgreSQL auf Debian oder anderer Aspekte von Hochverfügbarkeit benötigen, steht Ihnen unser PostgreSQL Competence Center zur Verfügung – Falls gewünscht auch 24 Stunden am Tag, an 365 Tagen im Jahr.

Wir freuen uns über Ihre Kontaktaufnahme.

Viele Nutzer sind durch Herstellervorgaben, Verordnungen oder Arbeitsanweisungen dazu gezwungen, auf sämtlichen IT-Geräten Virenscanner zu betreiben. Diese Verallgemeinerung kann gerade dann, wenn es nicht um klassische Desktops oder Dateiserver geht, zu Problemen führen.

Nach landläufiger Auslegung des Grundschutzes ist dieser gewährleistet, wenn auf allen Systemen ein Virenscanner vorhanden ist und die Signaturen aktuell gehalten werden. Die Einhaltung der beiden Kriterien kann über ein klassisches Monitoring überwacht werden.

Probleme treten jedoch oft dann auf, wenn sogenannte On-Access-Scanner aktiv sind und in die Arbeit mit dem Dateisystem eingreifen. Die PostgreSQL-Entwickler raten sogar dringend von der Verwendung von Antivirensoftware ab!

Virenscanner

Virenscanner und PostgreSQLWas sind (On-Access-) Virenscanner und weshalb werden sie benötigt? Große Teile der verwendeten (Endanwender-) Software unterscheidet nicht klar zwischen Code und Daten. So enthält eine reine Textdatei ausschließlich Daten und keinen Programmcode, es besteht keine Notwendigkeit je einen Teil einer Textdatei als Code zu interpretieren und auszuführen. Leider wird diese Trennung nicht flächendeckend praktiziert. So können viele Dateien z.B. Office-Dokumente sowohl Daten als auch Code enthalten. Hierdurch ergibt sich ein Angriffsvektor. Angreifer können Code in Daten verstecken, der dann unbemerkt vom Nutzer ausgeführt wird.

Da das zugrundeliegende Problem aufwendig zu lösen und auch keine positive Tendenz ersichtlich ist, bieten Virenscanner in vielen Bereichen eine Linderung der Symptome. So ist es gängige Praxis und sinnvoll, eingehende Fremddaten und nicht vertrauenswürdige Nutzereingaben zu validieren und zu prüfen. Hierbei werden diese mit einer Blacklist von bekannten Angriffsmustern verglichen und im Falle einer Übereinstimmung entsprechend behandelt.

Auch wenn neue, maßgeschneiderte oder unbekannte Angriffe durch solche Systeme nicht erkannt werden können, kann die Kosten-Nutzen-Rechnung doch an einigen Stellen aufgehen.

Arbeiten mit dem Dateisystem

Möchte eine Anwendung (A) Daten lesen oder schreiben erfolgt die Interaktion mit dem Speichermedium (B) über Systemcalls. Die Anwendung übergibt die zu schreibenden Daten und erhält eine Bestätigung zurück, ob die Daten erfolgreich geschrieben wurden, bzw. im Fehlerfall einen entsprechenden Fehlercode.

Funktionsweise Echtzeitscanner

Ein Echtzeitscanner oder On-Access-Scanner greift nun in diesen Ablauf ein und setzt sich selbst als Man-in-the-Middle zwischen Applikation (A) und Hardware (B). Der Linux-Kernel bietet hierfür seit einiger Zeit die API fanotify. Verschiedene Hersteller verwenden aber oft eigene, nicht standardisierte Verfahren, um in den Datenstrom einzugreifen. Es gibt diverse mögliche Eingriffspunkte – z.B. die Applikationen selbst oder verwendete E/A-Bibliotheken. Auch Eingriffe in das Dateisystem werden verwendet.

Problem Performance

Durch den Eingriff in die E/A-Zugriffe werden diese deutlich aufwendiger! Je nach Eingriffspunkt und verwendeter Scanengine werden Speicherzugriffe um Größenordnungen langsamer. Auf Office-Desktopsystemen oder Applicationservern (stateless) sind auf Grund niedriger IO und weniger Writes oft keine starken Auswirkungen spürbar. Auf schreiblastigen Systemen können diese jedoch gravierend sein. Wenn viele kleine Bereiche beschreiben werden und es nicht nur auf den mittleren Datendurchsatz, sondern um das schnelle Abarbeiten einzelner Schreibvorgänge mit niedrigen Latenzen geht, ist mit großen Einbußen zu rechnen.

Durch ihre speziellen Nutzungsmuster und komplexe Dienstfunktionalität sind Datenbanksysteme besonders betroffen. Hinzu kommt noch, dass beim eigentlichen Scanvorgang auch CPU-Zeit benötigt wird. Je nach eingesetzter Antiviren-Lösung kann es auch hier zu Engpässen kommen.

Problem Datensicherheit

Wenn zum Abfangen der E/A-Anfragen nur die kerneleigenen APIs verwendet werden, erhöht sich bereits die theoretische Fehlerwahrscheinlichkeit, da mehr Code ausgeführt wird und die Vorgänge komplexer werden. In der Praxis verkompliziert sich hierdurch zwar Debugging und Fehlersuche, mit deutlichen Einbußen in der Datensicherheit ist jedoch nicht zu rechnen, korrekte API-Verwendung vorausgesetzt. fanotify ist jedoch derzeit in den verbreiteten Antivirenlösungen nicht der Standard.

Durch die eigene Implementierung von E/A-Hooks bietet sich eine ganze Menge Fehlerpotenzial. So konnten wir bei der Ursachenermittlung eines korrupten Datenbanksystems sehen, dass die Antivirenlösung E/A-Fehler von Hardware und Treibern teils nicht weitergeleitet hat.

Fehlerbeispiel:

  1. Die Datenbank sendet eine Schreib-Anforderung an das Dateisystems (8k-Block schreiben, Herausschreiben auf Platte)
  2. write() wird von der Antivirenlösung abgefangen
  3. Der Virenscanner überprüft die Daten (kein Fund)
  4. write() wird an das Dateisystem weitergegeben
  5. Das Dateisystem übermittelt die Daten per Treiber an die Hardware
  6. Es tritt ein Hardwareproblem auf! Daten können nicht geschrieben werden!
  7. Der Treiber meldet dem Dateisystem den Fehler
  8. Das Dateisystem meldet dem Virenscanner den Fehler
  9. Der Virenscanner “verschluckt” den Fehler und meldet der Datenbank das erfolgreiche Schreiben
  10. Die Datenbank meldet dem Client die erfolgreiche Dürchführung einer Transaktion

So entstand eine schleichender Datenverlust, der erst entdeckt wurde als den Nutzern der angeschlossenen Systeme Fehler in den Bestandsdaten auffielen. Der Virenscanner hat hier durch sein nicht standardkonformes Verhalten eine der Kernfunktionalität des Datenbanksystems sabotiert.

PostgreSQL

PostgreSQL praktiziert eine strikte Trennung zwischen Daten und Code. Einträge in Tabellen, auch in Binärblöcken, können designbedingt nicht ausgeführt werden.

Aufgrund dieser strikten Trennung ist es für PostgreSQL selbst nicht notwendig, den eigenen Datenbestand auf Viren zu untersuchen. Für weniger gut differenzierende Anwendungssoftware kann es jedoch sinnvoll sein, Teilmengen zu überprüfen. Werden in der Datenbank beispielsweise Emails oder Dateien, die Code enthalten (z.B. Office-Dokumente), abgelegt, kann es durchaus Mehrwert bieten, diese Spalten auf bekannte Schadsoftware hin zu überprüfen.

In der Praxis wird hier jedoch fast immer ein generischer Echtzeitscanner verwendet, der nicht nur die erforderlichen Bereiche prüft, sondern On-Access-Methoden verwendet, und daher sämtliche im ersten Teil angesprochenen Probleme mit sich bringt.

Schadsoftware gefunden, was nun?

Erschwerend kommt hinzu, dass die Bearbeitung von Schadsoftware-Funden bei einer externen Software sehr schwer ist. Was soll z.B. im Falle eines gefundenen trojanischen Pferds passieren?

Automatischer Eingriff

Auf einem Desktop-System möchte man beispielsweise, dass die betroffene Datei in Quarantäne verschoben oder gelöscht wird. Mit dem Betrieb eines Datenbank-Servers ist solches Verhalten jedoch nicht vereinbar. Da es sich bei den Dateien um die physische Repräsentation der Datenbasis handelt, wird durch diesen Mechanismus Datenkorruption verursacht, die sich z.B. als stiller Datenverlust oder auch Verletzung von Konsistenzbedingungen wie das Nichterkennen von Duplikaten äußern kann.

Alarmierung

In der Praxis ist es daher meist sinnvoll, nur Alarmierungen zu erzeugen, die dann von einem Administrator bearbeitet werden. Doch was ist das geeignete Vorgehen, wenn der Virenscanner gestern Nacht eine Infektion der Datei “/var/lib/postgresql/11/main/base/13090/1255” gemeldet hat?

  • Wo ist der infizierte Code in der Datenbank?
  • Wo kam er her?
  • Ist die Meldung überhaupt richtig? False-Positive?

Gibt es bereits zufriedenstellende Lösungen?

Der beschriebene Status-Quo erfüllt offensichtlich nicht alle Anforderungen. Gibt es für Datenbanken (PostgreSQL) eine bessere Lösung?

Derzeit bleibt die Überprüfung in der Anwendung oder Fremdsystemen die verbreitetste Methode. Verschiedene Hersteller bieten auch Netzwerk-Virenscanner an, die den Netzwerkverkehr überwachen und durchsuchen. Aber auch hier gibt es viele der bereits angesprochenen konzeptionellen Schwächen, z.B. wie ein Fund behandelt wird.

Lösungsversuch

pg_SnakeOil LogoWünschenswert wäre es, die Überprüfung auf Schadcode und auch die Behandlung von Funden im Datenbanksystem selbst abzuwickeln. Optimalerweise per SQL-Schnittstelle, so dass Entwickler oder DBAs die Funktionalität in den normalen Anwendungsbetrieb integrieren können.

So könnte genau definiert werden, wann welche Eingabe zu überprüfen ist. Positiv-Funde können dann über die gemeinen SQL-Fehlercodes gemeldet werden, und würden nicht länger die Dienstfunktionalität gefährden. Man könnte sich beispielsweise eine Tabelle mit einer Text-Spalte vorstellen, in der Usereingaben gespeichert werden. Nun kann vor jedem INSERT oder UPDATE überprüft werden, ob bekannter Schadcode vorliegt und die Eintragung per CONSTRAINT verhindert werden.

Um die Machbarkeit einer solch integrierten Antivirenlösung aufzuzeigen, hat credativ die PostgreSQL-Erweiterung pg_snakeoil veröffentlicht. Diese macht die Funktionen von ClamAV in der SQL-Welt zugänglich, und erlaubt eine Schadcode-Prüfung bei voller ACID-Konformität. pg_snakeoil wurde unter der PostgreSQL-Lizenz veröffentlicht und kann in der aktuellen Version bei Github gefunden werden.

Unterstützung

Falls Sie Unterstützung bei PostgreSQL, Malware-Prävention, IT-Grundschutz oder anderer Aspekte der IT-Infrastruktur benötigen, steht Ihnen unser Open Source Support Center zur Verfügung – Falls gewünscht auch 24 Stunden am Tag, an 365 Tagen im Jahr.

Wir freuen uns über Ihre Kontaktaufnahme.

In dieser Woche wurde Version 1.3 unserer PostgreSQL-Appliance Elephant Shed veröffentlicht.

Das Highlight der neuen Version ist die Unterstützung für Red Hat Enterprise Linux 7 und CentOS 7. Wie bereits unter Debian setzt die Appliance auf die postgresql-common-Infrastruktur, für die schon länger ein RPM-Port existiert, der hier nun voll zum Tragen kommt. Die auf RPM-Systemen bekannten PostgreSQL-Pakete von yum.postgresql.org werden über pg_createcluster in das System integriert und können über die Elephant Shed-Weboberfläche verwaltet werden.

Alle weiteren Elephant Shed-Komponenten, unter anderem pgAdmin4, Grafana, Prometheus, pgbackrest, Cockpit und shellinabox, funktionieren weiterhin wie in der Debian-Version der Appliance. Lediglich für die Nutzung von pgAdmin4 und shellinabox muss die SELinux-Funktion abgeschaltet werden, da die Pakete diese Funktion nicht unterstützen.

Neben der RPM-Portierung wurde vor allem die Infrastruktur der Appliance aktualisiert. Der Prometheus-Node-Exporter ist nun in Version 0.16 verfügbar, in der viele Metrik-Namen an das Prometheus-Namensschema angepasst wurden. Das Grafana-Dashboard wurde ebenfalls entsprechend aktualisiert. In der Apache-Konfiguration wurde von authnz_pam auf authnz_external gewechselt, da ersteres auf CentOS nicht verfügbar ist, und eine stabile Funktion unter Debian Buster nicht mehr garantiert wird.

Die nächsten Punkte auf der Elephant-Shed-Roadmap sind die Integration der REST-API für die Kontrolle einzelner Komponenten, sowie Multi-Host-Support, um mehrere Elephant-Shed-Instanzen gleichzeitig zu kontrollieren. Eine Überarbeitung des User-Interface ist ebenfalls geplant.

Die aktualisierten Pakete stehen über packages.credativ.com zum Download bereit. Wer Elephant Shed bereits installiert hat, kann die Updates wie gewohnt über apt einspielen.

Die von credativ entwickelte, quelloffene PostgreSQL-Appliance Elephant-Shed erfreut sich größter Beliebtheit bei Anwendern, da die wichtigsten Komponenten für die Administration und Verwaltung einer PostgreSQL-Instanz bereits integriert sind. Eigene Anpassungen können jederzeit vorgenommen werden.

Für Elephant Shed bietet die credativ einen umfassenden technischen Support mit garantierten Service-Level-Agreements, der optional auch an 365 Tagen im Jahr rund um die Uhr zur Verfügung steht.

Heute wurde PostgreSQL Version 11 veröffentlicht. Das neue Release bringt Verbesserung in vielen Bereichen.

Schon seit Version 9.6 können Query-Pläne auf mehreren CPU-Kernen parallel ausgeführt werden, dies wird nun für weitere Plan-Typen unterstützt, insbesondere das Erstellen von B-tree-Indexen. Verbessert wurden auch Sequential Scans und UNION-Queries.

Brandneu ist die Möglichkeit, Queries per Just-in-Time-Compilation (JIT) zu optimieren. Dafür wird beim Kompilieren von PostgreSQL der Quellcode as LLVM-Bitcode gespeichert. Beim Ausführen einer Query, deren Planner-Kosten eine Schranke überschreiten wird dieser Bitcode von libllvm dann speziell für diese Query in nativen Maschinencode übersetzt. Da alle benutzten Datentypen usw. vorher bekannt sind, fallen im Maschinencode alle Fallunterscheidungen weg, die im allgemeinen Fall notwendig sind. Das Feature ist per default abgeschaltet und kann mit „SET jit = on;“ aktiviert werden. In PostgreSQL 12 soll es dann standardmäßig aktiv sein.

Bisher konnten auf SQL-Ebene nur Funktionen definiert werden. Neu sind nun Prozeduren, die selbständig BEGIN/COMMIT verwalten können. Damit können Batch-Operationen nun vollständig auf die Datenbankseite übertragen werden.

Der Bereich Tabellen-Partitionierung wurde weiterhin verbessert, und unterstützt nun auch Hash-Partitionierung. Die Integration mit Partitionen, die postgres_fdw benutzen, wurde ebenfalls verbessert. Es ist nun möglich, eine Default-Partition anzulegen, die Daten aufnimmt, die in keine der existierenden Partitionen fällt.

Weitere Verbesserungen sind die Möglichkeit, Spalten mit Default-Wert zu Tabellen hinzuzufügen, ohne dass die Tabelle komplett neu geschrieben werden muss. Mit „Covering Indexes“ können mehr Index-Zugriffe als „Index Only Scan“ durchgeführt werden. Window-Funktionen unterstützen nun auch die RANGE und GROUPS-Schlüsselwörter.

Weitere Informationen gibt es im Release-Announcement des PostgreSQL-Projekts.

Nächste Woche findet in Lissabon die PGConf.EU statt. Wir von credativ sind mit einem Stand vertreten und feiern das PostgreSQL-Release unter anderem mit unserem „This Amp goes to 11„-T-Shirt.

Das Shirt ist bei uns kostenlos am Stand erhältlich. Wer nicht an der Konferenz teilnimmt, kann sich ein Shirt bestellen. Der Erlös wird an das PostgreSQL-Projekt gespendet.

PostgreSQL 11 Amp

In der aktuellen Ausgabe beschäftigt sich das Linux Magazin in einem sehr interessanten Artikel mit einer Frage, die derzeit in vielen Unternehmen intensiv diskutiert wird:

Betreibt man seine Datenbank auf der eigenen Hardware, oder verlagert man sie in die Cloud?

Um die einzelnen Vor- und Nachteile zu untersuchen, wurden Datenbanklösungen diverser Cloud-Anbieter mit einer lokalen PostgreSQL-Installation verglichen. Für die Tests auf der lokalen Installation wurde auch die von credativ entwickelte PostgreSQL Appliance „Elephant Shed“ verwendet.

Den vollständigen Artikel inklusive Testergebnissen findet man in der neuesten Ausgabe des Linux Magazins (10/18). Optional kann der Artikel auf der Seite des Linux-Magazins auch online erworben werden.

Über PostgreSQL

PostgreSQL ist eines der führenden Open-Source-Datenbanksysteme, mit einer weltweiten Community bestehend aus Tausenden von Nutzern und Mitwirkenden. Das PostgreSQL Projekt baut auf über 25 Jahre Erfahrung auf, beginnend an der University of California, Berkeley, und ist bald schon in der Version 11 verfügbar.

Für PostgreSQL bietet credativ umfangreiche Services und einen tiefgehenden Entwicklersupport an. Von der Konzeption, zur Datenbankmigration, Optimierungen und Tunings, bis hin zur Hochverfügbarkeit: Der gesamte PostgreSQL-Datenbank-Lifecycle wird durch credativ abgedeckt.

Um eine komfortablere und flexiblere Administration zu ermöglichen, hat credativ die Elephant Shed PostgreSQL Appliance entwickelt.

Über Elephant Shed

Elephant Shed ist eine vollständig freie, leistungsfähige Open Source-Lösung, die eine enorme Erleichterung für den Betrieb von PostgreSQL im Unternehmenseinsatz bietet.

Elephant Shed baut auf bewährten Komponenten auf, die ausschließlich unter anerkannten Open Source Lizenzen veröffentlicht werden – und bietet somit den vollständigen Softwarestack für die Administration, das Monitoring, die Erstellung von Backups, und vielem mehr für PostgreSQL-Datenbanken.

Weitere Informationen über PostgreSQL und Elephant Shed erhalten Sie auf der jeweiligen Projektseite.

Die zweite Beta-Version von PostgreSQL 11, welche sich nun im Feature-Freeze befindet, ist kürzlich erschienen. Zeit also, einige Verbesserungen vorzustellen, die credativ zu dieser Version im Bereich Checksummen und Basebackups beigetragen hat.

Checksummen-Überprüfung während Basebackups

Checksummen für den Tabellen und Indexen zugeordneten Dateien können seit Version 9.3 beim Anlegen einer PostgreSQL-Instanz verwendet werden und warnen bei Bitfehlern in einzelnen Daten-Pages. Sie ermöglichen damit die frühzeitige Erkennung von Storage-Problemen. Allerdings wurden diese bisher nur im Rahmen von Abfragen geprüft, wenn auf den entsprechenden Datensatz zugegriffen wurde. Eine explizite Prüfung ist erst ab Version 11 mit dem neuen pg_verify_checksums Tool möglich, welches allerdings nur bei ausgeschalteter Instanz funktioniert.

Unsere Änderung erlaubt nun Checksummen beim Erstellen eines Basebackups zu überprüfen. Da bei einem Basebackup naturgemäß alle Datenblöcke gelesen werden müssen, ist dies eine gute Gelegenheit deren Konsistenz zu überprüfen. Checksummen-Fehler werden dabei als Warnungen und nicht als Fehler geloggt, damit nicht das gesamte Basebackup abgebrochen werden muss. Dem normalerweise für die Erstellung von Basebackups bzw. Standby Clones verwendeten Programm pg_basebackup wurde eine Option --no-verify-checksums hinzugefügt, die optional diese Überprüfung abschaltet.

Replikations-Slots bei Basebackups

Die zweite Änderung betrifft die Behandlung von Replikations-Slots von pg_basebackup bei der Erstellung von Standby-Servern. Diese erlauben es einem Primary, die für den im Slot spezifizierten Standby benötigten Transaktionslogs vorzuhalten, auch wenn dieser temporär nicht verfügbar ist. Zwar konnte man pg_basebackup einen Replikations-Slot nennen, welcher dann während des Basebackups verwendet wird, allerdings musste dieser bereits vorher manuell angelegt worden sein, da es ansonsten zu einer Fehlermeldung kommt. Unsere Änderung erlaubt nun mit der neuen Option -C bzw. --create-slot in einem Kommando einen Standby-Clone inklusive Verwendung von Replikations-Slots zu erstellen:

$ pg_basebackup -v -h primary.lan -D data --slot=standby1 --create-slot --write-recovery-conf
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/1D000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "standby1"
pg_basebackup: write-ahead log end point: 0/1D0000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
$ cat data2/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/.pgpass'' host=primary.lan
  port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'standby1'

Danach muss nur noch der Standby gestartet werden und repliziert nun automatisch.

Darüberhinaus wurden einige weitere kleine Verbesserungen an pg_basebackup bzw. dessen Tests von uns umgesetzt.

Paralleler Dump nach /dev/null

Einen Patch, der es nicht in das Release schaffte, wollen wir aber dennoch vorstellen: Paralleler pg_dump nach /dev/null im Directory-Format. Grund hierfür ist die gebräuchliche Verwendung von pg_dump für die Überprüfung von PostgreSQL-Instanzen auf Fehler, wobei als Zielort /dev/null verwendet wird, sodass kein zusätzlicher Speicherplatz benötigt wird. Das Problem hierbei ist, dass /dev/null nur im Custom-Format verwendet werden kann, welches aber keine Parallelität erlaubt. Das Directory-Format erlaubt zwar mehrere Prozesse gleichzeitig zu verwenden, allerdings nicht in Verbindung mit /dev/null; letzteres wird durch unseren vorgeschlagenen Patch ermöglicht.

Die Begründung für die Ablehnung war nicht technischer Natur, sondern das pg_dump kein Diagnose-Werkzeug ist und deswegen keine spezielle Unterstützung hierfür eingebaut werden sollte. Nichtsdestotrotz funktioniert der vorgeschlagene Patch und wird auch bei Kunden von uns eingesetzt. Versionen des Patches für PostgreSQL 9.3, 9.4, 9.5, 9.6 und 10 sind verfügbar.

Elephant Shed bündelt und integriert bewährte Komponenten, die für das Management eines PostgreSQL-Servers benötigt werden. Dabei werden erprobte Tools für alle relevanten Bereiche bereits vorinstalliert und vorkonfiguriert. Die Mehrzahl dieser Tools kann über eine komfortable Weboberfläche gesteuert werden. Selbst gestandene PostgreSQL-Administratoren werden kaum einen Bereich finden, der von Elephant Shed nicht abgedeckt wird.

Neu in Version 1.2 ist die Unterstützung von Ubuntu Bionic (18.04), neben der bisherigen Unterstützung für Debian Stretch (9). Beide Distributionen sind für die Architekturen amd64 (x86_64) und ppc64el (IBM POWER) verfügbar.

Nach Rückmeldungen durch Benutzer wurde für die Version 1.2 das Grafana-Dashboard überarbeitet. Verschiedene PostgreSQL-Cluster pro Maschine werden nun durch den Cluster-Namen wie „10/main“ unterschieden. Alle Metrikpanels benutzen nun korrekte Einheiten, d.h. die Achsen sind nun mit „Bytes pro Sekunde“ oder „Operationen pro Minute“ beschriftet. Grafana wurde auf Version 5 aktualisiert.

Die Prometheus-Timeseries-Datenbank wurde auf Version 2 aktualisiert, mit besserer Performance und kompakterer Datenhaltung. Leider ist das Speicherformat nicht kompatibel, alte Monitoring-Daten werden nicht erhalten.

Bug-Fixes und Verbesserungen:

  • prometheus-sql-exporter: Die automatische Erkennung von PostgreSQL-Clustern und Datenbanken wurde neu geschrieben und ist nun stabiler
  • Prometheus nutzt nun den Host- und Cluster-Namen in Timeseries-Labels
  • prometheus-node-exporter wurde auf Version 0.15 aktualisiert
  • pgBadger: Handhabung von log_line_prefix korrigiert

Die aktualisierten Pakete stehen über packages.credativ.com zum Download bereit. Wer Elephant Shed bereits installiert hat, kann die Updates wie gewohnt über apt einspielen.

Elephant Shed ist ein Open-Source-Projekt, entwickelt und gepflegt von credativ. Für Elephant Shed bietet die credativ einen umfassenden technischen Support mit garantierten Service Level Agreements, der optional auch an 365 Tagen im Jahr rund um die Uhr zur Verfügung steht.

Unsere PostgreSQL-Appliance Elephant Shed bringt fertig konfiguriertes Datenbank- und System-Monitoring mit. Wir setzen dabei auf Grafana und Prometheus. Hier stellen wir die Grundlagen vor, auf denen diese Monitoring-Architektur basiert.

Im Vordergrund steht hier Performance-Monitoring, d.h. die Sammlung quantitativer Metriken über die Zeit, z.B. den Verlauf der Zahl der Transaktionen pro Sekunde. Qualitative Metriken wie „ist die Festplatte vollgelaufen“ und Alerting können von Grafana und Prometheus ebenfalls erfasst werden, sollen hier aber nicht behandelt werden.

Grafana Architecture

Prometheus-Exporter

Prometheus als Timeseries-Datenbank bildet die Zentrale des Monitorings. Metriken haben hier einen Namen und verschiedene Labels, z.B. sql_pg_stat_database mit Labels datname=“omdb“ und col=“xact_commit“.

Prometheus bezieht seine Daten von sogenannten Exportern, die periodisch (z.B. alle 30 s) abgefragt werden. Wohl in jeder Installation zu finden ist der Node-Exporter, der System-Daten wie Dateisystem-Füllstand, CPU-Auslastung und Speicherbelegung auswertet. Für die PostgreSQL-Überwachung setzen wir den SQL-Exporter in einer von uns gepatchten Version ein.

Im SQL-Exporter sind SQL-Abfragen konfiguriert, die in allen PostgreSQL-Clustern und -Datenbanken auf dem System ausgeführt werden. Dabei ist es üblich, immer absolute Werte abzufragen, z.B. Transaktionen seit Systemstart, und nicht schon vorzuverarbeiten. Ein Wert wie „Transaktionen pro Sekunde“ wird dann durch Prometheus aus den Rohdaten berechnet.

# SELECT datname, xact_commit, xact_rollback
  FROM pg_stat_database
  WHERE datname !~ 'template(0|1)'
  ORDER BY datname;
 
 datname  | xact_commit | xact_rollback
----------+-------------+---------------
 foo      |        8881 |             0
 omdb     |       29149 |         18926
 postgres |       43780 |          1754
(3 Zeilen)

Das Ergebnis stellt der Exporter dann per http auf localhost:9237 zur Verfügung:

sql_pg_stat_database{col="xact_commit", datname="omdb", host=":5432", sql_job="10/main", user="postgres"} 29149
sql_pg_stat_database{col="xact_rollback", datname="omdb", host=":5432", sql_job="10/main", user="postgres"} 18926

Prometheus-Abfrage

Im Webinterface von Prometheus können die gesammelten Daten dann abgefragt werden. Im einfachsten Fall besteht eine Prometheus-Query einfach aus dem Namen der Timeseries:

sql_pg_stat_database{col=~'xact_commit|xact_rollback',datname='omdb',host=':5432'}

Prometheus Graph

Die Graphik zeigt die steigende Zahl von durchgeführten Transaktionen. Um nun auf die Rate der Transaktionen pro Sekunde zu kommen, benutzt man die Funktion rate(), wobei über ein angegebenes Intervall gemittelt wird, hier 1 Minute:

rate(sql_pg_stat_database{col=~'xact_commit|xact_rollback',datname='omdb',host=':5432'}[1m])

Prometheus Rate Graph

Grafana

Grafana dient nun dazu, die von Prometheus gesammelten Daten hübsch formatiert in Dashboards anzuzeigen. Für jedes „Panel“ im Dashboard werden Prometheus-Queries hinterlegt und Achsenbeschriftungen und andere Grapheigenschaften konfiguriert. Dabei passiert das Rendering der Daten vollständig im Browser, Grafana stellt lediglich Umgebung zur Verfügung, der Browser bezieht dann die Timeseries-Werte direkt als JSON von Prometheus.

Grafana Overview

Elephant Shed

Das von uns für Elephant Shed entwickelte PostgreSQL-Dashboard kann natürlich auch für andere Setups genutzt werden. Alle benötigten Dateien finden sich im Git-Repository.

Vergangene Woche fand die deutschsprachige PostgreSQL-Konferenz am Müggelsee im Süd-Osten von Berlin statt. credativ war mit insgesamt neun Personen vertreten. Vier unserer Kollegen haben als Speaker zum Vortragsprogramm beigetragen. Auch in der Event-Organisation war credativ vertreten und hat mitgeholfen, die Konferenz zu stemmen und zu einem Erfolg zu machen. Aufgrund der langen Anfahrt sind wir schon am Vortag angereist und konnten uns am Freitag dann ganz auf die Konferenz und das Publikum am credativ-Stand konzentrieren.

PGConfDE 2018 Flagge

PGConfDE 2018 Messestand credativ

Das Vortragsprogramm war abwechslungsreich gestaltet. Den Auftakt machte Peer Heinlein mit der Keynote „Umdenken! 11 Gebote zum IT-Management“, in der er unter anderem die Diskrepanz im Investitionsverhalten zwischen Hardware und Personal ansprach. In der Praxis beobachtet er oft, dass für Hardware wesentlich mehr Geld ausgegeben wird, als für Personal. Zitate wie: „Nehmen wir gleich ein Terabyte RAM, dann haben wir was für die Zukunft!“ seien keine Seltenheit. Bei Schulungen und Stellen werde dagegen nicht selten gespart. Auch „zu komplexe“ hochverfügbare Systeme, bei denen der „nicht redundante“ Admin der Single Point of Failure ist, seinen die Regel. Viele seiner Beobachtungen können wir von credativ aus der Praxis bestätigen.

In seinem Vortrag „Modernes SQL: Wie PostgreSQL die Konkurrenz aussticht“ stellte Markus Winand einige SQL-Sprachkonstrukte vor, die zwar Teil des SQL-Standards sind (teilweise auch schon seit SQL:2003), trotzdem aber ausschließlich von PostgreSQL implementiert werden – aber nicht von der Konkurrenz. Beispiele sind FILTER, boolean-Aggregate und DOMAINs.

Während dessen hat Devrim Gündüz in seinem Talk „WAL for DBAs – Everything you want to know“ Details über Interna des PostgreSQL-Write Ahead Logs vorgestellt und ist darauf eingegangen, was Administratoren zu beachten haben.

Bruce Momjian stellte den aktuellen Stand von Sharding in PostgreSQL“ vor. Leider blieb im Vortrag nicht mehr viel Zeit um auf die Zukunft zu blicken, so dass hier lediglich die vergangene Entwicklung beleuchtet wurde.

Mit eigenen Vorträgen waren wir durch Alexander Sosna und Adrian Vondendriesch vertreten, die die Hintergründe unserer PostgreSQL-Appliance Elephant Shed vorstellten, sowie durch Christoph Berg, der vorstellte, wie man mit Prometheus und Grafana ein Monitoring für PostgreSQL einrichtet. Michael Meskes stellte in seiner Closing Session die Frage „Hat Open Source eine Zukunft?“ und mahnte, auf völlig offene Produkte zu setzen, die ohne Open Core und Vendor Lock-In auskommen.

Insgesamt war die Konferenz eine lohnende Erfahrung für uns, insbesondere die Live-Demo von Elephant Shed am credativ-Stand war ein guter Einstieg zu interessanten Gesprächen.

Die nächste PGConf.DE findet im Mai 2019 in Leipzig statt. Wir freuen uns jetzt schon!

PGConfDE 2018 Vortrag Michael Meskes

PGConfDE 2018 Speaker

In diesem Jahr ist es endlich wieder soweit – Die PGConf.DE öffnet am 13. April 2018 ihre Pforten!

Mit ca. 200 Besuchern ist die PGConf.DE die deutschlandweit größte PostgreSQL Veranstaltung, und findet in diesem Jahr in Berlin statt. Zuletzt wurde die PGConf.DE 2015 in Hamburg ausgetragen.

credativ ist natürlich wieder mit dabei – in diesem Jahr als Platin-Sponsor. Insgesamt halten unsere Kollegen drei Vorträge:

  • PostgreSQL-Monitoring mit Grafana und Prometheus – von Christoph Berg
    • „PostgreSQL liefert eine Vielzahl von Performance-Metriken. In diesem Vortrag zeigen wir, wie man diese Daten von PostgreSQL-Clustern mit Prometheus-Exportern abruft und als Graphen in Grafana darstellt. Die Präsentation erläutert die Monitoring-Grundlagen, und in einer Live-Demo werden neue Graphen in das Grafana-Dashboard eingefügt.“
  • PostgreSQL und alles was dazugehört – Elephant Shed – von Adrian Vondendriesch und Alexander Sosna
    • „PostgreSQL ist für viele Nutzer heute ein zentrales Element ihrer IT-Infrastruktur, oder auf dem Weg dahin. Für einen reibungslosen Betrieb wird jedoch eine entsprechende leistungsfähige Umgebung benötigt. Im Vortrag wird eine Stand-Alone-Datenbankserver-Umgebung vorgestellt, die für Entwicklung und Produktion verwendet werden kann und sowohl PostgreSQL-Neueinsteigern, -Umsteigern, als auch Veteranen das Leben möglichst einfach macht.“
      Elephant Shed
      ist eine von credativ entwickelte, leistungsfähige PostgreSQL Appliance, die vollständig als freie Open Source-Lösung zum Download zur Verfügung steht.
  • Hat Open Source eine Zukunft? – von Dr. Michael Meskes

Das vollständige Vortragsprogramm kann auf der Veranstaltungswebsite eingesehen werden.

Wir freuen uns auf Euren Besuch an unserem Stand und in den Vorträgen! Wenn Ihr noch nicht registriert seid, müsst Ihr schnell sein – Laut Veranstalter werden die Tickets bereits knapp.

Weitere Informationen erhaltet Ihr auf der Veranstaltungswebsite PGConf.DE

Neben den Vortragenden beschäftigt die credativ viele Mitglieder der PostgreSQL-Community. Einige Mitarbeiter waren schon vor Firmengründung an dem Projekt beteiligt und leisten auch heute noch einen wichtigen Beitrag für die Software. Auch daraus entstand für die credativ eine enge und langjährige Verbundenheit mit dem PostgreSQL-Projekt und der Community.