Closed8
error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role does not exist
rdhd
rails:db:createできない
rails db:create
connection to server at "::1", port 5432 failed: FATAL: role "ct" does not exist
Couldn't create 'rails_blog_development' database. Please check your configuration.
rails aborted!
ActiveRecord::ConnectionNotEstablished: connection to server at "::1", port 5432 failed: FATAL: role "ct" does not exist
Caused by:
PG::ConnectionBad: connection to server at "::1", port 5432 failed: FATAL: role "ct" does not exist
Tasks: TOP => db:create
(See full trace by running task with --trace)
gemをインストールしました。
gem install pg -- --with-pg-config=/usr/local/bin/pg_config
➜ ~ pg_config
BINDIR = /usr/local/Cellar/postgresql@14/14.8_2/bin
DOCDIR = /usr/local/Cellar/postgresql@14/14.8_2/share/doc
HTMLDIR = /usr/local/Cellar/postgresql@14/14.8_2/share/doc
INCLUDEDIR = /usr/local/include/postgresql@14
PKGINCLUDEDIR = /usr/local/include/postgresql@14
INCLUDEDIR-SERVER = /usr/local/include/postgresql@14/server
LIBDIR = /usr/local/lib/postgresql@14
PKGLIBDIR = /usr/local/lib/postgresql@14
LOCALEDIR = /usr/local/Cellar/postgresql@14/14.8_2/share/locale
MANDIR = /usr/local/Cellar/postgresql@14/14.8_2/share/man
SHAREDIR = /usr/local/share/postgresql@14
SYSCONFDIR = /usr/local/Cellar/postgresql@14/14.8_2/etc
PGXS = /usr/local/lib/postgresql@14/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--disable-debug' '--prefix=/usr/local/Cellar/postgresql@14/14.8_2' '--datadir=/usr/local/share/postgresql@14' '--libdir=/usr/local/lib/postgresql@14' '--includedir=/usr/local/include/postgresql@14' '--enable-thread-safety' '--with-gssapi' '--with-icu' '--with-ldap' '--with-libxml' '--with-libxslt' '--with-lz4' '--with-openssl' '--with-pam' '--with-perl' '--with-uuid=e2fs' '--with-extra-version= (Homebrew)' '--with-bonjour' '--with-tcl' 'PG_SYSROOT=/Library/Developer/CommandLineTools/SDKs/MacOSX13.sdk' 'CC=clang' 'LDFLAGS=-L/usr/local/opt/openssl@3/lib -L/usr/local/opt/readline/lib' 'CPPFLAGS=-I/usr/local/opt/openssl@3/include -I/usr/local/opt/readline/include' 'CXX=clang++' 'PKG_CONFIG_PATH=/usr/local/opt/icu4c/lib/pkgconfig:/usr/local/opt/openssl@3/lib/pkgconfig:/usr/local/opt/krb5/lib/pkgconfig:/usr/local/opt/lz4/lib/pkgconfig:/usr/local/opt/readline/lib/pkgconfig' 'PKG_CONFIG_LIBDIR=/usr/lib/pkgconfig:/usr/local/Homebrew/Library/Homebrew/os/mac/pkgconfig/13'
CC = clang
CPPFLAGS = -I/usr/local/Cellar/icu4c/73.2/include -isysroot /Library/Developer/CommandLineTools/SDKs/MacOSX13.sdk -I/usr/local/opt/openssl@3/include -I/usr/local/opt/readline/include -I/usr/local/Cellar/lz4/1.9.4/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Werror=unguarded-availability-new -Wendif-labels -Wmissing-format-attribute -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -Wno-compound-token-split-by-macro -Wno-deprecated-non-prototype -O2
CFLAGS_SL =
LDFLAGS = -isysroot /Library/Developer/CommandLineTools/SDKs/MacOSX13.sdk -L/usr/local/opt/openssl@3/lib -L/usr/local/opt/readline/lib -L/usr/local/Cellar/lz4/1.9.4/lib -Wl,-dead_strip_dylibs
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -llz4 -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm
VERSION = PostgreSQL 14.8 (Homebrew)
postgresql@14 ロール存在しない
role "ct"を存在しないとのことで作成してみましたができませんでした。
➜ psql --version
psql (PostgreSQL) 14.8 (Homebrew)
➜ which psql
/usr/local/bin/psql
➜ brew services restart postgresql@14
Stopping `postgresql@14`... (might take a while)
==> Successfully stopped `postgresql@14` (label: homebrew.mxcl.postgresql@14)
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)
➜ brew services list
Name Status User File
mysql none
postgresql@14 started root ~/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist
➜ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "ct" does not exist
➜ psql postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "ct" does not exist
➜ ~ psql -l
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "ct" does not exist
➜ whoami
ct
➜ ~ createuser -s ct
createuser: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "ct" does not exist
➜ ~ createuser ct --interactive
Shall the new role be a superuser? (y/n) y
createuser: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "ct" does not exist
➜ ~ /usr/local/opt/postgresql@14/bin/createuser -s ct
createuser: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "ct" does not exist
➜ ~ sudo -u ct -i
➜ ~ createuser -l -d -P ct
Enter password for new role:
Enter it again:
createuser: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "ct" does not exist
pg_hba.conf
ユーザーがまだいないです。
➜ cd /usr/local/var/postgresql@14
➜ cat pg_hba.conf
# "host" records. In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.
# CAUTION: Configuring the system for local "trust" authentication
# allows any local user to connect as any PostgreSQL user, including
# the database superuser. If you do not trust all your local users,
# use another authentication method.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
postgresステータス
postgresのステータスに特にエラーがなく動いているようです。
➜ ~ ps aux | grep postgres
ct 19074 0.0 0.0 34130480 616 s003 S+ 6:15PM 0:00.00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn --exclude-dir=.idea --exclude-dir=.tox postgres
ct 18668 0.0 0.0 34360584 1748 ?? Ss 6:08PM 0:00.00 postgres: logical replication launcher
ct 18667 0.0 0.0 34215000 1012 ?? Ss 6:08PM 0:00.01 postgres: stats collector
ct 18666 0.0 0.0 34361608 2108 ?? Ss 6:08PM 0:00.01 postgres: autovacuum launcher
ct 18665 0.0 0.0 34369608 5292 ?? Ss 6:08PM 0:00.01 postgres: walwriter
ct 18664 0.0 0.0 34361416 2356 ?? Ss 6:08PM 0:00.03 postgres: background writer
ct 18663 0.0 0.0 34369608 2656 ?? Ss 6:08PM 0:00.01 postgres: checkpointer
ct 18655 0.0 0.1 34363772 16300 ?? S 6:08PM 0:00.04 /usr/local/opt/postgresql@14/bin/postgres -D /usr/local/var/postgresql@14
➜ ~ pgpg_ctl status -D /usr/local/var/postgresql@14
pg_ctl: server is running (PID: 18655)
/usr/local/Cellar/postgresql@14/14.8_2/bin/postgres "-D" "/usr/local/var/postgresql@14"
➜ ~ ls -la /tmp/.s.PGSQL.5432
srwxrwxrwx 1 777 wheel 0 Jul 18 18:08 /tmp/.s.PGSQL.5432
➜ ~ pg_ctl -D /usr/local/var/postgresql@14 stop
waiting for server to shut down....2023-07-18 19:12:19.282 JST [23929] LOG: received fast shutdown request
2023-07-18 19:12:19.283 JST [23929] LOG: aborting any active transactions
2023-07-18 19:12:19.284 JST [23929] LOG: background worker "logical replication launcher" (PID 23936) exited with exit code 1
2023-07-18 19:12:19.284 JST [23931] LOG: shutting down
2023-07-18 19:12:19.290 JST [23929] LOG: database system is shut down
done
server stopped
ログ
➜ cd /usr/local/var/log/
➜ log ls
postgresql@14.log
➜ cat postgresql@14.log
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise. See the documentation for
more information on how to properly start the server.
rootがpostgresqlを起動する権限がない、別のユーザーを作成する必要があるです。
adduserでユーザーを作ってみる
コマンドが存在しないとエラーが出ました。
➜ ~ sudo adduser postgres --system
Password:
sudo: adduser: command not found
➜ ~ sudo useradd postgres --system
sudo: useradd: command not found
As with any server daemon that is accessible to the outside world, it is advisable to run PostgreSQL under a separate user account. This user account should only own the data that is managed by the server, and should not be shared with other daemons. (For example, using the user nobody is a bad idea.) It is not advisable to install executables owned by this user because compromised systems could then modify their own binaries.
To add a Unix user account to your system, look for a command
useraddoradduser. The user name postgres is often used, and is assumed throughout this book, but you can use another name if you like.
rdhdrootから起動する
同じエラーが出てます。
➜ ~ pg_ctl -D /usr/local/var/postgresql@14 start
waiting for server to start....2023-07-18 19:47:40.782 JST [26484] LOG: starting PostgreSQL 14.8 (Homebrew) on x86_64-apple-darwin22.4.0, compiled by Apple clang version 14.0.3 (clang-1403.0.22.14.1), 64-bit
2023-07-18 19:47:40.783 JST [26484] LOG: listening on IPv6 address "::1", port 5432
2023-07-18 19:47:40.783 JST [26484] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-07-18 19:47:40.784 JST [26484] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-07-18 19:47:40.789 JST [26485] LOG: database system was shut down at 2023-07-18 19:12:19 JST
2023-07-18 19:47:40.794 JST [26484] LOG: database system is ready to accept connections
done
server started
➜ ~ psql
2023-07-18 19:48:20.117 JST [26547] FATAL: role "ct" does not exist
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "ct" does not exist
アンインストールして再インストールする
PostgreSQL の設定ファイル、ログ、データなどを手動削除する必要があります。
➜ brew uninstall postgresql@14
Uninstalling /usr/local/Cellar/postgresql@14/14.8_2... (3,315 files, 44.6MB)
Error: Could not remove postgresql@14 keg! Do so manually:
sudo rm -rf /usr/local/Cellar/postgresql@14/14.8_2
➜ sudo rm -rf /usr/local/Cellar/postgresql@14/14.8_2
Password:
➜ sudo rm -rf /usr/local/Cellar/postgresql@14
# 残りのファイル手動で削除する
➜ ls -l /usr/local/var/postgresql@14
total 120
-rw------- 1 ct admin 3 Feb 12 00:24 PG_VERSION
drwx------ 9 ct admin 288 Mar 18 18:34 base
drwx------ 59 ct admin 1888 Jun 2 20:17 global
drwx------ 2 ct admin 64 Feb 12 00:24 pg_commit_ts
drwx------ 2 ct admin 64 Feb 12 00:24 pg_dynshmem
-rw------- 1 ct admin 4789 Feb 12 00:24 pg_hba.conf
-rw------- 1 ct admin 1636 Feb 12 00:24 pg_ident.conf
drwx------ 5 ct admin 160 Jul 20 13:10 pg_logical
drwx------ 4 ct admin 128 Feb 12 00:24 pg_multixact
drwx------ 2 ct admin 64 Feb 12 00:24 pg_notify
drwx------ 2 ct admin 64 Feb 12 00:24 pg_replslot
drwx------ 2 ct admin 64 Feb 12 00:24 pg_serial
drwx------ 2 ct admin 64 Feb 12 00:24 pg_snapshots
drwx------ 2 ct admin 64 Jul 20 13:05 pg_stat
drwx------ 3 ct admin 96 Jul 20 13:19 pg_stat_tmp
drwx------ 3 ct admin 96 Feb 12 00:24 pg_subtrans
drwx------ 2 ct admin 64 Feb 12 00:24 pg_tblspc
drwx------ 2 ct admin 64 Feb 12 00:24 pg_twophase
drwx------ 4 ct admin 128 Feb 12 00:24 pg_wal
drwx------ 3 ct admin 96 Feb 12 00:24 pg_xact
-rw------- 1 ct admin 88 Feb 12 00:24 postgresql.auto.conf
-rw------- 1 ct admin 28719 Feb 12 00:24 postgresql.conf
-rw------- 1 ct admin 88 Jul 20 13:05 postmaster.opts
-rw------- 1 ct admin 95 Jul 20 13:05 postmaster.pid
➜ rm -rf /usr/local/var/postgresql@14
# 削除されたことを確認する
➜ ls -l /usr/local/var/postgresql@14
ls: /usr/local/var/postgresql@14: No such file or directory
➜ brew install postgresql@14
==> Fetching postgresql@14
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/14/manifests/14.8_2
Already downloaded: /Users/chloe/Library/Caches/Homebrew/downloads/48d5fd21a79ce56dc177b1529e83e66b3aa6cc746aeb2fbe5f055d65cf377fd5--postgresql@14-14.8_2.bottle_manifest.json
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/14/blobs/sha256:adb57c116645b96
Already downloaded: /Users/chloe/Library/Caches/Homebrew/downloads/7112387ef475191265c810bbcc7100cbb458a7e71b0a65eff18c7e0824f039c4--postgresql@14--14.8_2.ventura.bottle.tar.gz
==> Pouring postgresql@14--14.8_2.ventura.bottle.tar.gz
==> /usr/local/Cellar/postgresql@14/14.8_2/bin/initdb --locale=C -E UTF-8 /usr/local/var/po
==> Caveats
This formula has created a default database cluster with:
initdb --locale=C -E UTF-8 /usr/local/var/postgresql@14
For more details, read:
https://www.postgresql.org/docs/14/app-initdb.html
To restart postgresql@14 after an upgrade:
brew services restart postgresql@14
Or, if you don't want/need a background service you can just run:
/usr/local/opt/postgresql@14/bin/postgres -D /usr/local/var/postgresql@14
==> Summary
🍺 /usr/local/Cellar/postgresql@14/14.8_2: 3,315 files, 44.6MB
==> Running `brew cleanup postgresql@14`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).
➜ brew services start postgresql@14
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)
➜ psql postgres
psql (14.8 (Homebrew))
Type "help" for help.
postgres=# exit
➜ rails db:create
Created database 'rails_blog_development'
Created database 'rails_blog_test'
host:db
Jul 22 11:08:54 PM could not translate host name "db" to address: Name or service not known
Jul 22 11:08:54 PM Couldn't create 'rails_blog_production' database. Please check your configuration.
Jul 22 11:08:54 PM rails aborted!
Jul 22 11:08:54 PM ActiveRecord::ConnectionNotEstablished: could not translate host name "db" to address: Name or service not known
Jul 22 11:08:54 PM
Jul 22 11:08:54 PM
Jul 22 11:08:54 PM Caused by:
Jul 22 11:08:54 PM PG::ConnectionBad: could not translate host name "db" to address: Name or service not known
Jul 22 11:08:54 PM
Jul 22 11:08:54 PM Tasks: TOP => db:create
Jul 22 11:08:54 PM (See full trace by running task with --trace)
Jul 22 11:08:55 PM ==> Build failed 😞
Jul 22 11:08:55 PM
host:dbなし
Jul 23 12:13:28 AM We could not find your database: postgres. Which can be found in the database configuration file located at config/database.yml.
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM To resolve this issue:
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM - Did you create the database for this app, or delete it? You may need to create your database.
Jul 23 12:13:28 AM - Has the database name changed? Check your database.yml config has the correct database name.
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM To create your database, run:
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM bin/rails db:create
Jul 23 12:13:28 AM Couldn't create 'rails_blog_production' database. Please check your configuration.
Jul 23 12:13:28 AM rails aborted!
Jul 23 12:13:28 AM ActiveRecord::NoDatabaseError: We could not find your database: postgres. Which can be found in the database configuration file located at config/database.yml.
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM To resolve this issue:
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM - Did you create the database for this app, or delete it? You may need to create your database.
Jul 23 12:13:28 AM - Has the database name changed? Check your database.yml config has the correct database name.
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM To create your database, run:
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM bin/rails db:create
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM Caused by:
Jul 23 12:13:28 AM PG::ConnectionBad: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Jul 23 12:13:28 AM Is the server running locally and accepting connections on that socket?
Jul 23 12:13:28 AM
Jul 23 12:13:28 AM Tasks: TOP => db:create
Jul 23 12:13:28 AM (See full trace by running task with --trace)
Jul 23 12:13:28 AM ==> Build failed 😞
Jul 23 12:13:28 AM
このスクラップは2023/09/17にクローズされました