postgres administration - ghdrako/doc_snipets GitHub Wiki
pg_reload_conf ()
pg_cancel_backend ( pid integer )
pg_terminate_backend ( pid integer, timeout bigint DEFAULT 0 )
SELECT pg_cancel_backend(<process_id>);
PostgreSQL offers two functions to kill slow queries:
- pg_cancel_backend: Terminate the query and keep the connection alive
- pg_terminate_backend: Terminate the database connection including the query
SELECT pg_sleep(1000000); -- simulate long running query
SELECT *
FROM pg_stat_activity
WHERE datname IS NOT NULL
AND state = 'active'
ORDER BY query_start
LIMIT 1;
SELECT pg_cancel_backend(1207);
SELECT pg_terminate_backend(1207);
Change a role's password:
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
Change a password expiration date, specifying that the password should expire at midday on 4th May 2015 using the time zone which is one hour ahead of UTC:
ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
Make a password valid forever:
ALTER ROLE fred VALID UNTIL 'infinity';
Ustawienie wymuszenia zmiany hasla
ALTER ROLE n1402431 VALID UNTIL '2024-12-04 12:00:00+00';
Do not use the database you want to drop.
psql -h localhost postgres postgres
- Make sure no one can connect to this database. You can use one of following methods (the second seems safer, but does not prevent connections from superusers).
\l
/* Method 1: update system catalog */
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';
/* Method 2: use ALTER DATABASE. Superusers still can connect!*/
select datconnlimit from pg_database where datname='mydb';
ALTER DATABASE mydb CONNECTION LIMIT 0;
...
-- restore limit ( -1 unlimited)
ALTER DATABASE mydb CONNECTION LIMIT x;
ALTER ROLE x SET CONNECTION LIMIT 0;
- Force disconnection of all clients connected to this database, using pg_terminate_backend.
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb';
/* For old versions of PostgreSQL (up to 9.1), change pid to procpid:
SELECT pg_terminate_backend(procpid)
FROM pg_stat_activity
WHERE datname = 'mydb'; */
- Drop the database.
DROP DATABASE mydb;
Step 1 requires superuser privileges for the 1st method, and database owner privileges for the 2nd one. Step 2 requires superuser privileges. Step 3 requires database owner privilege.
Postgres 13 adds the FORCE option for DROP DATABASE. See filiprem's answer.
The shell utility dropdb is basically just a wrapper around the SQL command and inherits the same option. So it's simple and reliable from the shell now, too:
dropdb mydb --force
Or short:
dropdb mydb -f
DROP DATABASE <dbname> WITH (FORCE);