CHERTS’s gists

CHERTS / pg_change_owner.sql

Last active September 2, 2024 14:17

How to change owner of PostgreSQL database/schema/table/sequence/views/functions/types?

	DO $$
	DECLARE
	sch RECORD;
	tab RECORD;
	seq RECORD;
	viw RECORD;
	mat RECORD;
	fun RECORD;
	ctype RECORD;
	v_schema_exclude text[] := '{pg_catalog,information_schema,monitor,repack}';

CHERTS / psaux.bash

Created July 29, 2024 13:32

ps aux written entirely in bash without ever forking

	#!/bin/bash

	# ps aux written entirely in bash without ever forking
	# Author: Isabella Bosia
	# Github: https://github.com/izabera/ps/tree/develop

	#
	# so initially i was hoping you could get everything from /proc/<pid>/status
	# because it's easy to parse (in most cases) but apparently you can't get
	# things like the cpu% :(

CHERTS / create_publication_and_slot.sql

Created July 25, 2024 13:47

Create logical replication and test

	-- Create publication
	CREATE PUBLICATION cdc;

	-- Create slot
	SELECT pg_create_logical_replication_slot('test_slot_v1', 'pgoutput');

	-- Create example table
	CREATE TABLE replication_test_v1
	(
	id integer NOT NULL PRIMARY KEY,

CHERTS / debezium_checking_capture_data.sql

Created July 25, 2024 13:29

Checking capturing data change use logical slot

	-- Create slot
	SELECT pg_create_logical_replication_slot('test_slot_v1', 'pgoutput');

	-- Show publication name
	SELECT pubname FROM pg_publication_tables GROUP BY pubname;

	-- Peak changes (does not consume changes)
	SELECT pg_logical_slot_peek_binary_changes('test_slot_v1', NULL, NULL, 'publication_names', 'dbz_publication', 'proto_version', '1');

	-- Get changes (consumes changes)

CHERTS / partitioned_table.sql

Last active July 10, 2024 06:27

Examples of transfer data to partitioned table in PostgreSQL

	CREATE TABLE IF NOT EXISTS orders_with_partitions (
	id uuid not null,
	processed_at timestamp with time zone not null,
	created_at timestamp with time zone not null default CURRENT_TIMESTAMP,
	data. jsonb not null
	) PARTITION BY RANGE (created_at);

	CREATE INDEX IF NOT EXISTS orders_with_partitions_order_id_idx
	ON orders_with_partitions (order_id);

CHERTS / product_and_order.sql

Created July 10, 2024 06:15

Product and orders examples database using PostgreSQL

	CREATE TYPE products_unit AS ENUM ('Килограмм', 'Грамм', 'Литр', 'Метр', 'Пара', 'Штука');

	CREATE TABLE IF NOT EXISTS products (
	product_no bigint PRIMARY KEY,
	price numeric,
	unit products_unit,
	active bool,
	name text
	);

CHERTS / debezium_heartbeat_table.sql

Last active June 20, 2024 12:10

Create Debezium heartbeat table in PostgreSQL

	CREATE SCHEMA debezium;
	CREATE TABLE debezium.debezium_signal(id VARCHAR(42) PRIMARY KEY, type VARCHAR(32) NOT NULL, data VARCHAR(2048) NULL);
	CREATE TABLE debezium.pg_heartbeat(id SERIAL, last_update TIMESTAMP DEFAULT current_timestamp, PRIMARY KEY (id));
	--INSERT INTO debezium.pg_heartbeat(id) VALUES (0);
	GRANT USAGE ON SCHEMA debezium TO debezium_dwh;
	GRANT SELECT, INSERT, UPDATE, DELETE ON debezium.debezium_signal TO debezium_dwh;
	GRANT SELECT, INSERT, UPDATE, DELETE ON debezium.pg_heartbeat TO debezium_dwh;
	ALTER PUBLICATION dbz_publication ADD TABLE debezium.debezium_signal;
	ALTER PUBLICATION dbz_publication ADD TABLE debezium.pg_heartbeat;
	ALTER TABLE debezium.debezium_signal REPLICA IDENTITY FULL;

CHERTS / test_postgis.sql

Created June 5, 2024 14:26

Simple test for PostGIS extension for PostgreSQL

CHERTS / get_root_ca_percona_everest.sh

Created May 30, 2024 08:07

Get root ca for Percona Everest (PostgreSQL)

	#/bin/bash

	EVEREST_ENV=prod

	mkdir ~/.postgresql
	touch ~/.postgresql/root.crt
	kubectl get secrets/pgo-root-cacert -n ${EVEREST_ENV} -o jsonpath="{.data['root\.crt']}" \| base64 -d >> ~/.postgresql/root.crt

	# psql "host=127.0.0.1 port=5432 dbname=postgres user=postgres password=XXXXXX sslmode=prefer"

CHERTS / xtrabackup_init_replication.sh

Last active May 28, 2024 14:26

Init MySQL replication after xtrabackup prepare (support gtid with multiline)

	#!/bin/bash

	MYSQL_MASTER_HOST=X.X.X.X
	MYSQL_REPL_USER_NAME="repl"
	MYSQL_REPL_USER_PASSWORD="bigpassword"
	XTRABACKUP_INFO=/mnt/backup/xtrabackup_info
	XTRABACKUP_BINLOG_INFO=/mnt/backup/xtrabackup_binlog_info
	USE_AUTOPOSITION=1

	if [ ! -f "${XTRABACKUP_INFO}" ]; then

Mikhail Grigorev CHERTS