Creating PostgreSQL Database

How to create a database in PostgreSQL and make it accessible for an application:

1. Install PostgreSQL server and client. In all major Linux distributions you can install them from the main package repository (note that you need postgresql-contrib package also). Make sure it is up and running.

2. Switch to postgres user:

   $ sudo su - postgres
   

3. Start psql:

   $ psql
   

4. Create a user <username> with password <password>:

   postgres=# CREATE USER <username> WITH PASSWORD '<password>';
   

5. Create a database <dbname>:

   postgres=# CREATE DATABASE <dbname> OWNER <username> ENCODING 'utf8';
   

6. Connect to the database:

   postgres=# \c <dbname>
   

7. Activate uuid-ossp extension:

   <dbname>=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
   

8. Type \q to quit.
9. Press ^D to exit the postgres user shell.
10. Under superuser, edit /var/lib/pgsql/data/pg_hba.conf (/etc/postgresql/*/main/pg_hba.conf in Debian) and add the following lines to it:

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   local   <dbname>        <username>                              password
   host    <dbname>        <username>      127.0.0.1/32            password
   host    <dbname>        <username>      ::1/128                 password

1. Restart PostgreSQL server:

      $ sudo systemctl restart postgresql