I trying to create rails application with postgresql database, but when I create database rake db:migrate bash show me an error
FATAL: role "my" does not exist
how fix?
sorry for my bad English
UPD
my@my-P5K-SE ~/ror/timonin $ rake db:create
FATAL: role "my" does not exist
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:898:in `rescue in connect'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:888:in `connect'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:568:in `initialize'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:41:in `new'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:41:in `postgresql_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:435:in `new_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:445:in `checkout_new_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:416:in `acquire_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:351:in `block in checkout'
/usr/local/rvm/rubies/ruby-2.1.3/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:350:in `checkout'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:265:in `block in connection'
/usr/local/rvm/rubies/ruby-2.1.3/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:264:in `connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:541:in `retrieve_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_handling.rb:113:in `retrieve_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_handling.rb:87:in `connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/postgresql_database_tasks.rb:8:in `connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/postgresql_database_tasks.rb:17:in `create'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:88:in `create'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:102:in `block in create_current'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:209:in `block in each_current_configuration'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:208:in `each'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:208:in `each_current_configuration'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:101:in `create_current'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/railties/databases.rake:17:in `block (2 levels) in <top (required)>'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:240:in `call'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:240:in `block in execute'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:235:in `each'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:235:in `execute'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/usr/local/rvm/rubies/ruby-2.1.3/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:172:in `invoke_with_call_chain'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:165:in `invoke'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:150:in `invoke_task'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:106:in `each'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:106:in `block in top_level'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:115:in `run_with_threads'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:100:in `top_level'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:78:in `block in run'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:176:in `standard_exception_handling'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:75:in `run'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/bin/rake:33:in `<top (required)>'
/usr/local/rvm/gems/ruby-2.1.3/bin/rake:23:in `load'
/usr/local/rvm/gems/ruby-2.1.3/bin/rake:23:in `<main>'
/usr/local/rvm/gems/ruby-2.1.3/bin/ruby_executable_hooks:15:in `eval'
/usr/local/rvm/gems/ruby-2.1.3/bin/ruby_executable_hooks:15:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "database"=>"timonin_development"}
FATAL: role "my" does not exist
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:898:in `rescue in connect'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:888:in `connect'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:568:in `initialize'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:41:in `new'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql_adapter.rb:41:in `postgresql_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:435:in `new_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:445:in `checkout_new_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:416:in `acquire_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:351:in `block in checkout'
/usr/local/rvm/rubies/ruby-2.1.3/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:350:in `checkout'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:265:in `block in connection'
/usr/local/rvm/rubies/ruby-2.1.3/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:264:in `connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:541:in `retrieve_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_handling.rb:113:in `retrieve_connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/connection_handling.rb:87:in `connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/postgresql_database_tasks.rb:8:in `connection'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/postgresql_database_tasks.rb:17:in `create'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:88:in `create'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:102:in `block in create_current'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:209:in `block in each_current_configuration'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:208:in `each'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:208:in `each_current_configuration'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/tasks/database_tasks.rb:101:in `create_current'
/usr/local/rvm/gems/ruby-2.1.3/gems/activerecord-4.1.4/lib/active_record/railties/databases.rake:17:in `block (2 levels) in <top (required)>'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:240:in `call'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:240:in `block in execute'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:235:in `each'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:235:in `execute'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/usr/local/rvm/rubies/ruby-2.1.3/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:172:in `invoke_with_call_chain'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/task.rb:165:in `invoke'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:150:in `invoke_task'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:106:in `each'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:106:in `block in top_level'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:115:in `run_with_threads'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:100:in `top_level'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:78:in `block in run'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:176:in `standard_exception_handling'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/lib/rake/application.rb:75:in `run'
/usr/local/rvm/gems/ruby-2.1.3/gems/rake-10.3.2/bin/rake:33:in `<top (required)>'
/usr/local/rvm/gems/ruby-2.1.3/bin/rake:23:in `load'
/usr/local/rvm/gems/ruby-2.1.3/bin/rake:23:in `<main>'
/usr/local/rvm/gems/ruby-2.1.3/bin/ruby_executable_hooks:15:in `eval'
/usr/local/rvm/gems/ruby-2.1.3/bin/ruby_executable_hooks:15:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "database"=>"timonin_test"}
database.yml
# PostgreSQL. Versions 8.2 and up are supported.
#
# Install the pg driver:
# gem install pg
# On OS X with Homebrew:
# gem install pg -- --with-pg-config=/usr/local/bin/pg_config
# On OS X with MacPorts:
# gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
# gem install pg
# Choose the win32 build.
# Install PostgreSQL and put its /bin directory on your path.
#
# Configure Using Gemfile
# gem 'pg'
#
default: &default
adapter: postgresql
encoding: unicode
# For details on connection pooling, see rails configuration guide
# http://guides.rubyonrails.org/configuring.html#database-pooling
pool: 5
development:
<<: *default
database: timonin_development
# The specified database role being used to connect to postgres.
# To create additional roles in postgres see `$ createuser --help`.
# When left blank, postgres will use the default role. This is
# the same name as the operating system user that initialized the database.
#username: timonin
# The password associated with the postgres role (username).
#password:
# Connect on a TCP socket. Omitted by default since the client uses a
# domain socket that doesn't need configuration. Windows does not have
# domain sockets, so uncomment these lines.
#host: localhost
# The TCP port the server listens on. Defaults to 5432.
# If your server runs on a different port number, change accordingly.
#port: 5432
# Schema search path. The server defaults to $user,public
#schema_search_path: myapp,sharedapp,public
# Minimum log levels, in increasing order:
# debug5, debug4, debug3, debug2, debug1,
# log, notice, warning, error, fatal, and panic
# Defaults to warning.
#min_messages: notice
# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
<<: *default
database: timonin_test
# As with config/secrets.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password as a unix environment variable when you boot
# the app. Read http://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full rundown on how to provide these environment variables in a
# production deployment.
#
# On Heroku and other platform providers, you may have a full connection URL
# available as an environment variable. For example:
#
# DATABASE_URL="postgres://myuser:mypass@localhost/somedatabase"
#
# You can use this database configuration with:
#
# production:
# url: <%= ENV['DATABASE_URL'] %>
#
production:
<<: *default
database: timonin_production
username: timonin
password: <%= ENV['TIMONIN_DATABASE_PASSWORD'] %>
причины проблемы
Postgresql не может использовать пользователя root для запуска службы, поэтому вы должны переключиться на другого пользователя, чтобы запустить обычную службу, но иногда нам нужно использовать пользователя root для использования postgresql
Решение
метод первый
Не используйте postgresql как пользователь root без необходимости
Метод второй
Вручную создайте корневую роль, здесь я использую имя пользователя postgres для запуска службы postgresql
su postgres
# Создать пользователя root
postgres=#create user root with password 'password';
CREATE ROLE
# Предоставить права доступа к базе данных пользователю root
postgres=# GRANT ALL PRIVILEGES ON DATABASE mydatabase to root;
GRANT
# Измените пользователя на суперпользователя (см. Фактические потребности)
postgres=# ALTER ROLE root WITH SUPERUSER;
postgres=# q
Вы также можете напрямую создать root как суперпользователь, вход в систему означает разрешение входа в систему
CREATE ROLE root superuser PASSWORD 'password' login;
Вопрос:
Я создаю команду bash в PHP скрипт. Встроенная команда такова:
su postgres -c "for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" demodoo` ;do psql -c "alter table $tbl owner to postgres" demodoo ;done "
Когда я пытаюсь запустить эту команду в оболочке, я получаю эту ошибку:
psql: FATAL: role "root" does not exist
Почему это происходит, тогда как я выполняю команду под пользователем postgres?
Спасибо
Cheers,
ИЗМЕНИТЬ
Я меняю команду на
sudo -u postgres for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" demodoo` ;do psql -c "alter table $tbl owner to postgres" demodoo ;done
но теперь я получаю еще одну ошибку, которая не может понять происхождение:
-bash: syntax error near unexpected token `do'
Ответ №1
попробовать:
sudo -u postgres psql
> CREATE USER root WITH SUPERUSER;
Ответ №2
Я хочу, чтобы он работал, сохраняя содержимое команды
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" demodoo` ;do psql -c "alter table $tbl owner to postgres" demodoo ;done
в файле myfile.sh, затем вызовите файл следующим образом:
sudo -u postgres /bin/bash myfile.sh
Спасибо за помощь
I started a postgres docker container using
sudo docker run --name some-postgres -d postgres
and then attempted to connect to it using
sudo docker run -it --rm --link some-postgres:postgres postgres psql -h postgres
which gives the error psql: FATAL: role "root" does not exist
These commands are exactly the same as the ones listed on the docker hub page Only run as root and without -e POSTGRES_PASSWORD=mysecretpassword
Have I done something wrong here? What could be causing this?
asked Jun 19, 2018 at 13:56
$ docker run --rm --name some-postgres -d postgres
cb2ddbb0f4f715077ebc1bfc2dc7151e5a6d07cd374c28be1db6d6ad77b9b16a
$ docker run -it --rm --link some-postgres:postgres postgres psql -h postgres -U postgres
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.
postgres=#
You forgot the -U postgres
answered Jun 25, 2018 at 22:28
You must log in to answer this question.
Not the answer you’re looking for? Browse other questions tagged
.
Not the answer you’re looking for? Browse other questions tagged
.
ВНИМАНИЕ ! ! ! Если мы работаем от root пользователя, то мы не сможем подключиться к СУБД PostgreSQL. Мы получим ошибку вида.
psql: ошибка: подключиться к серверу через сокет "/var/run/postgresql/.s.PGSQL.5432" не удалось: ВАЖНО: роль "root" не существует
Нам нужно изменить пользователя в терминале Debian. Меняем root на кого? После установки PostgreSQL в систему, вместе с ней устанавливается и новый пользователь. Его зовут postgres. По умолчанию он не имеет пароля для входа.
Команда смены пользователя root на postgres в Debian:
su - postgres
Обратите внимание на терминальную оболочку. В ней изменилась запись пользователя:
Теперь мы сможем работать с утилитой psql. Подключаемся к СУБД при помощи утилиты:
psql
Нам возвращаются версии ПО.
Обратите внимание на самую низкую запись:
postgres=#
Она говорит о том, что теперь мы находимся в самом интерфейсе СУБД PostgreSQL. Ещё она говорит о том, что мы сразу подключились к базе данных, которая тоже называется postgres. Есть пользователь postgres, а есть база данных postgres. Оба они устанавливаются по умолчанию. Не путайте! С этого момент мы можем начать вводить команды.
Во внутреннем интерфейсе вводим мета-команду:
du
или
dg
Вы получите список доступных пользователей в системе PostgreSQL. Вам будут известны их атрибуты ролей и список групп ролей, членами которых они являются.
В нашем случае есть только один пользователь. Это тот самый postgres, от имени которого мы подключились к СУБД.
Справка
Метакоманды — это всё, что вводится в psql не взятое в кавычки и начинающееся с обратной косой черты, является метакомандой psql и обрабатывается самим psql. Эти команды делают psql полезным для задач администрирования и разработки скриптов.
Формат команды psql следующий: обратная косая черта, сразу за ней команда, затем аргументы. Аргументы отделяются от команды и друг от друга любым количеством пробелов.
Чтобы выйти нужно использовать клавишу с буквой Q.
Информационные ссылки
Список команд программы PSQL — https://postgrespro.ru/docs/postgresql/14/app-psql
Официальный сайт PostgreSQL — https://www.postgresql.org
Графический веб-интерфейс для управления СУБД PostgreSQL — https://www.pgadmin.org
In this document I am going to discuss about some of the most frequent errors faced by users while connecting to PostgreSQL. This is document is to help the beginners who started working on PostgreSQL. PostgreSQL has a process called postmaster which acts as listener process, by default the process listens on 5432.
There are different tools available to connect to PostgreSQL, here I am going to use the psql command line tool but there is no difference in error messages.
ERROR – 1
[root@localhost ~]# psql
psql: error: FATAL: role “root” does not exist
By default “psql” command line tool use the operating system user as the database user and localhost the hostname if the user name is not passed as argument, here I logged in as the root user and tried to login to PostgreSQL without username which caused the error.
Solution
give the username when logging into the database, by default the psql command line take the username as the database name, so no need to pass the database as I am trying to connect to PostgreSQL.
psql -U postgres -h localhost
ERROR – 2
[root@localhost ~]# psql -U postgres
psql: error: FATAL: Peer authentication failed for user “postgres”
Solution
By default PostgreSQL installation using yum repository configure the pg_hba.conf with peer, ident authentication. So change the peer and ident authentication methods to md5 and reload the configuration.
[root@localhost data]# psql -U postgres
Password for user postgres:
psql (12.5)
Type "help" for help.
postgres=#
ERROR – 3
[root@localhost data]# psql -h 192.168.159.151 -U postgres -d postgres
psql: error: could not connect to server: Connection refused
Is the server running on host “192.168.159.151” and accepting
TCP/IP connections on port 5432?
This is the common error users get when they connect to PostgreSQL. The error represents there is no listener for host and port combination.
Solution
Most common issue is listener_address parameter is set to ‘localhost’ or port set to a non default value.
postgres=# show listen_addresses ;
listen_addresses
------------------
localhost
(1 row)
postgres=# show port;
port
------
5432
(1 row)
Here the issue is listener_address is localhost, so changed it to ‘*’ where it listens on all ip’s in my host. I have only one ip configured, if your server has multiple ip’s then set the ip address on which the PostgreSQL connections should happen.
Configure the listener_address or change the port and restart the PostgreSQL cluster.
postgres=# alter system set listen_addresses to '*';
ALTER SYSTEM
postgres=# q
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-12-20 07:12:20.317 PST [3051] LOG: starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-12-20 07:12:20.318 PST [3051] LOG: listening on IPv4 address "0.0.0.0", port 5432
2020-12-20 07:12:20.318 PST [3051] LOG: listening on IPv6 address "::", port 5432
2020-12-20 07:12:20.319 PST [3051] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-12-20 07:12:20.320 PST [3051] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 07:12:20.332 PST [3051] LOG: redirecting log output to logging collector process
2020-12-20 07:12:20.332 PST [3051] HINT: Future log output will appear in directory "log".
done
server started
ERROR – 4
-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: no pg_hba.conf entry for host “192.168.159.151”, user “postgres”, database “postgres”, SSL off
This is another common error which users will face due to missing entry for the user host address (ip address) in pg_hba.conf file. host based authentication file presents in data directory which has a list of ip addresses or host addresses allowed to connect to PostgreSQL.
Solution
Make an entry like below for the user host or subnet in pg_hba.conf file. Any ip address with 192.168.X.X will be able to connect to the host.
host all all 192.168.0.0/16 md5ERROR – 5
-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: sorry, too many clients already
-bash-4.2$ psql -h 192.168.159.151 -U viswamitra -d postgres
psql: error: FATAL: remaining connection slots are reserved for non-replication superuser connections
-bash-4.2$ psql -U viswamitra -d postgres -h 192.168.159.151
psql: error: FATAL: too many connections for role “viswamitra”
This is also a common error users face after started using the database in application. This errors will come after the connection limit is reached to the maximum configured value.
Connection limit can be set at different levels
- cluster – which is applicable for all users and databases
- user – which is applicable for the user
- database – which is applicable for the given database
Solution
Login to the database as a super user, if there are any idle connections from longer time close them and check the application connection pool settings to close the idle connections after some time interval.
postgres=# select pid,datname,usename,application_name,state,now()-state_change as idle_duration from pg_stat_activity where state = 'idle';
pid | datname | usename | application_name | state | idle_duration
------+----------+------------+------------------+-------+-----------------
3656 | alex | postgres | psql | idle | 00:14:06.647055
3652 | alex | postgres | psql | idle | 00:14:11.718486
3623 | postgres | viswamitra | psql | idle | 00:15:47.530912
(3 rows)
postgres=# select pg_terminate_backend(3656);
pg_terminate_backend
----------------------
t
(1 row)
If there are no idle sessions to cancel and the error is first and second one in the list increase the “max_connections” in postgresql.conf file. This requires a reboot of the PostgreSQL cluster to make it effect.
postgres=# alter system set max_connections to '1000';
ALTER SYSTEM
postgres=#
/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restartFor third error (5.3) , the connection limit is configured at the user level, it can be changed using the alter user statement.
postgres=# alter user viswamitra connection limit 100;
ALTER ROLE
I’m starting to work with an existing Rails project that uses Docker. I’ve used Rails for a long time but never Docker.
After I do a docker build . I try to do a docker-compose up, but I get:
FATAL: role «root» does not exist
/usr/local/bundle/gems/activerecord-4.2.5.2/lib/active_record/connection_adapters/postgresql_adapter.rb:661:in `rescue in connect’: FATAL: role «root» does not exist (ActiveRecord::NoDatabaseError)
It seems to me that the Docker machine is probably trying to connect to the database as the root user, but there’s no role called root, so the connection is rightly failing.
The thing I don’t know is why Docker is apparently trying to connect to the database as root and how to get it to use the right user.
Here’s my database.yml:
development:
database: my_app_development
adapter: postgresql
encoding: unicode
pool: 5
Any help is appreciated.
Edit: here’s my docker-compose.yml:
web:
build: .
volumes:
- .:/my_app
ports:
- "3000:3000"
links:
- postgres
- redis
- mailcatcher
env_file:
- 'config/application.yml'
postgres:
image: postgres:9.4
ports:
- "5432"
env_file:
- 'config/database.yml'
redis:
image: redis:3.0.6
mailcatcher:
image: schickling/mailcatcher
ports:
- "1080:1080"
asked Jun 22, 2016 at 14:16
Jason SwettJason Swett
43k65 gold badges216 silver badges348 bronze badges
4
Postgres image expects POSTGRES_USER, POSTGRES_PASSWORD and POSTGRES_DB to be provided. Otherwise it will use default values. Create a .env file in the root directory of your project:
# .env file
POSTGRES_USER=testuser
POSTGRES_PASSWORD=testpass
POSTGRES_DB=db_development
and change you docker-compose file as:
web:
build: .
volumes:
- .:/my_app
ports:
- 3000:3000
depends_on:
- postgres
- redis
- mailcatcher
postgres:
image: postgres:9.4
ports:
- 5432:5432
env_file: .env
redis:
image: redis:3.0.6
mailcatcher:
image: schickling/mailcatcher
ports:
- 1080:1080
You could also provide the environment variables without .env file:
web:
build: .
volumes:
- .:/my_app
ports:
- 3000:3000
depends_on:
- postgres
- redis
- mailcatcher
postgres:
image: postgres:9.4
ports:
- 5432:5432
environment:
- POSTGRES_USER=testuser
- POSTGRES_PASSWORD=testpass
- POSTGRES_DB=db_development
redis:
image: redis:3.0.6
mailcatcher:
image: schickling/mailcatcher
ports:
- 1080:1080
and update your database.yml file:
default: &default
adapter: postgresql
encoding: unicode
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: <%= ENV['POSTGRES_USER'] %>
password: <%= ENV['POSTGRES_PASSWORD'] %>
host: postgres
development:
<<: *default
database: db_development
test:
<<: *default
database: db_test
production:
<<: *default
database: db_production
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 25 } %>
WARNING: Don’t use links, it’ll be removed soon
answered May 8, 2018 at 12:39
Aref AslaniAref Aslani
1,52013 silver badges26 bronze badges
You might want to update your compose and database yml as follows. With the expected db user and password in the database.yml. Also, you can make this is an environment variable. But try the default for the postgres db docker image first as follows;
database.yml
development:
database: my_app_development
adapter: postgresql
encoding: unicode
pool: 5
username: postgres
password:
host: postgres(db name in docker-compose.yml)
docker-compose.yml
web:
build: .
command: bundle exec rails s -p 3000 -b '0.0.0.0'
volumes:
- .:/my_app
ports:
- "3000:3000"
links:
- postgres
- redis
- mailcatcher
postgres:
image: postgres:9.4
ports:
- "5432"
redis:
image: redis:3.0.6
mailcatcher:
image: schickling/mailcatcher
ports:
- "1080:1080"
I dont think you want to keep
env_file:
- 'config/database.yml'
and
env_file:
- 'config/application.yml'
Then create the databases with docker-compose run web rake db:create
I added the command instruction because I dont know what your Dockerfile looks like. But if you have a successful build of the app image with docker build -t app-name ., you can remove it and just run docker-compose up.
answered Jun 22, 2016 at 16:25
Olalekan SogunleOlalekan Sogunle
2,2791 gold badge20 silver badges26 bronze badges
0
Without an explicit user set in the database.yml it will attempt to use the root user as this is the same user postgres is running under in the container. To fix this, try setting your database.yml as:
development:
database: my_app_development
adapter: postgresql
encoding: unicode
pool: 5
username: postgres
Note the addition of the username field.
answered Oct 17, 2017 at 21:39
SamuelSamuel
2,3211 gold badge22 silver badges39 bronze badges
I check
test: [ "CMD", "pg_isready", "-q", "-d", "kong", "-U", "kong" ]
Don`t have error
But have another error
docker-compose up 0.2s
[+] Running 4/3
- Network docker-compose-healthcheck_default Created 0.0s
- Container kong-postgres Created 2.8s
- Container kong-migration Created 0.1s
- Container kong Created 0.1s
Attaching to kong, kong-migration, kong-postgres
kong-postgres | ********************************************************************************
kong-postgres | WARNING: POSTGRES_HOST_AUTH_METHOD has been set to "trust". This will allow
kong-postgres | anyone with access to the Postgres port to access your database without
kong-postgres | a password, even if POSTGRES_PASSWORD is set. See PostgreSQL
kong-postgres | documentation about "trust":
kong-postgres | https://www.postgresql.org/docs/current/auth-trust.html
kong-postgres | In Docker's default configuration, this is effectively any other
kong-postgres | container on the same system.
kong-postgres |
kong-postgres | It is not recommended to use POSTGRES_HOST_AUTH_METHOD=trust. Replace
kong-postgres | it with "-e POSTGRES_PASSWORD=password" instead to set a password in
kong-postgres | "docker run".
kong-postgres | ********************************************************************************
kong-postgres | The files belonging to this database system will be owned by user "postgres".
kong-postgres | This user must also own the server process.
kong-postgres |
kong-postgres | The database cluster will be initialized with locale "en_US.utf8".
kong-postgres | The default database encoding has accordingly been set to "UTF8".
kong-postgres | The default text search configuration will be set to "english".
kong-postgres |
kong-postgres | Data page checksums are disabled.
kong-postgres |
kong-postgres | fixing permissions on existing directory /var/lib/postgresql/data ... ok
kong-postgres | creating subdirectories ... ok
kong-postgres | selecting default max_connections ... 100
kong-postgres | selecting default shared_buffers ... 128MB
kong-postgres | selecting default timezone ... Etc/UTC
kong-postgres | selecting dynamic shared memory implementation ... posix
kong-postgres | creating configuration files ... ok
kong-postgres | creating template1 database in /var/lib/postgresql/data/base/1 ... ok
kong-postgres | initializing pg_authid ... ok
kong-postgres | setting password ... ok
kong-postgres | initializing dependencies ... ok
kong-postgres | creating system views ... ok
kong-postgres | loading system objects' descriptions ... ok
kong-postgres | creating collations ... ok
kong-postgres | creating conversions ... ok
kong-postgres | creating dictionaries ... ok
kong-postgres | setting privileges on built-in objects ... ok
kong-postgres | creating information schema ... ok
kong-postgres | loading PL/pgSQL server-side language ... ok
kong-postgres | vacuuming database template1 ... ok
kong-postgres | copying template1 to template0 ... ok
kong-postgres | copying template1 to postgres ... ok
kong-postgres | syncing data to disk ... ok
kong-postgres |
kong-postgres | Success. You can now start the database server using:
kong-postgres |
kong-postgres | pg_ctl -D /var/lib/postgresql/data -l logfile start
kong-postgres |
kong-postgres |
kong-postgres | WARNING: enabling "trust" authentication for local connections
kong-postgres | You can change this by editing pg_hba.conf or using the option -A, or
kong-postgres | --auth-local and --auth-host, the next time you run initdb.
kong-postgres | waiting for server to start....LOG: database system was shut down at 2022-02-15 04:04:37 UTC
kong-postgres | LOG: MultiXact member wraparound protections are now enabled
kong-postgres | LOG: autovacuum launcher started
kong-postgres | LOG: database system is ready to accept connections
kong-postgres | done
kong-postgres | server started
kong-postgres | CREATE DATABASE
kong-postgres |
kong-postgres |
kong-postgres | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
kong-postgres |
kong-postgres | waiting for server to shut down....LOG: received fast shutdown request
kong-postgres | LOG: aborting any active transactions
kong-postgres | LOG: autovacuum launcher shutting down
kong-postgres | LOG: shutting down
kong-postgres | LOG: database system is shut down
kong-postgres | done
kong-postgres | server stopped
kong-postgres |
kong-postgres | PostgreSQL init process complete; ready for start up.
kong-postgres |
kong-postgres | LOG: database system was shut down at 2022-02-15 04:04:38 UTC
kong-postgres | LOG: MultiXact member wraparound protections are now enabled
kong-postgres | LOG: autovacuum launcher started
kong-postgres | LOG: database system is ready to accept connections
kong-migration | Bootstrapping database...
kong-migration | migrating core on database 'kong'...
kong-migration | core migrated up to: 000_base (executed)
kong-migration | core migrated up to: 003_100_to_110 (executed)
kong-migration | core migrated up to: 004_110_to_120 (executed)
kong-migration | core migrated up to: 005_120_to_130 (executed)
kong-migration | core migrated up to: 006_130_to_140 (executed)
kong-migration | core migrated up to: 007_140_to_150 (executed)
kong-migration | core migrated up to: 008_150_to_200 (executed)
kong-migration | core migrated up to: 009_200_to_210 (executed)
kong-migration | core migrated up to: 010_210_to_211 (executed)
kong-migration | core migrated up to: 011_212_to_213 (executed)
kong-migration | core migrated up to: 012_213_to_220 (executed)
kong-migration | core migrated up to: 013_220_to_230 (executed)
kong-migration | core migrated up to: 014_230_to_270 (executed)
kong-migration | migrating acl on database 'kong'...
kong-migration | acl migrated up to: 000_base_acl (executed)
kong-migration | acl migrated up to: 002_130_to_140 (executed)
kong-migration | acl migrated up to: 003_200_to_210 (executed)
kong-migration | acl migrated up to: 004_212_to_213 (executed)
kong-migration | migrating acme on database 'kong'...
kong-migration | acme migrated up to: 000_base_acme (executed)
kong-migration | migrating basic-auth on database 'kong'...
kong | 2022/02/15 04:04:50 [warn] 1#0: the "user" directive makes sense only if the master process runs with super-user privileges, ignored in /usr/local/kong/nginx.conf:6
kong | nginx: [warn] the "user" directive makes sense only if the master process runs with super-user privileges, ignored in /usr/local/kong/nginx.conf:6
kong | 2022/02/15 04:04:50 [error] 1#0: init_by_lua error: /usr/local/share/lua/5.1/kong/cmd/utils/migrations.lua:20: New migrations available; run 'kong migrations up' to proceed
kong | stack traceback:
kong | [C]: in function 'error'
kong | /usr/local/share/lua/5.1/kong/cmd/utils/migrations.lua:20: in function 'check_state'
kong | /usr/local/share/lua/5.1/kong/init.lua:506: in function 'init'
kong | init_by_lua:3: in main chunk
kong | nginx: [error] init_by_lua error: /usr/local/share/lua/5.1/kong/cmd/utils/migrations.lua:20: New migrations available; run 'kong migrations up'
to proceed
kong | stack traceback:
kong | [C]: in function 'error'
kong | /usr/local/share/lua/5.1/kong/cmd/utils/migrations.lua:20: in function 'check_state'
kong | /usr/local/share/lua/5.1/kong/init.lua:506: in function 'init'
kong | init_by_lua:3: in main chunk
kong-migration | basic-auth migrated up to: 000_base_basic_auth (executed)
kong-migration | basic-auth migrated up to: 002_130_to_140 (executed)
kong-migration | basic-auth migrated up to: 003_200_to_210 (executed)
kong-migration | migrating bot-detection on database 'kong'...
kong-migration | bot-detection migrated up to: 001_200_to_210 (executed)
kong-migration | migrating hmac-auth on database 'kong'...
kong-migration | hmac-auth migrated up to: 000_base_hmac_auth (executed)
kong-migration | hmac-auth migrated up to: 002_130_to_140 (executed)
kong-migration | hmac-auth migrated up to: 003_200_to_210 (executed)
kong-migration | migrating ip-restriction on database 'kong'...
kong-migration | ip-restriction migrated up to: 001_200_to_210 (executed)
kong-migration | migrating jwt on database 'kong'...
kong-migration | jwt migrated up to: 000_base_jwt (executed)
kong-migration | jwt migrated up to: 002_130_to_140 (executed)
kong-migration | jwt migrated up to: 003_200_to_210 (executed)
kong-migration | migrating key-auth on database 'kong'...
kong-migration | key-auth migrated up to: 000_base_key_auth (executed)
kong-migration | key-auth migrated up to: 002_130_to_140 (executed)
kong-migration | key-auth migrated up to: 003_200_to_210 (executed)
kong-migration | migrating oauth2 on database 'kong'...
kong-migration | oauth2 migrated up to: 000_base_oauth2 (executed)
kong-migration | oauth2 migrated up to: 003_130_to_140 (executed)
kong-migration | oauth2 migrated up to: 004_200_to_210 (executed)
kong-migration | oauth2 migrated up to: 005_210_to_211 (executed)
kong-migration | migrating rate-limiting on database 'kong'...
kong-migration | rate-limiting migrated up to: 000_base_rate_limiting (executed)
kong-migration | rate-limiting migrated up to: 003_10_to_112 (executed)
kong-migration | rate-limiting migrated up to: 004_200_to_210 (executed)
kong-migration | migrating response-ratelimiting on database 'kong'...
kong-migration | response-ratelimiting migrated up to: 000_base_response_rate_limiting (executed)
kong-migration | migrating session on database 'kong'...
kong-migration | session migrated up to: 000_base_session (executed)
kong-migration | session migrated up to: 001_add_ttl_index (executed)
kong-migration | 42 migrations processed
kong-migration | 42 executed
kong-migration | Database is up-to-date
kong exited with code 1
kong-migration exited with code 0
kong | 2022/02/15 04:04:52 [warn] 1#0: the "user" directive makes sense only if the master process runs with super-user privileges, ignored in /usr/local/kong/nginx.conf:6
kong | nginx: [warn] the "user" directive makes sense only if the master process runs with super-user privileges, ignored in /usr/local/kong/nginx.conf:6
kong | 2022/02/15 04:04:52 [notice] 1#0: using the "epoll" event method
kong | 2022/02/15 04:04:52 [notice] 1#0: openresty/1.19.9.1
kong | 2022/02/15 04:04:52 [notice] 1#0: built by gcc 10.3.1 20210424 (Alpine 10.3.1_git20210424)
kong | 2022/02/15 04:04:52 [notice] 1#0: OS: Linux 5.10.60.1-microsoft-standard-WSL2
kong | 2022/02/15 04:04:52 [notice] 1#0: getrlimit(RLIMIT_NOFILE): 1048576:1048576
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker processes
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker process 1098
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker process 1099
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker process 1100
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker process 1101
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker process 1102
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker process 1103
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker process 1104
kong | 2022/02/15 04:04:52 [notice] 1#0: start worker process 1105
kong | 2022/02/15 04:04:52 [notice] 1099#0: *3 [kong] init.lua:311 only worker #0 can manage, context: init_worker_by_lua*
kong | 2022/02/15 04:04:52 [notice] 1101#0: *4 [kong] init.lua:311 only worker #0 can manage, context: init_worker_by_lua*
kong | 2022/02/15 04:04:52 [notice] 1098#0: *1 [lua] warmup.lua:92: single_dao(): Preloading 'services' into the core_cache..., context: init_worker_by
context: init_worker_by_lua*
kong | 2022/02/15 04:04:52 [notice] 1103#0: *5 [kong] init.lua:311 only worker #0 can manage, context: init_worker_by_lua*
kong | 2022/02/15 04:04:52 [notice] 1105#0: *8 [kong] init.lua:311 only worker #0 can manage, context: init_worker_by_lua*
kong | 2022/02/15 04:04:52 [notice] 1100#0: *2 [kong] init.lua:311 only worker #0 can manage, context: init_worker_by_lua*
kong | 2022/02/15 04:04:52 [notice] 1098#0: *1 [lua] warmup.lua:129: single_dao(): finished preloading 'services' into the core_cache (in 0ms), contextcache (in 0ms), context: init_worker_by_lua*
kong | 2022/02/15 04:04:52 [notice] 1102#0: *7 [kong] init.lua:311 only worker #0 can manage, context: init_worker_by_lua*
kong | 2022/02/15 04:04:52 [notice] 1104#0: *6 [kong] init.lua:311 only worker #0 can manage, context: init_worker_by_lua*
kong | 2022/02/15 04:04:57 [crit] 1105#0: *15 [lua] balancers.lua:240: create_balancers(): failed loading initial list of upstreams: failed to get fromams: failed to get from node cache: could not acquire callback lock: timeout, context: ngx.timer
kong | 2022/02/15 04:04:57 [crit] 1103#0: *12 [lua] balancers.lua:240: create_balancers(): failed loading initial list of upstreams: failed to get fromams: failed to get from node cache: could not acquire callback lock: timeout, context: ngx.timer
kong | 2022/02/15 04:04:57 [crit] 1100#0: *14 [lua] balancers.lua:240: create_balancers(): failed loading initial list of upstreams: failed to get fromams: failed to get from node cache: could not acquire callback lock: timeout, context: ngx.timer
kong | 2022/02/15 04:04:57 [crit] 1102#0: *16 [lua] balancers.lua:240: create_balancers(): failed loading initial list of upstreams: failed to get fromams: failed to get from node cache: could not acquire callback lock: timeout, context: ngx.timer
kong | 2022/02/15 04:04:57 [crit] 1101#0: *13 [lua] balancers.lua:240: create_balancers(): failed loading initial list of upstreams: failed to get fromams: failed to get from node cache: could not acquire callback lock: timeout, context: ngx.timer
kong | 2022/02/15 04:04:57 [crit] 1098#0: *17 [lua] balancers.lua:240: create_balancers(): failed loading initial list of upstreams: failed to get fromams: failed to get from node cache: could not acquire callback lock: timeout, context: ngx.timer
kong | 2022/02/15 04:04:57 [crit] 1104#0: *18 [lua] balancers.lua:240: create_balancers(): failed loading initial list of upstreams: failed to get fromams: failed to get from node cache: could not acquire callback lock: timeout, context: ngx.timer
This article is half-done without your Comment! *** Please share your thoughts via Comment ***
A PostgreSQL Fatal error like role or username does not exist. This is a very common error which is facing by PostgreSQL Users.
Actually, this is happening because of misconfiguration of System username and Database Username.
Most of the Linux users are trying to log in PostgreSQL using root user. But actually, this is wrong.
You will need to become the operating system user under which PostgreSQL was installed to create the first user account.
Generally, the default user name is Postgres and default database is also Postgres.
Once you install PostgreSQL on Linux, you should set the password for the Postgres user which is the default super user of PostgreSQL.
Please do not try to log in with any other default Linux or Unix system user name.
Set password for the default Postgres user:
Create another role using “postgres”:
Creating new role “dbrnd”. -p = set the password and -d = allow to create database.
|
sudo -u postgres createuser dbrnd -d -P |
PostgreSQL: How to Install PostgreSQL 9.4 on Ubuntu 14.04?
PostgreSQL: How to Install PostgreSQL 9.6 on Ubuntu 16.04?
Jan 23, 2017