You can do the following (adapted from here):
First, you block new connections.
Now, we are fortunate in that you only have one username which is connecting (see commments), so the process should be relatively painless (if you are the
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
So now, having blocked new connections, we get rid of current ones:
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'database_name'
If you are the
postgresql user and the other user(s) have a different name, you can do this (from here):
WHERE usename = 'foo_user';
So, you do your maintenance work and then:
GRANT CONNECT ON DATABASE dbname TO PUBLIC, username;
Now, there is another command which can be used, but it's a bit more drastic (from here) -
pg_cancel_backend(pid) will attempt to gracefully kill a running query process.
pg_terminate_backend(pid) will immediately kill the running query process, but potentially have side affects across additional queries running on your database server. The full connection may be reset when running pg_terminate_backend, so other running queries can be affected. Use as a last resort.
If that's not enough of a warning, check this out (from here)!
Be careful with that! As pointed by Erwin Andreasen in the comments
bellow, pg_terminate_backend is the kill -9 in PostgreSQL. It will
terminate the entire process which can lead to a full database restart
in order to recover consistency.
pg_terminate_backend(pid) may cause a server restart, which you have specified is not what you want!