Postgres

Terminate connections

I literally keep forgetting this command. So it’s here for all of us to benefit.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
  AND datname = 'database_name';

[Source: StackOverflow]

Copying databases

Sometimes we have a snapshot we want to use for some kind of testing/verification that either requires time to create/restore that slows me down. So I like copying the database instead (if I have the space) as I need a new fresh copy of the data.

CREATE DATABASE newdb WITH TEMPLATE olddb OWNER dbuser;

This will create a new database using the old database as a template. It requires that there are no connections to the old database before doing this step.

Remember you can use the terminate SQL above to kill any connections while creating the new database

[Source: Stackoverflow]

See also