Install Procedure for postgresql 9.3¶

Requirements¶

To install postgresql you will need the following:

• a installed and supported operating system (e.g. CentOS 6.x)
• root-access
• a fast internet connection
• postgresql Repo

Preliminary Note¶

This procedure is based on a documentation supplied by

• http://www.if-not-true-then-false.com/2010/howto-install-postgresql-8-4-database-server-on-centos-fedora-red-hat/
• http://www.postgresonline.com/journal/archives/203-postgresql90-yum.html

Install¶

modify repo-settings¶

yum list | grep postgresql

You should see a bunch of entries and the ones we want are marked with pgdg93.

If you see postgresql from other repositories besides PGDG, then you may want to exclude them to minimize the risk of installing the wrong thing. This is optional since these days the PostgreSQL package names now include the version number as part of the name so less likely for you to screw up:

vim /etc/yum.repos.d/CentOS-Base.repo

Add to the bottom of the sections [base] and [updates]:

exclude=postgresql*

Install postgresql and postgresql-server packages on CentOS 6.x:¶

yum install postgresql93 postgresql93-server postgresql93-libs postgresql93-contrib postgresql93-devel

Configure PostgreSQL 9.3 Database Server¶

Initialize the cluster first with initdb command:

/etc/init.d/postgresql-9.3 initdb

Edit /var/lib/pgsql/9.3/data/postgresql.conf file:

vim /var/lib/pgsql/9.3/data/postgresql.conf

Set PostgreSQL server to listen all addresses and Change PostgreSQL port (default is 5432). Add/Uncomment/Edit following lines:

...
listen_addresses = '*'
...
port = 5432
...

Edit /var/lib/pgsql/9.3/data/pg_hba.conf file:

vim /var/lib/pgsql/9.3/data/pg_hba.conf

Add (example) your local network with md5 passwords:

...
# Local networks
local    all    all            trust
host    all    all    xx.xx.xx.xx/xx    md5
# Example
host    all    all    10.20.4.0/24    md5
host    all     mes     0.0.0.0/0       md5

manually start PostgreSQL Server:

/etc/init.d/postgresql-9.3 start

automatically start the service at boot time:

/sbin/chkconfig postgresql-9.3 on

you can check the runlevels by

/sbin/chkconfig --list postgresql-9.3

you should get an output like:

postgresql     0:off   1:off   2:on   3:on   4:on   5:on   6:off

Change to postgres user:

su - postgres

Create test database (as postgres user):

createdb test

Login test database (as postgres user):

psql test

Create New “testuser” Role with Superuser and Password:

CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

logout from psql by \q

configure firewall¶

Open PostgreSQL Port (5432) on Iptables Firewall (as root user again)

system-config-firewall-tui

add port 5432:tcp

check if settings are ok

cat /etc/sysconfig/iptables

You should have following line before COMMIT:

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

Restart Iptables Firewall:

/etc/init.d/iptables restart

Usage¶

Test remote connection:

psql -h dbserver -U testuser test

Post Installation Steps¶

DON'T FORGET TO REMOVE testuser BEFORE GOING TO PRODUCTION

dropuser testuser

DON'T FORGET TO SETUP vacuum BEFORE GOING TO PRODUCTION

vacuumdb -a -U postgres -z -v

maybe with a cron-job

OR

as autovacuum is working by default, let's get some log-entries to see it actually working by changing postgres.conf

log_autovacuum_min_duration = 10

you can check the settings of the database by

su - postgres
psql test

psql# show all;