Heroku PostgresからローカルPostgreSQLへ移行したメモ

概要

Heroku PostgresからローカルPostgreSQLへ移行しようと試行錯誤したメモです。

背景

HerokuでPostgreSQLを運用していたが、あまり使わなくなった＆お金がかかるので、Macのローカルにデータベースを移行したい。

やったこと

Heroku Postgresからダンプする

だいぶ前にやったので覚えてないが、pg_dumpでダンプしたみたい。

$ head ~/Downloads/Heroku_PostgreSQL-2023_11_11_13_25_09-dump.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.9 (Ubuntu 14.9-1.pgdg20.04+1)
-- Dumped by pg_dump version 14.10 (Homebrew)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;

MacでPostgreSQLを動かす

MacにインストールしてあるPostgreSQLを確認する。
14.10がインストールされている。

$ brew list postgresql
/usr/local/Cellar/postgresql@14/14.10_1/bin/clusterdb
/usr/local/Cellar/postgresql@14/14.10_1/bin/createdb
/usr/local/Cellar/postgresql@14/14.10_1/bin/createuser
/usr/local/Cellar/postgresql@14/14.10_1/bin/dropdb
/usr/local/Cellar/postgresql@14/14.10_1/bin/dropuser
/usr/local/Cellar/postgresql@14/14.10_1/bin/ecpg
/usr/local/Cellar/postgresql@14/14.10_1/bin/initdb
/usr/local/Cellar/postgresql@14/14.10_1/bin/oid2name
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_amcheck
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_archivecleanup
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_basebackup
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_checksums
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_config
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_controldata
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_ctl
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_dump
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_dumpall
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_isready
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_receivewal
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_recvlogical
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_resetwal
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_restore
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_rewind
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_test_fsync
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_test_timing
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_upgrade
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_verifybackup
/usr/local/Cellar/postgresql@14/14.10_1/bin/pg_waldump
/usr/local/Cellar/postgresql@14/14.10_1/bin/pgbench
/usr/local/Cellar/postgresql@14/14.10_1/bin/postgres
/usr/local/Cellar/postgresql@14/14.10_1/bin/postmaster
/usr/local/Cellar/postgresql@14/14.10_1/bin/psql
/usr/local/Cellar/postgresql@14/14.10_1/bin/reindexdb
/usr/local/Cellar/postgresql@14/14.10_1/bin/vacuumdb
/usr/local/Cellar/postgresql@14/14.10_1/bin/vacuumlo
/usr/local/Cellar/postgresql@14/14.10_1/homebrew.mxcl.postgresql@14.plist
/usr/local/Cellar/postgresql@14/14.10_1/homebrew.postgresql@14.service
/usr/local/Cellar/postgresql@14/14.10_1/include/postgresql@14/ (851 files)
/usr/local/Cellar/postgresql@14/14.10_1/lib/postgresql@14/ (116 files)
/usr/local/Cellar/postgresql@14/14.10_1/share/doc/ (1152 files)
/usr/local/Cellar/postgresql@14/14.10_1/share/man/ (294 files)
/usr/local/Cellar/postgresql@14/14.10_1/share/postgresql@14/ (862 files)

PostgreSQLを起動する。

$ brew services start postgresql
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)

# 起動できてない。。。
$ brew services list
Name          Status     User     File
postgresql@14 error  512 massakai ~/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist

$ brew uninstall postgresql@14
Uninstalling /usr/local/Cellar/postgresql@14/14.10_1... (3,317 files, 44.7MB)

PostgreSQL 14を入れ直してもエラーが解決できなかったので、PostgreSQL 16にしてみる。

$ brew install postgresql@16
==> Downloading https://formulae.brew.sh/api/formula.jws.json

==> Downloading https://formulae.brew.sh/api/cask.jws.json

==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/16/manifests/16.1_1-1
########################################################################################################### 100.0%
==> Fetching postgresql@16
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/16/blobs/sha256:9ce1f0d41cabf44535ddf83f474636dded2789
########################################################################################################### 100.0%
==> Pouring postgresql@16--16.1_1.ventura.bottle.1.tar.gz
==> /usr/local/Cellar/postgresql@16/16.1_1/bin/initdb --locale=C -E UTF-8 /usr/local/var/postgresql@16
==> Caveats
This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /usr/local/var/postgresql@16
For more details, read:
  https://www.postgresql.org/docs/16/app-initdb.html

postgresql@16 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have postgresql@16 first in your PATH, run:
  echo 'export PATH="/usr/local/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc

For compilers to find postgresql@16 you may need to set:
  export LDFLAGS="-L/usr/local/opt/postgresql@16/lib"
  export CPPFLAGS="-I/usr/local/opt/postgresql@16/include"

For pkg-config to find postgresql@16 you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/postgresql@16/lib/pkgconfig"

To start postgresql@16 now and restart at login:
  brew services start postgresql@16
Or, if you don't want/need a background service you can just run:
  LC_ALL="C" /usr/local/opt/postgresql@16/bin/postgres -D /usr/local/var/postgresql@16
==> Summary
🍺  /usr/local/Cellar/postgresql@16/16.1_1: 3,796 files, 67.4MB
==> Running `brew cleanup postgresql@16`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).

$ brew services start postgresql@16
==> Successfully started `postgresql@16` (label: homebrew.mxcl.postgresql@16)

# 起動できた
$ brew services list
Name          Status  User     File
cassandra     none
grafana       none
mysql         none
postgresql@16 started massakai ~/Library/LaunchAgents/homebrew.mxcl.postgresql@16.plist
unbound       none

# パスが通ってない
$ psql
zsh: command not found: psql

$ brew list postgresql@16
/usr/local/Cellar/postgresql@16/16.1_1/bin/clusterdb
/usr/local/Cellar/postgresql@16/16.1_1/bin/createdb
/usr/local/Cellar/postgresql@16/16.1_1/bin/createuser
/usr/local/Cellar/postgresql@16/16.1_1/bin/dropdb
/usr/local/Cellar/postgresql@16/16.1_1/bin/dropuser
/usr/local/Cellar/postgresql@16/16.1_1/bin/ecpg
/usr/local/Cellar/postgresql@16/16.1_1/bin/initdb
/usr/local/Cellar/postgresql@16/16.1_1/bin/oid2name
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_amcheck
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_archivecleanup
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_basebackup
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_checksums
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_config
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_controldata
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_ctl
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_dump
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_dumpall
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_isready
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_receivewal
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_recvlogical
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_resetwal
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_restore
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_rewind
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_test_fsync
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_test_timing
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_upgrade
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_verifybackup
/usr/local/Cellar/postgresql@16/16.1_1/bin/pg_waldump
/usr/local/Cellar/postgresql@16/16.1_1/bin/pgbench
/usr/local/Cellar/postgresql@16/16.1_1/bin/postgres
/usr/local/Cellar/postgresql@16/16.1_1/bin/psql
/usr/local/Cellar/postgresql@16/16.1_1/bin/reindexdb
/usr/local/Cellar/postgresql@16/16.1_1/bin/vacuumdb
/usr/local/Cellar/postgresql@16/16.1_1/bin/vacuumlo
/usr/local/Cellar/postgresql@16/16.1_1/homebrew.mxcl.postgresql@16.plist
/usr/local/Cellar/postgresql@16/16.1_1/homebrew.postgresql@16.service
/usr/local/Cellar/postgresql@16/16.1_1/include/libpq/libpq-fs.h
/usr/local/Cellar/postgresql@16/16.1_1/include/postgresql/ (853 files)
/usr/local/Cellar/postgresql@16/16.1_1/include/ (23 files)
/usr/local/Cellar/postgresql@16/16.1_1/lib/libecpg.6.dylib
/usr/local/Cellar/postgresql@16/16.1_1/lib/libecpg_compat.3.dylib
/usr/local/Cellar/postgresql@16/16.1_1/lib/libpgtypes.3.dylib
/usr/local/Cellar/postgresql@16/16.1_1/lib/libpq.5.dylib
/usr/local/Cellar/postgresql@16/16.1_1/lib/pkgconfig/ (4 files)
/usr/local/Cellar/postgresql@16/16.1_1/lib/postgresql/ (94 files)
/usr/local/Cellar/postgresql@16/16.1_1/lib/ (13 other files)
/usr/local/Cellar/postgresql@16/16.1_1/share/doc/ (1163 files)
/usr/local/Cellar/postgresql@16/16.1_1/share/locale/ (434 files)
/usr/local/Cellar/postgresql@16/16.1_1/share/man/ (294 files)
/usr/local/Cellar/postgresql@16/16.1_1/share/postgresql@16/ (872 files)

# パスを通す
$ vim ~/.zshenv
# PostgreSQL
export POSTGRESQL_HOME=/usr/local/Cellar/postgresql@16/16.1_1
path=(
    ${POSTGRESQL_HOME}/bin
    $path
)

データベースをMacのPostgreSQLへ移行する

データベースを作成して、リストアする。

# データベースを作成する
$ createdb utalog

# データベースにリストアする
$ psql utalog < ~/Downloads/Heroku_PostgreSQL-2023_11_11_13_25_09-dump.sql
SET
SET
SET
SET
SET
 set_config
------------

(1 行)

SET
SET
SET
SET
CREATE SCHEMA
ERROR:  role "uf2ts7do23mj9" does not exist
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE SEQUENCE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE SEQUENCE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE SEQUENCE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE SEQUENCE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE SEQUENCE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE SEQUENCE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE SEQUENCE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE SEQUENCE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER SEQUENCE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER TABLE
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
CREATE TABLE
ERROR:  role "ursyobjwizzsde" does not exist
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 2
COPY 0
COPY 0
COPY 202
COPY 0
COPY 0
COPY 80
COPY 48
COPY 19
COPY 60
COPY 72
COPY 1
COPY 181
COPY 4090
COPY 6415
COPY 769
COPY 769
COPY 1274
COPY 398
 setval
--------
      1
(1 行)

 setval
--------
      1
(1 行)

 setval
--------
      1
(1 行)

 setval
--------
      1
(1 行)

 setval
--------
     80
(1 行)

 setval
--------
     48
(1 行)

 setval
--------
     19
(1 行)

 setval
--------
     92
(1 行)

 setval
--------
      1
(1 行)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  role "ursyobjwizzsde" does not exist
ERROR:  role "ursyobjwizzsde" does not exist

なんかエラーが出てるな。

ERROR:  role "uf2ts7do23mj9" does not exist

データベースのデータを確認する。

# テーブルを表示する
SELECT * FROM pg_catalog.pg_tables;

ちゃんとテーブルはできてたので問題なさそう。

PostgreSQLにあまり詳しくなくて、アプリケーションアカウントをちゃんと作るのがめんどくさそうだったので、とりあえずデフォルトで作成されているMacのユーザー名のアカウントをそのまま使うことにする。

DjangoではSELECT, INSERT, UPDATE, DELETEの権限が必要で、スキーマの更新にはCREATE TABLE, ALTER TABLEの権限も必要になる。
PostgreSQLだとALTER TABLEコマンドを使用するには、変更するテーブルを所有している必要があるらしい。

Djangoの設定を変更する

ローカルのPostgreSQLへ接続する設定をする。

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'localhost',
        'PORT': '5432',
        'NAME': 'utalog',  # データベース名
        'USER': 'massakai',  # ユーザー名
        'PASSWORD': '',  # ローカルでのみ使用するため
        'TIME_ZONE': 'Asia/Tokyo',
    }
}

Cookie	期間	説明
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Heroku PostgresからローカルPostgreSQLへ移行したメモ

概要

背景

やったこと

Heroku Postgresからダンプする

MacでPostgreSQLを動かす

データベースをMacのPostgreSQLへ移行する

Djangoの設定を変更する

参考

いいね:

関連

コメントするコメントをキャンセル

2024年4月
月	火	水	木	金	土	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

概要

背景

やったこと

Heroku Postgresからダンプする

MacでPostgreSQLを動かす

データベースをMacのPostgreSQLへ移行する

Djangoの設定を変更する

参考

共有:

いいね:

関連

コメントする コメントをキャンセル

コメントするコメントをキャンセル