postgres cassandra_fdw 扩展试用
经过测试cassandra_fdw 对于pg11 支持哟问题，所以就参考官方的建议使用了pg 9.5 构建docker 镜像
cassandra_fdw docker 镜像
- Dockerfile
FROM postgres:9.5 as build
WORKDIR /app
RUN apt-get update && apt-get install -y cmake git build-essential gcc libssl-dev libuv1-dev postgresql-server-dev-9.5
RUN git clone https://github.com/The-Alchemist/cassandra_fdw.git
RUN git clone https://github.com/datastax/cpp-driver.git && cd cpp-driver && git checkout 2.15.0 && cmake . && make && make install
RUN cd /app/cassandra_fdw && USE_PGXS=1 make && USE_PGXS=1 make install
FROM postgres:9.5
RUN apt-get update && apt-get install -y libssl-dev libuv1-dev
COPY --from=build /usr/lib/postgresql /usr/lib/postgresql
COPY --from=build /usr/share/postgresql /usr/share/postgresql
COPY --from=build /usr/local/lib/x86_64-linux-gnu/libcassandra.so /lib/x86_64-linux-gnu/libcassandra.so
RUN ln -s /lib/x86_64-linux-gnu/libcassandra.so /lib/x86_64-linux-gnu/libcassandra.so.2
环境准备
需要依赖cassandra，为了部署方便，使用了scylladb
- docker-compose.yaml
version: "3"
services:
scylladb:
image: scylladb/scylla
command: --authenticator=PasswordAuthenticator
ports:
- "9042:9042"
scylladb2:
image: scylladb/scylla
command: --seeds=scylladb --authenticator=PasswordAuthenticator
ports:
- "9043:9042"
scylladb3:
image: scylladb/scylla
command: --seeds=scylladb --authenticator=PasswordAuthenticator
ports:
- "9044:9042"
psotgres-fdw:
image: dalongrong/pg-cassandra_fdw
ports:
- "5432:5432"
environment:
- "POSTGRES_PASSWORD=dalong"
pg:
image: dalongrong/pgspider:pg_cron
ports:
- "5433:5432"
environment:
- "POSTGRES_PASSWORD=dalong"
- 启动&&初始化scylladb demo数据
docker-compose up -d
// 进入scylladb 容器
cqlsh -u cassandra -p cassandra
cqlsh> CREATE KEYSPACE example WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1};
cqlsh> CREATE TABLE example.oorder (id int primary key);
cqlsh> INSERT into example.oorder (id) values (1);
cqlsh> INSERT into example.oorder (id) values (2);
试用cassandra_fdw
- 创建扩展
CREATE EXTENSION cassandra_fdw;
-- CREATE SERVER object.
CREATE SERVER cass_serv FOREIGN DATA WRAPPER cassandra_fdw
OPTIONS (host 'scylladb');
-- Create a USER MAPPING for the SERVER.
CREATE USER MAPPING FOR public SERVER postgres
OPTIONS (username 'cassandra', password 'cassandra');
-- CREATE a FOREIGN TABLE.
--
-- Note that a valid "primary_key" OPTION is required in order to use
-- UPDATE or DELETE support.
CREATE FOREIGN TABLE test (id int) SERVER cass_serv
OPTIONS (schema_name 'example', table_name 'oorder', primary_key 'id');
-- Query the FOREIGN TABLE.
SELECT * FROM test LIMIT 5;
- 效果
- 集成postgres_fdw 扩展
// 创建扩展
create extension postgres_fdw;
// 创建server
CREATE SERVER pg_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'psotgres-fdw', dbname 'postgres');
// 创建用户映射
CREATE USER MAPPING FOR postgres
SERVER pg_server
OPTIONS (user 'postgres', password 'dalong');
// 创建schema
CREATE SCHEMA app;
// 导入schema
IMPORT FOREIGN SCHEMA public
FROM SERVER pg_server
INTO app;
// 数据查询
select * from app.test;
效果 
说明
cassandra_fdw 支持import schema 特性，我们可以直接导入table 信息
参考资料
https://github.com/The-Alchemist/cassandra_fdw
https://github.com/rongfengliang/pgspider-docker/blob/master/Dockerfile-cassandra_fdw
https://www.cnblogs.com/rongfengliang/p/11209744.html
