Copy/Duplicate an entire Postgres database on the same server.

Stolen from TablePlus Blog, Cool product check it out here!

First, lets check to see if the database has active connections..

SELECT pid,usename,client_addr,wait_event_type,wait_event,state,query,backend_type
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'source_database_name'
AND pid <> pg_backend_pid();

 pid  | usename | client_addr  | wait_event_type | wait_event | state | query  |  backend_type
------+---------+--------------+-----------------+------------+-------+--------+----------------
 7623 | username | 10.49.60.187 | Client          | ClientRead | idle  | COMMIT | client backend
 7883 | username | 10.49.60.187 | Client          | ClientRead | idle  | COMMIT | client backend
 8388 | username | 10.49.60.187 | Client          | ClientRead | idle  | COMMIT | client backend
 8209 | username | 10.49.60.187 | Client          | ClientRead | idle  | COMMIT | client backend
 8568 | username | 10.49.60.187 | Client          | ClientRead | idle  | COMMIT | client backend
(5 rows)

At, this point we need to terminate those connections, either you can stop your application and re-run the query and wait. Or you can forcefully disconnect them (don't blame me if this breaks your db).

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'source_database_name'
AND pid <> pg_backend_pid();

Now we can copy the database...

CREATE DATABASE new_database_copy WITH TEMPLATE source_database_name;