citus failover testing

gistfile1.txt

postgres@pg:~/citusdb$ cat setup.sh
#!/bin/bash

export PATH=/usr/lib/postgresql/14/bin:$PATH
port=5432
if [[ -d dbcr ]]
then
        pg_ctl -D dbcr stop
        rm -rf dbcr*
fi

for i in db1 db2 db3 db4
do
        pg_ctl -D $i stop
        [[ -f ${i}.log ]] && rm ${i}.log
        [[ -d ${i} ]] && rm -rf ${i}
        if [[ $1x == "start"x ]]; then
          initdb -D $i
          echo "shared_preload_libraries = 'citus'" >> $i/postgresql.auto.conf
          echo "port=$port" >>  $i/postgresql.auto.conf
          echo "wal_level=logical"  >>  $i/postgresql.auto.conf
          echo "max_files_per_process=128" >>  $i/postgresql.auto.conf
          echo "max_connections=40" >> $i/postgresql.auto.conf
          echo "log_connections='on'" >> $i/postgresql.auto.conf
          echo "log_min_duration_statement=0" >> $i/postgresql.auto.conf
          echo "max_parallel_workers_per_gather=2" >> $i/postgresql.auto.conf
          echo "logical_decoding_work_mem='32MB'" >> $i/postgresql.auto.conf
          #echo "citus.explain_all_tasks=1" >> $i/postgresql.auto.conf
          port=$(( port + 1 ))
        fi
done
[[ $1 == "start" ]] || exit 1

for i in 1 2 3 4
do
        db=db${i}
        pg_ctl -D ${db} -l ${db}.log start
done

for p in 5432 5433 5434 5435
do
        createdb -p $p citusdb
        psql -p $p -d citusdb -c "create extension citus;"
done

psql -p 5432 citusdb <<'EOF'
 SELECT citus_set_coordinator_host('localhost', 5432);
 SELECT * from citus_add_node('localhost', 5433);
 SELECT * from citus_add_node('localhost', 5434);
 SELECT * from citus_add_node('localhost', 5435);
 CREATE TABLE dist_t(dist_id int primary key, col1 int, col2 text);
 insert into dist_t select x,x,x from generate_series(1, 10000) x;
 CREATE TABLE ref_t(ref_id int primary key, col1 int, col2 int);
 insert into ref_t select x,x,x from generate_series(1, 100000) x;
 SELECT create_reference_table('ref_t');
 SELECT create_distributed_table('dist_t',   'dist_id');
 create table local_t(id int primary key);
 insert into local_t select 1;
EOF

#setup replica for each node
pg_basebackup -D pg1 -c fast -S pg1 -C -R  -d "port=5432"
pg_basebackup -D pg2 -c fast -S pg2 -C -R  -d "port=5433"
pg_basebackup -D pg3 -c fast -S pg3 -C -R  -d "port=5434"
pg_basebackup -D pg4 -c fast -S pg4 -C -R  -d "port=5435"

#update port numbers
for i in 1 2 3 4
do
        perl -i -pe 's/543/643/' pg${i}/postgresql.auto.conf
        pg_ctl -D pg${i} -l pg${i}.log start
done

#this is downtime stuff. force all existing nodes to stop in one dc, to assume no data and replication all caught up
#pg_controldata -D pg1 | grep REDO
#checkpoint if needed on replicas and then confirm if primary and replica have same REDO
for i in 1 2 3 4
do
        pg_ctl -D db${i} -l db${i}.log stop
done

#promote replicas
for i in {6432..6435}
do
        psql citusdb -p $i -c 'select pg_promote();'
done

#update pg_dist_node
for i in {6432..6435}
do
psql citusdb -p $i <<EOF
update pg_dist_node set nodeport = 6432 where nodeport = 5432;
update pg_dist_node set nodeport = 6433 where nodeport = 5433;
update pg_dist_node set nodeport = 6434 where nodeport = 5434;
update pg_dist_node set nodeport = 6435 where nodeport = 5435;
EOF
done

#i am not sure if this is needed if i updated manually on all nodes, but need to confirm
psql citusdb -p 6432 <<EOF
select citus_update_node(1, 'localhost', 6432);
select citus_update_node(2, 'localhost', 6433);
select citus_update_node(3, 'localhost', 6434);
select citus_update_node(4, 'localhost', 6435);
SELECT * FROM citus_get_active_worker_nodes();
EOF

------------------------

output
postgres@pg:~/citusdb$ bash setup.sh start
pg_ctl: directory "db1" does not exist
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 "C.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.

creating directory db1 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

    pg_ctl -D db1 -l logfile start

pg_ctl: directory "db2" does not exist
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 "C.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.

creating directory db2 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

    pg_ctl -D db2 -l logfile start

pg_ctl: directory "db3" does not exist
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 "C.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.

creating directory db3 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

    pg_ctl -D db3 -l logfile start

pg_ctl: directory "db4" does not exist
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 "C.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.

creating directory db4 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

    pg_ctl -D db4 -l logfile start

waiting for server to start.... done
server started
waiting for server to start.... done
server started
waiting for server to start.... done
server started
waiting for server to start.... done
server started
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
 citus_set_coordinator_host
----------------------------

(1 row)

 citus_add_node
----------------
              2
(1 row)

 citus_add_node
----------------
              3
(1 row)

 citus_add_node
----------------
              4
(1 row)

CREATE TABLE
INSERT 0 10000
CREATE TABLE
INSERT 0 100000
NOTICE:  Copying data from local table...
NOTICE:  copying the data has completed
DETAIL:  The local data in the table is no longer visible, but is still on disk.
HINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.ref_t$$)
 create_reference_table
------------------------

(1 row)

NOTICE:  Copying data from local table...
NOTICE:  copying the data has completed
DETAIL:  The local data in the table is no longer visible, but is still on disk.
HINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.dist_t$$)
 create_distributed_table
--------------------------

(1 row)

CREATE TABLE
INSERT 0 1
waiting for server to start.... done
server started
waiting for server to start.... done
server started
waiting for server to start.... done
server started
waiting for server to start.... done
server started
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped

pg_promote
------------
 t
(1 row)

 pg_promote
------------
 t
(1 row)

 pg_promote
------------
 t
(1 row)

 pg_promote
------------
 t
(1 row)

UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
 citus_update_node
-------------------

(1 row)

 citus_update_node
-------------------

(1 row)

 citus_update_node
-------------------

(1 row)

 citus_update_node
-------------------

(1 row)

 node_name | node_port
-----------+-----------
 localhost |      6435
 localhost |      6434
 localhost |      6433
(3 rows)

#basic testing
postgres@pg:~/citusdb$ psql citusdb -p 6432
psql (14.8 (Ubuntu 14.8-1.pgdg22.04+1))
Type "help" for help.

citusdb=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

citusdb=# SELECT * FROM citus_get_active_worker_nodes();
 node_name | node_port
-----------+-----------
 localhost |      6435
 localhost |      6434
 localhost |      6433
(3 rows)

citusdb=# select count(1) from dist_t;
 count
-------
 10000
(1 row)

citusdb=# delete from dist_t where dist_id < 50;
DELETE 49
citusdb=# insert into dist_t select x,x,x from generate_series(1, 49) x;
INSERT 0 49
citusdb=#