Wiki source code of PostgreSQL Installation
Version 12.1 by Sorin Burjan on 2013/04/03
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | Follow these instuctions: | ||
| 2 | |||
| 3 | * Download and install [[PostgreSQL>>http://www.postgresql.org/]] | ||
| 4 | * Download the appropriate [[Postgres JDBC4 driver>>http://jdbc.postgresql.org/download.html]]. You can also download it directly from the [[Maven Central Repository>>http://repo1.maven.org/maven2/postgresql/postgresql/]] and copy the JAR into your container's common lib directory or in the XWiki webapp (in ##WEB-INF/lib##) | ||
| 5 | * Start PostgreSQL | ||
| 6 | ** On Mac you could issue the following shell commands to start/stop PostgreSQL 9.1 (adapt to your version and to your setup):((( | ||
| 7 | {{code}} | ||
| 8 | sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data | ||
| 9 | sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl stop -D /Library/PostgreSQL/9.1/data | ||
| 10 | {{/code}} | ||
| 11 | ))) | ||
| 12 | * Create the ##xwiki## user and the ##xwiki## database:((( | ||
| 13 | * Using the ##psql## tool:((( | ||
| 14 | In a shell, start the PostgreSQL interactive terminal: {{code}}psql -U <replace_with_your_admin_user_eg_postgres>{{/code}} | ||
| 15 | |||
| 16 | Create the ##xwiki## database: | ||
| 17 | |||
| 18 | {{code language="none"}} | ||
| 19 | CREATE DATABASE xwiki | ||
| 20 | WITH OWNER = <replace_with_your_admin_user_eg_postgres> | ||
| 21 | ENCODING = 'UNICODE' | ||
| 22 | TABLESPACE = pg_default; | ||
| 23 | {{/code}} | ||
| 24 | |||
| 25 | Verify that the ##xwiki## database is listed in the available databases: {{code}}\l{{/code}} | ||
| 26 | |||
| 27 | Connect to the ##xwiki## database: {{code}}\connect xwiki{{/code}} | ||
| 28 | |||
| 29 | Create a ##xwiki## user: {{code language="none"}}CREATE USER xwiki PASSWORD 'xwiki' VALID UNTIL 'infinity';{{/code}} | ||
| 30 | |||
| 31 | Verify that the ##xwiki## user is listed in the available users: {{code language="none"}}\du{{/code}} | ||
| 32 | |||
| 33 | Give all the permissions to the ##xwiki## user: {{code}}GRANT ALL ON SCHEMA public TO xwiki;{{/code}} | ||
| 34 | ))) | ||
| 35 | * Using the ##createuser## and ##createdb## programs:((( | ||
| 36 | {{info}} | ||
| 37 | Make sure that the ##createuser## and ##createdb## programs are in your ##$PATH##. The example below also assumes that the ##postgres## user exists in your setup (this is the default on Linux). | ||
| 38 | {{/info}} | ||
| 39 | |||
| 40 | Create the ##xwiki## user: {{code}}createuser xwiki -S -D -R -P -Upostgres{{/code}} | ||
| 41 | |||
| 42 | Create the ##xwiki## database: {{code}}createdb xwiki -Eunicode -Oxwiki -Upostgres{{/code}} | ||
| 43 | |||
| 44 | Note that if you need to remove this DB at some point you can issue:{{code}}dropdb -Upostgres xwiki{{/code}} | ||
| 45 | ))) | ||
| 46 | ))) | ||
| 47 | * Tell XWiki to use this database. To do this, edit the ##WEB-INF/hibernate.cfg.xml## file where you have expanded the XWiki WAR file and uncomment the PostgreSQL part. Make sure to review the ##connection.url## property. For example a typical value would be:{{code}}<property name="connection.url">jdbc:postgresql://localhost:5432/xwiki</property>{{/code}} | ||
| 48 | |||
| 49 | == Multiwiki Status == | ||
| 50 | |||
| 51 | Prior to XWiki 4.5M1, multiwiki mode was not fully working specifically database creation was not working. The implementation was trying to create one database for each subwiki. This is what we call the ##database## mode. You could still use XWiki on PostGreSQL but you had to manually create the databases. | ||
| 52 | |||
| 53 | Since XWiki 4.5M1, we've switched by default to use the ##schema## mode, i.e. a subwiki is represented as a Schema in the database. | ||
| 54 | |||
| 55 | The mode used is controlled by a property in ##hibernate.cfg.xml##: | ||
| 56 | |||
| 57 | {{code}} | ||
| 58 | <property name="xwiki.virtual_mode">schema|database</property> | ||
| 59 | {{/code}} | ||
| 60 | |||
| 61 | If you use the ##database## mode, be aware that [[the issue still exists>>http://jira.xwiki.org/browse/XWIKI-8753]]. | ||
| 62 | |||
| 63 | = Performance Tuning = | ||
| 64 | |||
| 65 | In several cases, for example when rolling back a document to a previous version, your postgres log will show something similar: | ||
| 66 | |||
| 67 | {{code}} | ||
| 68 | 2013-04-03 18:44:36 EEST LOG: checkpoints are occurring too frequently (22 seconds apart) | ||
| 69 | 2013-04-03 18:44:36 EEST HINT: Consider increasing the configuration parameter "checkpoint_segments". | ||
| 70 | 2013-04-03 18:46:05 EEST LOG: checkpoints are occurring too frequently (6 seconds apart) | ||
| 71 | 2013-04-03 18:46:05 EEST HINT: Consider increasing the configuration parameter "checkpoint_segments". | ||
| 72 | |||
| 73 | {{/code}} | ||
| 74 | |||
| 75 | This can result in slow DB performance. If using a production environment, you must set the "checkpoint_segments" parameter from your PostgreSQL configuration. | ||
| 76 | More links related to this: | ||
| 77 | * http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server | ||
| 78 | * http://www.postgresql.org/docs/current/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS |
