Documentation for Confluence 4.1.x. Documentation for earlier versions of Confluence is available too.

Skip to end of metadata
Comment: Corrected links that should have been relative instead of absolute.
Go to start of metadata

This document provides instructions for setting up Confluence for use with a PostgreSQL database. Please check the Known Issues for PostgreSQL before you start.

On this page:

1. Install PostgreSQL

 

To install PostgreSQL,

  1. Download the database software and installer from the PostgreSQLdownload site and save it to your desktop. Choose the package that matches your operating system. Where available, choose the One Click Installer. These instructions assume you will use the One Click Installer. For example:
  2. Run the installer. 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 db root level account.
    • 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 db port.
    • Choose the locale that best fits your geographic location, when prompted to enter a locale.
    • Do not launch Stack Builder at the completion of the installer.
  3. PostgreSQL is now installed on your machine.

2. Create a User and a Database

 

 

(info) All screenshots below are taken from a PostgreSQL configuration on a Windows machine.

To create a PostgreSQL user and database,

  1. Start the 'pgAdmin III' administration tool on your machine. The pgAdmin III administration console will display. The database user and database that will be used by Confluence are created via the 'pgAdmin III' tool.

  2. Connect to the PostgreSQL server (e.g. double-click on the server name in the object browser). Enter a 'postgres' password when prompted.


  3. Create a new user, i.e. login role (e.g. right-click click 'Login Roles' in the object browser and select 'New Login Role...'):
    • Enter a name and password for the new user.
    • Do not select any role privileges.

  4. Create a database (e.g. right-click 'Databases' and select 'New Database...'):
    • Enter a name for the new database.
    • Set the owner of the database to the user you created in the previous step.
    • Select 'UTF8' for 'Encoding'.
Creating a User and Database via Linux command-line

If you are on Linux and do not have the above pgAdmin III administration tool, you can use the command line interface instead. Assuming that you are using the default installation directory of /opt/PostgreSQL/8.3/bin/, enter the following commands:

sudo -s -H -u postgres
# Create the Confluence user:
/opt/PostgreSQL/8.3/bin/createuser -S -d -r -P -E confuser
# Create the Confluence database:
/opt/PostgreSQL/8.3/bin/createdb -O confuser confluence
exit

3. Configure Confluence to use the PostgreSQL Database

 

 

Once you have installed and set up PostgreSQL, you will need to configure Confluence to use the PostgreSQL database.

To configure Confluence to use PostgreSQL,

  1. Install Confluence, if you haven't done so already. Ensure that you download the Confluence distribution, not the evaluation installer.
  2. Ensure that Confluence is stopped (for example, by ensuring that the application server or service which is running Confluence has been stopped or terminated).

  3. Install the database drivers, if needed:

    PostgreSQL JDBC Drivers bundled with Confluence
    The JDBC drivers for this database are bundled with Confluence. You do not have to download or install any JDBC drivers to use this database with Confluence, if you are using a direct JDBC connection. If you are connecting via a datasource, you will still need to download and install the drivers manually. See Database JDBC drivers for more information on the bundled JDBC drivers.

    Note: Confluence only bundles the JDBC 3 driver which will work under the 1.6 JVM. However, if you are using Java 6 and want to use the JDBC 4 driver, you can download it via Database JDBC drivers and install it as described below. You will need to remove the existing PostgreSQL JDBC 3 driver (e.g. postgresql-8.4-701.jdbc3), if you do want to use the JDBC 4 driver.

    • If you are configuring a datasource to connect to your PostgreSQL database, you will need to place the jar file in <confluence install>/WEB-INF/lib (for Confluence 2.10 onwards) or <confluence install>/common/lib (for earlier versions). Information and links to the appropriate database drivers are available on Database JDBC drivers.
      (info) Windows renames .jar extensions to .zip! Just rename it back to .jar. You'll have to set your folder options to view hidden file extensions if you can't rename it without changing the file type (Tools >> Folder Options >> View >> Uncheck "Hide Extensions for known file types."
  4. Start Confluence and after entering your license code on the 'Confluence Setup Wizard' page, click 'Custom Installation'. The 'Choose a Database Configuration' page will display.
  5. Select 'PostgreSQL' and click 'External Database'. The 'Configure Database' page will display.
  6. Choose your desired database connection method (please note that if you choose to connect via datasource, you will need to install the appropriate database drivers as described in the previous step).

  7. Enter your PostgreSQL database setup details (as defined in the previous step above):

    Connecting to an SSL Database

    Simply add ssl=true parameter in the Database URL, for example:

    jdbc:postgresql://localhost:5432/confluence?ssl=true

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 and 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 as your SQL browser.

Troubleshooting

 

 
  • Known Issues for PostgreSQL contains common issues encountered when setting up your PostgreSQL database to work with Confluence.
  • 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 Confluence is complaining that it's missing a class file, you might have forgotten to place the jdbc driver in the WEB-INF/lib folder or possibly have placed it in the wrong folder.
  • 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-install/logs and confluence-data/logs).


 

 
 

6 Comments

Hide/Show Comments

  1. May 10, 2010

    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.

  2. May 10, 2010

    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.

  3. Jan 18, 2011

    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?

    1. Feb 07, 2011

      Martin Cleaver [blended perspectives]

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

  4. Feb 07, 2011

    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:

  5. Nov 20, 2011

    Anonymous

    You've hit the ball out the park! Incredbile!