Alfresco DevCon - PostgreSQL practical guide

Posted by Daniela Di Noi on 11/7/18 3:00 PM

Find me on:

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.





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.


PostgreSQl master slave


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.



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.

Alfresco Devcon


Scripts, commands and lines are available in the ebook, written by Thijs, that you can download for free. 

Download the ebook




Topics: Alfresco, PostgreSQL

About Xenit 


Xenit is a Belgium-based IT company, focusing  on content services solutions, and covering all document-related business processes, from data migration to digital archive to hybrid/cloud hosting solution, to help organizations get control of their information. Premier Partner and System Integrator of Alfresco Digital Business Platform, Xenit has more than 10 years of experience in Alfresco Content and Process Services.


Subscribe to Email Updates

Recent Posts

Posts by Topic