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 must have can login permission. 
  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 2022-11-29_04-39-46_Configuring a PostgreSQL Datasource in Apache Tomcat.

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

  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:

        org.postgresql.Driver
      • When prompted for the Database URL, use this format:

        jdbc:postgresql://<server>:<port>/<database>
        

        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.

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).
Last modified on Sep 5, 2017

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.