Skip to content

Instantly share code, notes, and snippets.

View borg-create.sh
#/bin/bash
# encrypted USB stick by uuid
UUID=a20ec820-de7d-40ec-ac29-4595a10c38e7
PASS=xxxxxxxx
EXCLUDE=$HOME/.config/borg/exclude.list
# helpers
info() { notify-send "$*"; }
trap 'echo $( date ) Backup interrupted >&2; disk_unmount; exit 2' INT TERM
@fljdin
fljdin / init.sql
Last active Jun 8, 2021
Sample demo for daterange blogpost
View init.sql
DROP TABLE IF EXISTS staff;
CREATE TABLE staff (
id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name varchar(50),
start date NOT NULL,
finish date,
period daterange GENERATED ALWAYS AS (daterange(start, finish, '[]')) STORED,
CHECK (start < finish OR finish IS NULL)
);
@fljdin
fljdin / is_public_holiday.sql
Last active Aug 6, 2020
ecrire-ses-tests-unitaires-en-sql
View is_public_holiday.sql
-- Type month_day
DROP TYPE IF EXISTS month_day CASCADE;
CREATE TYPE month_day AS (month int, day int);
-- Function easter_date(date)
CREATE OR REPLACE FUNCTION easter_date(year int)
RETURNS date LANGUAGE plpgsql
AS $$
DECLARE
g integer := year % 19;
View list_index.sql
SELECT i.indrelid::regclass AS "Table", i.indexrelid::regclass AS "Index",
pg_size_pretty(pg_relation_size(i.indexrelid)) AS "Taille de l'index"
FROM pg_index i JOIN (
SELECT indrelid, indclass, indkey, indexprs, indpred
FROM pg_index GROUP BY indrelid, indclass, indkey, indexprs, indpred
HAVING COUNT (*) > 1
) g ON i.indrelid = g.indrelid AND i.indclass = g.indclass AND i.indkey = g.indkey
AND COALESCE(i.indexprs::text,'x') = COALESCE(g.indexprs::text,'x')
AND COALESCE(i.indpred::text,'x') = COALESCE(g.indpred::text,'x')
ORDER BY pg_relation_size(i.indexrelid) DESC, i.indexrelid::regclass;
View testing.h
#ifndef __testing_h__
#define __testing_h__
#include <stdio.h>
#define ANSI_GREEN "\033[0;32m"
#define ANSI_RED "\033[0;31m"
#define ANSI_RESET "\033[0m"
#define log_passed(M,...) fprintf(stderr,\
View backup.sh
#!/bin/bash
echo -n "Starting backup... Are you sure ? y/(n) " ; read response
test ${response:-n} != "y" && exit 1
dotfiles=$(dirname $0)/dotfiles.txt
destination=$(dirname $0)/home
# create dotfiles archives or append new files to it
tar -czf $HOME/dotfiles.tar.gz -C $HOME --files-from=$dotfiles
View get_table_maintenance_info.sql
SELECT schemaname, relname, pg_size_pretty(pg_relation_size(relname::regclass)) relsize, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE greatest(last_autoanalyze,last_analyze) < now()-interval '5d'
AND pg_relation_size(relname::regclass) > 10e6;
View cache hit ratio.sql
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
pg_statio_user_tables;
View awk meminfo enhance.sh
awk '/Hugepagesize:/{p=$2}/ 0 /{next}/ kB$/{v[sprintf("%9d GB %-s",int($2/1024/1024),$0)]=$0;next}{h[$0]=$2}END{for(k in v) print k;for (k in h) print sprintf("%9d GB %-s",p*h[k]/1024/1024,k)}' /proc/meminfo|sort -nr|grep --color=auto -E "^|.*(Huge.*_[TF]|Mem).*:|"
@fljdin
fljdin / check_cluster.sh
Last active Jun 21, 2019
Perform health check on clusterware and associated databases
View check_cluster.sh
#! /usr/bin/env bash
#
# comment : perform health check on clusterware and associated databases
# deploy : chmod +x /usr/local/bin/check_cluster
export version="20170426.0930"
cif () { printf "[INFO]\t%s\n" "$1" ; }
ttl () { printf "\033[1;34m----\t%s\033[0m\n" "$1" ; }
cok () { printf "\033[1;32m[OK]\033[0m\t%s\n" "$1" ; }