Skip to content

Instantly share code, notes, and snippets.

@cabecada
cabecada / gist:f522074040789151ac7f453c102039c9
Last active Aug 4, 2022
postgresql corruption zero pages index
View gist:f522074040789151ac7f453c102039c9
contains unexpected zero page at block 35651600
adjust=# select pg_relation_filepath('indexname');
pg_relation_filepath
--------------------------------------------------
pg_tblspc/5392705/PG_12_201909212/114783/5412650
ls -lrt pg_tblspc/5392705/PG_12_201909212/114783/5412650\.* | wc -l
473
there are 473 chunks of 1GB files for the index.
to calculate blocks we first see how many blocks are there in each chunk i.e
@cabecada
cabecada / gist:acff4e9a1cc2bc0154c4bce4dff37d55
Created Jun 14, 2022
pgbouncer haproxy external-check primary replica failover
View gist:acff4e9a1cc2bc0154c4bce4dff37d55
primary : 192.168.64.11
replica : 192.168.64.12
cat /etc/haproxy/haproxy.cfg
global
maxconn 100
stats socket ipv4@127.0.0.1:9999 level admin
stats socket /var/run/haproxy.sock mode 666 level admin
stats timeout 2m
external-check
View gist:5610f0923ed0fdebda6989ab2ed80392
postgres@demo:~/temp$ psql -p 5432 -h 127.0.0.1 -d postgres -U postgres -c 'select pg_is_in_recovery();'
pg_is_in_recovery
-------------------
f
(1 row)
postgres@demo:~/temp$ psql -p 5433 -h 127.0.0.1 -d postgres -U postgres -c 'select pg_is_in_recovery();'
pg_is_in_recovery
-------------------
t
View gist:481704cc152b36ee33dbed3ac412d5b8
root@demo:~# cat /opt/pgsqlchk1
#!/usr/bin/bash
export PGPASSWORD="1234"
VALUE=`timeout 5 /usr/bin/psql -t -d postgres -h 127.0.0.1 -U postgres -p 5432 -c 'select not pg_is_in_recovery()' 2>/dev/null`
rc=$?
if [ $rc -eq 0 ]
View gist:1dee213d19d4af1f279e3c472f778c3c
root@demo:~# cat /etc/haproxy/haproxy.cfg
global
maxconn 100
defaults
log global
mode tcp
retries 2
timeout client 30m
timeout connect 4s
View gist:e20648c1a699b7d563394c3e351bf1bd
cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/
apt-get install postgresql pgbouncer
sudo su - postgres
psql << EOF
CREATE ROLE pgbouncer LOGIN;
-- set a password for the user
\password pgbouncer
@cabecada
cabecada / gist:25137c7943b074e51ae9ed30d68b3e6c
Last active May 24, 2022
postgresql interrupt unkillable queries using ProcessInterrupts() via gdb
View gist:25137c7943b074e51ae9ed30d68b3e6c
# reference https://www.cybertec-postgresql.com/en/cancel-hanging-postgresql-query/
postgres@db1:~$ pg_config --libdir
/opt/postgresql/postgresql-14.3/lib
postgres@db1:~$ cat loop.c
#include "postgres.h"
#include "fmgr.h"
#include <unistd.h>
@cabecada
cabecada / gist:f0569848d30e1279068ae142d2cf448a
Created May 24, 2022
postgresql compile from source ... ubuntu arch64
View gist:f0569848d30e1279068ae142d2cf448a
1 apt-get install libpython3-dev
2 apt-get install libtcl-dev
3 apt-get install gdb
4 apt-get install linux-tools-common
9 apt-get install libipc-run-perl libipc-run3-perl
3 wget https://ftp.postgresql.org/pub/source/v14.3/postgresql-14.3.tar.bz2
4 sudo apt-get install gcc
5 apt-get install gcc libreadline-dev zlib1g-dev
6 perl -v
10 groupadd -g 54321 dba
View PostgreSQL-EXTENSIONs.md

🗺🐘 1000+ PostgreSQL EXTENSIONs

This is a list of URLs to PostgreSQL EXTENSION repos, listed in alphabetical order of parent repo, with active forks listed under each parent.

⭐️ >= 10 stars
⭐️⭐️ >= 100 stars
⭐️⭐️⭐️ >= 1000 stars
Numbers of stars might not be up-to-date.

View gist:636f3c8e48d198e88e1c71be56d4444b
root@ubuntu-linux-20-04-desktop:~# cat /etc/haproxy/haproxy.cfg
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
description PostgreSQL Database HAProxy Stats page
log 127.0.0.1 local0 info
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 3000