cabecada

-- 
-- Befor using it you should enable dblink extension in database and allow user to run dblink_connect_u
-- You may need to change 'dbname=osm' to your db connection options in line 34.
-- CREATE EXTENSION dblink;
-- GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO user;
-- GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO user;
--

DROP FUNCTION IF EXISTS public.g_parsel(query text, table_to_chunk text, num_chunks integer);
CREATE OR REPLACE FUNCTION public.g_parsel(query text, table_to_chunk text, num_chunks integer default 2)

cabecada

https://itecnote.com/tecnote/postgresql-can-a-postgres-commit-exist-in-procedure-that-has-an-exception-block/

https://github.com/postgres/postgres/blob/REL_14_STABLE/src/pl/plpgsql/src/expected/plpgsql_transaction.out


 There are parts of the procedure where I might want to commit my work so-far so that it won't be rolled back if an exceptions ensues.

I want to have an exception handling block at the end of the procedure where I catch the exception 
and insert the information from the exception into a logging table.

cabecada

consistent hashing using fnv and murmur3
https://github.com/flier/pyfasthash
https://github.com/amutu/fnvhash

cabecada

citusdb=# select * from pg_timezone_names limit 1;
    name    | abbrev | utc_offset | is_dst
------------+--------+------------+--------
 Etc/GMT-12 | +12    | 12:00:00   | f
(1 row)

citusdb=# select current_timestamp at time zone 'UTC';
         timezone
---------------------------
 2023-11-20 09:59:05.33791

cabecada

https://stackoverflow.com/questions/33504982/postgresql-concurrently-change-column-type-from-int-to-bigint
https://tech.coffeemeetsbagel.com/schema-migration-from-int-to-bigint-on-a-massive-table-in-postgres-aabb835c3b84
http://zemanta.github.io/2021/08/25/column-migration-from-int-to-bigint-in-postgresql/
https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/postgres-tips-how-to-convert-2-billion-rows-to-bigint-with-citus/ba-p/1490128
https://making.lyst.com/2020/05/26/altering-a-postgres-column-with-minimal-downtime/

alter table my_table add column new_id bigint;

begin; update my_table set new_id = id where id between 0 and 100000; commit;
begin; update my_table set new_id = id where id between 100001 and 200000; commit;

cabecada

#!/usr/bin/env python

#https://github.com/psycopg/psycopg2/issues/261

import psycopg2

ISOLEVEL = psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT

import time

cabecada

-- port 5432
drop publication db1 ;
drop subscription db12;
drop subscription db13;
drop table t;
create table t(id int primary key);
create publication db1 for all tables WITH ( PUBLISH = 'insert,update,delete,truncate' );
CREATE SUBSCRIPTION db12 connection 'dbname=postgres port=5433 host=localhost user=postgres' publication db2 WITH (origin = none, copy_data =
 off);
CREATE SUBSCRIPTION db13 connection 'dbname=postgres port=5434 host=localhost user=postgres' publication db3 WITH (origin = none, copy_data =

cabecada

    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

cabecada

why pg_repack will not do online data migration 
https://github.com/reorg/pg_repack/pull/162

cabecada

postgres@pg:~/citusdb$ cat setup.sh
#!/bin/bash

export PATH=/usr/lib/postgresql/14/bin:$PATH
port=5432
if [[ -d dbcr ]]
then
        pg_ctl -D dbcr stop
        rm -rf dbcr*
fi