I’m using MacPorts and after migrating to a new machine using Apple’s Migration Assistant the db server was unavailable:
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
I tried
sudo port load postgresql14-server
but that didn’t work. Looking at the /Library/LaunchDaemons/org.macports.postgresql14-server.plist
file, though revealed two things:
<key>Disabled</key><true/>
and the actual start command
<string>--start-cmd</string>
<string>/opt/local/etc/LaunchDaemons/org.macports.postgresql14-server/postgresql14-server.wrapper</string>
So, running the start command manually finally showed the issue:
> sudo /opt/local/etc/LaunchDaemons/org.macports.postgresql14-server/postgresql14-server.wrapper start
pg_ctl: could not open PID file "/opt/local/var/db/postgresql14/defaultdb/postmaster.pid": Permission denied
and indeed:
> sudo ls -la /opt/local/var/db/postgresql14/defaultdb/
total 60
drwx------ 26 postgres postgres 832 Jan 31 05:13 .
drwxr-xr-x 4 postgres postgres 128 Feb 5 12:31 ..
-rw------- 1 500 postgres 3 Nov 7 2021 PG_VERSION
drwx------ 22 500 postgres 704 Feb 4 10:37 base
drwx------ 60 500 postgres 1920 Feb 4 10:37 global
drwx------ 2 500 postgres 64 Nov 7 2021 pg_commit_ts
...
-rw------- 1 500 postgres 105 Jan 31 05:13 postmaster.pid
The user id was messed up. Fix:
sudo chown -hR postgres /opt/local/var/db/postgresql14/defaultdb/
to set the owner of all files to postgres
.
With that change, the server works:
> sudo /opt/local/etc/LaunchDaemons/org.macports.postgresql14-server/postgresql14-server.wrapper start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start.... done
server started
> psql -U postgres -W -p 5432 -h localhost
Password:
psql (14.6)
Type "help" for help.
postgres=#
q
Добрый день, поставил PostgreSQL, но при авторизации возникает ошибка:
C:UsersAdmin>psql v-
Пароль пользователя Егор:
psql: ошибка: подключиться к серверу "localhost" (::1), порту 5432 не удалось: ВАЖНО: пользователь "Егор" не прошёл проверку подлинности (по паролю)
Сразу, важная деталь, раньше учётка Windows называлась Егор, сейчас Admin, и что более важно, в реестре я заменил не все совпадения, так как их миллион, но PostgreSQL я ставил после переименования.
Переустановил 2 раза, пароль 100% правильный. Еще пробовал по советам менять пользователя, но тоже не помогло.
Подскажите, пожалуйста, что еще попробовать?
Please read before replying it as duplicate (as it perhaps can happen). I am running my postmaster (postgres) server. See below for ‘sudo netstat -anp|grep 5432’ output?
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 29606/postmaster
unix 2 [ ACC ] STREAM LISTENING 1650581 29606/postmaster /var/run/postgresql/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 1650582 29606/postmaster /tmp/.s.PGSQL.5432
I am able to connect from localhost using
psql -h localhost (OR 127.0.0.1) -d <DB> -U user -W
But when I try to connect from other hosts using tcp, by specifying
psql -h ip_add_postmaster -d <DB> -U user -W
It throws:
psql: could not connect to server: Connection refused
Is the server running on host XXXXXX and accepting TCP/IP connections on port 5432?
What’s wrong here?
pg_hba.conf
# 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
In postgresql.conf,
listen_addresses = 'localhost, 127.0.0.1, ip_add_postmaster'
Note: ip_add_postmaster is same as my Elastic IP and not public DNS. If this information
matters.
What am I doing wrong here? Machine is hosted on Amazon EC2 and have open the port 5432.
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.
Thomas Ward♦
70.4k29 gold badges173 silver badges236 bronze badges
answered Aug 19, 2013 at 23:54
StewartStewart
1,1581 gold badge7 silver badges7 bronze badges
3
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 equivalentPGHOST
setting to point to the right directory. - Don’t use third-party packages.
answered Jun 27, 2011 at 17:41
3
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♦
68.3k55 gold badges210 silver badges320 bronze badges
answered Oct 9, 2014 at 13:17
angelousangelous
3813 silver badges2 bronze badges
6
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.2k7 gold badges52 silver badges51 bronze badges
0
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
4
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♦
68.3k55 gold badges210 silver badges320 bronze badges
answered Sep 13, 2016 at 9:05
mymusisemymusise
1911 silver badge2 bronze badges
2
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
6,98710 gold badges36 silver badges45 bronze badges
answered Nov 6, 2012 at 3:22
JoeJoe
611 silver badge1 bronze badge
1
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
1
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
1
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
1
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 chmod
s 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♦
68.3k55 gold badges210 silver badges320 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♦
68.3k55 gold badges210 silver badges320 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,49915 gold badges53 silver badges86 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
Login as postgres user:
su postgres
psql -h /tmp/ yourdatabase
Zanna♦
68.3k55 gold badges210 silver badges320 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♦
68.3k55 gold badges210 silver badges320 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♦
68.3k55 gold badges210 silver badges320 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.2k55 gold badges75 silver badges81 bronze badges
answered Apr 22, 2019 at 23:46
1
-
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
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
1
Simply add /tmp unix_socket_directories
postgresql.conf
unix_socket_directories = '/var/run/postgresql,/tmp'
answered Jun 17, 2019 at 1:00
2
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 badges17 bronze badges
2
PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.
Содержание:
- Установка PostgreSQL 11 в Windows 10
- Доступ к PostgreSQL по сети, правила файерволла
- Утилиты управления PostgreSQL через командную строку
- PgAdmin: Визуальный редактор для PostgresSQL
- Query Tool: использование SQL запросов в PostgreSQL
Установка PostgreSQL 11 в Windows 10
Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.
В процессе установки установите галочки на пунктах:
- PostgreSQL Server – сам сервер СУБД
- PgAdmin 4 – визуальный редактор SQL
- Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
- Command Line Tools – инструменты командной строки
Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).
По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.
Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.
Доступ к PostgreSQL по сети, правила файерволла
Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.
Запустите командную строку от имени администратора. Введите команду:
netsh advfirewall firewall add rule name="Postgre Port" dir=in action=allow protocol=TCP localport=5432
- Где rule name – имя правила
- Localport – разрешенный порт
Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:
New-NetFirewallRule -Name 'POSTGRESQL-In-TCP' -DisplayName 'PostgreSQL (TCP-In)' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432
После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.
Совет. Для изменения порта в установленной PostgreSQL отредактируйте файл postgresql.conf по пути C:Program FilesPostgreSQL11data.
Измените значение в пункте
port = 5432
. Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:
Restart-Service -Name postgresql-x64-11
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
- Запустите командную строку.
Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:
chcp 1251
- Перейдите в каталог bin выполнив команду:
CD C:Program FilesPostgreSQL11bin
Основные команды PostgreSQL:
PgAdmin: Визуальный редактор для PostgresSQL
Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.
По умолчанию все созданные базы хранятся в каталоге base по пути C:Program FilesPostgreSQL11database.
Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.
Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.
Query Tool: использование SQL запросов в PostgreSQL
Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.
- Выберите базу данных, в панели Tools откройте Query Tool
- Создадим таблицу сотрудников:
CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);
Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.
После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.
Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.
Здесь вы можете заполнить данные в таблице.
После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;
Содержание
- connection refused 0x0000274d 10061
- Решение¶
- Responses
- Browse pgsql-general by date
- Server doesn’t listen [PostgreSQL] ?
- Русские Блоги
- Ошибка подключения PostgreSQL, не удалась подключиться к серверу: соединение отказано (0x0000274D / 10061) .
- Thread: Connection refused (0x0000274D/10061)
- Connection refused (0x0000274D/10061)
- could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «localhost» (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «localhost» (127.0.0.1) and accepting TCP/IP connections on port 5432?
- Re: Connection refused (0x0000274D/10061)
- Re: Connection refused (0x0000274D/10061)
- Re: Connection refused (0x0000274D/10061)
- Re: Connection refused (0x0000274D/10061)
- Re: Connection refused (0x0000274D/10061)
- Re: Connection refused (0x0000274D/10061)
- Re: Connection refused (0x0000274D/10061)
- Обсуждение: could not connect to server: Connection refused (0x0000274D/10061)
- could not connect to server: Connection refused (0x0000274D/10061)
- Re: could not connect to server: Connection refused (0x0000274D/10061)
- Re: could not connect to server: Connection refused (0x0000274D/10061)
- Re: could not connect to server: Connection refused (0x0000274D/10061)
- Re: could not connect to server: Connection refused (0x0000274D/10061)
- Re: could not connect to server: Connection refused (0x0000274D/10061)
- Re: could not connect to server: Connection refused (0x0000274D/10061)
- Re: could not connect to server: Connection refused (0x0000274D/10061)
connection refused 0x0000274d 10061
Подобная ошибка возникает, если доступ к серверу ограничен файерволом или PostgreSQL не принимает подключение по указанному адресу/порту (10.0.2.15). Настройка файервола выходит за рамки тематики данного сайта, поэтому ниже приводится только решение проблемы, связанной с настройкой PostgreSQL.
Решение¶
Необходимо зайти на сервер по ssh и проверить статус СУБД командой:
Pезультат выполнения команды означает, что PostgreSQL принимает подключения по адресу 127.0.0.1 и порту 5432. Чтобы изменить настройки, понадобится отредактировать файл postgresql.conf
Найти местонахождение файла можно командой:
Надо указать PostgreSQL, что необходимо принимать подключения по всем адресам:
и перезагрузить СУБД:
Теперь необходимо проверить настройки:
Теперь доступ к базе данных есть с любого адреса. Можно попытаться подключиться к базе данных:
Если сервер доступен, то будет получен доступ к базе данных postgres:
I have installed PostgreSQL. However everytime I try to connect through PGAdmin or through psql it gives me the below error.
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «localhost» (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «localhost» (127.0.0.1) and accepting TCP/IP connections on port 5432?
I have checked the postgresql.conf file and listen_addresses = ‘*’ is not commented out.
Also this is what my pg_hba.conf.txt file is set to
Can anyone please advise on how I can resolve this issue?
From: | «Pietro Laranci» |
---|
Subject: Connection refused (0x0000274D/10061) Date: 2011-09-30 10:17:51 Message-ID: DCDDF9A40AB945B8841EC648D1BF5323@Elaborazione1 Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-general
Good morning to everybody
I have a pg9 db to wich i can connect to by local but not from remote. In the properties of the postgres db , the port is set to 5300 (not the default 5432).
I tried to connect in remote with pgadmin3 and quantum gis, both give the error Connection refused (0x0000274D/10061).
This is the complet message in quantum gis (but is silimar in pgadmin3)
«could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host «95.110.201.74» and accepting
TCP/IP connections on port 5300?»
Whit both the programs installed on the same pc of the db, i can connect without problem to the db.
I already did these settings:
— in postgresql.conf : listen_addresses = ‘*’ and port = 5300 (in the place of the default 5432)
host [dbname] all 0.0.0.0/0 md5
host [dbname] all 127.0.0.1/32 md5
I also tried to substitute on the db properties «host=localhost» with «host=95.110.201.74»
On the machines are not active firewalls.
Have you any clue of what’s wrong?
Responses
- Re: Connection refused (0x0000274D/10061) at 2011-09-30 13:46:48 from Adrian Klaver
- Re: Connection refused (0x0000274D/10061) at 2011-09-30 23:31:08 from Craig Ringer
Browse pgsql-general by date
From | Date | Subject | |
---|---|---|---|
Next Message | Emanuel Araújo | 2011-09-30 11:42:20 | Re: Problem dbi_link with postgresql 9.04 |
Previous Message | Albe Laurenz | 2011-09-30 10:16:29 | Re: stored procedures (packages) |
Copyright © 1996-2019 The PostgreSQL Global Development Group
Источник
Server doesn’t listen [PostgreSQL] ?
OS: Windows 7 64-bit
На рабочем компьютере потребовалось переустановить PostgreSQL.
После переустановки при попытке подключения к серверу PostgreSQL 9.3 (localhost: 5432) и создания нового сервера localhost (localhost: 5432) — появляется окно с ошибкой:
«Server doesn’t listen
The server doesn’t accept connections: the connection library reports
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «localhost» (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «localhost» (127.0.0.1) and accepting TCP/IP connections on port 5432?
If you encounter this message, please check if the server you’re trying to contact is actually running PostgreSQL on the given port. Test if you have network connectivity from your client to the server host using ping or equivalent tools. Is your network / VPN / SSH tunnel / firewall configured correctly?
For security reasons, PostgreSQL does not listen on all available IP addresses on the server machine initially. In order to access the server over the network, you need to enable listening on the address first.
For PostgreSQL servers starting with version 8.0, this is controlled using the «listen_addresses» parameter in the postgresql.conf file. Here, you can enter a list of IP addresses the server should listen on, or simply use ‘*’ to listen on all available IP addresses. For earlier servers (Version 7.3 or 7.4), you’ll need to set the «tcpip_socket» parameter to ‘true’.
You can use the postgresql.conf editor that is built into pgAdmin III to edit the postgresql.conf configuration file. After changing this file, you need to restart the server process to make the setting effective.
If you double-checked your configuration but still get this error message, it’s still unlikely that you encounter a fatal PostgreSQL misbehaviour. You probably have some low level network connectivity problems (e.g. firewall configuration). Please check this thoroughly before reporting a bug to the PostgreSQL community. «
В интернете чаще всего советуют проверить корректиность pg_hba.conf.
Сравнил свой файл с файлом коллеги с работающим PostgreSQL — файлы идентичны.
Подскажите, пожалуйста, как решить проблему или в каком направлении искать решение?
UPD:
В службах при попытке запустить postgresql-службу появляется окно с текстом:
«Служба «postgresql-x64-9.3» на «Локальный компьютер» была запущена и затем остановлена. Некоторые службы автоматически останавливаются, если они не используются другими службами или программами.»
Источник
Русские Блоги
Ошибка подключения PostgreSQL, не удалась подключиться к серверу: соединение отказано (0x0000274D / 10061) .
Подключитесь к локальной базе данных PostgreSQL при использовании NaviCat:
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host»localhost» (:1) and acceptingTCP/IP connections on port 5433 ?
could not connect to server: Connection refused (0x0000274D/10061)Is the server running on host»localhost» (127.0.0.1) and acceptingTCP/IP connections on port 5433?
Сначала проверьте это, не запускайте службу PostgreSQL, потому что у вас нет начального сервиса, может сообщить об этой ошибке (я просто);
Методы, как показано ниже:
Win + R открывает входную команду ввода и введите список сервисов Services.msc.
Щелкните правой кнопкой мыши
Попробуйте снова подключиться к базе данных, чтобы увидеть, если она успешна, если у вас нет успеха, сделайте следующие шаги:
1. Найти «# я я l c в папке установки 9.5 data pg_hba.conf в PostgreSQL
Затем добавьте «Local PGSQL все доверие» на этой строке.
«Хозяин все 127.0.0.1/32 MD5» ниже
Добавлена следующее, контент «Host все Все в 172.168,91/24 MD5»
Примечание: 32 и 24 из 127.0.0.1/32 и 192.168.91.1/24, используя 32 Указывают, что IP фиксируется, а 24 используется для обозначения первых 3 битов.
Последний может быть установлен сами, так что первый трехбитный IP-адрес может получить доступ к базе данных PostgreSQL с тем же компьютером, что и настройка. Быть
2, в файле PostgreSQL 9,5 data postgresql.conf, найдите «#listen_addresses = ‘localhost’, измените его на» listen_addresses = ‘*’ «. Таким образом, PostgreSQL может слушать все IP-адреса.
3, затем перезапустите службу PostgreSQL. Если система включает брандмауэр, пожалуйста, отключите сначала. Если вы хотите использовать PGADMIN для подключения удаленного сервера базы данных, вы должны выбрать разрешение в опции SSL.
Источник
Thread: Connection refused (0x0000274D/10061)
Connection refused (0x0000274D/10061)
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «localhost» (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «localhost» (127.0.0.1) and accepting TCP/IP connections on port 5432?
When I am trying to run localhost:3000, its giving this error massage, Can you please help me out here?
Re: Connection refused (0x0000274D/10061)
Re: Connection refused (0x0000274D/10061)
Re: Connection refused (0x0000274D/10061)
On 09/06/2019 19:51, Sourav Majumdar wrote:
>
>В В В could not connect to server: Connection refused (0x0000274D/10061)
>В В В Is the server running on host «localhost» (::1) and accepting TCP/IP
>В В В connections on port 5432? could not connect to server: Connection
>В В В refused (0x0000274D/10061) Is the server running on host «localhost»
>В В В (127.0.0.1) and accepting TCP/IP connections on port 5432?
В From the above, you have the server listening on port 3000 (you would
have set this in postgresql.conf — did you?), but the client is trying
to connect on port 5432 (the default).
You therefore need to tell the client to connect to port 3000 — if you
are using psql, you need the -p option:
В В psql -p 3000 (. etc . )
Re: Connection refused (0x0000274D/10061)
Re: Connection refused (0x0000274D/10061)
Re: Connection refused (0x0000274D/10061)
Re: Connection refused (0x0000274D/10061)
On 6/9/19 1:46 PM, Ray O’Donnell wrote:
> On 09/06/2019 20:49, Sourav Majumdar wrote:
>> Hello,
>> Thanks for your quick response. I am new to pgsql, didnot configured
>> it , can you please give a guidance for that? I will be highly oblised
>> if you can assist me.
>> I am using windows 8.1, 64 bit.
>> ruby 2.3.3p222 (2016-11-21 revision 56859) [i386-mingw32]
>> Rails 5.2.3
>> Trying to setup ruby on rails on my local host
Alright the penny just dropped, maybe:
1) You have started the Rails Web server on port 3000
2) You tried to do something that accessed the Postgres server.
3) You got connection refused error.
If the above is correct then the error message:
«: Connection refused (0x0000274D/10061) Is the server running on host
«localhost» (::1) and accepting TCP/IP connections on port 5432?»
1) The server is not running. Check that the Postgres service is running
in Windows.
Источник
Обсуждение: could not connect to server: Connection refused (0x0000274D/10061)
could not connect to server: Connection refused (0x0000274D/10061)
I installed Postgres 8.1.4 on Linux and postgres server is running and I successfully created database on local machine.
I modified listen_addresses = ‘*’ in Postgresql.conf and however If I try to access postgres through pgAdmin III. I am sticking with an exception.
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «192.168.9.126» and accepting TCP/IP connections on port 5432?
Thanks & Regards,
Anuj Pankaj
Re: could not connect to server: Connection refused (0x0000274D/10061)
Вложения
Re: could not connect to server: Connection refused (0x0000274D/10061)
Re: could not connect to server: Connection refused (0x0000274D/10061)
Is it really running? (run ps ax | grep [p]ost)
Ya, I have restarted the server by «service postgresql restart». I am getting OK sign.
Regards,
Anuj Pankaj
——Original Message——
From: Devrim GÜNDÜZ [mailto: ]
Sent: Wednesday, September 29, 2010 6:42 PM
To: Anuj Pankaj
Cc:
Subject: Re: [ADMIN] could not connect to server: Connection refused (0x0000274D/10061)
On Wed, 2010-09-29 at 18:26 +0530, Anuj Pankaj wrote:
>
> I modified listen_addresses = ‘*’ in Postgresql.conf and however If I
> try to access postgres through pgAdmin III. I am sticking with an
> exception.
Did you restart PostgreSQL after the change?
—
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim
«Legal Disclaimer: This electronic message and all contents contain information from Cybage Software Private Limited which may be privileged, confidential, or otherwise protected from disclosure. The information is intended to be for the addressee(s) only. If you are not an addressee, any disclosure, copy, distribution, or use of the contents of this message is strictly prohibited. If you have received this electronic message in error please notify the sender by reply e-mail to and destroy the original message and all copies. Cybage has taken every reasonable precaution to minimize the risk of malicious content in the mail, but is not liable for any damage you may sustain as a result of any malicious content in this e-mail. You should carry out your own malicious content checks before opening the e-mail or attachment.»
www.cybage.com
Re: could not connect to server: Connection refused (0x0000274D/10061)
Below is the output by running the command (ps ax | grep [p]ost)
1118 ? S 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
1120 ? S 0:00 postgres: logger process
1122 ? S 0:00 postgres: writer process
1123 ? S 0:00 postgres: stats buffer process
1124 ? S 0:00 postgres: stats collector process
Please help to run my postgres on remote manchine through pgAdmin III.
From: Vladimir Rusinov [mailto:]
Sent: Wednesday, September 29, 2010 7:47 PM
To: Anuj Pankaj
Cc: Devrim GÜNDÜZ;
Subject: Re: [ADMIN] could not connect to server: Connection refused (0x0000274D/10061)
Is it really running? (run ps ax | grep [p]ost)
2010/9/29 Anuj Pankaj
Ya, I have restarted the server by «service postgresql restart». I am getting OK sign.
Regards,
Anuj Pankaj
——Original Message——
From: Devrim GÜNDÜZ [mailto: ]
Sent: Wednesday, September 29, 2010 6:42 PM
To: Anuj Pankaj
Cc:
Subject: Re: [ADMIN] could not connect to server: Connection refused (0x0000274D/10061)
On Wed, 2010-09-29 at 18:26 +0530, Anuj Pankaj wrote:
>
> I modified listen_addresses = ‘*’ in Postgresql.conf and however If I
> try to access postgres through pgAdmin III. I am sticking with an
> exception.
Did you restart PostgreSQL after the change?
—
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim
«Legal Disclaimer: This electronic message and all contents contain information from Cybage Software Private Limited which may be privileged, confidential, or otherwise protected from disclosure. The information is intended to be for the addressee(s) only. If you are not an addressee, any disclosure, copy, distribution, or use of the contents of this message is strictly prohibited. If you have received this electronic message in error please notify the sender by reply e-mail to and destroy the original message and all copies. Cybage has taken every reasonable precaution to minimize the risk of malicious content in the mail, but is not liable for any damage you may sustain as a result of any malicious content in this e-mail. You should carry out your own malicious content checks before opening the e-mail or attachment.»
www.cybage.com
—
Sent via pgsql-admin mailing list ( )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: could not connect to server: Connection refused (0x0000274D/10061)
Re: could not connect to server: Connection refused (0x0000274D/10061)
I installed Postgres 8.1.4 on Linux and postgres server is running and I successfully created database on local machine.
I modified listen_addresses = ‘*’ in Postgresql.conf and however If I try to access postgres through pgAdmin III. I am sticking with an exception.
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «192.168.9.126» and accepting TCP/IP connections on port 5432?
Thanks & Regards,
Anuj Pankaj
Re: could not connect to server: Connection refused (0x0000274D/10061)
I installed Postgres 8.1.4 on Linux and postgres server is running and I successfully created database on local machine.
I modified listen_addresses = ‘*’ in Postgresql.conf and however If I try to access postgres through pgAdmin III. I am sticking with an exception.
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host «192.168.9.126» and accepting TCP/IP connections on port 5432?
My guess is that you have the iptables firewall running on this server, and it doesn’t allow remote users to connect to port 5432 on this server. This is usually the case in a default RedHat/CentOS Linux system for example. On that distribution, if you run system-config-securitylevel as root you’ll get to a text interface to change settings around. Go into the «Customize» section, and add «postgres:tcp» to the «Other ports» section. Running «service iptables restart» afterwards will make the change active.
On other distributions, you can usually confirm whether this is the problem by running (again as root) «iptables -L». If you see any «(policy REJECT)» in there, and there isn’t also a reference to the postgres port on the list of what is accepted, you’ll need to find the firewall reconfiguration tool.
It is possible there’s a firewall somewhere else between the server and your system causing the problem instead, but from the fact that you’re using the 192.168.*.* block I’d assume your client and the server are on the same local LAN.
Источник
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
3
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 equivalentPGHOST
setting to point to the right directory. - Don’t use third-party packages.
answered Jun 27, 2011 at 17:41
3
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
6
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
0
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
4
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
2
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
1
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
1
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
1
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
1
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 chmod
s 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
Login as postgres user:
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
1
-
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
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
1
Simply add /tmp unix_socket_directories
postgresql.conf
unix_socket_directories = '/var/run/postgresql,/tmp'
answered Jun 17, 2019 at 1:00
2
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
2