cabecada

postgres@pg:~/demo$ cat post_upgrade.sh
#!/bin/bash

killall /usr/lib/postgresql/15/bin/postgres
killall /usr/lib/postgresql/16/bin/postgres

rm -rf /var/lib/postgresql/demo/15
rm -rf /var/lib/postgresql/demo/16

mkdir -p /var/lib/postgresql/demo/{15,16}

cabecada

https://www.youtube.com/watch?v=FWK3lR6bSn8
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
https://www.postgresql.org/docs/current/ssl-tcp.html
https://www.cybertec-postgresql.com/en/setting-up-ssl-authentication-for-postgresql/
https://luppeng.wordpress.com/2021/08/07/create-and-install-ssl-certificates-for-postgresql-database-running-locally/
https://www.alibabacloud.com/blog/599116


pgbouncer
https://www.crunchydata.com/blog/improving-pgbouncer-security-with-tlsssl

cabecada

playing with corruption

 initdb -D db1 --data-checksums 2>/dev/null >/dev/null
 pg_ctl -D db1 -l db1.log start
 psql <<EOF
create table t(col1 int primary key, col2 int);
create table t2(col1 int references t(col1) on update cascade on delete cascade);
insert into t select generate_series(1, 100);
insert into t2 select generate_series(1, 100);
EOF

cabecada

https://www.postgresql.fastware.com/blog/how-to-fix-transaction-wraparound-in-postgresql


wget https://github.com/postgres/postgres/archive/697f8d266cfb33409f7ccf3319f4448477066329.zip
unzip 697f8d266cfb33409f7ccf3319f4448477066329.zip
cd postgresql-16
./configure --prefix /opt/16/usr/local --enable-tap-tests
cd /var/lib/postgresql/postgres/postgres-16/src/test/modules/xid_wraparound
make check PG_TEST_EXTRA='xid_wraparound'

cabecada

postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col1   | integer |           | not null |
 col2   | text    |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (col1)
Referenced by:
    TABLE "t2" CONSTRAINT "t2_col2_fkey" FOREIGN KEY (col2) REFERENCES t1(col1) ON UPDATE CASCADE ON DELETE CASCADE

cabecada

 postgres@pg:~/citusdb/migration/14$ /usr/lib/postgresql/14/bin/psql -p 5432 citusdb
 psql (14.9 (Ubuntu 14.9-1.pgdg22.04+1), server 14.10 (Ubuntu 14.10-1.pgdg22.04+1))
 Type "help" for help.
 
 citusdb=#
 citusdb=# \dt
           List of relations
  Schema |  Name   | Type  |  Owner
 --------+---------+-------+----------
  public | dist_t  | table | postgres

cabecada

bloating system catalog to simulate slow systems


postgres@pg:~/udemy/15$ more db1/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_locks_per_transaction=512
allow_system_table_mods = on

cabecada

failover testing citus

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

export PATH=/opt/15/usr/local/bin:$PATH
port=5432

#coordinator
if [[ -d dbcr ]]

cabecada

using citus_create_restore_point() for pitr


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

export PATH=/opt/15/usr/local/bin:$PATH
port=5432

for i in db1 db2 db3

cabecada

postgres@pg:~/udemy/16$ psql
psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1))
Type "help" for help.

postgres=# drop table t;
DROP TABLE
postgres=# create table t(col1 text, col2 text);
CREATE TABLE
postgres=# insert into t select x::text, x::text from generate_series(1, 10) x;
INSERT 0 10