cabecada

postgres@ubuntu:/mnt/VHD/consistent$ cat pg_consistent.c
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

PG_MODULE_MAGIC;

// --- MurmurHash3 Engine ---
static uint32_t murmur3_32(const char *key, int len, uint32_t seed) {
    uint32_t h1 = seed;

cabecada

this is just for fun.
we corrupt some page of a table that is heavily bloated and autovaccum has not yet run. now the bloat is enough for autovacuum to see the threshold breach
and trigger its run on the bloated table. it runs, then it finds the corrupted page and it fails silently. then it sleeps but it finds the 
same table again for threshold breach and runs it again only to fail again. this goes on forever, monitoring shows no new bloat, but the table 
does not have enough bloat to trouble other queries. (maybe a cold table). but then, txid starts growing and we now see
a txid exhaustion limit approaching. it triggers forced autovaccum, but still it keeps growing. 

postgres@ubuntu:/mnt/VHD$ pg_ctl -D db1 -l logfile start
waiting for server to start.... done
server started

cabecada

testing the utility on https://github.com/wublabdubdub/PDU-PostgresqlDataUnloader

--setup archiving on disk for now
postgres@ubuntu:/tmp$ grep '^archive_command' db1/postgresql.conf
archive_command = 'test ! -f /tmp/archive/%f && cp %p /tmp/archive/%f'          # command to use to archive a WAL file
postgres@ubuntu:/tmp$ pg_ctl -D db1 -l logfile start
waiting for server to start.... done
server started

--create a simple table with 100000 records 

cabecada

https://medium.com/@pranavt84/postgresql-page-structure-a-deep-dive-e82094a613de

seek= how far you go ahead in the output file

skip= how far you go ahead in the input file

count= how many segments you copy (can be set via bs=)

say you have 2 16 byte files like so:

cabecada

postgres=# set client_min_messages TO debug1;

SET

postgres=# create table t(col1 int primary key);

DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"

DEBUG: building index "t_pkey" on table "t" serially

cabecada

sudo apt-get -y -q install libipc-run-perl lcov build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache pkg-config libicu-dev


sudo mkdir /opt/postgresql/17
sudo chown -R postgres:postgres /opt/postgresql/17
cd postgres
./configure --prefix=/opt/postgresql/17 --enable-debug --enable-cassert --enable-tap-tests --enable-coverage CFLAGS="-ggdb3 -O0"
make -j4 install

cabecada

https://jumpcloud.com/blog/how-to-upgrade-ubuntu-20-04-to-ubuntu-22-04
https://askubuntu.com/questions/1098480/attempting-to-upgrade-from-ubuntu-server-16-04-to-18
https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/13273
https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/
https://github.com/ardentperf/glibc-unicode-sorting


2.27 > 2.31 
index corruption

cabecada

The Ultimate PostgreSQL Guide for Software Engineers

Table of Contents

• The Ultimate PostgreSQL Guide for Software Engineers
  • Table of Contents
  • Introduction to PostgreSQL
    • What is PostgreSQL?
    • Why PostgreSQL Stands Out
    • PostgreSQL vs. Other Database Systems
    • When to Choose PostgreSQL
• Understanding Relational Database Concepts

cabecada

https://jumpcloud.com/blog/how-to-upgrade-ubuntu-20-04-to-ubuntu-22-04
https://askubuntu.com/questions/1098480/attempting-to-upgrade-from-ubuntu-server-16-04-to-18
https://gitlab.com/gitlab-com/gl-infra/production-engineering/-/issues/13273
https://aws.amazon.com/blogs/database/manage-collation-changes-in-postgresql-on-amazon-aurora-and-amazon-rds/

https://www.youtube.com/watch?v=0E6O-V8Jato
Sorting Out glibc Collation Challenges: Joe Conway - PGCon 2023


glibc upgrades from 2.27 to 2.31, which changes the ordering

cabecada

https://www.enterprisedb.com/blog/partitioning-large-table-without-long-running-lock

create table orig_table
( id int generated always as identity not null,
  data float default random()
);

create index orig_data_index on orig_table(data);
create index orig_id_index on orig_table(id);