How to drop and recreate the database constraints on PostgreSQL.
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
Sometimes the application database constraints may prevent the user (for a good reason) from performing updates on it, even the ones described in our KBs, like:
It's needed, then, to drop the constraints on the database before attempting the procedure.
It's important to notice that dropping the database constraints should be considered a last resort action.
So, whenever a direct database update is necessary, try to follow through the constraints by workarounding the reported warnings.
Cause
- After a failed migration process, or a problematic one that ended with numerous errors, specially in Confluence Space imports, the database may have its integrity compromised resulting in unexpected behaviours, like the inability to re-attempt the import or delete the Space.
Resolution
Read before proceeding
- Remember to ALWAYS do a database backup before running any database updates.
- Make sure to stop all applications that access the database
Use this two SQL statements to generate SQL script files to drop and recreate the Constraints:
copy (SELECT 'ALTER TABLE '||nspname||'.\"'||relname||'\" DROP CONSTRAINT \"'||conname||'\";'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname) to '<path-to-save>/droppingConstraints.sql';
copy (SELECT 'ALTER TABLE '||nspname||'.\"'||relname||'\" ADD CONSTRAINT \"'||conname||'\" '|| pg_get_constraintdef(pg_constraint.oid)||';'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC) to '<path-to-save>/addingConstraint.sql';- Use the generated droppingConstraints.sql SQL script to DROP the constraints.
- Do the necessary changes in the database.
- Use the generated addingConstraints.sql SQL script to ADD the constraints back to the database.
Start the applications and check if everything is in place.
If the application becomes unstable after the database changes it might be necessary to rollback.
That's why you need to generate a database backup before the entire process.