How to install PostgreSQL in RHEL/ CentOS

Here are following steps :-

# yum install postgresql postgresql-server -y

# chkconfig postgresql on

# service postgresql start

# service postgresql initdb

# /etc/init.d/postgresql restart

# nmap X.X.X.X

Postgre SQL Port Number – 5432

pgsql_backup.sh

##################################################
# pgsql_backup.sh
##################################################
#!/bin/bash
# Location of the backup logfile.
logfile=”/path/to/logfile.log”
# Location to place backups.
backup_dir=”/directory/to/place/backups”
touch $logfile
timeslot=`date +%H-%M`
databases=`psql -h localhost -U postgres -q -c “l” | sed -n 4,/eof/p | grep -v rows) | awk {‘print $1’}`

for i in $databases; do
timeinfo=`date ‘+%T %x’`
echo “Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i ” >> $logfile
/server/pgsql/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
/server/pgsql/bin/pg_dump $i -h 127.0.0.1 | gzip > “$backup_dir/postgresql-$i-$timeslot-database.gz”
done
##################################################

How to connect Remote PostgreSQL Database

For accessing the pgsql database remotely,  make the following changes in the server.

1) Open the Port 5432 in the server.

# iptables -A INPUT -p tcp –dport 5432 -j ACCEPT

# service iptables restart

If you are using csf firewall, open the port 5432 in /etc/csf/csf.conf and restart csf firewall.

2) Edit the Pgsql configuration file

# vi /var/lib/pgsql/data/postgresql.conf

Make sure that the following entries are there:-

tcpip_socket = true
port = 5432
listen_addresses=’localhost’
Next set IP address(es) to listen on;
you can use comma-separated list of addresses; defaults to
‘localhost’, and ‘*’ is all ip address:
listen_addresses=’*’
Or just bind to 202.54.11.2 and 202.54.11.3 IP address
listen_addresses=’202.54.11.2 202.54.11.3′

3) Edit PostgreSQL HOST ACCESS CONTROL FILE (pg_hba.conf)

# vi /var/lib/pgsql/data/pg_hba.conf

Add the machine IP address from which database is accessing remotely.

local all trust
host all 127.0.0.1 255.255.255.255 trust
host all X.X.X.X 255.255.255.255 trust

Here X.X.X.X  is the machine IP address from which database is accessing remotely.

6. For the connection with ssl add the machine IP in the hostSSL section in the /var/lib/pgsql/data/pg_hba.conf

# vi /var/lib/pgsql/data/pg_hba.conf

Entries will be like this:-

hostssl all all 99.266.122 235 255.255.255.255 trust
host all all 99.266.122.235 255.255.255 trust

How to Backup & Restore PostgreSQL Database

pg_dump is an effective tool for PostgreSQL through which we can backup postgres database and to restore PostgreSQL database, command psql is enough.

1) Backup postgres database, By ssh to server and execute following command to take backup of database.

# pg_dump -U Db_user Db_name -f database_name.sql

2) Executing above command will prompt you for database password, once password is authenticated database will be backup into file database_name.sql

Note: replace Db_User and Db_name with your actual database user and database name respectively.

3) Restore postgres database

# psql -U Db_user -d Db_name -f mydb.sql

How to Install PostgreSQL

It is easy to install it via yum or up2date. However, if you need a specific version, it is better to go for rpm installation.

Method-1 :- Using YUM

# yum groupinstall postgresql

Method-2 :- Using UP2DATE

# up2date @ postgresql

Method-3 :- Using RPM

1. You need three rpm.
=======================
postgresql-libs–1PGDG.rhel
postgresql-server–1PGDG.rhel
postgresql–1PGDG.rhel
=======================

2. Download the RPM from rpm.pbone.net for your coresponding OS and the required version.

3. Download it to “/usr/local/src” Directory

4. Install them using

# rpm -ivh postgresql-libs–1PGDG.rhel

# rpm -ivh postgresql-server–1PGDG.rhel

# rpm -ivh postgresql–1PGDG.rhel

5. Once you have completed this you need can start the service.

# /etc/init.d/postgresql start

6. Edit the file ‘/var/lib/pgsql/data/pg_hba.conf’, replace all entries of “ident sameuser” with “trust”.

# vi /var/lib/pgsql/data/pg_hba.conf

“ident sameuser” with “trust”

7. Edit the file ‘/var/lib/pgsql/data/postgresql.conf’, change the line “max_connections=100? to atleast “max_connections=500?.

# vi /var/lib/pgsql/data/postgresql.conf

max_connections=500

8. Restart PostgreSQL.

# /etc/init.d/postgresql restart

9. If the initialisation fails the files pg_hba.conf and postgresql.conf will not be created. In such a case, perform the following operation. Check the startup log which is found in /var/lib/pgsql/

# chown postgres /var/lib/pgsql/data

# su postgres

# initdb –D /var/lib/pgsql/data

10. To make sure it starts automatically on boot – do the following command

# chkconfig –list

# chkconfig postgresql on

How to change database encoding value for postgresql database

We can change database encoding values for postgresql database by using following steps but make sure that you have taken backup for current database.

1. Log in to server as root user and switch to postgres

# su postgres

2. To check all database run

# psql -l

3. To switch database for example we are using support_test

# psql -d support_test

4. Run command to check encoding

support_test=# encoding
UTF8

SET CLIENT_ENCODING TO ‘LATIN1′;
or
SET NAMES ‘LATIN1′;

5. Now check encoding

support_test=# SHOW client_encoding;
client_encoding

PgSQL query failed error

Error:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: permission
denied for relation cg_ref_codes in /home/user/public_html/essencial.php on line 48

Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL
result resource in /home/user/public_html/essencial.php on line 50

Fix:

1. Open the pgsql configuration file.

# vi /var/lib/pgsql/data/pg_hba.conf

local   all         all                                     md5
host    all         all      127.0.0.1  255.255.255.255     md5

2. Edit the file.

local   all         all                                     md5
host    all         all      127.0.0.1  255.255.255.255    password

3. Save it.

4. Restart the postgresql service.

# /etc/init.d/postgresql REstart

Postgres Databases are not being showed on cPanel

We can fix this by modifying /var/lib/pgsql/data/pg_hba.conf from ‘md5? to ‘trust’. It should have the following entries:-

local all all trust
host all all 127.0.0.1 255.255.255.255 trust

Try to access cPanel now. Databases will be showing up on cPanel.

How to install pdo_pgsql in cPanel server

Its that easy to add the php module pdo_pgsql in a cPanel server. Just use the command

# pecl install pdo_pgsql

Then go for a Apache restart. It will be shown up in your phpinfo page.

# /etc/init.d/httpd /restart