Install PostgreSQL on CentOS 6.x

While working on my last assignments I had to install/re-install PostgreSQL multiple times and initially didn’t find a consolidated tutorial as to how to do it step-by-step on CentOS 6.x.

So here is a step-by-step guide on “How to install PostgreSQL on CentOS 6.x”

Install PostgreSQL

You first need to add the PostgreSQL repository to yum and update it.

CentOS 6.x 32bit:

rpm -Uvh

CentOS 6.x 64bit:

rpm -Uvh

Finally update the yum package.

yum update

Install & Initialize PostgreSQL:

yum install postgresql94-server postgresql94-contrib
service postgresql-9.4 initdb

Start PostgreSQL service and make it to start automatically on every reboot.

service postgresql-9.4 start
chkconfig postgresql-9.4 on

Adjust iptables for external access of PostGreSQL.

vi /etc/sysconfig/iptables

Add the following line:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT

Save and exit the file. Restart iptables service.

service iptables restart

Configure PostgreSQL-MD5 Authentication:

MD5 authentication requires the client to supply an MD5-encrypted password for authentication.

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

Add or Modify the lines as shown below

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all               md5
host    all             all             md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Restart PostgreSQL service:

service postgresql-9.4 restart

Access PostgreSQL command prompt

The default database name and database user are “postgres”. To access PostgreSQL related operations:

su - postgres 

Sample Output:

psql (9.4.0)
Type "help" for help.

To exit from PosgreSQL prompt, type \q.

Set “postgres” user password, login to PostgreSQL prompt,

su - postgres 

Set postgres password:

postgres=# \password postgres 
Enter new password: 
Enter it again: 
postgres=# \q

Create New User and Database

For example, let us create a new user called “saurabh” with password “agarwal”, and database called “mypostgres”.

Switch to postgres user:

su - postgres

Create user saurabh.

$ createuser saurabh

Create database:

$ createdb mypostgres

Login to the psql prompt and set password and grant access:

bash# psql
postgres=# alter user senthil with encrypted password 'agarwal';
postgres=# grant all privileges on database mypostgres to saurabh;

Hope this helps someone, starting afresh with PostgreSQL installation.

Let me know (in comments below) if something is missed.


Export Insert Data in PostgreSQL

Recently, I was involved a SDN App which is using PostgreSQL database as backend. After finally developing the app and doing upgrades, we stumbled upon the migration scenario where data export of the existing database was needed. Hence I started researching about, how to export data (basically only insert statements) in PostgreSQL.

Though it seemed very every easy to do this from pgAdmin GUI tool, we needed to do it from console due to firewalls and it took us some time to figure out the right command to do it.

PostgreSQL Version: 9.2

Login as postgres admin user

su - postgres

Run the export command below:

.\pg_dump --username "postgres" --format plain --section data --inserts --file "<backup file name>" "<db to backup>"

Hope it helps!