Setup postgresql92 » Historie » Revision 3
Revision 2 (Jeremias Keihsler, 12.04.2019 15:26) → Revision 3/6 (Jeremias Keihsler, 12.04.2019 15:26)
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>