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. Each machine is configured in the same way. 1 cpu 1 GB ram Hard disk of 12 GB with single partition ext4 sshd up and running The machines have one bridged network adapter eth0.

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 most of the cases I had to take screenshots of the code and paste it as image to avoid the formatting to be scrambled.

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 the name as I think the idea is still valid.

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 reference and an usage example click here.

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 MySQL foreign data wrapper, eventually to copy the data locally every now and then.

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 a community on gitter. Please join! I’ve also added a RELASE_NOTESfile to explain the changes.

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 fantastic friends.

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 failed with the primary key violation.

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. The release is already available on pypi and includes several bug fixes and performance improvement.

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 Install system wide or in virtualenv follow the same rules. The configuration files and the package files are installed in the python’s site-packages/pg_chameleon.