使用singer tap-postgres 同步数据到pg
singer 是一个很不错的开源etl 解决方案，以下演示一个简单的数据从pg 同步到pg
很简单就是使用tap-postgres + target-postgres
环境准备
对于测试的环境的数据库使用docker-compose 运行
- docker-compose 文件
version: "3"
services:
tap:
image: postgres:9.6.11
ports:
- "5433:5432"
environment:
- "POSTGRES_PASSWORD:dalong"
target:
image: postgres:9.6.11
ports:
- "5432:5432"
environment:
- "POSTGRES_PASSWORD:dalong"
- tap 以及target 环境的配置
singer 推荐的环境配置使用python venv 虚拟环境
tap 配置
mkdir tap-pg
cd tap-pg
python3 -m venv venv
source venv/bin/activate
pip install tap-postgre
target 配置
mkdir target-pg
cdtarget-pg
python3 -m venv venv
source venv/bin/activate
pip installtarget-postgres
- 项目结构
-rw-r--r-- 1 dalong staff 251B 6 5 14:20 docker-compose.yaml
-rw-r--r-- 1 dalong staff 145B 6 5 14:27 tap-pg.json
-rw-r--r-- 1 dalong staff 143B 6 5 14:27 target-pg.json
- 启动pg 数据库以及初始化测试数据
docker-compose up -d
导入测试数据： 注意连接 localhost 5433 端口pg 服务
CREATE TABLE userapps (
id SERIAL PRIMARY KEY,
username text,
userappname text
);
INSERT INTO "public"."userapps"("id","username","userappname")
VALUES
(1,E'dalong',E'app'),
(2,E'first',E'login');
使用tap 以及target
- 配置数据库连接
tap: tap-pg.json
{
"host": "localhost",
"port": 5433,
"dbname": "postgres",
"user": "postgres",
"password": "dalong",
"schema": "public"
}
target: target 数据库配置
{
"host": "localhost",
"port": 5432,
"dbname": "postgres",
"user": "postgres",
"password": "dalong",
"schema": "copy"
}
- tap 模式发现
运行方式
./tap-pg/venv/bin/tap-postgres -c ta-pg.json -d > catalog.json
- 选择需要同步的表以及同步方式
以下为一个简单的demo，实际可以自己根据情况调整
{
"streams": [
{
"table_name": "userapps",
"stream": "userapps",
"metadata": [
{
"breadcrumb": [],
"metadata": {
"table-key-properties": [
"id"
],
+ "selected": true,
+ "replication-method": "FULL_TABLE",
"schema-name": "public",
"database-name": "postgres",
"row-count": 0,
"is-view": false
}
},
{
"breadcrumb": [
"properties",
"id"
],
"metadata": {
"sql-datatype": "integer",
"inclusion": "automatic",
"selected-by-default": true
}
},
{
"breadcrumb": [
"properties",
"username"
],
"metadata": {
"sql-datatype": "text",
"inclusion": "available",
"selected-by-default": true
}
},
{
"breadcrumb": [
"properties",
"userappname"
],
"metadata": {
"sql-datatype": "text",
"inclusion": "available",
"selected-by-default": true
}
}
],
"tap_stream_id": "postgres-public-userapps",
"schema": {
"type": "object",
"properties": {
"id": {
"type": [
"integer"
],
"minimum": -2147483648,
"maximum": 2147483647
},
"username": {
"type": [
"null",
"string"
]
},
"userappname": {
"type": [
"null",
"string"
]
}
},
"definitions": {
"sdc_recursive_integer_array": {
"type": [
"null",
"integer",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_integer_array"
}
},
"sdc_recursive_number_array": {
"type": [
"null",
"number",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_number_array"
}
},
"sdc_recursive_string_array": {
"type": [
"null",
"string",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_string_array"
}
},
"sdc_recursive_boolean_array": {
"type": [
"null",
"boolean",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_boolean_array"
}
},
"sdc_recursive_timestamp_array": {
"type": [
"null",
"string",
"array"
],
"format": "date-time",
"items": {
"$ref": "#/definitions/sdc_recursive_timestamp_array"
}
},
"sdc_recursive_object_array": {
"type": [
"null",
"object",
"array"
],
"items": {
"$ref": "#/definitions/sdc_recursive_object_array"
}
}
}
}
}
]
}
- 执行同步
./tap-pg/venv/bin/tap-postgres -c tap-pg.json --catalog catalog.json | ./target-pg/venv/bin/target-postgres -c target-pg.json
效果
/Users/dalong/mylearning/singer-project/target-pg/venv/lib/python3.7/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel
package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For det
ails see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
""")
INFO Selected streams: ['postgres-public-userapps']
INFO No currently_syncing found
INFO Beginning sync of stream(postgres-public-userapps) with sync method(full)
INFO Stream postgres-public-userapps is using full_table replication
INFO Current Server Encoding: UTF8
INFO Current Client Encoding: UTF8
INFO hstore is UNavailable
INFO Beginning new Full Table replication 1559717835286
INFO select SELECT "id" , "userappname" , "username" , xmin::text::bigint
FROM "public"."userapps"
ORDER BY xmin::text ASC with itersize 20000
INFO METRIC: {"type": "counter", "metric": "record_count", "value": 2, "tags": {}}
INFO Table 'userapps' does not exist. Creating
INFO Loading 2 rows into 'userapps'
INFO COPY userapps_temp ("id", "userappname", "username") FROM STDIN WITH (FORMAT CSV, ESCAPE '\')
INFO UPDATE 0
INFO INSERT 0 2
{"bookmarks": {"postgres-public-userapps": {"last_replication_method": "FULL_TABLE", "version": 1559717835286, "xmin": null}}, "currently_syncing": null}
- 界面效果
说明
以上只是一个简单的演示，实际上我们可选的工具很多，比如dbt，pgloader，数据导出导入，其他类似etl 工具，或者使用pg 的fdw,dblink。。。
参考资料
https://github.com/rongfengliang/singer-pg2pg
https://github.com/singer-io/tap-postgres
https://www.getdbt.com/
https://github.com/dimitri/pgloader
https://www.postgresql.org/docs/10/contrib-dblink-function.html
