PostgreSQL Installation

PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2, developed at the University of California at Berkeley Computer Science Department. POSTGRES pioneered many concepts that only became available in some commercial database systems much later.
 PostgreSQL is an open-source descendant of this original Berkeley code. It supports a large part of the SQL standard and offers many modern features:
 
• complex queries
 • foreign keys
 • triggers
 • updatable views
 • transactional integrity
 • multiversion concurrency control
 Also, PostgreSQL can be extended by the user in many ways, for example by adding new
 • data types
 • functions
 • operators
 • aggregate functions
 • index methods
 • procedural languages

 And because of the liberal license, PostgreSQL can be used, modified, and distributed by anyone free of charge for any purpose, be it private, commercial, or academic.

Reference Links :-
 PostgreSQL: Documentation: 9.4: Installation Procedure
 http://pkgs.org/altlinux-sisyphus/classic-i586/libpq5.3-9.0.18-alt1.i586.rpm.html
 http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/repoview/
 http://yum.postgresql.org/repopackages.php
 http://www.postgresql.org/ftp/source/v9.3.5/
 http://www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from-source/
 http://ftp.scientificlinux.org/linux/slf52/i386/SL/repodata/repoview/readline-devel-0-5.1-1.1.html
 wget http://wwwmaster.postgresql.org/redir/198/f/source/v8.3.7/postgresql-8.3.7.tar.gz

untar the source tar file
 cd to the folder
 ./configure
 make
 su
 make 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/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
 /usr/local/pgsql/bin/createdb test
 /usr/local/pgsql/bin/psql test

-------------------------

[Lab root @ hostname /data/5]# tar -zxvf postgresql-9.3.5.tar
 postgresql-9.3.5/
 postgresql-9.3.5/contrib/
 postgresql-9.3.5/contrib/tcn/
 postgresql-9.3.5/contrib/tcn/tcn.control
 postgresql-9.3.5/contrib/tcn/Makefile
 postgresql-9.3.5/contrib/tcn/tcn.c
 postgresql-9.3.5/contrib/tcn/tcn--1.0.sql
 postgresql-9.3.5/contrib/sslinfo/
 postgresql-9.3.5/contrib/sslinfo/sslinfo.control
 postgresql-9.3.5/contrib/sslinfo/sslinfo.c

---blah blah blah -----

[Lab root @ hostname /data/5]#

[Lab root @ hostname /data/5]# ll -lhtr
 total 27M
 drwxrwxrwx 6 1107 1107 4.0K Jul 21 15:28 postgresql-9.3.5
 drwx------ 2 root root 16K Aug 27 16:03 lost+found
 -rw-r--r-- 1 oracle dba 21M Sep 11 08:37 postgresql-9.3.5.tar
 -rw-r--r-- 1 root root 24K Sep 12 06:00 pg_catcheck93-1.0.0-1.rhel6.x86_64.rpm
 -rw-r--r-- 1 root root 1010K Sep 12 06:00 postgresql93-9.3.5-1PGDG.rhel6.x86_64.rpm
 -rw-r--r-- 1 root root 485K Sep 12 06:00 postgresql93-contrib-9.3.5-1PGDG.rhel6.x86_64.rpm
 -rw-r--r-- 1 root root 191K Sep 12 06:00 postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64.rpm
 -rw-r--r-- 1 root root 4.1M Sep 12 06:00 postgresql93-server-9.3.5-1PGDG.rhel6.x86_64.rpm
 drwxr-xr-x 3 root root 4.0K Sep 12 06:13 AuxiliaryFiles
 [Lab root @ hostname /data/5]

[Lab root @ hostname /data/5]# cd postgresql-9.3.5
 [Lab root @ hostname /data/5/postgresql-9.3.5]# ll -lhtr
 total 1.1M
 -rw-r--r-- 1 1107 1107 1.2K Jul 21 15:10 README
 -rw-r--r-- 1 1107 1107 1.5K Jul 21 15:10 Makefile
 -rw-r--r-- 1 1107 1107 283 Jul 21 15:10 HISTORY
 -rw-r--r-- 1 1107 1107 3.6K Jul 21 15:10 GNUmakefile.in
 -rw-r--r-- 1 1107 1107 1.2K Jul 21 15:10 COPYRIGHT
 -rw-r--r-- 1 1107 1107 65K Jul 21 15:10 configure.in
 -rwxr-xr-x 1 1107 1107 868K Jul 21 15:10 configure
 -rw-r--r-- 1 1107 1107 385 Jul 21 15:10 aclocal.m4
 drwxrwxrwx 56 1107 1107 4.0K Jul 21 15:25 contrib
 drwxrwxrwx 2 1107 1107 4.0K Jul 21 15:26 config
 drwxrwxrwx 3 1107 1107 4.0K Jul 21 15:26 doc
 -rw-r--r-- 1 1107 1107 75K Jul 21 15:28 INSTALL
 drwxrwxrwx 15 1107 1107 4.0K Jul 21 15:28 src
 [Lab root @ hostname /data/5/postgresql-9.3.5]

[Lab root @ hostname /data/5/postgresql-9.3.5]# ./configure --without-readline --without-zlib
 hecking build system type... x86_64-unknown-linux-gnu
 checking host system type... x86_64-unknown-linux-gnu
 checking which template to use... linux
 checking whether to build with 64-bit integer date/time support... yes
 checking whether NLS is wanted... no
 checking for default port number... 5432

---blah blah blah -----
 [Lab root @ hostname /data/5/postgresql-9.3.5]#

[Lab root @ hostname /data/5/postgresql-9.3.5]# make

---blah blah blah -----

make -C config all
 make[1]: Entering directory `/data/5/postgresql-9.3.5/config'
 make[1]: Nothing to be done for `all'.
 make[1]: Leaving directory `/data/5/postgresql-9.3.5/config'
 All of PostgreSQL successfully made. Ready to install.
 [Lab root @ hostname /data/5/postgresql-9.3.5]

[Lab root @ hostname /data/5/postgresql-9.3.5]# make install

---blah blah blah -----

make[1]: Entering directory `/data/5/postgresql-9.3.5/config'
 /bin/mkdir -p '/usr/local/pgsql/lib/pgxs/config'
 /usr/bin/install -c -m 755 ./install-sh '/usr/local/pgsql/lib/pgxs/config/install-sh'
 make[1]: Leaving directory `/data/5/postgresql-9.3.5/config'
 PostgreSQL installation complete.
 [Lab root @ hostname /data/5/postgresql-9.3.5]

Install the readline-devel and libtermcap-devel to fix configure issue for without using --without-readline & --without-zlib options.

[Lab root @ hostname /data/5/postgresql-9.3.5]# rpm -ivh libtermcap-devel-2.0.8-46.1.i386.rpm
 warning: libtermcap-devel-2.0.8-46.1.i386.rpm: Header V3 DSA/SHA1 Signature, key ID 82fd17b2: NOKEY
 A. Preparing... ########################################### [100%]
 1:libtermcap-devel ########################################### [100%]
 [Lab root @ hostname /data/5/postgresql-9.3.5]# rpm -ivh readline-devel-5.1-1.1.i386.rpm
 warning: readline-devel-5.1-1.1.i386.rpm: Header V3 DSA/SHA1 Signature, key ID 82fd17b2: NOKEY
 A. Preparing... ########################################### [100%]
 1:readline-devel ########################################### [100%]
 [Lab root @ hostname /data/5/postgresql-9.3.5]

After the installation, make sure bin, doc, include, lib, man and share directories are created under the default /usr/local/pgsql directory as shown below.

[Lab root @ hostname /data/5/postgresql-9.3.5]# cd /usr/local/pgsql/
 [Lab root @ hostname /usr/local/pgsql]# ll -lhtr
 total 20K
 drwx------ 15 postgres postgres 4.0K Sep 11 09:03 data
 drwxr-xr-x 6 root root 4.0K Sep 12 06:49 include
 drwxr-xr-x 6 root root 4.0K Sep 12 06:49 share
 drwxr-xr-x 2 root root 4.0K Sep 12 06:49 bin
 drwxr-xr-x 4 root root 4.0K Sep 12 06:49 lib
 Create postgreSQL user account

[Lab root @ hostname /usr/local/pgsql]# adduser postgres
 [Lab root @ hostname /usr/local/pgsql]# passwd postgres
 Changing password for user postgres.

You can now choose the new password.

Enter new password:
 Weak password: not enough different characters or classes.
 Re-type new password:
 passwd: all authentication tokens updated successfully.
 [Lab root @ hostname /usr/local/pgsql]#
 Creating Data directory and changing its permissions.
 [Lab root @ hostname /usr/local/pgsql]# mkdir postgresdb
 [Lab root @ hostname /usr/local/pgsql]# chown postgres:postgres postgresdb/
 [Lab root @ hostname /usr/local/pgsql]# ll -lhtr
 total 24K
 drwxr-xr-x 6 root root 4.0K Sep 12 06:49 include
 drwxr-xr-x 6 root root 4.0K Sep 12 06:49 share
 drwxr-xr-x 2 root root 4.0K Sep 12 06:49 bin
 drwxr-xr-x 4 root root 4.0K Sep 12 06:49 lib
 drwxr-xr-x 2 postgres postgres 4.0K Sep 12 07:11 postgresdb
 [Lab root @ hostname /usr/local/pgsql]# ls -ld /usr/local/pgsql/postgresdb
 drwxr-xr-x 2 postgres postgres 4096 Sep 12 07:11 /usr/local/pgsql/postgresdb
 [Lab root @ hostname /usr/local/pgsql]
 Initialize postgreSQL data directory

[Lab root @ hostname /usr/local/pgsql]# su - postgres
 -bash-4.1$
 -bash-4.1$
 -bash-4.1$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/postgresdb/
 The files belonging to this database system will be owned by user "postgres".
 This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
 The default database encoding has accordingly been set to "UTF8".
 The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/postgresdb ... ok
 creating subdirectories ... ok
 selecting default max_connections ... 100
 selecting default shared_buffers ... 128MB
 creating configuration files ... ok
 creating template1 database in /usr/local/pgsql/postgresdb/base/1 ... ok
 initializing pg_authid ... ok
 initializing dependencies ... ok
 creating system views ... ok
 loading system objects' descriptions ... ok
 creating collations ... ok
 creating conversions ... ok
 creating dictionaries ... ok
 setting privileges on built-in objects ... ok
 creating information schema ... ok
 loading PL/pgSQL server-side language ... ok
 vacuuming database template1 ... ok
 copying template1 to template0 ... ok
 copying template1 to postgres ... ok
 syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
 You can change this by editing pg_hba.conf or using the option -A, or
 --auth-local and --auth-host, the next time you run initdb.

A. Success. You can now start the database server using:

/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/postgresdb/
 or
 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/postgresdb/ -l logfile start

-bash-4.1$
 Post Installation set-up, configuring environment variables.

-bash-4.1$ cat ~/.bash_profile
 [ -f /etc/profile ] && source /etc/profile
 PGDATA=/var/lib/pgsql/9.3/data
 export PGDATA

## Shared Library files

LD_LIBRARY_PATH=/usr/local/pgsql/lib
 export LD_LIBRARY_PATH

## Environment variables

PATH=/usr/local/pgsql/bin:$PATH
 export PATH

MANPATH=/usr/local/pgsql/man:$MANPATH
 export MANPATH

-bash-4.1$
 -bash-4.1$ source ~/.bash_profile
 -bash-4.1$
 -bash-4.1$ env | grep pgsql
 MANPATH=/usr/local/pgsql/man:
 LD_LIBRARY_PATH=/usr/local/pgsql/lib
 PATH=/usr/local/pgsql/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
 PWD=/var/lib/pgsql
 HOME=/var/lib/pgsql
 PGDATA=/var/lib/pgsql/9.3/data
 -bash-4.1$

Validate the postgreSQL data directory

-bash-4.1$ ls -l /usr/local/pgsql/postgresdb/
 total 92
 drwx------ 5 postgres postgres 4096 Sep 12 07:13 base
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 global
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_clog
 -rw------- 1 postgres postgres 4476 Sep 12 07:13 pg_hba.conf
 -rw------- 1 postgres postgres 1636 Sep 12 07:13 pg_ident.conf
 drwx------ 4 postgres postgres 4096 Sep 12 07:13 pg_multixact
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_notify
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_serial
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_snapshots
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_stat
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_stat_tmp
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_subtrans
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_tblspc
 drwx------ 2 postgres postgres 4096 Sep 12 07:13 pg_twophase
 -rw------- 1 postgres postgres 4 Sep 12 07:13 PG_VERSION
 drwx------ 3 postgres postgres 4096 Sep 12 07:13 pg_xlog
 -rw------- 1 postgres postgres 20557 Sep 12 07:13 postgresql.conf
 -bash-4.1$

Start postgreSQL database

Use the postgres postmaster command to start the postgreSQL server in the background as shown below.

-bash-4.1$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/postgresdb >logfile 2>&1 &
 [1] 26520
 -bash-4.1$
 -bash-4.1$
 -bash-4.1$
 -bash-4.1$
 -bash-4.1$
 -bash-4.1$ cat logfile
 LOG: could not create IPv6 socket: Address family not supported by protocol
 LOG: database system was shut down at 2014-09-12 07:13:33 EDT
 LOG: autovacuum launcher started
 accept connections
 -bash-4.1$

Create postgreSQL DB and test the installation
 Create a test database and connect to it to make sure the installation was successful as shown below.

-bash-4.1$ cd /usr/local/pgsql/bin/
 -bash-4.1$ ll -lhtr
 total 8.2M
 -rwxr-xr-x 1 root root 5.6M Sep 12 06:49 postgres
 lrwxrwxrwx 1 root root 8 Sep 12 06:49 postmaster -> postgres
 -rwxr-xr-x 1 root root 752K Sep 12 06:49 ecpg
 -rwxr-xr-x 1 root root 100K Sep 12 06:49 initdb
 -rwxr-xr-x 1 root root 44K Sep 12 06:49 pg_ctl
 -rwxr-xr-x 1 root root 342K Sep 12 06:49 pg_dump
 -rwxr-xr-x 1 root root 144K Sep 12 06:49 pg_restore
 -rwxr-xr-x 1 root root 82K Sep 12 06:49 pg_dumpall
 -rwxr-xr-x 1 root root 358K Sep 12 06:49 psql
 -rwxr-xr-x 1 root root 59K Sep 12 06:49 createdb
 -rwxr-xr-x 1 root root 58K Sep 12 06:49 dropdb
 -rwxr-xr-x 1 root root 64K Sep 12 06:49 createlang
 -rwxr-xr-x 1 root root 63K Sep 12 06:49 droplang
 -rwxr-xr-x 1 root root 62K Sep 12 06:49 createuser
 -rwxr-xr-x 1 root root 58K Sep 12 06:49 dropuser
 -rwxr-xr-x 1 root root 58K Sep 12 06:49 clusterdb
 -rwxr-xr-x 1 root root 62K Sep 12 06:49 vacuumdb
 -rwxr-xr-x 1 root root 60K Sep 12 06:49 reindexdb
 -rwxr-xr-x 1 root root 32K Sep 12 06:49 pg_isready
 -rwxr-xr-x 1 root root 30K Sep 12 06:49 pg_config
 -rwxr-xr-x 1 root root 30K Sep 12 06:49 pg_controldata
 -rwxr-xr-x 1 root root 36K Sep 12 06:49 pg_resetxlog
 -rwxr-xr-x 1 root root 65K Sep 12 06:49 pg_basebackup
 -rwxr-xr-x 1 root root 47K Sep 12 06:49 pg_receivexlog
 -bash-4.1$
 -bash-4.1$
 -bash-4.1$ ./createdb mydb
 -bash-4.1$
 -bash-4.1$ ./psql mydb
 psql (9.3.5)
 Type "help" for help.

mydb=#
 mydb=#

Ask Question