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 gist:8406eaabdcd88106832c16aaf9725fdb
CREATE TABLE "nobelists" (
"id" serial primary key,
"name" text NOT NULL,
"birthdate" timestamp,
"deathdate" timestamp,
"birth_country" text,
"birth_state" text,
"birth_city" text,
"url" text,
"discipline" text,
View gist:0c44f1eab115d406d7876898c76b9c75
publisher pg13
postgres@db:~/playground/pg13$ createdb pagila
postgres@db:~/playground/pg13$ psql -p 5444 pagila < pagila-0.10.1/pagila-schema.sql 2>/dev/null >/dev/null
postgres@db:~/playground/pg13$ psql -p 5444 pagila < pagila-0.10.1/pagila-data.sql 2>/dev/null >/dev/null
postgres@db:~/playground/pg13$ psql -p 5444 pagila -c 'CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES;'
ERROR: publication "pglogical_rep01" already exists
postgres@db:~/playground/pg13$ psql -p 5444 pagila -c 'drop publication pglogical_rep01;'
DROP PUBLICATION
postgres@db:~/playground/pg13$ psql -p 5444 pagila -c 'CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES;'
CREATE PUBLICATION
@cabecada
cabecada / log_exception_to_table
Last active Aug 6, 2021
log_exception_to_table
View log_exception_to_table
postgres=# truncate table t;
TRUNCATE TABLE
postgres=# truncate table error_log_table;
TRUNCATE TABLE
postgres=#
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
@cabecada
cabecada / gist:21ffdf41a9da7945c275b82ff1733a6f
Last active Aug 5, 2021
postgresql_predicate_using_immutable_functions
View gist:21ffdf41a9da7945c275b82ff1733a6f
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# drop table t cascade;
DROP TABLE
test=#
test=# create table t(id int);
CREATE TABLE
test=# insert into t select x from generate_series(1, 100000) x;
INSERT 0 100000
test=# create or replace function txt_to_int(text) returns int as $$
@cabecada
cabecada / table_delete_using_oid_from_pg_class_restore
Last active Jul 24, 2021
table_delete_using_oid_from_pg_class_restore
View table_delete_using_oid_from_pg_class_restore
postgres@db:~/playground/demo$ pg_ctl -D data -l logfile start
waiting for server to start.... done
server started
postgres@db:~/github$ cd pagila
postgres@db:~/github/pagila$ ls -lrt
total 8292
-rw-rw-r-- 1 postgres postgres 5304 Jul 7 18:21 README.md
-rw-rw-r-- 1 postgres postgres 1100 Jul 7 18:21 LICENSE.txt
-rw-rw-r-- 1 postgres postgres 3034559 Jul 7 18:21 pagila-data.sql
-rw-rw-r-- 1 postgres postgres 52458 Jul 7 18:21 pagila-schema.sql
@cabecada
cabecada / postgres_composite_index
Created Jul 18, 2021
postgres_composite_index
View postgres_composite_index
\d+ dates
Table "public.dates"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------------+---------+-----------+----------+-----------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('dates_id_seq'::regclass) | plain | | |
date | date | | | | plain | | |
epoch | bigint | | | | plain | | |
day_suffix | text | | | | extended | pglz | |
day_name | text | | | |
@cabecada
cabecada / gist:cbc7506ba9ec0ae024d3284db57ba746
Created Jul 13, 2021
logical_replication_to_diff_dbs_via_schemas
View gist:cbc7506ba9ec0ae024d3284db57ba746
setup:
original server has schema db1,db2
goal: create new servers db1 and db2 and move over schema db1 to server db1 and schema db2 to server db2
we make use of logical replication to publish INS,UPD,DEL changes from original server to respective schema servers.
https://www.postgresql.org/docs/current/logical-replication-restrictions.html
https://www.postgresql.org/docs/current/logical-replication.html
server mainserver db1server db2server
schema db1 -> db1
View reattach_replica_to_primary.txt
##create a db
initdb -D primary
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
View gist:f9d6f07857ba4041223667e9abe40b63
cat test_part.sql
drop extension if exists pg_partman;
drop schema if exists partman cascade;
drop table if exists public.t cascade;
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
create table t(id int, col1 timestamp, primary key(id, col1)) partition by range(col1);
SELECT partman.create_parent( p_parent_table => 'public.t',
p_control => 'col1',