Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.
PostgreSQL 12 will give you more control on resetting statistics gathered by pg_stat_statements. When you check the documentation for PostgreSQL 11 (as linked in the previous sentence) you will see that the function has the following signature: pg_stat_statements_reset() returns void This means your only choice is to reset all the statistics. Today this commit landed and this will give you…
Automation is key today, nobody wants to do the same tasks over and over and again. Cloud without automation is not even possible. There are several tools around that help with automation and one of the most popular is Ansible. We already have several posts about Ansible on our blog platform but this one will be rather long. Setting up…
Getting data out of PostgreSQL in csv format is not a big issue. Using copy you can do that easily. PostgreSQL 12 will enhance psql so that you can directly return csv formatted output from a query. Using “copy” you would do something like this: postgres=# copy pg_database to ‘/var/tmp/aa.txt’ with (format csv); COPY 3 postgres=# ! cat /var/tmp/aa.txt 11788,postgres,10,6,en_US.utf8,en_US.utf8,f,t,-1,11787,598,1,1663,…
A common way to identify long running queries in PostgreSQL is to set log_min_duration_statement to a value that is known to cause troubles. In other words: If you know most of your statements usually execute in under a second you could set log_min_duration_statement to “2s” (which means two seconds) so that whenever a statement takes longer than two seconds it…
Traditionally everything which is related to recovery in PostgreSQL goes to recovery.conf. This is not only true for recovery settings but also for turning an instance into a replica which follows a master. Some days ago this commit landed in the PostgreSQL git repository. What that effectively means is, that there will be no more recovery.conf starting with PostgreSQL 12.…
After the minimal setup of the DC/OS in my further articles, I wanted to extend my DC/OS and add a loadbalancer. There are two options for loadbalancing in DC/OS 1. Marathon-LB (a layer 7 load balancer, used for external requests, based on HAProxy) 2. Named VIPs (a layer 4 load balancer used for internal TCP traffic) In this article we…
Some days ago EnterpriseDB released the latest version of its backup and recovery tool for PostgreSQL and EDB Postgres Advanced Server, release notes here. The main new features, at least for me, are backups using multiple cores and parallel incremental restores. Besides that BART does not require pg_basebackup anymore for taking full backups of a PostgreSQL instance. The downside with…
PostgreSQL supports index only scans since version 9.2 which was released in September 2013. The purpose of an index only scan is to fetch all the required values entirely from the index without visiting the table (the heap) at all. Of course that can speed up a query because avoiding to touch the heap, means reading less data and reading…
Hervé already did some tests with zheap and documented his results yesterday. After some more discussions with Amit who did the session about zHeap at the conference here in Lisbon (you can find the slides here). I thought it might be a good idea to do some more testing on that probably upcoming feature. Lets go. If you want to…
The new release 8.0.13 for MySQL is available since last week. Concerning security, this comes with a new feature already announced: the Password Verification Policy. Let’s have a look… This aim of this feature is to secure the attempts to change a password by specifying the old one to be replaced. It is turned off by default: mysql> show variables…
Finally, after all the configuration stuff is done, we can run the playbooks Create SSH Access First the SSH Access on all nodes need to be created. Therefore the access-onprem.yml is used: Be careful, I used CentOS on my system, so I commented the apt-get and the debian-based part out. If you want to run the playbook on another operating…
To start into this topic I want to shortly explain some basics. Afterwards I show you how to prepare the configuration files. Ansible Ansible is a Open Source automation utility. It is used for orchestration and configuration as well as the administration of PCs/Servers. You could say, okay but we have puppet or saltstack or an other framework, why should…
