How to drop and recreate the database constraints on PostgreSQL.
This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Restricted functions in Atlassian Cloud apps, the contents of this article cannot be applied to Atlassian Cloud applications.
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.
- 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.
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.