Sarin RajJuly 13, 2019
Replica is a copy of information or data and the process to create a replica of that information or data is called Replication. Data base replication refers to transfer of each database instance from a database to other database server. Keep backups of database with high availability and Load balancing is main benefit of database replication. High availability means we could switch over to our up to dated database backup at any times, The primary database server refers to master database server and destination server known as slave server. When primary database goes down, to promote slave server as primary is called fail over.
Database replication is copying and distributing data and database objects from one to another or multiple database servers. And they synchronising each other to maintain consistency. So data is available in more than one database server if main server goes to down by any issue the complete data is available in another replica server. Availability, Reliability, high performance, Load reduction, Support many users are some of the advantages of database replication. In database mirroring maintaining complete database backups of primary database when it fail. While database mirroring, sometimes positioned as an alternative approach to data replication. Data mirroring has found considerable use within the Microsoft SQL Server community. There available many third-party database replication tools for execute and manage database replication processes. Transnational replication, Merge replication and Snapshot replications are some of the different ways of replication.
Transactional replication : It used in server-to-server scenarios. The user receive full copies of the database in the same order as occurred with the publisher. It does not simply copy the data changes, rather consistently and accurately replicates each other. The receiver do not modify data because it strictly for read only purpose.
Merge replication : It combines data from multiple sources into a single central database. Merge replication use initial synchronisation for taking snapshot of data on the master and moving it to slave like transactional replication. It is primarily designed for mobile applications or distributed server applications that have possible data conflicts. It is useful when slave server not in the continuous connection to the network, when it connect to the network replication will detect and combine changes from the master and change data on the slave. Also it allows both publisher and subscriber to independently make changes to the database.
Snapshot replication : It is used to provide the initial data set for transactional and merge replication. It distributes data exactly as it appears at a specific moment in time does not monitor for updates to the data. The complete snapshot is send to user, so it only use for infrequent data change.
Postgres is an open source object-relational database management system it handle high workloads, it supported in Linux, Unix, BSD and Windows servers. Postgresql has many different types of replication methods to keep data from data loss of any failure of primary database. The main benefit of PostgreSQL database replication is to distribute databases to multiple machines, when the primary database server has a problem or when it down there is a available of backup server.
From postgres 9.0 version doing replication with WAL logs, WAL stands for WriteAhead Log it is a internal log files it contains information on past and future disk operations. If any system failure some of the operations present in the WAL may not have been tagged as complete this will helps us to know where the database was unfinished. This achieves database will reconstruct or rollback in stable status. For replication we just need some configurations on both servers with where the log files to stored.
WAL used to sync master and slave database servers, this can be achieved by two ways, they are File based log shipping and Streaming WAL Records.
In file based log shipping the master can directly copy the logs to slave server or it share storage with the slave servers, one WAL log file contains upto 16MB of data. There is a delay in replication because WAL file propagate only after it reaches that threshold, this delay is called lag.
In Streaming Wal records the WAL record chunks streamed by database servers. WAL records are streamed as they generate so it does not wait for the WAL file to be filled. Streaming WAL replication are propagate in two ways synchronous and asynchronous
Three Approaches to Replication in PostgreSQL
1. PostgreSQL streaming replication to replicate data from primary to secondary node. Back up to S3/Blob storage.
2. Volume level replication to replicate at the storage layer from primary to secondary node. Back up to S3/Blob storage.
3. Take incremental backups from the primary node to S3. Reconstruct a new secondary node from S3. When secondary is close enough to primary, start streaming from primary.
Streaming Replication
In Streaming replication continuously ship and apply the WAL XLOG records to standby servers in order to keep them current. It can be asynchronous or synchronous but most of people use default asynchronous replication because of the performance penalty. To set up synchronous replication need to configure synchronous_standby_name and synchronous_commit parameters in the postgresql.conf file. The XLOG decodes of primary database are periodically send to the standby via network. It saying what bytes to add or change in what file. There is some prerequisites for streaming replication like same version of the PostgreSQL Database must installed on both servers, the master server must operate in WAL archiving enabled mode, Both servers always should have connectivity between them to transfer the archived WAL files from production
In Asynchronous replication the data can be replicated after the transaction has been committed on the primary database server. This delay is called lag, but unlike other replication methods this is very short. So there will be chance to cause small data loss. Some of the advantages of asynchronous replication are a low overhead, simplicity, and robustness.
Synchronous replication there no propagation lag between primary and replication servers, the data written by the transaction on the servers at the time the transaction commits. It does not produce the delay for propagation of log file from maser to slave it COMMIT is only valid once it has been confirmed by the desired number of PostgreSQL servers.
Some disadvantages streaming replication:
When master server fails then the slave server promote as master server. The Postgres replication does not provide monitoring and fail over. When the primary node fails, we need to promote a secondary to be the new primary. WAL are the REDO logs in PostgreSQL, it contains all the changes that made in the database and it used for replicaion, recovery, backup and point in recovery. Any changes that have not applied to the data pages can be redone from the REDO logs.
A WAL record will specify, bit by bit, the changes made to the data. Thus, each WAL record will appended into a WAL file. Another main important parameter when configuring PostgreSQL is wal_level, it determines how much information written to the WAL. The default value is minimal which writes only the information to recover from the immediate down. Next is hot_standby which writes information required to run read-only queries and finaly logical writes information to support logical decoding.
LOGICAL REPLICATION
As we know that streaming replication is cluster based we cannot do selective replication but we use some external tools Slony, Bucardo, BDR, etc for selective replication. PostgreSQL 10 introduce a feature Logical Replication, which perform database/object level replication. Logical Replication is based on blocks and byte-by-byte replication it does not need an exact binary copy at the slave server also get write permission to slave server. It also use the WAL file for replication but it will decode it into logical changes, By using this replication we can replicating only some of the tables or consolidating multiple databases into a single one.
Logical Replication consist of a publication on primary server and subscription on slave server concept. Publication is a set of changes from a single table or group of tables. You can limit the changes of objects to replicated, it does event based filtering. Subscription can defined on the destination server and the node on which it defined and referred to as the "subscriber". The connection to the source database defined in subscription. Once a subscription created, Logical replication copies a snapshot of the data on the publisher database. Once that completes, it waits for delta changes and sends them to the subscription node as soon as they occur.
It has some limitations like, the tables must have the same full qualified name between publication and subscription and must have primary key or unique key. It does not replicate schema, sequence,TRUNCATE, Large objects.
Odoo is a Open ERP which have variety of business applications including CRM, eCommerce, Accounting, Inventory, Pos and project management. All These applications installed and accessed through a web interface. In any situation like Hardware or software failure. If primary data base goes fail. Then all running process stopped, so odoo need to connect the backup database server without causing any interrupt that can achieved by odoo database replication. Moreover, we can improve Reliability of odoo database that is the data is available in more than one database server, so if one server goes down the data is still available on replicated server.
Odoo database replication can be used to provide fault tolerance and it is relatively inexpensive to implement. In odoo database replication there is a publisher and subscriber, the publisher is the database that hold main copy of the odoo data.
If a node is fail or network become unavailable, the replicated data queued. And when the node comes online the replication process will restart. When access multiple users to same data of odoo, providing replicated copies of the data at each local site reduces network traffic and improves response time. Replication allows you to increase data durability guarantee by ensuring that data modifications written to multiple machines and spread data reads across multiple machines on your network which improve your application's read performance.
Here odoo main db server is synchronized with some other db server for the backup purpose. We hope you understood how Database Replication work with Odoo. And if you enjoy our content here, you’ll love the stuff we share on LinkedIn.
0