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.
A recent tweet from Kevin Closson outlined that in PostgreSQL it might be confusing if something is an index or table. Why is it like that? Lets have a look and start be re-building the example from Kevin: For getting into the same situation Kevin described we need something like this: postgres=# create table base4(custid int, custname varchar(50)); CREATE TABLE…
A recent tweet of mine resulted in Martin Widlake to write a really great blog post about What makes a community. Please read it before you continue to read this. There was another response from Stefan Koehler which is worth mentioning as well. Both, Martin and Stefan, speak about Oracle communities because this is were they are involved in. At…
It has been quite a while since the last post in this series but today comes the next one. Being at a customer this morning this question popped up: Can we have instead of triggers on a view in PostgreSQL as well? I couln’d immediately answer (although I was quite sure you can) so here is the test. I took…
Some days ago EDB Postgres Advanced Server 10 was released and one feature which might be handy is auto pre-warming. What this does is to save all the buffers (or better a description of the buffers) which are currently loaded in to shared_buffers to disk and then re-read the buffers automatically when the instance is restarted. Lets see how it…
Did you ever wonder what exactly is in a PostgreSQL data file? Usually you don’t care, I agree. But there might be situations where knowing how you can do this might be a great help. Maybe your file is corrupted and you want to recover as much data as possible? Maybe you just want to do some research. There is…
When you start using PostgreSQL 10 logical replication you might think it is a good idea to setup bi-directional replication so you end up with two or more masters that are all writable. I will not go into the details of multi master replication here (conflict resolution, …) but will show what happens when you try to do that. Lets…
Another interesting topic that popped up last week during pgconfeu: Are large objects supported with logical replication in PostgreSQL 10? The only truth is a test, isn’t it? Lets go… Obviously we need a table containing same large objects to start with: postgres=# create table t1 ( a int, b oid); CREATE TABLE Before inserting some data lets create a…
While doing the EDB quiz at their booth last week at pgconfeu one of the questions was: Are temporary tables auto vacuumed? What do you think? My first thought was yes, but lets see. The first question we need to answer is: How can we check if a table (no matter if temporary or not for now) was auto vacuumed…
What I really don’t like is this: Someone wants me to work on a Linux system for whatever purpose and then I get messages like this: postgres@pgbox:/home/postgres/ [pg960final] cat /proc/sysrq-trigger cat: /proc/sysrq-trigger: Permission non accordée or: postgres@pgbox:/home/postgres/ [pg960final] cat /proc/sysrq-trigger cat: /proc/sysrq-trigger: Permiso denegado It is not that I don’t like French or Spanish but usually (well, not in that…
Preparing the storage devices for use with ASM is one of the first tasks you should do. When you do not want to use ASMLib then udev is what you should use for this. As the order/naming of the devices in the device tree is not guaranteed (e.g. dm-1 is not guaranteed to be the same device after a reboot…
SLES 12 SP3 was released some time ago and as we have customers running on that I thought it might be a good idea to test the upgrade from SP2 to SP3. Actually it turned out this is quite easy and can be done online. The supported methods for doing this is either by using YaST or Zypper directly. As…
