Skip to content

Instantly share code, notes, and snippets.

@cabecada
cabecada / default-config-postgres.sql
Created Sep 16, 2021 — forked from paydro/default-config-postgres.sql
A base setup for new self-managed postgres databases. See related guide (https://tightlycoupled.io/my-goto-postgres-configuration-for-web-services/). Also, please make sure to change all the passwords from `secret` to something suitable. !! Update !! see this gist for a config that works for self-managed and RDS databases: https://gist.github.co…
View default-config-postgres.sql
CREATE ROLE owner LOGIN ENCRYPTED PASSWORD 'secret' CONNECTION LIMIT 3;
ALTER ROLE owner SET statement_timeout = 20000;
ALTER ROLE owner SET lock_timeout = 3000;
ALTER ROLE owner SET idle_in_transaction_session_timeout = 3000; -- v9.6+
CREATE ROLE readwrite_users NOLOGIN;
CREATE ROLE readonly_users NOLOGIN;
CREATE DATABASE exampledb WITH OWNER owner ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
View clone_schema.sql
CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text)
RETURNS void AS
$BODY$
DECLARE
object text;
buffer text;
default_ text;
column_ text;
@cabecada
cabecada / gist:9e82947f29b9dafd0dafb6bfb6f17cf2
Created Dec 29, 2018 — forked from 3manuek/gist:0a6dec10cd796ec2e13f3f92eacf3642
Postgres sharding using FOREIGN DATA WRAPPERS and inheritance. [WIP]
View gist:9e82947f29b9dafd0dafb6bfb6f17cf2
# Sharding within Foreign Data Wrappers and Inheritance [WIP]
Previous sources:
https://www.depesz.com/2015/04/02/waiting-for-9-5-allow-foreign-tables-to-participate-in-inheritance/
http://snowman.net/slides/pgfdw_sharding.pdf
Postgres 10 next things:
https://wiki.postgresql.org/wiki/Built-in_Sharding
<logical replication>
View gist:31147aa03c3e72fdc4f7f12e657740dc
-- So as an example of using a log orientated approach in PostgreSQL,
-- let's write a simple blog application. We will want to be able to:
-- * Write and edit blog posts
-- * Publish revisions of posts for public viewing
-- * Delete posts
-- * Add or remove tags to posts
-- Let's start by creating a schema.
@cabecada
cabecada / proutprout_names_generator.sql
Created May 28, 2018 — forked from regilero/proutprout_names_generator.sql
hoity-toity french names generator in pure postgreSQL single query
View proutprout_names_generator.sql
SELECT *
FROM (
SELECT
(
SELECT concat('Marie-', string_agg(x,'-')) as name_first_female
FROM (
select start_arr[ 1 + ( (random() * 100)::int) % 32 ]
FROM
(
select '{Claude,Thérèse,Géraldine,Sylvie,Sophie,Solange,Vivianne,Cunégonde,Albertine,Charlotte,Caroline,Célestine,Bérangère,Sylvianne,Alphonsine,Claire,Françoise,Chantal,Geneviève,Christine,Louise,Adeline,Céleste,Angélique,Clothilde,Jeanne,Pierre,Valentine,Paule,Noëlle,Lise,Cécile}'::text[] as start_arr
View analyze_results.sql
select
test,
query::char(48),
instance,
--scale,
--clients,
round(mean_time::numeric, 4) mean_time,
round(stddev_time::numeric, 4) stddev_time,
round(((mean_time - mean_time_lag)::numeric / mean_time::numeric )*100, 1) as mean_time_diff,
round(((stddev_time - stddev_time_lag)::numeric / stddev_time::numeric )*100, 1) as stddev_time_diff
@cabecada
cabecada / vm-resize-hard-disk.md
Created Sep 7, 2017 — forked from christopher-hopper/vm-resize-hard-disk.md
Resize a Hard Disk for a Virtual Machine provisioned using Vagrant from a Linux base box to run using VirutalBox.
View vm-resize-hard-disk.md

Resize a Hard Disk for a Virtual Machine

Our Virtual Machines are provisioned using Vagrant from a Linux base box to run using VirutalBox. If the Hard Disk space runs out and you cannot remove files to free-up space, you can resize the Hard Disk using some VirtualBox and Linux commands.

Some assumptions

The following steps assume you've got a set-up like mine, where:

@cabecada
cabecada / postgres-cheatsheet.md
Created Aug 31, 2017 — forked from Kartones/postgres-cheatsheet.md
PostgreSQL command line cheatsheet
View postgres-cheatsheet.md

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h):

  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)
View mongodb_3.2.x_sharding.md

#MongoDB 3.2.x Sharding Sharding is used when the database is too large to run on a single server and you need to spread the load across multiple servers. The name itself refers to the breaking (sharding) of the data into seperate groups of data which will reside on different servers.

##Configuration Server Start the server on your server (myserver0)

mongod --configsvr --dbpath /data

On myserver1 start the shard giving the configuration server as the --configdb option

@cabecada
cabecada / gist:9c73fa47fdad31ce97d333315cda946b
Created Jan 13, 2017 — forked from Atem18/gist:4696071
Tutorial to seting up a django website in production.
View gist:9c73fa47fdad31ce97d333315cda946b

Set up Django, Nginx and Gunicorn in a Virtualenv controled by Supervisor

Steps with explanations to set up a server using:

  • Virtualenv
  • Virtualenvwrapper
  • Django
  • Gunicorn