How to drop and recreate the database constraints on PostgreSQL.

このページの内容

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

この記事はアトラシアンのサーバー製品にのみ適用されます。クラウドとサーバー製品の違いについてはこちらをご確認ください。

問題

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:

Creating a file to Drop 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';

Creating a file to Add the constraints later on.
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';
  1. Use the generated droppingConstraints.sql SQL script to DROP the constraints.
  2. Do the necessary changes in the database.
  3. Use the generated addingConstraints.sql SQL script to ADD the constraints back to the database.
  4. 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.

最終更新日: 2016 年 2 月 19 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.