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