Difference between revisions of "Postgresql"

From SME Server
Jump to navigationJump to search
(Please warn before supplying dangerous commands to the reader)
Line 2: Line 2:
  
 
{{Note box|This howto are created with information extracted from forums. Proceed at your own risk. Work for me.}}
 
{{Note box|This howto are created with information extracted from forums. Proceed at your own risk. Work for me.}}
 +
 +
{{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.}}
  
 
Enable centosplus repository and install postgres (at the time writen this howto is 8.1.9 version)
 
Enable centosplus repository and install postgres (at the time writen this howto is 8.1.9 version)
  
 
  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
 
{{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
Line 21: Line 21:
 
  cp /var/lib/pgsql/data/postgresql.conf /etc/e-smith/templates-custom/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
 
  cp /var/lib/pgsql/data/pg_hba.conf /etc/e-smith/templates-custom/var/lib/pgsql/data
 
  
 
Now login as postgres user:
 
Now login as postgres user:

Revision as of 09:03, 4 November 2007

How to install Postgres 8

Important.png Note:
This howto are created with information extracted from forums. Proceed at your own risk. Work for me.



Warning.png Warning:
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.


Enable centosplus repository and install postgres (at the time writen this howto is 8.1.9 version)

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

Start postgres

/etc/init.d/postgresql start

Then make the necesary 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

Add a plus new white line at the end.

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 are all ok:

signal-event post-upgrade
signal-event reboot 


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 ok, you can enter at postgres console. Then exit:

\q

and exit from postgres user:

exit