Репликация PostgreSQL+Bucardo. Работа с РЭСами

Привычка все документировать :)
После всех экспериментов начинаю реализовывать промышленную схему репликации. Тут последовательность действий. Комментариев будет мало, в основном конкретные инструкции.
Поехали...

Скадовск. Первая БД.

alexsf@shaman:~> ssh alexsf@10.77.11.71
alexsf@bucardik:~> sudo -s
bucardik:/home/alexsf # /etc/init.d/bucardo_stop
postgres@bucardik:~> cd work
postgres@bucardik:~/work> mkdir old.1
postgres@bucardik:~/work> mv old old.1
postgres@bucardik:~/work> mv skres_schema.sql old.1
postgres@bucardik:~/work> pg_dump -h 10.77.25.2 -p 5432 -U postgres -F p -s -s -v -f "skres_schema.sql" -n 'res' "UTF8_sk"
postgres@bucardik:~/work> psql -h 10.77.11.71 -U postgres res
res=# drop schema res cascade;
res=# CREATE SCHEMA res AUTHORIZATION res;
res=# \q
postgres@bucardik:~/work> psql -f skres_schema.sql -h 10.77.11.71 -U postgres res
postgres@bucardik:~/work> psql -h 10.77.11.71 -U res res
res=# \o drop_constr.sql
res=# \t
res=# SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
res-# FROM pg_constraint con, pg_class cl, pg_tables t, information_schema.constraint_column_usage cc
res-# WHERE t.schemaname = 'res'
res-# AND t.tablename = cl.relname
res-# AND con.contype = 'c'
res-# AND con.conrelid = cl.oid
res-# AND cc.constraint_name = con.conname
res-# AND cl.relname not in ('billsqlreport','dual','rp_abon','log')
res-# ORDER BY con.conname;
res=# \o drop_rule.sql
res=# SELECT 'DROP RULE '||rulename||' ON '||tablename||';'
res-# FROM pg_rules
res-# WHERE schemaname = 'res'
res-# ORDER BY tablename, rulename;
res=# \i drop_constr.sql
res=# \i drop_rule.sql
res=# \q
postgres@bucardik:~/work> psql -h 10.77.11.71 -U bucardo bucardo
bucardo=# INSERT INTO bucardo.db (name, dbhost, dbname, dbuser, dbpass)
bucardo-# VALUES ('skres','10.77.25.2','UTF8_sk','res','???');
bucardo=# INSERT INTO bucardo.db (name, dbhost, dbname, dbuser, dbpass)
bucardo-# VALUES ('allres','10.77.11.71','res','res','???');
bucardo=# INSERT INTO bucardo.dbgroup(name) VALUES('slave');
bucardo=# INSERT INTO bucardo.dbgroup(name) VALUES('master');
bucardo=# INSERT INTO bucardo.dbmap(db, dbgroup) VALUES('skres','slave');
bucardo=# INSERT INTO bucardo.dbmap(db, dbgroup) VALUES('allres','master');
bucardo=# \q
postgres@bucardik:~/work> psql -h 10.77.11.71 -U res res
res=# \o 2goat.sql
res=# \t
res=# SELECT 'INSERT INTO bucardo.goat
res'# (db,schemaname,tablename,pkey,pkeytype,analyze_after_copy)
res'# VALUES (''skres'',''res'','''||cl.relname
res-# ||''','''||cc.column_name||''',''bigint'',''false'');'
res-# FROM pg_constraint con, pg_class cl, pg_tables t,
res-# information_schema.constraint_column_usage cc
res-# WHERE t.schemaname = 'res'
res-# AND t.tablename = cl.relname
res-# AND con.contype = 'p'
res-# AND con.conrelid = cl.oid
res-# AND cc.constraint_name = con.conname
res-# AND cl.relname not in ('billsqlreport','dual','rp_abon',
res(# 'log','auth_action','auth_domain',
res(# 'auth_dt_ac_type','auth_entity','auth_entity_segr',
res(# 'auth_group','auth_group_action','auth_role',
res(# 'auth_role_action','auth_segr','auth_segrtype',
res(# 'auth_user','auth_user_action','auth_user_group',
res(# 'auth_userstatus')
res-# ORDER BY cl.relname;
res=# \q
postgres@bucardik:~/work> psql -h 10.77.11.71 -U bucardo bucardo
bucardo=# \i 2goat.sql
bucardo=# INSERT INTO bucardo.herd(name) VALUES('herd_skres');
bucardo=# INSERT INTO bucardo.herdmap (herd, goat)
bucardo-# SELECT 'herd_skres', id
bucardo-# FROM goat WHERE db = 'skres';
bucardo=# \q
postgres@bucardik:~/work> ssh root@10.77.25.2
linux01:~ # su - postgres -c "cd /files/ftp; pg_dump -C -F c -b -f pgsql.UTF8_sk "UTF8_sk""
linux01:~ # exit
postgres@bucardik:~/work> wget --no-proxy -c --ftp-user=sit --ftp-password=??? ftp://10.77.25.2/ftp/pgsql.UTF8_sk
postgres@bucardik:~/work> psql -h 10.77.11.71 -U bucardo bucardo
bucardo=# INSERT INTO bucardo.sync (name,source,targetdb,synctype,
bucardo(# stayalive,checktime,analyze_after_copy)
bucardo-# VALUES('sync_skres','herd_skres','allres','pushdelta','false','60 minutes','false');
bucardo=# \q
postgres@bucardik:~/work> psql -h 10.77.11.71 -U res res
res=# \o drop_fk.sql
res=# \t
res=# SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
res-# FROM pg_constraint con, pg_class cl, pg_tables t, information_schema.constraint_column_usage cc
res-# WHERE t.schemaname = 'res'
res-# AND t.tablename = cl.relname
res-# AND con.contype = 'f'
res-# AND con.conrelid = cl.oid
res-# AND cc.constraint_name = con.conname
res-# ORDER BY con.conname;
res=# \q
postgres@bucardik:~/work> psql -h 10.77.11.71 -U res res
res=# \i drop_fk.sql
res=# \q
postgres@bucardik:~/work> /usr/bin/pg_restore -h 10.77.11.71 -p 5432 -U res -d res -a -v "pgsql.UTF8_sk"
postgres@bucardik:~/work> grep FOREIGN -B 1 skres_schema.sql > restore_fk.sql
postgres@bucardik:~/work> psql -h 10.77.11.71 -U res res
res=# \i restore_fk.sql
res=# \q
postgres@bucardik:~/work> exit
bucardik:~ # /etc/init.d/bucardo_start

Все, проверил, Скадовск катается. Теперь Новотроицк.

bucardik:~ # /etc/init.d/bucardo_stop
bucardik:~ # su - postgres
postgres@bucardik:~> cd work
postgres@bucardik:~/work> mkdir skres
postgres@bucardik:~/work> mv 2* skres; mv d* skres
postgres@bucardik:~/work> mv p* skres; mv r* skres; mv skres_* skres
postgres@bucardik:~/work> mkdir ntres
postgres@bucardik:~/work> cd ntres
postgres@bucardik:~/work/ntres> psql -h 10.77.50.2 -U postgres ntres
ntres=# ALTER ROLE res SUPERUSER;
ntres=# \q
postgres@bucardik:~/work/ntres> psql -h 10.77.50.2 -U res ntres
ntres=# CREATE LANGUAGE plpgsql;
ntres=# CREATE LANGUAGE plperlu;
ntres=# \q
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U bucardo bucardo
bucardo=# INSERT INTO bucardo.db (name, dbhost, dbname, dbuser, dbpass)
bucardo-# VALUES ('ntres','10.77.50.2','ntres','res','???');
bucardo=# INSERT INTO bucardo.dbmap(db, dbgroup) VALUES('ntres','slave');
bucardo=# \q
postgres@bucardik:~/work/ntres> mkdir /u02/pgdata/prepload
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U postgres postgres
postgres=# CREATE TABLESPACE prepload OWNER res
postgres-# LOCATION '/u02/pgdata/prepload';
postgres=# CREATE DATABASE prepload WITH ENCODING='UTF8'
postgres-# OWNER=res TABLESPACE=prepload;
postgres=# \q
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
prepload=# CREATE SCHEMA res AUTHORIZATION res;
prepload=# GRANT ALL ON SCHEMA res TO res;
prepload=# \q
postgres@bucardik:~/work/ntres> pg_dump -h 10.77.50.2 -p 5432 -U postgres -F p -s -s -v -f "ntres_schema.sql" -n 'res' "ntres"
postgres@bucardik:~/work/ntres> psql -f ntres_schema.sql -h 10.77.11.71 -U res prepload
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
prepload=# \o tmp_drop_fk.sql
prepload=# \t
prepload=# SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
prepload-# FROM pg_constraint con, pg_class cl, pg_tables t,
prepload-# information_schema.constraint_column_usage cc
prepload-# WHERE t.schemaname = 'res'
prepload-# AND t.tablename = cl.relname
prepload-# AND con.contype = 'f'
prepload-# AND con.conrelid = cl.oid
prepload-# AND cc.constraint_name = con.conname
prepload-# ORDER BY con.conname;
prepload=# \q
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
prepload=# \i tmp_drop_fk.sql
prepload=# \o tmp_drop_constr.sql
prepload=# \t
prepload=# SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
prepload-# FROM pg_constraint con, pg_class cl, pg_tables t,
prepload-# information_schema.constraint_column_usage cc
prepload-# WHERE t.schemaname = 'res'
prepload-# AND t.tablename = cl.relname
prepload-# AND con.conrelid = cl.oid
prepload-# AND cc.constraint_name = con.conname
prepload-# ORDER BY con.conname;
prepload=# \q
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
prepload=# \i tmp_drop_constr.sql
prepload=# \o tmp_drop_tables.sql
prepload=# \t
prepload=# select 'drop table '||table_name||' cascade;'
prepload-# from information_schema.tables
prepload-# where table_schema = 'res'
prepload-# and table_type = 'BASE TABLE'
prepload-# and (table_name like 'auth%'
prepload(# or table_name in ('billsqlreport','dual','rp_abon','log')
prepload(# or table_name not in (select table_name
prepload(# from information_schema.columns
prepload(# where column_name = 'domain_info'))
prepload-# order by table_name;
prepload=# \q
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
prepload=# \i tmp_drop_tables.sql
prepload=# \q
postgres@bucardik:~/work/ntres> grep PRIMARY -B 1 ntres_schema.sql > restore_pk.sql
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
prepload=# \i restore_pk.sql
prepload=# \o 2goat.sql
prepload=# \t
prepload=# SELECT 'INSERT INTO bucardo.goat
prepload'# (db,schemaname,tablename,pkey,pkeytype,analyze_after_copy)
prepload'# VALUES (''ntres'',''res'','''||cl.relname
prepload-# ||''','''||cc.column_name||''',''bigint'',''false'');'
prepload-# FROM pg_constraint con, pg_class cl, pg_tables t,
prepload-# information_schema.constraint_column_usage cc
prepload-# WHERE t.schemaname = 'res'
prepload-# AND t.tablename = cl.relname
prepload-# AND con.contype = 'p'
prepload-# AND con.conrelid = cl.oid
prepload-# AND cc.constraint_name = con.conname
prepload-# ORDER BY cl.relname;
prepload=# \q
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U bucardo bucardo
bucardo=# \i 2goat.sql
bucardo=# INSERT INTO bucardo.herd(name) VALUES('herd_ntres');
bucardo=# INSERT INTO bucardo.herdmap (herd, goat)
bucardo-# SELECT 'herd_ntres', id
bucardo-# FROM goat WHERE db = 'ntres';
bucardo=# \q
postgres@bucardik:~/work/ntres> ssh root@10.77.50.2
linux01:~ # su - postgres -c "pg_dump -C -F c -b -f pgsql.ntres "ntres""
linux01:/u02/scheduler # exit
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U bucardo bucardo
bucardo=# INSERT INTO bucardo.sync (name,source,targetdb,synctype,
bucardo(# stayalive,checktime,analyze_after_copy)
bucardo-# VALUES('sync_ntres','herd_ntres','allres','pushdelta','false','60 minutes','false');
bucardo=# \q
postgres@bucardik:~/work/ntres> ssh root@10.77.50.2
linux01:~ # cp /var/lib/pgsql/pgsql.ntres /u02/scheduler
linux01:~ # exit
postgres@bucardik:~/work/ntres> ssh root@10.77.50.3
linux02:~ # mkdir /mnt/linux01
linux02:~ # mount 10.77.50.2:/u02/scheduler /mnt/linux01
linux02:~ # cp /mnt/linux01/pgsql.ntres /files/
linux02:~ # exit
postgres@bucardik:~/work/ntres> wget --no-proxy -c --ftp-user=sit --ftp-password=??? ftp://10.77.50.3/pgsql.ntres
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
prepload=# drop schema res cascade;
prepload=# create schema res authorization res;
prepload=# \q
postgres@bucardik:~/work/ntres> /usr/bin/pg_restore -h 10.77.11.71 -p 5432 -U res -d prepload -v "pgsql.ntres"
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res prepload
prepload=# \o drop_tables_2load.sql
prepload=# \t
prepload=# select 'drop table '||table_name||' cascade;'
prepload-# from information_schema.tables
prepload-# where table_schema = 'res'
prepload-# and table_type = 'BASE TABLE'
prepload-# and (table_name like 'auth%'
prepload(# or table_name in ('billsqlreport','dual','rp_abon','log')
prepload(# or table_name not in (select table_name
prepload(# from information_schema.columns
prepload(# where column_name = 'domain_info'))
prepload-# order by table_name;
prepload=# \o tmp.log
prepload=# \i drop_tables_2load.sql \q
postgres@bucardik:~/work/ntres> pg_dump -F c -b -a -f pgsql.ntres2load -U res "prepload"
postgres@bucardik:~/work/ntres> pg_dump -F c -b -C -f pgsql.res.backup -U postgres "res"
postgres@bucardik:~/work/ntres> pg_dump -h 10.77.11.71 -p 5432 -U res -F p -s -s -v -f "res_schema.sql" -n 'res' "res"
postgres@bucardik:~/work/ntres> psql -h 10.77.11.71 -U res res
res=# \o drop_fk_2load_tmp.sql
res=# \t
res=# SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
res-# FROM pg_constraint con, pg_class cl, pg_tables t, information_schema.constraint_column_usage cc
res-# WHERE t.schemaname = 'res'
res-# AND t.tablename = cl.relname
res-# AND con.contype = 'f'
res-# AND con.conrelid = cl.oid
res-# AND cc.constraint_name = con.conname
res-# ORDER BY con.conname;
res=# \o tmp.log
res=# \i drop_fk_2load_tmp.sql \q
postgres@bucardik:~/work/ntres> /usr/bin/pg_restore -h 10.77.11.71 -p 5432 -U res -d res -a -v "pgsql.ntres2load"

########################################################################
-- удаление новотроицка из общей БД
select 'delete from '||table_name||' where domain_info = ''root.nt'';'
from information_schema.tables
where table_schema = 'res'
and table_type = 'BASE TABLE'
order by table_name;
########################################################################

prepload=# \o drop_tables_2load.sql
prepload=# select 'drop table '||table_name||' cascade;'
prepload-# from information_schema.tables
prepload-# where table_schema = 'res'
prepload-# and table_type = 'BASE TABLE'
prepload-# and (table_name like 'auth%'
prepload(# or table_name in ('billsqlreport','dual','rp_abon','log')
prepload(# or table_name not in (select table_name
prepload(# from information_schema.columns
prepload(# where column_name = 'domain_info'))
prepload-# order by table_name;
prepload=# \o tmp.log
prepload=# \i drop_tables_2load.sql


prepload=# \o drop_fk_2load.sql
prepload=# \t
prepload=# SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
prepload-# FROM pg_constraint con, pg_class cl, pg_tables t,
prepload-# information_schema.constraint_column_usage cc
prepload-# WHERE t.schemaname = 'res'
prepload-# AND t.tablename = cl.relname
prepload-# AND con.contype = 'f'
prepload-# AND con.conrelid = cl.oid
prepload-# AND cc.constraint_name = con.conname
prepload-# ORDER BY con.conname;
prepload=# \o tmp.log
prepload=# \i drop_fk_2load.sql
prepload=# \o drop_constr_2load.sql
prepload=# SELECT 'ALTER TABLE res.'||t.tablename||' DROP CONSTRAINT '||con.conname||';'
prepload-# FROM pg_constraint con, pg_class cl, pg_tables t,
prepload-# information_schema.constraint_column_usage cc
prepload-# WHERE t.schemaname = 'res'
prepload-# AND t.tablename = cl.relname
prepload-# AND con.conrelid = cl.oid
prepload-# AND cc.constraint_name = con.conname
prepload-# ORDER BY con.conname;
prepload=# \o tmp.log
prepload=# \i drop_constr_2load.sql
prepload=# \o drop_rule_2load.sql
prepload=# SELECT 'DROP RULE '||rulename||' ON '||tablename||';'
prepload-# FROM pg_rules
prepload-# WHERE schemaname = 'res'
prepload-# ORDER BY tablename, rulename;
prepload=# \o tmp.log
prepload=# \i drop_rule_2load.sql
prepload=# \o drop_tables_2load.sql
prepload=# select 'drop table '||table_name||' cascade;'
prepload-# from information_schema.tables
prepload-# where table_schema = 'res'
prepload-# and table_type = 'BASE TABLE'
prepload-# and (table_name like 'auth%'
prepload(# or table_name in ('billsqlreport','dual','rp_abon','log')
prepload(# or table_name not in (select table_name
prepload(# from information_schema.columns
prepload(# where column_name = 'domain_info'))
prepload-# order by table_name;
prepload=# \o tmp.log
prepload=# \i drop_tables_2load.sql
prepload=# \o drop_views_2load.sql
prepload=# select 'drop view '||table_name||';'
prepload-# from information_schema.views
prepload-# where table_catalog = 'prepload'
prepload-# and table_schema = 'res';
prepload=# \o tmp.log
prepload=# \i drop_views_2load.sql
prepload=# \o drop_index_2load.sql
prepload=# select 'alter table '||tablename||' drop index '||indexname||';'
prepload-# from pg_indexes
prepload-# where schemaname = 'res'
prepload-# order by tablename;
prepload=# \o tmp.log