Help us understand the problem. What is going on with this article?

HerokuでプロダクションDBと開発用DBのスキーマを比較する

More than 3 years have passed since last update.

要件

開発用DBとプロダクションDBで一部文字列カラムの長さが異なっていることが発覚したため検証を行った。(evolution用のSQLの内容が元のスキーマに反映されていなかったと思われる)

ただし、開発用DBは常にALTER TABLEで列追加されているのに対し、プロダクションDBではその中間段階でCREATE TABLEしているため、カラムの順番は無視する必要がある

herokuのpg:psqlコマンドでは-cオプションでSQLを即時実行できるので、スキーマ取得のコマンドを発行してそのdiffを取れば良い。

\dだと列の順序が無視できないので、システムテーブルのinformation_schema.columnsテーブルからSELECTする

コマンド

APP_NAMEの部分を適宜書き換えればすぐに使えます。

heroku pg:psql -a APP_NAME -c "select table_schema as sch, table_name as tbl, column_name as col, column_default as def, is_nullable as null, data_type as type, character_maximum_length as maxlen, numeric_precision as npc, numeric_precision_radix as npcr, numeric_scale as ns, datetime_precision as dp from information_schema.columns where table_schema = 'public' order by tbl, col;" > APP_NAME.txt
Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away