-- 使用runfast02的超级用户进入
pgcli -D 010_runfast02_super

-- test 创建一个新数据库
create database test_publication_db;

-- 在里面创建一张新表
use test_publication_db;

CREATE TABLE users(
    id INT PRIMARY KEY     NOT NULL,
    name           varchar(10)    NOT NULL,
    age            INT     NOT NULL
 );


-- 插入几条数据（插入数据跟创建publication的操作顺序没有先后）
insert into users(id, name, age) values(123,‘whw222‘,22),(2222,‘www222‘,23);

insert into users(id, name, age) values(8,‘whw8‘,88);


-- 创建publication
create publication test_pub for all tables;
-- CREATE PUBLICATION
-- 为单独一张表创建发布者： create publication test_pub for table users;

-- 查看发布者
table pg_publication;
-- 或者
select * from pg_catalog.pg_publication;

-- 删除发布
DROP PUBLICATION [ IF EXISTS ] name [, ...];

CREATE PUBLICATION name
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name

DROP PUBLICATION [ IF EXISTS ] name [, ...];

-- 使用runfast02_ne的超级用户进入
pgcli -D 010_runfast02_new_super

create database test_subscription_db;

use test_subscription_db;


CREATE TABLE users(
    id INT PRIMARY KEY     NOT NULL,
    name           varchar(10)    NOT NULL,
    age            INT     NOT NULL
);


-- 注意 PUBLICATION的名字要写上面创建的发布者的名字！！！
-- ??注意 链接信息要写“发布者”的（本例中是 runfast-02的！）
CREATE SUBSCRIPTION test_sub CONNECTION ‘host=xxx-xxx-runfast02.xxx.rds.cn-xxx-1.xxx.com.cn port=4432 dbname=test_publication_db user=postgres password=xxx&xxx‘ PUBLICATION test_pub; 


-- 查看订阅
table pg_subscription
-- 或者
select * from pg_catalog.pg_subscription;


-- 在订阅者上查看“复制槽”
table pg_replication_slots


-- 删除订阅
DROP SUBSCRIPTION [ IF EXISTS ] name;

CREATE SUBSCRIPTION subscription_name
    CONNECTION ‘conninfo‘
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

ALTER SUBSCRIPTION name CONNECTION ‘conninfo‘
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name RENAME TO new_name

DROP SUBSCRIPTION [ IF EXISTS ] name;