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/


postgres@pg:~/citusdb/certs$
postgres@pg:~/citusdb/certs$ mkdir CA
postgres@pg:~/citusdb/certs$ cd CA

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