Skip to content

Instantly share code, notes, and snippets.

View Vagrantfile
# -*- mode: ruby -*-
# vi: set ft=ruby :
Vagrant.configure("2") do |config|
config.vm.box = "centos/7"
config.vm.hostname = "centos7"
config.vm.provider "libvirt" do |libvirt|
libvirt.memory="1512"
libvirt.cpus=2
libvirt.disk_driver :cache => "none"
View Vagrantfile
# -*- mode: ruby -*-
# vi: set ft=ruby :
Vagrant.configure("2") do |config|
config.vm.box = "rockylinux/8"
config.vm.hostname = "rocky8"
config.vm.provider "libvirt" do |libvirt|
libvirt.memory = 512
libvirt.cpus = 2
libvirt.machine_virtual_size = 40
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;