【Mac】Postgres 9.6→10.1へバージョンアップ対応
不用意に brew upgradeしてしまった影響は、今だに出ているので、その対応を細々と。
元々入っていたPostgresのバージョンが9.6から10.1 にバージョンが上がった為、Postgresを起動しようとするとエラーで起動できなくなったので、その対応
$ postgres -D /usr/local/var/postgres 2018-01-22 14:18:16.340 JST [14935] FATAL: database files are incompatible with server 2018-01-22 14:18:16.340 JST [14935] DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.1.
brew の情報はとても親切なので、brew infoでpostgresの情報を出してやると、アップグレードコマンドも書いてあるので、そのとおりに実行。
$ brew info postgresql
postgresql: stable 10.1 (bottled), HEAD
Object-relational database system
https://www.postgresql.org/
Conflicts with:
postgres-xc (because postgresql and postgres-xc install the same binaries.)
/usr/local/Cellar/postgresql/9.6.1 (3,242 files, 36.4MB)
Poured from bottle on 2016-11-28 at 16:55:30
/usr/local/Cellar/postgresql/9.6.3 (3,259 files, 36.6MB)
Poured from bottle on 2017-08-03 at 13:25:30
/usr/local/Cellar/postgresql/10.1 (3,372 files, 38.8MB) *
Poured from bottle on 2018-01-15 at 16:28:38
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/postgresql.rb
==> Dependencies
Required: openssl ✔, readline ✔
Optional: python ✘, python3 ✘
==> Options
--with-dtrace
Build with DTrace support
--with-python
Enable PL/Python2
--with-python3
Enable PL/Python3 (incompatible with --with-python)
--without-perl
Build without Perl support
--without-tcl
Build without Tcl support
--HEAD
Install HEAD version
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
brew postgresql-upgrade-database
To have launchd start postgresql now and restart at login:
brew services start postgresql
Or, if you don't want/need a background service you can just run:
pg_ctl -D /usr/local/var/postgres start
アップグレードコマンドを実行した結果、無事に
$ brew postgresql-upgrade-database
==> Upgrading postgresql data from 9.6 to 10...
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Moving postgresql data from /usr/local/var/postgres to /usr/local/var/postgres.old...
The files belonging to this database system will be owned by user "myaccount".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
lc_collate values for database "postgres" do not match: old "ja_JP.UTF-8", new "en_US.UTF-8"
Failure, exiting
==> Upgraded postgresql data from 9.6 to 10!
==> Your postgresql 9.6 data remains at /usr/local/var/postgres.old
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
これで、成功していたら、postgresが起動しているので、後はいつもどおり、 ただ、結果のログを見ているとローカル接続以外は、できないよと警告が出ているので、hostを指定して接続している人は、 起動オプションなりpg_hba.confを変更しましょう
WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
