Installation

You have 2 options:

• Install PostgreSQL on your machine manually
• Use the official PostgreSQL docker image

Manual Installation

Follow these instructions:

• Download and install PostgreSQL (see Database support strategy for the supported versions).
• Download the appropriate Postgres JDBC 4.2 driver and copy the JAR into your container's common lib directory or in the XWiki webapp (in WEB-INF/lib)
• Start PostgreSQL
  • On Mac you could issue the following shell commands to start/stop PostgreSQL 9.1 (adapt to your version and to your setup):
    sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl start -D /Library/PostgreSQL/9.1/data
    sudo -u postgres /Library/PostgreSQL/9.1/bin/pg_ctl stop -D /Library/PostgreSQL/9.1/data
• Create the xwiki user and the xwiki database:
  • Using the psql tool:

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

    Create the xwiki database:

    CREATE DATABASE xwiki
    WITH OWNER = <replace_with_your_admin_user_eg_postgres>
    ENCODING = 'UNICODE'
    LOCALE_PROVIDER = 'builtin'
    LOCALE = 'C.UTF-8'
    TABLESPACE = pg_default
    TEMPLATE = template0;

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

    Verify that the xwiki database is listed in the available databases: \l

    Connect to the xwiki database: \connect xwiki

    Create a xwiki user: CREATE USER xwiki PASSWORD 'xwiki' VALID UNTIL 'infinity';

    Verify that the xwiki user is listed in the available users: \du

    Give all the permissions to the xwiki user: GRANT ALL PRIVILEGES ON DATABASE xwiki TO xwiki;

  • Using the createuser and createdb programs:

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

    Create the xwiki user: createuser xwiki -S -D -R -P -Upostgres

    Create the xwiki database: createdb xwiki -Eunicode --locale-provider=builtin --builtin-locale=C.UTF-8 -Oxwiki -Upostgres --template template0
    . On PostgreSQL versions before 17, use createdb xwiki -Eunicode --locale=C.utf8 -Oxwiki -Upostgres --template template0
    .

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

• 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:<property name="connection.url">jdbc:postgresql://localhost:5432/xwiki</property>

Using Docker

• Install Docker
• Run the following command (update to your needs):
  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
• Download the appropriate Postgres JDBC driver. You can also download it directly from the Maven Central Repository and copy the JAR into your container's common lib directory or in the XWiki webapp (in WEB-INF/lib)
• 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:<property name="connection.url">jdbc:postgresql://localhost:5432/xwiki</property>

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.

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

Performance Tuning

In several cases, for example when rolling back a document to a previous version, your postgres log will show something similar:

This can result in slow DB performance. If using a production environment, you must set the "checkpoint_segments" parameter from your PostgreSQL configuration.
More links related to this: 

• http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
• 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

Versions of the Postgres JDBC driver older than 9.2 do not support that method. Try to update the postgresql-*.jar

• if installed from debian package, try to update at least to the version from jessy
  • for example download from https://packages.debian.org/jessie/all/libpostgresql-jdbc-java/download
  • then use dpkg --install libpostgresql-jdbc-java_9.2-1002-1_all.deb to install
• alternatively if you use tomcat8, try downgrading to tomcat7
• otherwise find the postgresql-*.jar in your webapp and replace it by a newer version downloaded from  https://jdbc.postgresql.org/download.html

Restart the server to apply the changes.

Relation "hibernate_sequence" already exists

If you see this error in your postgres logs, you should know that it's normal and expected. Because of a bug in Hibernate we need to create this sequence if it doesn't exist and it's hard to check that in a DB-independent way. Thus we simply create the sequence and if it already exists you get this error at startup in your logs.