Postgres 9 how to drop a database that is in-use

PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity

Sample below to identify the procid for database cmdbuild:

postgres=# select * from pg_stat_activity where datname='cmdbuild';

datid	datname	procpid	usesysid
44867	cmdbuild	2467	16384
44867	cmdbuild	2468	16384

(2 rows)

pg_cancel_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. 

postgres=# select pg_terminate_backend(procpid) from pg_stat_activity where datname='cmdbuild';

 pg_terminate_backend 

----------------------

 t

 t

(2 rows)

postgres=# DROP DATABASE cmdbuild;

DROP DATABASE

Postgresql on Ubuntu server

Install
To install use the command line and type:

sudo apt-get install postgresql postgresql-contrib

This will install the latest version available in your Ubuntu release and the commonly used add-ons for it.
We then need to change the password for the postgres database user; as postgres we execute the psql command

sudo -u postgres psql postgres

and give the user a password

\password postgres

type a password and quit psql with Ctrl+D or \quit
to set a *nix password for the user use the

sudo passwd postgres

and type the new *nix password for the user

User access
Then to give users access amend the

/etc/postgresql/current/main/pg_hba.conf

and add something like below (granting all users on subnet 10.0.0.0 255.255.255.0 access to all databases with username / password method)

type	database	user	ip address	method
host	all	all	10.0.0.0/24	md5

And make sure postgresql listen on the correct interface / ip address in

/etc/postgresql/current/main/postgresql.conf

by configuring the listen_addresses. Default only listen on localhost.

This can then be tested with the following from a host on the 10.0.0.0/24 subnet:

psql -h 10.0.0.1 -U postgres -d postgres

Add a database and spesific user for it

To create a database with a user that have full rights on the database, use the following command:

sudo -u postgres createuser -D -A -P myuser
sudo -u postgres createdb -O myuser mydb

Else install pgadmin to manage the database.