Get all of the tables linked by a Foreign Key

No Comments

Here is a handy little PostgreSQL query that will list all of the tables linked by a foreign key to the table YOUR_TABLE.
The output will include the keys on each side.
This can be very useful if you want to build up a map of your database through the relationships between the tables.

select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname as col
from pg_attribute af,
pg_attribute a,
(select conrelid,confrelid,conkey[i] as conkey,
confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey, generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype = ‘f’) ss) ss2
where af.attnum = confkey and af.attrelid = confrelid and a.attnum = conkey and a.attrelid = conrelid AND (conrelid::regclass = ‘YOUR_TABLE’::regclass);

Categories: PostgreSQL

How to kill all sessions in Postgres so that they stay dead.

No Comments
Postgres Connections

Have you ever tried to drop or rename a postgres database or even create another database from one as a template, only to be confronted by the dreaded message:

postgres=# create database test with template a_database;
ERROR: source database “a_database” is being accessed by other users
DETAIL: There are 40 other sessions using the database.

So, we kill those sessions off with something like the below SQL that will kill all sessions that are connected to that database:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()pg_stat_activity.datname = ‘TARGET_DB’
AND pg_stat_activity.datname = ‘A_DATABASE’;

Job done, now we can create our database… But wait!

postgres=# create database test with template a_database;
ERROR: source database “a_database” is being accessed by other users
DETAIL: There are 40 other sessions using the database.

Someone has beaten us too it and reconnected!

If you are like me, the only logical solution is to try to do the above statements really really fast. Unfortunately, I normally lose, so I need a way to stop sessions from reconnecting to the database that doesn’t involve changing config files etc.

Fortunately, the simple solution is to disallow all connections to that database and we can do it with this line:

update pg_database set datallowconn = false where datname = ‘a_database’;

Once you are finished, you just need to set it back to True and the database will be open for connections again.

Categories: PostgreSQL