COMMON )
# yum remove postgresql10* postgresql9*
# yum install postgresql-bdr94-bdr
# vi /etc/hosts
------------------------------------------------------------------------------------------------
192.168.122.1 psql1
192.168.122.2 psql2
------------------------------------------------------------------------------------------------
# su - postgres
-bash-4.2$ vi .bash_profile
------------------------------------------------------------------------------------------------
export PATH=${PATH}:/usr/pgsql-9.4/bin
------------------------------------------------------------------------------------------------
-bash-4.2$ source .bash_profile
-bash-4.2$ rm -fr /var/lib/pgsql/9.4-bdr/*
-bash-4.2$ initdb /var/lib/pgsql/9.4-bdr -A trust
-bash-4.2$ cd 9.4-bdr/
-bash-4.2$ vi postgresql.conf
------------------------------------------------------------------------------------------------
listen_addresses = '*'
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
max_connections = 100
max_wal_senders = 10
max_replication_slots = 10
max_worker_processes = 10
------------------------------------------------------------------------------------------------
-bash-4.2$ vi pg_hba.conf
------------------------------------------------------------------------------------------------
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host replication bdrsync 192.168.122.1/32 trust
host replication bdrsync 192.168.122.2/32 trust
host ktest_db bdrsync 192.168.122.1/32 password
host ktest_db bdrsync 192.168.122.2/32 password
------------------------------------------------------------------------------------------------
-bash-4.2$ pg_ctl -l /tmp/pg_bdr.log -D /var/lib/pgsql/9.4-bdr start
-bash-4.2$ psql -c "CREATE USER bdrsync superuser;"
-bash-4.2$ psql -c "ALTER USER bdrsync WITH PASSWORD '12345#';"
-bash-4.2$ createuser ktester
-bash-4.2$ createdb -O ktester ktest_db
-bash-4.2$ psql ktest_db -c 'CREATE EXTENSION btree_gist;'
-bash-4.2$ psql ktest_db -c 'CREATE EXTENSION bdr;'
-bash-4.2$ pg_ctl -l /tmp/pg_bdr.log -D /var/lib/pgsql/9.4-bdr restart
-bash-4.2$ psql
postgres=# \c ktest_db
Master1 (IP: 192.168.122.1)
ktest_db=# SELECT bdr.bdr_group_create(local_node_name := 'psql1', node_external_dsn := 'host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345#');
bdr_group_create
------------------
(1 row)
ktest_db=# select * from bdr.bdr_nodes;
node_sysid | node_timeline | node_dboid | node_status | node_name | node_local_dsn
| node_init_from_dsn | node_read_only | node_seq_id
---------------------+---------------+------------+-------------+-----------+---------------------------------------------
---------------------+--------------------+----------------+-------------
6735928151685930893 | 1 | 16387 | r | psql1 | host=192.168.122.1 user=bdrsync dbname=sum_
db password=12345# | | f |
(1 row)
sum_db=# select * from bdr.bdr_connections;
conn_sysid | conn_timeline | conn_dboid | conn_origin_sysid | conn_origin_timeline | conn_origin_dboid | conn_is
_unidirectional | conn_dsn | conn_apply_delay | conn_replication_s
ets
---------------------+---------------+------------+-------------------+----------------------+-------------------+--------
----------------+------------------------------------------------------------------+------------------+-------------------
----
6735928151685930893 | 1 | 16387 | 0 | 0 | 0 | f
| host=192.168.122.71 user=bdrsync dbname=ktest_db password=12345# | | {default}
(1 row)
Master2 (IP: 192.168.122.2)
ktest_db=# SELECT bdr.bdr_group_join(local_node_name := 'psql2', node_external_dsn := 'host=192.168.122.2 user=bdrsync dbname=ktest_db password=12345#', join_using_dsn := 'host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345#');
ktest_db=# SELECT bdr.bdr_group_join(local_node_name := 'psql2', node_external_dsn := 'host=192.168.122.2 user=bdrsync dbname=ktest_db password=12345#', join_using_dsn := 'host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345#');
bdr_group_join
----------------
(1 row)
ktest_db=# select * from bdr.bdr_nodes;
node_sysid | node_timeline | node_dboid | node_status | node_name | node_local_dsn
node_sysid | node_timeline | node_dboid | node_status | node_name | node_local_dsn
| node_init_from_dsn | node_read_only | node_seq_id
---------------------+---------------+------------+-------------+-----------+-----------------------------------------------
-------------------+-----------------------------------------------------------------+----------------+-------------
6737344874282799845 | 1 | 16387 | r | psql1 | host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345# | | f |
6737381937345819706 | 1 | 16387 | r | psql2 | host=192.168.122.2 user=bdrsync dbname=ktest_db password=12345# | host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345# | f |
(2 rows)
Any node)
ktest_db=# create table shared_table ( idx serial primary key, username varchar (50) unique not null, memo varchar(500) not null);
CREATE TABLE
ktest_db=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | shared_table | table | postgres
(1 row)
ktest_db=# \d shared_table;
Table "public.shared_table"
Column | Type | Modifiers
----------+------------------------+------------------------------------------------------------
idx | integer | not null default nextval('shared_table_idx_seq'::regclass)
username | character varying(50) | not null
memo | character varying(500) | not null
Indexes:
"shared_table_pkey" PRIMARY KEY, btree (idx)
"shared_table_username_key" UNIQUE CONSTRAINT, btree (username)
ktest_db=# insert into shared_table (username,memo) values ('a1','aMemo'), ('a2','a2Memo');
INSERT 0 2
ktest_db=# select * from shared_table;
idx | username | memo
-----+----------+--------
1 | a1 | aMemo
2 | a2 | a2Memo
(2 rows)
Now it will be error for put data at the other node
ktest_db=# insert into shared_table (username,memo) values ('a4','a4Memo');
ERROR: duplicate key value violates unique constraint "shared_table_pkey"
DETAIL: Key (idx)=(1) already exists.
So, try this then no error.
ktest_db=# insert into shared_table (idx,username,memo) values ((select count(idx)+1 from shared_table),'a4','a4Memo');
INSERT 0 1
ktest_db=# insert into shared_table (idx,username,memo) values ((select count(idx)+1 from shared_table),'b4','b4Memo');
INSERT 0 1
ktest_db=# select * from shared_table;
idx | username | memo
-----+----------+--------
1 | a1 | aMemo
2 | a2 | a2Memo
3 | b4 | b4Memo
4 | a4 | a4Memo
or
ktest_db=# insert into shared_table (idx,username,memo) values ((select idx from shared_table order by idx desc limit 1)+1,'a5','a5Memo');
or
ktest_db=# insert into shared_table (idx,username,memo) values ((select max(idx) from shared_table)+1,'a5','a5Memo');
issue 1)
ktest_db=# SELECT bdr.bdr_group_create(local_node_name := 'psql1', node_external_dsn := 'host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345#');
FATAL: could not connect to the server in non-replication mode: FATAL: no pg_hba.conf entry for host "192.168.122.1", user "bdrsync", database "ktest_db", SSL off
DETAIL: dsn was: connect_timeout=30 keepalives=1 keepalives_idle=20 keepalives_interval=20 keepalives_count=5 host=192.168.122.71 user=bdrsync dbname=ktest_db password=12345# fallback_application_name='bdr (6735928151685930893,1,16387,):bdrnodeinfo'
CONTEXT: SQL statement "SELECT * FROM bdr_get_remote_nodeinfo(node_local_dsn)"
PL/pgSQL function internal_begin_join(text,text,text,text) line 42 at SQL statement
SQL statement "SELECT bdr.internal_begin_join(
'bdr_group_join',
local_node_name,
CASE WHEN node_local_dsn IS NULL THEN node_external_dsn ELSE node_local_dsn END,
join_using_dsn)"
PL/pgSQL function bdr_group_join(text,text,text,text,integer,text[]) line 21 at PERFORM
SQL statement "SELECT bdr.bdr_group_join(
local_node_name := local_node_name,
node_external_dsn := node_external_dsn,
join_using_dsn := null,
node_local_dsn := node_local_dsn,
apply_delay := apply_delay,
replication_sets := replication_sets)"
PL/pgSQL function bdr_group_create(text,text,text,integer,text[]) line 84 at PERFORM
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
==> This meaning is bdrsync user can't acess ktest_db. because, not defined at pg_hba.conf file.
issue 2)
amstest=# SELECT bdr.bdr_group_join(
amstest(# local_node_name := 'psql2',
amstest(# node_external_dsn := 'host=192.168.122.2 user=bdrsync dbname=ktest_db password=12345#',
amstest(# join_using_dsn := 'host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345#'
amstest(# );
ERROR: establish BDR: FATAL: no pg_hba.conf entry for replication connection from host "192.168.122.2", user "bdrsync", SSL off
DETAIL: Connection string is 'replication=database fallback_application_name='BDR test connection' connect_timeout=30 keepalives=1 keepalives_idle=20 keepalives_interval=20 keepalives_count=5 host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345#'
CONTEXT: SQL statement "SELECT * FROM bdr_test_replication_connection(remote_dsn)"
PL/pgSQL function internal_begin_join(text,text,text,text) line 110 at SQL statement
SQL statement "SELECT bdr.internal_begin_join(
'bdr_group_join',
local_node_name,
CASE WHEN node_local_dsn IS NULL THEN node_external_dsn ELSE node_local_dsn END,
join_using_dsn)"
PL/pgSQL function bdr_group_join(text,text,text,text,integer,text[]) line 21 at PERFORM
==> This meaning is bdrsync user can't access ktest_db at psql1 server.
So, it need also modify pg_hba.conf file.
bdrsync can access ktest_db with password. and bdrsync can access to replication db with trust (SSL) option.
issue 3)
Not create, join
issue4)
---------------------------------------------------------------------------------
issue 3)
ktest_db=# SELECT bdr.bdr_group_create(local_node_name := 'psql2', node_external_dsn := 'host=192.168.122.2 user=bdrsync dbname=ktest_db password=12345#', join_using_dsn := 'host=192.168.122.1 user=bdrsync dbname=ktest_db password=12345#');
ERROR: function bdr.bdr_group_create(local_node_name := unknown, node_external_dsn := unknown, join_using_dsn := unknown) does not exist
LINE 1: SELECT bdr.bdr_group_create(local_node_name := 'psql2', node...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Not create, join
issue4)
---------------------------------------------------------------------------------
-bash-4.2$ psql kage_db -c 'CREATE EXTENSION bdr;'
ERROR: bdr can only be loaded via shared_preload_libraries
---------------------------------------------------------------------------------------
Please check postgresql.conf file.
missing shared_preload_libraries = 'bdr' line.
Please check postgresql.conf file.
missing shared_preload_libraries = 'bdr' line.
