cabecada

postgres=# create table t(col1 int primary key, col2 int, col3 int);
CREATE TABLE
postgres=# create table r(col4 int primary key, col1 int references t(col1) on update cascade on delete cascade);
CREATE TABLE
postgres=# insert into t select x,x,x from generate_series(1, 1000000) x;
insert into r select col1, 1+ col1 % 10 from t;
INSERT 0 1000000
INSERT 0 1000000
postgres=# create table to_be_deleted as select col1, null::int as col2 from t where col2 % 9 = 3;
SELECT 111111

cabecada

This recipe is a work in progress and has never been run as-is.

• timeouts are in ms
• lock timeout: in postgres, when a statement that wants a restrictive lock waits on another lock, other statements that want locks can't jump the queue. so even though the statement that is waiting might only take a very short amount of time, when it starts running, while it is waiting no other statements can begin. So we set the lock timeout pretty low and retry if we don't get it.
• statement timeout: we set a short statement timeout before statements which do lock and which we expect to take a short amount of time, just in case something about our assumptions/understanding is wrong and the statement ends up taking a long time. if this happens the statement will bail early without causing harm, and we can investigate what is wrong with

cabecada

https://github.com/pgbouncer/pgbouncer/issues/982
https://www.pgbouncer.org/config.html


client -> pgbouncer(fe postgres server crt on 172.x.x.x) -> pgbouncer (be pgbouncer client crt) -> postgres (on 127.x.x.x)

  519  openssl ecparam -name prime256v1 -genkey -noout -out ca.key
  520  openssl req -new -x509 -sha256 -key ca.key -out ca.crt -subj "/CN=pg.mshome.net"
  522  openssl ecparam -name prime256v1 -genkey -noout -out server.key
  

cabecada

https://blog.dalibo.com/2022/09/19/psycopg-pipeline-mode.html

https://www.psycopg.org/psycopg3/docs/api/pq.html

conn.pgconn.trace(sys.stderr.fileno())
conn.pgconn.set_trace_flags(pq.Trace.SUPPRESS_TIMESTAMPS | pq.Trace.REGRESS_MODE)
conn.execute("select now()")
F       13      Parse    "" "BEGIN" 0
F       14      Bind     "" "" 0 0 1 0
F       6       Describe         P ""

cabecada

 ./configure --prefix /opt/17/usr/local --enable-tap-tests --with-perl --with-python --with-openssl --enable-dtrace --enable-debug --enable-cassert CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG -DBTREE_BUILD_STATS -DWAL_DEBUG"
 make 
 make install
 
 export PATH=/opt/17/usr/local/bin:$PATH
 
 select name,setting from pg_settings where name ~ 'trace.*locks';
      name       | setting
-----------------+---------
 trace_locks     | on

cabecada

{
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": [
             "rds-db:connect"
         ],
         "Resource": [
             "arn:aws:rds-db:region:account-id:dbuser:dbi-resource-id/database-user-name"

cabecada

Accomplished System Architect with over 13 years of hands-on experience managing large-scale databases, including those of 10s of 60TB scale. Proficient in designing and implementing robust monitoring and logging solutions to ensure optimal performance and reliability. Demonstrated expertise in building highly scalable architectures capable of handling substantial data volumes while maintaining seamless operations. Solid experience in facilitating ease of migration, upgrades, and incident management processes, ensuring minimal disruption and downtime. Adept at generating insightful reports to provide stakeholders with comprehensive visibility into system health and performance metrics. Proven track record of optimizing database environments for maximum efficiency and resilience in dynamic business landscapes.

Skills:

Systems Architecture               (Generl architecture meeting and discussions, making POCs in testlabs)  
Reliability Engineering            (uptime, fault injection and tolerance, upgrades, 

cabecada

postgres@pg:~/poc/patroni$ git status
On branch master
Your branch is up to date with 'origin/master'.

Changes not staged for commit:
  (use "git add <file>..." to update what will be committed)
  (use "git restore <file>..." to discard changes in working directory)
        modified:   Dockerfile.citus
        modified:   docker-compose-citus.yml
        modified:   postgres0.yml

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