Wiki source code of PostgreSQL Installation
Version 14.3 by Vincent Massol on 2015/07/25
Hide last authors
| author | version | line-number | content |
|---|---|---|---|
 |
14.2 | 1 | {{box cssClass="floatinginfobox" title="**Contents**"}} |
| 2 | {{toc/}} | ||
| 3 | {{/box}} | ||
| 4 | |||
| |
1.18 | 5 | Follow these instuctions: |
| 6 | |||
 |
11.7 | 7 | * Download and install [[PostgreSQL>>http://www.postgresql.org/]] |
 |
14.1 | 8 | * Download the appropriate [[Postgres JDBC41 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##) |
| |
1.1 | 9 | * Start PostgreSQL |
 |
12.1 | 10 | ** On Mac you could issue the following shell commands to start/stop PostgreSQL 9.1 (adapt to your version and to your setup):((( |
| |
11.7 | 11 | {{code}} |
| |
10.1 | 12 | sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data |
| 13 | sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl stop -D /Library/PostgreSQL/9.1/data | ||
| 14 | {{/code}} | ||
| 15 | ))) | ||
| |
7.1 | 16 | * Create the ##xwiki## user and the ##xwiki## database:((( |
| |
12.1 | 17 | * Using the ##psql## tool:((( |
| |
11.7 | 18 | In a shell, start the PostgreSQL interactive terminal: {{code}}psql -U <replace_with_your_admin_user_eg_postgres>{{/code}} |
| |
1.1 | 19 | |
| |
7.1 | 20 | Create the ##xwiki## database: |
| |
8.1 | 21 | |
| |
7.1 | 22 | {{code language="none"}} |
 |
1.10 | 23 | CREATE DATABASE xwiki |
| |
4.1 | 24 | WITH OWNER = <replace_with_your_admin_user_eg_postgres> |
| |
1.10 | 25 | ENCODING = 'UNICODE' |
| 26 | TABLESPACE = pg_default; | ||
| |
7.1 | 27 | {{/code}} |
| |
1.10 | 28 | |
| |
11.7 | 29 | Verify that the ##xwiki## database is listed in the available databases: {{code}}\l{{/code}} |
| |
7.1 | 30 | |
| |
11.7 | 31 | Connect to the ##xwiki## database: {{code}}\connect xwiki{{/code}} |
| |
5.1 | 32 | |
| |
11.7 | 33 | Create a ##xwiki## user: {{code language="none"}}CREATE USER xwiki PASSWORD 'xwiki' VALID UNTIL 'infinity';{{/code}} |
| |
7.1 | 34 | |
| |
11.7 | 35 | Verify that the ##xwiki## user is listed in the available users: {{code language="none"}}\du{{/code}} |
| |
1.16 | 36 | |
| |
11.7 | 37 | Give all the permissions to the ##xwiki## user: {{code}}GRANT ALL ON SCHEMA public TO xwiki;{{/code}} |
| |
7.1 | 38 | ))) |
| |
12.1 | 39 | * Using the ##createuser## and ##createdb## programs:((( |
| |
11.7 | 40 | {{info}} |
| |
7.1 | 41 | 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). |
| |
11.7 | 42 | {{/info}} |
| |
3.1 | 43 | |
| |
11.7 | 44 | Create the ##xwiki## user: {{code}}createuser xwiki -S -D -R -P -Upostgres{{/code}} |
| |
7.1 | 45 | |
| |
11.7 | 46 | Create the ##xwiki## database: {{code}}createdb xwiki -Eunicode -Oxwiki -Upostgres{{/code}} |
| |
7.1 | 47 | |
| |
11.7 | 48 | Note that if you need to remove this DB at some point you can issue:{{code}}dropdb -Upostgres xwiki{{/code}} |
| |
4.1 | 49 | ))) |
| |
7.1 | 50 | ))) |
| |
11.7 | 51 | * 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}} |
| |
11.8 | 52 | |
| |
14.3 | 53 | = Multiwiki Status = |
| |
11.8 | 54 | |
| |
13.1 | 55 | Prior to XWiki 4.5M1, multiwiki mode was not fully working on PostgreSQL. Since XWiki 4.5M1, we've made it work in ##schema## mode (i.e. a subwiki is represented as a Schema in the database). However it's not working in ##database## mode for the moment (i.e. a subwiki is represented as a Catalog in the database) because the PostGreSQL JDBC Driver doesn't support yet the ##setCatalog## method. |
| |
11.8 | 56 | |
| 57 | The mode used is controlled by a property in ##hibernate.cfg.xml##: | ||
| 58 | |||
| 59 | {{code}} | ||
| 60 | <property name="xwiki.virtual_mode">schema|database</property> | ||
| 61 | {{/code}} | ||
| 62 | |||
| |
12.1 | 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 |
