{"id":388,"date":"2016-09-19T12:42:03","date_gmt":"2016-09-19T02:42:03","guid":{"rendered":"https:\/\/icicimov.com\/blog\/?p=388"},"modified":"2017-01-26T22:56:33","modified_gmt":"2017-01-26T11:56:33","slug":"postgresql-confluence-db-replication-with-bucardo","status":"publish","type":"post","link":"https:\/\/icicimov.com\/blog\/?p=388","title":{"rendered":"PostgreSQL Confluence DB replication with Bucardo"},"content":{"rendered":"

In cases where we can’t use the built-in PostgreSQL replication facility, like for example Confluence DB which has replication protection, Bucardo<\/a> is very efficient option. It is an asynchronous PostgreSQL replication system, allowing for both multi-master and multi-slave operations. Bucardo is free and open source software released under the BSD license.<\/p>\n

In this case we use Bucardo to replicate Confluence from one site to another. As mentioned, each Confluence installation has a special table called clustersafety<\/code> which has an auto-generated unique id associated upon every restart and thus can not be replicated since it exists in memory only and is never stored on disk. Unfortunately the native PostgreSQL replication does not have an option to exclude a table(s), it is all or nothing. That’s where Bucardo comes in play.<\/p>\n

Both DB’s used by Confluence are on PostgreSQL-9.3 and the OS is Ubuntu-14.04.<\/p>\n

Setup<\/h1>\n

Installation<\/h2>\n

Bucardo can be installed on any of the source or target DB server but can also be installed on a separate management server. In one directional replication, like in our case, it can be setup on the source (push) or target (pull) server. For bidirectional replication when we are running a master-master DB setup we can set it up on both servers.<\/p>\n

Install needed packages on the target, in this case server2:<\/p>\n

ubuntu@server2:~$ sudo aptitude install postgresql-plperl-9.3 libdbix-safe-perl libboolean-perl libdbd-pg-perl libtest-simple-perl libdbi-perl libdbd-pg-perl libboolean-perl wget build-essential libreadline-dev libz-dev autoconf bison libtool libgeos-c1 libproj-dev libgdal-dev libxml2-dev libxml2-utils libjson0-dev xsltproc docbook-xsl docbook-mathml libossp-uuid-dev libperl-dev libdbix-safe-perl\n<\/code><\/pre>\n
We can then install the bucardo package or compile from source if we want the latest stable version:<\/p>\n
ubuntu@server2:~$ sudo mkdir \/usr\/src\/bucardo\nubuntu@server2:~$ sudo chown ubuntu\\: \/usr\/src\/bucardo\nubuntu@server2:~$ cd \/usr\/src\/bucardo\nubuntu@server2:~$ wget http:\/\/bucardo.org\/downloads\/Bucardo-5.4.1.tar.gz\nubuntu@server2:~$ tar -xzvf Bucardo-5.4.1.tar.gz\nubuntu@server2:~$ cd Bucardo-5.4.1\/\nubuntu@server2:~$ perl Makefile.PL\nubuntu@server2:~$ make\nubuntu@server2:~$ make test\nubuntu@server2:~$ sudo make install\n<\/code><\/pre>\n
As a prerequisites, ALL<\/strong> tables in the replicated DB need to have primary key. Luckily in the Confluence db there is a single table missing one which is easy to fix on the source side, which is always the server1 instance:<\/p>\n
ubuntu@server1:~$ sudo su - postgres\npostgres@help:~$ psql confluence\npsql (9.3.9)\nType \"help\" for help.\nconfluence=# SELECT table_catalog, table_schema, table_name FROM information_schema.tables WHERE table_type <> 'VIEW' AND (table_catalog, table_schema, table_name) NOT IN (SELECT table_catalog, table_schema, table_name FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY') AND table_schema NOT IN ('information_schema', 'pg_catalog');\n table_catalog | table_schema |      table_name     \n---------------+--------------+----------------------\n confluence    | public       | hibernate_unique_key\n(1 row)\nconfluence=#\nconfluence=# ALTER TABLE hibernate_unique_key ADD PRIMARY KEY (next_hi);\nALTER TABLE\n<\/code><\/pre>\n
Login to the source and target DB’s and create PL\/Perl extension:<\/p>\n
[ALL]$ sudo su - postgres\n[ALL]$ psql\npsql (9.3.9)\nType \"help\" for help.\n\npostgres=# CREATE EXTENSION plperl;\nCREATE EXTENSION\npostgres=#\n<\/code><\/pre>\n
To setup the bucardo db we first create the PID directory:<\/p>\n
ubuntu@server2:~$ sudo mkdir -p \/var\/run\/bucardo\n<\/code><\/pre>\n
Then we run:<\/p>\n
ubuntu@server2:~$ bucardo install\n\nCurrent connection settings:\n1. Host:           <none>\n2. Port:           5432\n3. User:           postgres\n4. Database:       bucardo\n5. PID directory:  \/var\/run\/bucardo\nEnter a number to change it, P to proceed, or Q to quit: 1\n\nChange the host to: 127.0.0.1\n\nChanged host to: 127.0.0.1\nCurrent connection settings:\n1. Host:           127.0.0.1\n2. Port:           5432\n3. User:           postgres\n4. Database:       bucardo\n5. PID directory:  \/var\/run\/bucardo\nEnter a number to change it, P to proceed, or Q to quit: P\n\nFailed to connect to database 'bucardo', will try 'postgres'\nCurrent connection settings:\n1. Host:           127.0.0.1\n2. Port:           5432\n3. User:           postgres\n4. Database:       postgres\n5. PID directory:  \/var\/run\/bucardo\nEnter a number to change it, P to proceed, or Q to quit: P\n\nPostgres version is: 9.3\nCreating superuser 'bucardo'\nAttempting to create and populate the bucardo database and schema\nDatabase creation is complete\n\nUpdated configuration setting \"piddir\"\nInstallation is now complete.\nIf you see errors or need help, please email bucardo-general@bucardo.org\n\nYou may want to check over the configuration variables next, by running:\nbucardo show all\nChange any setting by using: bucardo set foo=bar\n<\/code><\/pre>\n
Then login to PostgreSQL and set the password for the bucardo user:<\/p>\n
postgres=# ALTER USER bucardo WITH ENCRYPTED PASSWORD 'bucardo-password';\nALTER ROLE\n<\/code><\/pre>\n
Now we create the user and the db we want to replicate on the local server, the replication target:<\/p>\n
ubuntu@server2:~$ sudo -u postgres createuser -e -E -P confluence\nEnter password for new role:\nEnter it again:\nCREATE ROLE confluence ENCRYPTED PASSWORD 'md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;\n\nubuntu@server2:~$ sudo -u postgres createdb -e --encoding=UTF-8 --lc-collate=C --lc-ctype=C -T template0 -O confluence confluence\nCREATE DATABASE confluence OWNER confluence ENCODING 'UTF-8' TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';\n<\/code><\/pre>\n
Source DB settings and initial schema dump to the target<\/h2>\n
To copy over the database from the source server we first need to make some config changes on the source side. Add connection permission in the source pg_hba.conf<\/code> file by appending the following at the end of the \/etc\/postgresql\/9.3\/main\/pg_hba.conf<\/code> config file to allow connections from server2 over SSL:<\/p>\n
[...]\nhostssl all             postgres        <server2-ip>\/32        trust\nhostssl confluence      confluence      <server2-ip>\/32        md5\nhostssl all             bucardo         <server2-ip>\/32        md5\n<\/code><\/pre>\n
PostgreSQL by default listens on the local interface only so in the \/etc\/postgresql\/9.3\/main\/postgresql.conf<\/code> conf file we add:<\/p>\n
listen_address = '*';\n<\/code><\/pre>\n
We also need to create the Bucardo super user with some strong password:<\/p>\n
ubuntu@server1:~$ sudo -u postgres CREATE USER bucardo WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'bucardo-password';\n<\/code><\/pre>\n
and restart the database so the postgres user can connect remotely from the target server.<\/p>\n
Then on the target server2 we run:<\/p>\n
ubuntu@server2:~$ pg_dump -v -U postgres -h server1.mydomain.com -d confluence --schema-only | sudo -u postgres psql confluence\n<\/code><\/pre>\n
to copy over the Confluence DB schema.<\/p>\n
Firewall<\/h2>\n
We need to open the TCP port 5432<\/code> in the source server firewall for access from the target server only.<\/p>\n
Setting up Bucardo Sync<\/h2>\n
With the databases ready to go, we can now configure Bucardo itself. First lets test the bucardo user connection from the target to the source server:<\/p>\n
ubuntu@server2:~$ psql -h server1.mydomain.com -U bucardo -W -d confluence\nPassword for user bucardo:\npsql (9.3.10, server 9.3.9)\nSSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)\nType \"help\" for help.\n\nconfluence=# \\d\nconfluence=# \\q\n<\/code><\/pre>\n
Now we can add the Source Database first enabling ssl mode so the transfers are encrypted:<\/p>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password add db source_db dbname=confluence host=server1.mydomain.com user=bucardo pass=bucardo-password conn=sslmode=require\nAdded database \"source_db\"\n<\/code><\/pre>\n
Then we add the Destination Database which is running on the local host:<\/p>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password add db target_db dbname=confluence host=127.0.0.1 user=bucardo pass=bucardo-password\n<\/code><\/pre>\n
We add the tables and sequences we want to migrate from the source database but we want to exclude the clustersafety<\/code> table from the replication as it is a protection in Confluence against db syncing:<\/p>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password add all tables --exclude-table clustersafety db=source_db relgroup=confluence_db_group\nCreating relgroup: confluence_db_group\nAdded table public.AO_187CCC_SIDEBAR_LINK to relgroup confluence_db_group\nAdded table public.AO_21D670_WHITELIST_RULES to relgroup confluence_db_group\nAdded table public.AO_38321B_CUSTOM_CONTENT_LINK to relgroup confluence_db_group\nAdded table public.AO_42E351_HEALTH_CHECK_ENTITY to relgroup confluence_db_group\nAdded table public.AO_54C900_SPACE_BLUEPRINT_AO to relgroup confluence_db_group\nAdded table public.AO_5F3884_FEATURE_DISCOVERY to relgroup confluence_db_group\nAdded table public.AO_6384AB_FEATURE_METADATA_AO to relgroup confluence_db_group\nAdded table public.AO_92296B_AORECENTLY_VIEWED to relgroup confluence_db_group\nAdded table public.AO_9412A1_AONOTIFICATION to relgroup confluence_db_group\nAdded table public.AO_9412A1_AOREGISTRATION to relgroup confluence_db_group\nAdded table public.AO_9412A1_AOTASK to relgroup confluence_db_group\nAdded table public.AO_DC98AE_AOHELP_TIP to relgroup confluence_db_group\nAdded table public.AO_A0B856_WEB_HOOK_LISTENER_AO to relgroup confluence_db_group\nAdded table public.AO_EF9604_FEATURE_DISCOVERY to relgroup confluence_db_group\nAdded table public.AO_9412A1_AOUSER to relgroup confluence_db_group\nAdded table public.attachmentdata to relgroup confluence_db_group\nAdded table public.bandana to relgroup confluence_db_group\nAdded table public.AO_9412A1_USER_APP_LINK to relgroup confluence_db_group\nAdded table public.confversion to relgroup confluence_db_group\nAdded table public.cwd_directory to relgroup confluence_db_group\nAdded table public.confancestors to relgroup confluence_db_group\nAdded table public.content_perm_set to relgroup confluence_db_group\nAdded table public.contentproperties to relgroup confluence_db_group\nAdded table public.bodycontent to relgroup confluence_db_group\nAdded table public.content_label to relgroup confluence_db_group\nAdded table public.cwd_app_dir_group_mapping to relgroup confluence_db_group\nAdded table public.cwd_group to relgroup confluence_db_group\nAdded table public.cwd_application to relgroup confluence_db_group\nAdded table public.cwd_directory_operation to relgroup confluence_db_group\nAdded table public.cwd_app_dir_operation to relgroup confluence_db_group\nAdded table public.content_perm to relgroup confluence_db_group\nAdded table public.cwd_membership to relgroup confluence_db_group\nAdded table public.cwd_directory_attribute to relgroup confluence_db_group\nAdded table public.cwd_group_attribute to relgroup confluence_db_group\nAdded table public.cwd_application_attribute to relgroup confluence_db_group\nAdded table public.cwd_application_address to relgroup confluence_db_group\nAdded table public.os_group to relgroup confluence_db_group\nAdded table public.imagedetails to relgroup confluence_db_group\nAdded table public.os_user to relgroup confluence_db_group\nAdded table public.decorator to relgroup confluence_db_group\nAdded table public.hibernate_unique_key to relgroup confluence_db_group\nAdded table public.keystore to relgroup confluence_db_group\nAdded table public.external_entities to relgroup confluence_db_group\nAdded table public.cwd_user to relgroup confluence_db_group\nAdded table public.groups to relgroup confluence_db_group\nAdded table public.follow_connections to relgroup confluence_db_group\nAdded table public.indexqueueentries to relgroup confluence_db_group\nAdded table public.label to relgroup confluence_db_group\nAdded table public.local_members to relgroup confluence_db_group\nAdded table public.likes to relgroup confluence_db_group\nAdded table public.external_members to relgroup confluence_db_group\nAdded table public.extrnlnks to relgroup confluence_db_group\nAdded table public.logininfo to relgroup confluence_db_group\nAdded table public.os_propertyentry to relgroup confluence_db_group\nAdded table public.links to relgroup confluence_db_group\nAdded table public.cwd_user_attribute to relgroup confluence_db_group\nAdded table public.cwd_user_credential_record to relgroup confluence_db_group\nAdded table public.trackbacklinks to relgroup confluence_db_group\nAdded table public.user_mapping to relgroup confluence_db_group\nAdded table public.plugindata to relgroup confluence_db_group\nAdded table public.remembermetoken to relgroup confluence_db_group\nAdded table public.os_user_group to relgroup confluence_db_group\nAdded table public.users to relgroup confluence_db_group\nAdded table public.spaces to relgroup confluence_db_group\nAdded table public.trustedapp to relgroup confluence_db_group\nAdded table public.trustedapprestriction to relgroup confluence_db_group\nAdded table public.spacepermissions to relgroup confluence_db_group\nAdded table public.attachments to relgroup confluence_db_group\nAdded table public.cwd_app_dir_mapping to relgroup confluence_db_group\nAdded table public.spacegrouppermissions to relgroup confluence_db_group\nAdded table public.pagetemplates to relgroup confluence_db_group\nAdded table public.spacegroups to relgroup confluence_db_group\nAdded table public.content to relgroup confluence_db_group\nAdded table public.AO_54C900_CONTENT_BLUEPRINT_AO to relgroup confluence_db_group\nAdded table public.AO_54C900_C_TEMPLATE_REF to relgroup confluence_db_group\nAdded table public.notifications to relgroup confluence_db_group\nNew tables added: 76\n<\/code><\/pre>\n
This command creates the relation group called confluence_db_group<\/code> for us too which contains the list of tables and sequences we add to replication. Next we add the db sequences too:<\/p>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password add all sequences db=source_db relgroup=confluence_db_group\nAdded sequence public.AO_187CCC_SIDEBAR_LINK_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_21D670_WHITELIST_RULES_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_38321B_CUSTOM_CONTENT_LINK_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_42E351_HEALTH_CHECK_ENTITY_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_54C900_CONTENT_BLUEPRINT_AO_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_54C900_C_TEMPLATE_REF_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_54C900_SPACE_BLUEPRINT_AO_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_5F3884_FEATURE_DISCOVERY_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_6384AB_FEATURE_METADATA_AO_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_92296B_AORECENTLY_VIEWED_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_9412A1_AONOTIFICATION_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_9412A1_AOTASK_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_9412A1_AOUSER_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_9412A1_USER_APP_LINK_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_A0B856_WEB_HOOK_LISTENER_AO_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_DC98AE_AOHELP_TIP_ID_seq to relgroup confluence_db_group\nAdded sequence public.AO_EF9604_FEATURE_DISCOVERY_ID_seq to relgroup confluence_db_group\nNew sequences added: 17\n<\/code><\/pre>\n
In case we have added all tables by mistake then later we can remove the clustersafety<\/code> table from the replication:<\/p>\n
$ bucardo -U bucardo -d bucardo -P bucardo-password remove table public.clustersafety db=source_db\nRemoved the following tables:\n  public.clustersafety (DB: source_db)\n<\/code><\/pre>\n
Next we create the dbgroup:<\/p>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password add dbgroup confluence_db_group source_db:source target_db:target\nCreated dbgroup \"confluence_db_group\"\nAdded database \"source_db\" to dbgroup \"confluence_db_group\" as source\nAdded database \"target_db\" to dbgroup \"confluence_db_group\" as target\n<\/code><\/pre>\n
And create the sync with autokick=0<\/code> to prevent Bucardo to start replicating in case it is running:<\/p>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password add sync confluence_sync relgroup=confluence_db_group dbs=confluence_db_group autokick=0\nAdded sync \"confluence_sync\"\n<\/code><\/pre>\n
We can also tell Bucardo to validate the sync:<\/p>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password validate confluence_sync\nValidating sync confluence_sync ... OK\n<\/code><\/pre>\n
Now it’s time to migrate the data:<\/p>\n
ubuntu@server2:~$ pg_dump -v -U postgres -h server1.mydomain.com -d confluence --data-only --disable-triggers -N bucardo | PGOPTIONS='-c session_replication_role=replica' sudo -u postgres psql confluence\n<\/code><\/pre>\n
and after that’s finished we can enable the sync autokick:<\/p>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password update sync confluence_sync autokick=1\n<\/code><\/pre>\n
The parameter autokick=1<\/code> means Bucardo will monitor the table changes on the source and trigger sync in case of any changes.<\/p>\n
At the end we start Bucardo:<\/p>\n
ubuntu@server2~$ sudo mkdir -p \/var\/log\/bucardo\nubuntu@server2~$ sudo bucardo -U bucardo -d bucardo -P bucardo-password start\n<\/code><\/pre>\n
To check the Bucardo sync status:<\/p>\n
ubuntu@server2:\/usr\/src\/bucardo\/Bucardo-5.4.1$ sudo bucardo -U bucardo -d bucardo -P bucardo-password status confluence_sync\n======================================================================\nSync name                : confluence_sync\nCurrent state            : No records found\nSource relgroup\/database : confluence_db_group \/ source_db\nTables in sync           : 93\nStatus                   : Active\nCheck time               : None\nOverdue time             : 00:00:00\nExpired time             : 00:00:00\nStayalive\/Kidsalive      : Yes \/ Yes\nRebuild index            : No\nAutokick                 : Yes\nOnetimecopy              : No\nPost-copy analyze        : Yes\nLast error:              :\n======================================================================\n<\/code><\/pre>\n
After waiting for some time we can check the Bucardo sync status again:<\/p>\n
ubuntu@server2~$ sudo bucardo -U bucardo -d bucardo -P bucardo-password status confluence_sync\n\n======================================================================\nLast good                : Oct 05, 2015 09:26:01 (time to run: 22s)\nRows deleted\/inserted    : 2 \/ 2\nSync name                : confluence_sync\nCurrent state            : Good\nSource relgroup\/database : confluence_db_group \/ source_db\nTables in sync           : 93\nStatus                   : Active\nCheck time               : None\nOverdue time             : 00:00:00\nExpired time             : 00:00:00\nStayalive\/Kidsalive      : Yes \/ Yes\nRebuild index            : No\nAutokick                 : Yes\nOnetimecopy              : No\nPost-copy analyze        : Yes\nLast error:              :\n======================================================================\n<\/code><\/pre>\n
and we can see couple of changes have been applied. We can set this to email us via crontab for ubuntu user:<\/p>\n
0 *\/6 * * * \/usr\/local\/bin\/bucardo -U bucardo -d bucardo -P bucardo-password status confluence_sync | \/usr\/bin\/mail -s \"Bucardo status\" user@mydomain.com\n<\/code><\/pre>\n
This will send us email with Bucardo status every 6 hours so we can notice if anything broken.<\/p>\n
At the end we can check the Bucardo postgres processes on the target:<\/p>\n
ubuntu@server2:~$ ps ax -o pid,command | grep postgres | grep bucardo\n 2698 postgres: bucardo bucardo [local] idle                                                                                     \n 2702 postgres: bucardo confluence 127.0.0.1(15944) idle                                                                         \n 3188 postgres: bucardo bucardo [local] idle                                                                                     \n 3192 postgres: bucardo bucardo [local] idle                                                                                     \n 3205 postgres: bucardo confluence 127.0.0.1(15958) idle                                                                         \n 3309 postgres: bucardo bucardo [local] idle                                                                                     \n 3325 postgres: bucardo confluence 127.0.0.1(15981) idle\n<\/code><\/pre>\n
and the source server:<\/p>\n
ubuntu@server1:~$ ps ax -o pid,command | grep postgres | grep bucardo\n 5122 postgres: bucardo confluence <server2 -ip>(48271) idle                                                                      \n 5692 postgres: bucardo confluence <\/server2><server2 -ip>(48285) idle                                                                      \n 5693 postgres: bucardo confluence <\/server2><server2 -ip>(48286) idle                                                                      \n 5954 postgres: bucardo confluence <\/server2><server2 -ip>(48309) idle\n<\/code><\/pre>\n
Some useful commands<\/h2>\n
ubuntu@server2:~$ bucardo -U bucardo -d bucardo -P bucardo-password list tables\n1.  Table: public.AO_187CCC_SIDEBAR_LINK          DB: source_db  PK: ID (integer)                                            \n2.  Table: public.AO_21D670_WHITELIST_RULES       DB: source_db  PK: ID (integer)                                            \n3.  Table: public.AO_38321B_CUSTOM_CONTENT_LINK   DB: source_db  PK: ID (integer)                                            \n4.  Table: public.AO_42E351_HEALTH_CHECK_ENTITY   DB: source_db  PK: ID (integer)                                            \n74. Table: public.AO_54C900_CONTENT_BLUEPRINT_AO  DB: source_db  PK: ID (integer)                                            \n75. Table: public.AO_54C900_C_TEMPLATE_REF        DB: source_db  PK: ID (integer)                                            \n5.  Table: public.AO_54C900_SPACE_BLUEPRINT_AO    DB: source_db  PK: ID (integer)                                            \n6.  Table: public.AO_5F3884_FEATURE_DISCOVERY     DB: source_db  PK: ID (integer)                                            \n7.  Table: public.AO_6384AB_FEATURE_METADATA_AO   DB: source_db  PK: ID (integer)                                            \n8.  Table: public.AO_92296B_AORECENTLY_VIEWED     DB: source_db  PK: ID (bigint)                                             \n9.  Table: public.AO_9412A1_AONOTIFICATION        DB: source_db  PK: ID (bigint)                                             \n10. Table: public.AO_9412A1_AOREGISTRATION        DB: source_db  PK: ID (varchar)                                            \n11. Table: public.AO_9412A1_AOTASK                DB: source_db  PK: ID (bigint)                                             \n15. Table: public.AO_9412A1_AOUSER                DB: source_db  PK: ID (bigint)                                             \n18. Table: public.AO_9412A1_USER_APP_LINK         DB: source_db  PK: ID (bigint)                                             \n13. Table: public.AO_A0B856_WEB_HOOK_LISTENER_AO  DB: source_db  PK: ID (integer)                                            \n12. Table: public.AO_DC98AE_AOHELP_TIP            DB: source_db  PK: ID (integer)                                            \n14. Table: public.AO_EF9604_FEATURE_DISCOVERY     DB: source_db  PK: ID (integer)                                            \n16. Table: public.attachmentdata                  DB: source_db  PK: attachmentdataid (bigint)                               \n68. Table: public.attachments                     DB: source_db  PK: attachmentid (bigint)                                   \n17. Table: public.bandana                         DB: source_db  PK: bandanaid (bigint)                                      \n24. Table: public.bodycontent                     DB: source_db  PK: bodycontentid (bigint)                                  \n21. Table: public.confancestors                   DB: source_db  PK: descendentid|ancestorposition (bigint|integer)          \n19. Table: public.confversion                     DB: source_db  PK: confversionid (bigint)                                  \n73. Table: public.content                         DB: source_db  PK: contentid (bigint)                                      \n25. Table: public.content_label                   DB: source_db  PK: id (bigint)                                             \n31. Table: public.content_perm                    DB: source_db  PK: id (bigint)                                             \n22. Table: public.content_perm_set                DB: source_db  PK: id (bigint)                                             \n23. Table: public.contentproperties               DB: source_db  PK: propertyid (bigint)                                     \n26. Table: public.cwd_app_dir_group_mapping       DB: source_db  PK: id (bigint)                                             \n69. Table: public.cwd_app_dir_mapping             DB: source_db  PK: id (bigint)                                             \n30. Table: public.cwd_app_dir_operation           DB: source_db  PK: app_dir_mapping_id|operation_type (bigint|varchar)      \n28. Table: public.cwd_application                 DB: source_db  PK: id (bigint)                                             \n36. Table: public.cwd_application_address         DB: source_db  PK: application_id|remote_address (bigint|varchar)          \n35. Table: public.cwd_application_attribute       DB: source_db  PK: application_id|attribute_name (bigint|varchar)          \n20. Table: public.cwd_directory                   DB: source_db  PK: id (bigint)                                             \n33. Table: public.cwd_directory_attribute         DB: source_db  PK: directory_id|attribute_name (bigint|varchar)            \n29. Table: public.cwd_directory_operation         DB: source_db  PK: directory_id|operation_type (bigint|varchar)            \n27. Table: public.cwd_group                       DB: source_db  PK: id (bigint)                                             \n34. Table: public.cwd_group_attribute             DB: source_db  PK: id (bigint)                                             \n32. Table: public.cwd_membership                  DB: source_db  PK: id (bigint)                                             \n44. Table: public.cwd_user                        DB: source_db  PK: id (bigint)                                             \n56. Table: public.cwd_user_attribute              DB: source_db  PK: id (bigint)                                             \n57. Table: public.cwd_user_credential_record      DB: source_db  PK: id (bigint)                                             \n40. Table: public.decorator                       DB: source_db  PK: decoratorid (bigint)                                    \n43. Table: public.external_entities               DB: source_db  PK: id (bigint)                                             \n51. Table: public.external_members                DB: source_db  PK: groupid|extentityid (bigint|int8)                       \n52. Table: public.extrnlnks                       DB: source_db  PK: linkid (bigint)                                         \n46. Table: public.follow_connections              DB: source_db  PK: connectionid (bigint)                                   \n45. Table: public.groups                          DB: source_db  PK: id (bigint)                                             \n41. Table: public.hibernate_unique_key            DB: source_db  PK: next_hi (integer)                                       \n38. Table: public.imagedetails                    DB: source_db  PK: attachmentid (bigint)                                   \n47. Table: public.indexqueueentries               DB: source_db  PK: entryid (bigint)                                        \n42. Table: public.keystore                        DB: source_db  PK: keyid (bigint)                                          \n48. Table: public.label                           DB: source_db  PK: labelid (bigint)                                        \n50. Table: public.likes                           DB: source_db  PK: id (bigint)                                             \n55. Table: public.links                           DB: source_db  PK: linkid (bigint)                                         \n49. Table: public.local_members                   DB: source_db  PK: groupid|userid (bigint|int8)                            \n53. Table: public.logininfo                       DB: source_db  PK: id (bigint)                                             \n76. Table: public.notifications                   DB: source_db  PK: notificationid (bigint)                                 \n37. Table: public.os_group                        DB: source_db  PK: id (bigint)                                             \n54. Table: public.os_propertyentry                DB: source_db  PK: entity_name|entity_id|entity_key (varchar|bigint|varchar)\n39. Table: public.os_user                         DB: source_db  PK: id (bigint)                                             \n62. Table: public.os_user_group                   DB: source_db  PK: user_id|group_id (bigint|int8)                          \n71. Table: public.pagetemplates                   DB: source_db  PK: templateid (bigint)                                     \n60. Table: public.plugindata                      DB: source_db  PK: plugindataid (bigint)                                   \n61. Table: public.remembermetoken                 DB: source_db  PK: id (bigint)                                             \n70. Table: public.spacegrouppermissions           DB: source_db  PK: spacegrouppermid (bigint)                               \n72. Table: public.spacegroups                     DB: source_db  PK: spacegroupid (bigint)                                   \n67. Table: public.spacepermissions                DB: source_db  PK: permid (bigint)                                         \n64. Table: public.spaces                          DB: source_db  PK: spaceid (bigint)                                        \n58. Table: public.trackbacklinks                  DB: source_db  PK: linkid (bigint)                                         \n65. Table: public.trustedapp                      DB: source_db  PK: trustedappid (bigint)                                   \n66. Table: public.trustedapprestriction           DB: source_db  PK: trustedapprestrictionid (bigint)                        \n59. Table: public.user_mapping                    DB: source_db  PK: user_key (varchar)                                      \n63. Table: public.users                           DB: source_db  PK: id (bigint)\n\n$ tail -f \/var\/log\/bucardo\/log.bucardo\n.\n.\n(29294) [Mon Oct  5 09:19:28 2015] MCP   Inspecting source sequence \"public.AO_9412A1_AOUSER_ID_seq\" on database \"source_db\"\n(29294) [Mon Oct  5 09:19:28 2015] MCP   Inspecting source sequence \"public.AO_9412A1_USER_APP_LINK_ID_seq\" on database \"source_db\"\n(29294) [Mon Oct  5 09:19:28 2015] MCP   Inspecting source sequence \"public.AO_A0B856_WEB_HOOK_LISTENER_AO_ID_seq\" on database \"source_db\"\n(29294) [Mon Oct  5 09:19:29 2015] MCP   Inspecting source sequence \"public.AO_DC98AE_AOHELP_TIP_ID_seq\" on database \"source_db\"\n(29294) [Mon Oct  5 09:19:29 2015] MCP   Inspecting source sequence \"public.AO_EF9604_FEATURE_DISCOVERY_ID_seq\" on database \"source_db\"\n(29294) [Mon Oct  5 09:19:32 2015] MCP Active syncs: 1\n(29294) [Mon Oct  5 09:19:32 2015] MCP Entering main loop\n(29319) [Mon Oct  5 09:19:32 2015] VAC New VAC daemon. PID=29319\n(29294) [Mon Oct  5 09:19:32 2015] MCP Created VAC 29319\n(29321) [Mon Oct  5 09:19:33 2015] CTL New controller for sync \"confluence_sync\". Relgroup is \"confluence_db_group\", dbs is \"confluence_db_group\". PID=29321\n(29321) [Mon Oct  5 09:19:33 2015] CTL   stayalive: 1 checksecs: 0 kicked: 1\n(29321) [Mon Oct  5 09:19:33 2015] CTL   kidsalive: 1 onetimecopy: 1 lifetimesecs: 0 (NULL) maxkicks: 0\n(29294) [Mon Oct  5 09:19:33 2015] MCP Created controller 29321 for sync \"confluence_sync\". Kick is 1\n(29319) [Mon Oct  5 09:19:37 2015] VAC Connected to database \"source_db\" with backend PID of 18349\n(29321) [Mon Oct  5 09:19:37 2015] CTL Database \"source_db\" backend PID: 18350\n(29321) [Mon Oct  5 09:19:37 2015] CTL Database \"target_db\" backend PID: 29324\n(29341) [Mon Oct  5 09:23:35 2015] KID (confluence_sync) New kid, sync \"confluence_sync\" alive=1 Parent=29321 PID=29341 kicked=1 OTC: 1\n(29341) [Mon Oct  5 09:25:45 2015] KID (confluence_sync) Total target rows deleted: 15781\n(29341) [Mon Oct  5 09:25:45 2015] KID (confluence_sync) Total target rows copied: 15781\n(29341) [Mon Oct  5 09:25:46 2015] KID (confluence_sync) Total time for sync \"confluence_sync\" (15781 rows, 0 tables): 2 minutes 5 seconds (125.95 seconds)\n(29341) [Mon Oct  5 09:25:46 2015] KID (confluence_sync) Kid 29341 exiting at cleanup_kid.  Reason: Normal exit\n(29358) [Mon Oct  5 09:25:56 2015] KID (confluence_sync) New kid, sync \"confluence_sync\" alive=1 Parent=29321 PID=29358 kicked=1\n(29358) [Mon Oct  5 09:26:20 2015] KID (confluence_sync) Delta count for source_db.public.bandana                          : 2\n(29358) [Mon Oct  5 09:26:21 2015] KID (confluence_sync) Totals: deletes=2 inserts=2 conflicts=0\n(29358) [Mon Oct  5 09:46:02 2015] KID (confluence_sync) Delta count for source_db.public.bandana                          : 2\n(29358) [Mon Oct  5 09:46:04 2015] KID (confluence_sync) Totals: deletes=2 inserts=2 conflicts=0\n(29358) [Mon Oct  5 10:36:18 2015] KID (confluence_sync) Delta count for source_db.public.extrnlnks                        : 1\n(29358) [Mon Oct  5 10:36:18 2015] KID (confluence_sync) Totals: deletes=1 inserts=1 conflicts=0\n(29358) [Mon Oct  5 10:46:02 2015] KID (confluence_sync) Delta count for source_db.public.bandana                          : 2\n(29358) [Mon Oct  5 10:46:03 2015] KID (confluence_sync) Totals: deletes=2 inserts=2 conflicts=0\n(29358) [Mon Oct  5 11:03:08 2015] KID (confluence_sync) Delta count for source_db.public.logininfo                        : 1\n(29358) [Mon Oct  5 11:03:08 2015] KID (confluence_sync) Delta count for source_db.public.cwd_user_attribute               : 1\n(29358) [Mon Oct  5 11:03:09 2015] KID (confluence_sync) Totals: deletes=2 inserts=2 conflicts=0\n(29358) [Mon Oct  5 11:06:22 2015] KID (confluence_sync) Delta count for source_db.public.\"AO_92296B_AORECENTLY_VIEWED\"    : 1\n(29358) [Mon Oct  5 11:06:23 2015] KID (confluence_sync) Totals: deletes=1 inserts=1 conflicts=0\n(29358) [Mon Oct  5 11:06:44 2015] KID (confluence_sync) Delta count for source_db.public.\"AO_92296B_AORECENTLY_VIEWED\"    : 1\n(29358) [Mon Oct  5 11:06:44 2015] KID (confluence_sync) Totals: deletes=1 inserts=1 conflicts=0\n(29358) [Mon Oct  5 11:46:02 2015] KID (confluence_sync) Delta count for source_db.public.bandana                          : 2\n(29358) [Mon Oct  5 11:46:03 2015] KID (confluence_sync) Totals: deletes=2 inserts=2 conflicts=0\n(29358) [Mon Oct  5 12:46:02 2015] KID (confluence_sync) Delta count for source_db.public.bandana                          : 2\n(29358) [Mon Oct  5 12:46:03 2015] KID (confluence_sync) Totals: deletes=2 inserts=2 conflicts=0\n<\/code><\/pre>\n
Useful thing we can see here is that full confluence sync takes 2 minutes and 5 seconds.<\/p>\n
DB Monitoring<\/h2>\n
We can use the tail_n_mail<\/code> script provided by Bucardo to monitor the source database (https:\/\/bucardo.org\/wiki\/Tail_n_mail). Install the script:<\/p>\n
root@server1:~# wget -o \/usr\/local\/bin\/tail_n_mail.pl http:\/\/bucardo.org\/downloads\/tail_n_mail\nroot@server1:~# chmod +x \/usr\/local\/bin\/tail_n_mail.pl\n<\/code><\/pre>\n
Setup the error log file in \/etc\/postgresql\/9.3\/main\/postgresql.conf<\/code>:<\/p>\n
[..]\nlog_destination = 'stderr'        # Valid values are combinations of\nlogging_collector = on            # Enable capturing of stderr and csvlog\nlog_directory = '\/var\/log\/postgresql'\nlog_filename = 'postgresql-%Y-%m-%d.log'\nlog_rotation_size = 10MB        # Automatic rotation of logfiles will\nlog_line_prefix='%t [%p] %u@%d '\n[..]\n<\/code><\/pre>\n
Create rc file \/root\/.tailnmailrc<\/code> containing the log_line_prefix<\/code> setting from above:<\/p>\n
log_line_prefix='%t [%p] %u@%d '\n<\/code><\/pre>\n
Generate config file:<\/p>\n
root@server1:~# tail \/usr\/local\/bin\/tail_n_mail.pl > tnm.config.txt\n<\/code><\/pre>\n
and modify it to suit us:<\/p>\n
$ sudo vi \/root\/tnm.config.txt\n## Config file for the tail_n_mail program\n## This file is automatically updated\nEMAIL: igorc@encompasscorporation.com\nMAILSUBJECT: Encompass HOST Postgres errors UNIQUE : NUMBER\n\nFILE: \/var\/log\/postgresql\/postgresql-%Y-%m-%d.log\nINCLUDE: ERROR: \nINCLUDE: FATAL: \nINCLUDE: PANIC:\n<\/code><\/pre>\n
Test run:<\/p>\n
root@server1:~# perl \/usr\/local\/bin\/tail_n_mail.pl tnm.config.txt\n<\/code><\/pre>\n
and I received an email, all working fine. Finally create a cron job that runs every 5 minutes:<\/p>\n
*\/5 * * * * perl \/usr\/local\/bin\/tail_n_mail.pl \/root\/tnm.config.txt\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
In cases where we can’t use the built-in PostgreSQL replication facility, like for example Confluence DB which has replication protection, Bucardo is very efficient option. It is an asynchronous PostgreSQL replication system, allowing for both multi-master and multi-slave operations. Bucardo…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-388","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/388","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=388"}],"version-history":[{"count":5,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/388\/revisions"}],"predecessor-version":[{"id":393,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/388\/revisions\/393"}],"wp:attachment":[{"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icicimov.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}