Difference between revisions of "Postgresql"

From SME Server
Jump to navigationJump to search
(15 intermediate revisions by 6 users not shown)
Line 1: Line 1:
 +
{{Note box|For SME Server 9.x, 64-bit, we advise you to use [[Software_Collections:PostgreSQL]]}}
 +
 
== How to install Postgres 8 ==
 
== How to install Postgres 8 ==
  
{{Note box|This howto are created with information extracted from forums. Proceed at your own risk. Work for me.}}
+
{{Note box|This howto is created with information extracted from forums. Proceed at your own risk.}}
 +
 
 +
 
 +
{{Warning box|The command below will install '''postgres''' and update '''postgres-libs'''. For dependency resolution it will also install '''tcl''' and '''mx''' from the '''base''' repository.
 +
If you see other packages, such as '''perl''' or '''php''', '''abort the installation'''. This might occur because you have installed '''perl-CGI-Session''' or '''perl-DBD-Pg'''. These two packages might be on your system because you have installed '''otrs''' or another aplication. You can check if they are still needed:
  
Enable centosplus repository and install postgres (at the time writen this howto is 8.1.9 version)
+
rpm -q --whatrequires perl-CGI-Session perl-DBD-Pg
 +
 
 +
If that answers no packages need ... you can safely remove these packages
 +
 
 +
yum remove perl-CGI-Session perl-DBD-Pg}}
 +
 
 +
Enable centosplus repository and install postgres (at the time of writing this is version 8.1.9)
  
 
  yum --enablerepo=centosplus install postgresql postgresql-contrib postgresql-devel postgresql-docs postgresql-pl postgresql-python postgresql-test
 
  yum --enablerepo=centosplus install postgresql postgresql-contrib postgresql-devel postgresql-docs postgresql-pl postgresql-python postgresql-test
 +
After install, ignore the post-upgrade and restart message
  
{{Warning box|The above command install '''postgres''' and update '''postgres-libs''', and install for dependency '''tcl''' and '''mx''' from '''base''' repository. If you see other packages, such as '''perl''' or '''php''', '''abort the installation now!'''. This occur because maybe you have installed '''perl-CGI-Session''' or '''perl-DBD-Pg'''. These two packages are in your system because you have installed '''otrs''' or another aplication. In my case I have remove these two packages (because I not need '''otrs''' any more), and run again the installation.}}
+
===Start postgres===
 
 
Start postgres
 
  
 
  /etc/init.d/postgresql start
 
  /etc/init.d/postgresql start
  
Then make the necesary custom templates:
+
Then make the necessary custom templates:
  
 
  mkdir -p /etc/e-smith/templates/var/lib/pgsql/data
 
  mkdir -p /etc/e-smith/templates/var/lib/pgsql/data
Line 56: Line 67:
  
 
  host    all    all    0.0.0.0 255.255.255.255  reject
 
  host    all    all    0.0.0.0 255.255.255.255  reject
 +
  
Add a plus new white line at the end.
+
'''Be sure to add an extra white line at the end.That why you got an extra line on above box!'''
  
 
Expand template:
 
Expand template:
Line 74: Line 86:
 
  ln -s /etc/init.d/postgresql /etc/rc.d/rc6.d/K03postgresql
 
  ln -s /etc/init.d/postgresql /etc/rc.d/rc6.d/K03postgresql
  
To ensure are all ok:
+
To ensure everything is all right:
  
 
  signal-event post-upgrade
 
  signal-event post-upgrade
  signal-event reboot  
+
  signal-event reboot
 
 
  
 +
===Testing your environment===
 
To test if you have set postgres password correctly:
 
To test if you have set postgres password correctly:
  
Line 85: Line 97:
 
  psql -U postgres -d template1
 
  psql -U postgres -d template1
  
and enter the password you have set above. If ok, you can enter at postgres console. Then exit:
+
and enter the password you have set above. If all is correct, you should enter the postgres console. Then exit:
  
 
  \q
 
  \q
Line 92: Line 104:
  
 
  exit
 
  exit
 +
 +
 +
References: <ol></li><li>[http://forums.contribs.org/index.php?topic=38250.msg173298#msg173298 RE: How do I install postgreSQL 8+] by hedererjs</li><li>
 +
[http://www.issociate.de/board/post/272215/Fwd:_resetting_superuser_password.html Resetting the postgres superuser password]</li></ol>
 +
 +
==How to Remove==
 +
rpm -e postgresql.i386 postgresql-contrib postgresql-devel postgresql-docs postgresql-pl postgresql-test  postgresql-server postgresqlclient7  postgresql-python
 +
 +
 +
----
 +
[[Category:Howto]]
 +
[[Category:Administration]]

Revision as of 17:11, 6 January 2015

Important.png Note:
For SME Server 9.x, 64-bit, we advise you to use Software_Collections:PostgreSQL


How to install Postgres 8

Important.png Note:
This howto is created with information extracted from forums. Proceed at your own risk.



Warning.png Warning:
The command below will install postgres and update postgres-libs. For dependency resolution it will also install tcl and mx from the base repository.

If you see other packages, such as perl or php, abort the installation. This might occur because you have installed perl-CGI-Session or perl-DBD-Pg. These two packages might be on your system because you have installed otrs or another aplication. You can check if they are still needed:

rpm -q --whatrequires perl-CGI-Session perl-DBD-Pg

If that answers no packages need ... you can safely remove these packages

yum remove perl-CGI-Session perl-DBD-Pg


Enable centosplus repository and install postgres (at the time of writing this is version 8.1.9)

yum --enablerepo=centosplus install postgresql postgresql-contrib postgresql-devel postgresql-docs postgresql-pl postgresql-python postgresql-test

After install, ignore the post-upgrade and restart message

Start postgres

/etc/init.d/postgresql start

Then make the necessary custom templates:

mkdir -p /etc/e-smith/templates/var/lib/pgsql/data
mkdir -p /etc/e-smith/templates-custom/var/lib/pgsql/data
cp /var/lib/pgsql/data/postgresql.conf /etc/e-smith/templates/var/lib/pgsql/data
cp /var/lib/pgsql/data/pg_hba.conf /etc/e-smith/templates/var/lib/pgsql/data
cp /var/lib/pgsql/data/postgresql.conf /etc/e-smith/templates-custom/var/lib/pgsql/data
cp /var/lib/pgsql/data/pg_hba.conf /etc/e-smith/templates-custom/var/lib/pgsql/data

Now login as postgres user:

su postgres

Then access to superuser database, set a password and exit.

psql -U postgres -d template1
alter user postgres with encrypted password 'add_your_pass_here';
\q
exit

Edit the file /etc/e-smith/templates-custom/var/lib/pgsql/data/postgresql.conf remove # and change the following settings:

superuser_reserved_connections=2
ssl = on
password_encryption = on
listen_addresses = 'localhost'

Then copy your server SSL keys:

cd /var/lib/pgsql/data
cp /etc/httpd/conf/ssl.crt/server.crt .
cp /etc/httpd/conf/ssl.key/server.key .
chown postgres:postgres server.*

Edit the file /etc/e-smith/templates-custom/var/lib/pgsql/data/pg_hba.conf and change the following settings:

local    all   all                    md5
host     all   all    127.0.0.1/32    md5
host     all   all    ::1/128         md5

and add at the end:

host    all    all    0.0.0.0 255.255.255.255  reject

Be sure to add an extra white line at the end.That why you got an extra line on above box!

Expand template:

expand-template /var/lib/pgsql/data/pg_hba.conf
expand-template /var/lib/pgsql/data/postgresql.conf

Restart with new settings:

/etc/init.d/postgresql stop
/etc/init.d/postgresql start

and add the symlinks to start automatically:

ln -s /etc/init.d/postgresql /etc/rc.d/rc7.d/S56postgresql
ln -s /etc/init.d/postgresql /etc/rc.d/rc6.d/K03postgresql

To ensure everything is all right:

signal-event post-upgrade
signal-event reboot

Testing your environment

To test if you have set postgres password correctly:

su postgres
psql -U postgres -d template1

and enter the password you have set above. If all is correct, you should enter the postgres console. Then exit:

\q

and exit from postgres user:

exit


References:

  1. RE: How do I install postgreSQL 8+ by hedererjs
  2. Resetting the postgres superuser password

How to Remove

rpm -e postgresql.i386 postgresql-contrib postgresql-devel postgresql-docs postgresql-pl postgresql-test  postgresql-server postgresqlclient7  postgresql-python