CHERTS

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_created_at_idx
    ON orders_with_partitions (created_at);

CHERTS

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

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

-- Show PostGIS version
SELECT PostGIS_version();

-- Create table
CREATE TABLE global_points (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    location geography(POINT,4326)
);

CHERTS

#/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

#!/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

CHERTS

curl https://storage.yandexcloud.net/yandexcloud-yc/install.sh | bash -s sudo -- -n

CHERTS

DO $$
DECLARE
  tab RECORD;
  schemaName VARCHAR := 'public';
BEGIN
  for tab in (SELECT t.relname::varchar AS table_name
                FROM pg_class t
                JOIN pg_namespace n ON n.oid = t.relnamespace
                WHERE t.relkind = 'r' and n.nspname::varchar = schemaName
                ORDER BY 1)

CHERTS

# native using psql
SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec

# native using shell + psql
echo "SELECT 'CREATE DATABASE mydb' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec" | psql

CHERTS

SET GROUP_CONCAT_MAX_LEN=131072;
SET @selects = NULL;
SELECT CONCAT('SELECT * FROM (\n', GROUP_CONCAT(single_select SEPARATOR ' UNION\n'), '\n ) Q ORDER BY Q.TABLE_ROWS DESC') AS sql_query
FROM (
    SELECT CONCAT(
        'SELECT "', 
        table_name, 
        '" AS TABLE_NAME, COUNT(1) AS TABLE_ROWS
        FROM `', 
        table_schema,