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.
From time to time you might require to reboot your AWS instances. Maye you applied some patches or for whatever reason. Rebooting an AWS instance can be done in several ways: You can of course do that directly from the AWS console. You can use the AWS command line utilities as well. If you want to schedule a reboot you…
One of the new features in PostgreSQL 10 was the introduction of stronger password authentication based on SCRAM-SHA-256. How can you migrate your existing users that currently use md5 authentication to the new method without any interruption? Actually that is quite easy, as you will see in a few moments, but there is one important point to consider: Not every…
Usually you should use the data type that best fits the representation of your data in a relational database. But how many times did you see applications that store dates or numbers as text or dates as integers? This is not so uncommon as you might think and fixing that could be quite a challenge as you need to cast…
When you reference/call functions in PostgreSQL the optimizer does not really know much about the cost nor the amount of rows that a function returns. This is not really surprising as it is hard to predict what the functions is doing and how many rows will be returned for a given set of input parameters. What you might not know…
During one of the sessions from the last Swiss PGDay there was a question which could not be answered during the talk: Is it possible to modify pg_hba.conf from inside PostgreSQL without having access to the operating system? What everybody agreed on is, that there currently is no build-in function for doing this. When you are on a recent version…
So, finally, PostgreSQL on the beach is over. During discussions it was mentioned that some companies had issues to justify sending people to a conference to Ibiza. It was not really clear if they wanted to avoid the impression of making holidays while going to a conference or if it was just about the location. I am not judging this…
A couple of weeks ago, I found in my inbox an invitation from Red Hat concerning an event organized in Bern about Ansible. I heard a lot about Automation but did not really know what can or could be automated. Infrastructure? Application deployment? Networks? Containers? Cloud? All of them? So I wanted to learn more about Ansible and decided to…
We are slowly coming to the end of this little series about partitioning in PostgreSQL. In the last post we had a look at indexing and constraints and today we will have a look at sub partitioning. Sub partitioning means you go one step further and partition the partitions as well. Although it is not required to read all the…
dbi services is now SUSE Solution Partner. What better way to celebrate that than to attend an event? Last week I was at the SUSE Expert Days 2019 which took place at the Mövenpick Hotel in Lausanne. Keynote After a welcome coffee, keynote speech started with Mauro Beglieri (Global and Enterprise Account Manager). The open-source infrastructure and application delivery vendor…
Yesterday we talked about attaching and detaching of partitions. Today we will look at indexing and constraints when it comes to partitioned tables. If you missed the last posts, again, here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning PostgreSQL partitioning (4) : Hash partitioning PostgreSQL partitioning (5): Partition…
Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning…
This is the next post in the PostgreSQL partitioning series. If you missed the previous ones here they are: PostgreSQL partitioning (1): Preparing the data set PostgreSQL partitioning (2): Range partitioning PostgreSQL partitioning (3): List partitioning PostgreSQL partitioning (4): Hash partitioning This time we will have a look at partition pruning. Never heard of that? Don’t worry, hopefully it will…
