Install PostgreSQL

9:33 AM

1. Download your required Postgres version source code from  http://www.postgresql.org/ftp/source/


shell> wget https://ftp.postgresql.org/pub/source/v9.4.1/postgresql-9.4.1.tar.gz

2. Install PostgreSQL

shell> tar -zxvf postgresql-9.4.1.tar
shell> cd postgresql-9.4.1
shell> yum install kernel-devel kernel-headers gcc
 shell> ./configure 
shell> make && make install && make install-docs

3. PostgreSQL Installation Issue:

issue 1: configure: error: readline library not found
shell> yum install readline-devel.x86_64

issue2: configure: error: zlib library not found
shell> yum install zlib-devel.x86_64

4. Create postgreSQL user account and set password for login

shell> adduser postgres && passwd postgres


5. Create postgreSQL data directory

Create the postgres data directory and make postgres user as the owner.
shell> mkdir /usr/local/pgsql/data
shell> chown postgres:postgres /usr/local/pgsql/data

5. Initialize postgreSQL data directory 

shell> su - postgres
bash-4.1> /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/

6. Make the changes in your database config file.

shell> vi /usr/local/pgsql/data/postgresql.conf
Uncomment the line #listen_addresses = ‘localhost’ and change it to listen_addresses = ‘*’

shell> vi /usr/local/pgsql/data/pg_hba.conf
host all admin 192.168.1.1/24 md5


7. Set environment variables

shell> echo 'LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib' >> /etc/profile
shell> echo 'PATH=$PATH:/usr/local/pgsql/bin' >> /etc/profile
shell> echo 'MANPATH=$MANPATH:/usr/local/pgsql/bin' >> /etc/profile
shell> echo 'export PATH MANPATH LD_LIBRARY_PATH' >> /etc/profile

8. Create a super user for accessing database server from remote network.

bash-4.1$> /usr/local/pgsql/bin/psql -dpostgres
psql (9.4.1)
Type "help" for help.
postgres=#
postgres=#create role admin login password '$0m3p@ssw0rd' superuser;
postgres=#select * from pg_catalog.pg_user; 
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | t       | ******** |          |
 admin    |    16385 | f           | t        | t         | f       | ******** |          |
(2 rows)


9. Open the default PostgreSQL database port in server firewall to connecting from remote network.

shell> vi /etc/sysconfig/iptables
Add this line before reject rule "-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT"
shell> service iptables restart
shell> 

10. Stop postgreSQL database

Use the postgres postmaster command to start the postgreSQL server in the background as shown below.
bash-4.1> pg_ctl stop -D /usr/local/pgsql/data


11. Create a start/stop script for PostgreSQL:

shell> cd /etc/init.d/ 
shell> vi postgresql (copy and paste the following lines in the file)
#!/bin/bash

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# where to find commands like su, echo, etc...
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

DB_ENCODING=UTF8

DB_LOCALE=C

PG_INSTALL_DIR=/usr/local/pgsql-9.3

PG_DATA_DIR="${PG_INSTALL_DIR}/data"

PG_SERVER_LOG="$PG_DATA_DIR/serverlog"

PG_UNIX_USER=postgres

POSTGRES="$PG_INSTALL_DIR/bin/postgres"

PG_CTL="$PG_INSTALL_DIR/bin/pg_ctl"

INITDB="$PG_INSTALL_DIR/bin/initdb"

# die on first failure; do not keep trucking
set -e

if [ $# -ne 1 ]; then
    echo "please enter start/stop/restart etc..." 1>&2
    exit 1
fi

# Only start if we can find postgres and pg_ctl.
if [ ! -x $PG_CTL ]; then
    echo "$PG_CTL not found" 1>&2
    exit 1
fi

if [ ! -x $POSTGRES ]; then
    echo "$POSTGRES not found" 1>%amp;2
    exit 1
fi

case $1 in
  init)
	su - $PG_UNIX_USER -c "$INITDB --pgdata='$PG_DATA_DIR' --encoding=$DB_ENCODING --locale=$DB_LOCALE"
	;;
  start)
	echo -n "Starting PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL start -D '$PG_DATA_DIR' -l $PG_SERVER_LOG &"
	echo "ok"
	;;
  stop)
	echo -n "Stopping PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL stop -D '$PG_DATA_DIR' -s -m fast"
	echo "ok"
	;;
  restart)
	echo -n "Restarting PostgreSQL: "
	su - $PG_UNIX_USER -c "$PG_CTL stop -D '$PG_DATA_DIR' -s -m fast -w"
	su - $PG_UNIX_USER -c "$PG_CTL start -D '$PG_DATA_DIR' -l $PG_SERVER_LOG &"
	echo "ok"
	;;
  reload)
        echo -n "Reload PostgreSQL: "
        su - $PG_UNIX_USER -c "$PG_CTL reload -D '$PG_DATA_DIR' -s"
        echo "ok"
        ;;
  status)
	su - $PG_UNIX_USER -c "$PG_CTL status -D '$PG_DATA_DIR'"
	;;
  *)
	# Print help
	echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
	exit 1
	;;
esac

exit 0

12. Get the script ready to run

shell> chmod +x /etc/init.d/postgresql
shell>/etc/init.d/postgresql start

13. connect postgresql through remote server

remote server shell> psql -U admin -d postgres -h 192.168.1.3
Password for user admin:
psql (9.1.13, server 9.4.1)
WARNING: psql version 9.1, server version 9.4.
         Some psql features might not work.
Type "help" for help.
 
postgres=#

You Might Also Like

0 comments

Contact Form

Name

Email *

Message *

Translate

Wikipedia

Search results