Recent content on PostgreSQL DBA
In the previous blog post we have seen how plenty of ram doesn’t necessary results in a faster instance. This time we’ll quickly see how PostgreSQL and Linux relate to each other in particular if we want to run our RDBMS at scale. Then we’ll have a look to the virtual memory in Linux and how to make it more friendly to PostgreSQL. Let’s dig in.
Nowadays it’s not uncommon to deal with machines with hundreds of GB of RAM. Abundant memory can give PostgreSQL a massive performance boost. However things work slightly different than you may expect. Let’s find out!
Finally I found time to get a grip on the issues I had with gohugo and get my blog operational again. I’m taking the occasion to write about a project I care a lot as it’s strictly related with my hometown, the upcoming PGDay Napoli.
Again there is another pretty long hiatus. Thing happens and independently from how bad my childhood was, family comes first. Anyway after a rollercoaster ride that started in July 2021 things are getting more stable.
For obvious reason the FOSDEM this year is an online event. The staff is building from scratch an infrastructure in order to deliver the speaker’s videos in a virtual environment. The catch is that all the talks must be pre recorded and uploaded via pentabarf, the software historically used by FOSDEM to manage the talk submissions. What follows is my experience in recording,uploading and…
When in a pl/pgsql function there is an exception then the function stops the execution and returns an error. When this happens all the changes made are rolled back. It’s always possible to manage the error at application level, however there are some cases where managing the exception inside the function it may be a sensible choice. And pl/pgsql have a nice way to do that. The EXCEPTION…
With PostgreSQL 12 the generated columns are now supported natively. Until the version Postgresql 11 it were possible to have generated columns using a trigger. In this post we’ll see how to configure a generated column via trigger and natively then we’ll compare the performances of both strategies.
The transactional model has been in PostgreSQL since the early versions. In PostgreSQL its implementation follows the guidelines of the SQL standard with some notable exceptions. When designing an application it’s important to understand how the concurrent access to data happens in order to avoid unexpected results or even errors.
Has been a long time since I wrote on my blog. The paths that life decides for you are strange. Often what you planned is completely scrambled by something bigger and completely unforeseen. Back in 2018 I planned to relocate to France but the move went quite wrong for reasons that only now I can see clearly. Anyway, finally after 10 months wandering without a real home I’m finally settled…
In the previous post we modified the apt role for controlling the setup in a declarative way. Then we added a ssh role for configuring the three devuan servers. The role is used to configure the server’s postgres process owner for ssh passwordless connection. In this tutorial we’ll complete the setup for the postgres user and then we’ll configure the database clusters with a new…
Today is two years since I started working on pg_chameleon. Back in 2016 this commit changed the project’s license from GPL v2 to the 2 clause BSD and the project’s scope, which became a MySQL to PostgreSQL replica system. Since then I learned a lot of lessons, made several mistakes and worked out solutions which resulted in a decent tool for bridging two different universes. Writing a…
In the previous post we configured three devuan servers from scratch using ansible adding the pgdg repository to the apt sources and installing the PostgreSQL binaries. This tutorial will revisit the apt role’s configuration and will introduce a new role for configuring the postgres operating system’s user for passwordless ssh connections to each other server.
The ansible is a fictional device invented by Ursula K. Le Guin . The ansible allows fast communication between two points across the space, regardless of the distance. It doesn’t surprise that the Red Hat’s ansible shares the same name as it gives a simple and efficient way to manage servers and automate tasks, yet remaining very lightweight with minimal requirements on the target…
In the previous post we introduced the PostgreSQL’s dependency system. At first sight the implementation can look like a maze where the succession of relationships are not clear. This post will try to give a practical example to show how pg_depend can act like an Ariadne’s thread in order to resolve the dependencies. The scenario presented is very simple but can be used as a starting…
The 20th of April I was supposed to give the pgchameleon’s talk at the Pycon 9. However personal issues prevented me to come to Florence. This post is an explanation and an apology for the pycon 9 attendees and organisers.
Database administrators usually work behind the scenes, over the weekends and in antisocial hours. They strive to look after the database which is at the core of any business with little or no recognition. The sad truth is that the DBA is one of the most obscure and one of the less appreciated roles within the IT organisations.
One of the things that distinguish PostgreSQL from other database systems is the way the dependencies between database objects are managed. PostgreSQL do not allow any invalid object like views or functions. Therefore any action that can invalidate dependent objects results in an error or it’s propagated to the dependent objects. The mechanism could be confusing at first sight. However the…
When in February I received the email with the news that my talk was accepted at the pgcon it took me some time, and several espresso coffee, to realise that I wasn’t dreaming. The pgcon email reached me few days after the same talk was accepted at the pycon nove, the Italy’s nationwide Python conference. The Italian Pycon and the Pgcon are great conferences with amazing speakers and…
Before the release of pg_chameleon 2.0 I had to write an upgrade procedure to allow a smooth migration from the version 1.8. An interesting challenge I faced was to determine the maximum and minimum position for the MySQL’s binary logs belonging to the same server but stored with different values in multiple sources. The user defined aggregates , a feature pretty unique to PostgreSQL,…
Back in the days of when I was an Oracle DBA I had to solve a strange behaviour on an Oracle installation. The system for some reasons stopped accepting the writes occasionally and without an apparent reason. This behaviour appeared odd to anybody except for my team, which addressed the issue immediately. The problem was caused by a not optimal configuration on the Oracle instance. This thought…
Few years ago I went trough a massive frustration. My team aligned with the rest of the company in using the Agile methodology. Initially the team decided to use Scrum then it switched to a sort of kanban. However even both approaches failed miserably dropping the team in utter chaos. I remember the daily standups. It was like playing a stage role, people stated the tasks for the day knowing that…
An ancient italian proverb says Il backup è quella cosa che andava fatta prima. A rough translation could be something like this: the backup is the thing that had to be done before. The concept of backup is quite confusing. Some people wrongly believe that executing a local backup is sufficient to ensure the recover of the data. In the real world a valid backup is present only if three conditions…
pgbackrest is an amazing tool that saves entire PostgreSQL clusters with ease. In this post we’ll go trough the configuration of the backup using a remote backup server. For the extensive documentation and detailed instructions please refer to the user guide. operating system configuration For our example we’ll use two virtual machines on virtualbox with devuan 1.0 Jessie installed.…
Five years ago I decided to start a blog on the topic I like the most, PostgreSQL. After evaluating the different platforms I decided to use blogger, as I needed something very basic. Over these years I discovered how blogger is limited, in particular when writing documents containing source code. For example, book’s chapters are pretty rubbish, despite the efforts I made to write them. In…
When I started writing pg_chameleon I had in mind a tool making simple to migrate from different dbms engines to PostgreSQL. The name was inspired by the Culture Club’s Karma chameleon song which suggested me the idea of a tool capable to move data between different database technologies. Then tool’s scope changed becoming a replica system rather a migration system but I’ve kept…
Has been a while since I wrote a post on this blog. I’ve been quite busy on coding pgchameleon’s version 2. I finally managed to release an alpha1 the 11th of November but this release had several issues which prevented the users to have real tests. However, after a week of debugging I’ve released pg_chameleon v2.0.0.alpha2 which is now quite usable. For a complete command line…
Today is one year since I started working seriously on pg_chameleon. With this commit I changed the project’s license to the 2 clause BSD and the project’s scope, evolving the project into a MySQL to PostgreSQL replica system. Initially this change was just a try. I needed to synchronise the data between MySQL and PostgreSQL and at that time the only option I had it was to use the…
Bugfix emergency release v1.3.1 I discovered a regression when running the init_replica caused by a wrong handling of missing time in master coordinates. Sorry about that. After another round of bug fixes I’ve released the version 1.3 of my pet project pg_chameleon. The package is available on pypi as usual. The changelog is available here. If you have any question/issue to discussI created…
Last 30th of May I had the honor to talk at the second Estonia PostgreSQL User Group. My talk was about my pet project pg_chameleon and it went very well. The audience was about 40 people highly interested in the topic and the questions were all around. I’m impressed by the Estonian PostgreSQL User Group. Their organizational skills are amazing, the members interest is very high and they are…
Last week I announced the stable release of pg_chameleon as I felt quite confident the tool worked properly. However the murphy law is always ready to teach us we live in an interesting universe. A couple of days after the release I noticed on a server which data were modified seldom a strange replica issue. For some reason at specific moments of the day the inserts replayed on the postgres tables…
In 2013 I started my pet project pg_chameleon to discharge my frustration only to find the entire thing even more frustrating. It’s fascinating how time and circumstances can fix the personal perspective revamping the passion for development that never left me. This philosophical introduction is to tell I still can’t believe I’ve relased the RC1 of my pet project pg_chameleon.…
After fixing few bugs I decided to release the first pg_chameleon beta. The package details with the changelog are available here http://www.pgdba.co.uk/p/mysql-to-postgresql-replica.html These are the notable changes. Switch to psycopg2 2.7.x - Thanks to Daniele’s work now the requirement installs without need for python or postgresql headers, making the install in virtualenv much simpler…
Over the last week end I finally worked trough python’s setuptools and relased the alpha4 for pg_chameleon 1.0. This is the final alpha version the version 1.0. The system now can be installed in the virtualenv just typing pip install pg_chameleon It’s important pip is up to date so if there’s any error upgrade pip with pip install pip –upgrade The first install creates in…
Thanks everybody for listening. And massive thanks to the PostgreSQL Europe staff for organising an amazing conference. The slides from my talk are available fordownload here. The open source replica system is on github https://github.com/the4thdoctor/pg_chameleon and hopefully Transferwise will also release the customised obfuscator soon.
The next week I will present at theFosdem PGDay 2017. This is the first time I travel to Brussels, the first time I visit the Fosdem and the presentation is almost brand new. I gave the same presentation at the last Brighton PostgreSQL meetup and this helped me to tailor the slides in a better way. During the talk I will explain about the first project I was assigned since I joined Transferwisein…
As mentioned in the presentation I did at the Brighton PostgreSQL Meetup, pg chameleon is a commute project. In general I work on it when I travel. And thanks to the pgconf and other requirements I travelled a lot recently. This is the presentation’s recording (sorry the audio is suboptimal). So I was able to build a minimum viable product which seems to work properly. The alpha1 release is…
This is the recording of the pg_chameleon presentation I gave at the Brighton PostgreSQL Meetup The slides are available here http://www.slideshare.net/FedericoCampoli/pg-chameleon-mysql-to-postgresql-replica
When I first read about the systemd I truly believed the linux community would be able to improve this init system. Or at least to stop it if not good enough to be used as replacement for sysvinit. I was wrong. After a strong and poisonous debating Debian switched to systemd in Jessie. Several contributors left Debian and organised a fork called Devuan which is pushing for the init freedom.
Back from Estonia after the amazing European PGConf 2016 I’m about to give a talk on the MySQL to PostgreSQL replication project I’m developing. But first things first. I want to thank all the organisers of the PGConf Europe. The talks were super interesting the location absolutely stunning and the old and new friends I met, remembered me how amazing is the PostgreSQL community.…
The slides from the talk Life on a rollercoaster are available here http://www.slideshare.net/FedericoCampoli/life-on-arollercoaster
It’s almost time for the pgconf 2016. This year the conference is in Tallinn the capital of Estonia. The conference is packed with super interesting talks which I really look forward to listen. I’ll also present the talk life on a rollercoaster which tells the story of the last 4 years of a DBA(just guess who’s he :P) dealing with large large PostgreSQL installations. I decided…
The next meetup at Brighton will be Friday 14th October at 19.30. Daniele Varrazzo will kindly talk about the most popular PostgreSQL driver in the python universe. Psycopg is the most used PostgreSQL driver for the Python programming language. As a driver it stays at a relatively low level, allowing to use all the features offered by the database. Behind the scenes, it does its best to convert…
Back in the 2013 I started playing with sqlalchemy to create a simple extractor from heterogeneous systems to be pushed in postgresql. I decided to give the project a name which recalled the transformation and I called pg_chameleon. To be honest I didn’t like sqlalchemy. Like any other ORM adds an interface to the data layer with a mental approach to the data itself. I lost the interest to…
After the summer break the Brighton PostgreSQL meetup restarts with the monthly technical talks. This time is my round again. I’ll speak on how to scale the backup and recovery on large postgres installations. Actually this is the talk I’ve submitted to the european pgconf. I made the talk in a storytelling form in order to avoid to bore the audience to the death. The talk should be…
Thanks to the Ferrara’s University, departement of Engineering I’ll attend to another evening with PostgreSQL. This time the event will be a seminary with all talks made by me. The first talk will cover our beloved elephant’s history. The second talk will look to the engine with particular attention to the MVCC and the memory manager. The final talk will cover thebackup and…
I’ve been busy recently and I failed to update on the last meetup news. I apologise for that. We had a very interesting meetup in January. Alexey Bashtanov explained how the grouping works in postgres and how to improve or even re implement in C the grouping functions. On the meetup page there are the pictures from the meeting. The presentation’s recording is available thereand the…
I’ll be at the University of Ferrara Saturday 9th of January for a PostgreSQL afternoon. This is the confirmed schedule. 15:00 - Federico Campoli: PostgreSQL, the big the fast and the (NOSQL on) Acid 15:40 - Michele Finelli: The PostgreSQL’s transactional system 16:20 - Coffee break / general chat 16:40 - Federico Campoli: Streaming replication 17:30 - Federico Campoli: Query tuning in…
This second meetup went very well. The audience was interested and we had fun time thanks to the beer and pizzas offered alongside with the venue by our sponsor brandwatch. Here a couple of pictures from the meetup. The recording worked much better than the previous time, here’s the presentation’s video. We’ll meet again shortly for a nice beer. Next technical talk will be…
Three days to go for the next Brighton PostgreSQL meetup. I’ll run a live hangout of the talk. You can join the event there. https://plus.google.com/events/cge4691km5qm8euj4erkcp7jecs The record will become available on youtube shortly after the talk’s end.
November 27th at 19.00 GMT I’ll talk at theBrighton PostgreSQL meetup. This time the group chosen the streaming replication as topic. The talk will cover the PostgreSQL write ahead logging and the crash recovery process. The audience will learn how to setup a standby server using the streaming replication and how to troubleshoot it. Please RSVP here.