Skip to content

Instantly share code, notes, and snippets.

View cherts's full-sized avatar

Mikhail Grigorev cherts

155 followers · 0 following
View GitHub Profile
@cherts
cherts / clickhouse_generate_test_data.sql
Last active November 1, 2025 08:54
Generating test data for Clickhouse
-- Create ReplicatedMergeTree table
CREATE TABLE mydb.my_test_data ON CLUSTER '{cluster}'
(
event_id UUID,
user_id UInt32,
event_time DateTime,
event_type Enum('click' = 1, 'view' = 2, 'purchase' = 3),
amount Decimal(18, 2)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
@cherts
cherts / docker-clean.sh
Created September 3, 2025 07:05
Clean all docker components (containers, images, volumes)
#!/bin/bash
# vim: set sw=4 sts=4 et tw=80 :
_docker_list() {
echo "List of container:"
docker ps -a
echo "List of images:"
docker images ls
echo "List of volumes:"
docker volume ls
@cherts
cherts / psql_sequence.sql
Created May 29, 2025 15:14
PostgreSQL and sequence
# Create test table
CREATE TABLE mytable (
id bigint not null generated by default as identity primary key,
value1 varchar,
value2 varchar
);
# Add data (bad!)
INSERT INTO mytable(id, value1, value2) VALUES
(1,'Daniele','Teti'),
@cherts
cherts / postgres_fdw_examples.sql
Created May 28, 2025 09:23
PostgreSQL FDW Examples
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER IF NOT EXISTS my_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '1.2.3.4', dbname 'my_db', port '5432', sslmode 'disable', connect_timeout '10', application_name 'fdw');
CREATE USER MAPPING IF NOT EXISTS FOR my_user SERVER my_server OPTIONS (user 'my_user', password 'BigPa$$w0rd!');
IMPORT FOREIGN SCHEMA public LIMIT TO (table1,table2) FROM SERVER my_server INTO public;
SELECT * FROM table1 LIMIT 10;
SELECT * FROM table2 LIMIT 10;
@cherts
cherts / pg_update_batch_test.sql
Created April 29, 2025 10:59
CREATE TABLE test (
id BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
data_field VARCHAR(255) NOT NULL
);
INSERT INTO test(data_field) SELECT(i::text) FROM generate_series(1,10000) as t(i);
-- repeat several times
WITH candidate_rows AS (
SELECT id
@cherts
cherts / pg_save_load_via_copy_and_stdout_stdin.sh
Created April 24, 2025 14:57
[PostgreSQL] Save and load data via COPY and STDOUT/STDIN
# Prepare test data (table size: 1M rows, ~87MB)
psql -t -X postgres://user:[email protected]:5432/dbname -c "
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
age INTEGER NOT NULL
);
INSERT INTO customers(first_name,last_name,age) VALUES(CONCAT('FirstName',generate_series(1, 1000000)), CONCAT('LastName',round((random()*100)::integer,0)), round((random()*100)::integer,0));
"
@cherts
cherts / autovacuum_forecast.sql
Created March 6, 2025 09:41
PostgreSQL autovacuum forecast
WITH rel_set AS (
SELECT
oid
, CASE split_part(
split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2)
, ','
, 1
)
WHEN '' THEN NULL
ELSE
@cherts
cherts / snapd_cleanup_unused.sh
Created January 14, 2025 13:57
Remove all unused version of snap packages
#!/bin/sh
LANG=en_US.UTF-8 snap list --all | awk '/disabled/{print $1, $3}' |
while read pkg revision; do
sudo snap remove "$pkg" --revision="$revision"
done
journalctl --vacuum-size=100M
@cherts
cherts / pg_get_cluster_role.sql
Last active November 8, 2024 10:19
Determine whether the running PostgreSQL cluster is the primary one or not.
SELECT DISTINCT
CASE
WHEN b.sender=0 AND c.receiver=0 THEN
'standalone'
WHEN b.sender>0 AND c.receiver=0 THEN
'primary'
WHEN b.sender=0 AND c.receiver>0 THEN
'replica'
WHEN b.sender>0 AND c.receiver>0 THEN
'primary+replica'
@cherts
cherts / install_latest_pgscv.sh
Created September 27, 2024 11:07
Install latest pgSCV
wget -qO - $(OS=$(uname -s | tr '[:upper:]' '[:lower:]'); ARCH=$(uname -p | sed 's/x86_64/amd64/g'); wget https://api.github.com/repos/CHERTS/pgscv/releases/latest -qO - | grep -wo "https.*${OS}_${ARCH}.tar.g
z") | tar xzf - -C /tmp && \
mv /tmp/pgscv.yaml /etc 2>/dev/null && \
mv /tmp/pgscv.service /etc/systemd/system 2>/dev/null && \
mv /tmp/pgscv.default /etc/default/pgscv 2>/dev/null && \
mv /tmp/pgscv /usr/sbin 2>/dev/null && \
chown postgres:postgres /etc/pgscv.yaml 2>/dev/null && \
systemctl daemon-reload && \
systemctl enable pgscv --now