Creating a PostgreSQL User and Database

Change to the postgres user on the system:

su postgres

Then type in:

psql

At the prompt paste the following two lines of code making sure to replace [username] with the database username, [password] with the database password and [database] with the database name.

CREATE USER [username] WITH ENCRYPTED PASSWORD '[password]';
CREATE DATABASE [database] ENCODING 'UTF8' OWNER [username] TEMPLATE template0;

To check if the database was successfully created, just type in \l at the psql prompt. You should then see a list of databases including the database that you have just created.

A couple of assumptions:

1. Since we are using an encrypted password in line 1 this means you must have md5 set as the authentication method in the pg_hba.conf file.
2. We are creating the database based on a specific template, in this case template0. Depending on what you want to do this might not be the correct template to copy (there is also template1).
3. We are using UTF-8 encoding for the database.