Error invalid data directory postgresql

Error: Invalid data directory PostgreSQL 9.2

С PostgreSQL работаю впервые и сразу такие грабли! На любую команду PostgreSQL выдает ошибку:

Задача просто сделать бэкап базы, хочу просмотреть список баз и выбрать нужные.

Это скрипты-обёртки Debian над командами postgres так ругаются, у Вас видимо пустой или левый каталог в /etc/postgresql/ или /var/lib/postgresql

А если указать точные параметры подключения к серверу?

А какой там пароль по умолчанию?

С паролем разобрался:

Вопрос каким параметром в строку внести пароль, чтобы можно было написать скрипт? Сейчас пароль запрашивается отдельно.

Разобрался, все ок! Спасибо. )))

Ну и написал бы тут ответ, мало ли кому пригодится. Я, при необходимости, пользуюсь переменной окружения PGPASSWORD (если не напутал), но может быть есть другие варианты.

Сработал вариант с файлом .pgpass в корне профиля пользователя. Подробности тут:

Ты можешь в pg_hba сделать своему пользователю подключение без проверки пароля с этого хоста, например.

Источник

tap349

problems with new homebrew versioning scheme

running brew upgrade has created quite a mess for me because of a new versioning scheme: postgresql95 formula is replaced with postgresql@9.5 (this is because homebrew/core now supports multiple versions). but this migration was not smooth and resulted in many errors, to name a few:

invalid value for parameter «TimeZone»: «UTC»

should be fixed by restarting service or the whole system according to SO.

command not found: psql

psql must have a symlink in /usr/local/bin/ (it has been added to PATH in

/.zshenv in my case) — it’s gone now for some mysterious reason.

psql: FATAL: database «db_name» does not exist

this is because running brew upgrade has created new data directory for postgresql@9.5 (/usr/local/var/postgresql@9.5) while all my databases are stored in /usr/local/var/postgres.

solution

so this is what I did to fix problems mentioned above:

• brew untap ‘homebrew/versions’ (it’s deprecated now)
• brew untap ‘caskroom/versions’ (it’s deprecated now)
• brew uninstall postgresql postgresql95 postgresql@9.5 (remove everything)
• brew install postgresql@9.5 (install latest 9.5 version)
• brew switch postgresql@9.5 9.5.6 (switch to latest 9.5 version)
• brew prune postgresql@9.5 (remove old 9.5 versions — if any)

brew link postgresql@9.5 —force (create symlink in /usr/local/bin)

it’s not recommended though — it must be better to add bin directory of specific PostgreSQL installation to PATH explicitly in

cd /usr/local/var && mv postgres postgresql@9.5 (rename directory with databases)

it might be necessary to remove existing postgresql@9.5 directory beforehand that could be created when upgrading PostgreSQL (but still double check it doesn’t contain any databases).

rm /usr/local/Cellar/postgresql95 (remove symlink to postgresql@9.5 )

I guess it has been created for compatibility reasons.

NOTE: installing postgresql formula still installs the latest version (9.6 as of now) — all directories are named just postgresql accordingly. but its binaries are not symlinked into usr/bin/local directory by default (now they all point to 9.5 installation) — if you need it run brew link postgresql —force manually.

psql: could not connect to server: Connection refused

solution

the problem usually appears after hard reboot. the latter doesn’t allow PostgreSQL to exit gracefully and delete its PID files — postmaster.pid in particular. so upon reboot PostgreSQL thinks it’s still running and corresponding service fails to start.

so just delete that PID file and start the service:

or else try to restart the service (stopping the service might remove obsolete PID file — I didn’t try this method though):

command not found: psql

solution

in my case only versioned formula of PostgreSQL ( postgresql@9.5 ) was installed but it didn’t create a symlink to psql in /usr/local/bin/.

to solve this problem either:

install the latest version of PostgreSQL ( postgresql )

unversioned formula creates a symlink in /usr/local/bin/ automatically.

create symlinks for binaries from old version of PostgreSQL manually

add the whole bin/ from old version of PostgreSQL to PATH

psql: could not connect to server: No such file or director

solution

to diagnose this and similar problems run postgres in the foreground (see brew info postgresql output ):

it turns out /usr/local/var/postgres contains data for PostgreSQL 9.6 (when postgresql formula was installed, 9.6 was the latest version).

=> it’s necessary to migrate existing data from a previous major version (9.6) to the latest one (10) (see brew info postgresql output ):

this command will install a previous major version of PostgreSQL (if it has been uninstalled) which is required to upgrade database.

after upgrade is complete, you can remove old major version along with its data (/usr/local/var/postgres.old):

now make sure postgresql service is started:

Источник

Error: Invalid data directory: Restarting PostgreSQL 9.1 database server #16

ubuntu precise 12.04.2 in vagrant
this portgresql cookbook

The text was updated successfully, but these errors were encountered:

Wonder where am I going wrong here? What is the proper way to setup user/password and use this server cookbook?

I’d guess that your Vagrant user is trying to run the package install command for Postgres and being rejected. Try SSHing into the vagrant box and confirming that your vagrant user is allowed to apt-get install postgresql-9.1 . Also if you still can’t install it due to the invalid data directory problem you might consider purging the vestigial postgres files laying around from previously botched installs using this purge script: http://stackoverflow.com/a/2748644/539

Personally I have a Chef override in place that adds my vagrant user to the sudoers file:
override_attributes(

Thanks very much. Appreciate help.

I’m seeing exactly the same issue, even from a fresh machine ( vagrant destroy -f; vagrant up ). I am running on vagrant’s Ubuntu 10.04 box and I do have a sudo recipe running giving vagrant passwordless sudo access similar to the above example.

If I ssh into the box and follow the purging steps outlined in the StackOverflow post (actually only the apt-get purge step needs to done) and reinstall postgres, it starts successfully. But every time the machine is torn down the Invalid data directory error resurfaces.

This seems to be a permissions issue as far as I can tell. Chef is not running the postgres service as the postgres user. If you add read, execute permissions to the posgresql dir it appears to work.

Is there a better way to fix this?

© 2023 GitHub, Inc.

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

• Expanded. Click to collapse Collapsed. Click to showDeveloper
  • Hierarchy leaf Accessibility Checklist
  • Hierarchy leaf Code Review Guidelines
  • Expanded. Click to collapse Collapsed. Click to showContribution
    • Hierarchy leaf Git Workflow
    • Hierarchy leaf Translations
  • Expanded. Click to collapse Collapsed. Click to showDeveloping Plugins
    • Hierarchy leaf Developing an OmniAuth Authentication Plugin
  • Hierarchy leaf Release Process
  • Hierarchy leaf Report a bug
  • Hierarchy leaf Security
  • Hierarchy leaf Setting up an OpenLDAP server for testing
  • Hierarchy leaf Theme Features
• Hierarchy leaf Download
• Expanded. Click to collapse Collapsed. Click to showFeature tour
  • Hierarchy leaf Release Notes OpenProject 30
  • Expanded. Click to collapse Collapsed. Click to showRelease Notes OpenProject 30 — Overview
    • Hierarchy leaf Glossary
    • Hierarchy leaf Release Notes — Accessibility
    • Hierarchy leaf Release Notes — Accessibility changes
    • Hierarchy leaf Release Notes — Add work package queries as menu items to sidebar
    • Hierarchy leaf Release Notes — Copy projects based on Templates
    • Hierarchy leaf Release Notes — Design changes
    • Hierarchy leaf Release Notes — Fixed Bugs
    • Hierarchy leaf Release Notes — Keyboard Shortcuts
    • Hierarchy leaf Release Notes — Project settings
    • Hierarchy leaf Release Notes — Ruby&Rails Update
    • Hierarchy leaf Release Notes — Security
    • Hierarchy leaf Release Notes — Timelines
    • Hierarchy leaf Release Notes — Work packages
• Hierarchy leaf Howto create animated gifs
• Hierarchy leaf Migration Squashing
• Hierarchy leaf Mod security
• Hierarchy leaf New work package page
• Hierarchy leaf OP3 to OP4 Debian upgrade
• Hierarchy leaf OP4 Ubuntu1404 Stable with MySQL in production
• Hierarchy leaf OpenProject 40 Development Setup
• Expanded. Click to collapse Collapsed. Click to showOpenProject Foundation
  • Hierarchy leaf Boards
  • Hierarchy leaf Members
  • Hierarchy leaf OPF-Meetings
  • Hierarchy leaf Statutes
• Expanded. Click to collapse Collapsed. Click to showRelease Notes
  • Hierarchy leaf OpenProject released on Bitnami
• Expanded. Click to collapse Collapsed. Click to showRelease Notes OpenProject 40 — Overview
  • Hierarchy leaf Release Notes OpenProject 40 — Accessibility improvements
  • Hierarchy leaf Release Notes OpenProject 40 — Column header functions in work package table
  • Hierarchy leaf Release Notes OpenProject 40 — Improved Design
  • Hierarchy leaf Release Notes OpenProject 40 — Integrated query title on work package page
  • Hierarchy leaf Release Notes OpenProject 40 — Integrated toolbar on work package page
  • Hierarchy leaf Release Notes OpenProject 40 — OmniAuth integration for OpenProject
  • Hierarchy leaf Release Notes OpenProject 40 — Work package details pane
• Expanded. Click to collapse Collapsed. Click to showSecurity and privacy
  • Hierarchy leaf FAQ
• Expanded. Click to collapse Collapsed. Click to showSupport
  • Expanded. Click to collapse Collapsed. Click to showDownload and Installation
    • Hierarchy leaf Installation MacOS
    • Expanded. Click to collapse Collapsed. Click to showInstallation OpenProject 3 0
      • Hierarchy leaf Debian Stable with MySQL in production
      • Hierarchy leaf Installation Ubuntu
      • Hierarchy leaf Installation Windows
      • Hierarchy leaf Installation on Centos 65 x64 with Apache and PostgreSQL 93
    • Expanded. Click to collapse Collapsed. Click to showInstallation OpenProject 40
      • Hierarchy leaf OP4 Debian Stable with MySQL in production
    • Expanded. Click to collapse Collapsed. Click to showMigration paths
      • Hierarchy leaf From Chilliproject to OpenProject
      • Hierarchy leaf Migration 15 to 30
      • Hierarchy leaf Migration 24 to 30
      • Hierarchy leaf Migration Redmine 2x › OpenProject 30
      • Hierarchy leaf OpenProject 3 Migration
    • Hierarchy leaf OpenProject 40
  • Expanded. Click to collapse Collapsed. Click to showNews
    • Hierarchy leaf New OpenProject Translations Plugin
    • Hierarchy leaf New Plugin on OpenProjectorg Local Avatars
    • Hierarchy leaf New design for OpenProject
    • Hierarchy leaf News Accessibility workshop for OpenProject
    • Hierarchy leaf News Glossary for OpenProject
    • Hierarchy leaf News Heartbleed fixed
    • Hierarchy leaf News Icon Fonts
    • Hierarchy leaf News OpenProject 30 Release
    • Hierarchy leaf News Release GitHub Integration Plugin
    • Hierarchy leaf News Success Story Deutsche Telekom
    • Hierarchy leaf News Timelines
    • Hierarchy leaf OpenProject 3013 released
    • Hierarchy leaf OpenProject 3017 released
    • Hierarchy leaf OpenProject 40 released
    • Hierarchy leaf OpenProject 40 will be coming soon
    • Hierarchy leaf OpenProject 405 released
    • Hierarchy leaf OpenProject and pkgrio
    • Hierarchy leaf OpenProject news moved to a new blog
    • Hierarchy leaf OpenProjectBitnami
    • Hierarchy leaf Packager version with plugins released («Community edition»)
    • Hierarchy leaf Registration OpenProject-Foundation
    • Hierarchy leaf Release OpenProject AuthPlugins
    • Hierarchy leaf Updates on OpenProject
    • Hierarchy leaf We need your feedback for the the new fullscreen view for work packages
  • Hierarchy leaf OpenProject Plug-Ins
• Hierarchy leaf Wiki

Cannot backup after upgrade: «Generating database backup. Error: Invalid data directory»

I had OpenProject 8 with MySQL database on Ubuntu 18.04 and everything worked.

After upgrade to OpenProject 10 and migration to PostreSQL I cannot backup:

At /var/log/openproject I do not see any relevant informations.

How can I fix it?

Backups are very important for me.

RE: Cannot backup after upgrade: «Generating database backup. Error: Invalid data directory» — Added by M V almost 3 years ago

How can I fix it?

RE: Cannot backup after upgrade: «Generating database backup. Error: Invalid data directory» — Added by Oliver Günther almost 3 years ago

This is an error from PostgreSQL that fails to find your database cluster’s data directory. How did you migrate to PostgreSQL? Did you use the packaged wizard to install a PostgreSQL database or installed one yourself?

Please also append your /etc/openproject/installer.dat (remove any passwords from it).

More information on the error itself:

RE: Cannot backup after upgrade: »Generating database backup. Error: Invalid data directory» — Added by M V almost 3 years ago

Hi, thanks fo reply.

I did following steps (upgrade from openproject 8 to 9 and after that to 10):

RE: Cannot backup after upgrade: «Generating database backup. Error: Invalid data directory» — Added by Oliver Günther almost 3 years ago

This means you manually set up the database openproject in your postgresql database?

Can you connect to it manually outside openproject with psql postgresql://openproject:secure-password@localhost/openproject» ?

RE: Cannot backup after upgrade: »Generating database backup. Error: Invalid data directory» — Added by M V almost 3 years ago

Connection works without error:

RE: Cannot backup after upgrade: «Generating database backup. Error: Invalid data directory» — Added by Oliver Günther almost 3 years ago

That’s weird, we don’t do anything special in the backup script:

It’s basically just a pg_dump -Fc $DATABASE_URL > some-path . What happens if you try to run that yourself with the URL above?

RE: Cannot backup after upgrade: »Generating database backup. Error: Invalid data directory» — Added by M V almost 3 years ago

It works without any error.

RE: Cannot backup after upgrade: «Generating database backup. Error: Invalid data directory» — Added by Oliver Günther almost 3 years ago

Curiouser and curiouser! Does your backup target /var/db/openproject/backup exist and is writable to the openproject user?

RE: Cannot backup after upgrade: »Generating database backup. Error: Invalid data directory» — Added by M V almost 3 years ago

Permissions are ok (I hope).

«openproject run backup» create empty dump file:

Источник

I’m trying to move my bot to an Ubuntu virtual server from Vultr but it’s having a problem connecting to the postgres database. I’ve tried editing the config from md5 to true, and host to local, etc. But those only give me different errors and also make it stop working on my original machine too. It’s working perfectly fine on my Windows machine. Here is the error I’m facing:

asyncpg.exceptions.InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host "[local]", user "postgres", database "xxx", SSL off

So I’ve tried to change this line:

async def create_db_pool():
    bot.pg_con = await asyncpg.create_pool(database='xxx', user='postgres', password='???')

to this:

async def create_db_pool():
    bot.pg_con = await asyncpg.create_pool(database='xxx', user='postgres', password='???', ssl=True)

and that gives me this error:

asyncpg.exceptions._base.InterfaceError: `ssl` parameter can only be enabled for TCP addresses, got a UNIX socket path: '/run/postgresql/.s.PGSQL.5432'

So I don’t know what else to try. I’ve been stuck on this for a while. If it’s relevant, it connects at the bottom of the bot.py file like this:

bot.loop.run_until_complete(create_db_pool())

Whether ssl is True or not, the database seems to still function on my Windows machine. But I can’t get it to work on my Ubuntu virtual server.

If I edit my config to this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5
# IPv6 local connections:
host    all             all             ::/0                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             0.0.0.0/0            md5
host    replication     all             ::/0                 md5

Then I get a call error like this:

OSError: Multiple exceptions: [Errno 111] Connect call failed ('::1', 5432, 0, 0), [Errno 111] Connect call failed ('127.0.0.1', 5432)

This is really driving me crazy. I have no idea what to do. I bought this virtual server to host my bot on but I can’t even get it to connect to the database.

When I simply type psql in the terminal, I get this error:

Error: Invalid data directory for cluster 12 main

Postgres is not working as intended in basically any way. I’m using Vultr.com to host the Ubuntu server, if that matters. And connecting with PuTTy.

Using Fedora, I updated my postgresql.conf and pg_hba.conf files running this:

sudo systemctl stop postgresql
sudo rm /var/lib/pgsql/data/postgresql.conf
sudo rm /var/lib/pgsql/data/pg_hba.conf
sudo cp etc/postgresql.conf /var/lib/pgsql/data/postgresql.conf
sudo cp etc/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf
sudo systemctl start postgresql

It always worked without problems. But then I changed to Debian, running this:

sudo systemctl stop postgresql
sudo rm -f /etc/postgresql/11/main/postgresql.conf
sudo rm -f /etc/postgresql/11/main/pg_hba.conf
sudo cp etc/postgresql.conf /etc/postgresql/11/main/postgresql.conf
sudo cp etc/pg_hba.conf /etc/postgresql/11/main/pg_hba.conf
sudo systemctl start postgresql

What am I doing wrong? When I try to use psql o connect from a Python app, I always get «Error: Invalid data directory for cluster 11 main». Before changing those two files, PostgreSQL worked, but now I can’t do anything.

My pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

04 Apr 2017

• problems with new homebrew versioning scheme
• psql: could not connect to server: Connection refused
• command not found: psql
• psql: could not connect to server: No such file or director
• Error: Invalid data directory for cluster 10 main
• FATAL: remaining connection slots are reserved for non-replication superuser connections
• FATAL: sorry, too many clients already
• could not access the server configuration file “/etc/postgresql/12/main/postgresql.conf”

problems with new homebrew versioning scheme

1. https://github.com/Homebrew/brew/blob/master/docs/Versions.md

running brew upgrade has created quite a mess for me because of a new
versioning scheme: postgresql95 formula is replaced with postgresql@9.5
(this is because homebrew/core now supports multiple versions). but this
migration was not smooth and resulted in many errors, to name a few:

• invalid value for parameter "TimeZone": "UTC"

  should be fixed by restarting service or the whole system according to SO.

• command not found: psql

  psql must have a symlink in /usr/local/bin/ (it has been added to PATH
  in ~/.zshenv in my case) — it’s gone now for some mysterious reason.

• psql: FATAL: database "db_name" does not exist

  this is because running brew upgrade has created new data directory for
  postgresql@9.5 (/usr/local/var/postgresql@9.5) while all my databases are
  stored in /usr/local/var/postgres.

solution

so this is what I did to fix problems mentioned above:

• brew untap 'homebrew/versions' (it’s deprecated now)
• brew untap 'caskroom/versions' (it’s deprecated now)
• brew uninstall postgresql postgresql95 postgresql@9.5 (remove everything)
• brew install postgresql@9.5 (install latest 9.5 version)
• brew switch postgresql@9.5 9.5.6 (switch to latest 9.5 version)
• brew prune postgresql@9.5 (remove old 9.5 versions — if any)

• brew link postgresql@9.5 --force (create symlink in /usr/local/bin)

  it’s not recommended though — it must be better to add bin directory of
  specific PostgreSQL installation to PATH explicitly in ~/.zshenv.

• cd /usr/local/var && mv postgres postgresql@9.5 (rename directory with
  databases)

  it might be necessary to remove existing postgresql@9.5 directory beforehand
  that could be created when upgrading PostgreSQL (but still double check it
  doesn’t contain any databases).

• rm /usr/local/Cellar/postgresql95 (remove symlink to postgresql@9.5)

  I guess it has been created for compatibility reasons.

• gem uninstall pg && bundle (reinstall pg gem)

NOTE: installing postgresql formula still installs the latest version (9.6 as
of now) — all directories are named just postgresql accordingly. but its
binaries are not symlinked into usr/bin/local directory by default (now they
all point to 9.5 installation) — if you need it run
brew link postgresql --force manually.

psql: could not connect to server: Connection refused

$ psql -d <DB_NAME>
psql: could not connect to server: Connection refused
  Is the server running locally and accepting
  connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

rails console:

PG::ConnectionBad: could not connect to server: Connection refused
  Is the server running on host "localhost" (127.0.0.1) and accepting
  TCP/IP connections on port 5432?
could not connect to server: Connection refused
  Is the server running on host "localhost" (::1) and accepting
  TCP/IP connections on port 5432?

solution

https://stackoverflow.com/a/13573207/3632318

the problem usually appears after hard reboot. the latter doesn’t allow
PostgreSQL to exit gracefully and delete its PID files — postmaster.pid in
particular. so upon reboot PostgreSQL thinks it’s still running and
corresponding service fails to start.

so just delete that PID file and start the service:

$ rm /usr/local/var/postgresql@9.5/postmaster.pid
$ brew services start postgresql@9.5

or else try to restart the service (stopping the service might remove obsolete
PID file — I didn’t try this method though):

$ brew services restart postgresql@9.5

command not found: psql

$ psql --version
zsh: command not found: psql

solution

in my case only versioned formula of PostgreSQL (postgresql@9.5) was installed
but it didn’t create a symlink to psql in /usr/local/bin/.

to solve this problem either:

• install the latest version of PostgreSQL (postgresql)

  unversioned formula creates a symlink in /usr/local/bin/ automatically.

• create symlinks for binaries from old version of PostgreSQL manually

  $ ln -s /usr/local/Cellar/postgresql@9.5/9.5.10/bin/psql /usr/local/bin/psql
  

• add the whole bin/ from old version of PostgreSQL to PATH

  ~/.zshenv:

  path=(/usr/local/Cellar/postgresql@9.5/9.5.10/bin $path)
  

psql: could not connect to server: No such file or director

$ psql -d postgres
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

solution

to diagnose this and similar problems run postgres in the foreground (see
brew info postgresql output):

$ pg_ctl -D /usr/local/var/postgres start
waiting for server to start....
[10867] FATAL: database files are incompatible with server
[10867] DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.3.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

it turns out /usr/local/var/postgres contains data for PostgreSQL 9.6 (when
postgresql formula was installed, 9.6 was the latest version).

=> it’s necessary to migrate existing data from a previous major version (9.6)
to the latest one (10) (see brew info postgresql output):

$ brew postgresql-upgrade-database
==> brew install postgresql@9.6
...
==> 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 "tap".
This user must also own the server process.
...
==> 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)

this command will install a previous major version of PostgreSQL (if it has been
uninstalled) which is required to upgrade database.

after upgrade is complete, you can remove old major version along with its data
(/usr/local/var/postgres.old):

$ brew uninstall postgresql@9.6
$ rm -rf /usr/local/var/postgres.old

now make sure postgresql service is started:

$ brew services list
Name       Status  User Plist
memcached  started tap  /Users/tap/Library/LaunchAgents/homebrew.mxcl.memcached.plist
postgresql started tap  /Users/tap/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
redis      started tap  /Users/tap/Library/LaunchAgents/homebrew.mxcl.redis.plist

and try to run psql again:

$ psql -d postgres
psql (10.3)
Type "help" for help.

postgres=#

Error: Invalid data directory for cluster 10 main

$ psql -U sith_production -d sith_production
Error: Invalid data directory for cluster 10 main

solution

1. https://stackoverflow.com/a/26183931/3632318

add -h localhost option:

$ psql -U sith_production -d sith_production -h localhost
/ enter password for database user sith_production

for this to be possible it’s necessary to have this line in
/etc/postgresql/10/main/pg_hba.conf:

local   sith_production sith_production                         md5

restart postgresql service for changes to take effect.

https://stackoverflow.com/a/26735105/3632318:

Authentication methods details: trust — anyone who can connect to the server
is authorized to access the database peer — use client’s operating system user
name as database user name to access it. md5 — password-base authentication

FATAL: remaining connection slots are reserved for non-replication superuser connections

this error indicates you have run out of connections.

solution

see PostgreSQL — Tuning on how to
increase the maximum number of allowed connections.

see PostgreSQL — Monitoring
on how to monitor open connections.

FATAL: sorry, too many clients already

most likely this error is also caused by a low number of allowed connections —
see solution for the error above.

could not access the server configuration file “/etc/postgresql/12/main/postgresql.conf”

$ sudo -u postgres /usr/lib/postgresql/12/bin/postgres 
  -D /var/lib/postgresql/12/main 
  -c config_file=/etc/postgresql/12/main/postgresql.conf

postgres: could not access the server configuration file "/etc/postgresql/12/main/postgresql.conf": No such file or directory

solution

the problem was with already existing cluster.

first I reinstalled PostgreSQL manually:

$ sudo apt autoremove --purge postgresql-12
$ sudo rm -rf /etc/postgresql
$ sudo rm -rf /run/postgresql
$ sudo apt install postgresql-12

but cluster from previous installation must have remained in the system so a new
cluster was not created when running the last command:

$ sudo apt install postgresql-12
...
Configuring already existing cluster (configuration: /etc/postgresql/12/main, data: /var/lib/postgresql/12/main, owner: 113:118)
Error: move_conffile: required configuration file /var/lib/postgresql/12/main/postgresql.conf does not exist
Error: could not create default cluster. Please create it manually with

  pg_createcluster 12 main --start

or a similar command (see 'man pg_createcluster').

=> solution is to remove old clusters manually and run installation again:

$ sudo rm -rf /var/lib/postgresql
$ sudo apt install postgresql-12

see the tip on how to remove PostgreSQL completely in PostgreSQL — Tips.

05/13/2018

DISCLAIMER: This is for postgresql 10 and ubuntu 18.04, and may or may not work for other versions. PS: If you have been tampering with the language settings lately, please tell me, because there may be a connection with the postgres failure

Yesterday I was having the exact same problem, and nobody on the entire internet could help me, so I went rogue… And it worked!

First and foremost, if you have any database with data you are interested in keeping, I cant help you there; you will have to figure out a way to backup all your data.

Now onto the actual steps (this is exactly what I did, the steps between [ ] you may skip):

sudo apt remove —purge postgres*
[ sudo apt remove —purge pg* ]
sudo apt autoremove
sudo apt autoclean
sudo apt clean
[ sudo find / -name «*postgres*» -type f -delete ] -> this may delete any backups

Now make sure you have this line in /etc/apt/sources.list

deb http://cz.archive.ubuntu.com/ubuntu bionic main

If you don’t, just add it… Lets continue:

sudo apt update
sudo apt upgrade
sudo apt install -y postgresql-10 postgresql-contrib postgresql-client
[ sudo apt install -y postgresql-server pgadmin3 ] -> this you may need for metasploit
[ sudo reboot ]

Now you have to check if the necessary directories were created:
/etc/postgresql/10/main -> config files
/usr/lib/postgresql/10/bin -> scripts & executables
/var/run/postgresql -> temporary files
/var/lib/postgresql/10/main -> folders

If any of those don’t exist, I cant help you. You also need to make sure the user postgres exists Lets continue:

sudo chown root /usr/lib/postgresql -R
sudo chgrp root /usr/lib/postgresql -R
sudo chmod 755 /usr/lib/postgresql -R

Now go edit /etc/environment and add this to the PATH: /usr/lib/postgresql/10/bin

sudo chown postgres /etc/postgresql -R
sudo chgrp postgres /etc/postgresql -R
sudo chmod 700 /etc/postgresql -R

sudo echo «» > /etc/postgresql/10/main/postgresql.log
sudo chown postgresql /etc/postgresql/10/main/postgresql.log
sudo chgrp postgresql /etc/postgresql/10/main/postgresql.log
sudo chmod 700 /etc/postgresql/10/main/postgresql.log

sudo mkdir /var/run/postgresql/10-main.pg_stat_tmp
sudo chown postgres /var/run/postgresql/10-main.pg_stat_tmp -R
sudo chgrp postgres /var/run/postgresql/10-main.pg_stat_tmp -R
sudo chmod 700 /var/run/postgresql/10-main.pg_stat_tmp -R

Create /var/lib/postgresql/.bashrc and write this to it

shopt -s histappend
HISTSIZE=1000
HISTFILESIZE=2000
shopt -s checkwinsize
case "$TERM" in
    xterm-color|*-256color) color_prompt=yes;;
esac
force_color_prompt=yes
if [ -n "$force_color_prompt" ]; then
    if [ -x /usr/bin/tput ] && tput setaf 1 >&/dev/null; then
    color_prompt=yes
    else
    color_prompt=
    fi
fi 
if [ "$color_prompt" = yes ]; then
    PS1='${debian_chroot:+($debian_chroot)}[33[01;32m]u@h[33[00m]:[33[01;34m]w[33[00m]$ '
else
    PS1='${debian_chroot:+($debian_chroot)}u@h:w$ '
fi
unset color_prompt force_color_prompt
if [ -x /usr/bin/dircolors ]; then
    test -r ~/.dircolors && eval "$(dircolors -b ~/.dircolors)" || eval "$(dircolors -b)"
    alias ls='ls --color=auto'
    alias grep='grep --color=auto'
    alias fgrep='fgrep --color=auto'
    alias egrep='egrep --color=auto'
fi
alias ll='ls -alF'
alias la='ls -A'
alias l='ls -CF'
if ! shopt -oq posix; then
  if [ -f /usr/share/bash-completion/bash_completion ]; then
    . /usr/share/bash-completion/bash_completion
  elif [ -f /etc/bash_completion ]; then
    . /etc/bash_completion
  fi
fi
source /etc/environment

And then:

sudo chown postgresql /var/lib/postgresql/.bashrc
sudo chgrp postgresql /var/lib/postgresql/.bashrc
sudo chmod 664 /var/lib/postgresql/.bashrc
[ sudo reboot ]

And now for the final part:

sudo su
su postgres

To start postgresql:

pg_ctl start -D /etc/postgresql/10/main -l /etc/postgresql/10/main/postgresql.log

To end it:

kill $(cat /var/run/postgresql/10-main.pid)

Basic configuration (enter the postgresql interpreter):

psql

du+ -> list postgresql users
l -> list postgresql databases
createuser and dropuser -> self explanatory
createdb and dropdb -> self explanatory

EXTRA: METASPLOIT

If anyone reading this needs postgresql for metasploit, you can follow this link (its for 16.04 but works fine in 18.04), there is a mistake however: At some point, you will need to run this:
rvm --default use ruby-${RUBY_VERSION}@metasploit-framework gem pristine --all
Instead of the suggested:
rvm --default use ruby-${RUByVERSION}@metasploit-framework

Furthermore, if you run into trouble with the ruby bundle install part, this command may save your life: gem pristine --all

If i try to start the Postgres server
Error: Invalid data directory for cluster 11 main [Fail]
It is output and it has been struggling with environment setting for 6 hours.

I went to the place where the web page was displayed yesterday the environment construction was completed
This morning my PC was restarted on my own and I thought about getting started
I was able to start suddenly.

Error message

With WSL

hogeOS @ PC: ~/$sudo service postgresql start ← (Server start command)
 * Starting PostgreSQL 11 database server
* Error: Invalid data directory for cluster 11 main [fail]

Was output.

If i try to display a web page with Rails s

PG :: ConnectionBad
could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

Is in a state that is more output.

Worry

If the server is not shut down normally, /usr/local/var/postgres/server.log will remain and it may be affected at the next connection, but I do not know where server.log is.