psql
export PGPASSWORD=invalid
psql -d gitlabhq_production -U postgres -h sp2-git-db-301.prod-ins2.pcbl.local -p 5432
export PGPASSWORD=invalid
psql -d gitlabhq_production -U postgres -h sp2-git-db-202-p 5432
select * from pg_stat_activity where datname='gitlabhq_production' \gx
select pg_terminate_backend(pid) from pg_stat_activity where datname='gitlabhq_production';
CREATE DATABASE gitlabhq_production
WITH
OWNER = gitlabhq_production
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
psql -d praefecthq_production -U postgres -h sp2-git-db-202 -p 5432
vi /etc/patroni/config.yml
- host all postgres 10.122.2.234/32 md5
patronictl -c /etc/patroni/config.yml reload sp30_db_git_01
- host all postgres 10.122.2.201/32 md5
psql
==============. приемник
\q
username: postgres
password: "AQpwBT98jzOq0NS"
drop DATABASE gitlabhq_production;
CREATE DATABASE gitlabhq_production
WITH
OWNER = gitlabhq_production
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
pg_dump -h sp2-git-db-202 -p 5432 -d gitlabhq_production -Fc -s -U postgres | pg_restore -d gitlabhq_production -h sp2-git-db-301 -p 5432 -U postgres
==============. источник
\c gitlabhq_production
CREATE PUBLICATION git FOR ALL TABLES;
create publication git for all tables;
============ приемник
\c gitlabhq_production
CREATE SUBSCRIPTION git CONNECTION 'host=sp2-git-db-202 dbname=gitlabhq_production user=postgres password=AQpwBT98jzOq0NS' PUBLICATION git WITH (copy_data = true);
========= источник
select * from pg_publication \gx
select * from pg_stat_replication \gx
select * from pg_replication_slots \gx
SELECT redo_lsn,slot_name,restart_lsn,round((redo_lsn-restart_lsn)/1024/1024/1024,2) AS GB_behind
FROM pg_control_checkpoint(), pg_replication_slots ;
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication;
\dRp
============= приемник
select * from pg_subscription \gx
select * from pg_stat_subscription \gx
select * from pg_subscription_rel \gx
\dRs
select * from praefecthq_production.node_status;
SELECT pg_size_pretty( pg_database_size( 'gitlabhq_production' ) );
--------------select pg_drop_replication_slot('git');
=======================================================================================. назад
========. источник
\c gitlabhq_production
select * from pg_stat_activity where datname='gitlabhq_production' \gx
select pg_terminate_backend(pid) from pg_stat_activity where datname='gitlabhq_production';
drop publication git;
============= приемник
\c gitlabhq_production
alter subscription git disable;
alter subscription git set (slot_name=none);
drop subscription git;
================= источник
select pg_drop_replication_slot('git');
============ приемник
\c gitlabhq_production
select 'select setval('''||sch||'.'||pk_seq||''', (SELECT MAX('||col||') FROM '||sch||'.'||tbl||'))'
from ( select tc.table_schema sch, tc.table_name tbl, kcu.column_name col, pg_get_serial_sequence(tc.table_schema||'."'||tc.table_name||'"', kcu.column_name) as pk_seq
from information_schema.table_constraints tc join information_schema.key_column_usage kcu on kcu.constraint_name = tc.constraint_name and kcu.table_schema = tc.table_schema
where tc.constraint_type = 'PRIMARY KEY' and tc.table_name not like UPPER('%_SCHEMA_HISTORY') ) as meta where meta.pk_seq is not null; \gexec
select setval('''||sch||'.'||pk_seq||''', (SELECT MAX('||col||') FROM '||sch||'.'||tbl||'))'
from ( select tc.table_schema sch, tc.table_name tbl, kcu.column_name col, pg_get_serial_sequence(tc.table_schema||'."'||tc.table_name||'"', kcu.column_name) as pk_seq
from information_schema.table_constraints tc join information_schema.key_column_usage kcu on kcu.constraint_name = tc.constraint_name and kcu.table_schema = tc.table_schema
where tc.constraint_type = 'PRIMARY KEY' and tc.table_name not like UPPER('%_SCHEMA_HISTORY') ) as meta where meta.pk_seq is not null; \gexec