A few previous posts ago, we talked about Bucardo and its utility to replicate postgresql databases, we saw how to install Bucardo and we took the first steps to synchronize our databases; specifically, we create a simple master-master relationship of two Databases belonging to different hosts; the input is as follows:
BUCARDO: THE REPLICATION SYSTEM FOR POSTGRESQL
Now I bring you a small detail that must be taken into account when synchronizing the Database and it is about synchronizing the sequences:
The sequences and Bucardo
Sequences are used to generate unique integer values for assignment to associated tables; in other words, they are the ones used to generate primary keys (Primary Key -PK-) for later storage in the tables; by default Bucardo does not synchronize the sequences for these identifiers when inserting the replicated records, resulting in an inconsistency between the databases to be synchronized (as far as the sequences are concerned); however we have several ways to solve this; first the easiest:
1.0 Modify the counter sequence
Let's imagine that we have two Databases, in different networks that require an Internet connection to synchronize:
However, in at least one of the networks it is not possible to have a permanent Internet connection (except for a few hours a day) with the result that synchronization cannot be carried out on a regular basis.
To make the situation worse, both Databases are updated by different users all the time.
What could we do if we want to synchronize these two Databases?
1.1 Avoid record collision
We can handle different sequences so that the records are never overlapped and when synchronizing the Databases the Database identifiers will never collide (or indicate that the PK of the record to be inserted already exists):
Secuencia BD1 = START WITH 1 INCREMENT BY 2.
Secuencia BD2 = START WITH 2 INCREMENT BY 2.We can also use different intervals; that is, start the counter in one of the Databases at 1 and the other at 100000000:
Secuencia BD1 = START WITH 1.
Secuencia BD2 = START WITH 100000000.You can see both scenarios in the following link Pushdelta syncs and sequences.
1.2 Synchronizing the sequences with Bucardo
The other scenario is to indicate that -in addition to the tables- they also synchronize the sequences; to do this we use the following command:
bucardo add <sequences> db=dbname=<nombrebd> relgroup=<nombrerelgroup>Where <sequences> are the sequences to sync; if we want to synchronize all the sequences in our Database:
bucardo add all sequences db=dbname=<nombrebd> relgroup=<nombrerelgroup>Following our example that we saw in the previous Bucardo entry, to synchronize both the tables and their sequences, the following commands are necessary:
bucardo add database oo2local dbname=oo2 user=postgres pass=postgres
bucardo add database ooremoto dbname=oo user=postgres pass=XXXX host=XXXXX
bucardo add dbgroup mydbgroup midblocal:source midbremoto:source
bucardo add all tablerelgroup=myrelgroup db=midblocal
bucardo add all sequences db=midblocal relgroup=myrelgroup
bucardo add sync mysync relgroup=myrelgroup dbs=mydbgroup autokick=0
bucardo update sync mysync autokick=1The very good news is that if you were using the schema outlined in the previous installment (all the tables without the schemas), you can execute this simple line:
bucardo add all tablerelgroup=myrelgroup db=midblocal
To tell Bucardo to also synchronize the sequences and that's it; no need to reboot or update anything else, Bucardo will take the new settings automatically; even so, if you want to know that the changes you applied are actually being taken by Bucardo, just run the following command:
linux1@linux1-Lenovo-3000:~$ bucardo status mysync
======================================================================
Last good : Aug 13, 2015 12:04:39 (time to run: 8s)
Rows deleted/inserted : 1 / 1
Sync name : mysync
Current state : Good
Source relgroup/database : myrelgroup / midblocal
Tables in sync : 47
Status : Active
Check time : None
Overdue time : 00:00:00
Expired time : 00:00:00
Stayalive/Kidsalive : Yes / Yes
Rebuild index : No
Autokick : Yes
Onetimecopy : No
Post-copy analyze : Yes
Last error: :
======================================================================Bucardo adds in Tables in sync the total number of tables and sequences to be synchronized; As my Database has 29 tables and 18 sequences that in total give 47 tables and sequences.
Icons obtained from:
Icons made by Webalys from www.flaticon.com is licensed by CC BY 3.0
Icons made by SimpleIcon from www.flaticon.com is licensed by CC BY 3.0
Develop with Laravel, Django, Flask, CodeIgniter, HTML5, CSS3, MySQL, JavaScript, Vue, Android, iOS, Flutter