PostgreSQL Installation

Version 14.3 by Vincent Massol on 2015/07/25

Follow these instuctions:

  • Download and install PostgreSQL
  • Download the appropriate Postgres JDBC41 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)
  • 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'
      TABLESPACE = pg_default;

      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 ON SCHEMA public 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 -Oxwiki -Upostgres

      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>

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:

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

Performance Tuning

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

2013-04-03 18:44:36 EEST LOG:  checkpoints are occurring too frequently (22 seconds apart)
2013-04-03 18:44:36 EEST HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2013-04-03 18:46:05 EEST LOG:  checkpoints are occurring too frequently (6 seconds apart)
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.
More links related to this: 

Tags:
   

Get Connected