You will need to first connect to the user postgres from your localhost to create the database owner. The command prompt postgres=# indicates that you are connected to the postgres database. The # in the command prompt actually means that the user connected to is a database superuser[Sort Off like in Linux].
Creating a New User:
To create a New User(mynewuser) in PostgreSQL we will use the CREATE ROLE command as follows:
postgres=#CREATE ROLE mynewuser
postgres-#login
postgres-#password ‘mypassword’;
This will create a new user called ‘mynewuser’ with the password ‘mypassword’.
Creating TableSpace :
Now we will move ahead to create the default tablespace for the new database. This is where the database’s files will be stored. Create a new folder structure as C:/myDB/mynewuser/system
The steps below will help you to create a new tablespace as mynewuser_system
postgres=# CREATE TABLESPACE mynewuser_system
postgres-# OWNER jason
postgres-# LOCATION ‘C:/myDB/mynewuser/system’;
Hers’s how you can check if the tablespace was created. Just key in the following command.
postgres=#db+ mynewuser_system
Create a Database and the related Database objects:
Finally we will now create a new database and the associated database objects. It’s very simple… so here are the SQL Statements.
postgres=# CREATE DATABASE myFirstPSQLdb
postgres-# OWNER jason
postgres-# TEMPLATE template0
postgres-# TABLESPACE mynewuser_system;
You can verify if the Database is installed properly by the following command.
postgres=#l+
Now we will finally create the related Database Objects.
First login into the Database:
postgres=# c myFirstPSQLdb
Then run your SQL script as follows:
myFirstPSQLdb=# i C:/myDB/myObjects.sql
