Database Setup for PostgreSQL

This page provides instructions for configuring Confluence to use a PostgreSQL database.

Step 1. Check the prerequisites

Check the following before you start:

  • Check that your version of PostgreSQL is supported. See Supported Platforms. If your version is not supported, please upgrade to a supported version of PostgreSQL before installing Confluence.
  • If you have been evaluating Confluence and wish to transfer your data to a new database, consult the following guide first: Migrating to Another Database.
  • If you are migrating from another database, consult the following guide first: Migrating to Another Database.

Step 2. Install PostgreSQL

If you do not already have an operational PostgreSQL database, install it now.

  1. Download and install PostgreSQL. Please note the following information when installing PostgreSQL:
    • The password that you are prompted to provide during the installation process is for the 'postgres' account, which is the database root-level account, sometimes called the super user ('postgres'). Remember this username and password. You will need it each time you log in to the database.
    • The default port for PostgreSQL is 5432. If you decide to change the default port, please ensure that your new port number does not conflict with any services running on that port. You will also need to remember to update all further mentions of the database port.
    • Choose the locale that best fits your geographic location.
    • Do not launch Stack Builder at the completion of the installer.

Step 3. Set up your PostgreSQL database and user

Next you need to create a database within PostgreSQL to hold your Confluence data, and a database user with authority to access that database. 

  1. Create a database user (for example confluenceuser)
    • Your new user must be able to create database objects and create roles
  2. Create a database (for example confluence)
    • Owner is your new database user (for example confluenceuser)
    • Character encoding should be utf8 encoding.

You can use pgAdmin as an alternative to the command line to complete this step.  If you used the graphical installer when installing PostgreSQL, pgAdmin will be already installed on your computer.

Step 4. Install Confluence and the PostgreSQL database driver

Decide whether you will set up a direct JDBC connection or a datasource connection to PostgreSQL, to suit your environment. If unsure, choose direct JDBC.

Install Confluence if you have not done so already. See the Confluence Installation Guide.

  • If you plan to set up a direct JDBC connection to PostgreSQL, you can run the Confluence installation and move directly on to the Confluence Setup Wizard, as described below. The PostgreSQL JDBC driver is bundled with Confluence, as documented on this page: Database JDBC Drivers.
  • If you plan to set up a datasource connection to PostgreSQL:
    • Stop immediately after the Confluence installation, before opening the Confluence Setup Wizard in your browser. If you have already got part-way through the Confluence Setup Wizard, stop at the database setup step. You will be able to restart the setup wizard at the same step later.
    • Follow the steps described in Configuring a PostgreSQL Datasource in Apache Tomcat.

Step 5. Set up your database connection in the Confluence Setup Wizard

Start Confluence, go to the Confluence Setup Wizard in your browser, and follow these steps:

  1. When prompted to choose an evaluation or production installation, choose production installation.
  2. When prompted to choose an embedded or external database, select PostgreSQL from the dropdown list and choose External Database.
  3. Choose either the direct JDBC or the datasource connection, to suit the choice you made earlier.
    • For the JDBC connection:
      • When prompted for a Driver Class Name, enter:

      • When prompted for the Database URL, use this format:

        For example:  jdbc:postgresql://localhost:5432/confluence

        Note: If you need to connect to an SSL database, add the ssl=true parameter in the database URL. For example: jdbc:postgresql://localhost:5432/confluence?ssl=true

      • Enter the username (for example confluenceuser) and password you chose earlier when setting up your Confluence database.
    • For a datasource connection: Set the Datasource Name to the following: java:comp/env/jdbc/confluence

That's it - Confluence is now using your PostgreSQL database to store its data.

Screenshot: Setting up the PostgreSQL JDBC connection in the Confluence Setup Wizard

Notes

  • If the server that is hosting the PostgreSQL database is not the same server as Confluence, then please ensure that the Confluence server can contact the database server. Please also refer to the PostgreSQL documentation on how to set up pg_hba.conf. If the pg_hba.conf file is not set properly, remote communication to the PostgresSQL server will fail.
  • Running SQL queries: For ongoing maintenance of your server, you can continue to use PGAdmin III as your SQL browser.

Troubleshooting

  • If you get the following error message, verify that you have given the confluenceuser user all the required database permissions when connecting from localhost.

    Could not successfully test your database: : Server connection failure during transaction. Due to underlying exception: 'java.sql.SQLException: Access denied for user 'confluenceuser'@'localhost' (using password: YES)'
  • If Confluence complains that it is missing a class file, you may have placed the JDBC driver in the wrong folder.
  • If you are unable to connect to the database from Confluence and they are on different machines, most likely you have a firewall in between the two machines or your pg_hba.conf file is misconfigured. Verify that your firewall is set to allow connections through 5432 or double check your hba configuration.

  • If none of the above describes your issue, please create a support ticket at http://support.atlassian.com and be sure to include your logs (found in <CONFLUENCE-INSTALLATION>/logs and <CONFLUENCE-HOME>/logs).

Was this helpful?

Thanks for your feedback!

18 Archived comments

  1. User avatar

    Tin Pham

    Confluence 3.2 does not include the JDBC4 driver. According to the PostgreSQL website, "If you are using the 1.6 JVM, then you should use the JDBC4 version.". To use JDBC4,

    1. Delete the postgresql-8.4-701.jdbc3.jar from <confluence-install-path>/confluence/WEB-INF/lib
    2. Download the JDBC4 driver postgresql-8.4-701.jdbc4.jar from PostgreSQL website.
    3. Copy the jdbc4 driver to <confluence-install-path>/confluence/WEB-INF/lib

      I am actually not sure step 1 of deleted the jdbc3 jar is required, but I figure that the libraries will collide. Perhaps a Confluece admin can verify this for me.

    11 May 2010
  2. User avatar

    Tin Pham

    I suggest adding instructions about ensuring that the Operating System has the utf-8 locale setup. utf-8 is required if you want the database to be able to support languages around the world.

    Here are my notes for Ubuntu,

    Select a Locale

    If you used a hosting company like Slice or Rackspace chances are the server image they provided is minimal and has no locale set. This is bad because it will cause problems with Perl (used to install quite a few things) and in my case it cause me head-aches setting up PostgreSQL.

    locale # Look at your current locale.

    If the results show an error or the locale is not set up for UTF-8 then you must run the following commands. If you are not from Canada, you can choose another language, just make sure to use UTF-8.

    sudo locale-gen en_CA.UTF-8
    sudo update-locale LANG=en_CA.UTF-8

    Finally, if you changed the locale, logoff and then back on for the changes to take effect. Verify again using the locale command.

    11 May 2010
  3. User avatar

    Anonymous

    How to change database settings? This article tells how to start installation. But I am already using confluence and want to migrate to a production database. How to do that?

    18 Jan 2011
    1. User avatar

      Martin Cleaver [Blended Perspectives]

      Anonymous - See Migrating to Another Database (& use an id when asking questions so you can be notified when someone replies!)

      07 Feb 2011
  4. User avatar

    Martin Cleaver [Blended Perspectives]

    The "Creating a User and Database via UNIX command-line"* *instructions are not clear whether this invocation will create the databases as UTF-8, and whether its dependent on the operating system having the locale set to UTF8.

    The following command will disambiguate for you:

    07 Feb 2011
  5. User avatar

    Adam Nofsinger

    When I tried to install PostgresSQL 9 on Windows Server 2008 R2 64-bit, the installer would fail at the very end saying that it failed to initialize the database cluster. I read a few things that had suggested fixes, but they all dealt with a user account postgres that I never got created on my install.  I was able to get around this by creating a data folder ahead of time C:\PostgresSQLData\ and setting permissions on that folder to read/write for Everyone. Then during the installation, I selected this folder as the location for the database files.

    I think it has something to do with UAC and trying to put the database files in the C:\Program Files\ folder.

    28 Feb 2013
  6. User avatar

    Florian Heigl

    It would be REALLY helpful to add the utf8 setting for postgres LC_CTYPE at creation time in this manual. Instead of wasting people's time by letting them find out later.

    06 Mar 2013
    1. User avatar

      Adam Nofsinger

      What is it exactly that I'm going to find out later?

      06 Mar 2013
      1. User avatar

        Anonymous

        that you might wanna use:

        createdb -T template0 -E utf8 -O dbuser dbname

        instead of just

        createdb -O dbuser dbname

         

        Otherwise the database might be running with C locale (see other comments about how to verify)

        06 Mar 2013
        1. User avatar

          Adam Nofsinger

          Ah, thanks.  I guess I don't need to worry then. I followed the instructions that use the GUI tool, and they mention the UTF-8 setting.

          06 Mar 2013
  7. User avatar

    Vick Khera

    The GUI create user method says to not grant any other privileges to the user, but the command line method specifies -d -r which enable permission to create new databases and roles. Those are probably not desired.

    Also for createdb, you want to ensure you get UTF8 by specifying -E UTF8 to the command line. Depending on the default configurations of your Postgres, you may also need to specify -T template0 to get it to work.

    Finally, as a matter of style, you do not need to su to postgres, you can specify -U postgres on the commands themselves.

    18 Apr 2013
  8. User avatar

    Susan sparks

    The latest Confluence installer for linux seems to be skipping over the "Configure Database" part entirely. I choose production confluence in the beginning, next generate a trial key, next setup admin account and next confluence is 'done'. At no point is there anything related to the DB in the setup wizard.

    11 Sep 2014
    1. User avatar

      Rachel Robins [Atlassian Tech Writer]

      Hi Susan, this is a known issue with 5.6 I believe, essentially the installer is recognising your evaluation license and setting up a trial installation for you, with the embedded database. There's more details here  CONF-34799 - Installer keeps setting up embedded database Resolved .  A fix will be available very soon. 

      15 Sep 2014
  9. User avatar

    Robert Lauriston at WebAction

    At least in some environments, before you can complete the "Configure Database" portion of Confluence installation, you have to edit /var/lib/pgsql/data/pg_hba.conf and change ident" to "trust" as per:
    Bamboo Database (PostgreSQL) setup wizard error - Ident authentication failed for user

    30 Oct 2014
  10. User avatar

    Robert Lauriston at WebAction

    Here's my terse cheat sheet for CentOS:
    sudo sh
    yum install postgresql-server

    service postgresql initdb
    chkconfig postgresql on
    reboot
    - PostgreSQL driver is bundled with Confluence, nothing to install
    sudo -u postgres psql
    create user confluence with password 'whatever' superuser;
    \du - list users
    create database confluence owner confluence encoding 'utf8';
    \list - list databases
    \quit
    sudo -sh
    vi /var/lib/pgsql/data/pg_hba.conf
    - change "ident" to "trust" as per: Bamboo Database (PostgreSQL) setup wizard error - Ident authentication failed for user
    /etc/init.d/postgresql restart

    30 Oct 2014
    1. User avatar

      Ryan Goodwin

      Thank you for the pg_hba.conf step. Don't know why this isn't in the original documentation, but you save me some serious time and effort.

      26 Dec 2014
    1. User avatar

      Tom Samplonius

      Never ever use "trust", as it disables all user validation.  It basically trusts that you are whatever user you say you are.

      Use "md5". 

      06 Jan 2015
      1. User avatar

        Robert Lauriston at WebAction

        Only trusted users have access to my PostgreSQL instance, which is used only by Confluence. Nothing else is running on that CentOS instance.

        06 Jan 2015
Powered by Confluence and Scroll Viewport