Creating a database on Postgresql server

1.    Login to PostgreSQL server

[oracle@oracleasm1 ~]$ su - postgres
Password:
-bash-4.1$
 
-bash-4.1$ psql
psql (8.4.18, server 9.6.15)
WARNING: psql version 8.4, server version 9.6.
         Some psql features might not work.
Type "help" for help.

Create a role (At first, I’m creating a role, who will be owning the database)

Be sure to always end your SQL statements with ; semicolon

postgres=# create user admin1
postgres=# ;
CREATE ROLE

Create the database using basic parameters 

postgres=# create database nexaprod
postgres-# with encoding='UTF8'
postgres-# owner=admin1
postgres-# connection limit=20 ;
CREATE DATABASE

Listing the databases available on server

postgres=#
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 nexaprod  | admin1   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
(4 rows)

1.    Apart from CREATE DATABSE command, you can also use createdb program to create new databases. Both commands are one and the same.

createdb is a command line utility which you run from bash and not from psql.

2.    Besides create user command on DB server, you can use bash command as below and maintain the privileges

-bash-4.1$ /usr/bin/createuser admin2
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
-bash-4.1$

OR

-bash-4.1$ /usr/bin/createuser nikhil
Shall the new role be a superuser? (y/n) y

3.    The standard create database statement is as below (in case, if you are willing to use your own template)

CREATE DATABASE nexaprod
OWNER = role_name
TEMPLATE=your_template
ENCODING=  LC_COLLATE=<>  LC_TYPE=<> TABLESPACE= <> CONNECTION LIMIT= max_connection

Role_name  : is the name of user which will own the database, make sure the user exist on DB server

Template     : Postgres allows you to create database based on template database

                        Default is template1

Encoding     : is the character set of the database

4.    Below is the simplest way to create the database which will use all default settings of “default template DB – that is template1.

$ create database nexaprod ;