It's almost one year from the Alfresco Devcon in Lisbon and, while we are preparing for the upcoming event in Edinburgh, planned for January 29-31, we want to share with you best moments and takeaways from one of the most acclaimed Alfresco events, dedicated to the Alfresco community.
This series is about five articles and five authors, part of the developer team, here at Xenit. All of them shared technical demos, best practices, tips and techniques, providing Alfresco developers, customers and partners access to practical knowledge and real use cases.
In this first blog post we celebrate Thijs Lemmens, Senior ECM Engineer, that presented a PostgreSQL practical guide for Alfresco. He focused on three main topics:
- Master - Slave replication
- Continuous backup and Point-in-time recovery
- Upgrading your database without downtime
"All these topics are covered in the context of Alfresco. However, these techniques are applicable to a wide range of applications" - Thijs Lemmens.
1. MASTER SLAVE REPLICATION
In PostgreSQL, a master-slave setup relies on a concept called streaming replication. This concept relies on the transaction logs (WAL) that ensure ACID compliance and copies those over from master to slave, or standby. Before you setup such a replication there is an important choice to make: Synchronous vs Asynchronous replications.
2. CONTINUOUS BACKUP AND POINT-IN-TIME RECOVERY
Readers that are familiar with PostgreSQL will know pg_dump without a doubt. While backing up a database with pg_dump is easy to use, it has some disadvantages when dealing with bigger databases. That’s where wal archiving comes in the picture.
The concept of wal archiving is very simple: the wal files are copied to another location. These copied wal files can then be used when we want to restore a backup.
This method has (at least) 3 advantages:
- You don’t need to schedule a nightly backup.
- The backup is done continuously, meaning that the time window of data loss, in case of disaster, is very small.
- Upon restore, you can decide to restore up until a certain time or transaction. If you made an error, like accidentally dropping a table, you can restore just before you made the mistake.
3. UPGRADING YOUR DATABASE WITHOUT DOWNTIME
The easiest way to perform a database upgrade is using pg_dump to make a logical backup and to restore that backup on the new version of the database. The problem with this approach is that you need some time to perform these operations, and while doing so, your database should be down, or in a read-only state. With the help of an extension pglogical, we can overcome this problem. Pglogical translates the physical statements to a version independent format, so they can be applied on another version of PostgreSQL.
Scripts, commands and lines are available in the ebook, written by Thijs, that you can download for free.