How to Export User Data to CSV in Confluence
Confluence doesn't currently have functionality to export user data, but you can leverage various database functionalities to accomplish this.
The following queries are specific to your database type. The output will consist of the id, username, first, and last name of the users.
- Always back up your data before performing any modification to the database.
- Cloud required format of user import differs slightly - please scroll down to the area that says Cloud's User Import and follow steps there exactly.
MySQL
Users
select id, user_name, lower_first_name, lower_last_name into outfile '/tmp/confusers.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from cwd_user;
this will not include headers
PostgreSQL
Users
copy cwd_user(id, user_name, lower_first_name, lower_last_name) to '/tmp/confusers.csv' delimiters',' CSV HEADER;
Groups
copy cwd_group(id, group_name, lower_group_name) to '/tmp/confgroup.csv' delimiters',' CSV HEADER;
Memberships
copy cwd_membership(child_name, parent_name) to '/tmp/confmembership.csv' delimiters',' CSV HEADER;
this will include the headers.
Cloud's User Import
At times, customers are required to prepare the CSV file for user import as the users residing on external user management, and it needs to disable external user management to import successfully. To prepare the data for Cloud applications' user import, the format required is:
Users
Updated statement
\copy (SELECT email_address AS email, user_name AS username, display_name as displayname FROM cwd_user) to '/tmp/user.csv' csv header
Old style (no longer relevant) : "copy cwd_user(user_name, first_name, last_name, email_address, credential) to '/tmp/confusers.csv' delimiters',' CSV HEADER;"
Membership
Updated statement
\copy (SELECT email_address AS email, user_name AS username, display_name as displayname FROM cwd_user) to '/tmp/user.csv' csv header
Old style (no longer relevant) : "copy cwd_membership(child_name, parent_name) to '/tmp/confmembership.csv' delimiters',' CSV HEADER;"
Order is to be follow exactly as recommended on this documentation
.