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.

 

 

 

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.

 

PostgreSQl master slave

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.

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 delivers Products and Solutions to create Return on Content, on top of Alfresco, the Digital Business Platform. 

Our platform, Alfred, is a blueprint content services architecture with prefabricated components, to unlock the value of Alfresco.

  • Alfred Desktop is a desktop application for Alfresco, that acts as Alfresco and looks like Microsoft Explorer
  • Alfred Finder is a web application to find and retrieve documents on Alfresco, preview them and edit metadata
  • Alfred Edge is an API Gateway, a single point of entry to Alfresco that simplifies and decouples your architecture
  • Alfred Archive is a secure, durable and extremely low cost storage service for data archiving and long-term backup.

 

Subscribe to Email Updates

Recent Posts