|
| 1 | +#pg_dtm |
| 2 | + |
| 3 | +###Design |
| 4 | + |
| 5 | +This repo implements distributed transaction manager using Snapshot Sharing mechanism. General concepts and alternative approaches described in postgres wikihttps://wiki.postgresql.org/wiki/DTM. |
| 6 | + |
| 7 | +Backend-DTM protocol description can be found in[dtmd/README](dtmd/README). |
| 8 | + |
| 9 | +###Installation |
| 10 | + |
| 11 | +* Patch postgres using xtm.patch. After that build and install postgres in usual way. |
| 12 | +```bash |
| 13 | +cd~/code/postgres |
| 14 | +patch -p1<~/code/pg_dtm/xtm.patch |
| 15 | +``` |
| 16 | +* Install pg_dtm extension. |
| 17 | +```bash |
| 18 | +export PATH=/path/to/pgsql/bin/:$PATH |
| 19 | +cd~/code/pg_dtm |
| 20 | +make&& make install |
| 21 | +``` |
| 22 | +* Run dtmd. |
| 23 | +```bash |
| 24 | +cd~/code/pg_dtm/dtmd |
| 25 | +make |
| 26 | +mkdir /tmp/clog |
| 27 | +./bin/dtmd& |
| 28 | +``` |
| 29 | +* To run something meaningful you need at leat two postgres instances. Also pg_dtm requires presense in```shared_preload_libraries```. |
| 30 | +```bash |
| 31 | +initdb -D ./install/data1 |
| 32 | +initdb -D ./install/data2 |
| 33 | +echo"port = 5433">> ./install/data2/postgresql.conf |
| 34 | +echo"shared_preload_libraries = 'pg_dtm'">> ./install/data1/postgresql.conf |
| 35 | +echo"shared_preload_libraries = 'pg_dtm'">> ./install/data2/postgresql.conf |
| 36 | +pg_ctl -D ./install/data1 -l ./install/data1/log start |
| 37 | +pg_ctl -D ./install/data2 -l ./install/data2/log start |
| 38 | +``` |
| 39 | + |
| 40 | +####Automatic provisioning |
| 41 | + |
| 42 | +For a cluster-wide deploy we use ansible, more details in tests/deploy_layouts. (Ansible instructions will be later) |
| 43 | + |
| 44 | +###Usage |
| 45 | + |
| 46 | +Now cluster is running and you can use global tx between two nodes. Let's connect to postgres instances at different ports: |
| 47 | + |
| 48 | +```sql |
| 49 | +create extension pg_dtm;-- node1 |
| 50 | +createtableaccounts(user_idint, amountint);-- node1 |
| 51 | +insert into accounts (select2*generate_series(1,100)-1,0);-- node1, odd user_id's |
| 52 | + create extension pg_dtm;-- node2 |
| 53 | +createtableaccounts(user_idint, amountint);-- node2 |
| 54 | +insert into accounts (select2*generate_series(1,100),0);-- node2, even user_id's |
| 55 | +select dtm_begin_transaction();-- node1, returns global xid, e.g. 42 |
| 56 | +select dtm_join_transaction(42);-- node2, join global tx |
| 57 | +begin;-- node1 |
| 58 | +begin;-- node2 |
| 59 | +update accountsset amount=amount-100where user_id=1;-- node1, transfer money from user#1 |
| 60 | +update accountsset amount=amount+100where user_id=2;-- node2, to user#2 |
| 61 | +commit;-- node1, blocks until second commit happend |
| 62 | +commit;-- node2 |
| 63 | +``` |
| 64 | + |
| 65 | +###Consistency testing |
| 66 | + |
| 67 | +To ensure consistency we use simple bank test: perform a lot of simultaneous transfers between accounts on different servers, while constantly checking total amount of money on all accounts. This test can be found in tests/perf. |
| 68 | + |
| 69 | +```bash |
| 70 | +> go run ./tests/perf/* |
| 71 | + -C value |
| 72 | + Connection string (repeatfor multiple connections) |
| 73 | + -a int |
| 74 | + The number of bank accounts (default 100000) |
| 75 | + -b string |
| 76 | + Backend to use. Possible optinos: transfers, fdw, pgshard, readers. (default"transfers") |
| 77 | + -gUse DTM to keep global consistency |
| 78 | + -iInit database |
| 79 | + -lUse'repeatable read' isolation level instead of'read committed' |
| 80 | + -n int |
| 81 | + The number updates each writer (readerincase of Reades backend) performs (default 10000) |
| 82 | + -pUse parallel execs |
| 83 | + -r int |
| 84 | + The number of readers (default 1) |
| 85 | + -s int |
| 86 | + StartID. Script will update rows starting from this value |
| 87 | + -vShow progress and other stufffor mortals |
| 88 | + -w int |
| 89 | + The number of writers (default 8) |
| 90 | +``` |
| 91 | +
|
| 92 | +So previous installation can be initialized with: |
| 93 | +``` |
| 94 | +go run ./tests/perf/*.go \ |
| 95 | +-C"dbname=postgres port=5432" \ |
| 96 | +-C"dbname=postgres port=5433" \ |
| 97 | +-g -i |
| 98 | +``` |
| 99 | +and tested with: |
| 100 | +``` |
| 101 | +go run ./tests/perf/*.go \ |
| 102 | +-C"dbname=postgres port=5432" \ |
| 103 | +-C"dbname=postgres port=5433" \ |
| 104 | +-g |
| 105 | +``` |
| 106 | +
|
| 107 | +### Using with postres_fdw. |
| 108 | +
|
| 109 | +We also provide a patch, that enables support of global transactions with postres_fdw. After patching and installing postres_fdw it is possible to run sametest via fdw usig key```-b fdw```. |
| 110 | +
|
| 111 | +### Using with pg_shard |
| 112 | +
|
| 113 | +Citus Data have branchin their pg_shard repo, that interacts with transaction manager. https://github.com/citusdata/pg_shard/tree/transaction_manager_integration |
| 114 | +To use this feature one should have following linein postgresql.conf (orset it via GUC) |
| 115 | +``` |
| 116 | +pg_shard.use_dtm_transactions = 1 |
| 117 | +``` |