rgreenjr

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

ruckus

** Find commmonly accessed tables and their use of indexes:

SELECT relname,seq_tup_read,idx_tup_fetch,cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS idx_tup_pct FROM pg_stat_user_tables WHERE (idx_tup_fetch + seq_tup_read)>0 ORDER BY idx_tup_pct;

Returns output like:

       relname        | seq_tup_read | idx_tup_fetch |      idx_tup_pct       
----------------------+--------------+---------------+------------------------
 schema_migrations    |          817 |             0 | 0.00000000000000000000
 user_device_photos   |          349 |             0 | 0.00000000000000000000

juliandunn

Deep Dive: PostgreSQL on AWS

When loading data

• disable backups (backup_retention=0)
• disable multi-AZ and autovacuum
• pg_dump -Fc (compressed) and pg_restore -j (parallel)
• Increase maintenance_work_mem

IgorBerman

require "pghero/version"
require "active_record"
require "pghero/engine" if defined?(Rails)

module PgHero
  # hack for connection
  class Connection < ActiveRecord::Base
    establish_connection ENV["PGHERO_DATABASE_URL"] if ENV["PGHERO_DATABASE_URL"]
  end

marr75

with table_stats as (
select psut.relname,
  psut.n_live_tup,
  1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) as index_use_ratio
from pg_stat_user_tables psut
order by psut.n_live_tup desc
),
table_io as (
select psiut.relname,
  sum(psiut.heap_blks_read) as table_page_read,

peterforgacs

Running Windows 10 on AWS EC2

Downloading the image

Download the windows image you want.

AWS vmimport supported versions: Microsoft Windows 10 (Professional, Enterprise, Education) (US English) (64-bit only)

So Home wont work.

anvk

--- PSQL queries which also duplicated from https://github.com/anvk/AwesomePSQLList/blob/master/README.md
--- some of them taken from https://www.slideshare.net/alexeylesovsky/deep-dive-into-postgresql-statistics-54594192

-- I'm not an expert in PSQL. Just a developer who is trying to accumulate useful stat queries which could potentially explain problems in your Postgres DB.

------------
-- Basics --
------------

-- Get indexes of tables

checco

--
-- Read only 
--

-- Create a group
CREATE ROLE postgres_ro_group;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO postgres_ro_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO postgres_ro_group;

cmcconnell1

#!/usr/bin/env python3
# Overview:
#   Provides idempotent remote RDS PostgreSQL (application) role/user creation from python for use outside of CM modules.
#   Because PostgreSQL doesn't have something like 'CREATE ROLE IF NOT EXISTS' which would be nice.
#   ref: https://stackoverflow.com/questions/8546759/how-to-check-if-a-postgres-user-exists
# Requirements: 
#   Python3 and psycopg2 module 
# cmcc
import psycopg2

shokoe

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/snap/bin:/snap/bin
log="/var/log/aws_inspector/aws_inspector_export_rep.log"
template_arn='arn:aws:inspector:us-east-1:XXXXXXXXXXXX:target/xxxxxxxxxx/template/xxxxxxxxxx'
wait_sec='5400'

log_out(){
        (($verifymon)) &&\
                echo -e "`date +'%Y-%m-%d %H:%M:%S'` (pid $$) -- $1" >> $log ||\
                echo -e "`date +'%Y-%m-%d %H:%M:%S'` (pid $$) -- $1" | tee -a $log