Un cliente con una configuración bdr defectuosa (cluster dos nodos cuando son necesarios 3) y tras una parada por el uso de secuencias bdr que necesitan un quorum de (nº nodos / 2 + 1) para poder funcionar (y solo funcionaba uno), se le deja con ese único nodo para posteriormente ampliar la configuración bdr a otras 3 máquinas (en otro site) para posteriormente borrar el nodo origen y que el cluster con bdr funcione únicamente en el nuevo site (3 máquinas nuevas).
En las 3 máquinas nuevas (destino) inicializamos el cluster postgres sobre ellas:
-bash-4.2$ /usr/pgsql-9.4/bin/pg_ctl -D /postgresql/autobus/data initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /postgresql/autobus/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /postgresql/autobus/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-9.4/bin/postgres -D /postgresql/autobus/data
or
/usr/pgsql-9.4/bin/pg_ctl -D /postgresql/autobus/data -l logfile start
Se comprueba la configuración inicial del cluster bdr en el nodo origen:
[local]:5432; postgres@autobus # 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 |
+---------------------+---------------+------------+-------------+-----------+---------------------------------------------
+--------------------+----------------+
| 6484326987971901429 | 1 | 16385 | r | node1 | port=5432 dbname=autobus host=192.168.100.10
| NULL | f |
+---------------------+---------------+------------+-------------+-----------+---------------------------------------------
+--------------------+----------------+
(1 row)
Time: 1.779 ms
[local]:5432; postgres@autobus # select * from pg_replication_slots;
+-----------+--------+-----------+--------+----------+--------+------+--------------+-------------+
| slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn |
+-----------+--------+-----------+--------+----------+--------+------+--------------+-------------+
+-----------+--------+-----------+--------+----------+--------+------+--------------+-------------+
(0 rows)
Time: 0.802 ms
Sobre las 3 máquinas destino creamos la base de datos y cargamos la extensión bdr:
postgres=# create database autobus;
CREATE DATABASE
postgres=# \c autobus
You are now connected to database "autobus" as user "postgres".
autobus=# create extension btree_gist;
CREATE EXTENSION
autobus=# create extension bdr;
CREATE EXTENSION
Añadimos el nuevo nodo a la configuración bdr desde todas las máquinas destino:
(nodo1-nuevo)
autobus=# select bdr.bdr_group_join(
local_node_name := '192.168.102.20',
node_external_dsn := 'host=192.168.102.20 port=5434 dbname=autobus',
join_using_dsn :='host=192.168.100.10 port=5432 dbname=autobus');
bdr_group_join
----------------
(1 row)
autobus=# select bdr.bdr_node_join_wait_for_ready();
bdr_node_join_wait_for_ready
------------------------------
(1 row)
(nodo2-nuevo)
autobus=# select bdr.bdr_group_join(
local_node_name := '192.168.102.21',
node_external_dsn := 'host=192.168.102.21 port=5434 dbname=autobus',
join_using_dsn :='host=192.168.102.20 port=5434 dbname=autobus');
bdr_group_join
----------------
(1 row)
autobus=# select bdr.bdr_node_join_wait_for_ready();
bdr_node_join_wait_for_ready
------------------------------
(1 row)
(nodo3-nuevo)
autobus=# select bdr.bdr_group_join(
local_node_name := '192.168.102.22',
node_external_dsn := 'host=192.168.102.22 port=5434 dbname=autobus',
join_using_dsn :='host=192.168.102.20 port=5434 dbname=autobus');
bdr_group_join
----------------
(1 row)
autobus=# select bdr.bdr_node_join_wait_for_ready();
bdr_node_join_wait_for_ready
------------------------------
(1 row)
Comprobamos desde el nodo origen la configuración final:
[local]:5432; postgres@autobus # 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 |
+---------------------+---------------+------------+-------------+---------------+---------------------------------------------+---------------------------------------------+----------------+
| 6484326987971901429 | 1 | 16385 | r | node1 | port=5432 dbname=autobus host=192.168.100.10 | NULL
| f |
| 6906900435321228361 | 1 | 16385 | r | 192.168.102.20 | host=192.168.102.20 port=5434 dbname=autobus | host=192.168.100.10 port=5432 dbname=autobus | f |
| 6906903421302212953 | 1 | 16385 | r | 192.168.102.21 | host=192.168.102.21 port=5434 dbname=autobus | host=192.168.102.20 port=5434 dbname=autobus | f |
| 6906906327689680484 | 1 | 16385 | r | 192.168.102.22 | host=192.168.102.22 port=5434 dbname=autobus | host=192.168.102.20 port=5434 dbname=autobus | f |
+---------------------+---------------+------------+-------------+---------------+---------------------------------------------+---------------------------------------------+----------------+
(4 rows)
Time: 0.718 ms
[local]:5432; postgres@autobus # select * from pg_replication_slots;
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
| slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn |
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
| bdr_16385_6906906327689680484_1_16385__ | bdr | logical | 16385 | autobus | t | NULL | 79287948 | 9B/4611F910 |
| bdr_16385_6906900435321228361_1_16385__ | bdr | logical | 16385 | autobus | t | NULL | 79287948 | 9B/4611F910 |
| bdr_16385_6906903421302212953_1_16385__ | bdr | logical | 16385 | autobus | t | NULL | 79287948 | 9B/4611F910 |
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
(3 rows)
Time: 0.855 ms
La eliminación del nodo origen desde el nodo1-nuevo (puede ser cualquier nodo que no sea el que se va a borrar):
[local]:5434; postgres@autobus # 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 |
+---------------------+---------------+------------+-------------+---------------+---------------------------------------------+---------------------------------------------+----------------+
| 6484326987971901429 | 1 | 16385 | r | node1 | port=5432 dbname=autobus host=192.168.100.10 | NULL | f |
| 6906900435321228361 | 1 | 16385 | r | 192.168.102.20 | host=192.168.102.20 port=5434 dbname=autobus | host=192.168.100.10 port=5432 dbname=autobus | f |
| 6906903421302212953 | 1 | 16385 | r | 192.168.102.21 | host=192.168.102.21 port=5434 dbname=autobus | host=192.168.102.20 port=5434 dbname=autobus | f |
| 6906906327689680484 | 1 | 16385 | r | 192.168.102.22 | host=192.168.102.22 port=5434 dbname=autobus | host=192.168.102.20 port=5434 dbname=autobus | f |
+---------------------+---------------+------------+-------------+---------------+---------------------------------------------+---------------------------------------------+----------------+
(4 rows)
Time: 0.312 ms
[local]:5434; postgres@autobus # select * from pg_replication_slots;
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
| slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn |
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
| bdr_16385_6484326987971901429_1_16385__ | bdr | logical | 16385 | autobus | t | NULL | 723260 | 0/BC0C0118 |
| bdr_16385_6906906327689680484_1_16385__ | bdr | logical | 16385 | autobus | t | NULL | 723260 | 0/BC0C0118 |
| bdr_16385_6906903421302212953_1_16385__ | bdr | logical | 16385 | autobus | t | NULL | 723260 | 0/BC0C0118 |
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
(3 rows)
Time: 2.467 ms
[local]:5434; postgres@autobus # select bdr.bdr_get_local_nodeid();
+-------------------------------+
| bdr_get_local_nodeid |
+-------------------------------+
| (6906900435321228361,1,16385) |
+-------------------------------+
(1 row)
Time: 0.217 ms
[local]:5434; postgres@autobus # select bdr.bdr_get_local_node_name();
+-------------------------+
| bdr_get_local_node_name |
+-------------------------+
| 192.168.102.20 |
+-------------------------+
(1 row)
Time: 0.596 ms
[local]:5434; postgres@autobus # select bdr.bdr_part_by_node_names('{node1}');
+------------------------+
| bdr_part_by_node_names |
+------------------------+
| |
+------------------------+
(1 row)
Time: 2.647 ms
[local]:5434; postgres@autobus # 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 |
+---------------------+---------------+------------+-------------+---------------+---------------------------------------------+---------------------------------------------+----------------+
| 6906900435321228361 | 1 | 16385 | r | 192.168.102.20 | host=192.168.102.20 port=5434 dbname=autobus | host=192.168.100.10 port=5432 dbname=autobus | f |
| 6906903421302212953 | 1 | 16385 | r | 192.168.102.21 | host=192.168.102.21 port=5434 dbname=autobus | host=192.168.102.20 port=5434 dbname=autobus | f |
| 6906906327689680484 | 1 | 16385 | r | 192.168.102.22 | host=192.168.102.22 port=5434 dbname=autobus | host=192.168.102.20 port=5434 dbname=autobus | f |
| 6484326987971901429 | 1 | 16385 | k | node1 | port=5432 dbname=autobus host=192.168.100.10 | NULL | f |
+---------------------+---------------+------------+-------------+---------------+---------------------------------------------+---------------------------------------------+----------------+
(4 rows)
Time: 0.396 ms
[local]:5434; postgres@autobus # delete from bdr.bdr_nodes where node_status='k';
DELETE 1
Time: 1.061 ms
[local]:5434; postgres@autobus # 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 |
+---------------------+---------------+------------+-------------+---------------+---------------------------------------------+---------------------------------------------+----------------+
| 6906900435321228361 | 1 | 16385 | r | 192.168.102.20 | host=192.168.102.20 port=5434 dbname=autobus | host=192.168.100.10 port=5432 dbname=autobus | f |
| 6906903421302212953 | 1 | 16385 | r | 192.168.102.21 | host=192.168.102.21 port=5434 dbname=autobus | host=192.168.102.20 port=5434 dbname=autobus | f |
| 6906906327689680484 | 1 | 16385 | r | 192.168.102.22 | host=192.168.102.22 port=5434 dbname=autobus | host=192.168.102.20 port=5434 dbname=autobus | f |
+---------------------+---------------+------------+-------------+---------------+---------------------------------------------+---------------------------------------------+----------------+
(3 rows)
Time: 0.371 ms
[local]:5434; postgres@autobus # select * from pg_replication_slots;
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
| slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn |
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
| bdr_16385_6906906327689680484_1_16385__ | bdr | logical | 16385 | autobus | t | NULL | 723844 | 0/BC3D3168 |
| bdr_16385_6906903421302212953_1_16385__ | bdr | logical | 16385 | autobus | t | NULL | 723844 | 0/BC3D3168 |
+-----------------------------------------+--------+-----------+--------+----------+--------+------+--------------+-------------+
(2 rows)
Time: 0.677 ms
