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

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

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

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

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