Some snippets for use in anywhere

snippets.md

Postgres

Dump

PROJECT=project; pg_dump -h localhost -U $PROJECT -d $PROJECT -f /tmp/$PROJECT.sql -bcOv --column-inserts

Restore

PROJECT=project; psql -h localhost -U $PROJECT -d $PROJECT -f /tmp/$PROJECT.sql --echo-errors

Fix owner for objects

export PROJECT=project

sudo -u postgres psql -Aqt -d $PROJECT -c " \
    SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO $PROJECT;' \
    FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema') \
    ORDER BY schemaname, tablename;\
" > /tmp/pgfix.sql

sudo -u postgres psql -d $PROJECT < /tmp/pgfix.sql

sudo -u postgres psql -Aqt -d $PROJECT -c " \
    SELECT 'ALTER SEQUENCE '|| sequence_schema || '.' || sequence_name ||' OWNER TO $PROJECT;' \
    FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema') \
    ORDER BY sequence_schema, sequence_name; \
" > /tmp/pgfix.sql

sudo -u postgres psql -d $PROJECT < /tmp/pgfix.sql

sudo -u postgres psql -Aqt -d $PROJECT -c " \
    SELECT 'ALTER VIEW '|| table_schema || '.' || table_name ||' OWNER TO $PROJECT;' \
    FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema') \
    ORDER BY table_schema, table_name; \
" > /tmp/pgfix.sql

sudo -u postgres psql -d $PROJECT < /tmp/pgfix.sql

Fix sequences

sudo -u postgres vi /tmp/fix-sequences-starter.sql

SELECT 'SELECT SETVAL(' ||
       quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

sudo -u postgres psql -Aqt -f /tmp/fix-sequences-starter.sql -o /tmp/fix-sequences.sql
sudo -u postgres psql -f /tmp/fix-sequences.sql
sudo -u postgres rm /tmp/fix-sequences.sql

Regexp

uuid4

r'^[a-fA-F0-9-]{36}$'