Bucardo 4.x.x попытка 2

Привезли железку новую. Переносим bucardo на неё. Соответственно нужно его заново ставить...
На железке установили SLES11, обновился с novell.com. нашел репозитарий с кучей perl модулей, установил его. Проставил из него немеряную толпу этих самых модулей. Дело за малым :)


Вот репозитарии:

http://download.opensuse.org/repositories/M17N/SLE_11
http://download.opensuse.org/repositories/devel:/languages:/perl/SLE_11

Вот что я понаставлял (rpm -qa | grep perl | sort):

apache2-mod_perl-2.0.4-43.1
limal-ca-mgm-perl-1.5.6-1.15
limal-nfs-server-perl-1.5.2-1.24
limal-perl-1.5.2-1.24
perl-5.10.0-64.43.1
perl-Algorithm-Annotate-0.10-141.1
perl-Algorithm-C3-0.07-2.2
perl-Algorithm-CheckDigits-0.50-1.15
perl-Algorithm-Diff-1.1902-131.1
perl-AnyEvent-4.352-2.1
perl-Apache-AuthCookie-3.12-4.3
perl-Apache-AuthNetLDAP-0.29-222.4
perl-Apache-DBI-1.07-45.4
perl-Apache-Filter-1.024-215.4
perl-Apache-Session-1.87-4.4
perl-Apache-Session-Wrapper-0.33-2.28
perl-Apache-SessionX-2.01-220.4
perl-Apache2-AuthCookieDBI-2.05-114.1
perl-Apache2-Reload-0.10-1.1
perl-App-CLI-0.07-76.1
perl-AppConfig-1.66-83.1
perl-Archive-Zip-1.30-5.2
perl-Array-Compare-1.17-6.2
perl-Authen-SASL-2.12-22.5
perl-Authen-SASL-Cyrus-0.12-178.1
perl-B-Keywords-1.09-1.1
perl-BIND-Conf_Parser-0.97-490.2
perl-BSD-Resource-1.2903-2.2
perl-Barcode-Code128-2.01-2.6
perl-BerkeleyDB-0.39-4.2
perl-Biblio-EndnoteStyle-0.05-1.2
perl-Bit-Vector-6.6-4.2
perl-Bootloader-0.4.89.1-0.2.1
perl-Business-ISBN-2.05-3.3
perl-Business-ISBN-Data-20081208-2.5
perl-CDDB_get-2.27-142.1
perl-CGI-3.48-1.1
perl-CGI-Application-4.31-6.1
perl-CGI-Session-4.42-10.6
perl-CGI-Session-Serialize-yaml-4.24-1.28
perl-CGI-Simple-1.112-2.5
perl-CMS-MediaWiki-0.8013-2.18
perl-Cache-2.04-1.11
perl-Cache-Memcached-Fast-0.12-3.2
perl-Cairo-1.061-2.4
perl-Calendar-Simple-1.20-1.2
perl-Capture-Tiny-0.06-1.1
perl-Carp-Assert-0.20-108.1
perl-Carp-Clan-6.00-43.6
perl-Chart-2.4.1-179.5
perl-Class-Adapter-1.05-3.2
perl-Class-Autouse-1.29-59.1
perl-Class-Container-0.12-4.3
perl-Class-Data-Inheritable-0.08-50.2
perl-Class-ErrorHandler-0.01-5.1
perl-Class-Inner-0.1-1.2
perl-Class-Interfaces-0.04-1.15
perl-Class-Singleton-1.4-1.2
perl-Class-Std-0.0.9-2.5
perl-Class-StrongSingleton-0.02-1.2
perl-Class-Throwable-0.10-1.15
perl-Config-Crontab-1.30-4.2
perl-Config-General-2.44-7.1
perl-Config-IniFiles-2.52-4.2
perl-Config-Std-0.0.4-3.4
perl-Config-Tiny-2.12-1.2
perl-Convert-ASCII-Armour-1.4-3.2
perl-Convert-ASN1-0.22-2.2
perl-Convert-BER-1.3101-329.2
perl-Convert-BinHex-1.119-6.1
perl-Convert-TNEF-0.17-400.6
perl-Convert-UUlib-1.051-135.2
perl-Crypt-Blowfish-2.10-135.2
perl-Crypt-CBC-2.30-2.4
perl-Crypt-DES-2.05-135.2
perl-Crypt-DES_EDE3-0.01-4.1
perl-Crypt-DH-0.06-2.2
perl-Crypt-GeneratePassword-0.03-1.2
perl-Crypt-IDEA-1.08-2.2
perl-Crypt-OpenSSL-AES-0.02-3.2
perl-Crypt-OpenSSL-Bignum-0.04-2.2
perl-Crypt-OpenSSL-RSA-0.25-4.2
perl-Crypt-OpenSSL-Random-0.04-2.2
perl-Crypt-RandPasswd-0.02-2.1
perl-Crypt-SSLeay-0.57-46.1
perl-Crypt-SmbHash-0.12-136.1
perl-Curses-1.27-2.2
perl-CursesWidgets-1.997-142.1
perl-Cyrus-IMAP-2.3.11-60.21.1
perl-Cyrus-SIEVE-managesieve-2.3.11-60.21.1
perl-DBD-ODBC-1.22-6.1
perl-DBD-Pg-2.15.1-6.1
perl-DBD-SQLite-1.27-9.1
perl-DBD-mysql-4.012-6.1
perl-DBI-1.609-6.1
perl-Daemon-Generic-0.61-1.1
perl-Data-ShowTable-3.3-707.2
perl-Date-Calc-5.6-4.2
perl-Date-Manip-6.05-22.1
perl-DateTime-0.50-2.6
perl-DateTime-Format-DateParse-0.04-2.8
perl-DateTime-Format-Mail-0.3001-2.15
perl-DateTime-Format-Strptime-1.0901-3.5
perl-DateTime-Format-W3CDTF-0.04-2.14
perl-DateTime-Locale-0.42-4.4
perl-DateTime-TimeZone-0.91-5.2
perl-Devel-StackTrace-1.20-3.4
perl-Devel-Symdump-2.08-8.1
perl-Digest-HMAC-1.01-630.5
perl-Digest-MD4-1.5-135.2
perl-Digest-SHA1-2.12-6.1
perl-EV-3.8-1.1
perl-Email-Abstract-3.001-2.8
perl-Email-Address-1.889-1.1
perl-Email-Find-0.09-4.16
perl-Email-MIME-1.861-2.4
perl-Email-MIME-ContentType-1.014-3.2
perl-Email-MIME-Encodings-1.313-2.1
perl-Email-Simple-2.003-3.2
perl-Email-Valid-0.182-1.2
perl-Error-0.17015-2.14
perl-Event-1.11-2.2
perl-Exception-Class-1.29-2.4
perl-Expect-1.21-50.1
perl-Exporter-Lite-0.01-4.2
perl-ExtUtils-Depends-0.302-4.2
perl-ExtUtils-PkgConfig-1.12-2.2
perl-Feed-Find-0.06-3.20
perl-File-Basename-Object-0.01-41.1
perl-File-HomeDir-0.82-4.1
perl-File-Next-1.02-1.2
perl-File-Remove-1.42-1.1
perl-File-Tail-0.99.3-137.1
perl-File-Type-0.22-141.1
perl-File-Which-1.07-4.2
perl-Filter-1.37-4.2
perl-Font-AFM-1.20-26.1
perl-GD-2.44-6.1
perl-GD-Barcode-1.15-5.2
perl-Glib-1.221-2.4
perl-HTML-Clean-0.8-877.2
perl-HTML-Encoding-0.60-4.18
perl-HTML-Format-2.04-143.1
perl-HTML-FromText-2.05-5.26
perl-HTML-Parser-3.61-11.1
perl-HTML-Scrubber-0.08-1.16
perl-HTML-SimpleParse-0.12-258.2
perl-HTML-TableExtract-2.10-76.3
perl-HTML-TagParser-0.16.1-1.2
perl-HTML-Tagset-3.20-45.2
perl-HTML-Template-2.9-105.2
perl-HTML-Tree-3.23-108.1
perl-Heap-0.80-1.2
perl-Hook-LexWrap-0.22-1.1
perl-IO-1.25-2.2
perl-IO-Digest-0.10-143.2
perl-IO-Multiplex-1.10-2.2
perl-IO-Pager-0.06-73.1
perl-IO-Prompt-0.99.4-2.4
perl-IO-Socket-SSL-1.27-6.2
perl-IO-String-1.08-136.2
perl-IO-Stty-.02-656.1
perl-IO-Tty-1.08-8.1
perl-IO-stringy-2.110-136.2
perl-IOC-0.29-1.20
perl-Inline-0.45-4.2
perl-JSON-2.15-2.3
perl-JSON-XS-2.24-2.1
perl-LWP-Authen-Wsse-0.05-1.21
perl-List-MoreUtils-0.22-76.1
perl-Log-Dispatch-2.26-5.1
perl-Log-Dispatch-FileRotate-1.19-1.3
perl-Log-Log4perl-1.26-7.2
perl-Log-TraceMessages-1.4-5.25
perl-MIME-Types-1.27-2.2
perl-MIME-tools-5.427-30.2
perl-Mail-Address-2.02-1.9
perl-Mail-DKIM-0.33-6.3
perl-Mail-Sendmail-0.79-109.2
perl-MailTools-2.04-2.2
perl-Module-Find-0.06-1.15
perl-Module-Info-0.31-79.1
perl-Module-Signature-0.55-3.1
perl-Moose-0.92-1.1
perl-Net-DNS-0.65-5.1
perl-Net-Daemon-0.43-81.1
perl-Net-IP-1.25-49.1
perl-Net-SLP-1.3-1.2
perl-Net-SNMP-5.2.0-134.5
perl-Net-SSLeay-1.35-2.14
perl-Net-Server-0.97-66.2
perl-Net-Telnet-3.03-317.2
perl-Net-ext-1.011-142.1
perl-NetAddr-IP-4.026-8.2
perl-NetxAP-0.02-695.2
perl-Pango-1.221-5.1
perl-Params-Validate-0.91-26.2
perl-Parse-RecDescent-1.96.0-5.1
perl-Path-Class-0.16-74.1
perl-PathTools-3.2701-1.2
perl-PerlIO-eol-0.14-107.1
perl-PerlIO-gzip-0.17-6.2
perl-PlRPC-0.2020-4.1
perl-Pod-Coverage-0.20-68.7
perl-Pod-POM-0.25-2.4
perl-PostScript-Simple-0.07-136.1
perl-RPC-XML-0.67-7.1
perl-Readonly-1.03-1.1
perl-Regexp-Common-2.122-7.1
perl-Return-Value-1.302-3.2
perl-SNMP-5.4.2.1-8.1
perl-SQL-Statement-1.15-117.1
perl-Set-Object-1.27-2.2
perl-Set-Scalar-1.24-4.2
perl-Sort-Versions-1.5-2.2
perl-Spiffy-0.30-115.1
perl-String-Format-1.16-1.1
perl-Sub-Delete-0.02-1.1
perl-Sub-Install-0.925-4.4
perl-Sub-Name-0.04-3.1
perl-Sub-Uplevel-0.2002-5.1
perl-Symbol-Table-1.01-41.1
perl-Sys-Hostname-Long-1.4-10.2
perl-Sys-SigAction-0.11-3.1
perl-TermReadKey-2.30-138.2
perl-TermReadLine-Gnu-1.16-106.1
perl-Test-Base-0.59-10.1
perl-Test-Deep-0.106-2.1
perl-Test-Exception-0.27-44.3
perl-Test-Harness-3.17-4.1
perl-Test-LongString-0.11-1.15
perl-Test-Manifest-1.22_02-1.6
perl-Test-NoWarnings-0.084-3.1
perl-Test-Object-0.07-1.1
perl-Test-Pod-1.26-134.1
perl-Test-Pod-Coverage-1.08-125.3
perl-Test-Signature-1.10-1.2
perl-Test-SubCalls-1.09-1.1
perl-Test-Tester-0.107-4.1
perl-Test-Unit-0.25-1.13
perl-Test-Warn-0.21-24.1
perl-Text-Diff-1.37-2.1
perl-Text-Iconv-1.7-141.1
perl-Text-Scan-0.30-4.23
perl-Text-Template-1.44-10.2
perl-Text-Unidecode-0.04-1.2
perl-Text-Wrap-2009.0305-1.1
perl-Tie-Cache-0.17-380.2
perl-Tie-IxHash-1.21-725.1
perl-Time-Duration-1.06-69.1
perl-Time-Period-1.20-440.2
perl-Time-Piece-1.15-2.1
perl-Time-modules-2006.0814-127.1
perl-TimeDate-1.19-267.1
perl-Tk-804.028-56.1
perl-Tree-DAG_Node-1.06-44.2
perl-UNIVERSAL-require-0.11-74.1
perl-URI-1.38-8.1
perl-URI-Fetch-0.08-1.25
perl-URI-Template-0.13-1.15
perl-Unicode-String-2.09-135.2
perl-Unix-Syslog-0.100-156.1
perl-User-Identity-0.91-5.2
perl-WWW-Curl-4.09-5.1
perl-Want-0.18-2.1
perl-X500-DN-0.29-83.2
perl-XML-Atom-0.28-1.33
perl-XML-Bare-0.45-4.2
perl-XML-DOM-1.44-147.2
perl-XML-Entities-0.0307-2.1
perl-XML-Feed-0.12-2.34
perl-XML-LibXML-1.69-2.9
perl-XML-LibXML-Common-0.13-162.2
perl-XML-NamespaceSupport-1.10-140.3
perl-XML-NodeFilter-0.01-165.2
perl-XML-Parser-2.36-66.1
perl-XML-RSS-1.36-1.24
perl-XML-RegExp-0.03-613.2
perl-XML-SAX-0.96-5.2
perl-XML-Simple-2.18-65.5
perl-XML-Stream-1.22-158.2
perl-XML-Twig-3.32-59.5
perl-XML-Writer-0.606-2.2
perl-XML-XPath-1.13-76.6
perl-YAML-0.70-11.2
perl-YAML-Syck-1.07-5.2
perl-base-5.10.0-64.43.1
perl-bp-0.7-198.1
perl-common-sense-2.0-1.1
perl-doc-5.10.0-64.43.1
perl-gettext-1.05-142.1
perl-ldap-0.39-14.2
perl-ldap-ssl-0.39-14.2
perl-libwww-perl-5.830-14.2
perl-ptkdb-1.1091-1.4
perl-satsolver-0.13.9-0.1.1
perl-spamassassin-3.2.5-33.2
perlref-5.004.1-321.1
sax2-libsax-perl-8.1-561.15.1
yast2-perl-bindings-2.17.2-1.27

Кроме того, нужет postgres версии 8.2. Такой версии в rpm варианте для SLES11 нет. Так что собрал из исходников в /opt/pgsql-8.2 версию 8.2.6

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

fagot:~ # cd /u02/install/bucardo
fagot:/u02/install/bucardo # wget -c "http://bucardo.org/downloads/Bucardo-4.4.0.tar.gz"
fagot:/u02/install/bucardo # wget -c "http://bucardo.org/downloads/DBIx-Safe-1.2.5.tar.gz"
fagot:/u02/install/bucardo # tar -xvzf DBIx-Safe-1.2.5.tar.gz
fagot:/u02/install/bucardo # cd DBIx-Safe-1.2.5/
fagot:/u02/install/bucardo/DBIx-Safe-1.2.5 # perl Makefile.PL
fagot:/u02/install/bucardo/DBIx-Safe-1.2.5 # make
fagot:/u02/install/bucardo/DBIx-Safe-1.2.5 # make test
fagot:/u02/install/bucardo/DBIx-Safe-1.2.5 # make install
fagot:/u02/install/bucardo/DBIx-Safe-1.2.5 # cd ..
fagot:/u02/install/bucardo # tar -xvzf Bucardo-4.4.0.tar.gz
fagot:/u02/install/bucardo # cd Bucardo-4.4.0/

Тут немного прервемся и настроим PostgreSQL. Базовую настройку описывать не буду - 300 раз уже это делал...

fagot:/u02/install/bucardo/Bucardo-4.4.0 # ln -s /usr/lib/perl5/5.10.0/i586-linux-thread-multi/CORE/libperl.so /usr/lib/libperl.so
fagot:/u02/install/bucardo/Bucardo-4.4.0 # mkdir /u02/pgdata/bucardo -p
fagot:/u02/install/bucardo/Bucardo-4.4.0 # chown postgres:postgres /u02/pgdata/ -R
fagot:/u02/install/bucardo/Bucardo-4.4.0 # su - postgres
postgres@fagot:~> 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=# \q
postgres@fagot:~> psql -U bucardo bucardo
bucardo=# CREATE LANGUAGE plperlu;
bucardo=# CREATE LANGUAGE plperl;
bucardo=# CREATE LANGUAGE plpgsql;
bucardo=# \q
postgres@fagot:~> exit

Ну и ставим Bucardo. напомню, что мы находимся в каталоге /u02/install/bucardo/Bucardo-4.4.0

fagot:/u02/install/bucardo/Bucardo-4.4.0 # perl Makefile.pl
fagot:/u02/install/bucardo/Bucardo-4.4.0 # make
fagot:/u02/install/bucardo/Bucardo-4.4.0 # make install
fagot:/u02/install/bucardo/Bucardo-4.4.0 # mkdir /var/run/bucardo
fagot:/u02/install/bucardo/Bucardo-4.4.0 # bucardo_ctl install

Все, поставили. Теперь настроим немного:

fagot:/u02/install/bucardo/Bucardo-4.4.0 # cd ~
fagot:~ # bucardo_ctl set default_email_from=bucardo-fagot@co.ksoe.com.ua
fagot:~ # bucardo_ctl set default_email_to=admesto@co.ksoe.com.ua
fagot:~ # bucardo_ctl set log_conflict_file=/var/log/bucardo/bucardo_conflict.log
fagot:~ # bucardo_ctl set reason_file=/var/log/bucardo/bucardo.restart.reason.log
fagot:~ # bucardo_ctl set warning_file=/var/log/bucardo/bucardo.warning.log
fagot:~ # bucardo_ctl set default_email_host=22.22.11.51
fagot:~ # mkdir /var/log/bucardo

Важное замечание. Все sync которые создаем, задаем copytype = insert !!!

Меняем настройки syslog-ng. Редактируем файл /etc/syslog-ng/syslog-ng.conf. добавляем или редактируем следующие строки:

filter f_bucardo {match('ucardo'); };
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);};

После чего перезапускаем syslog-ng (/etc/init.d/syslog restart)

Настраиваем logrotate. Добавляем файл /etc/logrotate.d/bucardo со следующим содержимым:

/var/log/bucardo/bucardo.log {
notifempty
daily
rotate 99
missingok
compress
dateext
copytruncate
olddir /var/log/arhiv/bucardo
}

Проверяем командой logrotate -f /etc/logrotate.d/bucardo

Всё, поставили и поднастроили :) и намного проще чем раньше :) Спасибо всем создателям репозитариев :)

Ну и теперь добавляем БД в репликацию. Два слова о структуре. Изначально делалось так: все РЭСы подчиненные, одна БД общая, куда все реплицируется. Теперь делаем так: РЭСы подчиненные, реплицируются каждый в свою копию БД на центральный сервер, а потом из этих БД сорбирается общая. То есть репликация типа в два этапа.

Вычищаем все старые настройки репликации в РЭСах и делаем все по новому.

Скадовск

fagot:/u02/install/bucardo/Bucardo-4.4.0 # cd ~
fagot:~ # mkdir /u02/pgdata/skres
fagot:~ # chown postgres:postgres /u02/pgdata/skres
fagot:~ # psql -h 22.22.11.72 -U postgres postgres
postgres=# CREATE ROLE res LOGIN PASSWORD 'res';
postgres=# ALTER ROLE res SUPERUSER;
postgres=# CREATE TABLESPACE skres OWNER res LOCATION '/u02/pgdata/skres';
postgres=# CREATE DATABASE skres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=skres;
postgres=# \c skres
skres=# CREATE SCHEMA res AUTHORIZATION res;
skres=# \q
fagot:~ # bucardo_ctl add database UTF8_sk name=skres_res host=22.22.25.2 user=res pass=res
fagot:~ # bucardo_ctl add database skres name=skres_obl host=22.22.11.72 user=res pass=res
fagot:~ # bucardo_ctl add all tables db=skres_res --herd=skres_herd --verbose
fagot:~ # mkdir work; cd work
fagot:~/work # /opt/pgsql-8.2/bin/pg_dump -h 22.22.25.2 -U postgres -F p -s -s -v -f "skres_schema.sql" -n 'res' "UTF8_sk"
fagot:~/work # psql -f skres_schema.sql -h 22.22.11.72 -U res skres
fagot:~/work # ssh root@22.22.25.2
linux01:~ # su - postgres -c "cd /files/ftp; pg_dump -C -F c -b -f pgsql.UTF8_sk "UTF8_sk""; exit
fagot:~/work # psql -h 22.22.11.72 -U bucardo bucardo
bucardo=# DELETE FROM bucardo.herdmap
bucardo-# WHERE goat IN (SELECT id
bucardo(# FROM bucardo.goat WHERE tablename IN
bucardo(# ('rp_abon','log','dual','billsqlreport'));
bucardo=# DELETE FROM bucardo.goat
bucardo-# WHERE tablename IN
bucardo-# ('rp_abon','log','dual','billsqlreport');
bucardo=# \q
fagot:~/work # bucardo_ctl add sync skres_sync type=pushdelta source=skres_herd targetdb=skres_obl --verbose
fagot:~/work # wget --no-proxy -c --ftp-user=sit --ftp-password=sit ftp://22.22.25.2/ftp/pgsql.UTF8_sk
fagot:~/work # mkdir skres
fagot:~/work # mv drop_* skres
fagot:~/work # mv pgsql.UTF8_sk skres
fagot:~/work # mv skres_schema.sql skres
fagot:~/work # cd skres/
fagot:~/work/skres # psql -U res skres
skres=# \o drop_constr.sql \t
skres=# SELECT 'ALTER TABLE res.'||t.tablename
skres-# ||' DROP CONSTRAINT '||con.conname||';'
skres-# FROM pg_constraint con, pg_class cl,
skres-# pg_tables t, information_schema.constraint_column_usage cc
skres-# WHERE t.schemaname = 'res'
skres-# AND t.tablename = cl.relname
skres-# AND con.contype = 'c'
skres-# AND con.conrelid = cl.oid
skres-# AND cc.constraint_name = con.conname
skres-# ORDER BY con.conname;
skres=# \o drop_rule.sql
skres=# SELECT 'DROP RULE '||rulename||' ON '||tablename||';'
skres-# FROM pg_rules
skres-# WHERE schemaname = 'res'
skres-# ORDER BY tablename, rulename;
skres=# \o drop_rule_constr.log
skres=# \i drop_constr.sql
skres=# \i drop_rule.sql
skres=# \q
fagot:~/work/skres # psql -U res skres
skres=# \o drop_fk.sql \t
skres=# SELECT 'ALTER TABLE res.'||t.tablename
skres-# ||' DROP CONSTRAINT '||con.conname||';'
skres-# FROM pg_constraint con, pg_class cl, pg_tables t,
skres-# information_schema.constraint_column_usage cc
skres-# WHERE t.schemaname = 'res'
skres-# AND t.tablename = cl.relname
skres-# AND con.contype = 'f'
skres-# AND con.conrelid = cl.oid
skres-# AND cc.constraint_name = con.conname
skres-# ORDER BY t.tablename, con.conname;
skres=# \o drop_fk.log
skres=# \i drop_fk.sql \q
fagot:~/work/skres # pg_restore -U res -d skres -a -v "pgsql.UTF8_sk"
fagot:~/work/skres # grep FOREIGN -B 1 skres_schema.sql > restore_fk.sql
fagot:~/work/skres # psql -U res skres
skres=# \o restore_fk.log \t
skres=# \i restore_fk.sql \q
fagot:~/work/skres # bucardo_ctl start "first DB skres added"

Тут как раз оказалось, что пока я настраивал, программисты поменяли структуру БД в Скадовске. Так что валидация базы проходит с ошибкой. Исправляем:

fagot:~/work/skres # bucardo_ctl stop "skres updates"
fagot:~/work/skres # psql -U res skres
skres=# create table epactoftech2measuremnt(
skres(# code double precision not null,
skres(# modify_time double precision not null,
skres(# domain_info varchar(32) not null,
skres(# techactrefcode double precision,
skres(# measurementrefcode double precision
skres(# );
skres=# alter table epactoftech2measuremnt add constraint pk_pctftch2msrm primary key (code);
skres=# create table eptaxinvoiceinfo(
skres(# code double precision not null,
skres(# renname varchar(150),
skres(# renaddress varchar(150),
skres(# renphone varchar(50),
skres(# renedrpou varchar(20),
skres(# reninn varchar(20),
skres(# conditiondelivery varchar(150),
skres(# formcalculation varchar(50),
skres(# customername varchar(150),
skres(# customeraddress varchar(150),
skres(# customerphone varchar(50),
skres(# customeredrpou varchar(20),
skres(# customerinn varchar(20),
skres(# modify_time double precision not null,
skres(# domain_info varchar(32) not null,
skres(# taxinvoicerefcode double precision
skres(# );
skres=# alter table eptaxinvoiceinfo add constraint pk_eptaxinvocnf primary key (code);
skres=# ALTER TABLE epactoftechcontrol ADD COLUMN counterrefcode DOUBLE PRECISION;
skres=# \q
fagot:~/work/skres # bucardo_ctl add all tables db=skres_res --herd=skres_herd --verbose
fagot:~/work/skres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/skres # bucardo_ctl validate skres_sync
fagot:~/work/skres # psql -h 22.22.25.2 -U res UTF8_sk
UTF8_sk=# update eptaxinvoiceinfo set code=code;
UTF8_sk=# update epactoftech2measuremnt set code=code;
UTF8_sk=# \q
fagot:~/work/skres # bucardo_ctl start "after skres update"
fagot:~/work/skres # bucardo_ctl stop "after skres update error for add FK"
fagot:~/work/skres # psql -U res skres
skres=# alter table epactoftech2measuremnt add constraint fk_pctftchcnt2techactref foreign key (techactrefcode)
skres-# references epactoftechcontrol(code);
skres=# alter table epactoftech2measuremnt add constraint fk_epmeasrmnt2measuremntrf foreign key (measurementrefcode)
skres-# references epmeasurement(code);
skres=# alter table eptaxinvoiceinfo add constraint fk_eptaxinvoc2taxinvoicerf foreign key (taxinvoicerefcode)
skres-# references eptaxinvoice(code);
skres=# ALTER TABLE epactoftechcontrol ADD CONSTRAINT fk_tact2counter FOREIGN KEY (counterrefcode)
skres-# REFERENCES epcounter(code);
skres=# \q
fagot:~/work/skres # bucardo_ctl start "after skres update add FK"

Фух. Всё, Скадовск заработал.

Новотроицк

fagot:~/work # mkdir /u02/pgdata/ntres
fagot:~/work # chown postgres:postgres /u02/pgdata/ntres/
fagot:~/work # psql -h 22.22.11.72 -U postgres postgres
postgres=# CREATE TABLESPACE ntres OWNER res LOCATION '/u02/pgdata/ntres';
postgres=# CREATE DATABASE ntres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=ntres;
postgres=# \c ntres
ntres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work # mkdir ntres
fagot:~/work # cd ntres
fagot:~/work/ntres # bucardo_ctl add database ntres name=ntres_res host=22.22.50.2 user=res pass=res
fagot:~/work/ntres # bucardo_ctl add database ntres name=ntres_obl host=22.22.11.72 user=res pass=res
fagot:~/work/ntres # bucardo_ctl add all tables db=ntres_res --herd=ntres_herd --verbose
fagot:~ # bucardo_ctl remove table log rp_abon dual billsqlreport --herd=ntres_herd --verbose
fagot:~/work/ntres # /opt/pgsql-8.2/bin/pg_dump -h 22.22.50.2 -U postgres -F p -s -s -v -f "ntres_schema.sql" -n 'res' "ntres"
fagot:~/work/ntres # psql -f ntres_schema.sql -U res ntres
fagot:~/work/ntres # psql -U res ntres
ntres=# \o drop_fk.sql \t
ntres=# SELECT 'ALTER TABLE res.'||t.tablename
ntres-# ||' DROP CONSTRAINT '||con.conname||';'
ntres-# FROM pg_constraint con, pg_class cl, pg_tables t,
ntres-# information_schema.constraint_column_usage cc
ntres-# WHERE t.schemaname = 'res'
ntres-# AND t.tablename = cl.relname
ntres-# AND con.contype = 'f'
ntres-# AND con.conrelid = cl.oid
ntres-# AND cc.constraint_name = con.conname
ntres-# ORDER BY t.tablename, con.conname;
ntres=# \o drop_fk.log
ntres=# \i drop_fk.sql \q
fagot:~/work/ntres # ssh root@22.22.50.2
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.ntres "ntres""; exit
fagot:~/work/ntres # bucardo_ctl add sync ntres_sync type=pushdelta source=ntres_herd targetdb=ntres_obl --verbose
fagot:~/work/ntres # wget --no-proxy -c --ftp-user=sit --ftp-password=qq ftp://22.22.50.2/postgres/pgsql.ntres
fagot:~/work/ntres # pg_restore -U res -d ntres -a -v "pgsql.ntres"
fagot:~/work/ntres # grep FOREIGN -B 1 ntres_schema.sql > restore_fk.sql
fagot:~/work/ntres # psql -U res ntres
ntres=# \o restore_fk.log \t
ntres=# \i restore_fk.sql \q

Цюрупинск

fagot:~ # mkdir /u02/pgdata/crres
fagot:~ # chown postgres:postgres /u02/pgdata/crres
fagot:~ # psql -U postgres postgres
postgres=# CREATE TABLESPACE crres OWNER res LOCATION '/u02/pgdata/crres';
postgres=# CREATE DATABASE crres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=crres;
postgres=# \c crres
crres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~ # bucardo_ctl add database crres name=crres_res host=22.22.33.2 user=res pass=res
fagot:~ # bucardo_ctl add database crres name=crres_obl host=22.22.11.72 user=res pass=res
fagot:~ # bucardo_ctl add all tables db=crres_res --herd=crres_herd --verbose
fagot:~ # bucardo_ctl remove table log rp_abon dual billsqlreport --herd=crres_herd --verbose
fagot:~ # cd work; mkdir crres; cd crres
fagot:~/work/crres # /opt/pgsql-8.2/bin/pg_dump -h 22.22.33.2 -U postgres -F p -s -s -v -f "crres_schema.sql" -n 'res' "crres"
fagot:~/work/crres # psql -f crres_schema.sql -U res crres
fagot:~/work/crres # ssh root@22.22.33.2
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.crres "crres""; exit
fagot:~/work/crres # psql -U res crres
crres=# \o drop_constr.sql \t
crres=# SELECT 'ALTER TABLE res.'||t.tablename
crres-# ||' DROP CONSTRAINT '||con.conname||';'
crres-# FROM pg_constraint con, pg_class cl,
crres-# pg_tables t, information_schema.constraint_column_usage cc
crres-# WHERE t.schemaname = 'res'
crres-# AND t.tablename = cl.relname
crres-# AND con.contype = 'c'
crres-# AND con.conrelid = cl.oid
crres-# AND cc.constraint_name = con.conname
crres-# ORDER BY con.conname;
crres=# \o drop_rule.sql
crres=# SELECT 'DROP RULE '||rulename||' ON '||tablename||';'
crres-# FROM pg_rules
crres-# WHERE schemaname = 'res'
crres-# ORDER BY tablename, rulename;
crres=# \o drop_rule_constr.log
crres=# \i drop_constr.sql
crres=# \i drop_rule.sql \q
fagot:~/work/crres # psql -U res crres
crres=# \o drop_fk.sql \t
crres=# SELECT 'ALTER TABLE res.'||t.tablename
crres-# ||' DROP CONSTRAINT '||con.conname||';'
crres-# FROM pg_constraint con, pg_class cl, pg_tables t,
crres-# information_schema.constraint_column_usage cc
crres-# WHERE t.schemaname = 'res'
crres-# AND t.tablename = cl.relname
crres-# AND con.contype = 'f'
crres-# AND con.conrelid = cl.oid
crres-# AND cc.constraint_name = con.conname
crres-# ORDER BY t.tablename, con.conname;
crres=# \o drop_fk.log
crres=# \i drop_fk.sql \q
fagot:~/work/crres # bucardo_ctl add sync crres_sync type=pushdelta source=crres_herd targetdb=crres_obl --verbose
fagot:~/work/crres # wget --no-proxy -c --ftp-user=sit --ftp-password=sit ftp://22.22.33.2/postgres/pgsql.crres
fagot:~/work/crres # pg_restore -U res -d crres -a -v "pgsql.crres"
fagot:~/work/crres # grep FOREIGN -B 1 crres_schema.sql > restore_fk.sql
fagot:~/work/crres # psql -U res crres
crres=# \o restore_fk.log \t
crres=# \i restore_fk.sql \q
fagot:~/work/crres # bucardo_ctl start "crres added"

Белозерка

fagot:~/work/crres # bucardo_ctl stop "for add bzres"
fagot:~/work/crres # cd ../; mkdir /u02/pgdata/bzres; mkdir bzres; cd bzres
fagot:~/work/bzres # chown postgres:postgres /u02/pgdata/bzres
fagot:~/work/bzres # psql -U postgres postgres
postgres=# CREATE TABLESPACE bzres OWNER res LOCATION '/u02/pgdata/bzres';
postgres=# CREATE DATABASE bzres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=bzres;
postgres=# \c bzres
bzres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/bzres # psql -h 22.22.35.2 -U postgres bzres
bzres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/bzres # bucardo_ctl add database bzres name=bzres_res host=22.22.35.2 user=res pass=res
fagot:~/work/bzres # bucardo_ctl add database bzres name=bzres_obl host=22.22.11.72 user=res pass=res
fagot:~/work/bzres # bucardo_ctl add all tables db=bzres_res --herd=bzres_herd --verbose
fagot:~/work/bzres # /opt/pgsql-8.2/bin/pg_dump -h 22.22.35.2 -U postgres -F p -s -s -v -f "bzres_schema.sql" -n 'res' "bzres"
fagot:~/work/bzres # psql -f bzres_schema.sql -U res bzres
fagot:~/work/bzres # ssh root@22.22.35.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.bzres "bzres""; exit
fagot:~/work/bzres # psql -U res bzres
bzres=# \o drop_fk.sql \t
bzres=# SELECT 'ALTER TABLE res.'||t.tablename
bzres-# ||' DROP CONSTRAINT '||con.conname||';'
bzres-# FROM pg_constraint con, pg_class cl, pg_tables t,
bzres-# information_schema.constraint_column_usage cc
bzres-# WHERE t.schemaname = 'res'
bzres-# AND t.tablename = cl.relname
bzres-# AND con.contype = 'f'
bzres-# AND con.conrelid = cl.oid
bzres-# AND cc.constraint_name = con.conname
bzres-# ORDER BY t.tablename, con.conname;
bzres=# \o drop_fk.log
bzres=# \i drop_fk.sql \q
fagot:~/work/bzres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/bzres # bucardo_ctl add sync bzres_sync type=pushdelta source=bzres_herd targetdb=bzres_obl --verbose
fagot:~/work/bzres # wget --no-proxy -c --ftp-user=sit --ftp-password=sit ftp://22.22.35.2/postgres/pgsql.bzres
fagot:~/work/bzres # pg_restore -U res -d bzres -a -v "pgsql.bzres"
fagot:~/work/bzres # grep FOREIGN -B 1 bzres_schema.sql > restore_fk.sql
fagot:~/work/bzres # psql -U res bzres
bzres=# \o restore_fk.log \t
bzres=# \i restore_fk.sql \q

Голая Пристань

fagot:~ # mkdir /u02/pgdata/gpres; mkdir ~/work/gpres; cd ~/work/gpres
fagot:~/work/gpres # chown postgres:postgres /u02/pgdata/gpres
fagot:~/work/gpres # psql -U postgres postgres
postgres=# CREATE TABLESPACE gpres OWNER res LOCATION '/u02/pgdata/gpres';
postgres=# CREATE DATABASE gpres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=gpres;
postgres=# \c gpres
gpres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/gpres # psql -h 22.22.34.2 -U postgres gpres
gpres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/gpres # bucardo_ctl add database gpres name=gpres_res host=22.22.34.2 user=res pass=res
fagot:~/work/gpres # bucardo_ctl add database gpres name=gpres_obl host=22.22.11.72 user=res pass=res
fagot:~/work/gpres # bucardo_ctl add all tables db=gpres_res --herd=gpres_herd --verbose
fagot:~/work/gpres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/gpres # pg_dump -h 22.22.34.2 -U postgres -F p -s -s -v -f "gpres_schema.sql" -n 'res' "gpres"
fagot:~/work/gpres # psql -f gpres_schema.sql -U res gpres
fagot:~/work/gpres # ssh root@22.22.34.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.gpres "gpres""; exit
fagot:~/work/gpres # psql -U res gpres
gpres=# \o drop_fk.sql \t
gpres=# SELECT 'ALTER TABLE res.'||t.tablename
gpres-# ||' DROP CONSTRAINT '||con.conname||';'
gpres-# FROM pg_constraint con, pg_class cl, pg_tables t,
gpres-# information_schema.constraint_column_usage cc
gpres-# WHERE t.schemaname = 'res'
gpres-# AND t.tablename = cl.relname
gpres-# AND con.contype = 'f'
gpres-# AND con.conrelid = cl.oid
gpres-# AND cc.constraint_name = con.conname
gpres-# ORDER BY t.tablename, con.conname;
gpres=# \o drop_fk.log
gpres=# \i drop_fk.sql \q
fagot:~/work/gpres # bucardo_ctl add sync gpres_sync type=pushdelta source=gpres_herd targetdb=gpres_obl --verbose
fagot:~/work/gpres # wget --no-proxy -c --ftp-user=sit --ftp-password=s ftp://22.22.34.2/postgres/pgsql.gpres
fagot:~/work/gpres # psql -U res gpres
gpres=# DROP SCHEMA res CASCADE;
gpres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/gpres # pg_restore -U res -d gpres -v "pgsql.gpres"
fagot:~/work/gpres # bucardo_ctl start "after add gpres, ntres, bzres"

В.Лепетиха

fagot:~/work/skres # mkdir /u02/pgdata/vlres; mkdir ~/work/vlres; cd ~/work/vlres
fagot:~/work/vlres # chown postgres:postgres /u02/pgdata/vlres
fagot:~/work/vlres # psql -U postgres postgres
postgres=# CREATE TABLESPACE vlres OWNER res LOCATION '/u02/pgdata/vlres';
postgres=# CREATE DATABASE vlres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=vlres;
postgres=# \c vlres
vlres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/vlres # psql -h 22.22.41.2 -U postgres vlres
vlres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/vlres # bucardo_ctl add database vlres name=vlres_res host=22.22.41.2 user=res pass=r
fagot:~/work/vlres # bucardo_ctl add database vlres name=vlres_obl host=22.22.11.72 user=res pass=res
fagot:~/work/vlres # bucardo_ctl add all tables db=vlres_res --herd=vlres_herd --verbose
fagot:~/work/vlres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/vlres # pg_dump -h 22.22.41.2 -U postgres -F p -s -s -v -f "vlres_schema.sql" -n 'res' "vlres"
fagot:~/work/vlres # psql -f vlres_schema.sql -U res vlres
fagot:~/work/vlres # ssh root@22.22.41.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.vlres "vlres""; exit
fagot:~/work/vlres # bucardo_ctl stop "for add vlres"
fagot:~/work/vlres # bucardo_ctl add sync vlres_sync type=pushdelta source=vlres_herd targetdb=vlres_obl --verbose
fagot:~/work/vlres # wget --no-proxy -c --limit-rate=5000 --ftp-user=sit --ftp-password=a ftp://22.22.41.2/postgres/pgsql.vlres
fagot:~/work/vlres # psql -U res vlres
vlres=# DROP SCHEMA res CASCADE;
vlres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/vlres # pg_restore -U res -d vlres -v "pgsql.vlres"
fagot:~/work/vlres # bucardo_ctl start "after add vlres"

В.Александровка

fagot:~/work/vlres # bucardo_ctl stop "for add vares brres"
fagot:~/work/vlres # mkdir /u02/pgdata/vares; mkdir ~/work/vares; cd ~/work/vares
fagot:~/work/vares # chown postgres:postgres /u02/pgdata/vares
fagot:~/work/vares # psql -U postgres postgres
postgres=# CREATE TABLESPACE vares OWNER res LOCATION '/u02/pgdata/vares';
postgres=# CREATE DATABASE vares WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=vares;
postgres=# \c vares
vares=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/vares # psql -h 22.22.59.2 -U postgres vares
vares=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/vares # bucardo_ctl add database vares name=vares_res host=22.22.59.2 user=res pass=ss
fagot:~/work/vares # bucardo_ctl add database vares name=vares_obl host=22.22.11.72 user=res pass=r
fagot:~/work/vares # bucardo_ctl add all tables db=vares_res --herd=vares_herd --verbose
fagot:~/work/vares # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/vares # pg_dump -h 22.22.59.2 -U postgres -F p -s -s -v -f "vares_schema.sql" -n 'res' "vares"
fagot:~/work/vares # psql -f vares_schema.sql -U res vares
fagot:~/work/vares # ssh root@22.22.59.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.vares "vares""; exit
fagot:~/work/vares # bucardo_ctl stop "for add vares"
fagot:~/work/vares # bucardo_ctl add sync vares_sync type=pushdelta source=vares_herd targetdb=vares_obl --verbose
fagot:~/work/vares # wget --no-proxy -c --ftp-user=alf --ftp-password=3 ftp://22.22.59.2/postgres/pgsql.vares
fagot:~/work/vares # psql -U res vares
vares=# DROP SCHEMA res CASCADE;
vares=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/vares # pg_restore -U res -d vares -v "pgsql.vares"
fagot:~/work/vares # bucardo_ctl start "after add vares"

Берислав

fagot:~ # mkdir /u02/pgdata/brres; mkdir ~/work/brres; cd ~/work/brres
fagot:~/work/brres # chown postgres:postgres /u02/pgdata/brres
fagot:~/work/brres # psql -U postgres postgres
postgres=# CREATE TABLESPACE brres OWNER res LOCATION '/u02/pgdata/brres';
postgres=# CREATE DATABASE brres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=brres;
postgres=# \c brres
brres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/brres # psql -h 22.22.44.2 -U postgres brres
brres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/brres # bucardo_ctl add database brres name=brres_res host=22.22.44.2 user=res pass=a
fagot:~/work/brres # bucardo_ctl add database brres name=brres_obl host=22.22.11.72 user=res pass=res
fagot:~/work/brres # bucardo_ctl add all tables db=brres_res --herd=brres_herd --verbose
fagot:~/work/brres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/brres # pg_dump -h 22.22.44.2 -U postgres -F p -s -s -v -f "brres_schema.sql" -n 'res' "brres"
fagot:~/work/brres # psql -f brres_schema.sql -U res brres
fagot:~/work/brres # ssh root@22.22.44.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.brres "brres""; exit
fagot:~/work/brres # bucardo_ctl stop "for add brres"
fagot:~/work/brres # bucardo_ctl add sync brres_sync type=pushdelta source=brres_herd targetdb=brres_obl --verbose
fagot:~/work/brres # wget --no-proxy -c --ftp-user=sit --ftp-password=w ftp://22.22.44.2/postgres/pgsql.brres
fagot:~/work/brres # psql -U res brres
brres=# DROP SCHEMA res CASCADE;
brres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/brres # pg_restore -U res -d brres -v "pgsql.brres"
fagot:~/work/brres # bucardo_ctl start "after add brres"

Чаплынка

fagot:~/work/vares # mkdir /u02/pgdata/cpres; mkdir ~/work/cpres; cd ~/work/cpres
fagot:~/work/cpres # chown postgres:postgres /u02/pgdata/cpres
fagot:~/work/cpres # psql -U postgres postgres
postgres=# CREATE TABLESPACE cpres OWNER res LOCATION '/u02/pgdata/cpres';
postgres=# CREATE DATABASE cpres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=cpres;
postgres=# \c cpres
cpres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/cpres # psql -h 22.22.27.2 -U postgres cpres
cpres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/cpres # bucardo_ctl add database cpres name=cpres_res host=22.22.27.2 user=res pass=a
fagot:~/work/cpres # bucardo_ctl add database cpres name=cpres_obl host=22.22.11.72 user=res pass=r
fagot:~/work/cpres # bucardo_ctl add all tables db=cpres_res --herd=cpres_herd --verbose
fagot:~/work/cpres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/cpres # pg_dump -h 22.22.27.2 -U postgres -F p -s -s -v -f "cpres_schema.sql" -n 'res' "cpres"
fagot:~/work/cpres # psql -f cpres_schema.sql -U res cpres
fagot:~/work/cpres # ssh root@22.22.27.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.cpres "cpres""; exit
fagot:~/work/cpres # bucardo_ctl stop "for add cpres"
fagot:~/work/cpres # bucardo_ctl add sync cpres_sync type=pushdelta source=cpres_herd targetdb=cpres_obl --verbose
fagot:~/work/cpres # wget --no-proxy -c --ftp-user=sit --ftp-password=r ftp://22.22.27.2/postgres/pgsql.cpres
fagot:~/work/cpres # psql -U res cpres
cpres=# DROP SCHEMA res CASCADE;
cpres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/cpres # pg_restore -U res -d cpres -v "pgsql.cpres"
fagot:~/work/cpres # bucardo_ctl start "after add cpres"

Геническ

fagot:~ # mkdir /u02/pgdata/gnres; mkdir ~/work/gnres; cd ~/work/gnres
fagot:~/work/gnres # chown postgres:postgres /u02/pgdata/gnres
fagot:~/work/gnres # psql -U postgres postgres
postgres=# CREATE TABLESPACE gnres OWNER res LOCATION '/u02/pgdata/gnres';
postgres=# CREATE DATABASE gnres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=gnres;
postgres=# \c gnres
gnres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/gnres # psql -h 22.22.49.2 -U postgres gnres
gnres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/gnres # bucardo_ctl add database gnres name=gnres_res host=22.22.49.2 user=res pass=a
fagot:~/work/gnres # bucardo_ctl add database gnres name=gnres_obl host=22.22.11.72 user=res pass=r
fagot:~/work/gnres # bucardo_ctl add all tables db=gnres_res --herd=gnres_herd --verbose
fagot:~/work/gnres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/gnres # pg_dump -h 22.22.49.2 -U postgres -F p -s -s -v -f "gnres_schema.sql" -n 'res' "gnres"
fagot:~/work/gnres # psql -f gnres_schema.sql -U res gnres
fagot:~/work/gnres # bucardo_ctl stop "for add gnres"
fagot:~/work/gnres # ssh root@22.22.49.2
linux01:~ # mkdir /files01/ftp/postgres; chown postgres:postgres /files01/ftp/postgres
linux01:~ # su - postgres -c "cd /files01/ftp/postgres; pg_dump -C -F c -b -f pgsql.gnres "gnres""; exit
fagot:~/work/gnres # bucardo_ctl add sync gnres_sync type=pushdelta source=gnres_herd targetdb=gnres_obl --verbose
fagot:~/work/gnres # wget --no-proxy -c --ftp-user=sit --ftp-password=l ftp://22.22.49.2/postgres/pgsql.gnres
fagot:~/work/gnres # psql -U res gnres
gnres=# DROP SCHEMA res CASCADE;
gnres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/gnres # pg_restore -U res -d gnres -v "pgsql.gnres"
fagot:~/work/gnres # bucardo_ctl start "after add gnres"

Н.Каховка

fagot:~/work/cpres # mkdir /u02/pgdata/nkres; mkdir ~/work/nkres; cd ~/work/nkres
fagot:~/work/nkres # chown postgres:postgres /u02/pgdata/nkres
fagot:~/work/nkres # psql -U postgres postgres
postgres=# CREATE TABLESPACE nkres OWNER res LOCATION '/u02/pgdata/nkres';
postgres=# CREATE DATABASE nkres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=nkres;
postgres=# \c nkres
nkres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/nkres # psql -h 22.22.12.2 -U postgres nkres
nkres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/nkres # bucardo_ctl add database nkres name=nkres_res host=22.22.12.2 user=res pass=as
fagot:~/work/nkres # bucardo_ctl add database nkres name=nkres_obl host=22.22.11.72 user=res pass=r
fagot:~/work/nkres # bucardo_ctl add all tables db=nkres_res --herd=nkres_herd --verbose
fagot:~/work/nkres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/nkres # pg_dump -h 22.22.12.2 -U postgres -F p -s -s -v -f "nkres_schema.sql" -n 'res' "nkres"
fagot:~/work/nkres # psql -f nkres_schema.sql -U res nkres
fagot:~/work/nkres # bucardo_ctl stop "for add nkres"
fagot:~/work/nkres # ssh root@22.22.12.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.nkres "nkres""; exit
fagot:~/work/nkres # bucardo_ctl add sync nkres_sync type=pushdelta source=nkres_herd targetdb=nkres_obl --verbose
fagot:~/work/nkres # wget --limit-rate=20000 --no-proxy -c --ftp-user=alexsf --ftp-password=aa ftp://22.22.12.2/postgres/pgsql.nkres
fagot:~/work/nkres # psql -U res nkres
nkres=# DROP SCHEMA res CASCADE;
nkres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/nkres # pg_restore -U res -d nkres -v "pgsql.nkres"

Каховка

fagot:~/work/gnres # mkdir /u02/pgdata/kvres; mkdir ~/work/kvres; cd ~/work/kvres
fagot:~/work/kvres # chown postgres:postgres /u02/pgdata/kvres
fagot:~/work/kvres # psql -U postgres postgres
postgres=# CREATE TABLESPACE kvres OWNER res LOCATION '/u02/pgdata/kvres';
postgres=# CREATE DATABASE kvres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=kvres;
postgres=# \c kvres
kvres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/kvres # psql -h 22.22.42.3 -U postgres kvres
kvres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/kvres # bucardo_ctl add database kvres name=kvres_res host=22.22.42.3 user=res pass=a
fagot:~/work/kvres # bucardo_ctl add database kvres name=kvres_obl host=22.22.11.72 user=res pass=r
fagot:~/work/kvres # bucardo_ctl add all tables db=kvres_res --herd=kvres_herd --verbose
fagot:~/work/kvres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/kvres # pg_dump -h 22.22.42.3 -U postgres -F p -s -s -v -f "kvres_schema.sql" -n 'res' "kvres"
fagot:~/work/kvres # psql -f kvres_schema.sql -U res kvres
fagot:~/work/kvres # ssh root@22.22.42.3
linux02:~ # mkdir /files/ftp/postgres; chown postgres:postgres /files/ftp/postgres
linux02:~ # su - postgres -c "cd /files/ftp/postgres; pg_dump -C -F c -b -f pgsql.kvres "kvres""; exit
fagot:~/work/kvres # bucardo_ctl add sync kvres_sync type=pushdelta source=kvres_herd targetdb=kvres_obl --verbose
fagot:~/work/kvres # wget --limit-rate=20000 --no-proxy -c --ftp-user=sit --ftp-password=r ftp://22.22.42.3/postgres/pgsql.kvres
fagot:~/work/kvres # psql -U res kvres
kvres=# DROP SCHEMA res CASCADE;
kvres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/kvres # pg_restore -U res -d kvres -v "pgsql.kvres"

Каланчак

fagot:/var/log/bucardo # mkdir /u02/pgdata/klres; mkdir ~/work/klres; cd ~/work/klres
fagot:~/work/klres # chown postgres:postgres /u02/pgdata/klres
fagot:~/work/klres # psql -U postgres postgres
postgres=# CREATE TABLESPACE klres OWNER res LOCATION '/u02/pgdata/klres';
postgres=# CREATE DATABASE klres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=klres;
postgres=# \c klres
klres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/klres # psql -h 22.22.26.2 -U postgres klres
klres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/klres # bucardo_ctl add database klres name=klres_res host=22.22.26.2 user=res pass=a
fagot:~/work/klres # bucardo_ctl add database klres name=klres_obl host=22.22.11.72 user=res pass=r
fagot:~/work/klres # bucardo_ctl add all tables db=klres_res --herd=klres_herd --verbose
fagot:~/work/klres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/klres # pg_dump -h 22.22.26.2 -U postgres -F p -s -s -v -f "klres_schema.sql" -n 'res' "klres"
fagot:~/work/klres # psql -f klres_schema.sql -U res klres
fagot:~/work/klres # ssh root@22.22.26.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.klres "klres""; exit
fagot:~/work/klres # bucardo_ctl add sync klres_sync type=pushdelta source=klres_herd targetdb=klres_obl --verbose
fagot:~/work/klres # wget --limit-rate=5000 --no-proxy -c --ftp-user=sit --ftp-password=r ftp://22.22.26.2/postgres/pgsql.klres
fagot:~/work/klres # psql -U res klres
klres=# DROP SCHEMA res CASCADE;
klres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/klres # pg_restore -U res -d klres -v "pgsql.klres"

Воронцовка

fagot:~ # mkdir /u02/pgdata/nvres; mkdir ~/work/nvres; cd ~/work/nvres
fagot:~/work/nvres # chown postgres:postgres /u02/pgdata/nvres
fagot:~/work/nvres # psql -U postgres postgres
postgres=# CREATE TABLESPACE nvres OWNER res LOCATION '/u02/pgdata/nvres';
postgres=# CREATE DATABASE nvres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=nvres;
postgres=# \c nvres
nvres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/nvres # psql -h 22.22.58.2 -U postgres nvres
nvres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/nvres # bucardo_ctl add database nvres name=nvres_res host=22.22.58.2 user=res pass=a
fagot:~/work/nvres # bucardo_ctl add database nvres name=nvres_obl host=22.22.11.72 user=res pass=r
fagot:~/work/nvres # bucardo_ctl add all tables db=nvres_res --herd=nvres_herd --verbose
fagot:~/work/nvres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/nvres # pg_dump -h 22.22.58.2 -U postgres -F p -s -s -v -f "nvres_schema.sql" -n 'res' "nvres"
fagot:~/work/nvres # psql -f nvres_schema.sql -U res nvres
fagot:~/work/nvres # ssh root@22.22.58.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.nvres "nvres""; exit
fagot:~/work/nvres # bucardo_ctl add sync nvres_sync type=pushdelta source=nvres_herd targetdb=nvres_obl --verbose
fagot:~/work/nvres # wget --no-proxy -c --ftp-user=sit --ftp-password=u ftp://22.22.58.2/postgres/pgsql.nvres
fagot:~/work/nvres # psql -U res nvres
nvres=# DROP SCHEMA res CASCADE;
nvres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/nvres # pg_restore -U res -d nvres -v "pgsql.nvres"

Высокополье

fagot:~ # mkdir /u02/pgdata/vpres; mkdir ~/work/vpres; cd ~/work/vpres
fagot:~/work/vpres # chown postgres:postgres /u02/pgdata/vpres
fagot:~/work/vpres # psql -U postgres postgres
postgres=# CREATE TABLESPACE vpres OWNER res LOCATION '/u02/pgdata/vpres';
postgres=# CREATE DATABASE vpres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=vpres;
postgres=# \c vpres
vpres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/vpres # psql -h 22.22.57.2 -U postgres vpres
vpres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/vpres # bucardo_ctl add database vpres name=vpres_res host=22.22.57.2 user=res pass=a
fagot:~/work/vpres # bucardo_ctl add database vpres name=vpres_obl host=22.22.11.72 user=res pass=r
fagot:~/work/vpres # bucardo_ctl add all tables db=vpres_res --herd=vpres_herd --verbose
fagot:~/work/vpres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/vpres # pg_dump -h 22.22.57.2 -U postgres -F p -s -s -v -f "vpres_schema.sql" -n 'res' "vpres"
fagot:~/work/vpres # psql -f vpres_schema.sql -U res vpres
fagot:~/work/vpres # ssh root@22.22.57.2
linux01:~ # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:~ # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.vpres "vpres""; exit
fagot:~/work/vpres # bucardo_ctl add sync vpres_sync type=pushdelta source=vpres_herd targetdb=vpres_obl --verbose
fagot:~/work/vpres # wget --limit-rate=20000 --no-proxy -c --ftp-user=alexsf --ftp-password=ff ftp://22.22.57.2/postgres/pgsql.vpres
fagot:~/work/vpres # psql -U res vpres
vpres=# DROP SCHEMA res CASCADE;
vpres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/vpres # pg_restore -U res -d vpres -v "pgsql.vpres"

fagot:~ # bucardo_ctl start "after add all res"

##########################################################
##### Проверка наличия первичных ключей на таблицах
##########################################################

select distinct t.tablename
from pg_tables t, pg_class cl, pg_constraint con
where t.schemaname = 'res'
and t.tablename = cl.relname
and t.tablename not in (select cl.relname
from pg_constraint con, pg_class cl
where contype = 'p'
and con.conrelid = cl.oid)

##########################################################
##### И вот начинается самое весёлое!!!
##########################################################

И чё, думали всё????
А фиг!!!

Теперь из этих 15-ти баз нужно собрать одну мегабазу. Кстати, в скором времени добавится еще 2, а потом еще 1, но очень большая :)

Итак, начинаем.

fagot:~ # mkdir /u02/pgdata/combine; mkdir ~/work/combine; cd ~/work/combine
fagot:~/work/combine # chown postgres:postgres /u02/pgdata/combine/
fagot:~/work/combine # psql -U postgres postgres
postgres=# CREATE TABLESPACE combine OWNER res LOCATION '/u02/pgdata/combine';
postgres=# CREATE DATABASE combine WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=combine;
postgres=# \c combine
combine=# CREATE SCHEMA res AUTHORIZATION res; \q

Далее. За основу возьмем схему Скадовска.

fagot:~/work/combine # pg_dump -h 22.22.25.2 -U postgres -F p -s -s -v -f "skres_schema.sql" -n 'res' "UTF8_sk"
fagot:~/work/combine # psql -f skres_schema.sql -U res combine

Теперь анализируем БД, которые уже реплицируются. Видим, что таблицы '__tmppp' и 'countergen_or' не нужны. Удаляем их.

fagot:~/work/combine # psql -U res combine
combine=# drop table __tmppp;
combine=# drop table countergen_or; \q

В дальнейшем нужно учитывать, что с этими таблицами не работаем. В принципе, вообще исключаем их из репликации.

fagot:~/work/combine # bucardo_ctl remove table __tmppp countergen_or

Теперь нам нужно удалить из схемы объекты, которые помешают сделать общую БД. Это CHECK проверки по первичным ключам и правила (RULE). Удалено это будет навсегда.

fagot:~/work/combine # psql -U res combine
combine=# \o drop_constr.sql \t
combine=# SELECT 'ALTER TABLE res.'||t.tablename
combine-# ||' DROP CONSTRAINT '||con.conname||';'
combine-# FROM pg_constraint con, pg_class cl,
combine-# pg_tables t, information_schema.constraint_column_usage cc
combine-# WHERE t.schemaname = 'res'
combine-# AND t.tablename = cl.relname
combine-# AND con.contype = 'c'
combine-# AND con.conrelid = cl.oid
combine-# AND cc.constraint_name = con.conname
combine-# ORDER BY con.conname;
combine=# \o drop_rule.sql
combine=# SELECT 'DROP RULE '||rulename||' ON '||tablename||';'
combine-# FROM pg_rules
combine-# WHERE schemaname = 'res'
combine-# ORDER BY tablename, rulename;
combine=# \o drop_rule_constr.log
combine=# \i drop_constr.sql
combine=# \i drop_rule.sql \q

Теперь, на время заливки БД в одну общую мы грохнем PK, FK, INDEX. Все. Потом восстановим.
Для начала подготовим скрипты для восстановления:

fagot:~/work/combine # grep FOREIGN -B 1 skres_schema.sql > restore_fk.sql
fagot:~/work/combine # grep PRIMARY -B 1 skres_schema.sql > restore_pk.sql
fagot:~/work/combine # grep 'CREATE INDEX' -B 1 skres_schema.sql > restore_ix.sql
fagot:~/work/combine # grep 'ADD CONSTRAINT' -B 1 skres_schema.sql > restore_alter_all_tables.sql

Ну а теперь непосредственно займемся гроханьем. Любимое занятие :)

fagot:~/work/combine # psql -U res combine
combine=# \o drop_fk.sql \t
combine=# SELECT 'ALTER TABLE res.'||t.tablename
combine-# ||' DROP CONSTRAINT '||con.conname||';'
combine-# FROM pg_constraint con, pg_class cl, pg_tables t,
combine-# information_schema.constraint_column_usage cc
combine-# WHERE t.schemaname = 'res'
combine-# AND t.tablename = cl.relname
combine-# AND con.contype = 'f'
combine-# AND con.conrelid = cl.oid
combine-# AND cc.constraint_name = con.conname
combine-# ORDER BY t.tablename, con.conname;
combine=# \o drop_fk.log
combine=# \i drop_fk.sql
combine=# \o drop_pk.sql
combine=# SELECT 'ALTER TABLE res.'||t.tablename
combine-# ||' DROP CONSTRAINT '||con.conname||';'
combine-# FROM pg_constraint con, pg_class cl, pg_tables t,
combine-# information_schema.constraint_column_usage cc
combine-# WHERE t.schemaname = 'res'
combine-# AND t.tablename = cl.relname
combine-# AND con.contype = 'p'
combine-# AND con.conrelid = cl.oid
combine-# AND cc.constraint_name = con.conname
combine-# ORDER BY t.tablename, con.conname;
combine=# \o drop_pk.log
combine=# \i drop_pk.sql
combine=# \o drop_uniq.sql
combine=# SELECT 'ALTER TABLE res.'||t.tablename
combine-# ||' DROP CONSTRAINT '||con.conname||';'
combine-# FROM pg_constraint con, pg_class cl, pg_tables t,
combine-# information_schema.constraint_column_usage cc
combine-# WHERE t.schemaname = 'res'
combine-# AND t.tablename = cl.relname
combine-# AND con.contype = 'u'
combine-# AND con.conrelid = cl.oid
combine-# AND cc.constraint_name = con.conname
combine-# ORDER BY t.tablename, con.conname;
combine=# \o drop_uniq.log
combine=# \i drop_uniq.sql
combine=# \o drop_ix.sql
combine=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
combine-# from pg_indexes
combine-# where schemaname = 'res';
combine=# \o drop_ix.log
combine=# \i drop_ix.sql
combine=# \q

Вроде всё. Хотя PK лучше восстановить....

fagot:~/work/combine # psql -U res combine
combine=# \o restore_pk.log \t
combine=# \i restore_pk.sql \q

Так. За основу по данным берем Цюрупинский РЭС. Его вливаем полностью, из остальных заливаем только таблицы, где есть поле domain_info. Вот как можно их выгрести:

SELECT *
FROM information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
AND (table_name IN (SELECT table_name
FROM information_schema.columns
WHERE column_name = 'domain_info'))
ORDER BY table_name;

Итак, заливаем Цюрупинск:

fagot:~/work/combine # mkdir dump; cd dump
fagot:~/work/combine/dump # pg_dump -a -U res -F c -v -f "crres.res.all.backup" crres
fagot:~/work/combine/dump # pg_restore -U res -d combine -a -v "crres.res.all.backup"

###################################################
# Мегаскрипт для создания общей БД
###################################################

backup.sql

\set filename 'pg_dump.':DBNAME'.':USER
\o :filename \t
SELECT
'pg_dump -a -U res -F c -v -f "table.'
||table_catalog||'.res.'||table_name
||'.backup" -t "'||table_name||'" '
||table_catalog||''
FROM information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
AND (table_name IN (SELECT table_name
FROM information_schema.columns
WHERE column_name = 'domain_info'))
ORDER BY table_name;
\q

reslist

brres
bzres
cpres
gnres
gpres
klres
kvres
nkres
ntres
nvres
skres
vares
vlres
vpres

truncate_combine.sql

\o truncate_combine_exec.sql \t
select 'truncate table res.'||table_name||';'
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema = 'res'
order by table_name;
\o truncate_combine_exec.log
\i truncate_combine_exec.sql

combine_build.sh

#!/bin/bash

echo "#########################################"
echo "# Установка текущей директории"
echo "#########################################"
export CURDIR=$(pwd)
echo $CURDIR
echo "#######################################"
echo " Зачистка схемы res БД combine"
echo "######################################"
psql -U res combine -f truncate_combine.sql
echo ""
echo "Готово."
echo ""
echo "#############################################"
echo "# Цюрупинск"
echo "##############################################"
mkdir $CURDIR/crres; cd $CURDIR/crres
echo ""
echo "Начинаем сливать dump..."
echo ""
pg_dump -a -U res -F c -v -f "crres.res.all.backup" crres
echo ""
echo "Слили."
echo ""
echo "Начинаем его заливать..."
echo ""
pg_restore -U res -d combine -a -v "crres.res.all.backup"
echo ""
echo "Залили."
echo ""
echo "###################################################"
echo "# Подготовка скриптов для потабличного резервного"
echo "# копирования таблиц остальных БД"
echo "###################################################"
for resname in $(cat $CURDIR/reslist)
do
echo "РЭС $resname."
mkdir $CURDIR/$resname; cd $CURDIR/$resname; psql -f ../backup.sql -U res $resname
done
echo "###################################################"
echo "# Потабличное резервное копирование БД"
echo "###################################################"
for resname in $(cat $CURDIR/reslist)
do
echo "------------------------------------------------"
echo ""
echo "Выливаем РЭС $resname:"
echo ""
echo "------------------------------------------------"
cd $CURDIR/$resname; bash < pg_dump.$resname.res
done
echo "###################################################"
echo "# Потабличная заливка в общую БД"
echo "###################################################"
for resname in $(cat $CURDIR/reslist)
do
echo "------------------------------------------------"
echo ""
echo "Заливаем РЭС $resname:"
echo ""
echo "------------------------------------------------"
cd $CURDIR/$resname
for fname in $(ls -1 table.*)
do
echo "++++++++++"
echo "Файл $fname"
echo "++++++++++"
pg_restore -U res -d combine -a -v "$fname"
done
done



Теперь потратим немного времени на сбор статистики. Лучше день потерять, потом за 5 минут долететь :)

stat.sh

#!/bin/bash

psql -d postgres -f va.sql -U postgres
psql -d bucardo -f va.sql -U postgres
psql -d combine -f va.sql -U postgres

psql -d brres -f va.sql -U postgres
psql -d bzres -f va.sql -U postgres
psql -d cpres -f va.sql -U postgres
psql -d crres -f va.sql -U postgres
psql -d gnres -f va.sql -U postgres
psql -d gpres -f va.sql -U postgres
psql -d klres -f va.sql -U postgres
psql -d kvres -f va.sql -U postgres
psql -d nkres -f va.sql -U postgres
psql -d ntres -f va.sql -U postgres
psql -d nvres -f va.sql -U postgres
psql -d skres -f va.sql -U postgres
psql -d vares -f va.sql -U postgres
psql -d vlres -f va.sql -U postgres
psql -d vpres -f va.sql -U postgres

va.sql

vacuum full verbose;
analyze verbose;

А теперь настроим репликацию общей БД.

fagot:/u02/work/bucardo/combine # bucardo_ctl add database combine name=combine host=22.22.11.72 user=res pass=r
fagot:/u02/work/bucardo/combine # mkdir add2bucardo; cd add2bucardo/
fagot:/u02/work/bucardo/combine/add2bucardo #

###################################################
# Мегаскрипты для подготовки таблицы goat
###################################################

goat.sh

#!/bin/bash

export CURDIR=$(pwd)

echo "РЭС crres."
psql -f goat.crres.sql -U res crres

for resname in $(cat $CURDIR/reslist.14)
do

echo "РЭС $resname."

echo "\o goat.$resname.insert.sql \t" > goat.$resname.sql
echo "SELECT 'INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) '" >> goat.$resname.sql
echo "||'VALUES (''"$resname"_obl'',''res'','''||table_name||''',''code'');'" >> goat.$resname.sql
echo "FROM information_schema.tables" >> goat.$resname.sql
echo "WHERE table_schema = 'res' AND table_type = 'BASE TABLE' AND (table_name IN (SELECT table_name" >> goat.$resname.sql
echo "FROM information_schema.columns WHERE column_name = 'domain_info')) ORDER BY table_name;" >> goat.$resname.sql
echo "" >> goat.$resname.sql

psql -f goat.$resname.sql -U res $resname

done

for resname in $(cat $CURDIR/reslist)
do

echo "РЭС $resname."
psql -f goat.$resname.insert.sql -U bucardo bucardo

done

psql -f goat.crres.insert.2.sql -U bucardo bucardo

goat.crres.sql

\o goat.crres.insert.sql \t
SELECT
'INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) '
||'VALUES (''crres_obl'',''res'','''||table_name||''',''code'');'
FROM information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
ORDER BY table_name;

reslist.14

brres
bzres
cpres
gnres
gpres
klres
kvres
nkres
ntres
nvres
skres
vares
vlres
vpres

reslist

brres
bzres
cpres
crres
gnres
gpres
klres
kvres
nkres
ntres
nvres
skres
vares
vlres
vpres

goat.crres.insert.2.sql

INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) VALUES ('crres_obl','res','auth_action','action_code');
INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) VALUES ('crres_obl','res','auth_group','group_code');
INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) VALUES ('crres_obl','res','auth_group_action','group_action_code');
INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) VALUES ('crres_obl','res','auth_role','role_code');
INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) VALUES ('crres_obl','res','auth_user','user_code');
INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) VALUES ('crres_obl','res','auth_user_action','user_action_code');
INSERT INTO bucardo.goat (db, schemaname, tablename, pkey) VALUES ('crres_obl','res','auth_user_group','user_group_code');

###################################################
# Мегаскрипты для создание HERD и SYNC
###################################################

#!/bin/bash

export CURDIR=$(pwd)

for resname in $(cat $CURDIR/reslist)
do
echo "РЭС $resname."
bucardo_ctl add herd combine_"$resname"_herd
done

for resname in $(cat $CURDIR/reslist)
do
echo "РЭС $resname."
echo "INSERT INTO bucardo.herdmap (herd, goat)" > herd.combine.$resname.sql
echo "SELECT 'combine_"$resname"_herd', id" >> herd.combine.$resname.sql
echo "FROM goat WHERE db = '"$resname"_obl';" >> herd.combine.$resname.sql
psql -f herd.combine.$resname.sql -U bucardo bucardo
done

bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or

for resname in $(cat $CURDIR/reslist)
do
echo "РЭС $resname."
bucardo_ctl add sync combine_"$resname"_sync type=pushdelta source=combine_"$resname"_herd targetdb=combine --verbose
done

И ТУТ У НАС ВОЗНИКЛА ПРОБЛЕМА!!!

Дело в том, что букардо выключает тригера когда вносит данные. Таким образом в общую БД ничего не попадает... Думай башка, шапка куплю...

РЕШЕНИЕ ПРОБЛЕМЫ

Ну во первых. У нас получается большая нагрузка на дисковую подсистему. /u02 у нас крутится на программном RAID5. Переделаем на программный RAID10. Для этого сначала через yast2 грохаем 5-й RAID, и в консоли делаем 10-й

mdadm -v --create /dev/md0 --level=raid10 --raid-devices=4 /dev/sda4 /dev/sdb4 /dev/sdc4 /dev/sdd4

Ну и потом в yast2 форматируем и монтируем.


####################################################
####################################################
####################################################
Итак, новый виток в развитии!!!
####################################################
####################################################
####################################################


Начинаем делать общую БД. Решил делать следующим образом: у нас остается репликация РЭСов один в один. При этом делаем общую БД, куда прямо из РЭСов сливаем часть таблиц. Никаких промежуточных БД.

Начинаем с Цюрупинска:

fagot:/u02/work/bucardo/combine # bucardo_ctl add herd combine_crres_herd
fagot:/u02/work/bucardo/combine # mkdir crres; cd crres
fagot:/u02/work/bucardo/combine/crres # psql -U res crres
crres=# CREATE TABLE res.bucardo_tmp_crres_goat AS
crres-# SELECT table_name
crres-# FROM information_schema.tables
crres-# WHERE table_schema = 'res'
crres-# AND table_type = 'BASE TABLE'
crres-# AND (table_name IN (SELECT table_name
crres(# FROM information_schema.columns
crres(# WHERE column_name = 'domain_info')
crres(# )
crres-# ORDER BY table_name;
crres=# \q
fagot:/u02/work/bucardo/combine/crres # pg_dump -U res -F c -v -f bucardo_tmp_crres_goat.backup -t bucardo_tmp_crres_goat crres
fagot:/u02/work/bucardo/combine/crres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_crres_goat.backup
fagot:/u02/work/bucardo/combine/crres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_crres_herd', id from goat
bucardo-# where db = 'crres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_crres_goat)
bucardo-# ;
bucardo=# \q
fagot:/u02/work/bucardo/combine/crres # psql -U res crres
crres=# drop table bucardo_tmp_crres_goat; \q
fagot:/u02/work/bucardo/combine/crres # bucardo_ctl add sync combine_crres_sync type=pushdelta source=combine_crres_herd targetdb=combine --verbose

Запустили, проверили, работает. Теперь проверяем соответствие количества записей в таблицах. Все д.б. один в один:

--без учета domain_info
select 'select '''||table_name||''' as table, count(*) from res.'||table_name||' union'
from information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
ORDER BY table_name;

-- с учетом domain_info
select 'select '''||table_name||''' as table, count(*) from res.'||table_name
||' where domain_info = ''root.vsp'' union'
FROM information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
AND (table_name IN (SELECT table_name
FROM information_schema.columns
WHERE column_name = 'domain_info'))
AND table_name not in ('countergen_or','enelement','enelement2recordpoint','enfeeder','enpost',
'enshema','ensubstation','enswitch','enswitchhistory')
ORDER BY table_name;


А у нас не так. У нас бля жопа, как всегда. Ну б.. как так можно работать??? Ну какого х.. Ну вапче непонятно.

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

fagot:/u02/work/pg_dumpall_bucardo # cd /u02/work/bucardo/combine
fagot:/u02/work/bucardo/combine # cat skres_schema.sql | grep "CREATE INDEX" > restore_ix.sql
fagot:/u02/work/bucardo/combine # psql -U res combine
combine=# \o restore_ix.log
combine=# \i restore_ix.sql
combine=# \q
fagot:/u02/work/bucardo/combine #

Программисты дали набор скриптов, результаты по которым сошлись в копейку. Двигаемся дальше...

Новотроицк:

fagot:/u02/work/bucardo/combine # bucardo_ctl add herd combine_ntres_herd
Added herd "combine_ntres_herd"
fagot:/u02/work/bucardo/combine # mkdir ntres; cd ntres
fagot:/u02/work/bucardo/combine/ntres # psql -U res ntres
ntres=# CREATE TABLE res.bucardo_tmp_ntres_goat AS
ntres-# SELECT table_name
ntres-# FROM information_schema.tables
ntres-# WHERE table_schema = 'res'
ntres-# AND table_type = 'BASE TABLE'
ntres-# AND (table_name IN (SELECT table_name
ntres(# FROM information_schema.columns
ntres(# WHERE column_name = 'domain_info'))
ntres-# ORDER BY table_name;
ntres=# \q
fagot:/u02/work/bucardo/combine/ntres # pg_dump -U res -F c -v -f bucardo_tmp_ntres_goat.backup -t bucardo_tmp_ntres_goat ntres
fagot:/u02/work/bucardo/combine/ntres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_ntres_goat.backup
fagot:/u02/work/bucardo/combine/ntres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_ntres_herd', id from goat
bucardo-# where db = 'ntres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_ntres_goat);
bucardo=# \q
fagot:/u02/work/bucardo/combine/ntres # psql -U res ntres
ntres=# drop table bucardo_tmp_ntres_goat; \q
fagot:/u02/work/bucardo/combine/ntres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/ntres/tables # psql -U res ntres
ntres=# \o tdump.sh \t
ntres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
ntres-# ||table_catalog||'.res.'||table_name
ntres-# ||'.backup" -t "'||table_name||'" '
ntres-# ||table_catalog||''
ntres-# FROM information_schema.tables
ntres-# WHERE table_schema = 'res'
ntres-# AND table_type = 'BASE TABLE'
ntres-# AND (table_name IN (SELECT table_name
ntres(# FROM information_schema.columns
ntres(# WHERE column_name = 'domain_info'))
ntres-# ORDER BY table_name;
ntres=# \q
fagot:/u02/work/bucardo/combine/ntres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/ntres/tables # psql -U res ntres
ntres=# \o trestore.sh \t
ntres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
ntres-# ||table_catalog||'.res.'||table_name
ntres-# ||'.backup" '||table_catalog||''
ntres-# FROM information_schema.tables
ntres-# WHERE table_schema = 'res'
ntres-# AND table_type = 'BASE TABLE'
ntres-# AND (table_name IN (SELECT table_name
ntres(# FROM information_schema.columns
ntres(# WHERE column_name = 'domain_info'))
ntres-# ORDER BY table_name;
ntres=# \q
fagot:/u02/work/bucardo/combine/ntres/tables # bash trestore.sh

Во время восстановления не залилась одна таблица со след ошибкой:

pg_restore: restoring data for table "counterentertype"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2913; 0 9730890 TABLE DATA counterentertype res
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "pk_cntrentertp"
CONTEXT: COPY counterentertype, line 1: "1 новый счетчик 0 root.nt"
WARNING: errors ignored on restore: 1

Программисты должны разобраться...
Пока они там решают, таблицу counterentertype из репликации по Новотроицку исключим:

fagot:/u02/work/bucardo/combine/ntres/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'counterentertype'
bucardo(# and db = 'ntres_res')
bucardo-# and herd = 'combine_ntres_herd';
bucardo=# \q

Так, вроде ничего не забыли, делаем sync для Новотроицка:

fagot:/u02/work/bucardo/combine/ntres/tables # cd ..
fagot:/u02/work/bucardo/combine/ntres # bucardo_ctl add sync combine_ntres_sync type=pushdelta source=combine_ntres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/ntres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert' where name='combine_ntres_sync'; bucardo=# \q

После этого в Новотроицке чистим bucardo.bucardo_track и запускаем репликацию. После чего сравниваем данные.

Голая пристань

fagot:/u02/work/bucardo/combine/ntres # cd ..; mkdir gpres; cd gpres
fagot:/u02/work/bucardo/combine/gpres # bucardo_ctl add herd combine_gpres_herd
fagot:/u02/work/bucardo/combine/gpres # psql -U res gpres
gpres=# CREATE TABLE res.bucardo_tmp_gpres_goat AS
gpres-# SELECT table_name
gpres-# FROM information_schema.tables
gpres-# WHERE table_schema = 'res'
gpres-# AND table_type = 'BASE TABLE'
gpres-# AND (table_name IN (SELECT table_name
gpres(# FROM information_schema.columns
gpres(# WHERE column_name = 'domain_info'))
gpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/gpres # pg_dump -U res -F c -v -f bucardo_tmp_gpres_goat.backup -t bucardo_tmp_gpres_goat gpres
fagot:/u02/work/bucardo/combine/gpres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_gpres_goat.backup
fagot:/u02/work/bucardo/combine/gpres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_gpres_herd', id from goat
bucardo-# where db = 'gpres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_gpres_goat); \q
fagot:/u02/work/bucardo/combine/gpres # psql -U res gpres
gpres=# drop table bucardo_tmp_gpres_goat; \q
fagot:/u02/work/bucardo/combine/gpres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/gpres/tables # psql -U res gpres
gpres=# \o tdump.sh \t
gpres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
gpres-# ||table_catalog||'.res.'||table_name
gpres-# ||'.backup" -t "'||table_name||'" '
gpres-# ||table_catalog||''
gpres-# FROM information_schema.tables
gpres-# WHERE table_schema = 'res'
gpres-# AND table_type = 'BASE TABLE'
gpres-# AND (table_name IN (SELECT table_name
gpres(# FROM information_schema.columns
gpres(# WHERE column_name = 'domain_info'))
gpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/gpres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/gpres/tables # psql -U res gpres
gpres=# \o trestore.sh \t
gpres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
gpres-# ||table_catalog||'.res.'||table_name
gpres-# ||'.backup" '||table_catalog||''
gpres-# FROM information_schema.tables
gpres-# WHERE table_schema = 'res'
gpres-# AND table_type = 'BASE TABLE'
gpres-# AND (table_name IN (SELECT table_name
gpres(# FROM information_schema.columns
gpres(# WHERE column_name = 'domain_info'))
gpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/gpres/tables # bash trestore.sh

Получаем ошибку:

pg_restore: restoring data for table "correctionreason"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2931; 0 10048701 TABLE DATA correctionreason res
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "pk_crrsn"
CONTEXT: COPY correctionreason, line 1: "1 Перерахунок в зв'язку з помилкою контролера при знятт..."
WARNING: errors ignored on restore: 1

Таблицу correctionreason из репликации исключаем:

fagot:/u02/work/bucardo/combine/gpres/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'gpres_res')
bucardo-# and herd = 'combine_gpres_herd'; \q
fagot:/u02/work/bucardo/combine/gpres/tables # cd ..
fagot:/u02/work/bucardo/combine/gpres # bucardo_ctl add sync combine_gpres_sync type=pushdelta source=combine_gpres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/gpres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert' where name='combine_gpres_sync'; \q

И вот на нашем мегасупертернистом пути мы столкнулись с еще одной проблемой: порядок столбцов в таблицах разный. А для COPY он д.б. один в один. Тут возникает мысль, а нет ли каши в общей БД? Одно дело когда типы данных столбцов несовместимы, а если это не так???? Вот скрипт, который показывает порядок столбцов:

select table_name, column_name, ordinal_position
from information_schema.columns
where table_schema = 'res'
and table_name in (SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
AND (table_name IN (SELECT table_name
FROM information_schema.columns
WHERE column_name = 'domain_info')))
order by table_schema, table_name, ordinal_position, column_name;

А вот как мы будеи сравнивать:

check.sh:

#!/bin/bash
export CURDIR=$(pwd)
echo $CURDIR
psql -h 22.22.25.2 -U res -f check_insert.sql UTF8_sk
psql -h 22.22.33.2 -U res -f check_insert.sql crres
psql -h 22.22.50.2 -U res -f check_insert.sql ntres
psql -h 22.22.35.2 -U res -f check_insert.sql bzres
psql -h 22.22.41.2 -U res -f check_insert.sql vlres
psql -h 22.22.44.2 -U res -f check_insert.sql brres
psql -h 22.22.59.2 -U res -f check_insert.sql vares
psql -h 22.22.27.2 -U res -f check_insert.sql cpres
psql -h 22.22.49.2 -U res -f check_insert.sql gnres
psql -h 22.22.12.2 -U res -f check_insert.sql nkres
psql -h 22.22.42.3 -U res -f check_insert.sql kvres
psql -h 22.22.26.2 -U res -f check_insert.sql klres
psql -h 22.22.58.2 -U res -f check_insert.sql nvres
psql -h 22.22.57.2 -U res -f check_insert.sql vpres
psql -h 22.22.34.2 -U res -f check_insert.sql gpres

check_insert.sql:

\set filename 'schema_inserts.':DBNAME'.':USER
\o :filename \t
select 'insert into bucardo_tmp_check_schemas(table_catalog, table_schema, table_name, column_name, data_type, ordinal_position) '||
' values('''||table_catalog||''','''||table_schema||''','''||table_name||''','''||column_name||''','''||data_type||''','''||ordinal_position||''');'
from information_schema.columns
where table_schema = 'res'
and table_name in (SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
AND (table_name IN (SELECT table_name
FROM information_schema.columns
WHERE column_name = 'domain_info')))
order by table_schema, table_name, ordinal_position, column_name
\q

check2bucardo.sh:

psql -U bucardo -f schema_inserts.UTF8_sk.res bucardo
psql -U bucardo -f schema_inserts.brres.res bucardo
psql -U bucardo -f schema_inserts.bzres.res bucardo
psql -U bucardo -f schema_inserts.cpres.res bucardo
psql -U bucardo -f schema_inserts.crres.res bucardo
psql -U bucardo -f schema_inserts.gnres.res bucardo
psql -U bucardo -f schema_inserts.gpres.res bucardo
psql -U bucardo -f schema_inserts.klres.res bucardo
psql -U bucardo -f schema_inserts.kvres.res bucardo
psql -U bucardo -f schema_inserts.nkres.res bucardo
psql -U bucardo -f schema_inserts.ntres.res bucardo
psql -U bucardo -f schema_inserts.nvres.res bucardo
psql -U bucardo -f schema_inserts.vares.res bucardo
psql -U bucardo -f schema_inserts.vlres.res bucardo
psql -U bucardo -f schema_inserts.vpres.res bucardo

Естественно предварительно создали таблицу bucardo_tmp_check_schemas.

А теперь приступаем к анализу.

-- таблицы, которых нет в Цюрупинском РЭСе,
-- с учетом разницы по тестовым таблицам
select distinct table_catalog, table_name
from bucardo_tmp_check_schemas
where table_name not in
(select distinct table_name
from bucardo_tmp_check_schemas
where table_catalog = 'crres')
and table_name not in ('enelement','enelement2recordpoint','enfeeder',
'enpost','enshema','ensubstation','enswitch','enswitchhistory')
order by table_catalog, table_name;

нормально

-- разница в полях
select table_catalog, table_name, column_name
from bucardo_tmp_check_schemas
where table_name||'---'||column_name not in
(select table_name||'---'||column_name
from bucardo_tmp_check_schemas
where table_catalog = 'crres')
and table_name not in ('enelement','enelement2recordpoint','enfeeder',
'enpost','enshema','ensubstation','enswitch','enswitchhistory')

нормально

-- разница в типах данных
select table_catalog, table_name, column_name, data_type
from bucardo_tmp_check_schemas
where table_name||'---'||column_name||'---'||data_type not in
(select table_name||'---'||column_name||'---'||data_type
from bucardo_tmp_check_schemas
where table_catalog = 'crres')
and table_name not in ('enelement','enelement2recordpoint','enfeeder',
'enpost','enshema','ensubstation','enswitch','enswitchhistory');

-- not null
-- тут поступаем проще - в РЭСе выносим все not null и устанавливаем из БД combine
select 'alter table res.'||table_name||' alter column "'||column_name||'" set not null;'
from information_schema.columns
where table_catalog = 'combine'
and table_schema = 'res'
and is_nullable = 'NO'
and column_name not in
(select column_name
from information_schema.key_column_usage
where table_catalog = 'combine'
and table_schema = 'res')
order by table_name, column_name;

select 'alter table res.'||table_name||' alter column "'||column_name||'" drop not null;'
from information_schema.columns
where table_catalog = 'combine'
and table_schema = 'res'
and is_nullable = 'NO'
and column_name not in
(select column_name
from information_schema.key_column_usage
where table_catalog = 'combine'
and table_schema = 'res')
order by table_name, column_name;

-- разница в позиции столбцов
drop table bucardo_tmp_check_schemas_position;

create table bucardo_tmp_check_schemas_position as
select table_catalog, table_name, column_name, ordinal_position, ordinal_position as etalon
from bucardo_tmp_check_schemas
where table_name||'---'||column_name||'---'||ordinal_position not in
(select table_name||'---'||column_name||'---'||ordinal_position
from bucardo_tmp_check_schemas
where table_catalog = 'crres')
and table_name not in ('enelement','enelement2recordpoint','enfeeder',
'enpost','enshema','ensubstation','enswitch','enswitchhistory');

select table_catalog, table_name, column_name, ordinal_position,
(select ordinal_position from bucardo_tmp_check_schemas
where table_catalog = 'crres'
and table_name = p.table_name
and column_name = p.column_name) as etalon
from bucardo_tmp_check_schemas_position p

или то же самое одним запросом

select table_catalog, table_name, column_name, ordinal_position,
(select ordinal_position from bucardo_tmp_check_schemas
where table_catalog = 'crres'
and table_name = p.table_name
and column_name = p.column_name) as etalon
from (select table_catalog, table_name, column_name, ordinal_position, ordinal_position as etalon
from bucardo_tmp_check_schemas
where table_name||'---'||column_name||'---'||ordinal_position not in
(select table_name||'---'||column_name||'---'||ordinal_position
from bucardo_tmp_check_schemas
where table_catalog = 'crres')
and table_name not in ('enelement','enelement2recordpoint','enfeeder',
'enpost','enshema','ensubstation','enswitch','enswitchhistory')) p

ИТАК. По результатам исследования мы выровняли типы данных, но пока не можем выровнять позиции полей. Это дело веселое, и требует обстоятельного подхода... А пока вот засранцы, где с последовательностью полей есть проблемы:

brres
bzres
gpres
nkres
ntres

К сожалению среди них оказался Новотроицк. Его из combine пока выносим и продолжаем добавлять те РЭСы где проблем нет. К проблемным вернемся попозже...

Настроенные объекты репликации удалять не будем. Просто вынесем данные неправильных РЭСов и переведем sync в inactive состояние.

SELECT 'delete from res.'||table_name||' where domain_info !=''root''; commit;'
FROM information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
AND (table_name IN (SELECT table_name
FROM information_schema.columns
WHERE column_name = 'domain_info'))
ORDER BY table_name;

Так, проыерили, подравняли, пришли к тому состоянию, что в БД combine только Цюрупинск, сверенный и красивый. Определили РЭСы, которые пока не трогаем. А те, что трогаем - ну... трогаем, а как же есче...

Высокополье (потому что маленький):

fagot:/u02/work/bucardo # cd /u02/work/bucardo/combine; mkdir vpres; cd vpres
fagot:/u02/work/bucardo/combine/vpres # bucardo_ctl add herd combine_vpres_herd
fagot:/u02/work/bucardo/combine/vpres # psql -U res vpres
vpres=# CREATE TABLE res.bucardo_tmp_vpres_goat AS
vpres-# SELECT table_name
vpres-# FROM information_schema.tables
vpres-# WHERE table_schema = 'res'
vpres-# AND table_type = 'BASE TABLE'
vpres-# AND (table_name IN (SELECT table_name
vpres(# FROM information_schema.columns
vpres(# WHERE column_name = 'domain_info'))
vpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vpres # pg_dump -U res -F c -v -f bucardo_tmp_vpres_goat.backup -t bucardo_tmp_vpres_goat vpres
fagot:/u02/work/bucardo/combine/vpres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_vpres_goat.backup
fagot:/u02/work/bucardo/combine/vpres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_vpres_herd', id from goat
bucardo-# where db = 'vpres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_vpres_goat); \q
fagot:/u02/work/bucardo/combine/vpres # psql -U res vpres
vpres=# drop table bucardo_tmp_vpres_goat; \q
fagot:/u02/work/bucardo/combine/vpres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/vpres/tables # psql -U res vpres
vpres=# \o tdump.sh \t
vpres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
vpres-# ||table_catalog||'.res.'||table_name
vpres-# ||'.backup" -t "'||table_name||'" '
vpres-# ||table_catalog||''
vpres-# FROM information_schema.tables
vpres-# WHERE table_schema = 'res'
vpres-# AND table_type = 'BASE TABLE'
vpres-# AND (table_name IN (SELECT table_name
vpres(# FROM information_schema.columns
vpres(# WHERE column_name = 'domain_info'))
vpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vpres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/vpres/tables # psql -U res vpres
vpres=# \o trestore.sh \t
vpres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
vpres-# ||table_catalog||'.res.'||table_name
vpres-# ||'.backup" '||table_catalog||''
vpres-# FROM information_schema.tables
vpres-# WHERE table_schema = 'res'
vpres-# AND table_type = 'BASE TABLE'
vpres-# AND (table_name IN (SELECT table_name
vpres(# FROM information_schema.columns
vpres(# WHERE column_name = 'domain_info'))
vpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vpres/tables # bash trestore.sh

Как уже стало привычным(надо учесть на будущее), получаем:

pg_restore: restoring data for table "correctionreason"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2926; 0 10049169 TABLE DATA correctionreason res
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "pk_crrsn"
CONTEXT: COPY correctionreason, line 1: "1 Перерахунок в зв'язку з помилкою контролера при знятт..."
WARNING: errors ignored on restore: 1

fagot:/u02/work/bucardo/combine/vpres/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'vpres_res')
bucardo-# and herd = 'combine_vpres_herd'; \q
fagot:/u02/work/bucardo/combine/vpres/tables # cd ..
fagot:/u02/work/bucardo/combine/vpres # bucardo_ctl add sync combine_vpres_sync type=pushdelta source=combine_vpres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/vpres # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_vpres_sync'; \q
fagot:/u02/work/bucardo/combine/vpres # psql -U postgres -h 22.22.57.2 vpres
vpres=# truncate table bucardo.bucardo_track; \q

Проверяем. Ко всем уже существующим проверкам добавляем проверку domain_info:

select 'select distinct '''||table_name||''', domain_info from res.'||table_name||' union'
FROM information_schema.tables
WHERE table_schema = 'res'
AND table_type = 'BASE TABLE'
AND (table_name IN (SELECT table_name
FROM information_schema.columns
WHERE column_name = 'domain_info'))
ORDER BY table_name;

Пустых быть не должно. Если находим - отдаем засранцев программистам.

Ну что, Высокополье бегает. Продолжаем в том же духе.

Скадовск:

fagot:/u02/work/bucardo/combine/vpres # cd ..; mkdir skres; cd skres
fagot:/u02/work/bucardo/combine/skres # bucardo_ctl add herd combine_skres_herd
fagot:/u02/work/bucardo/combine/skres # psql -U res skres
skres=# CREATE TABLE res.bucardo_tmp_skres_goat AS
skres-# SELECT table_name
skres-# FROM information_schema.tables
skres-# WHERE table_schema = 'res'
skres-# AND table_type = 'BASE TABLE'
skres-# AND (table_name IN (SELECT table_name
skres(# FROM information_schema.columns
skres(# WHERE column_name = 'domain_info'))
skres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/skres # pg_dump -U res -F c -v -f bucardo_tmp_skres_goat.backup -t bucardo_tmp_skres_goat skres
fagot:/u02/work/bucardo/combine/skres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_skres_goat.backup
fagot:/u02/work/bucardo/combine/skres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_skres_herd', id from goat
bucardo-# where db = 'skres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_skres_goat); \q
fagot:/u02/work/bucardo/combine/skres # psql -U res skres
skres=# drop table bucardo_tmp_skres_goat; \q
fagot:/u02/work/bucardo/combine/skres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/skres/tables # psql -U res skres
skres=# \o tdump.sh \t
skres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
skres-# ||table_catalog||'.res.'||table_name
skres-# ||'.backup" -t "'||table_name||'" '
skres-# ||table_catalog||''
skres-# FROM information_schema.tables
skres-# WHERE table_schema = 'res'
skres-# AND table_type = 'BASE TABLE'
skres-# AND (table_name IN (SELECT table_name
skres(# FROM information_schema.columns
skres(# WHERE column_name = 'domain_info'))
skres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/skres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/skres/tables # psql -U res skres
skres=# \o trestore.sh \t
skres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
skres-# ||table_catalog||'.res.'||table_name
skres-# ||'.backup" '||table_catalog||''
skres-# FROM information_schema.tables
skres-# WHERE table_schema = 'res'
skres-# AND table_type = 'BASE TABLE'
skres-# AND (table_name IN (SELECT table_name
skres(# FROM information_schema.columns
skres(# WHERE column_name = 'domain_info'))
skres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/skres/tables # bash trestore.sh

correctionreason исключаем из репликации (пока с ней вопрос неясен)

fagot:/u02/work/bucardo/combine/skres/tables # cd ..; psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'skres_res')
bucardo-# and herd = 'combine_skres_herd'; \q
fagot:/u02/work/bucardo/combine/skres # bucardo_ctl add sync combine_skres_sync type=pushdelta source=combine_skres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/skres # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_skres_sync'; \q
fagot:/u02/work/bucardo/combine/skres # psql -U postgres -h 22.22.25.2 UTF8_sk
UTF8_sk=# truncate table bucardo.bucardo_track; \q

Чаплынка

fagot:/u02/work/bucardo/combine/skres # cd ..; mkdir cpres; cd cpres
fagot:/u02/work/bucardo/combine/cpres # bucardo_ctl add herd combine_cpres_herd
fagot:/u02/work/bucardo/combine/cpres # psql -U res cpres
cpres=# CREATE TABLE res.bucardo_tmp_cpres_goat AS
cpres-# SELECT table_name
cpres-# FROM information_schema.tables
cpres-# WHERE table_schema = 'res'
cpres-# AND table_type = 'BASE TABLE'
cpres-# AND (table_name IN (SELECT table_name
cpres(# FROM information_schema.columns
cpres(# WHERE column_name = 'domain_info'))
cpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/cpres # pg_dump -U res -F c -v -f bucardo_tmp_cpres_goat.backup -t bucardo_tmp_cpres_goat cpres
fagot:/u02/work/bucardo/combine/cpres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_cpres_goat.backup
fagot:/u02/work/bucardo/combine/cpres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_cpres_herd', id from goat
bucardo-# where db = 'cpres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_cpres_goat); \q
fagot:/u02/work/bucardo/combine/cpres # psql -U res cpres
cpres=# drop table bucardo_tmp_cpres_goat; \q
fagot:/u02/work/bucardo/combine/cpres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/cpres/tables # psql -U res cpres
cpres=# \o tdump.sh \t
cpres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
cpres-# ||table_catalog||'.res.'||table_name
cpres-# ||'.backup" -t "'||table_name||'" '
cpres-# ||table_catalog||''
cpres-# FROM information_schema.tables
cpres-# WHERE table_schema = 'res'
cpres-# AND table_type = 'BASE TABLE'
cpres-# AND (table_name IN (SELECT table_name
cpres(# FROM information_schema.columns
cpres(# WHERE column_name = 'domain_info'))
cpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/cpres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/cpres/tables # psql -U res cpres
cpres=# \o trestore.sh \t
cpres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
cpres-# ||table_catalog||'.res.'||table_name
cpres-# ||'.backup" '||table_catalog||''
cpres-# FROM information_schema.tables
cpres-# WHERE table_schema = 'res'
cpres-# AND table_type = 'BASE TABLE'
cpres-# AND (table_name IN (SELECT table_name
cpres(# FROM information_schema.columns
cpres(# WHERE column_name = 'domain_info'))
cpres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/cpres/tables # bash trestore.sh
fagot:/u02/work/bucardo/combine/cpres/tables # cd ..; psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'cpres_res')
bucardo-# and herd = 'combine_cpres_herd'; \q
fagot:/u02/work/bucardo/combine/cpres # bucardo_ctl add sync combine_cpres_sync type=pushdelta source=combine_cpres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/cpres # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_cpres_sync'; \q
fagot:/u02/work/bucardo/combine/cpres # psql -U postgres -h 22.22.27.2 cpres
cpres=# truncate table bucardo.bucardo_track; \q

Геническ

fagot:/u02/work/bucardo/combine/cpres # cd ..; mkdir gnres; cd gnres
fagot:/u02/work/bucardo/combine/gnres # bucardo_ctl add herd combine_gnres_herd
fagot:/u02/work/bucardo/combine/gnres # psql -U res gnres
gnres=# CREATE TABLE res.bucardo_tmp_gnres_goat AS
gnres-# SELECT table_name
gnres-# FROM information_schema.tables
gnres-# WHERE table_schema = 'res'
gnres-# AND table_type = 'BASE TABLE'
gnres-# AND (table_name IN (SELECT table_name
gnres(# FROM information_schema.columns
gnres(# WHERE column_name = 'domain_info'))
gnres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/gnres # pg_dump -U res -F c -v -f bucardo_tmp_gnres_goat.backup -t bucardo_tmp_gnres_goat gnres
fagot:/u02/work/bucardo/combine/gnres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_gnres_goat.backup
fagot:/u02/work/bucardo/combine/gnres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_gnres_herd', id from goat
bucardo-# where db = 'gnres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_gnres_goat); \q
fagot:/u02/work/bucardo/combine/gnres # psql -U res gnres
gnres=# drop table bucardo_tmp_gnres_goat; \q
fagot:/u02/work/bucardo/combine/gnres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/gnres/tables # psql -U res gnres
gnres=# \o tdump.sh \t
gnres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
gnres-# ||table_catalog||'.res.'||table_name
gnres-# ||'.backup" -t "'||table_name||'" '
gnres-# ||table_catalog||''
gnres-# FROM information_schema.tables
gnres-# WHERE table_schema = 'res'
gnres-# AND table_type = 'BASE TABLE'
gnres-# AND (table_name IN (SELECT table_name
gnres(# FROM information_schema.columns
gnres(# WHERE column_name = 'domain_info'))
gnres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/gnres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/gnres/tables # psql -U res gnres
gnres=# \o trestore.sh \t
gnres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
gnres-# ||table_catalog||'.res.'||table_name
gnres-# ||'.backup" '||table_catalog||''
gnres-# FROM information_schema.tables
gnres-# WHERE table_schema = 'res'
gnres-# AND table_type = 'BASE TABLE'
gnres-# AND (table_name IN (SELECT table_name
gnres(# FROM information_schema.columns
gnres(# WHERE column_name = 'domain_info'))
gnres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/gnres/tables # bash trestore.sh
fagot:/u02/work/bucardo/combine/gnres/tables # cd ..; psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'gnres_res')
bucardo-# and herd = 'combine_gnres_herd'; \q
fagot:/u02/work/bucardo/combine/gnres # bucardo_ctl add sync combine_gnres_sync type=pushdelta source=combine_gnres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/gnres # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_gnres_sync'; \q
fagot:/u02/work/bucardo/combine/gnres # psql -U postgres -h 22.22.49.2 gnres
gnres=# truncate table bucardo.bucardo_track; \q

Каланчак:

fagot:/u02/work/bucardo/combine/gnres # cd ..; mkdir klres; cd klres
fagot:/u02/work/bucardo/combine/klres # bucardo_ctl add herd combine_klres_herd
fagot:/u02/work/bucardo/combine/klres # psql -U res klres
klres=# CREATE TABLE res.bucardo_tmp_klres_goat AS
klres-# SELECT table_name
klres-# FROM information_schema.tables
klres-# WHERE table_schema = 'res'
klres-# AND table_type = 'BASE TABLE'
klres-# AND (table_name IN (SELECT table_name
klres(# FROM information_schema.columns
klres(# WHERE column_name = 'domain_info'))
klres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/klres # pg_dump -U res -F c -v -f bucardo_tmp_klres_goat.backup -t bucardo_tmp_klres_goat klres
fagot:/u02/work/bucardo/combine/klres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_klres_goat.backup
fagot:/u02/work/bucardo/combine/klres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_klres_herd', id from goat
bucardo-# where db = 'klres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_klres_goat); \q
fagot:/u02/work/bucardo/combine/klres # psql -U res klres
klres=# drop table bucardo_tmp_klres_goat; \q
fagot:/u02/work/bucardo/combine/klres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/klres/tables # psql -U res klres
klres=# \o tdump.sh \t
klres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
klres-# ||table_catalog||'.res.'||table_name
klres-# ||'.backup" -t "'||table_name||'" '||table_catalog||''
klres-# FROM information_schema.tables
klres-# WHERE table_schema = 'res'
klres-# AND table_type = 'BASE TABLE'
klres-# AND (table_name IN (SELECT table_name
klres(# FROM information_schema.columns
klres(# WHERE column_name = 'domain_info'))
klres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/klres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/klres/tables # psql -U res klres
klres=# \o trestore.sh \t
klres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
klres-# ||table_catalog||'.res.'||table_name
klres-# ||'.backup" '||table_catalog||''
klres-# FROM information_schema.tables
klres-# WHERE table_schema = 'res'
klres-# AND table_type = 'BASE TABLE'
klres-# AND (table_name IN (SELECT table_name
klres(# FROM information_schema.columns
klres(# WHERE column_name = 'domain_info'))
klres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/klres/tables # bash trestore.sh
fagot:/u02/work/bucardo/combine/klres/tables # cd ..; psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'klres_res')
bucardo-# and herd = 'combine_klres_herd'; \q
fagot:/u02/work/bucardo/combine/klres # bucardo_ctl add sync combine_klres_sync type=pushdelta source=combine_klres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/klres # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_klres_sync'; \q
fagot:/u02/work/bucardo/combine/klres # psql -U postgres -h 22.22.26.2 klres
klres=# truncate table bucardo.bucardo_track; \q

Каховка

fagot:/u02/work/bucardo/combine/klres # cd ..; mkdir kvres; cd kvres
fagot:/u02/work/bucardo/combine/kvres # bucardo_ctl add herd combine_kvres_herd
fagot:/u02/work/bucardo/combine/kvres # psql -U res kvres
kvres=# CREATE TABLE res.bucardo_tmp_kvres_goat AS
kvres-# SELECT table_name
kvres-# FROM information_schema.tables
kvres-# WHERE table_schema = 'res'
kvres-# AND table_type = 'BASE TABLE'
kvres-# AND (table_name IN (SELECT table_name
kvres(# FROM information_schema.columns
kvres(# WHERE column_name = 'domain_info'))
kvres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/kvres # pg_dump -U res -F c -v -f bucardo_tmp_kvres_goat.backup -t bucardo_tmp_kvres_goat kvres
fagot:/u02/work/bucardo/combine/kvres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_kvres_goat.backup
fagot:/u02/work/bucardo/combine/kvres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_kvres_herd', id from goat
bucardo-# where db = 'kvres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_kvres_goat); \q
fagot:/u02/work/bucardo/combine/kvres # psql -U res kvres
kvres=# drop table bucardo_tmp_kvres_goat; \q
fagot:/u02/work/bucardo/combine/kvres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/kvres/tables # psql -U res kvres
kvres=# \o tdump.sh \t
kvres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
kvres-# ||table_catalog||'.res.'||table_name
kvres-# ||'.backup" -t "'||table_name||'" '||table_catalog||''
kvres-# FROM information_schema.tables
kvres-# WHERE table_schema = 'res'
kvres-# AND table_type = 'BASE TABLE'
kvres-# AND (table_name IN (SELECT table_name
kvres(# FROM information_schema.columns
kvres(# WHERE column_name = 'domain_info'))
kvres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/kvres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/kvres/tables # psql -U res kvres
kvres=# \o trestore.sh \t
Showing only tuples.
kvres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
kvres-# ||table_catalog||'.res.'||table_name
kvres-# ||'.backup" '||table_catalog||''
kvres-# FROM information_schema.tables
kvres-# WHERE table_schema = 'res'
kvres-# AND table_type = 'BASE TABLE'
kvres-# AND (table_name IN (SELECT table_name
kvres(# FROM information_schema.columns
kvres(# WHERE column_name = 'domain_info'))
kvres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/kvres/tables # bash trestore.sh
fagot:/u02/work/bucardo/combine/kvres/tables # cd ..; psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'kvres_res')
bucardo-# and herd = 'combine_kvres_herd'; \q
fagot:/u02/work/bucardo/combine/kvres # bucardo_ctl add sync combine_kvres_sync type=pushdelta source=combine_kvres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/kvres # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_kvres_sync'; \q
fagot:/u02/work/bucardo/combine/kvres # psql -U postgres -h 22.22.42.3 kvres
kvres=# truncate table bucardo.bucardo_track; \q

Нововоронцовка

fagot:/u02/work/bucardo/combine/kvres # cd ..; mkdir nvres; cd nvres
fagot:/u02/work/bucardo/combine/nvres # bucardo_ctl add herd combine_nvres_herd
fagot:/u02/work/bucardo/combine/nvres # psql -U res nvres
nvres=# CREATE TABLE res.bucardo_tmp_nvres_goat AS
nvres-# SELECT table_name
nvres-# FROM information_schema.tables
nvres-# WHERE table_schema = 'res'
nvres-# AND table_type = 'BASE TABLE'
nvres-# AND (table_name IN (SELECT table_name
nvres(# FROM information_schema.columns
nvres(# WHERE column_name = 'domain_info'))
nvres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/nvres # pg_dump -U res -F c -v -f bucardo_tmp_nvres_goat.backup -t bucardo_tmp_nvres_goat nvres
fagot:/u02/work/bucardo/combine/nvres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_nvres_goat.backup
fagot:/u02/work/bucardo/combine/nvres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_nvres_herd', id from goat
bucardo-# where db = 'nvres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_nvres_goat); \q
fagot:/u02/work/bucardo/combine/nvres # psql -U res nvres
nvres=# drop table bucardo_tmp_nvres_goat; \q
fagot:/u02/work/bucardo/combine/nvres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/nvres/tables # psql -U res nvres
nvres=# \o tdump.sh \t
Showing only tuples.
nvres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
nvres-# ||table_catalog||'.res.'||table_name
nvres-# ||'.backup" -t "'||table_name||'" '||table_catalog||''
nvres-# FROM information_schema.tables
nvres-# WHERE table_schema = 'res'
nvres-# AND table_type = 'BASE TABLE'
nvres-# AND (table_name IN (SELECT table_name
nvres(# FROM information_schema.columns
nvres(# WHERE column_name = 'domain_info'))
nvres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/nvres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/nvres/tables # psql -U res nvres
nvres=# \o trestore.sh \t
nvres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
nvres-# ||table_catalog||'.res.'||table_name
nvres-# ||'.backup" '||table_catalog||''
nvres-# FROM information_schema.tables
nvres-# WHERE table_schema = 'res'
nvres-# AND table_type = 'BASE TABLE'
nvres-# AND (table_name IN (SELECT table_name
nvres(# FROM information_schema.columns
nvres(# WHERE column_name = 'domain_info'))
nvres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/nvres/tables # bash trestore.sh
fagot:/u02/work/bucardo/combine/nvres/tables # cd ..; psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'nvres_res')
bucardo-# and herd = 'combine_nvres_herd'; \q
fagot:/u02/work/bucardo/combine/nvres # bucardo_ctl add sync combine_nvres_sync type=pushdelta source=combine_nvres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/nvres # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_nvres_sync'; \q
fagot:/u02/work/bucardo/combine/nvres # psql -U postgres -h 22.22.58.2 nvres
nvres=# truncate table bucardo.bucardo_track; \q

В.Александровка

fagot:/u02/work/bucardo/combine/nvres # cd ..; mkdir vares; cd vares
fagot:/u02/work/bucardo/combine/vares # bucardo_ctl add herd combine_vares_herd
fagot:/u02/work/bucardo/combine/vares # psql -U res vares
vares=# CREATE TABLE res.bucardo_tmp_vares_goat AS
vares-# SELECT table_name
vares-# FROM information_schema.tables
vares-# WHERE table_schema = 'res'
vares-# AND table_type = 'BASE TABLE'
vares-# AND (table_name IN (SELECT table_name
vares(# FROM information_schema.columns
vares(# WHERE column_name = 'domain_info'))
vares-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vares # pg_dump -U res -F c -v -f bucardo_tmp_vares_goat.backup -t bucardo_tmp_vares_goat vares
fagot:/u02/work/bucardo/combine/vares # pg_restore -U bucardo -d bucardo -v bucardo_tmp_vares_goat.backup
fagot:/u02/work/bucardo/combine/vares # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_vares_herd', id from goat
bucardo-# where db = 'vares_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_vares_goat); \q
fagot:/u02/work/bucardo/combine/vares # psql -U res vares
vares=# drop table bucardo_tmp_vares_goat; \q
fagot:/u02/work/bucardo/combine/vares # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/vares/tables # psql -U res vares
vares=# \o tdump.sh \t
vares=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
vares-# ||table_catalog||'.res.'||table_name
vares-# ||'.backup" -t "'||table_name||'" '||table_catalog||''
vares-# FROM information_schema.tables
vares-# WHERE table_schema = 'res'
vares-# AND table_type = 'BASE TABLE'
vares-# AND (table_name IN (SELECT table_name
vares(# FROM information_schema.columns
vares(# WHERE column_name = 'domain_info'))
vares-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vares/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/vares/tables # psql -U res vares
vares=# \o trestore.sh \t
vares=# SELECT 'pg_restore -U res -d combine -a -v "table.'
vares-# ||table_catalog||'.res.'||table_name
vares-# ||'.backup" '||table_catalog||''
vares-# FROM information_schema.tables
vares-# WHERE table_schema = 'res'
vares-# AND table_type = 'BASE TABLE'
vares-# AND (table_name IN (SELECT table_name
vares(# FROM information_schema.columns
vares(# WHERE column_name = 'domain_info'))
vares-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vares/tables # bash trestore.sh
fagot:/u02/work/bucardo/combine/vares/tables # cd ..; psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'vares_res')
bucardo-# and herd = 'combine_vares_herd'; \q
fagot:/u02/work/bucardo/combine/vares # bucardo_ctl add sync combine_vares_sync type=pushdelta source=combine_vares_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/vares # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_vares_sync'; \q
fagot:/u02/work/bucardo/combine/vares # psql -U postgres -h 22.22.59.2 vares
vares=# truncate table bucardo.bucardo_track; \q

В.Лепетиха

fagot:/u02/work/bucardo/combine/vares # cd ..; mkdir vlres; cd vlres
fagot:/u02/work/bucardo/combine/vlres # bucardo_ctl add herd combine_vlres_herd
fagot:/u02/work/bucardo/combine/vlres # psql -U res vlres
vlres=# CREATE TABLE res.bucardo_tmp_vlres_goat AS
vlres-# SELECT table_name
vlres-# FROM information_schema.tables
vlres-# WHERE table_schema = 'res'
vlres-# AND table_type = 'BASE TABLE'
vlres-# AND (table_name IN (SELECT table_name
vlres(# FROM information_schema.columns
vlres(# WHERE column_name = 'domain_info'))
vlres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vlres # pg_dump -U res -F c -v -f bucardo_tmp_vlres_goat.backup -t bucardo_tmp_vlres_goat vlres
fagot:/u02/work/bucardo/combine/vlres # pg_restore -U bucardo -d bucardo -v bucardo_tmp_vlres_goat.backup
fagot:/u02/work/bucardo/combine/vlres # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_vlres_herd', id from goat
bucardo-# where db = 'vlres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_vlres_goat); \q
fagot:/u02/work/bucardo/combine/vlres # psql -U res vlres
vlres=# drop table bucardo_tmp_vlres_goat; \q
fagot:/u02/work/bucardo/combine/vlres # mkdir tables; cd tables/
fagot:/u02/work/bucardo/combine/vlres/tables # psql -U res vlres
vlres=# \o tdump.sh \t
vlres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
vlres-# ||table_catalog||'.res.'||table_name
vlres-# ||'.backup" -t "'||table_name||'" '||table_catalog||''
vlres-# FROM information_schema.tables
vlres-# WHERE table_schema = 'res'
vlres-# AND table_type = 'BASE TABLE'
vlres-# AND (table_name IN (SELECT table_name
vlres(# FROM information_schema.columns
vlres(# WHERE column_name = 'domain_info'))
vlres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vlres/tables # bash tdump.sh
fagot:/u02/work/bucardo/combine/vlres/tables # psql -U res vlres
vlres=# \o trestore.sh \t
vlres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
vlres-# ||table_catalog||'.res.'||table_name
vlres-# ||'.backup" '||table_catalog||''
vlres-# FROM information_schema.tables
vlres-# WHERE table_schema = 'res'
vlres-# AND table_type = 'BASE TABLE'
vlres-# AND (table_name IN (SELECT table_name
vlres(# FROM information_schema.columns
vlres(# WHERE column_name = 'domain_info'))
vlres-# ORDER BY table_name; \q
fagot:/u02/work/bucardo/combine/vlres/tables # bash trestore.sh
fagot:/u02/work/bucardo/combine/vlres/tables # cd ..; psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'vlres_res')
bucardo-# and herd = 'combine_vlres_herd'; \q
fagot:/u02/work/bucardo/combine/vlres # bucardo_ctl add sync combine_vlres_sync type=pushdelta source=combine_vlres_herd targetdb=combine --verbose
fagot:/u02/work/bucardo/combine/vlres # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert'
bucardo-# where name = 'combine_vlres_sync'; \q
fagot:/u02/work/bucardo/combine/vlres # psql -U postgres -h 22.22.41.2 vlres
vlres=# truncate table bucardo.bucardo_track; \q

Ивановка

Ивановку буквально только что перевели на PostgreSQL. Так что для нее повторяем всю работу, как делали для каждого РЭСа.

Добавляем БД один в один:

fagot:~ # mkdir /u02/pgdata/ivres; mkdir ~/work/ivres; cd ~/work/ivres
fagot:~/work/ivres # chown postgres:postgres /u02/pgdata/ivres
fagot:~/work/ivres # psql -U postgres postgres
postgres=# CREATE TABLESPACE ivres OWNER res LOCATION '/u02/pgdata/ivres';
postgres=# CREATE DATABASE ivres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=ivres;
postgres=# \c ivres
ivres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:~/work/ivres # psql -h 22.22.52.2 -U postgres ivres
ivres=# ALTER ROLE res SUPERUSER; \q
fagot:~/work/ivres # bucardo_ctl add database ivres name=ivres_res host=22.22.52.2 user=res pass=a
fagot:~/work/ivres # bucardo_ctl add database ivres name=ivres_obl host=22.22.11.72 user=res pass=r
fagot:~/work/ivres # bucardo_ctl add all tables db=ivres_res --herd=ivres_herd --verbose
fagot:~/work/ivres # bucardo_ctl remove table log rp_abon dual billsqlreport
fagot:~/work/ivres # pg_dump -h 22.22.52.2 -U postgres -F p -s -s -v -f "ivres_schema.sql" -n 'res' "ivres"