Postgresql配置pglogical 和pg_pathma

centos 7 install pg

创建容器

docker run -d --privileged -v /sys/fs/cgroup:/sys/fs/cgroup -p 5306:5432 --name cent_pg_partition centos_j1 /usr/sbin/init

进入容器

docker exec -it cent_pg_partition /bin/bash

install postgresql

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

yum install postgresql96

yum install postgresql96-server postgresqyl96-contrib

/usr/pgsql-9.6/bin/postgresql96-setup initdb

systemctl enable postgresql-9.6

systemctl start postgresql-9.6

passwd postgres
alter user postgres with password '123456';

登陆数据库服务器

su postgres

psql

create database testduliyan;--create database

create user super with superuser password '34567';

\c testduliyan --切换数据库`

create table duliyan(id int primary key , name varchar(50));

install pglogical

yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm

yum install postgresql96-pglogical

修改配置文件

修改配置文件postgresql.conf

vi /var/lib/pgsql/9.6/data/postgresql.conf

listen_addresses ='*'
port=5432
password_encryption = on

---

wal_level = 'logical'
max_worker_processes = 10 # one per database needed on provider node
max_replication_slots = 10 # one per node needed on provider node
max_wal_senders = 10 # one per node needed on provider node
shared_preload_libraries = 'pglogical'
track_commit_timestamp = on

修改pg_hba.conf

vi /var/lib/pgsql/9.6/data/pg_hba.conf

host all all 0.0.0.0/0 md5.

---

local replication super trust
host replication super 127.0.0.1/32 trust
host replication super ::1/128 trust
host replication super 0.0.0.0/0 trust

CREATE EXTENSION pglogical; -- create 扩展工具`

select extname, extowner from pg_extension;`

yum install net-tool--172.17.0.9

psql -U super -h 127.0.0.1 -d testduliyan -W`

select pglogical.create_node(node_name := 'provider1',dsn :='host=172.17.0.9 port=5432 dbname= testduliyan user= super1 password=34567');`

select pglogical.replication_set_add_all_tables('default',ARRAY['public']);`

install pg_pathman

git clone https://github.com/postgrespro/pg_pathman
下载到主机的root文件夹， 然后复制到容器内

对于bash: make: command not found错误
yum -y install gcc automake autoconf libtool make

对于make: pg_config: Command not found错误
find / -name pg_config-- /usr/pgsql-9.6/bin/pg_config
export PATH=/usr/pgsql-9.6/bin:$PATH

cd pg_pathman

对于Makefile:66: /usr/pgsql-9.6/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory错误
yum install postgresql96-devel

对于fatal error: openssl/ssl.h: No such file or directory #include <openssl/ssl.h>
类似问题参考
yum install openssl
yum install openssl-devel
make USE_PGXS=1
make USE_PGXS=1 install

修改配置文件postgresql.conf

vi /var/lib/pgsql/9.6/data/postgresql.conf

shared_preload_libraries = 'pg_pathman,pglogical'

systemctl start postgresql-9.6

create extension pg_pathman;

\dx

测试pg_pathman工具

reference

create_range_partitions(relation REGCLASS, -- 主表OID
attribute TEXT, -- 分区列名
start_value ANYELEMENT, -- 开始值
p_interval ANYELEMENT, -- 间隔；任意类型，适合任意类型的分区表
p_count INTEGER DEFAULT NULL, -- 分多少个区
partition_data BOOLEAN DEFAULT TRUE) -- 是否立即将数据从主表迁移到分区,
--不建议这么使用, 建议使用非堵塞式的迁移( 调用partition_table_concurrently() )

使用非堵塞式的迁移接口

partition_table_concurrently(relation REGCLASS, -- 主表OID
batch_size INTEGER DEFAULT 1000, -- 一个事务批量迁移多少记录
sleep_time FLOAT8 DEFAULT 1.0) -- 获得行锁失败时，休眠多久再次获取，重

create table part_test(id int, info text, crt_time timestamp not null);
insert into part_test select id,md5(random()::text),clock_timestamp() from generate_series(1,10000) t(id);

select create_range_partitions('part_test'::regclass,'crt_time','2017-12-28 00:00:00'::timestamp,interval '1 month',10,false);--创建分区

select partition_table_concurrently('part_test'::regclass,10000,1.0);-- 迁移数据到分区

select count(*) from only part_test;