Problem: after migrating Atlassian Confluence PostgreSQL database to another server, you’re seeing lots of broken links (in red color) and some navigation macros not working properly (showing errors such as “page not found”).
The first thing Atlassian themselves and their community experts recommend doing in such situation is rebuilding the content indices as described here. However, that didn’t work for me.
What worked is the solution posted here by Atlassian community users Danila Bystrov and Alexey Astafyev: re-creating the Postgres database with the following settings: LC_COLLATE ‘C’ LC_CTYPE ‘C’.
# Login as postgres user
su - postgres
# Backup Confluence database
pg_dump --format custom --file confluence.sqlc <your_confluence_db_name>
# !!! CAREFUL !!! drop Confluence database
dropdb <your_confluence_db_name>
# Create new Confluence database
psql
CREATE DATABASE <your_confluence_db_name> WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
exit
# Restore the backup
pg_restore --dbname=<your_confluence_db_name> confluence.sqlc