創(chuàng)建容器
docker run -d --privileged -v /sys/fs/cgroup:/sys/fs/cgroup -p 5306:5432 --name cent_pg_partition centos_j1 /usr/sbin/init
進(jìn)入容器
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';
登陸數(shù)據(jù)庫服務(wù)器
su postgres
psql
create database testduliyan;--create database
create user super with superuser password '34567';
\c testduliyan --切換數(shù)據(jù)庫`
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 擴(kuò)展工具`
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
下載到主機(jī)的root文件夾梆造， 然后復(fù)制到容器內(nèi)
對于bash: make: command not found錯(cuò)誤
yum -y install gcc automake autoconf libtool make
對于make: pg_config: Command not found錯(cuò)誤
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錯(cuò)誤
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工具
create_range_partitions(relation REGCLASS, -- 主表OID
attribute TEXT, -- 分區(qū)列名
start_value ANYELEMENT, -- 開始值
p_interval ANYELEMENT, -- 間隔；任意類型，適合任意類型的分區(qū)表
p_count INTEGER DEFAULT NULL, -- 分多少個(gè)區(qū)
partition_data BOOLEAN DEFAULT TRUE) -- 是否立即將數(shù)據(jù)從主表遷移到分區(qū),
--不建議這么使用, 建議使用非堵塞式的遷移( 調(diào)用partition_table_concurrently() )
使用非堵塞式的遷移接口
partition_table_concurrently(relation REGCLASS, -- 主表OID
batch_size INTEGER DEFAULT 1000, -- 一個(gè)事務(wù)批量遷移多少記錄
sleep_time FLOAT8 DEFAULT 1.0) -- 獲得行鎖失敗時(shí)斜友，休眠多久再次獲取，重
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);--創(chuàng)建分區(qū)
select partition_table_concurrently('part_test'::regclass,10000,1.0);-- 遷移數(shù)據(jù)到分區(qū)
select count(*) from only part_test;
