Managing Databases with PostgreSQL

1. Creating a Database

The simplest way to create a new database is to use the createdb command. If you are going to use PostgreSQL frequently, its a good idea to add the PostgreSQL bin directory to your PATH.

bash-2.05a$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --location=PATH       alternative place to store the database
  -E, --encoding=ENCODING   encoding for the database
  -O, --owner=OWNER         database user to own the new database
  -T, --template=TEMPLATE   template database to copy
  -e, --echo                show the commands being sent to the server
  -q, --quiet               don't write any messages
  --help                    show this help, then exit
  --version                 output version information, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -W, --password            prompt for password

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@postgresql.org>.
bash-2.05a$ createdb -e test
Password:
CREATE DATABASE test;
CREATE DATABASE

All is well and your database called 'test' has been created.

2. Listing all available Databases

Use the following psql -l command

bash-2.05a$ psql -l
Password:
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
 test      | jurn     | SQL_ASCII
(3 rows)

bash-2.05a$

The databases template0 and template1 are created when you initialise the cluster. The test database was created in the previous example.

3. Dropping or Removing a Database

Simply use the dropdb command:

bash-2.05a$ dropdb -e test
Password:
DROP DATABASE test;
DROP DATABASE