Skip to content

Instantly share code, notes, and snippets.

Avatar

Lukas Fittl lfittl

View GitHub Profile
View pg_autoctl.service
[Unit]
Description=Keeper for pg_auto_failover
After=network.target
[Service]
ExecStart=/usr/bin/pg_autoctl run --pgdata /path/to/pg/data
User=postgres
ProtectSystem=full
ProtectHome=true
CapabilityBoundingSet=
@lfittl
lfittl / .flowconfig
Created Dec 2, 2017
Place override in flow-typed/npm/react-apollo_vx.x.x.js and ignore shipped types using .flowconfig
View .flowconfig
[ignore]
.*node_modules/react-apollo/.*
View drop_all_tables.sql
DO LANGUAGE plpgsql $$
DECLARE
t record;
tables text[];
BEGIN
FOR t IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') LOOP
tables := array_append(tables, quote_ident(t.schemaname) || '.' || quote_ident(t.tablename));
END LOOP;
IF array_length(tables, 1) > 0 THEN
@lfittl
lfittl / postgres12.md
Last active Dec 11, 2018
Postgres 12 patches worth writing
View postgres12.md

Postgres Patches for 12

  • queryid in log_min_duration_statement
  • queryid generation in core instead of contrib/pg_stat_statements
  • pg_stat_plans in contrib (think of pg_stat_statements + auto_explain, possibly just store the EXPLAIN output as the text)
  • pg_stat_statements in core
  • pg_stat_statements should have idx_scan and seq_scan counters
  • changefeeds (aka logical decoding for realtime updates, instead of LISTEN/NOTIFY)
  • JSON logical decoder in core
  • COPY from URL / Make COPY extendable (so there can be an S3 handler)
@lfittl
lfittl / copy_from_client.rb
Last active Mar 24, 2017
Helper method to use Postgres' COPY with ActiveRecord
View copy_from_client.rb
# Put this in config/initializers/copy_from_client.rb
class CopyFromClientHelper
attr_reader :count
def initialize(conn, column_types)
@count = 0
@conn = conn
@column_types = column_types
end
@lfittl
lfittl / docker-compose.yml
Created Nov 7, 2016
Enabling pg_stat_statements in a Docker container
View docker-compose.yml
version: '2'
services:
db:
image: postgres:9.5
ports:
- "5432:5432"
volumes:
- /var/lib/postgresql/data
- ./contrib/postgres:/docker-entrypoint-initdb.d
@lfittl
lfittl / output.txt
Last active Sep 20, 2016
Using pg_query fingerprinting to prevent SQL injections
View output.txt
#my_orm_method("1")
1
#my_orm_method("'abc'")
abc
#my_orm_method("1 UNION SELECT * FROM passwords")
test.rb:8:in `exec_with_fingerprint': Invalid fingerprint, rejecting (RuntimeError)
from test.rb:16:in `my_orm_method'
from test.rb:23:in `<main>'
@lfittl
lfittl / analyze_all.sh
Last active Sep 17, 2016
Running ANALYZE (or other table-specific commands) across distributed tables in a Citus cluster
View analyze_all.sh
# Analyzes all distributed tables
export PGHOST=...
export PGUSER=citus
export PGPASSWORD=...
#export PGSSLMODE=require
export PGDATABASE=citus
export PARALLEL_FACTOR=4
psql -tA -F" " -c "SELECT logicalrelid::regclass||'_'||s.shardid, \
View fix_composite_key_insert.rb
# Place this in app/models/concerns/fix_composite_key_insert.rb
# Fix for https://github.com/composite-primary-keys/composite_primary_keys/issues/365
#
# Act as if the record has a single primary key column in some cases, in
# particular when we get called from _create_record with the RETURNING result:
# https://github.com/rails/rails/blob/master/activerecord/lib/active_record/persistence.rb#L560
module FixCompositeKeyInsert
extend ActiveSupport::Concern
@lfittl
lfittl / instructions.md
Last active Aug 29, 2016
pganalyze-collector installation instructions for other distributions
View instructions.md

First, we'll need to place a few files in the right location:

curl -o /usr/bin/pganalyze-collector https://github.com/pganalyze/collector/releases/download/v0.9.4/pganalyze-collector-linux-amd64
curl -o /etc/systemd/system/pganalyze-collector.service https://github.com/pganalyze/collector/blob/master/contrib/systemd/pganalyze-collector.service
curl -o /etc/pganalyze-collector.conf https://github.com/pganalyze/collector/blob/master/contrib/pganalyze-collector.conf

In addition, run the following commands: