Psql ошибка подключиться к серверу через сокет var run postgresql s pgsql 5432

I experienced this issue when working with PostgreSQL on Ubuntu 18.04.

I checked my PostgreSQL status and realized that it was running fine using:

sudo systemctl status postgresql

I also tried restarting the PotgreSQL server on the machine using:

sudo systemctl restart postgresql

but the issue persisted:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Following Noushad’ answer I did the following:

List all the Postgres clusters running on your device:

pg_lsclusters

this gave me this output in red colour, showing that they were all down and the status also showed down:

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5434 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Restart the pg_ctlcluster for one of the server clusters. For me I restarted PG 10:

sudo pg_ctlcluster 10 main start

It however threw the error below, and the same error occurred when I tried restarting other PG clusters:

Job for postgresql@10-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@10-main.service" and "journalctl -xe" for details.

Check the log for errors, in this case mine is PG 10:

sudo nano /var/log/postgresql/postgresql-10-main.log

I saw the following error:

2020-09-29 02:27:06.445 WAT [25041] FATAL:  data directory "/var/lib/postgresql/10/main" has group or world access
2020-09-29 02:27:06.445 WAT [25041] DETAIL:  Permissions should be u=rwx (0700).
pg_ctl: could not start server
Examine the log output.

This was caused because I made changes to the file permissions for the PostgreSQL data directory.

I fixed it by running the command below. I ran the command for the 3 PG clusters on my machine:

sudo chmod -R 0700 /var/lib/postgresql/10/main
sudo chmod -R 0700 /var/lib/postgresql/11/main
sudo chmod -R 0700 /var/lib/postgresql/12/main

Afterwhich I restarted each of the PG clusters:

sudo pg_ctlcluster 10 main start
sudo pg_ctlcluster 11 main start
sudo pg_ctlcluster 12 main start

And then finally I checked the health of clusters again:

pg_lsclusters

this time around everything was fine again as the status showed online:

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
12  main    5434 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

That’s all.

I hope this helps

Источник

I know this is an old track, but I got the same exact issue and I solved it using some of the information I found here. so I would like to share this with you.
I’m using Kali linux (Linux kali 6.0.0-kali6-amd64) 2022-12-19 running on VMWare VM.
When I run Metasploit database manager I get this:

    └─$ sudo msfdb reinit

[i] Database already started psql: error: connection to server on
socket «/var/run/postgresql/.s.PGSQL.5432» failed: No such file or
directory
Is the server running locally and accepting connections on that socket? psql: error: connection to server on socket
«/var/run/postgresql/.s.PGSQL.5432» failed: No such file or directory
Is the server running locally and accepting connections on that socket? psql: error: connection to server on socket
«/var/run/postgresql/.s.PGSQL.5432» failed: No such file or directory
Is the server running locally and accepting connections on that socket? [+] Deleting configuration file
/usr/share/metasploit-framework/config/database.yml [+] Stopping
database [+] Starting database psql: error: connection to server on
socket «/var/run/postgresql/.s.PGSQL.5432» failed: No such file or
directory
Is the server running locally and accepting connections on that socket? [+] Creating database user ‘msf’ createuser: error:
connection to server on socket «/var/run/postgresql/.s.PGSQL.5432»
failed: No such file or directory
Is the server running locally and accepting connections on that socket? psql: error: connection to server on socket
«/var/run/postgresql/.s.PGSQL.5432» failed: No such file or directory
Is the server running locally and accepting connections on that socket? [+] Creating databases ‘msf’ createdb: error: connection
to server on socket «/var/run/postgresql/.s.PGSQL.5432» failed: No
such file or directory
Is the server running locally and accepting connections on that socket? psql: error: connection to server on socket
«/var/run/postgresql/.s.PGSQL.5432» failed: No such file or directory
Is the server running locally and accepting connections on that socket? [+] Creating databases ‘msf_test’ createdb: error:
connection to server on socket «/var/run/postgresql/.s.PGSQL.5432»
failed: No such file or directory
Is the server running locally and accepting connections on that socket? [+] Creating configuration file
‘/usr/share/metasploit-framework/config/database.yml’ [+] Creating
initial database schema rake aborted!
ActiveRecord::ConnectionNotEstablished: connection to server at «::1»,
port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections? connection to server at «127.0.0.1», port 5432 failed:
Connection refused
Is the server running on that host and accepting TCP/IP connections?
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:83:in
rescue in new_client' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:77:in new_client’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:37:in
postgresql_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in public_send’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in
new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:926:in checkout_new_connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:905:in
try_to_checkout_new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:866:in acquire_connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:588:in
checkout' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:428:in connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:1128:in
retrieve_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_handling.rb:327:in retrieve_connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_handling.rb:283:in
connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/tasks/database_tasks.rb:237:in migrate’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:92:in
block (3 levels) in <top (required)>' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:90:in each’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:90:in
block (2 levels) in <top (required)>' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/rake-13.0.6/exe/rake:27:in <top (required)>’
Caused by: PG::ConnectionBad: connection to server at "::1", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections? connection to server at "127.0.0.1", port 5432 failed:
Connection refused
Is the server running on that host and accepting TCP/IP connections?
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/pg-1.4.5/lib/pg/connection.rb:632:in
async_connect_or_reset' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/pg-1.4.5/lib/pg/connection.rb:760:in connect_to_hosts’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/pg-1.4.5/lib/pg/connection.rb:695:in
new' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/pg-1.4.5/lib/pg.rb:69:in connect’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:78:in
new_client' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/postgresql_adapter.rb:37:in postgresql_connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in
public_send' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in new_connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:926:in
checkout_new_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:905:in try_to_checkout_new_connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:866:in
acquire_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:588:in checkout’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:428:in
connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:1128:in retrieve_connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_handling.rb:327:in
retrieve_connection' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/connection_handling.rb:283:in connection’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/tasks/database_tasks.rb:237:in
migrate' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:92:in block (3 levels) in <top (required)>’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:90:in
each' /usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.7/lib/active_record/railties/databases.rake:90:in block (2 levels) in <top (required)>’
/usr/share/metasploit-framework/vendor/bundle/ruby/3.0.0/gems/rake-13.0.6/exe/rake:27:in
`<top (required)>’ Tasks: TOP => db:migrate (See full trace by running
task with —trace)

I searched every where and used every thing I could find, but nothing work. However, folowing some hints from this track. I was able to solve the issue.

When I typed :

$ pg_lsclusters

I got :

Ver Cluster Port Status Owner Data directory
Log file 14 main 5432 down, postgres
/var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 online postgres
/var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

This means that the older version «14» which uses the port «5432» required by «msfbd» is down whilst version «15» which is «online» uses «5433».
so simply changes the port using :

sudo PGPORT=5433 msfdb init

And voilà, it’s working just fine.

$ msfconsole

Call trans opt: received. 2-19-98 13:24:18 REC:Loc

 Trace program: running                                                                                                                                                                                                                 
                                                                                                                                                                                                                                        
       wake up, Neo...                                                                                                                                                                                                                  
    the matrix has you                                                                                                                                                                                                                  
  follow the white rabbit.

      knock, knock, Neo.

                    (`.         ,-,
                    ` `.    ,;' /
                     `.  ,'/ .'
                      `. X /.'
            .-;--''--.._` ` (
          .'            /   `
         ,           ` '   Q '
         ,         ,   `._    
      ,.|         '     `-.;_'
      :  . `  ;    `  ` --,.._;
       ' `    ,   )   .'
          `._ ,  '   /_
             ; ,''-,;' ``-
              ``-..__``--`

                         https://metasploit.com

   =[ metasploit v6.2.31-dev                          ]

— —=[ 2274 exploits — 1192 auxiliary — 405 post ]
— —=[ 951 payloads — 45 encoders — 11 nops ]
— —=[ 9 evasion ]

Metasploit tip: Tired of setting RHOSTS for modules? Try globally
setting it with setg RHOSTS x.x.x.x Metasploit Documentation:
https://docs.metasploit.com/

msf6 >

I guess that the problem occurred after a system update.

Источник

This issue comes from installing the postgres package without a version number. Although postgres will be installed and it will be the correct version, the script to setup the cluster will not run correctly; it’s a packaging issue.

If you’re comfortable with postgres there is a script you can run to create this cluster and get postgres running. However, there’s an easier way.

First purge the old postgres install, which will remove everything of the old installation, including databases, so back up your databases first.. The issue currently lies with 9.1 so I will assume that’s what you have installed

sudo apt-get remove --purge postgresql-9.1

Now simply reinstall

sudo apt-get install postgresql-9.1

Note the package name with the version number. HTH.

answered Aug 19, 2013 at 23:54

StewartStewart

1,1581 gold badge7 silver badges7 bronze badges

The error message refers to a Unix-domain socket, so you need to tweak your netstat invocation to not exclude them. So try it without the option -t:

netstat -nlp | grep 5432

I would guess that the server is actually listening on the socket /tmp/.s.PGSQL.5432 rather than the /var/run/postgresql/.s.PGSQL.5432 that your client is attempting to connect to. This is a typical problem when using hand-compiled or third-party PostgreSQL packages on Debian or Ubuntu, because the source default for the Unix-domain socket directory is /tmp but the Debian packaging changes it to /var/run/postgresql.

Possible workarounds:

Use the clients supplied by your third-party package (call /opt/djangostack-1.3-0/postgresql/bin/psql). Possibly uninstall the Ubuntu-supplied packages altogether (might be difficult because of other reverse dependencies).
Fix the socket directory of the third-party package to be compatible with Debian/Ubuntu.
Use -H localhost to connect via TCP/IP instead.
Use -h /tmp or equivalent PGHOST setting to point to the right directory.
Don’t use third-party packages.

answered Jun 27, 2011 at 17:41

This works for me:

Edit: postgresql.conf

sudo nano /etc/postgresql/9.3/main/postgresql.conf

Enable or add:

listen_addresses = '*'

Restart the database engine:

sudo service postgresql restart

Also, you can check the file pg_hba.conf

sudo nano /etc/postgresql/9.3/main/pg_hba.conf

And add your network or host address:

host    all             all             192.168.1.0/24          md5

Zanna♦

69k56 gold badges215 silver badges327 bronze badges

answered Oct 9, 2014 at 13:17

angelousangelous

3913 silver badges2 bronze badges

You can use psql -U postgres -h localhost to force the connection to happen over TCP instead of UNIX domain sockets; your netstat output shows that the PostgreSQL server is listening on localhost’s port 5432.

You can find out which local UNIX socket is used by the PostgrSQL server by using a different invocavtion of netstat:

netstat -lp --protocol=unix | grep postgres

At any rate, the interfaces on which the PostgreSQL server listens to are configured in postgresql.conf.

answered Jun 26, 2011 at 12:51

Riccardo MurriRiccardo Murri

16.3k7 gold badges52 silver badges51 bronze badges

Just create a softlink like this :

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

answered Jul 9, 2012 at 1:35

UrielUriel

2012 silver badges2 bronze badges

I make it work by doing this:

dpkg-reconfigure locales

Choose your preferred locales then run

pg_createcluster 9.5 main --start

(9.5 is my version of postgresql)

/etc/init.d/postgresql start

and then it works!

sudo su - postgres
psql

Zanna♦

69k56 gold badges215 silver badges327 bronze badges

answered Sep 13, 2016 at 9:05

mymusisemymusise

1911 silver badge2 bronze badges

I had to compile PostgreSQL 8.1 on Debian Squeeze because I am using Project Open, which is based on OpenACS and will not run on more recent versions of PostgreSQL.

The default compile configuration puts the unix_socket in /tmp, but Project Open, which relies on PostgreSQL, would not work because it looks for the unix_socket at /var/run/postgresql.

There is a setting in postgresql.conf to set the location of the socket. My problem was that either I could set for /tmp and psql worked, but not project open, or I could set it for /var/run/postgresql and psql would not work but project open did.

One resolution to the issue is to set the socket for /var/run/postgresql and then run psql, based on Peter’s suggestion, as:

psql -h /var/run/postgresql

This runs locally using local permissions. The only drawback is that it is more typing than simply «psql».

The other suggestion that someone made was to create a symbolic link between the two locations. This also worked, but, the link disappeared upon reboot. It maybe easier to just use the -h argument, however, I created the symbolic link from within the PostgreSQL script in /etc/init.d. I placed the symbolic link create command in the «start» section. Of course, when I issue a stop and start or restart command, it will try to recreate an existing symbolic link, but other than warning message, there is probably no harm in that.

In my case, instead of:

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

I have

ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432

and have explicitly set the unix_socket to /var/run/postgresql/.s.PGSQL.5432 in postgresql.conf.

Peachy

7,04710 gold badges37 silver badges45 bronze badges

answered Nov 6, 2012 at 3:22

JoeJoe

611 silver badge1 bronze badge

If your Postgres service is up and running without any error or there is no error in starting the Postgres service and still you are getting the mentioned error, follow these steps

Step1: Running `pg_lsclusters` will list all the postgres clusters running on your device

eg:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

most probably the status will be down in your case and postgres service

Step 2: Restart the pg_ctlcluster

#format is pg_ctlcluster <version> <cluster> <action>
sudo pg_ctlcluster 9.6 main start

#restart postgresql service
sudo service postgresql restart

Step 3: Step 2 failed and threw error

If this process is not successful it will throw an error. You can see the error log on /var/log/postgresql/postgresql-9.6-main.log

My error was:

FATAL: could not access private key file "/etc/ssl/private/ssl-cert-snakeoil.key": Permission denied
Try adding `postgres` user to the group `ssl-cert`

Step 4: check ownership of postgres

Make sure that postgres is the owner of /var/lib/postgresql/version_no/main

If not, run

sudo chown postgres -R /var/lib/postgresql/9.6/main/

Step 5: Check postgres user belongs to ssl-cert user group

It turned out that I had erroneously removed the Postgres user from the ssl-cert group. Run the below code to fix the user group issue and fix the permissions

#set user to group back with
sudo gpasswd -a postgres ssl-cert

# Fix ownership and mode
sudo chown root:ssl-cert  /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 740 /etc/ssl/private/ssl-cert-snakeoil.key

# now postgresql starts! (and install command doesn't fail anymore)
sudo service postgresql restart

answered Apr 16, 2018 at 14:44

NoushadNoushad

1711 silver badge5 bronze badges

I found uninstalling Postgres sounds unconvincing.
This helps to solve my problem:

Start the postgres server:
```
sudo systemctl start postgresql
```
Make sure that the server starts on boot:
```
sudo systemctl enable postgresql
```

Detail information can be found on DigitalOcean site Here.

answered Feb 1, 2018 at 15:45

parladparlad

2294 silver badges11 bronze badges

Solution:

Do this

export LC_ALL="en_US.UTF-8"

and this. (9.3 is my current PostgreSQL version. Write your version!)

sudo pg_createcluster 9.3 main --start

answered Feb 23, 2016 at 21:47

In my case it was caused by a typo I made while editing /etc/postgresql/9.5/main/pg_hba.conf

I changed:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

to:

# Database administrative login by Unix domain socket
local   all             postgres                                MD5

But MD5 had to be lowercase md5:

# Database administrative login by Unix domain socket
local   all             postgres                                md5

answered Nov 29, 2016 at 10:15

I failed to solve this problem with my postgres-9.5 server. After 3 days of zero progress trying every permutation of fix on this and other sites I decided to re-install the server and lose 5 days worth of work. But, I did replicate the issue on the new instance. This might provide some perspective on how to fix it before you take the catastrophic approach I did.

First, disable all logging settings in postgresql.conf. This is the section:

# ERROR REPORTING AND LOGGING

Comment out everything in that section. Then restart the service.

When restarting, use /etc/init.d/postgresql start or restart
I found it helpful to be in superuser mode while restarting. I had a x-window open just for that operation. You can establish that superuser mode with sudo -i.

Verify that the server can be reached with this simple command: psql -l -U postgres

If that doesn’t fix it, then consider this:

I was changing the ownership on many folders while trying to find a solution. I knew that I’d probably be trying to revert those folder ownerships and chmods for 2 more days. If you have already messed with those folder ownerships and don’t want to completely purge your server, then start tracking the settings for all impacted folders to bring them back to the original state. You might want to try to do a parallel install on another system and systematically check the ownership and settings of all folders. Tedious, but you may be able to get access to your data.

Once you do gain access, systematically change each relevant line in the # ERROR REPORTING AND LOGGING section of the postgresql.conf file. Restart and test. I found that the default folder for the logs was causing a failure. I specifically commented out log_directory. The default folder the system drops the logs into is then /var/log/postgresql.

Zanna♦

69k56 gold badges215 silver badges327 bronze badges

answered Jan 19, 2017 at 1:34

Possibly it could have happened because you changed the permissions of the /var/lib/postgresql/9.3/main folder.

Try changing it to 700 using the command below:

sudo chmod 700 main

Zanna♦

69k56 gold badges215 silver badges327 bronze badges

answered Nov 6, 2014 at 13:01

This is not exactly related to the question since I’m using Flask, but this was the exact error I was getting and this was the most relevant thread to get ideas.

My setup: Windows Subsystem for Linux, Docker-compose w/ makefile w/ dockerfile, Flask, Postgresql (using a schema consisting of tables)

To connect to postgres, setup your connection string like this:

from flask import Flask
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "postgresql+psycopg2://<user>:<password>@<container_name_in_docker-compose.yml>/<database_name>"

NOTE: I never got any IP (e.g. localhost, 127.0.0.1) to work using any method in this thread. Idea for using the container name instead of localhost came from here: https://github.com/docker-library/postgres/issues/297

Set your schema:

from sqlalchemy import MetaData
db = SQLAlchemy(app, metadata=MetaData(schema="<schema_name>"))

Set your search path for your functions when you setup your session:

db.session.execute("SET search_path TO <schema_name>")

answered Mar 29, 2019 at 21:30

The most upvoted answer isn’t even remotely correct because you can see in the question the server is running on the expected port (he shows this with netstat).

While the OP did not mark the other answer as chosen, they commented that the other answer (which makes sense and works) was sufficient,

But for these reasons that solution is poor and insecure even if it the server wasn’t running on port 5432:

What you’re doing here when you say --purge is you’re deleting the configuration file for PostgreSQL ((as well as all of the data with the database. You or may not even see a warning about this, but here is the warning just to show you now,

Removing the PostgreSQL server package will leave existing database clusters intact, i.e. their configuration, data, and log directories will not be removed. On purging the package, the directories can optionally be removed. Remove PostgreSQL directories when package is purged? [prompt for yes or no]

When you add it again PostgreSQL is reinstalling it to a port number that’s not taken (which may be the port number you expect). Before you even try this solution, you need to answer a few questions along the same line:

Do I want multiple versions of PostgreSQL on my machine?
Do I want an older version of PostgreSQL?
What do I want to happen when I dist-upgrade and there is a newer version?

Currently when you dist-upgrade on Ubuntu (and any Debian variant), the new version of PostgreSQL is installed alongside the old copy and the port number on the new copy is the port number of the old copy + 1. So you can just start it up, increment the port number in your client and you’ve got a new install! And you have the old install to fall back on — it’s safe!

However, if you only one want version of PostgreSQL purging to change the configuration is still not the right option because it will destroy your database. The only time this could even be acceptable is you want to destroy everything related to PostgreSQL. You’re better off ensuring your database is correct and then merely editing the configuration file so the new install runs on the old port number

#!/bin/bash

# We can find the version number of the newest PostgreSQL with this
VERSION=$(dpkg-query -W -f "${Version}" 'postgresql' | sed -e's/+.*//')
PGCONF="/etc/postgresql/${VERSION}/main/postgresql.conf"

# Then we can update the port.
sudo sed -ie '/port = /s/.*/port = 5432/' "$PGCONF"

sudo systemctl restart postgresql

Do not install a specific version of PostgreSQL. Only ever install postgresql. If you install a specific version then when you dist-upgrade your version will simply remain on your computer forever without upgrades. The repo will no longer have the security patches for the old version (which they don’t support). This must always be suboptimal to getting a newer version that they do support, running on a different port number.

answered Aug 4, 2021 at 18:30

Evan CarrollEvan Carroll

7,30615 gold badges53 silver badges87 bronze badges

I had the exact same problem Peter Eisentraut described. Using the netstat -nlp | grep 5432 command, I could see the server was listening on socket /tmp/.s.PGSQL.5432.

To fix this, just edit your postgresql.conf file and change the following lines:

listen_addresses = '*'
unix_socket_directories = '/var/run/postgresql'

Now run service postgresql-9.4 restart (Replace 9-4 with your version), and remote connections should be working now.

Now to allow local connections, simply create a symbolic link to the /var/run/postgresql directory.

ln -s /var/run/postgresql/.s.PGSQL.5432 /tmp/.s.PGSQL.5432

Don’t forget to make sure your pg_hba.conf is correctly configured too.

answered Nov 20, 2015 at 16:44

In my case, all i had to do was this:

sudo service postgresql restart

and then

sudo -u postgres psql

This worked just fine.
Hope it helps.
Cheers .

answered Jun 29, 2017 at 17:21

Find your file:

sudo find /tmp/ -name .s.PGSQL.5432

Result:

/tmp/.s.PGSQL.5432

su postgres
psql -h /tmp/ yourdatabase

Zanna♦

69k56 gold badges215 silver badges327 bronze badges

answered Jan 30, 2017 at 15:18

I had the same problem (on Ubuntu 15.10 (wily)). sudo find / -name 'pg_hba.conf' -print or sudo find / -name 'postgresql.conf' -print turned up empty. Before that it seemed that multiple instances of postgresql were installed.

You might have similar when you see as installed, or dependency problems listing

.../postgresql
.../postgresql-9.x

and so on.

In that case you must sudo apt-get autoremove each package 1 by 1.

Then following this to the letter and you will be fine. Especially when it comes to key importing and adding to source list FIRST

sudo apt-get update && sudo apt-get -y install python-software-properties && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

If not using wily, replace wily with your release, i.e with the output of lsb_release -cs

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'
sudo apt-get update && sudo apt-get install postgresql-9.3 pgadmin3

And then you should be fine and be able to connect and create users.

Expected output:

Creating new cluster 9.3/main ...
config /etc/postgresql/9.3/main
data   /var/lib/postgresql/9.3/main
locale en_US.UTF-8
socket /var/run/postgresql
port   5432

Source of my solutions (credits)

Zanna♦

69k56 gold badges215 silver badges327 bronze badges

answered Feb 5, 2016 at 16:43

While having the same issue I tried something different:

Starting the postgresql daemon manually I got:

FATAL:  could not create shared memory segment ...
   To reduce the request size (currently 57237504 bytes), reduce PostgreSQL's
   shared memory usage, perhaps by reducing shared_buffers or max_connections.

So what I did was to set a lower limit for shared_buffers and max_connections into postgresql.conf and restart the service.

This fixed the problem!

Here’s the full error log:

$ sudo service postgresql start
 * Starting PostgreSQL 9.1 database server                                                                                                                                                               * The PostgreSQL server failed to start. Please check the log output:
2013-06-26 15:05:11 CEST FATAL:  could not create shared memory segment: Invalid argument
2013-06-26 15:05:11 CEST DETAIL:  Failed system call was shmget(key=5432001, size=57237504, 03600).
2013-06-26 15:05:11 CEST HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 57237504 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
    If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
    The PostgreSQL documentation contains more information about shared memory configuration.

Zanna♦

69k56 gold badges215 silver badges327 bronze badges

answered Jun 26, 2013 at 13:29

After many exhausting attempts, I found the solution based on other posts!

dpkg -l | grep postgres
apt-get --purge remove <package-founded-1> <package-founded-2>
whereis postgres
whereis postgresql
sudo rm -rf <paths-founded>
sudo userdel -f postgres

Kevin Bowen

19.3k55 gold badges76 silver badges81 bronze badges

answered Apr 22, 2019 at 23:46

Check the status of postgresql:
```
service postgresql status
```
If it shows online, proceed to step no 3 else execute step no 2.
To make postgresql online, execute the following command:
```
sudo service postgresql start
```
Now check the status by running the command of the previous step. It should show online.
To start psql session, execute the following command:
```
sudo su postreg
```
Finally, check if it’s working or not by executing:
```
psql
```

answered May 29, 2021 at 12:21

Restart postgresql by using the command

sudo /opt/bitnami/ctlscript.sh restart postgresql

enter image description here

answered Apr 26, 2022 at 9:41

This error could mean a lot of different things.

In my case, I was running PostgreSQL 12 on a virtual machine.

I had changed the shared_buffer config and apparently, the system administrator edited the memory config for the virtual machine reducing the RAM allocation from where it was to below what I had set for the shared_buffer.

I figured that out by looking at the log in

/var/log/postgresql/postgresql-12-main.log

and after that I restarted the service using

sudo systemctl restart postgresql.service

that’s how it worked

answered Jun 30, 2022 at 9:47

Create postgresql directory inside run and then run the following command.

ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

answered Apr 26, 2019 at 5:35

Simply add /tmp unix_socket_directories

postgresql.conf

unix_socket_directories = '/var/run/postgresql,/tmp'

answered Jun 17, 2019 at 1:00

I had this problem with another port. The problem was, that I had a system variable in /etc/environments with the following value:

PGPORT=54420

As I removed it (and restarted), psql was able to connect.

answered Jul 18, 2021 at 14:46

BevorBevor

4908 silver badges18 bronze badges

Источник

#database #postgresql #ubuntu #unix #psql

#База данных #postgresql #ubuntu #unix #psql

Вопрос:

Со вчерашнего дня у меня возникает ошибка при запуске psql в Ubuntu 20.04 — PostgreSQL 12. Вот ошибка:

 psql: error: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Я уже видел много ответов на этот вопрос в Интернете, но никто не работал…

Это произошло, когда я перезапустил postgresql после установки phppgadmin, вот последние журналы :

 2021-01-01 21:37:27.981 UTC [1071608] LOG:  received fast shutdown request
2021-01-01 21:37:27.982 UTC [1071608] LOG:  aborting any active transactions
2021-01-01 21:37:27.982 UTC [434049] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.982 UTC [1514704] thegabdoosan@ephedia_web FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.984 UTC [1231171] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.986 UTC [1231170] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.988 UTC [899543] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.990 UTC [899542] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.992 UTC [899541] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.994 UTC [899540] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.996 UTC [899539] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.998 UTC [899538] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:27.999 UTC [899537] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:28.001 UTC [899536] thegabdoosan@ephedia FATAL:  terminating connection due to administrator command
2021-01-01 21:37:28.009 UTC [1071608] LOG:  background worker "logical replication launcher" (PID 1071615) exited with exit code 1
2021-01-01 21:37:28.010 UTC [1071610] LOG:  shutting down
2021-01-01 21:37:28.030 UTC [1071608] LOG:  database system is shut down

Я не вижу ничего странного

pg_hba.conf :

 # Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.106/24        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

postgresql.conf

 # - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories

Когда я пытаюсь запустить psql -h localhost , у меня появляется другая ошибка :

 psql: error: 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?

Когда я запускаю sudo systemctl status postgresql :

 ● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2021-01-02 09:10:59 UTC; 18min ago
    Process: 1750585 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1750585 (code=exited, status=0/SUCCESS)

janv. 02 09:10:59 vps-d989390a systemd[1]: Starting PostgreSQL RDBMS...
janv. 02 09:10:59 vps-d989390a systemd[1]: Finished PostgreSQL RDBMS.

Когда я запускаю ls /var/run/postgresql/ -a :

 0 drwxrwsr-x  3 postgres postgres   80 janv.  1 22:53 .
0 drwxr-xr-x 32 root     root     1060 janv.  2 09:09 ..
0 drwxr-s---  2 postgres postgres   40 janv.  1 21:37 12-main.pg_stat_tmp
0 lrwxrwxrwx  1 root     postgres   18 janv.  1 22:53 .s.PGSQL.5432 -> /tmp/.s.PGSQL.5432

Когда я запускаю sudo pg_ctlcluster 12 main start :

 Job for postgresql@12-main.service failed because the service did not take the steps required by its unit configuration.
See "systemctl status postgresql@12-main.service" and "journalctl -xe" for details.

и pg_lsclusters :

 Ver Cluster Port Status Owner     Data directory              Log file
12  main    5432 down   <unknown> /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

Когда я запускаю sudo systemctl status postgresql@12-main.service :

 ● postgresql@12-main.service - PostgreSQL Cluster 12-main
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor preset: enabled)
     Active: failed (Result: protocol) since Sat 2021-01-02 13:21:05 UTC; 3h 50min ago
    Process: 705 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 12-main start (code=exited, status=1/FAILURE)

Jan 02 13:21:04 vps-d989390a systemd[1]: Starting PostgreSQL Cluster 12-main...
Jan 02 13:21:05 vps-d989390a postgresql@12-main[723]: Error: Could not open logfile /var/log/postgresql/postgresql-12-main.log
Jan 02 13:21:05 vps-d989390a postgresql@12-main[705]: Error: /usr/lib/postgresql/12/bin/pg_ctl /usr/lib/postgresql/12/bin/pg_ctl start -D /var/lib/postgresql/12/main -l /var/log/postgresql/postgresql-12>
Jan 02 13:21:05 vps-d989390a systemd[1]: postgresql@12-main.service: Can't open PID file /run/postgresql/12-main.pid (yet?) after start: Operation not permitted
Jan 02 13:21:05 vps-d989390a systemd[1]: postgresql@12-main.service: Failed with result 'protocol'.
Jan 02 13:21:05 vps-d989390a systemd[1]: Failed to start PostgreSQL Cluster 12-main.

и вот последние 35 строк sudo journalctl -xe , когда я запускаю sudo systemctl start postgresql@12-main.service :
https://mystb.in/TillDimensionIntellectual.yaml

/etc/init.d/postgresql вывод: https://mystb.in/AmountsAlexanderExtreme.bash

Я также отключил ufw

Если я непреднамеренно установлю postgresql, потеряю ли я свои базы данных?

1. Что это systemctl status postgresql@12-main.service показывает? А также journalctl -xe после попытки запуска? Добавьте эту информацию в свой вопрос.

2. Я добавил это! 👌

3. Похоже, это какая-то комбинация ошибок разрешений ( Could not open logfile /var/log/postgresql/postgresql-12-main.log ) и неправильного каталога ( Can't open PID file /run/postgresql/12-main.pid ). Последнее должно быть /var/run/postgresql/12-main.pid . Как вы устанавливали пакеты? У вас есть более одного типа установки на компьютере?

4. Я установил пакеты с помощью apt-get. И я думаю, что у меня не более одного типа установки на компьютере:/ Если это ошибка разрешений, не могу ли я исправить это с помощью (а) командной строки (ов)?

5. Я хотел получить содержимое (то, что находится внутри) /etc/init.d/postgresql .

Ответ №1:

Расположение (или обработка) файла блокировки, похоже, изменилось (между версиями?). Я исправил это, отредактировав startupfile (который выполняется с помощью setuid root): sudo vi /etc/init.d/postgresql

 # Parse command line parameters.

case $1 in
  start)
        echo -n "Starting PostgreSQL: "
        test x"$OOM_ADJ" != x amp;amp; echo "$OOM_ADJ" > /proc/self/oom_adj

        #################################
        # FIX: Directory Lockfile must be writable by postgres
        mkdir -p /var/run/postgresql
        chown postgres.postgres /var/run/postgresql
        ##################################

        #echo su - $PGUSER -c "$DAEMON -D '$PGDATA' amp;"
        su - $PGUSER -c "$DAEMON -D '$PGDATA' amp;" >>$PGLOG 2>amp;1
        echo "ok"
        ;;
  stop)

Кстати: unix-domain-socket иногда тоже находится в этом каталоге. (раньше был /tmp/ )

BTW2: я поместил его в сценарий запуска, потому /var/run/ что при перезагрузке он стирается.

BTW3: используйте на свой страх и риск!

1. Уверен, что это не так. У меня те же настройки, что и у @TheGabDooSan, и я могу запустить Postgres 12. Также приведенный выше не postgresql является файлом инициализации, который поставляется с пакетами Ubuntu.

2. @AdrianKlaver Это правильно. Это более старая версия. (Я устанавливаю из исходного кода)

3. К сожалению, это не так, как настройка @TheGabDooSan, и поэтому она не применяется.

Источник

Сейчас столкнулся с тем же самым и стал гуглить, нашел этот вопрос. Потом вспомнил, что часом ранее редактировал файл start.conf, который находится в /etc/postgresql/дальше сами найдете)
В общем, я хотел, чтобы постгрес не запускался при старте машины, чтобы я мог запускать его через сервис пострес старт и поменял в том файле auto на manual, перезагрузил, смотрю — пострес не запустился, отлично. Но потом случился сабж. Потом поменял обратно на авто, опять перезагрузка, и проблема решилась. Возможно, кроме запуска сервиса нужно еще что-то запустить, чтобы все хорошо работало, этого уж я не знаю, не лютый линуксоид)

Обновление 17.07.2019
После перезагрузки компа столкнулся с тем же самым. Залез в логи, там прямым текстом написано

2019-07-17 15:12:07.950 MSK [547] ВАЖНО: для каталога данных «/var/lib/postgresql/11/main» установлены неправильные права доступа
2019-07-17 15:12:07.950 MSK [547] ПОДРОБНОСТИ: Маска прав должна быть u=rwx (0700) или u=rwx,g=rx (0750).
pg_ctl: не удалось запустить сервер
Изучите протокол выполнения.

Зачмодил каталог, и все заработало.

Источник

Step1: Running `pg_lsclusters` will list all the postgres clusters running on your device

Step 2: Restart the pg_ctlcluster

Step 3: Step 2 failed and threw error

Step 4: check ownership of postgres

Step 5: Check postgres user belongs to ssl-cert user group

Вопрос:

Комментарии:

Ответ №1:

Комментарии:

Step1: Running pg_lsclusters will list all the postgres clusters running on your device

Step 2: Restart the pg_ctlcluster

Step 3: Step 2 failed and threw error

Step 4: check ownership of postgres

Step 5: Check postgres user belongs to ssl-cert user group

Вопрос:

Комментарии:

Ответ №1:

Комментарии:

Step1: Running `pg_lsclusters` will list all the postgres clusters running on your device