Yesterday, the fourth beta of the upcoming PostgreSQL^®-major version 12 was released.

Compared to its predecessor PostgreSQL^® 11, there are many new features:

• Performance improvements for indexes: btree indexes now manage space more efficiently. The REINDEX command now also supports CONCURRENTLY, which was previously only possible with new indexes.
• WITH queries are now embedded in the main query and thus optimized much better by the planner. Previously, WITH queries were always executed independently.
• The native partitioning was further improved. Foreign keys can now also reference partitioned tables. Maintenance commands such as ATTACH PARTITION no longer require an exclusive table lock.
• The support of page checksums and the tool pg_checksums was further improved, also with substantial cooperation by credativ.
• It is now possible to integrate additional storage engines. The “zheap”, which is still under development, will be based on this, which promises more compact data storage with less bloat.

Of course, PostgreSQL^® 12 will be tested using sqlsmith, the SQL “fuzzer” from our colleague Andreas Seltenreich. Numerous bugs in different PostgreSQL^® versions were found with sqlsmith by using randomly generated SQL queries.

Debian and Ubuntu packages for PostgreSQL^® 12 are going to be published on apt.postgresql.org with credativ’s help. This work will be handled by our colleague Christoph Berg.

The release of PostgreSQL^® 12 is expected in the next weeks.

#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
 — -

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
---

git clone https://github.com/zalando/postgres-operator.git
cd postgres-operator

nano manifests/minimal-postgres-manifest.yaml

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"

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

kubectl create namespace test
kubectl config set-context $(kubectl config current-context) — namespace=test

kubectl create -f manifests/minimal-postgres-manifest.yaml

$ 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

sudo apt-get install postgresql-client

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

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)’

Patroni is a PostgreSQL high availability solution with a focus on containers and Kubernetes. Until recently, the available Debian packages had to be configured manually and did not integrate well with the rest of the distribution. For the upcoming Debian 10 “Buster” release, the Patroni packages have been integrated into Debian’s standard PostgreSQL framework by credativ. They now allow for an easy setup of Patroni clusters on Debian or Ubuntu.

Patroni employs a “Distributed Consensus Store” (DCS) like Etcd, Consul or Zookeeper in order to reliably run a leader election and orchestrate automatic failover. It further allows for scheduled switchovers and easy cluster-wide changes to the configuration. Finally, it provides a REST interface that can be used together with HAProxy in order to build a load balancing solution. Due to these advantages Patroni has gradually replaced Pacemaker as the go-to open-source project for PostgreSQL high availability.

However, many of our customers run PostgreSQL on Debian or Ubuntu systems and so far Patroni did not integrate well into those. For example, it does not use the postgresql-common framework and its instances were not displayed in pg_lsclusters output as usual.

Integration into Debian

In a collaboration with Patroni lead developer Alexander Kukushkin from Zalando the Debian Patroni package has been integrated into the postgresql-common framework to a large extent over the last months. This was due to changes both in Patroni itself as well as additional programs in the Debian package. The current Version 1.5.5 of Patroni contains all these changes and is now available in Debian “Buster” (testing) in order to setup Patroni clusters.

The packages are also available on apt.postgresql.org and thus installable on Debian 9 “Stretch” and Ubuntu 18.04 “Bionic Beaver” LTS for any PostgreSQL version from 9.4 to 11.

The most important part of the integration is the automatic generation of a suitable Patroni configuration with the pg_createconfig_patroni command. It is run similar to pg_createcluster with the desired PostgreSQL major version and the instance name as parameters:

pg_createconfig_patroni 11 test

This invocation creates a file /etc/patroni/11-test.yml, using the DCS configuration from /etc/patroni/dcs.yml which has to be adjusted according to the local setup. The rest of the configuration is taken from the template /etc/patroni/config.yml.in which is usable in itself but can be customized by the user according to their needs. Afterwards the Patroni instance is started via systemd similar to regular PostgreSQL instances:

systemctl start patroni@11-test

A simple 3-node Patroni cluster can be created and started with the following few commands, where the nodes pg1, pg2 and pg3 are considered to be hostnames and the local file dcs.yml contains the DCS configuration:

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

Afterwards, you can get the state of the Patroni cluster via

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 |
+---------+--------+------------+--------+---------+----+-----------+

Leader election has happened and pg1 has become the primary. It created its instance with the Debian-specific pg_createcluster_patroni program that runs pg_createcluster in the background. Then the two other nodes clone from the leader using the pg_clonecluster_patroni program which sets up an instance using pg_createcluster and then runs pg_basebackup from the primary. After that, all nodes are up and 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 |
+---------+--------+------------+--------+---------+----+-----------+

The well-known Debian postgresql-common commands work as well:

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 Behaviour

If the primary is abruptly shutdown, its leader token will expire after a while and Patroni will eventually initiate failover and a new leader election:

+---------+--------+-----------+------+---------+----+-----------+
| 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 |
+---------+--------+-----------+--------+---------+----+-----------+

The old primary will rejoin the cluster as standby once it is restarted:

+---------+--------+------------+--------+---------+----+-----------+
| 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 |
+---------+--------+------------+--------+---------+----+-----------+

If a clean rejoin is not possible due to additional transactions on the old timeline the old primary gets re-cloned from the current leader. In case the data is too large for a quick re-clone, pg_rewind can be used. In this case a password needs to be set for the postgres user and regular database connections (as opposed to replication connections) need to be allowed between the cluster nodes.

Creation of additional Instances

It is also possible to create further clusters with pg_createconfig_patroni, one can either assign a PostgreSQL port explicitly via the --port option, or let pg_createconfig_patroni assign the next free port as is known from pg_createcluster:

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

In order to easily deploy a 3-node Patroni cluster we have created an Ansible playbook on Github. It automates the installation and configuration of PostgreSQL and Patroni on the three nodes, as well as the DCS server on a fourth node.

Questions and Help

Do you have any questions or need help? Feel free to write to info@credativ.com.

This week version 1.3 of our PostgreSQL^® appliance Elephant Shed was released.

The highlight of the new version is support for Red Hat Enterprise Linux 7 and CentOS 7. As is already the case for Debian, the appliance heavily relies on the postgresql-common infrastructure which was previously ported to RPM.
The well-known PostgreSQL^® RPM packages from yum.postgresql.org are integrated into the system via pg_createcluster and can be administrated from the Elephant Shed web interface.

All other Elephant Shed components like pgAdmin4, Grafana, Prometheus, pgbackrest, Cockpit or shellinabox work in the same way as in the Debian version of the appliance. Only the SELinux functionality has to be deactivated in order to run pgAdmin4 and shellinabox as their packages do not support this.

Besides the port to RPM the appliance infrastructure was updated. The Prometheus node-exporter is now available in version 0.16 in which many metric names were adjusted to the Prometheus naming scheme. The Grafana dashboard was updated accordingly. The Apache configuration was switched from authnz_pam to authnz_external as the former is not available on CentOS and stable functionality could not longer be guaranteed on Debian Buster.

The next items on the Elephant-Shed roadmap are the integration of the REST-API in order to control particular components, as well as multi-host support so that several Elephant-Shed instances can be controlled simultaneously.
An overhaul of the user interface is planned as well.

The updated packages are available for download at packages.credativ.com. If Elephant-Shed was installed already, the updates are provided via apt as usual.

The open-source PostgreSQL^® appliance Elephant-Shed is developed by credativ and is increasingly popular, as the most important compontents for the administration of PostgreSQL^® servers are already included. Adjustments and extensions can be done at any time.

Comprehensible technical support for Elephant-Shed is offered by credativ including guaranteed service-level agreements and optional 365 days and 24/7 hours.

Today PostgreSQL^® version 11 was released. The new release brings improvements in many areas.

Since version 9.6 query plans can be executed on multiple CPU cores in parallel, this is now supported for other plan types, especially the creation of B-tree indexes. Sequential scans and UNION queries have also been improved.

Brand new is the possibility to optimize queries via Just-in-Time Compilation (JIT). When PostgreSQL^® is compiled, the source code is stored as LLVM bit code. When executing a query whose planner cost exceeds a limit, libllvm then translates this bit code into native machine code specifically for that query. Since all used data types etc. are known in advance, the machine code eliminates all case distinctions that are generally necessary. The feature is disabled by default and can be enabled with “SET jit = on;”. In PostgreSQL^® 12 it should then be active by default.

Until now only functions could be defined on SQL level. New are now procedures which can manage BEGIN/COMMIT independently. Batch operations can now be completely transferred to the database side.

Table Partitioning section has been further improved to support hash partitioning. Integration with partitions using postgres_fdw has also been improved. It is now possible to create a default partition that holds data that does not fall into any of the existing partitions.

This Amp goes to 11!

Other improvements include the ability to add columns with default values to tables without having to rewrite the table completely. “Covering Indexes” allows more index accesses than “Index Only Scan”. Window functions now also support the RANGE and GROUPS keywords.

More information can be found in the release announcement of the PostgreSQL^® project.

Next week the PGConf.EU will take place in Lisbon. We from credativ are present with a booth and celebrate the PostgreSQL^® release with our “This Amp goes to 11” T-shirt.

The shirt is available for free at our booth. If you don’t attend the conference, you can order a shirt. The profits will be donated to the PostgreSQL^® project.

From mid to late June credativ attended 3 consecutive conferences in Asia. The Open Source Summit Japan, the LC3 China and the PG Open China.

Open Source Summit Japan

As in the previous year, the Open Source Summit Japan was held again at the Ariake Conference Center on Odaiba, in the Bay of Tokyo. In order to cover the increasing number of participants, the event area was extended by several rooms. Accordingly, there was plenty of space for everyone.

In addition to a large number of technical presentations, mainly on containers, cloud and applications for automotive, there were also business and strategy orientated talks. All visitors could find topics of interest for themselves.

Specifically the presentation “Is There an Open Source Business Model: YES or NO?” by Jeffrey Borek (IBM) and Stephen Walli (Microsoft), deserves special mention, as it was the starting point for extensive discussions that day. Jeffrey and Stephen each gave their views and then gave the floor to the attendees to mix ask questions or give comments. The title being worded as it is doesn’t really explain the topic in detail. What the talk really was about is the question if a Open Source business model exists for large software companies.

Further to be emphasized is the presentation by our CEO Dr. Michael Meskes, whose question ”Is There a Future for Open SourceLC3 China

LC3 China (LinuxCon + ContainerCon + CloudOpen) was held in Beijing at the China National Convention Center. In the immediate vicinity of the Olympic Green Olympia Park, which was built for the 2008 Olympic Games.

Most of the presentations were held in English, although the vast majority of those attending were from China. A very welcoming gesture that definitely contributed to the international character. A large number of lectures with technical and business topics were held over the 4 days. Particularly interesting were the so called “office hours” as a separate position on the schedule. For this, 3 tables were provided at which discussions within the community could take place.

On the whole, LC3 seemed to be a platform for China’s largest IT companies, but it also showed that hacker groups and student groups have their own place in the community. We were certainly very happy to be able to participate and are looking forward to the next year.

PG Open China

Postgres’ growing success and the well-attended PostgreSQL^® conferences, which have been held in China since 2015, led to the first PG Open China being held at the Hotel Nikko New Century Beijing this year.

The aim of this conference was to provide a starting point for the Chinese PostgreSQL community and strenghthen its connection to the global PostgreSQL community

. For or this purpose, an international committee was established consisting of Bruce Momjian, Joshua D. Drake, Oleg Bartunov, Ray Feng and Dr. Michael Meskes.

After introducing this committee in a keynote, the event rooms were used for presentations and talks from the Chinese community. Joshua D. Drake and Oleg Bartunov also held a presentation. All lectures were translated with the help of an interpreter, so that all those present with less understanding of the English language nuances could comprehend everything.

The number of visitors of the PG Open China fortunately exceeded expectations and as such that the growth potential can clearly be seen. Especially with the focus on connection to the international community, the conference will certainly grow significantly over the next few years. It is therefore highly probable that we will be back on site next year. We were very happy to be one of the fist to participate in the PG Open China.

$ 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'

The credativ PostgreSQL^® Competence Center has released a project roadmap for the PostgreSQL^® Appliance Elephant Shed.

Elephant Shed is a freely available PostgreSQL^® solution developed by credativ, which combines management, monitoring and administration into one system.

The project roadmap for 2018 includes following points:

• Q2 2018: Support for Ubuntu 18.04
• Q3 2018: Support for CentOS 7

An additional planned feature is the implementation of REST API to control individual components. REST stands for REpresentational State Transfer and is an application programming interface based on the behavior of the World Wide Web. Specifically the PostgreSQL^® database and the backup via pgBackRest should be addressed.

Multi host support is also planned. A central control of several Elephant Shed instances is thus possible.

In order to make Elephant Shed even more user friendly, various configuration parameters of the web interface are going to be adjusted.

The project roadmap is of course also constantly being worked on. On GitHub you can leave us your feedback at any time.

We would like to take this opportunity to thank all users and testers, and look forward to further development of the project!

For further information please visit elephant-shed.io and GitHub.

This article was originally written by Philip Haas

Elephant Shed is now available as a Vagrant box. This makes it very easy to test and try out the PostgreSQL^® appliance.

Vagrant is an Open Source tool for creating portable virtual software environments. By being fully script controlled Vagrant makes it easy to generate virtual machines, in which a software component is installed for testing purposes. Vagrant itself is only the manager, whereas various backends such as Virtualbox or cloud providers can be used for the actual virtualization.

For the development of Elephant Shed we have relied on Vagrant from the very beginning. This “box” is now also available in the Vagrant Cloud.

To use this box Vagrant and VirtualBox must be installed. The host operating system hereby is irrelevant (Linux/MacOS/Windows), but inside the box runs Debian Stretch. The box is then automatically downloaded.

vagrant init credativ/elephant-shed
vagrant up

This creates a virtual machine where Elephant Shed runs in a VirtualBox on your computer.

• Default-User: admin
• Default-Passwort: admin
• The web interface listens to port 4433:https://localhost:4433
• PostgreSQL^® listens to port 55432: psql -h localhost -p 55432 -U admin

We are looking forward to your feedback!

Further information can be found on our Elephant Shed project page and on Github as well.

The credativ PostgreSQL^® Appliance is now available for download as a completely free Open Source solution. The new appliance called ‘Elephant Shed PostgreSQL^® Appliance‘ offers a tremendous ease of use for enterprise PostgreSQL^® operations. The long-term maintenance of the appliance will be handled by the PostgreSQL^® experts of credativ. Prepared images for the major cloud and virtualization platforms are already in work and will be released soon for VMWare, Virtualbox, Vagrant and for the cloud platforms Microsoft Azure, Amazon Web Services and Google Cloud Platform.

Elephant Shed builds on proven components, which are published exclusively under recognized Open Source licenses. These tools are an effective support for the management of a PostgreSQL^® server. All components used are pre-installed and integrated into the integral automation system. The majority of these tools can be used via a comfortable web interface.

Service and Support

For Elephant Shed, credativ offers comprehensive technical support with guaranteed service level agreements, which is also available 24 hours a day, 365 days a year. Support during installation and integration, as well as an introduction to Elephant Shed is of course also part of credativ’s services.

Please do not hesitate to contact us if you have any questions about Elephant Shed and our service and support services.

For more information, please visit our Elephant Shed- project page and Github.

This article was originally written by Philip Haas.