Monthly Archives: June 2016

Use Postgres to check for missing foreign keys – psql

select Distinct column_name, t1.tablename FROM information_schema.columns, (select * from pg_tables where schemaname='public' AND tablename = 'products') t1 WHERE table_name = 'projects' and column_name like '%_id' and column_name not in (

(SELECT
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column
FROM
pg_constraint o LEFT JOIN pg_class c ON c.oid = o.conrelid
LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r') AND m.relname = 'products'))

postgres doesn’t start after restart. postmaster.pid may be blocking startup.

psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/tmp/.s.PGSQL.5432”?

$ rm /usr/local/var/postgres/postmaster.pid

$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Note: the path to postgres may be different.