「Heroku」の版間の差分
提供: wikipokpok
(→データベース操作) |
|||
| (同じ利用者による、間の14版が非表示) | |||
| 1行目: | 1行目: | ||
[[category:memo|{{PAGENAME}}]] | [[category:memo|{{PAGENAME}}]] | ||
| + | == sendgrid設定 == | ||
| + | [https://www.bokukoko.info/entry/2020/12/07/080000 参考] | ||
| − | DB接続情報 | + | == DB接続情報 == |
$ heroku pg:credentials:url --app アプリケーション名 | $ heroku pg:credentials:url --app アプリケーション名 | ||
| − | herokuのタイムゾーン変更 | + | == herokuのタイムゾーン変更 == |
$ heroku run bash | $ heroku run bash | ||
~ $ date 確認 | ~ $ date 確認 | ||
| 16行目: | 18行目: | ||
Sun Feb 24 08:21:11 JST 2019 | Sun Feb 24 08:21:11 JST 2019 | ||
| − | heroku postgresql タイムゾーン設定 | + | == heroku postgresql タイムゾーン設定 == |
$ heroku pg:credentials:url --app shrxxxxx-xxx-xxx データベース名確認 | $ heroku pg:credentials:url --app shrxxxxx-xxx-xxx データベース名確認 | ||
$ heroku pg:psql | $ heroku pg:psql | ||
| 29行目: | 31行目: | ||
heroku pg:psql -c "\d clients" | heroku pg:psql -c "\d clients" | ||
| − | シーケンス確認 | + | == シーケンス確認 == |
heroku pg:psql -c "select * from clients_id_seq" | heroku pg:psql -c "select * from clients_id_seq" | ||
| 53行目: | 55行目: | ||
heroku pg:psql -c "\copy journals (journal_number,date,schedule_id,client_id,debit,debit_money,description_id,etc,credit,credit_money,user_id,jiin_id,account_id,created_at,updated_at) from '/home/shudo/kg_siwakemeisai_date.csv' with csv" | heroku pg:psql -c "\copy journals (journal_number,date,schedule_id,client_id,debit,debit_money,description_id,etc,credit,credit_money,user_id,jiin_id,account_id,created_at,updated_at) from '/home/shudo/kg_siwakemeisai_date.csv' with csv" | ||
| − | backup | + | == backup == |
heroku pg:backups:capture --app app_name | heroku pg:backups:capture --app app_name | ||
heroku pg:backups -a app_name | heroku pg:backups -a app_name | ||
| 62行目: | 64行目: | ||
heroku pg:backups restore bxxx -a アプリ名 | heroku pg:backups restore bxxx -a アプリ名 | ||
| − | CSV書き出し | + | == アプリ間でデータを移行する == |
| + | % heroku pg:copy shrieking-asylum-92472::DATABASE_URL DATABASE_URL -a tepnote | ||
| + | ▸ WARNING: Destructive action | ||
| + | ▸ This command will remove all data from DATABASE | ||
| + | ▸ Data from PINK will then be transferred to DATABASE | ||
| + | ▸ To proceed, type tepnote or re-run this command with --confirm tepnote | ||
| + | > tepnote | ||
| + | Starting copy of PINK to DATABASE... done | ||
| + | Copying... done | ||
| + | |||
| + | == CSV書き出し == | ||
$ heroku pg:psql -c "\copy (select * from schedules where jiin_id = 2) TO kougen_schedules.csv WITH CSV HEADER" | $ heroku pg:psql -c "\copy (select * from schedules where jiin_id = 2) TO kougen_schedules.csv WITH CSV HEADER" | ||
または | または | ||
| 70行目: | 82行目: | ||
| − | Hobby Dev Free から Hobby Basic $9.00 に移行したとき データの移し方 | + | == Hobby Dev Free から Hobby Basic $9.00 に移行したとき データの移し方 == |
heroku pg:copy SOURCE TARGET | heroku pg:copy SOURCE TARGET | ||
$ heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_color_URL | $ heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_color_URL | ||
| 76行目: | 88行目: | ||
$ heroku pg:promote HEROKU_POSTGRESQL_color_URL 新しいデータベースをプロモートする | $ heroku pg:promote HEROKU_POSTGRESQL_color_URL 新しいデータベースをプロモートする | ||
$ heroku pg:info 確認 | $ heroku pg:info 確認 | ||
| − | + | == データベース操作 == | |
| + | $ heroku pg:psql -c "SELECT * FROM users" | ||
$ heroku pg:psql -c "UPDATE clients SET nusi = true WHERE id = " | $ heroku pg:psql -c "UPDATE clients SET nusi = true WHERE id = " | ||
$ heroku pg:psql -c "DELETE FROM products WHERE price = 10" | $ heroku pg:psql -c "DELETE FROM products WHERE price = 10" | ||
| + | $ heroku pg:psql | ||
| + | => | ||
| + | INSERT INTO holidays (holiday, title, created_at, updated_at) VALUES | ||
| + | ('2021-1-1','元日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-1-11', '成人の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-2-11', '建国記念の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-2-23', '天皇誕生日', '2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-3-20', '春分の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-4-29', '昭和の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-5-3', '憲法記念日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-5-4', 'みどりの日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-5-5', 'こどもの日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-7-22', '海の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-7-23', 'スポーツの日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-8-8', '山の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-8-9', '休日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-9-20', '敬老の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-9-23', '秋分の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-11-3', '文化の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2021-11-23','勤労感謝の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-1-1', '元日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-1-10', '成人の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-2-11', '建国記念の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-2-23', '天皇誕生日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-3-21', '春分の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-4-29', '昭和の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-5-3', '憲法記念日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-5-4', 'みどりの日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-5-5', 'こどもの日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-7-18', '海の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-8-11', '山の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-9-19', '敬老の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-9-23', '秋分の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-10-10','スポーツの日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-11-3', '文化の日','2021-06-15 18:46:45','2021-06-15 18:46:45'), | ||
| + | ('2022-11-23','勤労感謝の日','2021-06-15 18:46:45','2021-06-15 18:46:45'); | ||
| + | |||
| + | == ローカルのテーブルをCSVで書き出しHEROKUのテーブルにコピーする == | ||
| + | =# COPY descriptions(content,user_id,jiin_id,account_id,created_at,updated_at,money) TO '/Users/jq/environment/table_data/descriptions.csv'; | ||
| + | |||
| + | heroku pg:psql -c "select setval ('descriptions_id_seq', 1, false)" ←シーケンスのリセット | ||
| + | |||
| + | heroku pg:psql -c "\COPY descriptions(content,user_id,jiin_id,account_id,created_at,updated_at,money) FROM '/Users/jq/environment/table_data/descriptions.csv'" | ||
| + | |||
| + | == ログ == | ||
$ heroku logs --tail | $ heroku logs --tail | ||
| − | データベースへ列の追加など | + | == データベースへ列の追加など == |
$ heroku run rails db:migrate | $ heroku run rails db:migrate | ||
| − | SELECTした結果でUPDATEする | + | == SELECTした結果でUPDATEする == |
$ heroku pg:psql -c "update kaimyous set ie_id = clients.ie_id from clients where kaimyous.client_id = clients.id and kaimyous.jiin_id = 1 and kaimyous.ie_id is null" | $ heroku pg:psql -c "update kaimyous set ie_id = clients.ie_id from clients where kaimyous.client_id = clients.id and kaimyous.jiin_id = 1 and kaimyous.ie_id is null" | ||
2021年6月16日 (水) 08:50時点における最新版
目次
sendgrid設定[編集]
DB接続情報[編集]
$ heroku pg:credentials:url --app アプリケーション名
herokuのタイムゾーン変更[編集]
$ heroku run bash ~ $ date 確認 Sat Feb 23 23:18:56 Asia 2019 ~ $ exit control + dでheroku bashを抜ける $ heroku config:add TZ=Asia/Tokyo 変更 TZ: Asia/Tokyo $ heroku run bash ~ $ date 確認 Sun Feb 24 08:21:11 JST 2019
heroku postgresql タイムゾーン設定[編集]
$ heroku pg:credentials:url --app shrxxxxx-xxx-xxx データベース名確認
$ heroku pg:psql
=> alter database "データベース名" set timezone = 'Asia/Tokyo';
=> \q 再接続
=> select current_timestamp; 確認
current_timestamp
-------------------------------
2019-02-06 10:01:21.112501+09
(1 row)
heroku pg:psql -c "\d clients"
シーケンス確認[編集]
heroku pg:psql -c "select * from clients_id_seq"
NULLと0000-00-00を削除する
idで個人と戒名を紐付ける
ie_idはあけておく 登録の確定により値が入る ie_id_vは前データベースの値
idはシーケンスを確認する kaimyousのclient_idは連番を埋める
シーケンス操作 次は2160から始まる
heroku pg:psql -c "select setval ('clients_id_seq', 2159)"
heroku pg:psql -c "\copy clients (name,yomi,ie_id,nusi,user_id,jiin_id,created_at,updated_at,ie_id_v) from '/home/shudo/an_meibo.csv' with csv"
client_idに2160からの連番を入れ clientsのidと紐付ける ie_idは空
heroku pg:psql -c "\copy kaimyous (content,user_id,jiin_id,client_id,created_at,updated_at,date,ie_id,g_age,relationship,ie_id_v) from '/home/shudo/an_kaimyou.csv' with csv"
scheduleのデータ移行 新しいie_id,client_idは空 履歴に出てこない
heroku pg:psql -c "\copy schedules (datetime,content,jiin_id,user_id,created_at,updated_at,ie_id,client_id,ie_id_v) from '/home/shudo/an_yotei.csv' with csv"
journalデータ移行準備
SELECT * FROM `siwakemeisai` JOIN siwake ON siwakemeisai.`dennpyouBanngou` = siwake.id
schedule_id,client_id,account_idは空 それぞれ紐付けできない
heroku pg:psql -c "\copy journals (journal_number,date,schedule_id,client_id,debit,debit_money,description_id,etc,credit,credit_money,user_id,jiin_id,account_id,created_at,updated_at) from '/home/shudo/kg_siwakemeisai_date.csv' with csv"
backup[編集]
heroku pg:backups:capture --app app_name heroku pg:backups -a app_name heroku pg:backups:download b004 -a app_name ローカル環境へリストア pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump 本番環境へリストア heroku pg:backups restore bxxx -a アプリ名
アプリ間でデータを移行する[編集]
% heroku pg:copy shrieking-asylum-92472::DATABASE_URL DATABASE_URL -a tepnote ▸ WARNING: Destructive action ▸ This command will remove all data from DATABASE ▸ Data from PINK will then be transferred to DATABASE ▸ To proceed, type tepnote or re-run this command with --confirm tepnote > tepnote Starting copy of PINK to DATABASE... done Copying... done
CSV書き出し[編集]
$ heroku pg:psql -c "\copy (select * from schedules where jiin_id = 2) TO kougen_schedules.csv WITH CSV HEADER" または $ heroku config #DATABASE_URLを確認 $ psql DATABASE_URL #psqlコンソールを立ち上げる \copy (SELECT * FROM users) TO /home/dump.csv CSV DELIMITER ','
Hobby Dev Free から Hobby Basic $9.00 に移行したとき データの移し方[編集]
heroku pg:copy SOURCE TARGET $ heroku pg:copy DATABASE_URL HEROKU_POSTGRESQL_color_URL $ heroku pg:info 確認 $ heroku pg:promote HEROKU_POSTGRESQL_color_URL 新しいデータベースをプロモートする $ heroku pg:info 確認
データベース操作[編集]
$ heroku pg:psql -c "SELECT * FROM users" $ heroku pg:psql -c "UPDATE clients SET nusi = true WHERE id = " $ heroku pg:psql -c "DELETE FROM products WHERE price = 10"
$ heroku pg:psql
=>
INSERT INTO holidays (holiday, title, created_at, updated_at) VALUES
('2021-1-1','元日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-1-11', '成人の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-2-11', '建国記念の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-2-23', '天皇誕生日', '2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-3-20', '春分の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-4-29', '昭和の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-5-3', '憲法記念日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-5-4', 'みどりの日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-5-5', 'こどもの日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-7-22', '海の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-7-23', 'スポーツの日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-8-8', '山の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-8-9', '休日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-9-20', '敬老の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-9-23', '秋分の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-11-3', '文化の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2021-11-23','勤労感謝の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-1-1', '元日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-1-10', '成人の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-2-11', '建国記念の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-2-23', '天皇誕生日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-3-21', '春分の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-4-29', '昭和の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-5-3', '憲法記念日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-5-4', 'みどりの日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-5-5', 'こどもの日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-7-18', '海の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-8-11', '山の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-9-19', '敬老の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-9-23', '秋分の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-10-10','スポーツの日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-11-3', '文化の日','2021-06-15 18:46:45','2021-06-15 18:46:45'),
('2022-11-23','勤労感謝の日','2021-06-15 18:46:45','2021-06-15 18:46:45');
ローカルのテーブルをCSVで書き出しHEROKUのテーブルにコピーする[編集]
=# COPY descriptions(content,user_id,jiin_id,account_id,created_at,updated_at,money) TO '/Users/jq/environment/table_data/descriptions.csv';
heroku pg:psql -c "select setval ('descriptions_id_seq', 1, false)" ←シーケンスのリセット
heroku pg:psql -c "\COPY descriptions(content,user_id,jiin_id,account_id,created_at,updated_at,money) FROM '/Users/jq/environment/table_data/descriptions.csv'"
ログ[編集]
$ heroku logs --tail
データベースへ列の追加など[編集]
$ heroku run rails db:migrate
SELECTした結果でUPDATEする[編集]
$ heroku pg:psql -c "update kaimyous set ie_id = clients.ie_id from clients where kaimyous.client_id = clients.id and kaimyous.jiin_id = 1 and kaimyous.ie_id is null"
