Репликация PostgreSQL+Bucardo. Рабочий вариант №1

Итак, после трех черновиков наступило время промышленной эксплуатации.

Описание задачи

Херсонская область. 19 структурных подразделений энергосбыта. Биллинг бытовых и промышленных потребителей. БД крутится в каждом филиале на SLES9 + PostgreSQL 8.2.5. Структура БД везде одинакова. Первичные ключи организованы таким образом, что уникальны не только в пределах филиала, но и области в целом. Каналы связи хреновые. 2 района с более-менее человеческой связью, 5 вообще только дозвон, остальные одно название. Есть головная контора, Херсоноблэнерго. Задача в том, чтоб филиалы, имеющие связь, реплицировались в головную контору с целью собрать общую БД быта и прома по области. Районы с дозвоном в данном мероприятии не участвуют, естественно.


Выбор компонентов

ОС центральной БД - SLES10 SP2 (SuSE Linux Enterprise Server 10 Service Pack 2)
БД - PostgreSQL 8.2.5 (так как во всех районах используется именно эта версия)
Репликация - Bucardo 3.0.9

Конфигурация сервера

IP-адрес сервера 10.77.11.71

Аппаратную часть не описываю - не актуально. Со временем сервер поменяем на более мощный.
Большую часть ПО ставил по умолчанию.

1. Доставил пакеты orarun, gtk-devel
2. Ни один из компонентов postgresql не устанавливал - версия не устраивает. Будем ставить нужную руками.
3. Целиком установил группу "C/C++ Compiler and Tools"
4. Что касается perl и его модулей... Вот листинг команды "rpm -qa | grep perl | sort"

limal-ca-mgm-perl-1.1.74-0.3
limal-nfs-server-perl-1.1.72-0.3
limal-perl-1.1.72-0.3
perl-5.8.8-14.7
perl-Bit-Vector-6.4-13.5
perl-Bootloader-0.4.19.12-0.3
perl-Carp-Clan-5.3-13.5
perl-Compress-Zlib-1.35-14.2
perl-Config-Crontab-1.11-12.2
perl-Config-IniFiles-2.39-13.4
perl-Crypt-SmbHash-0.12-13.2
perl-Date-Calc-5.4-14.5
perl-Digest-MD4-1.5-13.2
perl-Digest-SHA1-2.10-15.2
perl-PDA-Pilot-0.11.8-138.2
perl-Parse-RecDescent-1.80-259.2
perl-TermReadKey-2.30-13.2
perl-TimeDate-1.16-136.2
perl-URI-1.35-15.2
perl-X500-DN-0.28-133.2
perl-XML-Parser-2.34-43.2
perl-XML-Writer-0.600-13.2
perl-gettext-1.05-13.2
sax2-libsax-perl-7.1-125.54
yast2-perl-bindings-2.13.11-0.22

Возможно, среди тем модулей которые мы будем ставить будут повторения того, что уже установлено. Неважно. Следуем инструкция по установке приведенным ниже.

Предвариловка

В каталоге /files/install находится все, необходимое для установки и настройки.

/files/install/bucardo/perl_modules - набор perl-овских модулей, необходимых для установки и работы bucardo
/files/install/bucardo/Bucardo-3.0.9.tar.gz - собственно сам bucardo
/files/install/postgresql - rpm пакеты с postgresql 8.2.5. Вот их листинг:

postgresql-8.2.5-4.1.i586.rpm
postgresql-contrib-8.2.5-4.1.i586.rpm
postgresql-debuginfo-8.2.5-4.1.i586.rpm
postgresql-devel-8.2.5-4.1.i586.rpm
postgresql-docs-8.2.5-4.1.i586.rpm
postgresql-libs-8.2.5-4.1.i586.rpm
postgresql-pl-8.2.4-2.nosrc.rpm
postgresql-plperl-8.2.4-2.i586.rpm

Принятые условные обозначения

/dir/anotherdir/filename.fff - обозначение файлов и каталогов, листинг файлов
root# - обозначение консоли, открытой под root
postgres> - обозначение консоли, открытой под непривилегированным пользователем (в данном случае postgres)
rpm -qa | grep perl | sort - команда, выполняемая в каком-либо интерпретаторе (bash, psql...), результаты, возвращенные работой команды...

PostgreSQL

Устанавливаем PostgreSQL:

root# cd /files/install/postgresql
root# rpm -ivh *

Устанавливаем необходимые параметры ядра:

root# echo "kernel.shmmax = 1000000000" >> /etc/sysctl.conf
root# sysctl kernel.shmmax=1000000000

Делаем первый запуск PostgreSQL:

root# /etc/init.d/postgresql start

Редактируем конфиги postgresql:

Листинг /var/lib/pgsql/data/pg_hba.conf:

local all all ident sameuser
host all all 127.0.0.1/32 ident sameuser
host all all 0.0.0.0/0 password

Листинг /var/lib/pgsql/data/postgresql.conf:

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'
max_connections = 100
superuser_reserved_connections = 5
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
max_fsm_pages = 153600
max_stack_depth = 2MB
shared_buffers = 500MB
temp_buffers = 30MB
work_mem = 5MB
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
fsync = off
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
constraint_exclusion = on
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_line_prefix = '%s %h %u '
log_min_duration_statement = 60000
log_min_error_statement = warning
redirect_stderr = on
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = off
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
datestyle = 'iso, dmy'
lc_messages = 'ru_RU.UTF-8'
lc_monetary = 'ru_RU.UTF-8'
lc_numeric = 'ru_RU.UTF-8'
lc_time = 'ru_RU.UTF-8'
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

Перезапускаем PostgreSQL с новыми параметрами:

root# /etc/init.d/postgresql restart

После этого ставим PostgreSQL на автозапуск в YaST.
Меняем пароль пользователя postgres в БД:

root# su - postgres
postgres> psql
postgres=# ALTER ROLE postgres PASSWORD 'postgres';
postgres=# \q
postgres> exit

Создаем БД, в которую будут реплицироваться районы

root# mkdir /u02/pgdata/res -p
root# chown postgres:postgres /u02/pgdata -R
root# su - postgres
postgres> psql
postgres=# CREATE ROLE res LOGIN PASSWORD 'respasswd';
postgres=# ALTER ROLE res SUPERUSER;
postgres=# CREATE TABLESPACE res OWNER res LOCATION '/u02/pgdata/res';
postgres=# CREATE DATABASE res WITH ENCODING='UTF8' OWNER=res TABLESPACE=res;
postgres=# \q
postgres> psql -h 10.77.11.71 -U res -W 'res'
res=# CREATE SCHEMA res AUTHORIZATION res;
res=# CREATE LANGUAGE plpgsql;
res=# CREATE LANGUAGE plperlu;
res=# \q
postgres> exit

Bucardo

Модули ставим в следующей последовательности:

Pod-Escapes-1.04
Pod-Simple-2.06
Test-Pod-1.00
Net-Daemon-0.27
PlRPC-0.2020
DBI-1.51
ExtUtils-CBuilder-0.24
ExtUtils::MakeMaker 6.32
IO-1.2301
Storable-2.16
POE-Test-Loops-1.002
Curses-1.13
Event-1.10
Gtk-Perl-0.7009 (perl Makefile.PL --without-guessing). Модуль загадочный... Дохрена всего нужно проставить из Gnome. В принципе пакет опциональный, так что с ним не заморачиваемся, ставим с указанной опцией. Но в уме держим, что модуль установили ненастроенный.)
IO-Tty-1.02
Test-Pod-1.14
HTML-Tagset-3.20
HTML-Parser-3.56
libwww-perl-5.800 (на все вопросы отвечаем утвердительно)
Socket6-0.15
Tk-804.027 (make test нужно делать в X-ах, я например открыл VNC соединение, и тесты делал в нем, иначе все тесты провалятся)
Devel-Symdump-2.08
Pod-Coverage-0.19
Test-Pod-Coverage-1.06
POE-1.003 (make test нужно делать в X-ах, я например открыл VNC соединение, и тесты делал в нем, иначе все тесты провалятся)
Number-Compare-0.01
Text-Glob-0.08
File-Find-Rule-0.30
PathTools-3.2701
Test-Pod-Coverage-1.08
Test-Simple-0.80
Module-CoreList-2.15
Digest-SHA-5.47
Module-Signature-0.55
ExtUtils-ParseXS-2.19
version-0.76
Compress-Raw-Zlib-2.012
IO-Compress-Base-2.012
Compress-Raw-Bzip2-2.012
IO-Compress-Bzip2-2.012
IO-Compress-Zlib-2.012
Compress-Zlib-2.012 (make install UNINST=1 - так как у нас этот модуль уже установлен, но версия не та, то ставим именно так)
IO-Zlib-1.09
Package-Constants-0.01
Archive-Tar-1.40
Pod-Simple-3.07
podlators-2.2.0
Regexp-Common-2.122
Pod-Readme-0.09
Test-Harness-2.62
PAR-Dist-0.40
Archive-Zip-1.26
Module-Build-0.30 (t/use_tap_harness.....skipped all skipped: TAP::Parser not installed говорит, хотя он есть в установленном модуле Test-Harness. Проигнорируем...)
Test-Distribution-1.24
Test-Portability-Files-0.05
Test-Taint-1.04
Readonly-1.03
Readonly-XS-1.04
Params-Validate-0.91
Time-modules-2006.0814
File-MMagic-1.27
MIME-Types-1.24
Module-Find-0.05
Sub-Uplevel-0.18
Array-Compare-1.16
Test-Exception-0.27
Tree-DAG_Node-1.06
Test-Warn-0.11
Archive-Any-0.0932
Class-Accessor-0.31
Algorithm-Diff-1.1902
Array-Diff-0.04
CPAN-DistnameInfo-0.06
File-Slurp-9999.13
IO-Capture-0.05
List-MoreUtils-0.22 (при установке ругнулся, что Test::Pod not installed, но я его ставил. Игнорируем, но держим в уме, что модуль проблемный)
Test-Tester-0.103
Test-NoWarnings-0.084
Test-Deep-0.103
UNIVERSAL-require-0.11
Pod-Strip-1.02
Parse-RecDescent-1.94
Module-ExtractUse-0.23
Module-Pluggable-2.96
Params-Util-0.33
Sub-Install-0.924
Data-OptList-0.103
Sub-Exporter-0.980
Data-Section-0.005 (распаковывать обязательно при помощи tar -xvzf)
Text-Template-1.45
Software-License-0.008
YAML-0.62
Devel-Leak-0.03
YAML-Syck-0.95
Test-YAML-Valid-0.03
Test-YAML-Meta-0.11
Text-CSV_XS-0.57
Module-CPANTS-Analyse-0.82 (make test работает очень долго. Это нормально)
Test-Kwalitee-1.01 (Как его проставить - ХЕЗ. makefile.PL отсутствует как класс. Делаем так. Сначала под alexsf выполняем perl Build.PL, потом Build, потом под root копируем содержимое blib/lib в /usr/lib/perl5/site_perl/5.8.8, и Test::Kwalitee.3pm в /usr/share/man/man3)
POE-Component-Server-Syslog-1.16 (t/99_test_kwalitee....ok, значит предыдущий пакет поставлен правильно :) )
Sys-Syslog-0.27
Sub-Name-0.02
Sub-Identify-0.03
SUPER-1.16
Algorithm-C3-0.07
Class-C3-XS-0.07
Class-C3-0.19
Class-MOP-0.37
Test-LongString-0.11
Locale-US-1.2
Params-Coerce-0.14
Business-ISBN-Data-20081020
GD-Barcode-1.15
Business-ISBN-2.04 (сказал, что GD::Font не загружен. Такого не нашел. Игнорируем и ставим)
URI-1.37
IO-String-1.08
IO-stringy-2.110
DBM-Deep-1.0013 (ругнулся на несколько тестов, но игнорируем и ставим)
Test-Output-0.12 (ошибки игнорируем)
DateTime-Locale-0.41
Class-Singleton-1.03
DateTime-TimeZone-0.83 (Ругается, но ставим)
DateTime-0.4305 (некоторые тесты не прошли из-за отсутствия пакетов, которые проставлю дальше. Эти пакеты требуют, чтоб DateTime уже был установлен)
Set-Infinite-0.63
DateTime-Set-0.25
DateTime-Event-Recurrence-0.16
DateTime-Event-ICal-0.09
DateTime-Format-ICal-0.09
DateTime-Format-Strptime-1.0800
Class-Factory-Util-1.7
Task-Weaken-1.02
Test-Signature-1.10
DateTime-Format-HTTP-0.37
DateTime-Format-Mail-0.3001
DateTime-Format-IBeat-0.161
PadWalker-1.7
Devel-Cycle-1.10
Test-Memory-Cycle-1.04
DateTime-Format-Builder-0.7901
DateTime-Format-MySQL-0.04
Class-Inspector-1.23
aliased-0.22
Declare-Constraints-Simple-0.03
Moose-0.18
Mail-Sendmail-0.79 (ругается при тесте, что не может соединиться с сервером, но это нормально, игнорируем и ставим)
B-Keywords-1.08
Config-Tiny-2.12
Class-Data-Inheritable-0.08
Devel-StackTrace-1.12
Exception-Class-1.23
Module-Pluggable-3.1
Taint-Runtime-0.03
Clone-0.25
Scalar-List-Utils-1.19
Test-Object-0.07
PPI-1.203
String-Format-1.14
File-HomeDir-0.82
File-Which-0.05
Perl-Tidy-20071205
Pod-Spell-1.01
Regexp-Parser-0.20
Perl-Critic-1.092
Perl-Critic-Bangs-1.00 (на тесте ругается на некоторые вещи, но ставим все равно)
Perl-Critic-Lax-0.007
Test-Dynamic-1.3.3

DBD-Pg-1.49

Как этот модуль проверяется:

В pg_hba.conf меняем на:

local all all password
host all all 127.0.0.1/32 password
host all all 0.0.0.0/0 password

Устанавливаем переменные окружения:
user> export DBI_PASS=postgres
user> export TEST_VERBOSE=1
user> export DBI_USER=postgres
user> export DBI_DSN='dbi:Pg:dbname=postgres'

Перезапускаем PostgreSQL и только после этого делаем make test. Потом возвращаем pg_hba.conf на место.

Конфигурируем PostgreSQL под bucardo:

root# su - postgres
postgres> mkdir /u02/pgdata/bucardo -p
postgres> psql
postgres=# CREATE USER bucardo SUPERUSER;
postgres=# ALTER ROLE bucardo password 'bucardo';
postgres=# CREATE TABLESPACE bucardo OWNER bucardo LOCATION '/u02/pgdata/bucardo';
postgres=# CREATE DATABASE bucardo WITH ENCODING='UTF8' OWNER=bucardo TABLESPACE=bucardo;
postgres=# CREATE LANGUAGE plperlu;
postgres=# \q
postgres> exit
root# cd /files/install/bucardo
root# tar -xvzf Bucardo-3.0.9.tar.gz
root# su - postgres
postgres> cd /files/install/bucardo/Bucardo/Bucardo-3.0.9/
postgres> psql -h 10.77.11.71 -U bucardo bucardo
bucardo=# CREATE LANGUAGE plpgsql;
bucardo=# CREATE LANGUAGE plperlu;
bucardo=# \q
postgres> psql -f bucardo.schema -h 10.77.11.71 -U bucardo bucardo
postgres> exit

Внимательно смотрим, чтобы скрипт bucardo.schema выполнился без ошибок, и только после этого идем дальше.

В каталоге /files/install/bucardo/Bucardo/Bucardo-3.0.9 есть модуль DBIx-Safe-1.2.4. Устанавливаем его. make test не делаем.

Затем из этого же каталога устанавливаем модуль Bucardo-3.0.9. Перед установкой делаем следующее:

1. В скриптах bucardo-report, bucardo_ctl.rc, bucardo_rrd, check_bucardo_sync, Bucardo.pm, bucardo_ctl меняем в самом начале файла строку

#!/usr/localbin/perl -- -*-cperl-*-

на строку

#!/usr/bin/perl -- -*-cperl-*-

2. Экспортируем нелобходимые для теста переменные окружения:

user> export DBI_DSN='dbi:Pg:dbname=bucardo'
user> export DBI_USER=bucardo
user> export DBI_PASS=bucardo
user> export TEST_VERBOSE=1

3. PostgreSQL должен быть настроен на время теста на коннект к базе локальных соединений через пароль. То есть в pg_hba.conf первыми строками д.б.:

local all all password
host all all 127.0.0.1/32 password

4. Редактируем файл Bucardo-3.0.9/t/bucardo.test.data. Следующие строки должны быть такими:

DBNAME: bucardo
DBUSER: bucardo
DBHOST: 10.77.11.71

Только после этого ставим модуль:

perl Makefile.PL
make
make test TEST_VERBOSE=1

Во время теста могут быть некоторые ошибки связанные с тестовыми базами, которые мы не настраивали. Игнорируем. Во время таста были созданы тестовые базы bucardo_test, bucardo_test1, bucardo_test2, bucardo_test3. После установки модуля их можно удалить.

Поднастраиваем postgres. Файл pg_hba.conf должен выглядеть так:

#local all all ident sameuser
#host all all 127.0.0.1/32 ident sameuser
#host all all 0.0.0.0/0 password
local all all password
host all all 127.0.0.1/32 password
host all all 0.0.0.0/0 password

Если нужно будет локально подключиться под postgres без ввода пароля, то закоментированные строки разкомментируем, незакомментированные комментируем, перезапускаем postgresql, и логинимся. Но потом возвращаем все на место, иначе bucardo работать не будет!

Ставим bucardo на место:

root# cp /files/install/bucardo/Bucardo/Bucardo-3.0.9 /opt -R

Редактируем файл /opt/Bucardo-3.0.9/bucardo_ctl. Устанавливаем в секции my $bcargs следующие значения:

my $bcargs = {
ctlquiet => 0,
ctlverbose => 0,
dbname => 'bucardo',
dbuser => 'bucardo',
dbpass => 'bucardo',
verbose => 1, ## Highly recommended to leave this on
sendmail => 0,
extraname => '',
debugfilesep => 0,
debugname => '',
debugstderr => 0,
debugstdout => 0,
debugsyslog => 1,
debugdir => '',
debugfile => 0,
cleandebugs => 1,
};

Создаем локального пользователя bucardo и настраиваем:

root# useradd -c "Bucardo Replication" -d /home/bucardo -p bucardo_bucardo -s /bin/bash bucardo
root# mkdir /home/bucardo
root# chown bucardo /home/bucardo
root# mkdir /var/run/bucardo
root# chown bucardo /var/run/bucardo
root# mkdir /var/log/bucardo
root# chown bucardo /var/log/bucardo

Создаем скрипты для запуска и останова bucardo:

root# echo '#!/bin/bash' >> /etc/init.d/bucardo_start
root# echo '/opt/Bucardo-3.0.9/bucardo_ctl start "Start $(date +%Y-%m-%d.%H.%M.%S)"' >> /etc/init.d/bucardo_start
root# echo '#!/bin/bash' >> /etc/init.d/bucardo_stop
root# echo '/opt/Bucardo-3.0.9/bucardo_ctl stop "Stop $(date +%Y-%m-%d.%H.%M.%S)"' >> /etc/init.d/bucardo_stop
root# chmod oug+x /etc/init.d/bucardo_start
root# chmod oug+x /etc/init.d/bucardo_stop
root# ln -s /etc/init.d/bucardo_stop /etc/init.d/rc3.d/K10bucardo
root# ln -s /etc/init.d/bucardo_stop /etc/init.d/rc5.d/K10bucardo

Так как нам не нужно, чтобы bucardo стартовал при запуске системы, линки на автозапуск не делаем. Только на останов.

Настраиваем syslog-ng (у нас не используется старый syslog) на логирование bucardo. Редактируем файл /etc/syslog-ng/syslog-ng.conf.in.

В секцию # Filter definition добавляем:

filter f_bucardo {match('ucardo');};

Этот фильтр обязательно должен быть установлен до определения фильтра f_messages, который мы меняем следующим образом:

filter f_messages { not facility(news, mail) and not filter(f_iptables) and not filter(f_bucardo); };

В самый конец добавляем:

destination d_bucardo {file(var/log/bucardo/bucardo.log);};
log {source(src);filter(f_bucardo);destination(d_bucardo);};

Потом в консоли:

root# SuSEconfig
root# /etc/init.d/syslog reload
root# mkdir /var/log/arhiv/bucardo -p

Настраиваем ротацию логов. Создаем файл /etc/logrotate.d/bucardo со следующим содержимым:

/var/log/bucardo/* {
dateext
compress
copytruncate
daily
missingok
notifempty
olddir /var/log/arhiv/bucardo
rotate 100
}

Так, с установкой bucardo вроде все. Теперь настраиваем собственно репликацию.

Настройка репликации. Добавление первой подчиненной БД.

Для начала, нам нужно на центральном сервере в ранее созданой БД сделать схему, аналогичную той, что будет реплицироваться из районов. Bucardo, к сожалению, не умеет реплицировать DDL, так что схему придется сделать руками. Выливаем ее из БД одного из районов, который имеет честь быть первым :). По логике вещей, схема во всех районах идентичная. Если нет, будет терзать программистов :). Ну и заливаем ее в БД res на хосте, где будет крутиться bucardo. Ну и малеко конфигурируем подчиненную БД. Все действия выполняются на том хосте, где будет крутиться bucardo.

root# su - postgres
postgres> mkdir ~/work
postgres> cd ~/work
postgres> pg_dump -h 10.77.25.3 -p 5432 -U postgres -F p -s -s -v -f "skres_schema.sql" -n 'res' "UTF8_sk"
postgres> psql -f skres_schema.sql -h 10.77.11.71 -U postgres res
postgres> psql -h 10.77.25.3 -U postgres UTF8_sk
UTF8_sk=# ALTER ROLE res SUPERUSER;
UTF8_sk=# CREATE LANGUAGE plperlu;
UTF8_sk=# CREATE LANGUAGE plpgsql;
UTF8_sk=# \q
postgres> exit

Теперь нам необходимо оптимизировать схему для заливки всех возможных районов. Что значит оптимизировать — удалить constraints и rules, свойственные каждому конкретному РЭСу. Иначе у нас в такие таблицы кроме одного конкретного РЭСа ни один не сможет данные записать, так как они не будут проходить проверку. На головной базе res выполняем запросы, а потом выполняем результаты этих запросов.

SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
FROM pg_constraint con, pg_class cl, pg_tables t, information_schema.constraint_column_usage cc
WHERE t.schemaname = 'res'
AND t.tablename = cl.relname
AND con.contype = 'c'
AND con.conrelid = cl.oid
AND cc.constraint_name = con.conname
AND cl.relname not in ('billsqlreport','dual','rp_abon')
ORDER BY con.conname;

SELECT 'DROP RULE '||rulename||' ON '||tablename||';'
FROM pg_rules
WHERE schemaname = 'res'
ORDER BY tablename, rulename;

Прежде, чем выполнять результаты запросов обязательно проверяем, что туда не залетело ничего левого, то есть того, что не должно быть удалено.

Сливаем дамп базы на сервере в районе и вытягиваем его на сервер с bucardo:

root# ssh root@10.77.25.3
root@remote# su - postgres -c "cd /files/ftp; pg_dump -C -F c -b -f pgsql.UTF8_sk "UTF8_sk""
root@remote# exit
root# wget -c --ftp-user=sit --ftp-password=shdgasd ftp://10.77.25.3/pgsql.UTF8_sk

Естественно, здесь подразумевается, что на РЭСовском сервере поднят FTP.

Ну а теперь непосредственно bucardo. Вносим информацию в БД bucardo. Для этого при помощи любого доступного SQL менеджера для PostgreSQL подключаемся к базе bucardo под пользователем bucardo и выполняем:

Описание всех участвующих в процессе репликации БД. Сюда вносятся как подчиненные БД, так и главная БД.

INSERT INTO bucardo.db (name, dbhost, dbname, dbuser, dbpass)
VALUES ('skres','10.77.25.3','UTF8_sk','res','supersecretpasswordofresinskres');

INSERT INTO bucardo.db (name, dbhost, dbname, dbuser, dbpass)
VALUES ('allres','10.77.11.71','res','res','respasswd');

Описание групп БД. Группы БД позволят управлять репликацией не отдельно каждой БД, а наборами БД, объединенными в группы. В принципе, логично было бы все подчиненные БД кинуть в отдельную группу, главную БД в отдельную группу. Так как я пока не знаю, что будет лучше, то так и сделаю.

INSERT INTO bucardo.dbgroup(name) VALUES('slave');
INSERT INTO bucardo.dbgroup(name) VALUES('master');

Привязываем БД к группам БД.

INSERT INTO bucardo.dbmap(db, dbgroup) VALUES('skres','slave');
INSERT INTO bucardo.dbmap(db, dbgroup) VALUES('allres','master');

Вносим объекты, которые будут участвовать в репликации. К таким объектам относятся только таблицы. Так как таблиц у нас очень много, больше 400 штук на данный момент, то вставку будем осуществлять массировано при помощи скрипта. Выполняем его на подчиненной БД, а результаты в подключенной ранее БД bucardo. Обратите внимание, что часть таблиц из запроса исключаются. Это те, которые не участвуют в процессе репликации.

SELECT 'INSERT INTO bucardo.goat (db,schemaname,tablename,pkey,pkeytype,analyze_after_copy) VALUES (''skres'',''res'','''||cl.relname||''','''||cc.column_name||''',''bigint'',''false'');'
FROM pg_constraint con, pg_class cl, pg_tables t, information_schema.constraint_column_usage cc
WHERE t.schemaname = 'res'
AND t.tablename = cl.relname
AND con.contype = 'p'
AND con.conrelid = cl.oid
AND cc.constraint_name = con.conname
AND cl.relname not in ('billsqlreport','dual','rp_abon')
ORDER BY cl.relname;

ВНИМАНИЕ!!! Возможны составные первичные ключи на таблицах, чего для работы репликации быть не должно. Поэтому перед выполнением вышеуказанного запроса ищем все таблицы, PK в которые состоит более чем из одного поля и что-то с ними решаем!!! Ищем эти таблицы следующим образом:

SELECT cl.relname, count(*)
FROM pg_constraint con, pg_class cl, pg_tables t, information_schema.constraint_column_usage cc
WHERE t.schemaname = 'res'
AND t.tablename = cl.relname
AND con.contype = 'p'
AND con.conrelid = cl.oid
AND cc.constraint_name = con.conname
AND cl.relname not in ('billsqlreport','dual','rp_abon')
GROUP BY cl.relname
HAVING count(*) > 1;

Создаем так называемый herd для Скадовской БД. Для каждой последующей необходимо будет создавать свой. Herd — это группа объектов(goat), которые мы вносили в bucardo в предыдущем запросе.

INSERT INTO bucardo.herd(name) VALUES('herd_skres');

Сопоставляем существующие goat созданному herd.

INSERT INTO bucardo.herdmap (herd, goat)
SELECT 'herd_skres', id
FROM goat WHERE db = 'skres';

Ну и напоследок создаем правила синхронизации:

INSERT INTO bucardo.sync (name,source,targetdb,synctype,stayalive,checktime,analyze_after_copy)
VALUES('sync_skres','herd_skres','allres','pushdelta','false','60 minutes','false');

Во-о-о-от...
А потом оказалось, что таблицу log из процесса репликации необходимо исключить... Делаем это так:

DELETE FROM goat WHERE db='skres' AND tablename = 'log';

Но при этом остаются триггера и ссылки на таблицу в районской схеме bucardo... Смотрим в схеме bucardo в РЭСе созданные индексы на таблице bucardo_delta. Ищем среди них те, что созданы для таблицы log. Определяем, что tablename = 24926

DROP TRIGGER bucardo_add_delta_d ON res.log;
DROP TRIGGER bucardo_add_delta_i ON res.log;
DROP TRIGGER bucardo_add_delta_u ON res.log;
DROP TRIGGER bucardo_triggerkick_sync_skres ON res.log;

DELETE FROM bucardo.bucardo_delta
WHERE tablename = 24926;

DELETE FROM bucardo.bucardo_delta_targets
WHERE tablename = 24926;

DROP INDEX bucardo.bucardo_delta_res_log_rowid;
DROP INDEX bucardo.bucardo_delta_res_log_txn;

Исключили. Если еще какую-нибудь таблицу придется исключать, действуем по такому же принципу.

Всё, bucardo практически настроен. Теперь нам нужно залить бэкап РЭСа, только данные. После этого мы запустим bucardo и он начнет на них наворачивать изменения, произошедшие в удаленной базе после добавления правила синхронизации. Для того, чтоб залить из бэкапа только данные, нам нужно удалить все FK, залить бэкап, и восстановить все FK. Все просто.

Для удаления FK воспользуемся следующим запросом, результаты которого выполним в БД res на центральном сервере:

SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
FROM pg_constraint con, pg_class cl, pg_tables t, information_schema.constraint_column_usage cc
WHERE t.schemaname = 'res'
AND t.tablename = cl.relname
AND con.contype = 'f'
AND con.conrelid = cl.oid
AND cc.constraint_name = con.conname
ORDER BY con.conname;

Теперь заливаем из бэкапа только данные (я не помню, в каком каталоге находится pgsql.UTF8_sk, будем считать, что в ~/work относительно домашнего каталога пользователя postgres):

root# su - postgres
postgres> cd ~/work
postgres> /usr/bin/pg_restore -h 10.77.11.71 -p 5432 -U res -d res -a -v "pgsql.UTF8_sk"

Ну и восстанавливаем FK:

postgres> grep FOREIGN -B 1 skres_schema.sql > restore_fk.sql
postgres> psql -f restore_fk.sql -h 10.77.11.71 -U postgres res

Если необходимо все нахрен вычистить!!!

DELETE FROM bucardo.sync;
DELETE FROM bucardo.herdmap;
DELETE FROM bucardo.herd;
DELETE FROM bucardo.goat;
DELETE FROM bucardo.dbmap;
DELETE FROM bucardo.dbgroup;
DELETE FROM bucardo.db;

А потом тупо выносим в подчиненной БД схему bucardo, вместе с ней уйдут все созданные bucardo объекты.

Изменения в структуре БД

И вот то, чего я ждал, наконец произошло... Структура БД поменялась. Как назло я был в отпуске, программисты мне об этом сказали уже постфактум...

Продолбался долго, в итоге наваял 2 поста:

Тестирование
Правила