PostgreSQL Installation (XWiki.org)

= Installation =

6

7

You have 2 options:

8

9

* Install PostgreSQL on your machine manually

10

* Use the official PostgreSQL docker image

11

12

== Manual Installation ==

13

14

Follow these instructions:

15

16

* Download and install [[PostgreSQL>>http://www.postgresql.org/]] (see [[Database support strategy>>dev:Community.DatabaseSupportStrategy]] for the supported versions).

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##)

18

* Start PostgreSQL

19

** On Mac you could issue the following shell commands to start/stop PostgreSQL 9.1 (adapt to your version and to your setup):(((

20

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

24

)))

25

* Create the ##xwiki## user and the ##xwiki## database:(((

26

* Using the ##psql## tool:(((

27

In a shell, start the PostgreSQL interactive terminal: {{code}}psql -U <replace_with_your_admin_user_eg_postgres>{{/code}}

28

29

Create the ##xwiki## database:

30

31

32

CREATE DATABASE xwiki

33

WITH OWNER = <replace_with_your_admin_user_eg_postgres>

34

ENCODING = 'UNICODE'

35

LOCALE_PROVIDER = 'builtin'

36

LOCALE = 'C.UTF-8'

37

TABLESPACE = pg_default

38

TEMPLATE = template0;

39

40

41

On Postgres versions before 17, use ##C.utf8## as ##LOCALE## and omit the ##LOCALE_PROVIDER##.

42

43

Verify that the ##xwiki## database is listed in the available databases: {{code}}\l{{/code}}

44

45

Connect to the ##xwiki## database: {{code}}\connect xwiki{{/code}}

46

47

Create a ##xwiki## user: {{code language="none"}}CREATE USER xwiki PASSWORD 'xwiki' VALID UNTIL 'infinity';{{/code}}

48

49

Verify that the ##xwiki## user is listed in the available users: {{code language="none"}}\du{{/code}}

50

51

Give all the permissions to the ##xwiki## user: {{code}}GRANT ALL PRIVILEGES ON DATABASE xwiki TO xwiki;{{/code}}

52

)))

53

* Using the ##createuser## and ##createdb## programs:(((

54

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).

56

57

58

Create the ##xwiki## user: {{code}}createuser xwiki -S -D -R -P -Upostgres{{/code}}

59

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}}.

61

62

Note that if you need to remove this DB at some point you can issue:{{code}}dropdb -Upostgres xwiki{{/code}}

63

)))

64

)))

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}}

== Using Docker ==

* Install Docker

* Run the following command (update to your needs):(((

71

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

73

74

)))

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##)

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}}

= Multiwiki Status =

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.

81

82

The mode used is controlled by a property in ##hibernate.cfg.xml##:

83

84

85

<property name="xwiki.virtual_mode">schema|database</property>

86

87

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

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".

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:

102

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

= Troubleshooting =

== 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

114

** then use ##dpkg ~-~-install libpostgresql-jdbc-java_9.2-1002-1_all.deb## to install

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

120

== Relation "hibernate_sequence" already exists ==

121

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.

Wiki source code of PostgreSQL Installation

Quick Links

Navigation

About

About

Support

Platform

User Guide

Admin Guide

Developer Guide

Projects

XWiki

Extensions

Other

Contribute

Status

Practices

Under the Hood

Get Involved

Get Connected

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
		9	* Install PostgreSQL on your machine manually
		10	* Use the official PostgreSQL docker image
		11
		12	== Manual Installation ==
		13
		14	Follow these instructions:
		15
		16	* Download and install [[PostgreSQL>>http://www.postgresql.org/]] (see [[Database support strategy>>dev:Community.DatabaseSupportStrategy]] for the supported versions).
		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##)
		18	* Start PostgreSQL
		19	** On Mac you could issue the following shell commands to start/stop PostgreSQL 9.1 (adapt to your version and to your setup):(((
		20	{{code}}
		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	)))
		25	* Create the ##xwiki## user and the ##xwiki## database:(((
		26	* Using the ##psql## tool:(((
		27	In a shell, start the PostgreSQL interactive terminal: {{code}}psql -U <replace_with_your_admin_user_eg_postgres>{{/code}}
		28
		29	Create the ##xwiki## database:
		30
		31	{{code language="none"}}
		32	CREATE DATABASE xwiki
		33	WITH OWNER = <replace_with_your_admin_user_eg_postgres>
		34	ENCODING = 'UNICODE'
		35	LOCALE_PROVIDER = 'builtin'
		36	LOCALE = 'C.UTF-8'
		37	TABLESPACE = pg_default
		38	TEMPLATE = template0;
		39	{{/code}}
		40
		41	On Postgres versions before 17, use ##C.utf8## as ##LOCALE## and omit the ##LOCALE_PROVIDER##.
		42
		43	Verify that the ##xwiki## database is listed in the available databases: {{code}}\l{{/code}}
		44
		45	Connect to the ##xwiki## database: {{code}}\connect xwiki{{/code}}
		46
		47	Create a ##xwiki## user: {{code language="none"}}CREATE USER xwiki PASSWORD 'xwiki' VALID UNTIL 'infinity';{{/code}}
		48
		49	Verify that the ##xwiki## user is listed in the available users: {{code language="none"}}\du{{/code}}
		50
		51	Give all the permissions to the ##xwiki## user: {{code}}GRANT ALL PRIVILEGES ON DATABASE xwiki TO xwiki;{{/code}}
		52	)))
		53	* Using the ##createuser## and ##createdb## programs:(((
		54	{{info}}
		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).
		56	{{/info}}
		57
		58	Create the ##xwiki## user: {{code}}createuser xwiki -S -D -R -P -Upostgres{{/code}}
		59
		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}}.
		61
		62	Note that if you need to remove this DB at some point you can issue:{{code}}dropdb -Upostgres xwiki{{/code}}
		63	)))
		64	)))
		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}}
		66
		67	== Using Docker ==
		68
		69	* Install Docker
		70	* Run the following command (update to your needs):(((
		71	{{code language="bash"}}
		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
		73	{{/code}}
		74	)))
		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##)
		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
		78	= Multiwiki Status =
		79
		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.
		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
		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:
		102
		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
		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
		114	** then use ##dpkg ~-~-install libpostgresql-jdbc-java_9.2-1002-1_all.deb## to install
		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
		120	== Relation "hibernate_sequence" already exists ==
		121
		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.