2010-06-10

Bucardo 4.x.x хроника 3

Хроники вечного :) Очередная, надеюсь последняя, настройка репликации...



Общая подготовка:

fagot:~ # mkdir /u02/addres; cd /u02/addres
fagot:~ # mkdir combine; cd combine
fagot:/u02/addres/combine # pg_dump -h 1.1.25.2 -U postgres -F p -s -v -f "skres_schema.sql" -n 'res' "UTF8_sk"
fagot:/u02/addres/combine # psql -U postgres postgres
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:/u02/addres/combine # psql -f skres_schema.sql -U res combine
fagot:/u02/addres/combine # psql -U res combine
combine=# vacuum full analyze verbose;
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 \i drop_fk.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 \i drop_ix.sql \q

Белозерка 1 в 1:

fagot:~ # cd /u02/addres; mkdir bzres; cd bzres
fagot:/u02/addres/bzres # psql -U postgres postgres
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:/u02/addres/bzres # bucardo_ctl add all tables db=bzres_res --herd=bzres_herd --verbose
fagot:/u02/addres/bzres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/bzres # pg_dump -h 1.1.35.2 -U postgres -F p -s -v -f "bzres_schema.sql" -n 'res' "bzres"
fagot:/u02/addres/bzres # psql -f bzres_schema.sql -U res bzres
fagot:/u02/addres/bzres # ssh root@1.1.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:/u02/addres/bzres # bucardo_ctl add sync res_bzres_sync type=pushdelta source=bzres_herd targetdb=bzres_obl --verbose
fagot:/u02/addres/bzres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.35.2/postgres/pgsql.bzres
fagot:/u02/addres/bzres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert' where name='res_bzres_sync';
bucardo=# \c bzres res
bzres=# vacuum full analyze verbose;
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 \i drop_fk.sql
bzres=# \o drop_ix.sql
bzres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
bzres=# from pg_indexes
bzres=# where schemaname = 'res';
bzres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/bzres # pg_restore -U res -d bzres -v -a "pgsql.bzres"
fagot:/u02/addres/bzres # grep 'CREATE INDEX' -B 1 bzres_schema.sql > restore_ix.sql
fagot:/u02/addres/bzres # grep 'CREATE UNIQUE INDEX' -B 1 bzres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/bzres # psql -U res bzres
bzres=# truncate table log;
bzres=# truncate table rp_abon;
bzres=# truncate table dual;
bzres=# truncate table billsqlreport;
bzres=# \i restore_ix.sql \i restore_u_ix.sql
bzres=# vacuum full analyze verbose; \q

Белозерка combine:

fagot:/u02/addres/bzres # cd ..; mkdir bzresc; cd bzresc
fagot:/u02/addres/bzresc # bucardo_ctl add herd combine_bzres_herd
fagot:/u02/addres/bzresc # psql -U res bzres
bzres=# CREATE TABLE res.bucardo_tmp_bzres_goat AS
bzres-# SELECT table_name
bzres-# FROM information_schema.tables
bzres-# WHERE table_schema = 'res'
bzres-# AND table_type = 'BASE TABLE'
bzres-# AND (table_name IN (SELECT table_name
bzres(# FROM information_schema.columns
bzres(# WHERE column_name = 'domain_info'))
bzres-# ORDER BY table_name; \q
fagot:/u02/addres/bzresc # pg_dump -U res -F c -v -f bucardo_tmp_bzres_goat.backup -t bucardo_tmp_bzres_goat bzres
fagot:/u02/addres/bzresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_bzres_goat.backup
fagot:/u02/addres/bzresc # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_bzres_herd', id from goat
bucardo-# where db = 'bzres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_bzres_goat); \q
fagot:/u02/addres/bzresc # psql -U res bzres
bzres=# drop table bucardo_tmp_bzres_goat; \q
fagot:/u02/addres/bzresc # bucardo_ctl stop "stop for add sync combine_bzres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/bzresc # bucardo_ctl add sync combine_bzres_sync type=pushdelta source=combine_bzres_herd targetdb=combine --verbose
fagot:/u02/addres/bzresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_bzres_sync'; \q
fagot:/u02/addres/bzresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_bzres_sync"
### тут ждем пока произойдет синхронизация Белозерки
fagot:/var/log/bucardo # cd /u02/addres/bzresc
fagot:/u02/addres/bzresc # bucardo_ctl stop "stop for add tables combine_bzres_sync"
fagot:/u02/addres/bzresc # mkdir tables; cd tables
fagot:/u02/addres/bzresc/tables # psql -U res bzres
bzres=# \o tdump.sh \t
bzres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
bzres-# ||table_catalog||'.res.'||table_name
bzres-# ||'.backup" -t "'||table_name||'" '
bzres-# ||table_catalog||''
bzres-# FROM information_schema.tables
bzres-# WHERE table_schema = 'res'
bzres-# AND table_type = 'BASE TABLE'
bzres-# AND (table_name IN (SELECT table_name
bzres(# FROM information_schema.columns
bzres(# WHERE column_name = 'domain_info'))
bzres-# ORDER BY table_name; \q
fagot:/u02/addres/bzresc/tables # bash tdump.sh
fagot:/u02/addres/bzresc/tables # psql -U res bzres
bzres=# \o trestore.sh \t
bzres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
bzres-# ||table_catalog||'.res.'||table_name
bzres-# ||'.backup" '||table_catalog||''
bzres-# FROM information_schema.tables
bzres-# WHERE table_schema = 'res'
bzres-# AND table_type = 'BASE TABLE'
bzres-# AND (table_name IN (SELECT table_name
bzres(# FROM information_schema.columns
bzres(# WHERE column_name = 'domain_info'))
bzres-# ORDER BY table_name; \q
fagot:/u02/addres/bzresc/tables # bash trestore.sh
fagot:/u02/addres/bzresc/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'bzres_res')
bucardo-# and herd = 'combine_bzres_herd';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_bzres_sync'; \q
fagot:/u02/addres/bzresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_bzres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_bzres_sync"

Александровка 1 в 1:

fagot:~ # cd /u02/addres; mkdir vares; cd vares
fagot:/u02/addres/vares # psql -U postgres postgres
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:/u02/addres/vares # bucardo_ctl add all tables db=vares_res --herd=vares_herd --verbose
fagot:/u02/addres/vares # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/vares # pg_dump -h 1.1.59.2 -U postgres -F p -s -v -f "vares_schema.sql" -n 'res' "vares"
fagot:/u02/addres/vares # psql -f vares_schema.sql -U res vares
fagot:/u02/addres/vares # ssh root@1.1.59.2
linux01:/files # mkdir /files/postgres; chown postgres:postgres /files/postgres
linux01:/files # su - postgres -c "cd /files/postgres; pg_dump -C -F c -b -f pgsql.vares "vares""; exit
fagot:/u02/addres/vares # bucardo_ctl add sync res_vares_sync type=pushdelta source=vares_herd targetdb=vares_obl --verbose
fagot:/u02/addres/vares # wget --no-proxy -c --ftp-user=a --ftp-password=a ftp://1.1.59.2/postgres/pgsql.vares
fagot:/u02/addres/vares # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert' where name='res_vares_sync';
bucardo=# \c vares res
vares=# vacuum full analyze verbose;
vares=# \o drop_fk.sql \t
vares=# SELECT 'ALTER TABLE res.'||t.tablename
vares-# ||' DROP CONSTRAINT '||con.conname||';'
vares-# FROM pg_constraint con, pg_class cl, pg_tables t,
vares-# information_schema.constraint_column_usage cc
vares-# WHERE t.schemaname = 'res'
vares-# AND t.tablename = cl.relname
vares-# AND con.contype = 'f'
vares-# AND con.conrelid = cl.oid
vares-# AND cc.constraint_name = con.conname
vares-# ORDER BY t.tablename, con.conname;
vares=# \o drop_fk.log \i drop_fk.sql
vares=# \o drop_ix.sql
vares=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
vares=# from pg_indexes
vares=# where schemaname = 'res';
vares=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/vares # pg_restore -U res -d vares -v -a "pgsql.vares"
fagot:/u02/addres/vares # grep 'CREATE INDEX' -B 1 vares_schema.sql > restore_ix.sql
fagot:/u02/addres/vares # grep 'CREATE UNIQUE INDEX' -B 1 vares_schema.sql > restore_u_ix.sql
fagot:/u02/addres/vares # psql -U res vares
vares=# truncate table log;
vares=# truncate table rp_abon;
vares=# truncate table dual;
vares=# truncate table billsqlreport;
vares=# \i restore_ix.sql \i restore_u_ix.sql
vares=# vacuum full analyze verbose; \q

Александровка combine:

fagot:/u02/addres/vares # cd ..; mkdir varesc; cd varesc
fagot:/u02/addres/varesc # bucardo_ctl add herd combine_vares_herd
fagot:/u02/addres/varesc # 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/addres/varesc # pg_dump -U res -F c -v -f bucardo_tmp_vares_goat.backup -t bucardo_tmp_vares_goat vares
fagot:/u02/addres/varesc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_vares_goat.backup
fagot:/u02/addres/varesc # 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/addres/varesc # psql -U res vares
vares=# drop table bucardo_tmp_vares_goat; \q
fagot:/u02/addres/varesc # bucardo_ctl stop "stop for add sync combine_vares_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/varesc # bucardo_ctl add sync combine_vares_sync type=pushdelta source=combine_vares_herd targetdb=combine --verbose
fagot:/u02/addres/varesc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_vares_sync'; \q
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_vares_sync"
### тут ждем пока произойдет синхронизация В.Александровки
fagot:/var/log/bucardo # cd /u02/addres/varesc
fagot:/u02/addres/varesc # bucardo_ctl stop "stop for add tables combine_vares_sync"
fagot:/u02/addres/varesc # mkdir tables; cd tables
fagot:/u02/addres/varesc/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||'" '
vares-# ||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/addres/varesc/tables # bash tdump.sh
fagot:/u02/addres/varesc/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/addres/varesc/tables # bash trestore.sh
fagot:/u02/addres/varesc/tables # 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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_vares_sync'; \q
fagot:/u02/addres/varesc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_vares_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_vares_sync"

Высокополье 1 в 1:

fagot:~ # cd /u02/addres; mkdir vpres; cd vpres
fagot:/u02/addres/vpres # psql -U postgres postgres
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:/u02/addres/vpres # bucardo_ctl add all tables db=vpres_res --herd=vpres_herd --verbose
fagot:/u02/addres/vpres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/vpres # pg_dump -h 1.1.57.2 -U postgres -F p -s -v -f "vpres_schema.sql" -n 'res' "vpres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/vpres # psql -f vpres_schema.sql -U res vpres
fagot:/u02/addres/vpres # ssh root@1.1.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:/u02/addres/vpres # bucardo_ctl add sync res_vpres_sync type=pushdelta source=vpres_herd targetdb=vpres_obl --verbose
fagot:/u02/addres/vpres # wget --no-proxy -c --ftp-user=a --ftp-password=a ftp://1.1.57.2/postgres/pgsql.vpres
fagot:/u02/addres/vpres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c vpres res
vares=# vacuum analyze verbose;
vpres=# \o drop_fk.sql \t
vpres=# SELECT 'ALTER TABLE res.'||t.tablename
vpres-# ||' DROP CONSTRAINT '||con.conname||';'
vpres-# FROM pg_constraint con, pg_class cl, pg_tables t,
vpres-# information_schema.constraint_column_usage cc
vpres-# WHERE t.schemaname = 'res'
vpres-# AND t.tablename = cl.relname
vpres-# AND con.contype = 'f'
vpres-# AND con.conrelid = cl.oid
vpres-# AND cc.constraint_name = con.conname
vpres-# ORDER BY t.tablename, con.conname;
vpres=# \o drop_fk.log \i drop_fk.sql
vpres=# \o drop_ix.sql
vpres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
vpres-# from pg_indexes
vpres-# where schemaname = 'res';
vpres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/vpres # pg_restore -U res -d vpres -v -a "pgsql.vpres"
fagot:/u02/addres/vpres # grep 'CREATE INDEX' -B 1 vpres_schema.sql > restore_ix.sql
fagot:/u02/addres/vpres # grep 'CREATE UNIQUE INDEX' -B 1 vpres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/vpres # psql -U res vpres
vpres=# truncate table log;
vpres=# truncate table rp_abon;
vpres=# truncate table dual;
vpres=# truncate table billsqlreport;
vpres=# \i restore_ix.sql \i restore_u_ix.sql
vpres=# vacuum analyze verbose; \q

Высокополье combine:

fagot:/u02/addres/vpres # cd ..; mkdir vpresc; cd vpresc
fagot:/u02/addres/vpresc # bucardo_ctl add herd combine_vpres_herd
fagot:/u02/addres/vpresc # 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/addres/vpresc # pg_dump -U res -F c -v -f bucardo_tmp_vpres_goat.backup -t bucardo_tmp_vpres_goat vpres
fagot:/u02/addres/vpresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_vpres_goat.backup
fagot:/u02/addres/vpresc # 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);
bucardo=# \c vpres res
vpres=# drop table bucardo_tmp_vpres_goat; \q
fagot:/u02/addres/vpresc # bucardo_ctl stop "stop for add sync combine_vpres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/vpresc # bucardo_ctl add sync combine_vpres_sync type=pushdelta source=combine_vpres_herd targetdb=combine --verbose
fagot:/u02/addres/vpresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_vpres_sync'; \q
fagot:/u02/addres/vpresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_vpres_sync"
fagot:/var/log/bucardo # cd /u02/addres/vpresc
### тут ждем пока произойдет синхронизация Высокополья
fagot:/u02/addres/vpresc # bucardo_ctl stop "stop for add tables combine_vpres_sync"
fagot:/u02/addres/vpresc # mkdir tables; cd tables
fagot:/u02/addres/vpresc/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/addres/vpresc/tables # bash tdump.sh
fagot:/u02/addres/vpresc/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/addres/vpresc/tables # bash trestore.sh
fagot:/u02/addres/vpresc/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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_vpres_sync'; \q
fagot:/u02/addres/vpresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_vpres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_vpres_sync"

Скадовск 1 в 1:

fagot:~ # cd /u02/addres; mkdir skres; cd skres
fagot:/u02/addres/skres # psql -U postgres postgres
postgres=# CREATE DATABASE skres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=skres;
postgres=# \c skres
skres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:/u02/addres/skres # bucardo_ctl add all tables db=skres_res --herd=skres_herd --verbose
fagot:/u02/addres/skres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/skres # pg_dump -h 1.1.25.2 -U postgres -F p -s -v -f "skres_schema.sql" -n 'res' "UTF8_sk"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/skres # psql -f skres_schema.sql -U res skres
fagot:/u02/addres/skres # bucardo_ctl add sync res_skres_sync type=pushdelta source=skres_herd targetdb=skres_obl --verbose
fagot:/u02/addres/skres # ssh root@1.1.25.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.skres "UTF8_sk""; exit
fagot:/u02/addres/skres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.25.2/postgres/pgsql.skres
fagot:/u02/addres/skres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c skres res
skres=# vacuum analyze verbose;
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 \i drop_fk.sql
skres=# \o drop_ix.sql
skres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
skres-# from pg_indexes
skres-# where schemaname = 'res';
skres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/skres # pg_restore -U res -d skres -v -a "pgsql.skres"
fagot:/u02/addres/skres # grep 'CREATE INDEX' -B 1 skres_schema.sql > restore_ix.sql
fagot:/u02/addres/skres # grep 'CREATE UNIQUE INDEX' -B 1 skres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/skres # psql -U res skres
skres=# truncate table log;
skres=# truncate table rp_abon;
skres=# truncate table dual;
skres=# truncate table billsqlreport;
skres=# \i restore_ix.sql \i restore_u_ix.sql
skres=# vacuum analyze verbose; \q

Скадовск combine:

fagot:/u02/addres/skres # cd ..; mkdir skresc; cd skresc
fagot:/u02/addres/skresc # bucardo_ctl add herd combine_skres_herd
fagot:/u02/addres/skresc # 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/addres/skresc # pg_dump -U res -F c -v -f bucardo_tmp_skres_goat.backup -t bucardo_tmp_skres_goat skres
fagot:/u02/addres/skresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_skres_goat.backup
fagot:/u02/addres/skresc # 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);
bucardo=# \c skres res
skres=# drop table bucardo_tmp_skres_goat; \q
fagot:/u02/addres/skresc # bucardo_ctl stop "stop for add sync combine_skres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/skresc # bucardo_ctl add sync combine_skres_sync type=pushdelta source=combine_skres_herd targetdb=combine --verbose
fagot:/u02/addres/skresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_skres_sync'; \q
fagot:/u02/addres/skresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_skres_sync"
fagot:/var/log/bucardo # cd /u02/addres/skresc
### тут ждем пока произойдет синхронизация Скадовска
fagot:/u02/addres/skresc # bucardo_ctl stop "stop for add tables combine_skres_sync"
fagot:/u02/addres/skresc # mkdir tables; cd tables
fagot:/u02/addres/skresc/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;
skres=# \o trestore.sh
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/addres/skresc/tables # bash tdump.sh
fagot:/u02/addres/skresc/tables # bash trestore.sh
fagot:/u02/addres/skresc/tables # 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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_skres_sync'; \q
fagot:/u02/addres/skresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_skres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_skres_sync"

Лепетиха 1 в 1:

fagot:~ # cd /u02/addres; mkdir vlres; cd vlres
fagot:/u02/addres/vlres # psql -U postgres postgres
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:/u02/addres/vlres # bucardo_ctl add all tables db=vlres_res --herd=vlres_herd --verbose
fagot:/u02/addres/vlres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/vlres # pg_dump -h 1.1.41.2 -U postgres -F p -s -v -f "vlres_schema.sql" -n 'res' "vlres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/vlres # psql -f vlres_schema.sql -U res vlres
fagot:/u02/addres/vlres # bucardo_ctl add sync res_vlres_sync type=pushdelta source=vlres_herd targetdb=vlres_obl --verbose
fagot:/u02/addres/vlres # ssh root@1.1.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:/u02/addres/vlres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.41.2/postgres/pgsql.vlres
fagot:/u02/addres/vlres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c vlres res
vlres=# vacuum analyze verbose;
vlres=# \o drop_fk.sql \t
vlres=# SELECT 'ALTER TABLE res.'||t.tablename
vlres-# ||' DROP CONSTRAINT '||con.conname||';'
vlres-# FROM pg_constraint con, pg_class cl, pg_tables t,
vlres-# information_schema.constraint_column_usage cc
vlres-# WHERE t.schemaname = 'res'
vlres-# AND t.tablename = cl.relname
vlres-# AND con.contype = 'f'
vlres-# AND con.conrelid = cl.oid
vlres-# AND cc.constraint_name = con.conname
vlres-# ORDER BY t.tablename, con.conname;
vlres=# \o drop_fk.log \i drop_fk.sql
vlres=# \o drop_ix.sql
vlres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
vlres-# from pg_indexes
vlres-# where schemaname = 'res';
vlres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/vlres # pg_restore -U res -d vlres -v -a "pgsql.vlres"
fagot:/u02/addres/vlres # grep 'CREATE INDEX' -B 1 vlres_schema.sql > restore_ix.sql
fagot:/u02/addres/vlres # grep 'CREATE UNIQUE INDEX' -B 1 vlres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/vlres # psql -U res vlres
vlres=# truncate table log;
vlres=# truncate table rp_abon;
vlres=# truncate table dual;
vlres=# truncate table billsqlreport;
vlres=# \i restore_ix.sql \i restore_u_ix.sql
vlres=# vacuum analyze verbose; \q

Лепетиха combine:

fagot:/u02/addres/vlres # cd ..; mkdir vlresc; cd vlresc
fagot:/u02/addres/vlresc # bucardo_ctl add herd combine_vlres_herd
fagot:/u02/addres/vlresc # 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/addres/vlresc # pg_dump -U res -F c -v -f bucardo_tmp_vlres_goat.backup -t bucardo_tmp_vlres_goat vlres
fagot:/u02/addres/vlresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_vlres_goat.backup
fagot:/u02/addres/vlresc # 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);
bucardo=# \c vlres res
vlres=# drop table bucardo_tmp_vlres_goat; \q
fagot:/u02/addres/vlresc # bucardo_ctl stop "stop for add sync combine_vlres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/vlresc # bucardo_ctl add sync combine_vlres_sync type=pushdelta source=combine_vlres_herd targetdb=combine --verbose
fagot:/u02/addres/skresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_vlres_sync'; \q
fagot:/u02/addres/vlresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_vlres_sync"
fagot:/var/log/bucardo # cd /u02/addres/vlresc
### тут ждем пока произойдет синхронизация Лепетихи
fagot:/u02/addres/vlresc # bucardo_ctl stop "stop for add tables combine_vlres_sync"
fagot:/u02/addres/vlresc # mkdir tables; cd tables
fagot:/u02/addres/vlresc/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||'" '
vlres-# ||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;
vlres=# \o trestore.sh
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/addres/vlresc/tables # bash tdump.sh
fagot:/u02/addres/vlresc/tables # bash trestore.sh
fagot:/u02/addres/vlresc/tables # 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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_vlres_sync'; \q
fagot:/u02/addres/vlresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_vlres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_vlres_sync"

Чаплынка 1 в 1:

fagot:/var/log/bucardo # cd /u02/addres; mkdir cpres; cd cpres
fagot:/u02/addres/cpres # psql -U postgres postgres
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:/u02/addres/cpres # bucardo_ctl add all tables db=cpres_res --herd=cpres_herd --verbose
fagot:/u02/addres/cpres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/cpres # pg_dump -h 1.1.27.2 -U postgres -F p -s -v -f "cpres_schema.sql" -n 'res' "cpres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/cpres # psql -f cpres_schema.sql -U res cpres
fagot:/u02/addres/cpres # bucardo_ctl add sync res_cpres_sync type=pushdelta source=cpres_herd targetdb=cpres_obl --verbose
fagot:/u02/addres/cpres # ssh root@1.1.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:/u02/addres/cpres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.27.2/postgres/pgsql.cpres
fagot:/u02/addres/cpres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c cpres res
cpres=# vacuum analyze verbose;
cpres=# \o drop_fk.sql \t
cpres=# SELECT 'ALTER TABLE res.'||t.tablename
cpres-# ||' DROP CONSTRAINT '||con.conname||';'
cpres-# FROM pg_constraint con, pg_class cl, pg_tables t,
cpres-# information_schema.constraint_column_usage cc
cpres-# WHERE t.schemaname = 'res'
cpres-# AND t.tablename = cl.relname
cpres-# AND con.contype = 'f'
cpres-# AND con.conrelid = cl.oid
cpres-# AND cc.constraint_name = con.conname
cpres-# ORDER BY t.tablename, con.conname;
cpres=# \o drop_fk.log \i drop_fk.sql
cpres=# \o drop_ix.sql
cpres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
cpres-# from pg_indexes
cpres-# where schemaname = 'res';
cpres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/cpres # pg_restore -U res -d cpres -v -a "pgsql.cpres"
fagot:/u02/addres/cpres # grep 'CREATE INDEX' -B 1 cpres_schema.sql > restore_ix.sql
fagot:/u02/addres/cpres # grep 'CREATE UNIQUE INDEX' -B 1 cpres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/cpres # psql -U res cpres
cpres=# truncate table log;
cpres=# truncate table rp_abon;
cpres=# truncate table dual;
cpres=# truncate table billsqlreport;
cpres=# \i restore_ix.sql \i restore_u_ix.sql
cpres=# vacuum analyze verbose; \q

Чаплынка combine:

fagot:/u02/addres/cpres # cd ..; mkdir cpresc; cd cpresc
fagot:/u02/addres/cpresc # bucardo_ctl add herd combine_cpres_herd
fagot:/u02/addres/cpresc # 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/addres/cpresc # pg_dump -U res -F c -v -f bucardo_tmp_cpres_goat.backup -t bucardo_tmp_cpres_goat cpres
fagot:/u02/addres/cpresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_cpres_goat.backup
fagot:/u02/addres/cpresc # 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);
bucardo=# \c cpres res
cpres=# drop table bucardo_tmp_cpres_goat; \q
fagot:/u02/addres/cpresc # bucardo_ctl stop "stop for add sync combine_cpres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/cpresc # bucardo_ctl add sync combine_cpres_sync type=pushdelta source=combine_cpres_herd targetdb=combine --verbose
fagot:/u02/addres/cpresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_cpres_sync'; \q
fagot:/u02/addres/cpresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_cpres_sync"
fagot:/var/log/bucardo # cd /u02/addres/cpresc
### тут ждем пока произойдет синхронизация Чаплынки
fagot:/u02/addres/cpresc # bucardo_ctl stop "stop for add tables combine_cpres_sync"
fagot:/u02/addres/cpresc # mkdir tables; cd tables
fagot:/u02/addres/cpresc/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;
cpres=# \o trestore.sh
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/addres/cpresc/tables # bash tdump.sh
fagot:/u02/addres/cpresc/tables # bash trestore.sh
fagot:/u02/addres/cpresc/tables # 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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_cpres_sync'; \q
fagot:/u02/addres/cpresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_cpres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_cpres_sync"

Горностаевка 1 в 1:

fagot:~ # cd /u02/addres; mkdir gsres; cd gsres
fagot:/u02/addres/gsres # psql -U postgres postgres
postgres=# CREATE DATABASE gsres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=gsres;
postgres=# \c gsres
gsres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:/u02/addres/gsres # psql -h 1.1.46.2 -U postgres gsres
gsres=# alter role res superuser;
fagot:/u02/addres/gsres # bucardo_ctl add database gsres name=gsres_res host=1.1.46.2 user=res pass=r
fagot:/u02/addres/gsres # bucardo_ctl add database gsres name=gsres_obl host=1.1.11.72 user=res pass=r
fagot:/u02/addres/gsres # bucardo_ctl add all tables db=gsres_res --herd=gsres_herd --verbose
fagot:/u02/addres/gsres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/gsres # pg_dump -h 1.1.46.2 -U postgres -F p -s -v -f "gsres_schema.sql" -n 'res' "gsres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/gsres # psql -f gsres_schema.sql -U res gsres
fagot:/u02/addres/gsres # bucardo_ctl add sync res_gsres_sync type=pushdelta source=gsres_herd targetdb=gsres_obl --verbose
fagot:/u02/addres/gsres # ssh root@1.1.46.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.gsres "gsres""; exit
fagot:/u02/addres/gsres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.46.2/postgres/pgsql.gsres
fagot:/u02/addres/gsres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c gsres res
gsres=# vacuum analyze verbose;
gsres=# \o drop_fk.sql \t
Showing only tuples.
gsres=# SELECT 'ALTER TABLE res.'||t.tablename
gsres-# ||' DROP CONSTRAINT '||con.conname||';'
gsres-# FROM pg_constraint con, pg_class cl, pg_tables t,
gsres-# information_schema.constraint_column_usage cc
gsres-# WHERE t.schemaname = 'res'
gsres-# AND t.tablename = cl.relname
gsres-# AND con.contype = 'f'
gsres-# AND con.conrelid = cl.oid
gsres-# AND cc.constraint_name = con.conname
gsres-# ORDER BY t.tablename, con.conname;
gsres=# \o drop_fk.log \i drop_fk.sql \o drop_ix.sql
gsres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
gsres-# from pg_indexes
gsres-# where schemaname = 'res';
gsres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/gsres # pg_restore -U res -d gsres -v -a "pgsql.gsres"
fagot:/u02/addres/gsres # grep 'CREATE INDEX' -B 1 gsres_schema.sql > restore_ix.sql
fagot:/u02/addres/gsres # grep 'CREATE UNIQUE INDEX' -B 1 gsres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/gsres # psql -U res gsres
gsres=# truncate table log;
gsres=# truncate table rp_abon;
gsres=# truncate table dual;
gsres=# truncate table billsqlreport;
gsres=# \i restore_ix.sql \i restore_u_ix.sql
gsres=# vacuum analyze verbose; \q

Горностаевка combine:

fagot:/u02/addres/gsres # cd ..; mkdir gsresc; cd gsresc
fagot:/u02/addres/gsresc # bucardo_ctl add herd combine_gsres_herd
fagot:/u02/addres/gsresc # psql -U res gsres
gsres=# CREATE TABLE res.bucardo_tmp_gsres_goat AS
gsres-# SELECT table_name
gsres-# FROM information_schema.tables
gsres-# WHERE table_schema = 'res'
gsres-# AND table_type = 'BASE TABLE'
gsres-# AND (table_name IN (SELECT table_name
gsres(# FROM information_schema.columns
gsres(# WHERE column_name = 'domain_info'))
gsres-# ORDER BY table_name; \q
fagot:/u02/addres/gsresc # pg_dump -U res -F c -v -f bucardo_tmp_gsres_goat.backup -t bucardo_tmp_gsres_goat gsres
fagot:/u02/addres/gsresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_gsres_goat.backup
fagot:/u02/addres/gsresc # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_gsres_herd', id from goat
bucardo-# where db = 'gsres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_gsres_goat);
bucardo=# \c gsres res
gsres=# drop table bucardo_tmp_gsres_goat; \q
fagot:/u02/addres/gsresc # bucardo_ctl stop "stop for add sync combine_gsres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/gsresc # bucardo_ctl add sync combine_gsres_sync type=pushdelta source=combine_gsres_herd targetdb=combine --verbose
fagot:/u02/addres/gsresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_gsres_sync'; \q
fagot:/u02/addres/gsresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_gsres_sync"
fagot:/var/log/bucardo # cd /u02/addres/gsresc
### тут ждем пока произойдет синхронизация Горностаевки
fagot:/u02/addres/vlresc # bucardo_ctl stop "stop for add tables combine_gsres_sync"
fagot:/u02/addres/gsresc # mkdir tables; cd tables
fagot:/u02/addres/gsresc/tables # psql -U res gsres
gsres=# \o tdump.sh \t
gsres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
gsres-# ||table_catalog||'.res.'||table_name
gsres-# ||'.backup" -t "'||table_name||'" '
gsres-# ||table_catalog||''
gsres-# FROM information_schema.tables
gsres-# WHERE table_schema = 'res'
gsres-# AND table_type = 'BASE TABLE'
gsres-# AND (table_name IN (SELECT table_name
gsres(# FROM information_schema.columns
gsres(# WHERE column_name = 'domain_info'))
gsres-# ORDER BY table_name;
gsres=# \o trestore.sh
gsres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
gsres-# ||table_catalog||'.res.'||table_name
gsres-# ||'.backup" '||table_catalog||''
gsres-# FROM information_schema.tables
gsres-# WHERE table_schema = 'res'
gsres-# AND table_type = 'BASE TABLE'
gsres-# AND (table_name IN (SELECT table_name
gsres(# FROM information_schema.columns
gsres(# WHERE column_name = 'domain_info'))
gsres-# ORDER BY table_name; \q
fagot:/u02/addres/gsresc/tables # bash tdump.sh
fagot:/u02/addres/gsresc/tables # bash trestore.sh
fagot:/u02/addres/gsresc/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'gsres_res')
bucardo-# and herd = 'combine_gsres_herd';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_gsres_sync'; \q
fagot:/u02/addres/gsresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_gsres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_gsres_sync"

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

fagot:~ # cd /u02/addres; mkdir nvres; cd nvres
fagot:/u02/addres/nvres # psql -U postgres postgres
postgres=# CREATE DATABASE nvres WITH ENCODING='UTF8' OWNER=res
postgres-# CONNECTION LIMIT=-1 TABLESPACE=nvres;
postgres=# postgres=# \c nvres
postgres=# \c nvres
nvres=# CREATE SCHEMA res AUTHORIZATION res; \q
fagot:/u02/addres/nvres # bucardo_ctl add all tables db=nvres_res --herd=nvres_herd --verbose
fagot:/u02/addres/nvres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/nvres # pg_dump -h 1.1.58.2 -U postgres -F p -s -v -f "nvres_schema.sql" -n 'res' "nvres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/nvres # psql -f nvres_schema.sql -U res nvres
fagot:/u02/addres/nvres # bucardo_ctl add sync res_nvres_sync type=pushdelta source=nvres_herd targetdb=nvres_obl --verbose
fagot:/u02/addres/nvres # ssh root@1.1.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:/u02/addres/nvres # wget --no-proxy -c --ftp-user=s --ftp-password=v ftp://1.1.58.2/postgres/pgsql.nvres
fagot:/u02/addres/nvres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c nvres res
nvres=# vacuum analyze verbose;
nvres=# \o drop_fk.sql \t
nvres=# SELECT 'ALTER TABLE res.'||t.tablename
nvres-# ||' DROP CONSTRAINT '||con.conname||';'
nvres-# FROM pg_constraint con, pg_class cl, pg_tables t,
nvres-# information_schema.constraint_column_usage cc
nvres-# WHERE t.schemaname = 'res'
nvres-# AND t.tablename = cl.relname
nvres-# AND con.contype = 'f'
nvres-# AND con.conrelid = cl.oid
nvres-# AND cc.constraint_name = con.conname
nvres-# ORDER BY t.tablename, con.conname;
nvres=# \o drop_fk.log \i drop_fk.sql
nvres=# \o drop_ix.sql
nvres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
nvres-# from pg_indexes
nvres-# where schemaname = 'res';
nvres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/nvres # pg_restore -U res -d nvres -v -a "pgsql.nvres"
fagot:/u02/addres/nvres # grep 'CREATE INDEX' -B 1 nvres_schema.sql > restore_ix.sql
fagot:/u02/addres/nvres # grep 'CREATE UNIQUE INDEX' -B 1 nvres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/nvres # psql -U res nvres
nvres=# truncate table log;
nvres=# truncate table rp_abon;
nvres=# truncate table dual;
nvres=# truncate table billsqlreport;
nvres=# \i restore_ix.sql \i restore_u_ix.sql
nvres=# vacuum analyze verbose; \q

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

fagot:/u02/addres/nvres # cd ..; mkdir nvresc; cd nvresc
fagot:/u02/addres/nvresc # bucardo_ctl add herd combine_nvres_herd
fagot:/u02/addres/nvresc # 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/addres/nvresc # pg_dump -U res -F c -v -f bucardo_tmp_nvres_goat.backup -t bucardo_tmp_nvres_goat nvres
fagot:/u02/addres/nvresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_nvres_goat.backup
fagot:/u02/addres/nvresc # 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);
bucardo=# \c nvres res
nvres=# drop table bucardo_tmp_nvres_goat; \q
fagot:/u02/addres/nvresc # bucardo_ctl stop "stop for add sync combine_nvres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/nvresc # bucardo_ctl add sync combine_nvres_sync type=pushdelta source=combine_nvres_herd targetdb=combine --verbose
fagot:/u02/addres/nvresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_nvres_sync'; \q
fagot:/u02/addres/nvresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_nvres_sync"
fagot:/var/log/bucardo # cd /u02/addres/nvresc
### тут ждем пока произойдет синхронизация Воронцовки
fagot:/u02/addres/nvresc # bucardo_ctl stop "stop for add tables combine_nvres_sync"
fagot:/u02/addres/nvresc # mkdir tables; cd tables
fagot:/u02/addres/nvresc/tables # psql -U res nvres
nvres=# \o tdump.sh \t
nvres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
nvres-# ||table_catalog||'.res.'||table_name
nvres-# ||'.backup" -t "'||table_name||'" '
nvres-# ||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;
nvres=# \o trestore.sh
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/addres/nvresc/tables # bash tdump.sh
fagot:/u02/addres/nvresc/tables # bash trestore.sh
fagot:/u02/addres/nvresc/tables # 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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_nvres_sync'; \q
fagot:/u02/addres/nvresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_nvres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_nvres_sync"

Новая Каховка 1 в 1:

fagot:~ # cd /u02/addres; mkdir nkres; cd nkres
fagot:/u02/addres/nkres # psql -U postgres postgres
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:/u02/addres/nkres # bucardo_ctl add all tables db=nkres_res --herd=nkres_herd --verbose
fagot:/u02/addres/nkres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/nkres # pg_dump -h 1.1.12.2 -U postgres -F p -s -v -f "nkres_schema.sql" -n 'res' "nkres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/nkres # psql -f nkres_schema.sql -U res nkres
fagot:/u02/addres/nkres # bucardo_ctl add sync res_nkres_sync type=pushdelta source=nkres_herd targetdb=nkres_obl --verbose
fagot:/u02/addres/nkres # ssh root@1.1.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:/u02/addres/nkres # wget --no-proxy -c --ftp-user=a --ftp-password=a ftp://1.1.12.2/postgres/pgsql.nkres
fagot:/u02/addres/nkres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c nkres res
nkres=# vacuum analyze verbose;
nkres=# \o drop_fk.sql \t
nkres=# SELECT 'ALTER TABLE res.'||t.tablename
nkres-# ||' DROP CONSTRAINT '||con.conname||';'
nkres-# FROM pg_constraint con, pg_class cl, pg_tables t,
nkres-# information_schema.constraint_column_usage cc
nkres-# WHERE t.schemaname = 'res'
nkres-# AND t.tablename = cl.relname
nkres-# AND con.contype = 'f'
nkres-# AND con.conrelid = cl.oid
nkres-# AND cc.constraint_name = con.conname
nkres-# ORDER BY t.tablename, con.conname;
nkres=# \o drop_fk.log \i drop_fk.sql
nkres=# \o drop_ix.sql
nkres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
nkres-# from pg_indexes
nkres-# where schemaname = 'res';
nkres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/nkres # pg_restore -U res -d nkres -v -a "pgsql.nkres"
fagot:/u02/addres/nkres # grep 'CREATE INDEX' -B 1 nkres_schema.sql > restore_ix.sql
fagot:/u02/addres/nkres # grep 'CREATE UNIQUE INDEX' -B 1 nkres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/nkres # psql -U res nkres
nkres=# truncate table log; truncate table rp_abon;
nkres=# truncate table dual; truncate table billsqlreport;
nkres=# \i restore_ix.sql \i restore_u_ix.sql
nkres=# vacuum analyze verbose; \q

!!!-!!! Новая Каховка combine:

fagot:/u02/addres/nkres # cd ..; mkdir nkresc; cd nkresc
fagot:/u02/addres/nkresc # bucardo_ctl add herd combine_nkres_herd
fagot:/u02/addres/nkresc # psql -U res nkres
nkres=# CREATE TABLE res.bucardo_tmp_nkres_goat AS
nkres-# SELECT table_name
nkres-# FROM information_schema.tables
nkres-# WHERE table_schema = 'res'
nkres-# AND table_type = 'BASE TABLE'
nkres-# AND (table_name IN (SELECT table_name
nkres(# FROM information_schema.columns
nkres(# WHERE column_name = 'domain_info'))
nkres-# ORDER BY table_name; \q
fagot:/u02/addres/nkresc # pg_dump -U res -F c -v -f bucardo_tmp_nkres_goat.backup -t bucardo_tmp_nkres_goat nkres
fagot:/u02/addres/nkresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_nkres_goat.backup
fagot:/u02/addres/nkresc # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_nkres_herd', id from goat
bucardo-# where db = 'nkres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_nkres_goat);
bucardo=# \c nkres res
nkres=# drop table bucardo_tmp_nkres_goat; \q
fagot:/u02/addres/nkresc # bucardo_ctl stop "stop for add sync combine_nkres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/nkresc # bucardo_ctl add sync combine_nkres_sync type=pushdelta source=combine_nkres_herd targetdb=combine --verbose
fagot:/u02/addres/nkresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_nkres_sync'; \q
fagot:/u02/addres/nkresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_nkres_sync"
fagot:/var/log/bucardo # cd /u02/addres/nkresc
### тут ждем пока произойдет синхронизация Новой Каховки
fagot:/u02/addres/nkresc # bucardo_ctl stop "stop for add tables combine_nkres_sync"
fagot:/u02/addres/nkresc # mkdir tables; cd tables
fagot:/u02/addres/nkresc/tables # psql -U res nkres
nkres=# \o tdump.sh \t
nkres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
nkres-# ||table_catalog||'.res.'||table_name
nkres-# ||'.backup" -t "'||table_name||'" '
nkres-# ||table_catalog||''
nkres-# FROM information_schema.tables
nkres-# WHERE table_schema = 'res'
nkres-# AND table_type = 'BASE TABLE'
nkres-# AND (table_name IN (SELECT table_name
nkres(# FROM information_schema.columns
nkres(# WHERE column_name = 'domain_info'))
nkres-# ORDER BY table_name;
nkres=# \o trestore.sh
nkres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
nkres-# ||table_catalog||'.res.'||table_name
nkres-# ||'.backup" '||table_catalog||''
nkres-# FROM information_schema.tables
nkres-# WHERE table_schema = 'res'
nkres-# AND table_type = 'BASE TABLE'
nkres-# AND (table_name IN (SELECT table_name
nkres(# FROM information_schema.columns
nkres(# WHERE column_name = 'domain_info'))
nkres-# ORDER BY table_name; \q
fagot:/u02/addres/nkresc/tables # bash tdump.sh
fagot:/u02/addres/nkresc/tables # bash trestore.sh
fagot:/u02/addres/nkresc/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'nkres_res')
bucardo-# and herd = 'combine_nkres_herd';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_nkres_sync'; \q
fagot:/u02/addres/nkresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_nkres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_nkres_sync"

#В итоге оказалось, что размерности полей по Н.Каховке не совпадаюст.
#Бл...дь!
#Ищем засранцев:
fagot:/u02/addres/nkresc/tables # cd ..
fagot:/u02/addres/nkresc # psql -U res nkres
nkres=# create table bucardo_tmp_nkres_col as
nkres-# select table_name, column_name, data_type,
nkres-# character_maximum_length, character_octet_length,
nkres-# numeric_precision, numeric_precision_radix, numeric_scale,
nkres-# datetime_precision
nkres-# from information_schema.columns
nkres-# where table_catalog = 'nkres'
nkres-# and table_schema = 'res'
nkres-# order by table_name, column_name; \q
fagot:/u02/addres/nkresc # pg_dump -U res -F c -v -f bucardo_tmp_nkres_col.backup -t bucardo_tmp_nkres_col nkres
fagot:/u02/addres/nkresc # pg_restore -U res -d combine -v bucardo_tmp_nkres_col.backup
#Вот этим запросом потом отлавливаем разницу:
select i.table_name, i.column_name, i.data_type,
i.character_maximum_length, b.character_maximum_length,
i.character_octet_length, b.character_octet_length,
i.numeric_precision, b.numeric_precision,
i.numeric_precision_radix, b.numeric_precision_radix,
i.numeric_scale, b.numeric_scale,
i.datetime_precision, b.datetime_precision
from information_schema.columns i, res.bucardo_tmp_nkres_col b
where i.table_catalog = 'combine'
and i.table_schema = 'res'
and i.table_name in (select table_name from res.bucardo_tmp_nkres_col)
and i.table_name = b.table_name
and i.column_name = b.column_name
and (
i.character_maximum_length != b.character_maximum_length
or i.character_octet_length != b.character_octet_length
or i.numeric_precision != b.numeric_precision
or i.numeric_precision_radix != b.numeric_precision_radix
or i.numeric_scale != b.numeric_scale
or i.datetime_precision != b.datetime_precision
)
order by i.table_name, i.column_name
#После того, как подрихтуем, переливаем Н.Каховку в combine

Каховка 1 в 1:

fagot:~ # cd /u02/addres; mkdir kvres; cd kvres
fagot:/u02/addres/kvres # psql -U postgres postgres
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:/u02/addres/kvres # bucardo_ctl add all tables db=kvres_res --herd=kvres_herd --verbose
fagot:/u02/addres/kvres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/kvres # pg_dump -h 1.1.42.3 -U postgres -F p -s -v -f "kvres_schema.sql" -n 'res' "kvres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/kvres # psql -f kvres_schema.sql -U res kvres
fagot:/u02/addres/kvres # bucardo_ctl add sync res_kvres_sync type=pushdelta source=kvres_herd targetdb=kvres_obl --verbose
fagot:/u02/addres/kvres # ssh root@1.1.42.3
linux02:~ # mkdir /u02/postgres; chown postgres:postgres /u02/postgres
linux02:~ # su - postgres -c "cd /u02/postgres; pg_dump -C -F c -b -f pgsql.kvres "kvres""; exit
fagot:/u02/addres/kvres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.42.3/postgres/pgsql.kvres
fagot:/u02/addres/kvres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c kvres res
kvres=# vacuum analyze verbose;
kvres=# \o drop_fk.sql \t
kvres=# SELECT 'ALTER TABLE res.'||t.tablename
kvres-# ||' DROP CONSTRAINT '||con.conname||';'
kvres-# FROM pg_constraint con, pg_class cl, pg_tables t,
kvres-# information_schema.constraint_column_usage cc
kvres-# WHERE t.schemaname = 'res'
kvres-# AND t.tablename = cl.relname
kvres-# AND con.contype = 'f'
kvres-# AND con.conrelid = cl.oid
kvres-# AND cc.constraint_name = con.conname
kvres-# ORDER BY t.tablename, con.conname;
kvres=# \o drop_fk.log \i drop_fk.sql
kvres=# \o drop_ix.sql
kvres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
kvres-# from pg_indexes
kvres-# where schemaname = 'res';
kvres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/kvres # pg_restore -U res -d kvres -v -a "pgsql.kvres"
fagot:/u02/addres/kvres # grep 'CREATE INDEX' -B 1 kvres_schema.sql > restore_ix.sql
fagot:/u02/addres/kvres # grep 'CREATE UNIQUE INDEX' -B 1 kvres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/kvres # psql -U res kvres
kvres=# truncate table log; truncate table rp_abon;
kvres=# truncate table dual; truncate table billsqlreport;
kvres=# \i restore_ix.sql \i restore_u_ix.sql
kvres=# vacuum analyze verbose; \q

Каховка combine:

fagot:/u02/addres/kvres # cd ..; mkdir kvresc; cd kvresc
fagot:/u02/addres/kvresc # bucardo_ctl add herd combine_kvres_herd
fagot:/u02/addres/kvresc # 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/addres/kvresc # pg_dump -U res -F c -v -f bucardo_tmp_kvres_goat.backup -t bucardo_tmp_kvres_goat kvres
fagot:/u02/addres/kvresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_kvres_goat.backup
fagot:/u02/addres/kvresc # 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);
bucardo=# \c kvres res
kvres=# drop table bucardo_tmp_kvres_goat; \q
fagot:/u02/addres/kvresc # bucardo_ctl stop "stop for add sync combine_kvres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/kvresc # bucardo_ctl add sync combine_kvres_sync type=pushdelta source=combine_kvres_herd targetdb=combine --verbose
fagot:/u02/addres/kvresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_kvres_sync'; \q
fagot:/u02/addres/kvresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_kvres_sync"
fagot:/var/log/bucardo # cd /u02/addres/kvresc
### тут ждем пока произойдет синхронизация Каховки
fagot:/u02/addres/kvresc # bucardo_ctl stop "stop for add tables combine_kvres_sync"
fagot:/u02/addres/kvresc # mkdir tables; cd tables
fagot:/u02/addres/kvresc/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||'" '
kvres-# ||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;
kvres=# \o trestore.sh
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/addres/kvresc/tables # bash tdump.sh
fagot:/u02/addres/kvresc/tables # bash trestore.sh
fagot:/u02/addres/kvresc/tables # 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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_kvres_sync'; \q
fagot:/u02/addres/kvresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_kvres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_kvres_sync"

Берислав 1 в 1:

fagot:~ # cd /u02/addres; mkdir brres; cd brres
fagot:/u02/addres/brres # psql -U postgres postgres
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:/u02/addres/brres # bucardo_ctl add all tables db=brres_res --herd=brres_herd --verbose
fagot:/u02/addres/brres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/brres # pg_dump -h 1.1.44.2 -U postgres -F p -s -v -f "brres_schema.sql" -n 'res' "brres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/brres # psql -f brres_schema.sql -U res brres
fagot:/u02/addres/brres # bucardo_ctl add sync res_brres_sync type=pushdelta source=brres_herd targetdb=brres_obl --verbose
fagot:/u02/addres/brres # ssh root@1.1.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:/u02/addres/brres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.44.2/postgres/pgsql.brres
fagot:/u02/addres/brres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c brres res
brres=# vacuum analyze verbose;
brres=# \o drop_fk.sql \t
brres=# SELECT 'ALTER TABLE res.'||t.tablename
brres-# ||' DROP CONSTRAINT '||con.conname||';'
brres-# FROM pg_constraint con, pg_class cl, pg_tables t,
brres-# information_schema.constraint_column_usage cc
brres-# WHERE t.schemaname = 'res'
brres-# AND t.tablename = cl.relname
brres-# AND con.contype = 'f'
brres-# AND con.conrelid = cl.oid
brres-# AND cc.constraint_name = con.conname
brres-# ORDER BY t.tablename, con.conname;
brres=# \o drop_fk.log \i drop_fk.sql
brres=# \o drop_ix.sql
brres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
brres-# from pg_indexes
brres-# where schemaname = 'res';
brres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/brres # pg_restore -U res -d brres -v -a "pgsql.brres"
fagot:/u02/addres/brres # grep 'CREATE INDEX' -B 1 brres_schema.sql > restore_ix.sql
fagot:/u02/addres/brres # grep 'CREATE UNIQUE INDEX' -B 1 brres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/brres # psql -U res brres
brres=# truncate table log; truncate table rp_abon;
brres=# truncate table dual; truncate table billsqlreport;
brres=# \i restore_ix.sql \i restore_u_ix.sql
brres=# vacuum analyze verbose; \q

Берислав combine:

fagot:/u02/addres/brres # cd ..; mkdir brresc; cd brresc
fagot:/u02/addres/brresc # bucardo_ctl add herd combine_brres_herd
fagot:/u02/addres/brresc # psql -U res brres
brres=# CREATE TABLE res.bucardo_tmp_brres_goat AS
brres-# SELECT table_name
brres-# FROM information_schema.tables
brres-# WHERE table_schema = 'res'
brres-# AND table_type = 'BASE TABLE'
brres-# AND (table_name IN (SELECT table_name
brres(# FROM information_schema.columns
brres(# WHERE column_name = 'domain_info'))
brres-# ORDER BY table_name; \q
fagot:/u02/addres/brresc # pg_dump -U res -F c -v -f bucardo_tmp_brres_goat.backup -t bucardo_tmp_brres_goat brres
fagot:/u02/addres/brresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_brres_goat.backup
fagot:/u02/addres/brresc # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_brres_herd', id from goat
bucardo-# where db = 'brres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_brres_goat);
bucardo=# \c brres res
brres=# drop table bucardo_tmp_brres_goat; \q
fagot:/u02/addres/brresc # bucardo_ctl stop "stop for add sync combine_brres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/brresc # bucardo_ctl add sync combine_brres_sync type=pushdelta source=combine_brres_herd targetdb=combine --verbose
fagot:/u02/addres/brresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_brres_sync'; \q
fagot:/u02/addres/brresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_brres_sync"
fagot:/var/log/bucardo # cd /u02/addres/brresc
### тут ждем пока произойдет синхронизация Берислава
fagot:/u02/addres/brresc # bucardo_ctl stop "stop for add tables combine_brres_sync"
fagot:/u02/addres/brresc # mkdir tables; cd tables
fagot:/u02/addres/brresc/tables # psql -U res brres
brres=# \o tdump.sh \t
brres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
brres-# ||table_catalog||'.res.'||table_name
brres-# ||'.backup" -t "'||table_name||'" '
brres-# ||table_catalog||''
brres-# FROM information_schema.tables
brres-# WHERE table_schema = 'res'
brres-# AND table_type = 'BASE TABLE'
brres-# AND (table_name IN (SELECT table_name
brres(# FROM information_schema.columns
brres(# WHERE column_name = 'domain_info'))
brres-# ORDER BY table_name;
brres=# \o trestore.sh
brres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
brres-# ||table_catalog||'.res.'||table_name
brres-# ||'.backup" '||table_catalog||''
brres-# FROM information_schema.tables
brres-# WHERE table_schema = 'res'
brres-# AND table_type = 'BASE TABLE'
brres-# AND (table_name IN (SELECT table_name
brres(# FROM information_schema.columns
brres(# WHERE column_name = 'domain_info'))
brres-# ORDER BY table_name; \q
fagot:/u02/addres/brresc/tables # bash tdump.sh
fagot:/u02/addres/brresc/tables # bash trestore.sh
fagot:/u02/addres/brresc/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'brres_res')
bucardo-# and herd = 'combine_brres_herd';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_brres_sync'; \q
fagot:/u02/addres/brresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_brres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_brres_sync"

Новотроицк 1 в 1:

fagot:~ # cd /u02/addres; mkdir ntres; cd ntres
fagot:/u02/addres/ntres # psql -U postgres postgres
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:/u02/addres/ntres # bucardo_ctl add all tables db=ntres_res --herd=ntres_herd --verbose
fagot:/u02/addres/ntres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/ntres # pg_dump -h 1.1.50.2 -U postgres -F p -s -v -f "ntres_schema.sql" -n 'res' "ntres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/ntres # psql -f ntres_schema.sql -U res ntres
fagot:/u02/addres/ntres # bucardo_ctl add sync res_ntres_sync type=pushdelta source=ntres_herd targetdb=ntres_obl --verbose
fagot:/u02/addres/ntres # ssh root@1.1.50.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.ntres "ntres""; exit
fagot:/u02/addres/ntres # wget --no-proxy -c --ftp-user=ss --ftp-password=s ftp://1.1.50.2/postgres/pgsql.ntres
fagot:/u02/addres/ntres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c ntres res
ntres=# vacuum analyze verbose;
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 \i drop_fk.sql
ntres=# \o drop_ix.sql
ntres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
ntres-# from pg_indexes
ntres-# where schemaname = 'res';
ntres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/ntres # pg_restore -U res -d ntres -v -a "pgsql.ntres"
fagot:/u02/addres/ntres # grep 'CREATE INDEX' -B 1 ntres_schema.sql > restore_ix.sql
fagot:/u02/addres/ntres # grep 'CREATE UNIQUE INDEX' -B 1 ntres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/ntres # psql -U res ntres
ntres=# truncate table log; truncate table rp_abon;
ntres=# truncate table dual; truncate table billsqlreport;
ntres=# \i restore_ix.sql \i restore_u_ix.sql
ntres=# vacuum analyze verbose; \q

Новотроицк combine:

fagot:/u02/addres/ntres # cd ..; mkdir ntresc; cd ntresc
fagot:/u02/addres/ntresc # bucardo_ctl add herd combine_ntres_herd
fagot:/u02/addres/ntresc # 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; \q
fagot:/u02/addres/ntresc # pg_dump -U res -F c -v -f bucardo_tmp_ntres_goat.backup -t bucardo_tmp_ntres_goat ntres
fagot:/u02/addres/ntresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_ntres_goat.backup
fagot:/u02/addres/ntresc # 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=# \c ntres res
ntres=# drop table bucardo_tmp_ntres_goat; \q
fagot:/u02/addres/ntresc # bucardo_ctl stop "stop for add sync combine_ntres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/ntresc # bucardo_ctl add sync combine_ntres_sync type=pushdelta source=combine_ntres_herd targetdb=combine --verbose
fagot:/u02/addres/ntresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_ntres_sync'; \q
fagot:/u02/addres/ntresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_ntres_sync"
fagot:/var/log/bucardo # cd /u02/addres/ntresc
### тут ждем пока произойдет синхронизация Новотроицка
fagot:/u02/addres/ntresc # bucardo_ctl stop "stop for add tables combine_ntres_sync"
fagot:/u02/addres/ntresc # mkdir tables; cd tables
fagot:/u02/addres/ntresc/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=# \o trestore.sh
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; \q
fagot:/u02/addres/ntresc/tables # bash tdump.sh
fagot:/u02/addres/ntresc/tables # bash trestore.sh
fagot:/u02/addres/ntresc/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'ntres_res')
bucardo-# and herd = 'combine_ntres_herd';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_ntres_sync'; \q
fagot:/u02/addres/ntresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_ntres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_ntres_sync"

Геническ 1 в 1:

fagot:~ # cd /u02/addres; mkdir gnres; cd gnres
fagot:/u02/addres/gnres # psql -U postgres postgres
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:/u02/addres/gnres # bucardo_ctl add all tables db=gnres_res --herd=gnres_herd --verbose
fagot:/u02/addres/gnres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/gnres # pg_dump -h 1.1.49.2 -U postgres -F p -s -v -f "gnres_schema.sql" -n 'res' "gnres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/gnres # psql -f gnres_schema.sql -U res gnres
fagot:/u02/addres/gnres # bucardo_ctl add sync res_gnres_sync type=pushdelta source=gnres_herd targetdb=gnres_obl --verbose
fagot:/u02/addres/gnres # ssh root@1.1.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:/u02/addres/gnres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.49.2/postgres/pgsql.gnres
fagot:/u02/addres/gnres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c gnres res
gnres=# vacuum analyze verbose;
gnres=# \o drop_fk.sql \t
gnres=# SELECT 'ALTER TABLE res.'||t.tablename
gnres-# ||' DROP CONSTRAINT '||con.conname||';'
gnres-# FROM pg_constraint con, pg_class cl, pg_tables t,
gnres-# information_schema.constraint_column_usage cc
gnres-# WHERE t.schemaname = 'res'
gnres-# AND t.tablename = cl.relname
gnres-# AND con.contype = 'f'
gnres-# AND con.conrelid = cl.oid
gnres-# AND cc.constraint_name = con.conname
gnres-# ORDER BY t.tablename, con.conname;
gnres=# \o drop_fk.log \i drop_fk.sql
gnres=# \o drop_ix.sql
gnres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
gnres-# from pg_indexes
gnres-# where schemaname = 'res';
gnres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/gnres # pg_restore -U res -d gnres -v -a "pgsql.gnres"
fagot:/u02/addres/gnres # grep 'CREATE INDEX' -B 1 gnres_schema.sql > restore_ix.sql
fagot:/u02/addres/gnres # grep 'CREATE UNIQUE INDEX' -B 1 gnres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/gnres # psql -U res gnres
gnres=# truncate table log; truncate table rp_abon;
gnres=# truncate table dual; truncate table billsqlreport;
gnres=# \i restore_ix.sql \i restore_u_ix.sql
gnres=# vacuum analyze verbose; \q

Геническ combine:

fagot:/u02/addres/gnres # cd ..; mkdir gnresc; cd gnresc
fagot:/u02/addres/gnresc # bucardo_ctl add herd combine_gnres_herd
fagot:/u02/addres/gnresc # 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/addres/gnresc # pg_dump -U res -F c -v -f bucardo_tmp_gnres_goat.backup -t bucardo_tmp_gnres_goat gnres
fagot:/u02/addres/gnresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_gnres_goat.backup
fagot:/u02/addres/gnresc # 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);
bucardo=# \c gnres res
gnres=# drop table bucardo_tmp_gnres_goat; \q
fagot:/u02/addres/gnresc # bucardo_ctl stop "stop for add sync combine_gnres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/gnresc # bucardo_ctl add sync combine_gnres_sync type=pushdelta source=combine_gnres_herd targetdb=combine --verbose
fagot:/u02/addres/gnresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_gnres_sync'; \q
fagot:/u02/addres/gnresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_gnres_sync"
fagot:/var/log/bucardo # cd /u02/addres/gnresc
### тут ждем пока произойдет синхронизация Геническа
fagot:/u02/addres/gnresc # bucardo_ctl stop "stop for add tables combine_gnres_sync"
fagot:/u02/addres/gnresc # mkdir tables; cd tables
fagot:/u02/addres/gnresc/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;
gnres=# \o trestore.sh
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/addres/gnresc/tables # bash tdump.sh
fagot:/u02/addres/gnresc/tables # bash trestore.sh
fagot:/u02/addres/gnresc/tables # 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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_gnres_sync'; \q
fagot:/u02/addres/gnresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_gnres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_gnres_sync"

Голая Пристань(базовый РЭС по данным) 1 в 1:

fagot:~ # cd /u02/addres; mkdir gpres; cd gpres
fagot:/u02/addres/gpres # psql -U postgres postgres
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:/u02/addres/gpres # bucardo_ctl add all tables db=gpres_res --herd=gpres_herd --verbose
fagot:/u02/addres/gpres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/gpres # pg_dump -h 1.1.34.2 -U postgres -F p -s -v -f "gpres_schema.sql" -n 'res' "gpres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/gpres # psql -f gpres_schema.sql -U res gpres
fagot:/u02/addres/gpres # bucardo_ctl add sync res_gpres_sync type=pushdelta source=gpres_herd targetdb=gpres_obl --verbose
fagot:/u02/addres/gpres # ssh root@1.1.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:/u02/addres/gpres # wget --no-proxy -c --ftp-user=s --ftp-password=s ftp://1.1.34.2/postgres/pgsql.gpres
fagot:/u02/addres/gpres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c gpres res
gpres=# vacuum analyze verbose;
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 \i drop_fk.sql
gpres=# \o drop_ix.sql
gpres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
gpres-# from pg_indexes
gpres-# where schemaname = 'res';
gpres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/gpres # pg_restore -U res -d gpres -v -a "pgsql.gpres"
fagot:/u02/addres/gpres # grep 'CREATE INDEX' -B 1 gpres_schema.sql > restore_ix.sql
fagot:/u02/addres/gpres # grep 'CREATE UNIQUE INDEX' -B 1 gpres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/gpres # psql -U res gpres
gpres=# truncate table log; truncate table rp_abon;
gpres=# truncate table dual; truncate table billsqlreport;
gpres=# \i restore_ix.sql \i restore_u_ix.sql
gpres=# vacuum analyze verbose; \q

Голая Пристань(базовый РЭС по данным) combine:

fagot:/u02/addres/gpres # cd ..; mkdir gpresc; cd gpresc
fagot:/u02/addres/gpresc # bucardo_ctl add herd combine_gpres_herd
fagot:/u02/addres/gpresc # 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-# ORDER BY table_name; \q
fagot:/u02/addres/gpresc # pg_dump -U res -F c -v -f bucardo_tmp_gpres_goat.backup -t bucardo_tmp_gpres_goat gpres
fagot:/u02/addres/gpresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_gpres_goat.backup
fagot:/u02/addres/gpresc # 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);
bucardo=# \c gpres res
gpres=# drop table bucardo_tmp_gpres_goat; \q
fagot:/u02/addres/gpresc # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/gpresc # bucardo_ctl stop "stop for add sync combine_gpres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/gpresc # bucardo_ctl add sync combine_gpres_sync type=pushdelta source=combine_gpres_herd targetdb=combine --verbose
fagot:/u02/addres/gpresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_gpres_sync'; \q
fagot:/u02/addres/gpresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_gpres_sync"
fagot:/var/log/bucardo # cd /u02/addres/gpresc
### тут ждем пока произойдет синхронизация Голой Пристани
fagot:/u02/addres/gpresc # bucardo_ctl stop "stop for add tables combine_gpres_sync"
fagot:/u02/addres/gpresc # psql -U res combine
combine=# truncate table correctionreason; \q
fagot:/u02/addres/gpresc # mkdir tables; cd tables
fagot:/u02/addres/gpresc/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-# ORDER BY table_name;
gpres=# \o trestore.sh
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-# ORDER BY table_name; \q
fagot:/u02/addres/gpresc/tables # bash tdump.sh
fagot:/u02/addres/gpresc/tables # bash trestore.sh
fagot:/u02/addres/gpresc/tables # psql -U bucardo bucardo
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_gpres_sync'; \q
fagot:/u02/addres/gpresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_gpres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_gpres_sync"

Цюрупинск 1 в 1:

fagot:~ # cd /u02/addres; mkdir crres; cd crres
fagot:/u02/addres/crres # psql -U postgres postgres
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:/u02/addres/crres # bucardo_ctl add all tables db=crres_res --herd=crres_herd --verbose
fagot:/u02/addres/crres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/crres # pg_dump -h 1.1.33.2 -U postgres -F p -s -v -f "crres_schema.sql" -n 'res' "crres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/crres # psql -f crres_schema.sql -U res crres
fagot:/u02/addres/crres # bucardo_ctl add sync res_crres_sync type=pushdelta source=crres_herd targetdb=crres_obl --verbose
fagot:/u02/addres/crres # ssh root@1.1.33.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.crres "crres""; exit
fagot:/u02/addres/crres # wget --no-proxy -c --ftp-user=s --ftp-password=w ftp://1.1.33.2/postgres/pgsql.crres
fagot:/u02/addres/crres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c crres res
crres=# vacuum analyze verbose;
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 \i drop_fk.sql
crres=# \o drop_ix.sql
crres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
crres-# from pg_indexes
crres-# where schemaname = 'res';
crres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/crres # pg_restore -U res -d crres -v -a "pgsql.crres"
fagot:/u02/addres/crres # grep 'CREATE INDEX' -B 1 crres_schema.sql > restore_ix.sql
fagot:/u02/addres/crres # grep 'CREATE UNIQUE INDEX' -B 1 crres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/crres # psql -U res crres
crres=# truncate table log; truncate table rp_abon;
crres=# truncate table dual; truncate table billsqlreport;
crres=# \i restore_ix.sql \i restore_u_ix.sql
crres=# vacuum analyze verbose; \q

Цюрупинск combine:

fagot:/u02/addres/crres # cd ..; mkdir crresc; cd crresc
fagot:/u02/addres/crresc # bucardo_ctl add herd combine_crres_herd
fagot:/u02/addres/crresc # 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-# ORDER BY table_name; \q
fagot:/u02/addres/crresc # pg_dump -U res -F c -v -f bucardo_tmp_crres_goat.backup -t bucardo_tmp_crres_goat crres
fagot:/u02/addres/crresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_crres_goat.backup
fagot:/u02/addres/crresc # 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=# \c crres res
crres=# drop table bucardo_tmp_crres_goat; \q
fagot:/u02/addres/crresc # bucardo_ctl stop "stop for add sync combine_crres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/crresc # bucardo_ctl add sync combine_crres_sync type=pushdelta source=combine_crres_herd targetdb=combine --verbose
fagot:/u02/addres/crresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_crres_sync'; \q
fagot:/u02/addres/crresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_crres_sync"
fagot:/var/log/bucardo # cd /u02/addres/crresc
### тут ждем пока произойдет синхронизация Цюрупинска
fagot:/u02/addres/crresc # bucardo_ctl stop "stop for add tables combine_crres_sync"
fagot:/u02/addres/crresc # mkdir tables; cd tables
fagot:/u02/addres/crresc/tables # psql -U res crres
crres=# \o tdump.sh \t
crres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
crres-# ||table_catalog||'.res.'||table_name
crres-# ||'.backup" -t "'||table_name||'" '
crres-# ||table_catalog||''
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-# ORDER BY table_name;
crres=# \o trestore.sh
crres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
crres-# ||table_catalog||'.res.'||table_name
crres-# ||'.backup" '||table_catalog||''
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-# ORDER BY table_name; \q
fagot:/u02/addres/crresc/tables # bash tdump.sh
fagot:/u02/addres/crresc/tables # bash trestore.sh
fagot:/u02/addres/crresc/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'crres_res')
bucardo-# and herd = 'combine_crres_herd';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_crres_sync'; \q
fagot:/u02/addres/crresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_crres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_crres_sync"

Ивановка 1 в 1:

fagot:~ # cd /u02/addres; mkdir ivres; cd ivres
fagot:/u02/addres/ivres # psql -U postgres postgres
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:/u02/addres/ivres # bucardo_ctl add all tables db=ivres_res --herd=ivres_herd --verbose
fagot:/u02/addres/ivres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/ivres # pg_dump -h 1.1.52.2 -U postgres -F p -s -v -f "ivres_schema.sql" -n 'res' "ivres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/ivres # psql -f ivres_schema.sql -U res ivres
fagot:/u02/addres/ivres # bucardo_ctl add sync res_ivres_sync type=pushdelta source=ivres_herd targetdb=ivres_obl --verbose
fagot:/u02/addres/ivres # ssh root@1.1.52.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.ivres "ivres""; exit
fagot:/u02/addres/ivres # wget --no-proxy -c --ftp-user=a --ftp-password=a ftp://1.1.52.2/postgres/pgsql.ivres
fagot:/u02/addres/ivres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c ivres res
ivres=# vacuum analyze verbose;
ivres=# \o drop_fk.sql \t
ivres=# SELECT 'ALTER TABLE res.'||t.tablename
ivres-# ||' DROP CONSTRAINT '||con.conname||';'
ivres-# FROM pg_constraint con, pg_class cl, pg_tables t,
ivres-# information_schema.constraint_column_usage cc
ivres-# WHERE t.schemaname = 'res'
ivres-# AND t.tablename = cl.relname
ivres-# AND con.contype = 'f'
ivres-# AND con.conrelid = cl.oid
ivres-# AND cc.constraint_name = con.conname
ivres-# ORDER BY t.tablename, con.conname;
ivres=# \o drop_fk.log \i drop_fk.sql
ivres=# \o drop_ix.sql
ivres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
ivres-# from pg_indexes
ivres-# where schemaname = 'res';
ivres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/ivres # pg_restore -U res -d ivres -v -a "pgsql.ivres"
fagot:/u02/addres/ivres # grep 'CREATE INDEX' -B 1 ivres_schema.sql > restore_ix.sql
fagot:/u02/addres/ivres # grep 'CREATE UNIQUE INDEX' -B 1 ivres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/ivres # psql -U res ivres
ivres=# truncate table log; truncate table rp_abon;
ivres=# truncate table dual; truncate table billsqlreport;
ivres=# \i restore_ix.sql \i restore_u_ix.sql
ivres=# vacuum analyze verbose; \q

Ивановка combine:

fagot:/u02/addres/ivres # cd ..; mkdir ivresc; cd ivresc
fagot:/u02/addres/ivresc # bucardo_ctl add herd combine_ivres_herd
fagot:/u02/addres/ivresc # psql -U res ivres
ivres=# CREATE TABLE res.bucardo_tmp_ivres_goat AS
ivres-# SELECT table_name
ivres-# FROM information_schema.tables
ivres-# WHERE table_schema = 'res'
ivres-# AND table_type = 'BASE TABLE'
ivres-# AND (table_name IN (SELECT table_name
ivres(# FROM information_schema.columns
ivres(# WHERE column_name = 'domain_info'))
ivres-# ORDER BY table_name; \q
fagot:/u02/addres/ivresc # pg_dump -U res -F c -v -f bucardo_tmp_ivres_goat.backup -t bucardo_tmp_ivres_goat ivres
fagot:/u02/addres/ivresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_ivres_goat.backup
fagot:/u02/addres/ivresc # psql -U bucardo bucardo
bucardo=# insert into herdmap
bucardo-# select 'combine_ivres_herd', id from goat
bucardo-# where db = 'ivres_res'
bucardo-# and tablename in
bucardo-# (select table_name
bucardo(# from bucardo_tmp_ivres_goat);
bucardo=# \c ivres res
ivres=# drop table bucardo_tmp_ivres_goat; \q
fagot:/u02/addres/ivresc # bucardo_ctl stop "stop for add sync combine_ivres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/ivresc # bucardo_ctl add sync combine_ivres_sync type=pushdelta source=combine_ivres_herd targetdb=combine --verbose
fagot:/u02/addres/ivresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_ivres_sync'; \q
fagot:/u02/addres/ivresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_ivres_sync"
fagot:/var/log/bucardo # cd /u02/addres/ivresc
### тут ждем пока произойдет синхронизация Ивановки
fagot:/u02/addres/ivresc # bucardo_ctl stop "stop for add tables combine_ivres_sync"
fagot:/u02/addres/ivresc # mkdir tables; cd tables
fagot:/u02/addres/ivresc/tables # psql -U res ivres
ivres=# \o tdump.sh \t
ivres=# SELECT 'pg_dump -a -U res -F c -v -f "table.'
ivres-# ||table_catalog||'.res.'||table_name
ivres-# ||'.backup" -t "'||table_name||'" '
ivres-# ||table_catalog||''
ivres-# FROM information_schema.tables
ivres-# WHERE table_schema = 'res'
ivres-# AND table_type = 'BASE TABLE'
ivres-# AND (table_name IN (SELECT table_name
ivres(# FROM information_schema.columns
ivres(# WHERE column_name = 'domain_info'))
ivres-# ORDER BY table_name;
ivres=# \o trestore.sh
ivres=# SELECT 'pg_restore -U res -d combine -a -v "table.'
ivres-# ||table_catalog||'.res.'||table_name
ivres-# ||'.backup" '||table_catalog||''
ivres-# FROM information_schema.tables
ivres-# WHERE table_schema = 'res'
ivres-# AND table_type = 'BASE TABLE'
ivres-# AND (table_name IN (SELECT table_name
ivres(# FROM information_schema.columns
ivres(# WHERE column_name = 'domain_info'))
ivres-# ORDER BY table_name; \q
fagot:/u02/addres/ivresc/tables # bash tdump.sh
fagot:/u02/addres/ivresc/tables # bash trestore.sh

#!!!!!!!!!!! Незакончено - кривизна данных с горностаевкой
#Тут оказалось, что Горностаевку с Ивановкой криво засшили,
#поэтому в них есть пересечение по диапазону PK.
#Программеров темой нагрузил, они там попатчили, сказали,
#что исправили всё. Выносим Ивановку из combine
#и заливаем по новому.
fagot:/u02/addres/ivresc/tables # psql -U res combine
combine=# \o _delete_dinfo.sql \t
combine=# SELECT 'delete from '||table_name||' where domain_info = ''root.iva'';'
combine-# FROM information_schema.tables
combine-# WHERE table_schema = 'res'
combine-# AND table_type = 'BASE TABLE'
combine-# AND (table_name IN (SELECT table_name
combine(# FROM information_schema.columns
combine(# WHERE column_name = 'domain_info'))
combine-# ORDER BY table_name;
combine=# \o _delete_dinfo.log \i _delete_dinfo.sql \q
#После этого по всем правилам переливаем таблицы Ивановки.

#Это нормальный вариант:
fagot:/u02/addres/crresc/tables # psql -U bucardo bucardo
bucardo=# delete from herdmap
bucardo-# where goat = (select id from goat
bucardo(# where tablename = 'correctionreason'
bucardo(# and db = 'crres_res')
bucardo-# and herd = 'combine_crres_herd';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_crres_sync'; \q
fagot:/u02/addres/crresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_crres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_crres_sync"

Каланчак 1 в 1:

fagot:~ # cd /u02/addres; mkdir klres; cd klres
fagot:/u02/addres/klres # psql -U postgres postgres
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:/u02/addres/klres # bucardo_ctl add all tables db=klres_res --herd=klres_herd --verbose
fagot:/u02/addres/klres # bucardo_ctl remove table log rp_abon dual billsqlreport countergen_or
fagot:/u02/addres/klres # pg_dump -h 1.1.26.2 -U postgres -F p -s -v -f "klres_schema.sql" -n 'res' "klres"
### ВНИМАНИЕ!!! Ручная работа. Проверяем корректность схемы!!!
fagot:/u02/addres/klres # psql -f klres_schema.sql -U res klres
fagot:/u02/addres/klres # bucardo_ctl add sync res_klres_sync type=pushdelta source=klres_herd targetdb=klres_obl --verbose
fagot:/u02/addres/klres # ssh root@1.1.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:/u02/addres/klres # wget --no-proxy -c --ftp-user=s --ftp-password=a ftp://1.1.26.2/postgres/pgsql.klres
fagot:/u02/addres/klres # psql -U bucardo bucardo
bucardo=# update sync set copytype='insert';
bucardo=# \c klres res
klres=# vacuum analyze verbose;
klres=# \o drop_fk.sql \t
klres=# SELECT 'ALTER TABLE res.'||t.tablename
klres-# ||' DROP CONSTRAINT '||con.conname||';'
klres-# FROM pg_constraint con, pg_class cl, pg_tables t,
klres-# information_schema.constraint_column_usage cc
klres-# WHERE t.schemaname = 'res'
klres-# AND t.tablename = cl.relname
klres-# AND con.contype = 'f'
klres-# AND con.conrelid = cl.oid
klres-# AND cc.constraint_name = con.conname
klres-# ORDER BY t.tablename, con.conname;
klres=# \o drop_fk.log \i drop_fk.sql
klres=# \o drop_ix.sql
klres=# select 'DROP INDEX '||schemaname||'.'||indexname||';'
klres-# from pg_indexes
klres-# where schemaname = 'res';
klres=# \o drop_ix.log \i drop_ix.sql \q
fagot:/u02/addres/klres # pg_restore -U res -d klres -v -a "pgsql.klres"
fagot:/u02/addres/klres # grep 'CREATE INDEX' -B 1 klres_schema.sql > restore_ix.sql
fagot:/u02/addres/klres # grep 'CREATE UNIQUE INDEX' -B 1 klres_schema.sql > restore_u_ix.sql
fagot:/u02/addres/klres # psql -U res klres
klres=# truncate table log; truncate table rp_abon;
klres=# truncate table dual; truncate table billsqlreport;
klres=# \i restore_ix.sql \i restore_u_ix.sql
klres=# vacuum analyze verbose; \q

Каланчак combine:

fagot:/u02/addres/klres # cd ..; mkdir klresc; cd klresc
fagot:/u02/addres/klresc # bucardo_ctl add herd combine_klres_herd
fagot:/u02/addres/klresc # 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/addres/klresc # pg_dump -U res -F c -v -f bucardo_tmp_klres_goat.backup -t bucardo_tmp_klres_goat klres
fagot:/u02/addres/klresc # pg_restore -U bucardo -d bucardo -v bucardo_tmp_klres_goat.backup
fagot:/u02/addres/klresc # 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);
bucardo=# \c klres res
klres=# drop table bucardo_tmp_klres_goat; \q
fagot:/u02/addres/klresc # bucardo_ctl stop "stop for add sync combine_klres_sync"
### тут ждем пока репликация остановится полностью
fagot:/u02/addres/klresc # bucardo_ctl add sync combine_klres_sync type=pushdelta source=combine_klres_herd targetdb=combine --verbose
fagot:/u02/addres/klresc # psql -U bucardo bucardo
bucardo=# update bucardo.sync set copytype = 'insert';
bucardo=# update bucardo.sync set status = 'inactive'
bucardo-# where name = 'combine_klres_sync'; \q
fagot:/u02/addres/klresc # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl start "start after add sync combine_klres_sync"
fagot:/var/log/bucardo # cd /u02/addres/klresc
### тут ждем пока произойдет синхронизация Каланчака
fagot:/u02/addres/klresc # bucardo_ctl stop "stop for add tables combine_klres_sync"
fagot:/u02/addres/klresc # mkdir tables; cd tables
fagot:/u02/addres/klresc/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||'" '
klres-# ||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;
klres=# \o trestore.sh
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/addres/klresc/tables # bash tdump.sh
fagot:/u02/addres/klresc/tables # bash trestore.sh
fagot:/u02/addres/klresc/tables # 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';
bucardo=# update bucardo.sync set status = 'active'
bucardo-# where name = 'combine_klres_sync'; \q
fagot:/u02/addres/klresc/tables # cd /var/log/bucardo
fagot:/var/log/bucardo # bucardo_ctl stop "combine_klres_sync"
### тут ждем пока репликация остановится полностью
fagot:/var/log/bucardo # bucardo_ctl start "combine_klres_sync"

Общее заверщение:

fagot:/ # cd /u02/addres/
fagot:/u02/addres # bucardo_ctl remove table tmpcounter tmpcounter2counter tmpmol tmpnomenklature
fagot:/u02/addres # psql -U res combine
combine=# truncate table tmpcounter;
combine=# truncate table tmpcounter2counter;
combine=# truncate table tmpmol;
combine=# truncate table tmpnomenklature; \q
fagot:/u02/addres # pg_dump -h 1.1.25.2 -U postgres -F p -s -v -f "skres_schema.sql" -n 'res' "UTF8_sk"
fagot:/u02/addres # grep 'CREATE INDEX' -B 1 skres_schema.sql > restore_ix.sql
fagot:/u02/addres # grep 'CREATE UNIQUE INDEX' -B 1 skres_schema.sql > restore_u_ix.sql
fagot:/u02/addres # psql -U res combine
combine=# \i restore_ix.sql \i restore_u_ix.sql

Комментариев нет: