Create Users and Databases in PostgreSQL

psql is the command line interface for PostgreSQL. psql enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file. In addition, it provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

After you type the command to connect to the user postgres form localhost, you can create the database owner. postgres=# means that you are connected to the database postgres and # means that the user you are connected to is a database superuser.

To create the database owner named softpedia we will use the CREATE ROLE command:

postgres=#CREATE ROLE softpedia
postgres-#login
postgres-#password 'softpedia';

'softpedia' is the password for the user softpedia. My advice is to change it and put what password you want. To verify if the user softpedia was successfully created, type postgres=#softpedia and it will appear like in fig 1.

Next, we will learn to create the default tablespace, where the database’s files will be stored.

Create the folder c:/pgdata. Make sure the user posgres has full access to this folder. After that, create the folder: c:/pgdata/softpedia/system. Next step is to create softpedia_system tablespace:

postgres=# CREATE TABLESPACE softpedia_system
postgres-# OWNER pagila
postgres-# LOCATION 'c:/pgdata/softpedia/system';

To check if softpedia_system tablespace was created, type postgres=#db+ softpedia_system. Now you have to create the database and the database objects. To create softpedia database is very easy. If you know a few SQL commands it’s piece of cake.

postgres=# CREATE DATABASE softpedia
postgres-# OWNER softpedia
postgres-# TEMPLATE template0
postgres-# TABLESPACE softpedia_system;

Then verify if the database was created type: postgres=#l+

The final step is to create the softpedia database objects. First connect to the database: postgres=# c softpedia.

To create the database object make sure you have the sql script prepared and type: softpedia=# i c:/pgdata/softpedia-object.sql and then verify if the objects were imported successfully: softpedia=#d+ .

That’s it. Easy like 1, 2, 3.