Creating the prototype databases using initdb

Setting Environment Variable : (editing .bash_profile file)

1. Open Terminal
2. Switch user to Postgres "su - postgres"
3. Set the following variables
   

• PGPORT= Port No (eg : 5432)
  
• PGDATA= Absolute Path to your data directory (/opt/PostgresPlus/8.3/data)
  
• PATH= Absolute Path to your bin directory (/opt/PostgresPlus/8.3/bin)
• save file
  

Checking Environment Variables :

1. open Terminal
2. Switch user to postgres "su - postgres"
3. type following commands to check EVars
   

• echo $PATH (should display the postgres installed port no)
  
• echo $PGPORT (should point to the data directory)
  
• echo $PGDATA (should display the bin directory path)

Creating prototype DBs using Initdb :

• open terminal
• type "initdb" (command creates the two prototype databases template0 and template1)
  

Creating a new database with createdb :

• open terminal
• switch user to postgres
• type psql (you will be connected to postgres database "Postgres=#")
  
• After starting the postmaster, use the "createdb" command to create the database .
• Secondly you can also login using psql and execute following command to create a database Postgres=#CREATE DATABASE movies;

Creating a new PostgreSQL user :

• Use the createuser command to tell PostgreSQL which users are allowed to access your
  database.
• You can also use "CREATE USER username" command in psql client to create a user.

Logging inside PostgreSQL Database using PSQL :

• As root user execute :
  
  • su – postgres
  • psql –d movies (u will be directly connected to movies database)
    
• You will get logged inside psql client

Key Points :

• optional values can be inserted in psql command
• -p : you can specify the PORT no to be connected using this
  
• -U : you can specify the USER through which you want to connect to DB (user has to be exist)
  
• -h : you can specify the HOSTNAME/IP name on which Postgres installed
  
• -d : you can specify the DATABASE name to be connected

full psql command as follows :

• psql -d database -U user -p port -h host
• eg : psql -d movies -U movies -p 5444 -h localhost
• In the above example database is : movies, user : movies, port no : 5444, host : localhost
• Default Values - database : postgres, User : postgres, Port no : port on which u installed postgres (5432) host : localhost
• Default values are the values you inserted in ur .bash_profile file, U can specify default Username and Host names too in your environment variables (PGUSER)

These settings will initialize the all the settings required to work on PostgreSQL... Start working and post your comments...