marcopagot
@marcopagot (Asuka)

Are you sure you want to delete the question?

If your question is resolved, you may close it.

Leaving a resolved question undeleted may help others!

We hope you find it useful!

Postgresqlのupgradeが出来ません😭

解決したいこと

postgresqlを使えるようにしたい

現状)
記事を閲覧いただきありがとうございます。
現在、sqlの勉強をしようとpostgresqlの環境構築をしているのですが、環境構築が出来ず困っております。

つまづいている状況を箇条書きで説明させて頂きます。
1.initdbをしようとした。
2.initdb:errorが出た。どうやら、directoryが空じゃないことが原因らしい。
3.rmでdirectoryを削除した
4.再度initdbしたが同じinittdb:errorが出た。
5.postgres -Dでエラーの原因を追求した。
6.どうやらversion13が古く14にupgradeしなければいけない模様。
7.upgradeする為に、brew services stop postgresqlで一度、stopし、再度upgradeした。
8.Upgrading postgresql data from 13 to 14 failed!とエラーが出た。
9.infoで確認するとpostgresql: stable 14.1 (bottled), HEADと出ている。。。。

現在、上記9でつまづいております。。。
下記コードになるのですが、解決方法が分かる方がいらっしゃいましたら、何卒ご教示の程宜しくお願い致します🙇‍♀️
※上記番号と下記コード番号はリンクしております。

入力1

initdb --locale=C -E UTF-8 /opt/homebrew/var/postgres

出力(エラー)2

initdb: error: directory "/opt/homebrew/var/postgres" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/opt/homebrew/var/postgres" or run initdb
with an argument other than "/opt/homebrew/var/postgres".

入力5

postgres -D /opt/homebrew/var/postgres       

出力(エラー)6

2021-11-29 23:05:00.531 JST [69910] FATAL:  database files are incompatible with server
2021-11-29 23:05:00.531 JST [69910] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.1.

入力8

brew postgresql-upgrade-database

出力(エラー)8

==> Upgrading postgresql data from 13 to 14...
waiting for server to start....2021-11-29 22:24:40.062 JST [68308] LOG:  starting PostgreSQL 13.5 on arm-apple-darwin20.6.0, compiled by Apple clang version 13.0.0 (clang-1300.0.29.3), 64-bit
2021-11-29 22:24:40.064 JST [68308] LOG:  could not bind IPv6 address "::1": Address already in use
2021-11-29 22:24:40.064 JST [68308] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2021-11-29 22:24:40.064 JST [68308] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
2021-11-29 22:24:40.064 JST [68308] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2021-11-29 22:24:40.064 JST [68308] WARNING:  could not create listen socket for "localhost"
2021-11-29 22:24:40.064 JST [68308] FATAL:  could not create any TCP/IP sockets
2021-11-29 22:24:40.064 JST [68308] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.
Password for user 8skiphone: 
Password for user 8skiphone: 
Password for user 8skiphone: 
Password for user 8skiphone: 
Password for user 8skiphone: 
Password for user 8skiphone: 
Password for user 8skiphone: 
==> Moving postgresql data from /opt/homebrew/var/postgres to /opt/homebrew/var/postgres.old...
==> Creating database...
The files belonging to this database system will be owned by user "8skiphone".
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 /opt/homebrew/var/postgres ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Tokyo
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

    /opt/homebrew/opt/postgresql/bin/pg_ctl -D /opt/homebrew/var/postgres -l logfile start

==> Migrating and upgrading data...
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 system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok

lc_collate values for database "postgres" do not match:  old "C", new "en_US.UTF-8"
Failure, exiting
Error: Upgrading postgresql data from 13 to 14 failed!
==> Removing empty postgresql initdb database...
==> Moving postgresql data back from /opt/homebrew/var/postgres.old to /opt/homebrew/var/postgres...
Error: Failure while executing; `/opt/homebrew/opt/postgresql/bin/pg_upgrade -r -b /opt/homebrew/Cellar/postgresql@13/13.5_1/bin -B /opt/homebrew/opt/postgresql/bin -d /opt/homebrew/var/postgres.old -D /opt/homebrew/var/postgres -j 8` exited with 1.

入力9

brew info postgres

出力(エラ-)9

postgresql: stable 14.1 (bottled), HEAD
Object-relational database system
https://www.postgresql.org/
/opt/homebrew/Cellar/postgresql/14.1_1 (3,304 files, 45.8MB) *
  Poured from bottle on 2021-11-29 at 22:03:40
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/postgresql.rb
License: PostgreSQL
==> Dependencies
Build: pkg-config ✔
Required: icu4c ✔, krb5 ✔, openssl@1.1 ✔, readline ✔
==> Options
--HEAD
    Install HEAD version
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /opt/homebrew/var/postgres
For more details, read:
  https://www.postgresql.org/docs/14/app-initdb.html

To restart postgresql after an upgrade:
  brew services restart postgresql
Or, if you don't want/need a background service you can just run:
  /opt/homebrew/opt/postgresql/bin/postgres -D /opt/homebrew/var/postgres
==> Analytics
install: 146,074 (30 days), 410,463 (90 days), 1,670,888 (365 days)
install-on-request: 140,598 (30 days), 394,882 (90 days), 1,601,660 (365 days)
build-error: 22 (30 days)

質問が、漠然としており申し訳ありません。
宜しくお願い致します。

0

No Answers yet.

Your answer might help someone💌