Projekt

Allgemein

Profil

Aktionen
Historie

Wiki » Setup »

Setup postgresql92 » Historie » Revision 2

« Zurück | Revision 2/6 (Vergleich) | Weiter »
Jeremias Keihsler, 12.04.2019 15:26

Install Procedure for PostgreSQL 9.2

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

Preliminary Note

This procedure is based on a documentation supplied by

Install

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

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

Configure PostgreSQL 9.2 Database Server

Initialize the cluster first with initdb command:

/usr/bin/postgresql-setup initdb

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

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

automatically start the service at boot time:

/sbin/chkconfig postgresql-9.2 on

you can check the runlevels by

/sbin/chkconfig --list postgresql-9.2

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;

Von Jeremias Keihsler vor mehr als 7 Jahren aktualisiert · 6 Revisionen