Install PostgreSQL
9:33 AM1. 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 foundshell> 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=#
0 comments