最新版 |
編集中の文章 |
1行目: |
1行目: |
| [[category:memo|{{PAGENAME}}]] | | [[category:memo|{{PAGENAME}}]] |
| {{Font color}} | | {{Font color}} |
− | == tep5 準備 == | + | == 準備 == |
| プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]] | | プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]] |
| | | |
340行目: |
340行目: |
| | | |
| ==== 2022-12 -14 othersテーブル ==== | | ==== 2022-12 -14 othersテーブル ==== |
− | 09-15以降の増分を取る nusiがfalseのもの | + | 09-15以降の増分を取る |
| % psql -d update_dump -U jq -c "select id, user_id, jiin_id, ie_id, name, yomi, nusi, each_order, memo, generation, created_at, updated_at from clients_old where created_at > '2022-09-15' and nusi = false" -A -F, > ../others_add.csv | | % psql -d update_dump -U jq -c "select id, user_id, jiin_id, ie_id, name, yomi, nusi, each_order, memo, generation, created_at, updated_at from clients_old where created_at > '2022-09-15' and nusi = false" -A -F, > ../others_add.csv |
− |
| |
− | nusiがtrueのもの 両方必要
| |
− | % psql -d update_dump -U jq -c "select id, user_id, jiin_id, ie_id, name, yomi, nusi, each_order, memo, generation, created_at, updated_at from clients_old where created_at > '2022-09-15' and nusi = true" -A -F, > ../others_nusi_add.csv
| |
| | | |
| コピーコマンドで追加ができる | | コピーコマンドで追加ができる |
350行目: |
347行目: |
| | | |
| =# \COPY others FROM '../others_add.csv' DELIMITER ',' CSV HEADER; | | =# \COPY others FROM '../others_add.csv' DELIMITER ',' CSV HEADER; |
− |
| |
− | <nowiki>=# \COPY others FROM '../others_nusi_add.csv' DELIMITER ',' CSV HEADER;
| |
− | COPY 8</nowiki>
| |
| | | |
| ここまでのバックアップを取る | | ここまでのバックアップを取る |
621行目: |
615行目: |
| INSERT 0 918</nowiki> | | INSERT 0 918</nowiki> |
| | | |
− | generate | + | ==== generate ==== |
| % rails generate scaffold Place user:references note:references client:references zip:string ken:string city:string etc:string flag:boolean memo:text | | % rails generate scaffold Place user:references note:references client:references zip:string ken:string city:string etc:string flag:boolean memo:text |
| | | |
901行目: |
895行目: |
| | | |
| === 仕訳帳テーブル === | | === 仕訳帳テーブル === |
− | ==== 12-16 17:18 ==== | + | ==== 12-16 ==== |
| 確認 | | 確認 |
| <nowiki>latest_dump=# select count(*) from journals; | | <nowiki>latest_dump=# select count(*) from journals; |
955行目: |
949行目: |
| tep5_development=# TRUNCATE TABLE journals RESTART IDENTITY; | | tep5_development=# TRUNCATE TABLE journals RESTART IDENTITY; |
| TRUNCATE TABLE | | TRUNCATE TABLE |
| + | tep5_development=# select * from journals |
| + | journals journals_id_seq |
| tep5_development=# select * from journals_id_seq ; | | tep5_development=# select * from journals_id_seq ; |
| last_value | log_cnt | is_called | | last_value | log_cnt | is_called |
960行目: |
956行目: |
| 1 | 0 | f</nowiki> | | 1 | 0 | f</nowiki> |
| | | |
− | <nowiki>% psql --username=jq tep5_development < ../journals_latest.sql | + | ==== 以前 ==== |
− | SET
| + | 確認 |
− | SET
| + | <nowiki>=# select count(*) from journals; |
− | SET
| + | count |
− | SET
| + | ------- |
− | SET
| + | 14709 |
− | set_config | + | (1 row)</nowiki> |
− | ------------
| + | |
− | | + | % rails generate scaffold Journal user:references note:references account:references client_id:integer other_id:integer journal_number:integer date:date schedule_id:integer debit:string debit_money:integer description_id:integer etc:string credit:string credit_money:integer debit_s:integer credit_s:integer |
− | (1 row) | + | |
| + | % rails db:migrate |
| | | |
− | SET
| + | リストアして作業データベースにコピーする |
− | SET
| + | <nowiki>% pg_dump --username=jq --table journals heroku_dump > ../new_journals.sql |
− | SET
| + | % psql --username=jq update_heroku < ../new_journals.sql</nowiki> |
− | SET
| |
− | SET
| |
− | SET
| |
− | ERROR: relation "journals" already exists
| |
− | ALTER TABLE
| |
− | ERROR: relation "journals_id_seq" already exists
| |
− | ALTER TABLE
| |
− | ALTER SEQUENCE
| |
− | ALTER TABLE
| |
− | COPY 14885
| |
− | setval | |
− | -------- | |
− | 15199
| |
− | (1 row)
| |
| | | |
− | ERROR: multiple primary keys for table "journals" are not allowed
| + | テーブルの変更 |
− | ERROR: relation "index_journals_on_account_id" already exists
| + | =# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id; |
− | ERROR: relation "index_journals_on_credit_and_credit_money_and_account_id" already exists
| |
− | ERROR: relation "index_journals_on_credit_s" already exists
| |
− | ERROR: relation "index_journals_on_debit_and_client_id_and_debit_money" already exists
| |
− | ERROR: relation "index_journals_on_debit_s" already exists
| |
− | ERROR: relation "index_journals_on_description_id_and_schedule_id" already exists
| |
− | ERROR: relation "index_journals_on_jiin_id" already exists
| |
− | ERROR: relation "index_journals_on_user_id" already exists
| |
− | ERROR: relation "index_journals_on_user_id_and_jiin_id_and_journal_number" already exists</nowiki>
| |
| | | |
− | ここまでのバックアップを取る
| + | 新テーブルのclient_idに合わせる |
− | % pg_dump -Fc tep5_development > ../202212170834.dump | + | <nowiki>update_heroku=# update journals SET client_id = client_id + 20000 where note_id = 2; |
| + | UPDATE 3663 |
| + | update_heroku=# update journals SET client_id = client_id + 30000 where note_id = 3; |
| + | UPDATE 1815 |
| + | update_heroku=# update journals SET client_id = client_id + 40000 where note_id = 4; |
| + | UPDATE 719</nowiki> |
| | | |
− | ==== 以前 ====
| + | リストア コピー |
− | 確認
| + | <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table journals update_heroku > ../new_journals.sql |
− | <nowiki>=# select count(*) from journals; | + | jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_journals.sql |
− | count | + | SET |
− | ------- | + | SET |
− | 14709 | + | SET |
− | (1 row)</nowiki> | + | SET |
| + | SET |
| + | set_config |
| + | ------------ |
| + | |
| + | (1 row) |
| | | |
− | % rails generate scaffold Journal user:references note:references account:references client_id:integer other_id:integer journal_number:integer date:date schedule_id:integer debit:string debit_money:integer description_id:integer etc:string credit:string credit_money:integer debit_s:integer credit_s:integer | + | SET |
− | | + | SET |
− | % rails db:migrate
| + | SET |
− | | + | SET |
− | リストアして作業データベースにコピーする
| + | SET |
− | <nowiki>% pg_dump --username=jq --table journals heroku_dump > ../new_journals.sql | + | SET |
− | % psql --username=jq update_heroku < ../new_journals.sql</nowiki>
| + | ERROR: relation "journals" already exists |
| + | ALTER TABLE |
| + | ERROR: relation "journals_id_seq" already exists |
| + | ALTER TABLE |
| + | ALTER SEQUENCE |
| + | ALTER TABLE |
| + | COPY 14709 |
| + | setval |
| + | -------- |
| + | 15023 |
| + | (1 row) |
| | | |
− | テーブルの変更
| + | ERROR: multiple primary keys for table "journals" are not allowed |
− | =# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id; | + | CREATE INDEX |
− | | + | CREATE INDEX |
− | 新テーブルのclient_idに合わせる
| + | CREATE INDEX |
− | <nowiki>update_heroku=# update journals SET client_id = client_id + 20000 where note_id = 2;
| + | CREATE INDEX |
− | UPDATE 3663
| + | CREATE INDEX |
− | update_heroku=# update journals SET client_id = client_id + 30000 where note_id = 3;
| + | CREATE INDEX |
− | UPDATE 1815
| + | CREATE INDEX |
− | update_heroku=# update journals SET client_id = client_id + 40000 where note_id = 4;
| + | ERROR: relation "index_journals_on_user_id" already exists |
− | UPDATE 719</nowiki>
| + | CREATE INDEX |
− | | + | ERROR: relation "public.jiins" does not exist |
− | リストア コピー
| + | ALTER TABLE</nowiki> |
− | <nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table journals update_heroku > ../new_journals.sql | |
− | jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_journals.sql
| |
− | SET
| |
− | SET
| |
− | SET
| |
− | SET
| |
− | SET
| |
− | set_config
| |
− | ------------
| |
− |
| |
− | (1 row)
| |
| | | |
− | SET
| + | ダンプ ここまで形を整えたデータベースをバックアップしとく |
− | SET
| + | % pg_dump tep5_development > ../202209270816_tep5.sql |
− | SET
| + | |
− | SET
| + | ==== 替え玉 ==== |
− | SET
| + | update_heroku=# insert into accounts(account_cd, content, bs_pl, debit_credit, created_at, updated_at) VALUES ('9999', '替え玉', 1, 1, '2022-09-28', '2022-09-28'); |
− | SET
| + | tep5_development=# insert into accounts(account_cd, content, bs_pl, debit_credit, created_at, updated_at) VALUES ('9999', '替え玉', 1, 1, '2022-09-28', '2022-09-28'); |
− | ERROR: relation "journals" already exists
| |
− | ALTER TABLE
| |
− | ERROR: relation "journals_id_seq" already exists
| |
− | ALTER TABLE
| |
− | ALTER SEQUENCE
| |
− | ALTER TABLE
| |
− | COPY 14709
| |
− | setval | |
− | -------- | |
− | 15023
| |
− | (1 row)
| |
| | | |
− | ERROR: multiple primary keys for table "journals" are not allowed
| + | 確認 |
− | CREATE INDEX
| + | <nowiki>tep5_development=# select * from accounts order by id desc limit 1; |
− | CREATE INDEX
| |
− | CREATE INDEX
| |
− | CREATE INDEX
| |
− | CREATE INDEX
| |
− | CREATE INDEX
| |
− | CREATE INDEX
| |
− | ERROR: relation "index_journals_on_user_id" already exists
| |
− | CREATE INDEX
| |
− | ERROR: relation "public.jiins" does not exist
| |
− | ALTER TABLE</nowiki>
| |
− | | |
− | ダンプ ここまで形を整えたデータベースをバックアップしとく
| |
− | % pg_dump tep5_development > ../202209270816_tep5.sql
| |
− | | |
− | ==== 替え玉 ====
| |
− | update_heroku=# insert into accounts(account_cd, content, bs_pl, debit_credit, created_at, updated_at) VALUES ('9999', '替え玉', 1, 1, '2022-09-28', '2022-09-28');
| |
− | tep5_development=# insert into accounts(account_cd, content, bs_pl, debit_credit, created_at, updated_at) VALUES ('9999', '替え玉', 1, 1, '2022-09-28', '2022-09-28');
| |
− | | |
− | 確認 | |
− | <nowiki>tep5_development=# select * from accounts order by id desc limit 1; | |
| id | account_cd | content | bs_pl | debit_credit | created_at | updated_at | | id | account_cd | content | bs_pl | debit_credit | created_at | updated_at |
| ----+------------+---------+-------+--------------+---------------------+--------------------- | | ----+------------+---------+-------+--------------+---------------------+--------------------- |
1,163行目: |
1,116行目: |
| | | |
| === 戒名テーブル 変更あり === | | === 戒名テーブル 変更あり === |
− | ==== 12-17 11:14 ====
| |
− | 確認
| |
− | <nowiki>latest_dump=# select count(*) from kaimyous;
| |
− | count
| |
− | -------
| |
− | 3445</nowiki>
| |
− |
| |
− | 増分を書き出す
| |
− | % psql -d latest_dump -U jq -c "select id, user_id, jiin_id, ie_id, client_id, content, date, g_age, relationship, ie_id_v, created_at, updated_at from kaimyous where created_at >= '2022-09-13' order by jiin_id" -A -F, > ../kaimyous_add.csv
| |
− |
| |
− | ie_id(client_id)は修正する ie_id_vはmemoに対応するために書き出した
| |
− |
| |
− | コピーで追加
| |
− | <nowiki>=# \COPY kaimyous FROM '../kaimyous_add.csv' DELIMITER ',' CSV HEADER;
| |
− | COPY 70</nowiki>
| |
− |
| |
− | ==== 以前 ====
| |
| 確認 | | 確認 |
| <nowiki>heroku_dump=# select count(*) from kaimyous; | | <nowiki>heroku_dump=# select count(*) from kaimyous; |
1,432行目: |
1,368行目: |
| % pg_dump -Fc tep5_development > ../202212141027.dump | | % pg_dump -Fc tep5_development > ../202212141027.dump |
| | | |
− | == house_note ==
| + | == UUIDを有効にする == |
− | 旧データベースからダンプ
| |
− | % pg_dump --username=jq --table house_notes latest_dump > ../hns.sql
| |
− | | |
− | サーバーへ転送
| |
− | % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/hns.sql jq:/home/jq/dump_data
| |
− | | |
− | リストア
| |
− | $ psql update_dump < ../hns.sql
| |
− | | |
− | clientsのidとhouse_noteのidをあわせる
| |
− | <nowiki>tep5_production=# update house_notes SET ie_id = ie_id + 20000 where jiin_id = 2;
| |
− | UPDATE 30
| |
− | tep5_production=# update house_notes SET ie_id = ie_id + 30000 where jiin_id = 3;
| |
− | UPDATE 114
| |
− | tep5_production=# update house_notes SET ie_id = ie_id + 40000 where jiin_id = 4;
| |
− | UPDATE 25</nowiki>
| |
− | | |
− | セレクトしてアップデートする
| |
− | tep5_production=# UPDATE clients as Cl SET memo = (SELECT content FROM house_notes as Hn WHERE Hn.ie_id = Cl.id);
| |
− | | |
− | 削除
| |
− | =# drop table house_notes ;
| |
− | | |
− | == UUIDを有効にする == | |
| === 設定 === | | === 設定 === |
| % rails generate migration enable_extension_for_uuid | | % rails generate migration enable_extension_for_uuid |
1,531行目: |
1,443行目: |
| | | |
| $ rails generate scaffold Post content:text user:references picture:string | | $ rails generate scaffold Post content:text user:references picture:string |
− |
| |
− | $ rails generate scaffold Post user:references title:string content:text notification_time:datetime reminder_type:integer post_type:integer picture:string
| |
| | | |
| $ rails generate model Relationship follower_id:integer followed_id:integer | | $ rails generate model Relationship follower_id:integer followed_id:integer |
1,547行目: |
1,457行目: |
| config/envitonments/production.rb | | config/envitonments/production.rb |
| config.assets.compile = true | | config.assets.compile = true |
− |
| |
− | === カラム追加 ===
| |
− | <nowiki>% rails generate migration add_memo_to_schedules memo:text
| |
− | invoke active_record
| |
− | create db/migrate/20230311013341_add_memo_to_schedules.rb
| |
− | % rails db:migrate
| |
− | == 20230311013341 AddMemoToSchedules: migrating ===============================
| |
− | -- add_column(:schedules, :memo, :text)
| |
− | -> 0.0056s
| |
− | == 20230311013341 AddMemoToSchedules: migrated (0.0057s) ======================</nowiki>
| |
− |
| |
− | === 外部キー制約に変更 ===
| |
− | rails generate migration AddScheduleReferenceToJournal schedule:references
| |
| | | |
| == サーバーへ転送 == | | == サーバーへ転送 == |
1,567行目: |
1,464行目: |
| database.yml</nowiki> | | database.yml</nowiki> |
| | | |
− | すべて
| |
| % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/ | | % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/ |
| | | |
− | appのみ
| + | % rsync ~/rails_app_tepnote/202210031034_tep5.sql jq:/home/jq/dump_data |
− | % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/app/ jq:/home/jq/tep5/app/
| |
− | | |
− | データベース
| |
− | % rsync ~/rails_app_tepnote/202212170834.dump jq:/home/jq/dump_data | |
| | | |
| == サーバー側 == | | == サーバー側 == |
− | リストア
| |
− | $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U jq -d tep5_ production ../dump_data/202212141027.dump
| |
− |
| |
| $ bundle install --without test development | | $ bundle install --without test development |
| | | |
1,587行目: |
1,476行目: |
| | | |
| データベース削除 | | データベース削除 |
− | ~$ dropdb tep5_production
| |
− | または
| |
| postgres=# DROP DATABASE tep5_production; | | postgres=# DROP DATABASE tep5_production; |
| | | |
1,663行目: |
1,550行目: |
| | | |
| $ rails db:migrate RAILS_ENV=production | | $ rails db:migrate RAILS_ENV=production |
− |
| |
− | === シーケンス操作 ===
| |
− | 確認
| |
− | <nowiki>=# select * from others_id_seq ;
| |
− | last_value | log_cnt | is_called
| |
− | ------------+---------+-----------
| |
− | 14 | 30 | t</nowiki>
| |
− |
| |
− | 最大値確認
| |
− | <nowiki>=# select max(id) from others;
| |
− | max
| |
− | ------
| |
− | 5428</nowiki>
| |
− |
| |
− | 変更
| |
− | <nowiki>=# SELECT setval('others_id_seq', 5428, true);
| |
− | setval
| |
− | --------
| |
− | 5428</nowiki>
| |
− |
| |
− | === バックアップ ===
| |
− | dump
| |
− | $ pg_dump -Fc tep5_production > ~/dump_data/202212180817.dump
| |
− |
| |
− | ローカル側からコピーして二箇所で保存する
| |
− | % scp jq:/home/jq/dump_data/202212180817.dump /Users/jq/rails_app_tepnote
| |
− |
| |
− | == データベース変更 ==
| |
− | === まるごと ===
| |
− | ダンプ 本番データベースバックアップ データのみ
| |
− | $ pg_dump -a tep5_production > ~/dump_data/202212270953_data_only.sql
| |
− |
| |
− | ローカルからコピー
| |
− | % scp jq:/home/jq/dump_data/202212270953_data_only.sql /Users/jq/rails_app_tepnote
| |
− |
| |
− | ローカルデータベース削除
| |
− | % rails db:drop
| |
− |
| |
− | データベース作成
| |
− | % rails db:create
| |
− |
| |
− | マイグレーション変更
| |
− |
| |
− | マイグレーション
| |
− | % rails db:migrate
| |
− |
| |
− | リストア
| |
− | % psql --username=jq tep5_development < ../202212270953_data_only.sql
| |
− |
| |
− | === カラム追加 boolean ===
| |
− | % rails generate migration add_payment_to_descriptions payment:boolean
| |
− |
| |
− | add_column :descriptions, :payment, :boolean, default: false
| |
− |
| |
− | $ rails db:migrate
| |
− |
| |
− | === あとから外部キー制約を追加する手順 ===
| |
− | 親テーブルと子テーブルのIDを比較する 不整合があればテーブルを修正する
| |
− | <nowiki>=# SELECT id FROM others
| |
− | EXCEPT
| |
− | SELECT other_id FROM journals
| |
− | UNION ALL
| |
− | SELECT other_id FROM journals
| |
− | EXCEPT
| |
− | SELECT id FROM others order by id;
| |
− | id
| |
− | ------
| |
− | 2435</nowiki>
| |
− |
| |
− | 外部キー制約を追加するmigration ファイルをつくる
| |
− | <nowiki> % rails generate migration AddClientReferenceToJournal client:references
| |
− | invoke active_record
| |
− | create db/migrate/20230509044227_add_client_reference_to_journal.rb
| |
− | % rails generate migration AddOtherReferenceToJournal other:references
| |
− | invoke active_record
| |
− | create db/migrate/20230509044353_add_other_reference_to_journal.rb</nowiki>
| |
− |
| |
− | rails db:migrateでエラー
| |
− |
| |
− | すでにother_idカラムが存在するのでadd_referenceメソッドが実行できない
| |
− |
| |
− | 直接データベースを操作してother_idカラム名をhoge_idカラム名に変更する
| |
− |
| |
− | ALTER TABLE journals RENAME COLUMN other_id TO hoge_id;
| |
− |
| |
− | rails db:migrate
| |
− |
| |
− | hoge_idカラムの値をother_idカラムにコピーする
| |
− | UPDATE journals SET other_id = hoge_id;
| |
− |
| |
− | ローカルならrails consoleを使用して
| |
− |
| |
− | Journal.update_all('other_id = hoge_id')
| |
− |
| |
− | hoge_idを削除する
| |
− | ALTER TABLE journals DROP COLUMN hoge_id;
| |
− |
| |
− | === エラー ===
| |
− | 例えば待避したhoge_idを戻すとき次のようなエラーがでる
| |
− | <nowiki>=# UPDATE journals SET schedule_id = hoge_id;
| |
− | ERROR: insert or update on table "journals" violates foreign key constraint "fk_rails_9d4d4268db"
| |
− | DETAIL: Key (schedule_id)=(970) is not present in table "schedules".</nowiki>
| |
− | journalsテーブルのschedule_idがschedulesテーブルにないのでそれを拾い出して削除する
| |
− | <nowiki>=# delete from journals where hoge_id in (
| |
− | SELECT id FROM schedules
| |
− | EXCEPT
| |
− | SELECT hoge_id FROM journals
| |
− | UNION ALL
| |
− | SELECT hoge_id FROM journals
| |
− | EXCEPT
| |
− | SELECT id FROM schedules);</nowiki>
| |
− |
| |
− | == gemアップデート ==
| |
− | 確認
| |
− | % gem -v
| |
− | アップデート対象Gemを一覧表示
| |
− | % bundle outdated
| |
− |
| |
− | == 備忘 ==
| |
− | 現在UUIDコメントアウト中 config/initializers/generators.rb
| |