Wiki source code of PostgreSQL Installation
Last modified by Michael Hamann on 2024/11/06
Hide last authors
| author | version | line-number | content |
|---|---|---|---|
 |
14.2 | 1 | {{box cssClass="floatinginfobox" title="**Contents**"}} |
| 2 | {{toc/}} | ||
| 3 | {{/box}} | ||
| 4 | |||
| |
16.1 | 5 | = Installation = |
| |
1.18 | 6 | |
| |
16.1 | 7 | You have 2 options: |
| |
19.1 | 8 | |
| |
16.1 | 9 | * Install PostgreSQL on your machine manually |
| 10 | * Use the official PostgreSQL docker image | ||
| 11 | |||
| 12 | == Manual Installation == | ||
| 13 | |||
| 14 | Follow these instructions: | ||
| 15 | |||
| |
20.2 | 16 | * Download and install [[PostgreSQL>>http://www.postgresql.org/]] (see [[Database support strategy>>dev:Community.DatabaseSupportStrategy]] for the supported versions). |
| |
23.2 | 17 | * Download the appropriate [[Postgres JDBC 4.2 driver>>https://jdbc.postgresql.org/download/]] and copy the JAR into your container's common lib directory or in the XWiki webapp (in ##WEB-INF/lib##) |
| |
1.1 | 18 | * Start PostgreSQL |
 |
12.1 | 19 | ** 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 | 20 | {{code}} |
| |
10.1 | 21 | sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data |
| 22 | sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl stop -D /Library/PostgreSQL/9.1/data | ||
| 23 | {{/code}} | ||
| 24 | ))) | ||
| |
7.1 | 25 | * Create the ##xwiki## user and the ##xwiki## database:((( |
| |
12.1 | 26 | * Using the ##psql## tool:((( |
| |
11.7 | 27 | In a shell, start the PostgreSQL interactive terminal: {{code}}psql -U <replace_with_your_admin_user_eg_postgres>{{/code}} |
| |
1.1 | 28 | |
| |
7.1 | 29 | Create the ##xwiki## database: |
| |
8.1 | 30 | |
| |
7.1 | 31 | {{code language="none"}} |
 |
1.10 | 32 | CREATE DATABASE xwiki |
| |
4.1 | 33 | WITH OWNER = <replace_with_your_admin_user_eg_postgres> |
| |
1.10 | 34 | ENCODING = 'UNICODE' |
 |
24.1 | 35 | LOCALE_PROVIDER = 'builtin' |
| 36 | LOCALE = 'C.UTF-8' | ||
| |
25.1 | 37 | TABLESPACE = pg_default |
| 38 | TEMPLATE = template0; | ||
| |
7.1 | 39 | {{/code}} |
| |
1.10 | 40 | |
| |
25.1 | 41 | On Postgres versions before 17, use ##C.utf8## as ##LOCALE## and omit the ##LOCALE_PROVIDER##. |
| 42 | |||
| |
11.7 | 43 | Verify that the ##xwiki## database is listed in the available databases: {{code}}\l{{/code}} |
| |
7.1 | 44 | |
| |
11.7 | 45 | Connect to the ##xwiki## database: {{code}}\connect xwiki{{/code}} |
| |
5.1 | 46 | |
| |
11.7 | 47 | Create a ##xwiki## user: {{code language="none"}}CREATE USER xwiki PASSWORD 'xwiki' VALID UNTIL 'infinity';{{/code}} |
| |
7.1 | 48 | |
| |
11.7 | 49 | Verify that the ##xwiki## user is listed in the available users: {{code language="none"}}\du{{/code}} |
| |
1.16 | 50 | |
| |
24.2 | 51 | Give all the permissions to the ##xwiki## user: {{code}}GRANT ALL PRIVILEGES ON DATABASE xwiki TO xwiki;{{/code}} |
| |
7.1 | 52 | ))) |
| |
12.1 | 53 | * Using the ##createuser## and ##createdb## programs:((( |
| |
11.7 | 54 | {{info}} |
| |
7.1 | 55 | 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 | 56 | {{/info}} |
| |
3.1 | 57 | |
| |
11.7 | 58 | Create the ##xwiki## user: {{code}}createuser xwiki -S -D -R -P -Upostgres{{/code}} |
| |
7.1 | 59 | |
| |
25.1 | 60 | Create the ##xwiki## database: {{code}}createdb xwiki -Eunicode --locale-provider=builtin --builtin-locale=C.UTF-8 -Oxwiki -Upostgres --template template0{{/code}}. On PostgreSQL versions before 17, use {{code}}createdb xwiki -Eunicode --locale=C.utf8 -Oxwiki -Upostgres --template template0{{/code}}. |
| |
7.1 | 61 | |
| |
11.7 | 62 | Note that if you need to remove this DB at some point you can issue:{{code}}dropdb -Upostgres xwiki{{/code}} |
| |
4.1 | 63 | ))) |
| |
7.1 | 64 | ))) |
| |
11.7 | 65 | * 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 | 66 | |
| |
16.1 | 67 | == Using Docker == |
| 68 | |||
| |
23.3 | 69 | * Install Docker |
| |
16.1 | 70 | * Run the following command (update to your needs):((( |
| 71 | {{code language="bash"}} | ||
| |
25.1 | 72 | 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 --locale-provider=builtin --locale=C.UTF-8" -v postgres-data:/var/lib/postgresql/data -p 5432:5432 postgres:17 |
| |
16.1 | 73 | {{/code}} |
| 74 | ))) | ||
| |
25.1 | 75 | * Download the appropriate [[Postgres JDBC driver>>https://jdbc.postgresql.org/download/]]. You can also download it directly from the [[Maven Central Repository>>https://repo1.maven.org/maven2/org/postgresql/postgresql/]] and copy the JAR into your container's common lib directory or in the XWiki webapp (in ##WEB-INF/lib##) |
| |
16.1 | 76 | * 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}} |
| 77 | |||
| |
14.3 | 78 | = Multiwiki Status = |
| |
11.8 | 79 | |
| |
13.1 | 80 | 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 | 81 | |
| 82 | The mode used is controlled by a property in ##hibernate.cfg.xml##: | ||
| 83 | |||
| 84 | {{code}} | ||
| 85 | <property name="xwiki.virtual_mode">schema|database</property> | ||
| 86 | {{/code}} | ||
| 87 | |||
| |
12.1 | 88 | = Performance Tuning = |
| 89 | |||
| 90 | In several cases, for example when rolling back a document to a previous version, your postgres log will show something similar: | ||
| 91 | |||
| 92 | {{code}} | ||
| 93 | 2013-04-03 18:44:36 EEST LOG: checkpoints are occurring too frequently (22 seconds apart) | ||
| 94 | 2013-04-03 18:44:36 EEST HINT: Consider increasing the configuration parameter "checkpoint_segments". | ||
| 95 | 2013-04-03 18:46:05 EEST LOG: checkpoints are occurring too frequently (6 seconds apart) | ||
| 96 | 2013-04-03 18:46:05 EEST HINT: Consider increasing the configuration parameter "checkpoint_segments". | ||
| 97 | |||
| 98 | {{/code}} | ||
| 99 | |||
| 100 | This can result in slow DB performance. If using a production environment, you must set the "checkpoint_segments" parameter from your PostgreSQL configuration. | ||
| 101 | More links related to this: | ||
| |
19.1 | 102 | |
| |
12.1 | 103 | * http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server |
| 104 | * http://www.postgresql.org/docs/current/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS | ||
| |
15.1 | 105 | |
| 106 | = Troubleshooting = | ||
| 107 | |||
| 108 | == Method org.postgresql.jdbc4.Jdbc4Connection.isValid(int) is not yet implemented == | ||
| 109 | |||
| 110 | Versions of the Postgres JDBC driver older than 9.2 do not support that method. Try to update the ##postgresql-*.jar## | ||
| 111 | |||
| 112 | * if installed from debian package, try to update at least to the version from jessy | ||
| 113 | ** for example download from https://packages.debian.org/jessie/all/libpostgresql-jdbc-java/download | ||
| |
24.2 | 114 | ** then use ##dpkg ~-~-install libpostgresql-jdbc-java_9.2-1002-1_all.deb## to install |
| |
15.1 | 115 | * alternatively if you use tomcat8, try downgrading to tomcat7 |
| 116 | * otherwise find the ##postgresql-*.jar## in your webapp and replace it by a newer version downloaded from https://jdbc.postgresql.org/download.html | ||
| 117 | |||
| 118 | Restart the server to apply the changes. | ||
| 119 | |||
| |
17.1 | 120 | == Relation "hibernate_sequence" already exists == |
| 121 | |||
 |
18.1 | 122 | If you see this error in your postgres logs, you should know that it's normal and expected. Because of a [[bug in Hibernate>>https://github.com/xwiki/xwiki-platform/blob/1d8e1172757a75cda44398820505dacf384e0a56/xwiki-platform-core/xwiki-platform-oldcore/src/main/java/com/xpn/xwiki/store/XWikiHibernateBaseStore.java#L576-L576]] we need to create this sequence if it doesn't exist and it's [[hard to check that in a DB-independent way>>https://jira.xwiki.org/browse/XWIKI-14055]]. Thus we simply create the sequence and if it already exists you get this error at startup in your logs. |
