Skip to content

Instantly share code, notes, and snippets.

View vamsi-equinix's full-sized avatar

Vamsi vamsi-equinix

2 followers · 1 following
  • equinix.com
  • eq
View GitHub Profile
@simondobner
simondobner / postgres.md
Last active December 19, 2022 11:10
Postgres notes

Locks, Blocks and Killing sessions

Find blocked and blocking sessions

select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query                 as blocked_query
from pg_stat_activity
@GBraL
GBraL / POSTGRES_alter_owner.sql
Last active August 28, 2017 07:52
POSTGRES - alter owner of all objects on the connected database
-- ALTER OWNER OF ALL OBJECTS ON THE CONNECTED DATABASE
DO $$DECLARE
owner varchar = 'vokuro';
r record;
BEGIN
FOR r IN
-- SCHEMAS
SELECT 'ALTER SCHEMA '|| schema_name ||' OWNER TO ' || owner || ';' AS DDL
FROM information_schema.schemata WHERE NOT schema_name IN ('pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1', 'pg_toast_temp_1')
UNION ALL
@inindev
inindev / postgresql_jsonb_crud.sql
Last active February 13, 2024 03:18 — forked from matheusoliveira/json_manipulator.sql
Simple PostgreSQL 9.4 functions to manipulate jsonb objects adapted from Matheus de Oliveira's json_manipulator.sql. https://gist.github.com/matheusoliveira/9488951 (Note: performance is not a concern for those functions)
/*
* derivative work of Matheus de Oliveira's json_manipulator.sql
* https://gist.github.com/matheusoliveira/9488951
*
* adapted to support postgresql 9.4 jsonb type
* no warranties or guarantees of any kind are implied or offered
*
* license is as Matheus conferred it on 4/9/2015:
* matheusoliveira commented on Apr 9
* @hannes-landeholm, I'd like to take credit if you share them
@smathieu
smathieu / postgres_lock.rb
Created January 21, 2015 22:14
Postgres Lock Monitoring
class Jobs::Cron::Metrics::Postgres < QueueClassicPlus::Base
@queue = :cron
lock!
def self.perform
sql = <<-SQL
SELECT date_part('epoch', now() - query_start) AS connection_age
FROM pg_catalog.pg_stat_activity
WHERE datname = '#{database}'
ORDER BY connection_age DESC NULLS LAST
@jirutka
jirutka / pg_change_db_owner.sh
Last active June 17, 2025 05:53
Some convenient scripts to manage ownerships and privileges in PostgreSQL.
#!/bin/sh
#
# The MIT License
#
# Copyright 2014-2017 Jakub Jirutka <jakub@jirutka.cz>.
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
@j8mathis
j8mathis / Postgres Log Parsing
Created May 12, 2014 15:10
Catch those postgres errors
#! /usr/bin/env python
import sys
import os
import time
import re
import smtplib
from datetime import time, datetime, timedelta
import time
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
to_email = 'someone@somecompany.com'
@matheusoliveira
matheusoliveira / json_manipulator.sql
Last active January 13, 2025 00:56
Simple PostgreSQL functions to manipulate json objects. (Note: performance is not a concern for those functions)
CREATE OR REPLACE FUNCTION public.json_append(data json, insert_data json)
RETURNS json
IMMUTABLE
LANGUAGE sql
AS $$
SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json
FROM (
SELECT * FROM json_each(data)
UNION ALL
SELECT * FROM json_each(insert_data)
@selenamarie
selenamarie / prod_postgres.md
Last active March 15, 2019 05:12
An Ideal Postgres Environment

Ideal Postgres environment

Documentation

  • Documented replication topology
  • Documented network topology
  • Documented interface topology - including users, passwords, connection estimates, load balancers, connection proxies
  • Documented procedure, schedule for failover and testing
  • Documented procedure, schedule for disaster recovery and testing
@robertsosinski
robertsosinski / postgresql.py
Last active June 25, 2020 10:32
DataDog script for collecting PostgreSQL stats
# Create the datadog user with select only permissions:
# CREATE USER datadog WITH PASSWORD '<complex_password>';
#
# Grant select permissions on a table or view that you want to monitor:
# GRANT SELECT ON <schema>.<table> TO datadog;
#
# Grant permissions for a specific column on a table or view that you want to monitor:
# GRANT SELECT (id, name) ON <schema>.<table> TO datadog;
#
# Let non-superusers look at pg_stat_activity in a read-only fashon.
@rgreenjr
rgreenjr / postgres_queries_and_commands.sql
Last active March 14, 2026 17:03
Useful PostgreSQL Queries and Commands
-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'