Difference between revisions of "Postgresql"

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

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

yum  update smeserver-postgresql --enablerepo=smecontribs

yum remove smeserver-postgresql  postgresql*

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

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

@@ 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
- rpm -q --whatrequires perl-CGI-Session perl-DBD-Pg
-If that answers no packages need ... you can safely 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 of writing this is version 8.1.9)
+=== 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 install, ignore the post-upgrade and restart message
+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 necessary 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
- 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.
-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
-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: <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>
 ----
-[[Category:Howto]]
+[[Category:Contrib]]
 [[Category:Administration]]


postgresql logo
Url	https://www.postgresql.org/
Licence	AGPLv3
Category	database
Tags	database, db, pgsql

ID	Product	Version	Status	Summary (3 tasks) ⇒
12366	SME Contribs	10.0	UNCONFIRMED	datestyle on en_US needs to be mdy, not dmy.
12355	SME Contribs	10.0	UNCONFIRMED	Shutdown errors detected and logged during shutdown of Postgres 13.
12083	SME Contribs	10.0	CONFIRMED	NFR rewrite contrib to be able to run multiple pgsql version at once

Difference between revisions of "Postgresql"

Latest revision as of 17:42, 12 December 2022

Contents

Version

Install Postgresql 13

Upgrade

Uninstall

Manually restore db

Bugs

Changelog

References

Navigation menu

Search