Line 1: |
Line 1: |
− | == How to install Postgres 8 == | + | {{Languages}} |
| + | <!-- here we define the contrib name variable --> |
| + | <!-- we get the page title, remove suffix for translated version; if needed you can define there with the value you want--> |
| + | {{#vardefine:contribname| {{lc: {{#titleparts: {{BASEPAGENAME}} |1}} }} }} |
| + | {{#vardefine:smecontribname| smeserver-{{lc: {{#titleparts: {{BASEPAGENAME}} |1}} }} }} |
| + | <!-- we define the language --> |
| + | {{#vardefine:lang| {{lc: {{#titleparts: {{PAGENAME}} | | -1}} }} |en }} |
| + | {{Infobox contribs |
| + | |name={{#var:contribname}} |
| + | |image=Postgresql.png |
| + | |description_image= {{#var:contribname}} logo |
| + | |maintainer= |
| + | |licence= AGPLv3 |
| + | |url= https://www.postgresql.org/ |
| + | |category= database |
| + | |tags=database,db,pgsql |
| + | }} |
| | | |
− | {{Note box|This howto is created with information extracted from forums. Proceed at your own risk.}} | + | ===Version=== |
| + | {{#smeversion:smeserver-postgresql}} |
| | | |
| + | ===Install Postgresql 13=== |
| | | |
− | {{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.
| + | yum install smeserver-extrarepositories-pgsql -y |
− | 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:
| + | yum --enablerepo=smecontribs,pgsql13 install smeserver-postgresql |
| | | |
− | rom -q --whatrequires perl-CGI-Session perl-DBD-Pg
| |
| | | |
− | If that answers no packages need ... you can savely remove these packages
| + | enable repo to allow further updates |
| + | db yum_repositories setprop pgsql13 status enabled |
| + | signal-event yum-modify |
| | | |
− | yum remove perl-CGI-Session perl-DBD-Pg}} | + | === Upgrade === |
| + | yum update {{#var:smecontribname}} --enablerepo=smecontribs |
| | | |
− | Enable centosplus repository and install postgres (at the time writen this howto is 8.1.9 version)
| + | === Uninstall === |
| + | yum remove {{#var:smecontribname}} {{#var:contribname}}* |
| | | |
− | yum --enablerepo=centosplus install postgresql postgresql-contrib postgresql-devel postgresql-docs postgresql-pl postgresql-python postgresql-test
| + | === Manually restore db === |
| + | after you upgraded you server or migrate, here how to restore db |
| | | |
− | Start postgres
| + | using psql<syntaxhighlight lang="bash"> |
| + | exec chpst -u postgres /usr/pgsql-13/bin/psql < /home/e-smith/db/pgsql/pgsql.dump |
| + | </syntaxhighlight>using pg_restore |
| | | |
− | /etc/init.d/postgresql start
| + | <syntaxhighlight lang="bash"> |
| + | exec chpst -u postgres /usr/pgsql-13/bin/pg_restore < /home/e-smith/db/pgsql/pgsql.dump |
| + | </syntaxhighlight> |
| | | |
− | Then make the necesary custom templates:
| + | === Bugs === |
| + | Please raise bugs under the SME-Contribs section in [http://bugs.contribs.org/enter_bug.cgi bugzilla] |
| + | and select the {{#var:smecontribname}} component or use {{BugzillaFileBug|product=SME%20Contribs|component={{#var:smecontribname}}|title=this link}} |
| | | |
− | mkdir -p /etc/e-smith/templates/var/lib/pgsql/data
| + | Below is an overview of the current issues for this contrib:{{#bugzilla:columns=id,product,version,status,summary|sort=id|order=desc|component={{#var:smecontribname}} |noresultsmessage=No open bugs found.}} |
− | 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:
| + | ===Changelog=== |
| + | Only released version in smecontrib are listed here. |
| | | |
− | su postgres
| + | {{#smechangelog: {{#var:smecontribname}} }} |
| | | |
− | Then access to superuser database, set a password and exit.
| |
| | | |
− | psql -U postgres -d template1
| + | ===References=== |
− | alter user postgres with encrypted password 'add_your_pass_here';
| + | <ol><li>[http://forums.contribs.org/index.php?topic=38250.msg173298#msg173298 RE: How do I install postgreSQL 8+] by hedererjs</li><li> |
− | \q
| + | [http://www.issociate.de/board/post/272215/Fwd:_resetting_superuser_password.html Resetting the postgres superuser password]</li></ol> |
− | exit
| + | ---- |
− | | + | [[Category:Contrib]] |
− | Edit the file '''/etc/e-smith/templates-custom/var/lib/pgsql/data/postgresql.conf''' remove # and change the following settings:
| + | [[Category:Administration]] |
− | | |
− | 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
| |
− | | |
− | | |
− | References: [http://forums.contribs.org/index.php?topic=38250.msg173298#msg173298][http://www.issociate.de/board/post/272215/Fwd:_resetting_superuser_password.html]
| |