| Line 1: |
Line 1: |
| − | There is now a smeserver-postgresql contrib in the contribs repo.
| + | {{Languages}} |
| − | It requires postgresql 13 from the postgresql repo.
| + | <!-- 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= databases |
| | + | |tags=databases |
| | + | }} |
| | | | |
| − | <br>
| + | ===Version=== |
| − | ==Version== | |
| | {{#smeversion:smeserver-postgresql}} | | {{#smeversion:smeserver-postgresql}} |
| − | <br>
| |
| − | <br>
| |
| − | <br>
| |
| | | | |
| − | ==How to install Postgresql 13== | + | ===Install Postgresql 13=== |
| | | | |
| − | Install
| + | yum install smeserver-extrarepositories-pgsql -y |
| − | | |
| − | yum install smeserver-extrarepositories-pgsql | |
| | yum --enablerepo=smecontribs,pgsql13 install smeserver-postgresql | | yum --enablerepo=smecontribs,pgsql13 install smeserver-postgresql |
| | signal-event post-upgrade;signal-event reboot | | signal-event post-upgrade;signal-event reboot |
| Line 21: |
Line 30: |
| | signal-event yum-modify | | signal-event yum-modify |
| | | | |
| − | == How to install Postgres 8 == | + | === Upgrade === |
| − | | + | yum update {{#var:smecontribname}} {{#var:contribname}} --enablerepo=smecontribs |
| − | {{Warning box| '''Tis information is now outdated/deprecated as we now use systemd on Koozali SME Server v10'''}} | |
| − | | |
| − | {{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:
| |
| − | | |
| − | 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=== | + | === Uninstall === |
| − | To test if you have set postgres password correctly:
| + | yum remove {{#var:smecontribname}} {{#var:contribname}} |
| | | | |
| − | su postgres
| + | === Bugs === |
| − | psql -U postgres -d template1
| + | 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}} |
| | | | |
| − | and enter the password you have set above. If all is correct, you should enter the postgres console. Then exit:
| + | 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.}} |
| | | | |
| − | \q
| + | ===Changelog=== |
| | + | Only released version in smecontrib are listed here. |
| | | | |
| − | and exit from postgres user:
| + | {{#smechangelog: {{#var:smecontribname}} }} |
| | | | |
| − | exit
| |
| | | | |
| − | | + | ===References=== |
| − | References: <ol><li>[http://forums.contribs.org/index.php?topic=38250.msg173298#msg173298 RE: How do I install postgreSQL 8+] by hedererjs</li><li> | + | <ol><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> | | [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
| |
| − |
| |
| − |
| |
| − | == How to install Postgres 12 - old method ==
| |
| − |
| |
| − | This is an old method and the systemd method below should be used
| |
| − |
| |
| − | {{Note box|This howto is intended for use with SME Server 10, although it may work for other versions.}}
| |
| − |
| |
| − | Postgres 12 is one of the latest versions available. The directions here may apply to other versions as well. The installation of version 12 uses RPMs directly from the Postgres support site. The links below come from https://yum.postgresql.org/12/redhat/rhel-7-x86_64/repoview/postgresqldbserver12.group.html but you can adjust the 12 in this link to a different version and get a different version's installation.
| |
| − |
| |
| − | First, it is necessary to install the dependencies as well as the contrib RPM.<syntaxhighlight>
| |
| − | yum install https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-libs-12.5-1PGDG.rhel7.x86_64.rpm
| |
| − | yum install https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-12.5-1PGDG.rhel7.x86_64.rpm
| |
| − | yum install https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-contrib-12.5-1PGDG.rhel7.x86_64.rpm
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | Next, install the server portion.<syntaxhighlight>
| |
| − | yum install https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-server-12.5-1PGDG.rhel7.x86_64.rpm
| |
| − | </syntaxhighlight>Initialize the database.<syntaxhighlight>
| |
| − | /usr/pgsql-12/bin/postgresql-12-setup initdb
| |
| − | </syntaxhighlight>If you have a previously installed version of Postgres, you will need to stop the service for it before starting the service for the new version. Note that the new service name incorporates the version number. It can be started and enabled like this:<syntaxhighlight>
| |
| − | systemctl start postgresql-12
| |
| − | systemctl enable postgresql-12
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − |
| |
| − | ==Koozali SME v10 and manual install with systemd==
| |
| − |
| |
| − | {{Note box| There is now a contrib for Koozali SME Server v10 - See above for [[Postgres#How_to_install_Postgresql_13|easy installation]]}}
| |
| − |
| |
| − | This is for reference with PostGresql12 and manual installation:
| |
| − |
| |
| − | db yum_repositories set postgresql12 repository \
| |
| − | BaseURL https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7Server-\$basearch/ \
| |
| − | EnableGroups no \
| |
| − | GPGCheck yes \
| |
| − | Name "Postgresql-12" \
| |
| − | GPGKey https://download.postgresql.org/pub/repos/yum/RPM-GPG-KEY-PGDG-12 \
| |
| − | Visible no \
| |
| − | status disabled
| |
| − |
| |
| − | Update yum
| |
| − | signal-event yum-modify
| |
| − |
| |
| − |
| |
| − | Install the rpms
| |
| − | yum --enablerepo=postgresql12 install postgresql12-server postgresql12-contrib
| |
| − |
| |
| − | Set a config key for SME to recognise the service:
| |
| − | config set postgresql-12 service status enabled access private
| |
| − |
| |
| − | We will need a file:
| |
| − |
| |
| − | mkdir -p /usr/lib/systemd/system/postgresql-12.service.d
| |
| − | nano /usr/lib/systemd/system/postgresql-12.service.d/50koozali.conf
| |
| − |
| |
| − | Add something like this:
| |
| − |
| |
| − | [Unit]
| |
| − | After=network.target network.service wan.service
| |
| − | [Install]
| |
| − | WantedBy=sme-server.target
| |
| − |
| |
| − | Reconfigure and reboot:
| |
| − | signal-event post-upgrade;signal-event reboot
| |
| − |
| |
| − | The service will show as failed at this point.
| |
| − | systemctl status postgresql-12
| |
| − |
| |
| − | So we need to init the DB:
| |
| − | /usr/pgsql-12/bin/postgresql-12-setup initdb
| |
| − |
| |
| − | Then start it:
| |
| − | systemctl start postgresql-12
| |
| − |
| |
| − | And now we can see it running.
| |
| − |
| |
| − | We can check some of the defaults:
| |
| − |
| |
| − | grep '^[[:blank:]]*[^[:blank:]#;]' /var/lib/pgsql/12/data/pg_hba.conf
| |
| − | grep '^[[:blank:]]*[^[:blank:]#;]' /var/lib/pgsql/12/data/postgresql.conf
| |
| − |
| |
| | ---- | | ---- |
| | [[Category:Contrib]] | | [[Category:Contrib]] |
| | [[Category:Administration]] | | [[Category:Administration]] |
