Difference between revisions of "Postgresql"

From SME Server
Jump to navigationJump to search
(Please warn before supplying dangerous commands to the reader and be sure to merge changes with changes made by others)
 
(35 intermediate revisions by 9 users not shown)
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 are created with information extracted from forums. Proceed at your own risk. Work for me.}}
+
===Version===
 +
{{#smeversion:smeserver-postgresql}}
  
 +
===Install Postgresql 13===
  
−
{{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.}}
+
yum install smeserver-extrarepositories-pgsql -y
 +
yum --enablerepo=smecontribs,pgsql13 install smeserver-postgresql
  
−
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
+
enable repo to allow further updates
 +
  db yum_repositories setprop pgsql13 status enabled
 +
signal-event yum-modify
  
−
Start postgres
+
=== Upgrade ===
 +
yum  update {{#var:smecontribname}} --enablerepo=smecontribs
  
−
  /etc/init.d/postgresql start
+
=== Uninstall ===
 +
  yum remove {{#var:smecontribname}}  {{#var:contribname}}*
  
−
Then make the necesary custom templates:
+
=== Manually restore db ===
 +
after you upgraded you server or migrate, here how to restore db
  
−
mkdir -p /etc/e-smith/templates/var/lib/pgsql/data
+
using psql<syntaxhighlight lang="bash">
−
mkdir -p /etc/e-smith/templates-custom/var/lib/pgsql/data
+
exec chpst -u postgres /usr/pgsql-13/bin/psql < /home/e-smith/db/pgsql/pgsql.dump
−
cp /var/lib/pgsql/data/postgresql.conf /etc/e-smith/templates/var/lib/pgsql/data
+
</syntaxhighlight>using pg_restore
−
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:
+
<syntaxhighlight lang="bash">
 +
exec chpst -u postgres /usr/pgsql-13/bin/pg_restore < /home/e-smith/db/pgsql/pgsql.dump
 +
</syntaxhighlight>
  
−
su postgres
+
=== 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}}
  
−
Then access to superuser database, set a password and 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.}}
  
−
psql -U postgres -d template1
+
===Changelog===
−
alter user postgres with encrypted password 'add_your_pass_here';
+
Only released version in smecontrib are listed here.
−
\q
 
−
exit
 
  
−
Edit the file '''/etc/e-smith/templates-custom/var/lib/pgsql/data/postgresql.conf''' remove # and change the following settings:
+
{{#smechangelog: {{#var:smecontribname}} }}
  
−
superuser_reserved_connections=2
 
−
ssl = on
 
−
password_encryption = on
 
−
listen_addresses = 'localhost'
 
  
−
Then copy your server SSL keys:
+
===References===
−
 
+
  <ol><li>[http://forums.contribs.org/index.php?topic=38250.msg173298#msg173298 RE: How do I install postgreSQL 8+] by hedererjs</li><li>
−
cd /var/lib/pgsql/data
+
[http://www.issociate.de/board/post/272215/Fwd:_resetting_superuser_password.html Resetting the postgres superuser password]</li></ol>
−
cp /etc/httpd/conf/ssl.crt/server.crt .
+
----
−
cp /etc/httpd/conf/ssl.key/server.key .
+
[[Category:Contrib]]
−
chown postgres:postgres server.*
+
[[Category:Administration]]
−
 
 
−
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]
 

Latest revision as of 17:42, 12 December 2022

Geographylogo.png Languages: 

English





postgresql
Postgresql.png
postgresql logo
Urlhttps://www.postgresql.org/
LicenceAGPLv3
Category

database

Tags database, db, pgsql


Version

Contrib 10:
smeserver-postgresql
The latest version of smeserver-postgresql is available in the SME repository, click on the version number(s) for more information.


Install Postgresql 13

yum install smeserver-extrarepositories-pgsql -y
yum --enablerepo=smecontribs,pgsql13 install smeserver-postgresql


enable repo to allow further updates 

db yum_repositories setprop pgsql13 status enabled
signal-event yum-modify

Upgrade

yum  update smeserver-postgresql --enablerepo=smecontribs

Uninstall

yum remove smeserver-postgresql  postgresql*

Manually restore db

after you upgraded you server or migrate, here how to restore db

using psql

exec chpst -u postgres /usr/pgsql-13/bin/psql < /home/e-smith/db/pgsql/pgsql.dump

using pg_restore 

exec chpst -u postgres /usr/pgsql-13/bin/pg_restore < /home/e-smith/db/pgsql/pgsql.dump

Bugs

Please raise bugs under the SME-Contribs section in bugzilla and select the smeserver-postgresql component or use this link


Below is an overview of the current issues for this contrib:

IDProductVersionStatusSummary (3 tasks) ⇒
12366SME Contribs10.0UNCONFIRMEDdatestyle on en_US needs to be mdy, not dmy.
12355SME Contribs10.0UNCONFIRMEDShutdown errors detected and logged during shutdown of Postgres 13.
12083SME Contribs10.0CONFIRMEDNFR rewrite contrib to be able to run multiple pgsql version at once

Changelog

Only released version in smecontrib are listed here.

smeserver-postgresql Changelog: SME 10 (smecontribs)
2022/12/26 Jean-Philippe Pialasse 0.2.0-4.sme
- fix failing pgsql.init service preventing restore [SME: 12290]
2022/12/12 Jean-Philippe Pialasse 0.2.0-3.sme
- fix pre-backup event fails error 256 [SME: 12264]
2022/12/02 Jean-Philippe Pialasse 0.2.0-2.sme
- fix preset OUT missing
2021/11/17 Michel Begue 0.2.0-1
- First import to Sme 10 (postgresql13)

2007/09/11 Charlie Brady 0.0.1-24
- Add support for localhost/private/public access settings to

determine which network interfaces to bind to.


References

  1. RE: How do I install postgreSQL 8+ by hedererjs
  2. Resetting the postgres superuser password
Retrieved from "https://wiki.koozali.org/index.php?title=Postgresql&oldid=41864"