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.
How many times did you try to solve a performance issue but have not been able to reproduce the explain plan? Whatever you tried you always got a different result. Lets say you managed to get a dump of the database in question, got all the PostreSQL parameters the same, gathered statistics but still you do not manage to get…
The concept of replication slots was introduced in PostgreSQL 9.4 and was created to prevent a primary instance to delete WAL that a replica still needs to apply. That could happen when you have a network interruption or the replica was down for another reason. With replication slots you can prevent that at the downside that your master could fill…
PostgreSQL 12 will finally bring a feature other database systems already have for quite some time: Generated columns. What exactly is that and how does is look like in PostgreSQL? As usual, lets start with a simple test setup. We begin with a simple table containing two columns: postgres=# create table t1 ( a int, b text ); CREATE TABLE…
PostgreSQL 9.6 introduced a new view called pg_stat_progress_vacuum which gives information about currently running vacuum processes. That was a great addition because since then you could easily estimate on how long a specific vacuum process will need to complete. PostgreSQL 12 will use the same infrastructure to extend that to more operations. The first operation that can now be tracked…
This is another post dedicated to EnterpriseDB Postgres. Sometimes you may want to get specific messages not getting logged to the server’s logfile or audit records. That might be specific error codes or, even more important, passwords you specify when you create users. EDB comes with a solution for that by providing an extension which is called EDB Filter Log.…
It might be that there is a requirement to audit operations in the database. Maybe because of legal requirements, maybe because of security requirements or whatever. I’ve already written a post in the past describing what you can do in community PostgreSQL, this post is specific to EDB Postgres. The auditting features come be default in EDB Postgres and you…
PostgreSQL supports many authentication methods by default and one of them is Ident authentication. Using that method you can use the users defined in the operating system and map them to users in PostgreSQL. So how does that work? To start, lets create a new operating system user we want to use for connecting to the database: postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo…
As I discussed in some of my recent talks at conferences (at the DOAG for example), MySQL 8 came out with new features which bring lots of improvements in terms of security. “At-Rest” encryption has been existing from some releases by now: – InnoDB Tablespace Encryption: by 5.7.11 – Redo and Undo Log Data Encryption: by 8.0.1 Now starting from…
PostgreSQL comes with various helper functions that support you with working with files on the filesystem on the host PostgreSQL is running on. You might ask yourself why that is important but there are use cases for that. Maybe you want to list the contents of a directory because new files that showed up since the last check do trigger…
I have already written a post about prepared statements in PostgreSQL some time ago. What I did not mention in that post is the concept of generic and custom plans. So lets have a look at those. As always, we start with creating a demo table and populate that table with some sample data: pgbench=# create table demo ( a…
Recently a commit landed in the PostgreSQL development tree that made me aware of something I did not know so far: When you start PostgreSQL with a script using pg_ctl and that script was cancelled the postmaster was killed as well before PostgreSQL 12. Sounds weird? Lets do a little demo. The little demo script is quite simple: Print the…
Today (actually right now) we are a proud sponsor of the Red Hat Forum Switzerland 2019 in Geneva. With around 300 people attending this is quite a huge event and we had a lot of interesting discussions with other partners as well as existing customers or just people we met and didn’t know before. First of all we focused on…
