fd Blog

Daniel Hilgarth on software development

Setting Up PostgreSQL for Confluence

These are the steps to setup a new Confluence database or to move a Confluence database to a new server. The steps are the same for other Atlassian products. The only difference is the role name and the database name.

Create new Confluence database

  1. Switch to the correct user:

     # sudo -i -u postgres
  2. Connect to local database server:

     $ psql
  3. Create role:

     postgres=# create role confluenceuser with login password '<password>' valid until 'infinity';
  4. Create database:

     postgres=# create database confluence with encoding='utf-8' owner=confluenceuser connection limit=-1;
  5. Exit PostgreSQL and switch back to your own user:

     postgres=# \q
     $ exit

Move existing database to new server

On the old server, create a dump of the database.

  1. Switch to the correct user:

     # sudo -i -u postgres
  2. Create the dump:

     $ pg_dump confluence > confluence.db.dump    

Move the dump to the new server and perform the following actions on the new server:

  1. Create the role and database as specified in the first part
  2. Import the dump

     $ psql confluence < confluence.db.dump