Install Procedure for postgresql 9.3¶

Requirements¶

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

Preliminary Note¶

• 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

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

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

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

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

...
# 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

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

psql test

CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

configure firewall¶

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

system-config-firewall-tui

check if settings are ok

cat /etc/sysconfig/iptables

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

/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;