內(nèi)容說明pg同步

1、使用 sktyools 中的 londiste3 從 pg 同步至 gp。準(zhǔn)備1. 上傳-2.7.8.tar.xz,psycopg2-2.5.tar.gz，skytools-3.2.tar.gz 至需要安裝的tgres與 greenplum 的所有節(jié)點(diǎn), 三個(gè) 的地址分別為：；2. 配置好 yum 源tgres 安裝 skytools1 在安裝之前，需要確認(rèn)幾個(gè)依賴包是否已經(jīng)安裝好，確定 版本是否過低(要求版本 = 2.5)。 rootnfs src#2.6.6-V版本過低請(qǐng)參考如下安裝高版本的官網(wǎng)源碼編譯安裝rootlocalhost src# tar -xvf-2.7.8.tar.xzro

2、otlocalhost src#cd-2.7.8rootlocalhost -2.7.8# ./configure -enable-shared -prefix=/opt/2.7.8 HYPERLINK mailto:rootlocalhost-2.7.8 rootl HYPERLINK mailto:rootlocalhost-2.7.8 ocalhost-2.7.8# make HYPERLINK mailto:rootlocalhost-2.7.8 rootl HYPERLINK mailto:rootlocalhost-2.7.8 ocalhost-2.7.8# make instal

3、l HYPERLINK mailto:rootlocalhost-2.7.8 rootl HYPERLINK mailto:rootlocalhost-2.7.8 ocalhost-2.7.8# ldconfig -p|grep -ilib2.6.so.1.0 (libc6,x86-64) = /usr/lib64/lib2.6.so.1.0 HYPERLINK mailto:rootlocalhost-2.7.8 rootl HYPERLINK mailto:rootlocalhost-2.7.8 ocalhost-2.7.8# ldconfig重新加載配置文件-2.7.8# ldconfi

4、g -p|grep -irootlocalhostlib2.6.so (libc6,x86-64) = /usr/lib64/lib2.6.solibpyglib-2.0-.so.0 (libc6,x86-64) = /usr/lib64/libpyglib-2.0-.so.0 libpyglib-2.0-.so (libc6,x86-64) = /usr/lib64/libpyglib-2.0-.so安裝完后，需要修改文件/etc/ld.so.conf rootlocalhost-2.7.8# vi /etc/ld.so.conf添加/opt/2.7.8/liblib2.7.so.1.0 (

5、libc6,x86-64) = /opt/2.7.8/lib/lib2.7.so.1.0lib2.7.so (libc6,x86-64) = /opt/2.7.8/lib/lib2.7.solib2.6.so.1.0 (libc6,x86-64) = /usr/lib64/lib2.6.so.1.0lib2.6.so (libc6,x86-64) = /usr/lib64/lib2.6.solibpyglib-2.0-.so.0 (libc6,x86-64) = /usr/lib64/libpyglib-2.0-.so.0libpyglib-2.0-.so (libc6,x86-64) = /

6、usr/lib64/libpyglib-2.0-.so2.源碼安裝 psycopg2 rootlocalhost src# tar -xvf psycopg2-2.5.tar.gz注；如果為如上3.安裝 skytools源碼安裝，需要指定路徑/opt/ setup.py build2.7.8/bin/rootlocalhost src# tar -xvf skytools-3.2.tar.gzrootlocalhost src#cd skytools-3.2 HYPERLINK mailto:rootlocalhostskytools-3.2 root localhost skytools-3

7、.2#./configure -prefix=/usr/local/skytools-3.2 -with-pgconfig=/usr/local/pgsql/bin/pg_config HYPERLINK mailto:rootlocalhostskytools-3.2 root HYPERLINK mailto:rootlocalhostskytools-3.2 localhost skytools-3.2# make HYPERLINK mailto:rootlocalhostskytools-3.2 root HYPERLINK mailto:rootlocalhostskytools-

8、3.2 localhost skytools-3.2# make install HYPERLINK mailto:rootlocalhostskytools-3.2 root HYPERLINK mailto:rootlocalhostskytools-3.2 localhost skytools-3.2#setup_pkgloady build HYPERLINK mailto:rootlocalhostskytools-3.2 root HYPERLINK mailto:rootlocalhostskytools-3.2 localhost skytools-3.2#setup_pkgl

9、oady install HYPERLINK mailto:rootlocalhostskytools-3.2 root HYPERLINK mailto:rootlocalhostskytools-3.2 localhost skytools-3.2#setup_skytools.py buildsetup_skytools.py installroot localhost skytools-3.2#4.修改tgres 環(huán)境變量并創(chuàng)建日志目錄 HYPERLINK mailto:rootlocalhostskytools-3.2 root HYPERLINK mailto:rootlocalh

10、ostskytools-3.2 localhost skytools-3.2#su tgrestgres localhost $ vi .bash_profile export PGPORT=5432export PGHOME=/usr/local/pgsql export PGDATA=/PGDATA/dataexport PATH=$PGHOME/bin:$PATH:$HOME/bin:/usr/local/skytools-3.2/binexport LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH:/usr/local/skytools-3.2/

11、librootlocalhost src# cd psycopg2-2.5rootlocalhost psycopg2-2.5#su tgrestgres localhost $ which pg_config/usr/local/pgsql/bin/pg_config tgres localhost $exitrootlocalhost psycopg2-2.5#vi setup.cfg修改為如下 pg_config=/opt/pgsql/bin/pg_configroot localhost psycopg2-2.5#setup.py buildroot localhost psycopg

12、2-2.5#setup.py installtgres localhost $ which londiste3/usr/bin/londiste3tgres localhost $ londiste3 help tgres localhost $ mkdir londiste-查看是否能正確顯示5.配置 provider 文件并創(chuàng)建 root 節(jié)點(diǎn)tgres localhost $cd londistetgres localhost londiste$ vi p_skytf.ini londiste3job_name = src_pgqdb = host=192.168.236.120 por

13、t=5432 dbname=pgq usgq password=pgqpgq_queue_name = pgqtgres localhost londiste$ psql psql (9.4.2)Type help for help.tgres=# create database pgq; CREATE DATABASEtgrestgres=# create usCREATE ROLEtgres=#qgq superuser login ENCRYPTED PASSWORD pgq;tgres localhost londiste$cd /PGDATA/data添加本機(jī) ip 可以（如下本機(jī)

14、192.168.236.120）tgres localhost data#vi pg_hba.confhosthost host hostallreplication allallallpgqpgq all192.168.236.120/32192.168.236.121/32192.168.236.121/320.0.0.0/32trusttrust trusttrust其中 120 為本機(jī)tgres 數(shù)據(jù)庫 ip，121 為greenplum 數(shù)據(jù)庫 iptgres localhost data#tgres localhost londiste$ pg_ctl restart -m fas

15、t waiting for server to shut down.doneserver stopped server startingtgres localhost londiste$ LOG: redirecting log output to logging collector pro HFuture log output will appear in directory /PGDATA/data/pg_log.tgres localhost data#cdtgres localhost#cd londistestgres localhost#londiste3 p_skytf.ini

16、create-root node_p host=192.168.236.120 port=5432 usgqpassword=pgq dbname=pgq2015-07-30 10:22:23,449 11552 INFO plpgsql is installed2015-07-30 10:22:23,451 11552 INFO Installing pgqlogfile =/home/tgres/londiste/p_skytf.logfile =/home/tgres/londiste/p_fskytf.2015-07-30 10:22:23,451 11552 INFOReading

17、from /usr/share/skytools3/pgq.sql2015-07-30 10:22:23,670 11552 INFO pgq.get_batch_cursor is installed2015-07-30 10:22:23,671 11552 INFO Installing pgq_ext2015-07-30 10:22:23,671 11552 INFOReading from /usr/share/skytools3/pgq_ext.sql2015-07-30 10:22:23,749 11552 INFO Installing pgq_node2015-07-30 10

18、:22:23,749 11552 INFOReading from /usr/share/skytools3/pgq_node.sql2015-07-30 10:22:23,846 11552 INFO Installing londiste2015-07-30 10:22:23,847 11552 INFOReading from /usr/share/skytools3/londiste.sql2015-07-30 10:22:24,004 11552 INFO londiste.global_add_table is installed2015-07-30 10:22:24,031 11

19、552 INFO Initializing node2015-07-30 10:22:24,034 11552 INFO Location registered2015-07-30 10:22:24,111 11552 INFO Node node_p initialized for queue pgq with type root 2015-07-30 10:22:24,114 11552 INFO Done創(chuàng)建成功啟動(dòng) provider 進(jìn)程tgres localhost londiste$ lsp_skytf.ini p_skytf.logtgres localhost londiste

20、$ londiste3 -d p_skytf.ini workertgres localhost londiste$ ps -ef|grep londiste /usr/bin/londiste3 -d p_skytf.ini workertgres 116261 0 10:29 ?00:00:00 /usr/bin/tgres 11631 11056 0 10:29 pts/100:00:00 grep londiste6.配置并啟動(dòng)tickertgres localhost londiste$ vi pgq.ini pgqd# where to loglogfile = /home/#fi

21、lefile = /home/tgres/londiste/pgqd.logtgres/londiste/pgqd.# optional parameters # libpq connect string without dbname= base_connstr = host=192.168.236.120 us # startup db to query other databases #initial_database = template1# limit ticker to specific databases database_list = pgq# logo syslog #sysl

22、og = 1 #syslog_ident = pgqd# optional timeouts # how often to check for new databases #check_period = 60# how often to flush retry queue#retry_period = 30gq password=pgq port=5432# how often to#ma _period = 120tentance# how often to run tickerticker_period = 1tgres localhost londiste$ pgqd -d pgq.in

23、i2015-07-30 10:46:39.744 11924 LOG Starting pgqd 3.2tgres localhost londiste$ ps -ef|grep pgqtgres 11913 115430 10:45 ?0 10:46 ?0 10:46 ?0 10:46 pts/100:00:00tgres: pgq pgq 192.168.236.120(43821) idletgres 11926100:00:00 pgqd -d pgq.initgres 11927 11543tgres 11932 1105600:00:00tgres: pgq pgq 192.168

24、.236.120(43822) idle00:00:00 grep pgqgreenplum 安裝 skytools配置環(huán)境需要在 greenplum 的每個(gè)節(jié)點(diǎn)上執(zhí)行1.檢查 greenplum 的節(jié)點(diǎn)上安裝）版本（版本低可參考如上安裝方式，注意需要在 gp 的每個(gè)rootgpmaster2.6.6#-V2.安裝 psycopg2rootgpmaster rootgpmaster rootgpmastersrc# src#src#tar -xvf psycopg2-2.5.tar.gzn -R gpadmin:gpadmin psycopg2-2.5 su - gpadmingpadming

25、pmaster $ cd /usr/local/src/psycopg2-2.5gpadmingpmastsycopg2-2.5$ which pg_config/usr/local/greenplum-db/bin/pg_configgpadmingpmastsycopg2-2.5$ vi setup.cfgpg_config=/usr/local/greenplum-db/bin/pg_configgpadmingpmastgpadmingpmastsycopg2-2.5$sycopg2-2.5$setup.py buildsetup.py install3. 安裝 skytools ro

26、otgpmaster rootgpmasterrootgpmastersrc# src#src#tar -xvf skytools-3.2.tar.gzn -R gpadmin:gpadmin skytools-3.2 cd skytools-3.2rootgpmaster skytools-3.2# ./configure -prefix=/usr/local/skytools-3.2 -with-pgconfig=/usr/local/greenplum-db/bin/pg_configrootgpmaster skytools-3.2# su - gpadmingpadmingpmast

27、er gpadmingpmaster gpadmingpmasterlogout$ cd /usr/local/src/skytools-3.2 skytools-3.2$ makeskytools-3.2$ exitrootgpmaster skytools-3.2# make installrootxingrootxing rootxingskytools-3.2# cd/skytoolsskytools#n gpadmin:gpadmin installer_config.pyskytools# su - gpadmingpadminxing gpadminxing gpadminxin

28、g gpadminxinggpadminxingskytools#cd /usr/local/src/skytools-3.2skytools-3.2$ skytools-3.2$ skytools-3.2$skytools-3.2$以下操作在greenplummaster 節(jié)點(diǎn)上操作即可1. 修改配置文件添加如下gpadmingpmaster $ vi .bash_profilesource export export exportexport/usr/local/greenplum-db/greenplum_path.sh MASTER_DATA_DIRECTORY=/PG/gpmaste

29、r/segdb-1 PGPORT=5432PGUSER=gpadminPGDATABASE=tgresexportexportGPHOME=/usr/local/greenplum-dbPATH=$PATH:$HOME/bin:/usr/local/skytools-3.2/bingpadmingpmaster $source .bash_profile gpadmingpmaster $ londiste3 help-查看是否可用 2.創(chuàng)建londiste 同步所需 function，schema 等 gpadmingpmaster $ lsgpAdminLogs gpconfigs hos

30、tfile_exkeys hostfile_gpssh_segonlygpadmingpmaster gpadmingpmastergpadmingpmaster$ mkdir londiste$ cd londiste londiste$ pwd/home/gpadmin/londiste將pgq.sql,pgq_node.sql,londiste.sql，txid.sql 上傳至/home/gpadmin/londiste注意修改權(quán)限r(nóng)ootgpmaster rootgpmaster rootgpmasterrootgpmasterskytools# cd /home/gpadmingpa

31、dmin# cd londiste/londiste#n gpadmin:gpadmin *londiste# su gpadmingpadmingpmaster gpadmingpmaster gpadmingpmasterpsql (8.2.15)$ cd londiste londiste$londiste$ psqlType help for help.tgres=# create database pgq; CREATE DATABASE YKZVEYQ_ZUURY V_ HORJ YKZVEYQ_ZUURY V_ OTYZGRR YKZVEVQMRUGJ_ HORJ YKZVEVQ

32、MRUGJ_ OTYZGRRtgres=# create usgq superuser login ENCRYPTED PASSWORD CREATE ROLE運(yùn)行上傳的 4 條sqltgres=# c pgq pgq=# i txid.sqlpgq=# i pgq_node.sql pgq=# i londiste.sqlpgq=# i pgq.sqlpgq;3.創(chuàng)建 leaf 節(jié)點(diǎn)并啟動(dòng)相關(guān)進(jìn)程創(chuàng)建consumer 文件gpadmingpmaster londiste$vi s21_skytf.ini londiste3job_name = s21_gyldb = dbname=pgq u

33、spgq_queue_name = pgqgq port=5432 host=192.168.236.121 password=pgqlogfile = /home/gpadmin/londiste/s21_skytf.logfile = /home/gpadmin/londiste/s21_skytf.配置本機(jī)可以gpadmingpmaster londiste$ cd /PG/gpmaster/segdb-1/ gpadmingpmaster segdb-1$ vi pg_hba.confhosthost host hostallreplication allallallpgqpgq al

34、l192.168.236.121/32192.168.236.120/32192.168.236.120/320.0.0.0/32tgres 數(shù)據(jù)庫 iptrusttrust trusttrust其中 121 為本機(jī)greenplumip，120 為gpadmingpmaster segdb-1$cdgpadmingpmaster $ cd londistegpadmingpmaster londiste$ londiste3 s21_skytf.ini creaeaf node_b host=192.168.236.121 port=5432 usgq password=pgqdbname=

35、pgq -provide=host=192.168.236.120 port=5432 usgq dbname=pgq password=pgq2015-07-30 12:19:18,597 18133 INFO plpgsql is installed2015-07-30 12:19:18,604 18133 INFO pgq is installed2015-07-30 12:19:18,617 18133 INFO pgq.get_batch_cursor is installed2015-07-30 12:19:18,618 18133 INFO Installing pgq_ext2

36、015-07-3012:19:18,61918133INFOReadingfrom/usr/local/greenplum-db/ext/share/skytools3/pgq_ext.sql2015-07-30 12:19:22,441 18133 INFO pgq_node is installed2015-07-30 12:19:22,442 18133 INFO londiste is installed2015-07-30 12:19:22,460 18133 INFO londiste.global_add_table is installed2015-07-30 12:19:23

37、,934 18133 INFO Initializing node2015-07-30 12:19:24,026 18133 INFO Location registered2015-07-30 12:19:24,035 18133 INFO Location registered2015-07-30 12:19:24,048 18133 INFO Subscriber registered: node_b2015-07-30 12:19:24,106 18133 INFO Location registered2015-07-30 12:19:24,411 18133 INFO Locati

38、on registered2015-07-30 12:19:24,613 18133 INFO Node node_b initialized for queue pgq with type leaf 2015-07-30 12:19:24,882 18133 INFO Donegpadmingpmaster londiste$ londiste3 -d s21_skytf.ini worker gpadmingpmaster londiste$gpadmingpmaster londiste$ ps -ef|grep londistegpadmin1831010 12:21 ?00:00:0

39、0 /usr/local/greenplum-db/ext/bin/usr/local/greenplum-db/./ext/bin/londiste3 -d s21_skytf.ini workergpadmin12:21 pts/200:00:00 grep londiste驗(yàn)證狀態(tài)與情況主tgres 數(shù)據(jù)庫tgresnfs londiste$ londiste3 p_skytf.ini susQueue: pgqLocal node: node_pnode_p (root)|+-: node_b (leaf)Tables: 0/0/0Lag: 17s, Tick: 274Tables:

40、0/0/0Lag: 17s, Tick: 274從greenplum 數(shù)據(jù)庫gpadmingpmaster londiste$ londiste3 s21_skytf.ini susQueue: pgqLocal node: node_bnode_p (root)|+-: node_b (leaf)Tables: 0/0/0Lag: 4s, Tick: 274Tables: 0/0/0Lag: 4s, Tick: 274主從庫pg 與gp 分別創(chuàng)建測(cè)試表pgq=# create table xing(id添加測(cè)試表primary key,name varchar(20);主tgres$ lon

41、diste3 p_skytf.ini add-table public.xing2015-07-30 13:10:35,727 14206 INFO Table added: public.xing從greenplum$ londiste3 s21_skytf.ini add-table public.xing -no-triggers2015-07-30 13:11:35,337 1956 INFO Table added with no triggers: public.xing$ londiste3 s21_skytf.ini tables Tables on nodetable_nam

42、emerge_s etable_attrspublic.xingin-copy狀態(tài)變化 none in-copy catching-up wanna-sync do-sync ok 主pg數(shù)據(jù)pgq=# insertINSERT 0 1pgq=# insert INSERT 0 1pgq=# insert pgq=# insert INSERT 0 1pgq=# insert INSERT 0 1pgq=# insert INSERT 0 1pgq=# insertpgq=#qo xing values(1,guang);o xing values(2,guang);o xing values

43、(3,guang);o xing values(4,guang);o xing values(5,guang);o xing values(6,guang);o xing values(7,guang);$ londiste3 p_skytf.ini compare2015-07-30 13:18:45,580 14342 INFO Checking if node_p can be used for copy 2015-07-30 13:18:45,582 14342 INFO Node node_p seems good source, using it2015-07-30 13:18:4

44、5,583 14342 INFO public.xing: Using node node_p as provider 2015-07-30 13:18:45,631 14342 INFO Provider: node_p (root)2015-07-30 13:18:45,647 14342 INFO Locking public.xing2015-07-30 13:18:45,650 14342 INFO Syncing public.xing2015-07-30 13:18:48,686 14342 INFO Counting public.xing2015-07-30 13:18:48

45、,716 14342 INFO srcdb: 7 rows, checksum=21220058912015-07-30 13:18:48,718 14342 INFO dstdb: 7 rows, checksum=2122005891$ londiste3 p_skytf.ini s usQueue: pgqLocal node: node_pnode_p (root)|+-: node_b (leaf)Tables: 1/0/0Lag: 21s, Tick: 362Tables: 1/0/0Lag: 21s, Tick: 362從pg 查看狀態(tài)pgq=# select * from xi

46、ng; id | name+1 | guang3 | guang5 | guang7 | guang2 | guang4 | guang6 | guang刪除表在greenplum 主節(jié)點(diǎn)編輯remove_table.sqlpgq=# qtgresnfs londiste$ londiste3 p_skytf.ini s usQueue: pgqLocal node: node_pnode_p (root)|+-: node_b (leaf)Tables: 0/0/0Lag: 14s, Tick: 374Tables: 0/0/0Lag: 14s, Tick: 374可以看出都執(zhí)行remove 后還需要到greenplum 中truncate 該表。注：還需要將那些本不需要truncate 的表添加回來，如下 londiste3 s21_skytf.ini add-table public.ming no-triggers(在次添加即使不指定no-triggers，狀態(tài)也是正常的，原先的一些表信息已經(jīng)被)葉子節(jié)點(diǎn)執(zhí)行add-table 時(shí)未指定no-triggers，添加空表后查看狀態(tài)正常但向pg 表中$ londiste3 s21_skytf.ini tables Tables on node數(shù)據(jù)后如下table_namemerge_s etable_at