差分

Tep3-onk-2

17,923 バイト追加, 2023年6月27日 (火) 09:43

→‎あとから外部キー制約を追加する手順

[[category:memo|{{PAGENAME}}]]

{{Font color}}

== tep5 準備 ==

プロジェクトディレクトリ作成 移動 [[rails前バージョン確認|バージョン確認]]

$ createdb mydb

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

 

=== postgres dump restore ===

% pg_dump -Fc tep5_development > ../202212141027.dump

% pg_restore --verbose --clean --no-acl --no-owner -h localhost -U jq -d tep5_development ../202212141027.dump

=== 作り直すアプリはデータベース構造を変えるのでテーブルごとにダンプ・リストアする ===

=== clientsテーブル　othersテーブル ===

==== 2022-12 -14　clientsテーブル ====

latest_dumpデータベースからダンプして　作業データベースにリストアする

% pg_dump -t clients latest_dump > ../client_dump.sql

 

<nowiki>% psql -d update_dump < ../client_dump.sql

SET

SET

SET

SET

SET

set_config

------------

(1 row)

 

SET

SET

SET

SET

SET

SET

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

ALTER TABLE

COPY 5138

setval

--------

5428

(1 row)

 

ALTER TABLE

CREATE INDEX

CREATE INDEX

CREATE INDEX

CREATE INDEX

ERROR: relation "public.users" does not exist

ERROR: relation "public.jiins" does not exist</nowiki>

 

重複確認

<nowiki>=# SELECT ie_id, COUNT(ie_id)

FROM clients

where nusi = true GROUP BY ie_id

HAVING COUNT(ie_id) > 1

ORDER BY ie_id;

ie_id | count

-------+-------

2090 | 2

2129 | 2

2167 | 2

2174 | 2

.

.

.

(131 rows)</nowiki>

 

桁数を変えて重複を解消する

<nowiki>update_dump=# update clients SET ie_id = ie_id + 20000 where jiin_id = 2;

UPDATE 777

update_dump=# update clients SET ie_id = ie_id + 30000 where jiin_id = 3;

UPDATE 826

update_dump=# update clients SET ie_id = ie_id + 40000 where jiin_id = 4;

UPDATE 334</nowiki>

 

主重複解消

<nowiki>update_dump=# update clients set nusi = false where id = 3703;

UPDATE 1

update_dump=# update clients set nusi = false where id = 1346;

UPDATE 1</nowiki>

 

不要データ削除　修正

<nowiki>update_dump=# delete from clients where id in (4028,3911,3912,2998,3007,4359);

DELETE 0</nowiki>

<nowiki>update_dump=# update clients set nusi = true where id = 523;

UPDATE 1

update_dump=# delete from clients where id = 845;

DELETE 1

update_dump=# update clients set ie_id = 1264 where id = 515;

UPDATE 1

update_dump=# update clients set ie_id = 1264 where id = 1463;

UPDATE 1

update_dump=# update clients set nusi = false where id = 515;

UPDATE 1

update_dump=# delete from clients where id = 217;

DELETE 1

update_dump=# update clients set nusi = false where id = 218;

UPDATE 1

update_dump=# update clients set ie_id = 204 where id = 218;

UPDATE 1

update_dump=# delete from clients where id = 811;

DELETE 1

update_dump=# update clients set nusi = true where id = 1792;

UPDATE 1

update_dump=# delete from clients where id = 810;

DELETE 1

update_dump=# delete from clients where ie_id = 22653;

DELETE 3</nowiki>

 

確認

<nowiki>update_dump=# select count(*) from clients;

count

-------

5131

(1 row)</nowiki>

 

テーブル名変更

update_dump=# ALTER TABLE clients RENAME TO clients_old;

 

新テーブルのスキーマをダンプ　リストア

% pg_dump --username=jq --schema-only --table clients tep5_development > ../clients_schema.sql

% psql --username=jq update_dump < ../clients_schema.sql

 

旧テーブルをセレクトして新テーブルにインサート

<nowiki>update_dump=# insert into clients

(

id, user_id, note_id, content, client_line, memo, created_at, updated_at

)

select

ie_id, user_id, jiin_id, name, table_order, memo, created_at, updated_at

from

clients_old

where

nusi = true;

INSERT 0 1304</nowiki>

 

テーブルの増分を求める

<nowiki>update_dump=# SELECT created_at FROM clients_new EXCEPT SELECT created_at FROM clients;

created_at

----------------------------

2022-11-20 11:38:24.975644

2021-10-09 17:50:13.19454

2022-11-18 18:24:26.100683

2022-12-02 14:31:59.651794

2020-01-05 16:08:24.725981

2022-10-13 18:18:47.00945

2022-10-16 12:13:27.960598

2022-09-19 17:04:20.793626

2022-10-14 21:58:23.012871

2022-09-26 18:41:40.679061

(10 rows)</nowiki>

 

update_dump=# select * from clients_new where created_at > '2022-09-19';

 

書き出す

% psql -d update_dump -U jq -c "select * from clients_new where created_at > '2022-09-19'" -F, > ../clients_add.csv

 

増分をインサートする

<nowiki>tep5_development=# INSERT INTO clients (id, user_id, note_id, list_id, content, created_at, updated_at) VALUES

(35375,2,3,1,'支所','2022-11-18 18:24:26.100683','2022-11-18 18:24:26.108051'),

(5360,2,1,1,'広島門会','2022-10-13 18:18:47.00945 ','2022-10-13 18:18:47.023412'),

.

.

.

(25362,13,2,1,'岡山族会','2022-10-16 12:13:27.960598','2022-10-16 12:13:27.968872');

INSERT 0 7</nowiki>

 

ここまでのバックアップを取る

% pg_dump -Fc tep5_development > ../202212151515.dump

 

==== 2022-12 -14　othersテーブル ====

09-15以降の増分を取る　nusiがfalseのもの

% 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

 

コピーコマンドで追加ができる

memoの改行削除が必要 vim中でcontrol+v control+mで^Mが入力できる　:%s/^M//gc

=# \COPY others FROM '../others_add.csv' DELIMITER ',' CSV HEADER;

 

<nowiki>=# \COPY others FROM '../others_nusi_add.csv' DELIMITER ',' CSV HEADER;

COPY 8</nowiki>

 

ここまでのバックアップを取る

% pg_dump -Fc tep5_development > ../202212151649.dump

 

==== 旧clientsテーブルをリストアする ====

% pg_dump --username=jq --table clients_old heroku_dump > clients_old.sql

==== 旧clientsテーブルをリストアする ====

<nowiki>% psql --username=jq tep5_development < ../old_other.sql

SET

=== placesテーブル ===

==== 12-16 ====

確認

<nowiki>latest_dump=# select count(*) from places;

count

-------

921</nowiki>

 

=# select * from places where created_at > '2022-09-15';

 

増分書き出し

% psql -d latest_dump -U jq -c "select id, user_id, jiin_id, ie_id, zip, ken, city, etc, flag, created_at, updated_at from places where created_at > '2022-09-15'" -A -F, > ../places_add.csv

 

places_add.csv　memo列を,で作る　client_id(ie_id)の桁数を調整する

 

コピーで追加

=# \COPY places FROM '../places_add.csv' DELIMITER ',' CSV HEADER;

 

ここまでのバックアップを取る

% pg_dump -Fc tep5_development > ../202212160911.dump

 

==== 以前 ====

確認

<nowiki>heroku_dump=# select count(*) from places;

dump

% pg_dump --username=jq --table places_old heroku_dump > places_old.sql

 

==== 新データベースにリストア ====

<nowiki>% psql --username=jq tep5_development < ../places_old.sql

INSERT 0 918</nowiki>

==== generate ====

% rails generate scaffold Place user:references note:references client:references zip:string ken:string city:string etc:string flag:boolean memo:text

=== schedulesテーブル ===

==== 12-16 ====

確認

<nowiki>=# select count(*) from schedules;

count

-------

3716

</nowiki>

 

<nowiki>=# select count(*) from schedules;

count

-------

4026

</nowiki>

 

書き出し　jiin_id(note_id)変更のため並べ変える

% psql -d latest_dump -U jq -c "select id, user_id, jiin_id, ie_id, datetime, content, client_id, every_year, goji, created_at, updated_at from schedules where created_at >= '2022-09-13' order by jiin_id" -A -F, > ../schedules_add.csv

 

../schedules_add.csv　vimで修正する

jiin_id(note_id)2,3,4はそれぞれ20000,30000,40000を足す　gojiは修正する　:%s/,f,f/,f,/gc

 

copyで追加

=# \COPY schedules FROM '../schedules_add.csv' DELIMITER ',' CSV HEADER;

 

ここまでのバックアップを取る

% pg_dump -Fc tep5_development > ../202212161430.dump

 

==== 以前 ====

確認

<nowiki>heroku_dump=# select count(*) from schedules;

テーブルの変更

=# ALTER TABLE accounts RENAME COLUMN account TO content;

 

データ修正

=# update accounts set debit_credit = 1 where id = 43;

変更したテーブルをリストアし　アプリのデータベースにコピーする

=== 仕訳帳テーブル ===

==== 12-16 17:18 ====

確認

<nowiki>latest_dump=# select count(*) from journals;

count

-------

14885</nowiki>

 

最新をテーブルダンプする

% pg_dump --username=jq --table journals latest_dump > ../update_journals.sql

 

update_dumpデータベースにリストアする

<nowiki>% psql --username=jq update_dump < ../update_journals.sql

SET

SET

SET

SET

SET

set_config

------------

(1 row)

 

SET

SET

SET

SET

SET

SET

CREATE TABLE

ALTER TABLE

CREATE SEQUENCE

ALTER TABLE

ALTER SEQUENCE

ALTER TABLE

COPY 14885</nowiki>

 

update_dump=# ALTER TABLE journals RENAME COLUMN client_id TO other_id;

ALTER TABLE

 

update_dump=# ALTER TABLE journals ADD COLUMN client_id integer;

ALTER TABLE

 

update_dump=# ALTER TABLE journals RENAME COLUMN jiin_id TO note_id;

ALTER TABLE

 

=# update journals set account_id = 99 where account_id is null;

UPDATE 10379

 

% pg_dump -t journals update_dump > ../journals_latest.sql

 

<nowiki>tep5_development=# delete from journals ;

DELETE 14719

tep5_development=# TRUNCATE TABLE journals RESTART IDENTITY;

TRUNCATE TABLE

tep5_development=# select * from journals_id_seq ;

last_value | log_cnt | is_called

------------+---------+-----------

1 | 0 | f</nowiki>

 

<nowiki>% psql --username=jq tep5_development < ../journals_latest.sql

SET

SET

SET

SET

SET

set_config

------------

(1 row)

 

SET

SET

SET

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

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>

 

ここまでのバックアップを取る

% pg_dump -Fc tep5_development > ../202212170834.dump

 

==== 以前 ====

確認

<nowiki>=# select count(*) from journals;

=== 戒名テーブル　変更あり ===

==== 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;

ダンプ ここまで形を整えたデータベースをバックアップしとく

% pg_dump tep5_development > ../202210031034_tep5.sql

 

=== 通帳テーブル ===

確認

<nowiki>heroku_dump=# select count(*) from bankbooks;

count

-------

36</nowiki>

 

% rails generate scaffold Bankbook user:references note:references content:string memo:text

 

やり直し

% rails destroy scaffold Bankbook user:references note:references content:string memo:text

% rails generate scaffold Bankbook user:references note:references content:string bank_line:integer memo:text

 

（UUID削除 index追加）

% rails db:migrate

 

リストアして作業データベースにコピーする

% pg_dump --username=jq --table bankbooks heroku_dump > ../new_bankbooks.sql

% psql --username=jq update_heroku < ../new_bankbooks.sql

 

テーブルの変更

update_heroku=# ALTER TABLE bankbooks RENAME COLUMN jiin_id TO note_id;

ALTER TABLE

 

ダンプ　リストア

<nowiki>jq@MacBook-Pro tep5 % pg_dump --username=jq --table bankbooks update_heroku > ../new_bankbooks.sql

jq@MacBook-Pro tep5 % psql --username=jq tep5_development < ../new_bankbooks.sql

SET

SET

SET

SET

SET

set_config

------------

(1 row)

 

SET

SET

SET

SET

SET

SET

ERROR: relation "bankbooks" already exists

ALTER TABLE

ERROR: relation "bankbooks_id_seq" already exists

ALTER TABLE

ALTER SEQUENCE

ALTER TABLE

COPY 36

setval

--------

68

(1 row)

 

ERROR: multiple primary keys for table "bankbooks" are not allowed

CREATE INDEX

ERROR: relation "index_bankbooks_on_user_id" already exists

CREATE INDEX

ERROR: relation "public.jiins" does not exist</nowiki>

 

ダンプ ここまで形を整えたデータベースをバックアップしとく

% pg_dump tep5_development > ../202211021454_tep5.sql

 

=== relationshipsテーブル ===

確認

<nowiki>latest_dump=# select count(*) from relationships;

count

-------

40</nowiki>

 

relationshipsテーブル　ダンプ

% pg_dump --username=jq --table relationships latest_dump > ../new_relationships.sql

 

update_dumpデータベースにリストアしてロール列を削除する

% psql --username=jq update_dump < ../new_relationships.sql

=# ALTER TABLE relationships DROP COLUMN role;

 

修正したテーブルをダンプする

% pg_dump --username=jq --table relationships update_dump > ../relationships_dump.sql

 

リストアする

% psql -d tep5_development < ../relationships_dump.sql

 

バックアップを取る　-Fc カスタム形式 圧縮ファイル形式

% pg_dump -Fc tep5_development > ../202212141027.dump

 

== house_note ==

旧データベースからダンプ

% 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 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

config/envitonments/production.rb

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

== サーバーへ転送 ==

database.yml</nowiki>

すべて

% rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/ jq:/home/jq/tep5/

appのみ % rsync -r --delete --exclude-from='exclude-file.txt' ~/rails_app_tepnote/tep5/app/ jq:/home/jq/tep5/app/ データベース % rsync ~/rails_app_tepnote/202210031034_tep5202212170834.sql 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

データベース削除

~$ dropdb tep5_production

または

postgres=# DROP DATABASE tep5_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

• ← 古い編集

Shudo

ビューロクラット、管理者

3,728

回編集