This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Show PostGIS version | |
SELECT PostGIS_version(); | |
-- Create table | |
CREATE TABLE global_points ( | |
id SERIAL PRIMARY KEY, | |
name VARCHAR(64), | |
location geography(POINT,4326) | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#/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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
curl https://storage.yandexcloud.net/yandexcloud-yc/install.sh | bash -s sudo -- -n |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH tbl AS ( | |
SELECT table_schema, table_name | |
FROM information_schema.tables | |
WHERE table_name NOT LIKE 'pg_%' AND table_schema NOT IN ('information_schema') | |
) | |
SELECT table_schema, table_name, | |
(xpath('/row/c/text()', query_to_xml(format('SELECT count(1) AS c FROM "%I"."%I"', table_schema, table_name), FALSE, TRUE, '')))[1]::text::bigint AS table_count | |
FROM tbl; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
git config --global core.excludesfile ~/.gitignore_global | |
cat <<EOF > ~/.gitignore_global | |
.DS_Store | |
.DS_Store? | |
._* | |
.Spotlight-V100 | |
.Trashes | |
ehthumbs.db | |
Thumbs.db | |
EOF |
NewerOlder