cabecada

{
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": [
             "rds-db:connect"
         ],
         "Resource": [
             "arn:aws:rds-db:region:account-id:dbuser:dbi-resource-id/database-user-name"

cabecada

-- 
-- Befor using it you should enable dblink extension in database and allow user to run dblink_connect_u
-- You may need to change 'dbname=osm' to your db connection options in line 34.
-- CREATE EXTENSION dblink;
-- GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO user;
-- GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO user;
--

DROP FUNCTION IF EXISTS public.g_parsel(query text, table_to_chunk text, num_chunks integer);
CREATE OR REPLACE FUNCTION public.g_parsel(query text, table_to_chunk text, num_chunks integer default 2)

cabecada

t

pg_extension_name: citus pg_extension_version: 11.1-1 pg_readme_generated_at: 2023-01-30 17:35:52.80893+03 pg_readme_version: 0.5.5

Citus distributed database

cabecada

🗺🐘 1000+ PostgreSQL EXTENSIONs

This is a list of URLs to PostgreSQL EXTENSION repos, listed in alphabetical order of parent repo, with active forks listed under each parent.

⭐️ >= 10 stars
⭐️⭐️ >= 100 stars
⭐️⭐️⭐️ >= 1000 stars
Numbers of stars might not be up-to-date.

cabecada

1.1 Linux process management

• process scheduling
• interrupt handling
• signaling
• process prioritization
• process switching
• process state
• process memory

cabecada

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';

cabecada

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

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

cabecada

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

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