cabecada

#ref: 
https://pgstef.github.io/2018/01/04/introduction_to_pgbackrest.html
https://pgbackrest.org/user-guide.html


#setup
we have two servers s1 and s2
s1 is primary and s2 is replica
both have pgbackrest installed 
we have both servers where postgres user can ssh to each other instead of tls.

cabecada

https://kousiknath.medium.com/all-things-sharding-techniques-and-real-life-examples-in-nosql-data-storage-systems-3e8beb98830a

cabecada

$ cat /tmp/schaufel/dummy.conf
consumers = ({
    type = "dummy";
    threads = 1;
});

producers = ({
    type = "dummy";
    threads = 1;
});

cabecada

sharding
https://kousiknath.medium.com/all-things-sharding-techniques-and-real-life-examples-in-nosql-data-storage-systems-3e8beb98830a

cabecada

create table t (col1 int) partition by list(ajhash_generic(3, col1));
create table t1 partition of t for values in (1);
create table t2 partition of t for values in (2);
create table t3 partition of t for values in (3);
select x, ajhash_generic(3, x) from generate_series(1, 10) x;
 x  | ajhash_generic 
----+----------------
  1 |              2
  2 |              1
  3 |              2

cabecada

/*
scope
kpi + pgb + haproxy on same server
so we benefit from proximity and mac address immunity

pgb will maintain a pool of connections of host healthy by haproxy backend
when haproxy backend failsover, if the existing backend is terminated, then ofc it will queue till query_connect_timeout = 120
worst case restart pgbouncer (it will have fresh pool of connections from replica, and when it fails back
pool slowly repopulates with primary server connections without killing old connections (till server_idle_timeout)

cabecada

https://github.com/codeexpress/tcpmirror. fun way to upgrade databases ..... given we have current db on 15432 , and to be upgraded db on 15433, we mirror tcp traffic
~ $ psql -p 15432 -d postgres -h localhost -c 'select 1'
 ?column? 
----------
        1
(1 row)

psql -p 15433 -d postgres -h localhost -c 'select 1'
 ?column? 
----------

cabecada

txid wraparound 

https://cloud.google.com/sql/docs/postgres/txid-wraparound

Finding the database
To find out which database or databases contain the tables that are causing the wraparound, run the following query:


SELECT datname, 
       age(datfrozenxid), 

cabecada

postgres@esh-ab-worker-7 /tmp/vijay $ ls /tmp/vijay/
part-00108-32ff0120-8951-4c61-886c-5737e9e5906c_00000.c000.gz.parquet
part-00131-b9e1a880-2344-498e-9bca-92ec3d1dcf9c_00000.c000.gz.parquet
part-00202-6992e29e-1537-47c6-95ca-f5d0d0d48459_00000.c000.gz.parquet
part-00316-ebc5b084-301a-49d5-be74-39430c980976_00000.c000.gz.parquet
part-00396-c33274ff-9b70-4e42-bcad-fc635dc6cba7_00000.c000.gz.parquet


CREATE TABLE public.p (
    partition_key text,

cabecada

./configure --prefix /opt/12/usr/local  --enable-debug CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer" --enable-cassert --enable-depend  --with-python --with-perl
make
make install