DokuWiki » Infrastructure » Operating System » CentOS 6

h1. Install Procedure for postgresql 9.3

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

* [[repo_postgresql| postgresql Repo]]

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

h2. Install 

h3. modify repo-settings

<pre><code class="bash">

yum list | grep postgresql

</code></pre>

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:

<pre><code class="bash">

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

</code></pre>

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

<pre><code class="bash">

exclude=postgresql*

</code></pre>

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

<pre><code class="bash">

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

</code></pre>

h2. Configure PostgreSQL 9.3 Database Server 

Initialize the cluster first with initdb command:

<pre><code class="bash">

/etc/init.d/postgresql-9.3 initdb

</code></pre>

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

<pre><code class="bash">

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

</code></pre>

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

<pre><code class="bash">

...

listen_addresses = '*'

...

port = 5432

...

</code></pre>

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

<pre><code class="bash">

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

</code></pre>

Add (example) your local network with md5 passwords:

<pre><code class="bash">

...

# 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

</code></pre>

manually start PostgreSQL Server:

<pre><code class="bash">

/etc/init.d/postgresql-9.3 start

</code></pre>

automatically start the service at boot time:

<pre><code class="bash">

/sbin/chkconfig postgresql-9.3 on

</code></pre>

you can check the runlevels by

<pre><code class="bash">

/sbin/chkconfig --list postgresql-9.3

</code></pre>

you should get an output like:

<pre><code class="bash">

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

</code></pre>

Change to postgres user:

<pre><code class="bash">

su - postgres

</code></pre>

Create test database (as postgres user):

<pre><code class="bash">

createdb test

</code></pre>

Login test database (as postgres user):

<pre><code class="bash">

psql test

</code></pre>

Create New “testuser” Role with Superuser and Password:

<pre><code class="bash">

CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

</code></pre>

logout from @psql@ by @\q@

h2. configure firewall

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

<pre><code class="bash">

system-config-firewall-tui

</code></pre>

add port @5432:tcp@

check if settings are ok

<pre><code class="bash">

cat /etc/sysconfig/iptables

</code></pre>

You should have following line before COMMIT:

<pre><code class="bash">

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

</code></pre>

Restart Iptables Firewall:

<pre><code class="bash">

/etc/init.d/iptables restart

</code></pre>

h2. Usage 

Test remote connection:

<pre><code class="bash">

psql -h dbserver -U testuser test

</code></pre>

h2. Post Installation Steps 

*DON'T FORGET TO REMOVE* @testuser@ *BEFORE GOING TO PRODUCTION*

<pre><code class="bash">

dropuser testuser

</code></pre>

*DON'T FORGET TO SETUP* @vacuum@ *BEFORE GOING TO PRODUCTION*

<pre><code class="bash">

vacuumdb -a -U postgres -z -v

</code></pre>

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@

<pre><code class="bash">

log_autovacuum_min_duration = 10

</code></pre>

you can check the settings of the database by

<pre><code class="bash">

su - postgres

psql test

</code></pre>

<pre><code class="bash">

psql# show all;

</code></pre>