Database Setup for PostgreSQL
- 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.
- Create a database user (for example
confluenceuser)- Your new user must be able to create database objects and must have can login permission.
- Create a database (for example
confluence)- Owner is your new database user (for example
confluenceuser) - Character encoding should be
utf8encoding.
- Owner is your new database user (for example
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
- When prompted to choose an evaluation or production installation, choose production installation.
- When prompted to choose an embedded or external database, select PostgreSQL from the dropdown list and choose External Database.
- 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/confluenceNote: If you need to connect to an SSL database, add the
ssl=trueparameter 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.conffile 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 theconfluenceuseruser all the required database permissions when connecting fromlocalhost.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.conffile is misconfigured. Verify that your firewall is set to allow connections through 5432 or double check your hba configuration.- The following page contains common issues encountered when setting up your PostgreSQL database to work with Confluence: Known issues for PostgreSQL.
- 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>/logsand<).CONFLUENCE-HOME>/logs