Wiki source code of PostgreSQL Installation
Version 16.1 by Vincent Massol on 2017/03/02
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | {{box cssClass="floatinginfobox" title="**Contents**"}} | ||
| 2 | {{toc/}} | ||
| 3 | {{/box}} | ||
| 4 | |||
| 5 | = Installation = | ||
| 6 | |||
| 7 | You have 2 options: | ||
| 8 | * Install PostgreSQL on your machine manually | ||
| 9 | * Use the official PostgreSQL docker image | ||
| 10 | |||
| 11 | == Manual Installation == | ||
| 12 | |||
| 13 | Follow these instructions: | ||
| 14 | |||
| 15 | * Download and install [[PostgreSQL>>http://www.postgresql.org/]] | ||
| 16 | * 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##) | ||
| 17 | * Start PostgreSQL | ||
| 18 | ** On Mac you could issue the following shell commands to start/stop PostgreSQL 9.1 (adapt to your version and to your setup):((( | ||
| 19 | {{code}} | ||
| 20 | sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data | ||
| 21 | sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl stop -D /Library/PostgreSQL/9.1/data | ||
| 22 | {{/code}} | ||
| 23 | ))) | ||
| 24 | * Create the ##xwiki## user and the ##xwiki## database:((( | ||
| 25 | * Using the ##psql## tool:((( | ||
| 26 | In a shell, start the PostgreSQL interactive terminal: {{code}}psql -U <replace_with_your_admin_user_eg_postgres>{{/code}} | ||
| 27 | |||
| 28 | Create the ##xwiki## database: | ||
| 29 | |||
| 30 | {{code language="none"}} | ||
| 31 | CREATE DATABASE xwiki | ||
| 32 | WITH OWNER = <replace_with_your_admin_user_eg_postgres> | ||
| 33 | ENCODING = 'UNICODE' | ||
| 34 | TABLESPACE = pg_default; | ||
| 35 | {{/code}} | ||
| 36 | |||
| 37 | Verify that the ##xwiki## database is listed in the available databases: {{code}}\l{{/code}} | ||
| 38 | |||
| 39 | Connect to the ##xwiki## database: {{code}}\connect xwiki{{/code}} | ||
| 40 | |||
| 41 | Create a ##xwiki## user: {{code language="none"}}CREATE USER xwiki PASSWORD 'xwiki' VALID UNTIL 'infinity';{{/code}} | ||
| 42 | |||
| 43 | Verify that the ##xwiki## user is listed in the available users: {{code language="none"}}\du{{/code}} | ||
| 44 | |||
| 45 | Give all the permissions to the ##xwiki## user: {{code}}GRANT ALL ON SCHEMA public TO xwiki;{{/code}} | ||
| 46 | ))) | ||
| 47 | * Using the ##createuser## and ##createdb## programs:((( | ||
| 48 | {{info}} | ||
| 49 | 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). | ||
| 50 | {{/info}} | ||
| 51 | |||
| 52 | Create the ##xwiki## user: {{code}}createuser xwiki -S -D -R -P -Upostgres{{/code}} | ||
| 53 | |||
| 54 | Create the ##xwiki## database: {{code}}createdb xwiki -Eunicode -Oxwiki -Upostgres{{/code}} | ||
| 55 | |||
| 56 | Note that if you need to remove this DB at some point you can issue:{{code}}dropdb -Upostgres xwiki{{/code}} | ||
| 57 | ))) | ||
| 58 | ))) | ||
| 59 | * 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}} | ||
| 60 | |||
| 61 | == Using Docker == | ||
| 62 | |||
| 63 | * Install Docker | ||
| 64 | * Run the following command (update to your needs):((( | ||
| 65 | {{code language="bash"}} | ||
| 66 | docker run --name pgtest -e POSTGRES_PASSWORD=xwiki -e POSTGRES_ROOT_PASSWORD=xwiki -e POSTGRES_USER=xwiki -e POSTGRES_DB=xwiki -e POSTGRES_INITDB_ARGS="--encoding=UTF8" -v postgres-data:/var/lib/postgresql/data -p 5432:5432 postgres:9.5 | ||
| 67 | {{/code}} | ||
| 68 | ))) | ||
| 69 | * 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##) | ||
| 70 | * 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}} | ||
| 71 | |||
| 72 | = Multiwiki Status = | ||
| 73 | |||
| 74 | 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. | ||
| 75 | |||
| 76 | The mode used is controlled by a property in ##hibernate.cfg.xml##: | ||
| 77 | |||
| 78 | {{code}} | ||
| 79 | <property name="xwiki.virtual_mode">schema|database</property> | ||
| 80 | {{/code}} | ||
| 81 | |||
| 82 | = Performance Tuning = | ||
| 83 | |||
| 84 | In several cases, for example when rolling back a document to a previous version, your postgres log will show something similar: | ||
| 85 | |||
| 86 | {{code}} | ||
| 87 | 2013-04-03 18:44:36 EEST LOG: checkpoints are occurring too frequently (22 seconds apart) | ||
| 88 | 2013-04-03 18:44:36 EEST HINT: Consider increasing the configuration parameter "checkpoint_segments". | ||
| 89 | 2013-04-03 18:46:05 EEST LOG: checkpoints are occurring too frequently (6 seconds apart) | ||
| 90 | 2013-04-03 18:46:05 EEST HINT: Consider increasing the configuration parameter "checkpoint_segments". | ||
| 91 | |||
| 92 | {{/code}} | ||
| 93 | |||
| 94 | This can result in slow DB performance. If using a production environment, you must set the "checkpoint_segments" parameter from your PostgreSQL configuration. | ||
| 95 | More links related to this: | ||
| 96 | * http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server | ||
| 97 | * http://www.postgresql.org/docs/current/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS | ||
| 98 | |||
| 99 | = Troubleshooting = | ||
| 100 | |||
| 101 | == Method org.postgresql.jdbc4.Jdbc4Connection.isValid(int) is not yet implemented == | ||
| 102 | |||
| 103 | Versions of the Postgres JDBC driver older than 9.2 do not support that method. Try to update the ##postgresql-*.jar## | ||
| 104 | |||
| 105 | * if installed from debian package, try to update at least to the version from jessy | ||
| 106 | ** for example download from https://packages.debian.org/jessie/all/libpostgresql-jdbc-java/download | ||
| 107 | ** then use ##dpkg ~--install libpostgresql-jdbc-java_9.2-1002-1_all.deb## to install | ||
| 108 | ** | ||
| 109 | * alternatively if you use tomcat8, try downgrading to tomcat7 | ||
| 110 | * otherwise find the ##postgresql-*.jar## in your webapp and replace it by a newer version downloaded from https://jdbc.postgresql.org/download.html | ||
| 111 | |||
| 112 | Restart the server to apply the changes. |
