Steps to install PostgreSQL

posted Sep 18, 2010, 6:17 AM by Sachchida Ojha   [ updated Sep 18, 2010, 6:23 AM ]
gunzip postgresql-version.tar.gz
tar xvf postgresql-version.tar
./configure --prefix =       // default path is /usr/local/pgsql
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

 Creating the PostgreSQL User

 It is recommended to create a separate user to own the PostgreSQL files and processes that will be installed.The user name is typically postgres. By default, POSTGRESQL allows database access only to userslogged into the computer running the database server.

To enable network access, the postmaster must be started with the -i flag. 
Many distributions use a configure command that allows users to choose 
various options before compiling and installing the software. 
PostgreSQL is usually distributed in source code format. 
As a consequence, C source code must be compiled into a format that is 
understood by the CPU. This process is  handled by cc or gcc compiler. 
When PostgreSQL is installed, it creates files in its home directory, 
typically /usr/local/pgsql. This directory holds all the files needed 
by PostgreSQL in various subdirectories: 


 PostgreSQL command-line programs, such as psql. 
 Configuration files and tables shared by all databases.  
 A subdirectory for each database. 
Using the du and ls commands, administrators can display the 
amount of disk space used by each database, table, or index. 
 PostgreSQL documentation. 
 Include files  used by various programming languages. 
 Libraries used by various programming languages. 
This subdirectory also contains files used during initialization 
and sample configuration files that can be copied to /data and modified. 
 PostgreSQL manual pages.
Initialization creates a database called template1 in the PostgreSQL 
home directory. This database is used to create all other databases. 
Initdb  performs this initialization step: 
                $PGSQL_DIR/bin/initdb -D $PGDATA
     The "-D" option specifies the location where the data will be stored.
     Just make sure that the server account can write to the directory 
     (or create it, if it doesn't already exist) before starting "initdb".
Starting the Server 
 Once template1  is created, the database server can be started. 
This step typically involves running the program called postmaster. 
To start up the database server: 
        a). $PGSQL_DIR/bin/pg_ctl start -l logfile -D $PGDATA
        b). in the foreground: 
            $PGSQL_DIR/bin/postmaster -D $PGDATA
        c). in the background use:
            $PGSQL_DIR/bin/postmaster -D $PGDATA >logfile 2>&1 &
To stop a server:
     $PGSQL_DIR/bin/pg_ctl stop -l logfile -D $PGDATA
 Creating a Database 
 Once the database server is running, you can create databases by running 
createdb from the operating system prompt:  
        $PGSQL_DIR/bin/createdb testdb
        Initially, only the POSTGRESQL superuser can create new databases. 
        Other users can be given permission to create new databases. 
        The createdb program creates a new database by making a copy of the template1 db. 
        This database is created when POSTGRESQL is first initialized. Any modifications to 
        template1 will appear in subsequently created databases. 
To see a list avalable databases use following command:
                $PGSQL_DIR/bin/pg_ctl start -l 
To connect to database testdb enter:
                $PGSQL_DIR/bin/psql testdb
At the prompt you can enter SQL commands and start working with PostgreSql.
To switch to amother database: 
                testdb# \connect newdb;
 PostgreSQL download