DokuWiki » Infrastructure » Operating System » CentOS 7

h1. Install Procedure for PostgreSQL 9.2

h2. Requirements

To install postgresql you will need the following:

* a installed and supported operating system (e.g. CentOS 7.x)

* root-access

* a fast internet connection

h2. Preliminary Note

This procedure is based on a documentation supplied by 

* https://www.if-not-true-then-false.com/2012/install-postgresql-on-fedora-centos-red-hat-rhel/

* https://www.linode.com/docs/databases/postgresql/how-to-install-postgresql-relational-databases-on-centos-7/

h2. Install 

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

<pre><code class="bash">

yum install postgresql postgresql-server postgresql-libs postgresql-contrib postgresql-devel

</code></pre>

h2. Configure PostgreSQL 9.2 Database Server

Initialize the cluster first with initdb command:

<pre><code class="bash">

/usr/bin/postgresql-setup initdb

</code></pre>

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

<pre><code class="bash">

vim /var/lib/pgsql/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/data/pg_hba.conf file:

<pre><code class="bash">

vim /var/lib/pgsql/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.2 start

</code></pre>

automatically start the service at boot time:

<pre><code class="bash">

/sbin/chkconfig postgresql-9.2 on

</code></pre>

you can check the runlevels by

<pre><code class="bash">

/sbin/chkconfig --list postgresql-9.2

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