Postgres - mwicat/personal GitHub Wiki

convert table to inserts

pg_dump --table=export_table --data-only --column-inserts my_database > data.sql

Dump / export / import

Dump single database

pg_dump dbname > dbdump.sql

restore:

psql dbname < dbdump.sql

restore but stop on error:

psql --set ON_ERROR_STOP=on dbname < dbdump.sql

Dump all databases

pg_dumpall < alldbdump.sql

restore:

psql -f alldbdump.sql postgres

psycopg2

import psycopg2
import psycopg2.extras
conn = psycopg2.connect("dbname=db user=postgres password=pass host=localhost port=5433")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

Estimated number of records

SELECT pg_size_pretty(cast(reltuples as int)) FROM pg_class WHERE relname='table';

Last vacuum / analyse

select relname, last_vacuum, last_analyze from pg_stat_all_tables where schemaname = 'public'

Index ownership

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

List procedure source

select prosrc from pg_proc where proname = 'import_click';

List procedures with reference to...

SELECT  proname
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'namespace' and prosrc like '%proc%';

List procedures from namespace

SELECT  proname, prosrc
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      pronamespace = n.oid
WHERE   nspname = 'public';

Reload without restart

pg_ctl reload -D /dane/postgres/data

pg_ctlcluster 9.0 main reload

Testing

16B * 1000000 = 16MB

create table test as select '0123456789ABCDE' from generate_series(1,1000000);

Change default schema

SET search_path to new_schema;

Get function source

select proname, prosrc from pg_proc where proname='myproc';

Show config option

SHOW option

Move data between dbs

source: copy (select * from tbl limit 1) to stdout;

dest: copy tbl from stdin;

Current activity

SELECT datname,procpid,current_query FROM pg_stat_activity;

No folding

PAGER='less -S -F' psql

http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/

Locking

Show what locks what

   select 
     pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
     pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start, 
     age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid 
   from pg_stat_activity,pg_locks left 
     outer join pg_class on (pg_locks.relation = pg_class.oid)  
   where pg_locks.pid=pg_stat_activity.procpid order by query_start;

Show locks on a particular relation

SELECT
relname as blocked_on,
st.procpid as blocking_pid,
st.usename as blocking_user,
substr(st.current_query, 0, 50) as blocking_query,
st.query_start as blocking_start,
pgl.mode as blocking_lock_type

FROM 
pg_stat_activity st, 
pg_locks pgl, 
pg_class

WHERE 
pgl.mode = 'AccessExclusiveLock'
AND st.procpid = pgl.pid
AND relname = 'tevent_log'

ORDER BY relname;

Sizes

SELECT pg_size_pretty(pg_relation_size('ranking.tsummary'));

pg_size_pretty(pg_total_relation_size('ranking.tsummary'));

Top 30 sizes

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 30;

Tuning up DB import

Maybe use pg_bulkload?

wal_level = minimal
archive_mode = off
max_wal_senders = 0

synchronous_commit=off

maintenance_work_mem = 1GB
checkpoint_segments = 1000
  • Disable triggers: ALTER TABLE tblname DISABLE TRIGGER USER
  • Drop Indexes: DROP INDEX myidx
  • After import: ANALYZE

Replication

Reload config

SELECT pg_reload_conf(); pg_ctl reload -D data_dir/

Show valid indices

select
    t.relname as table_name,
    i.relname as index_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and ix.indisvalid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and t.relname like 'tsummary'
group by
    t.relname, i.relname
order by
    t.relname,
    i.relname;

Blocking queries

SELECT
bl.procpid as blocked_pid,
bl.usename as blocked_user,
bl.current_query as blocked_query,
bl.query_start as blocked_start,
relname as blocked_on ,
lq.procpid as blocking_pid,
lq.usename as blocking_user,
lq.current_query as blocking_query,
lq.query_start as blocking_start,
pgl2.mode as blocking_lock_type
FROM pg_stat_activity bl, pg_locks pgl1,
pg_stat_activity lq, pg_locks pgl2, pg_class
WHERE bl.procpid = pgl1.pid
AND not pgl1.granted
AND pg_class.oid = pgl1.relation
AND pgl2.relation = pgl1.relation
AND pgl2.granted
AND lq.procpid = pgl2.pid;

Testing

begin work;
lock table traffic in EXCLUSIVE mode;
select pg_sleep(300);
commit work;

Settings for populate

wal_level = minimal
wal_buffers = 16MB
archive_mode = off
max_wal_senders = 0

checkpoint_timeout=1800s
archive_command=''

fsync=off
synchronous_commit=off

maintenance_work_mem=1GB
checkpoint_segments=256

autovacuum=off

sudo sysctl -w kernel.shmmax=1000000000
sudo sysctl -w kernel.shmall=1000000000

include 'populate.conf'

Settings for performance

shared_buffers = 8GB
temp_buffers = 100MB
work_mem = 200MB
maintenance_work_mem = 200MB
max_stack_depth = 5MB

bgwriter_delay = 150ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0

enable_seqscan = off

effective_cache_size = 20GB

include 'performance.conf'

Dump part of table

BEGIN;
CREATE TEMP TABLE tmp AS SELECT * FROM country;
COPY tmp TO '/dane/country.copy';
ROLLBACK;

Get COPY section from sql

zcat backup-201302280433.sql.gz | awk '/^COPY rewrite_map/, /^\\\\.$/ {print; seen=1} seen && /^\\\\.$/ {exit}'

Get until

zcat backup-201302280433.sql.gz | awk -F '\t' '$2 ~ /^2013-03-12 12/ {exit} {print}'

Disable output formatting

psql -At

\a
\t

set record separator: \pset recordsep

Manual pg_ctlcluster

/usr/lib/postgresql/9.0/bin/pg_ctl start -D /dane/postgres/data -s -o '-c config_file="/etc/postgresql/9.0/main/postgresql.conf"'

Tunneling

socat

socat tcp-l:5433,reuseaddr,fork EXEC:'ssh host socat STDIO UNIX-CONNECT\:/var/run/postgresql/.s.PGSQL.5432'

nc

socat tcp-l:5433,reuseaddr,fork EXEC:'ssh host nc -U /var/run/postgresql/.s.PGSQL.5432'

sqltun

su postgres -c "socat STDIO unix-connect:/var/run/postgresql/.s.PGSQL.5432"

socat tcp-l:5433,reuseaddr,fork EXEC:'ssh bc-wtf1 ./sqltun'

Test function

CREATE OR REPLACE FUNCTION test_fun() RETURNS void AS $$
declare
begin
raise warning 'test output';
end;
$$ LANGUAGE plpgsql IMMUTABLE;
select * from test_fun();
DROP FUNCTION test_fun();

DB diff

while read t0 t1 pkey; do echo "select * from (select * from $t0 except select * from $t1) q1 join (select * from $t1 except select * from $t0) q2 using ($pkey)"; done