Fatal password authentication failed for user postgres windows - Доктор Windows

Answer given is almost correct just missing some pointers which i’ll be taking care of in my solution

First make sure your user have a sudo access if not you can use the below command to add your user as sudo user :-

sudo adduser <username> sudo

The change will take effect the next time the user logs in.

i) Now go to sudo vim /etc/postgresql/<your_postgres_version>/main/pg_hba.conf file and look for line that says :

local   all             postgres                                md5 #peer

and comment that. Just below that line there must be a commented line that says:

local   all             postgres                                peer

or for older versions it’ll be :-

local   all         postgres                          ident

Uncomment that line.

ii) Now restart the postgres by using any of these commands :-

sudo /etc/init.d/postgresql restart

sudo service postgresql restart

iii) Now you can simply log into postgres using the following command :

sudo -u postgres psql

iv) once you’re in you can create any operation you want to in my case i wanted to create a new database you can do the same using below command :

CREATE DATABASE airflow_replica;

Источник

Authentication failures and related problems generally manifest themselves through error messages like the following:

FATAL:  no pg_hba.conf entry for host "123.123.123.123", user "andym", database "testdb"

This is what you are most likely to get if you succeed in contacting the server, but it does not want to talk to you. As the message suggests, the server refused the connection request because it found no matching entry in its pg_hba.conf configuration file.

FATAL:  password authentication failed for user "andym"

Messages like this indicate that you contacted the server, and it is willing to talk to you, but not until you pass the authorization method specified in the pg_hba.conf file. Check the password you are providing, or check your Kerberos or ident software if the complaint mentions one of those authentication types.

FATAL:  user "andym" does not exist

The indicated database user name was not found.

FATAL:  database "testdb" does not exist

The database you are trying to connect to does not exist. Note that if you do not specify a database name, it defaults to the database user name, which might or might not be the right thing.

Tip

The server log might contain more information about an authentication failure than is reported to the client. If you are confused about the reason for a failure, check the server log.

Источник

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.

Already on GitHub?
Sign in
to your account

Assignees

Comments

$ sudo docker run --name postgresql -itd --restart always   --env 'PG_PASSWORD=postgres'   --env 'PG_TRUST_LOCALNET=true'   --publish 5432:5432   --volume /srv/docker/postgresql:/var/lib/postgresql   sameersbn/postgresql:9.6-2
59edeb353dec8c503a7f8d86396decfcdb31b98a3b463c3e9c990083fb4dc59e

$ psql -Upostgres -hlocalhost
Password for user postgres: 
psql: FATAL:  password authentication failed for user "postgres"

mauricedoepke, afourmy, Barbery, MrSwitch, mileslucas, iNLyze, zalper, dveselov, GopherJ, wsz87, and 30 more reacted with thumbs up emoji
Moathal reacted with heart emoji

v10, still reproduced. Solution is:

You need to define a password for user postgres

Get container
docker ps
Enter inside of the container
docker exec -it <hash> bash
Start query console
psql
Define password
ALTER ROLE postgres WITH PASSWORD 'your_password';

Then you may grant other users as superuser and etc. (Make your volume persist)

Container Mgmnt:

Up:
docker-compose up —remove-orphans —force-recreate —build PostgreSQL

Down:
docker-compose down -v

AhmedBHameed, mcyleung, eduardorangell, kemicofa, birhanuh, Joostluijben, AnjaneyuluBatta505, ybuasen, AliA74, adrianchavez123, and 39 more reacted with thumbs up emoji
SergeyMMedvedev, dingobar, willduarte, Robertmw, songtianyi, and deadman2000 reacted with thumbs down emoji
arogyakoirala, NightQnEarth, sterswift, daniel-covelli, and Tanja-4732 reacted with hooray emoji
Moathal and Mitjaaa reacted with heart emoji

@zalper how should i save your steps in the container!!
I did your steps and commit the container but still now saved when running my image again !!
BTW i’m NB to docker.

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows
Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)
Option 3: Change the port of the docker container

yondchang, bluwy, VShkaberda, TJBelcher, p3t3rix, mafrax, o-andres-u, nikluwfy, andersonmoura, Mallikanand, and 146 more reacted with thumbs up emoji
mjohnston-vtx, sqrnut-1, omneimneh, UUPRETI, IlyaBritkov, Dreammaker001, zcybupt, blackstorm0514, grad44, WeiWeiCheng123, and 21 more reacted with hooray emoji
oLadeira, theding0x, DALLESANAA, ml-vines, CMofjeld, lalit-g-deepr, faragos, downtest, milijanadj, Hellin1, and 21 more reacted with heart emoji
nikluwfy, andersonmoura, khalilahmad0, danfmaia, exaucae, rbarthel, mjohnston-vtx, viloil, sqrnut-1, omneimneh, and 26 more reacted with rocket emoji

@eddex Just wanted to chime in to say your Option 1 above fixed this for me after I had been scratching my head for a couple of hours. That solution does not appear anywhere else online that I have been able to find and was extremely helpful. Thanks!

eddex, yondchang, exaucae, jtonikeetup, mjohnston-vtx, Lyakhova, omneimneh, lucasfpds, Quiarom, liekeceton, and 7 more reacted with hooray emoji

@eddex Thanks! I also benefited from you recalling your problem and solution. I’d not seen it mentioned anywhere else in 2 days of troubleshooting. I had noticed double use of the port in some netstat results, but reasoned it away as being 2 references to the same process since I didn’t know postgres 12 was running on my PC. Bad assumption on my part

@eddex Thanks a lot. I was having the same issue with my local Postgres installation and the container I was running by using docker. I just stopped the service on Windows and started working.

@eddex also saved the day for me here.

@eddex — after a couple of hours of struggle, this answer helped me.
Didnt realise my Postgres locally starts automatically upon login.
As I didnt need the local version anymore, uninstalled it.

/remind me about this on saturday

I still got the issue and really don’t know what changed.
I was doing some work on my home machine and everything was set up properly and was working. Then I moved working on my working machine, set it up there and it worked as well (and it still works there).

After moving to my working machine I removed the containers and images for postgres:12 from my home machine. Now I want to set the things up again but for some reason it refuses to log me. Haven’t changed any docker-compose settings.
I am afraid that if I now remove everything from my working machine and try to set it up again, it’ll stop working there too.

This is my docker-compose.yml file.

version: '3.1'

services:

    postgres:
        image: postgres:12
        container_name: "postgres-v12"
        ports:
            - 5432:5432
        environment:
            POSTGRES_PASSWORD: admin
        volumes:
            - db_vol:/var/lib/postgresql/data

    pgadmin:
        image: dpage/pgadmin4
        container_name: pgadmin4
        ports:
            - 3333:80
        environment:
            PGADMIN_DEFAULT_EMAIL: admin
            PGADMIN_DEFAULT_PASSWORD: admin


volumes:
    db_vol:
        external:
            name: "postgres-v12"

I don’t have postgres installed directly on my machine, tried to change ports, passwords, adding networks, re-pulling images, restarting docker etc. and it just won’t work.

Any ideas anyone?

Today, I have decided to try and re-create the volume. So I deleted this postgres-v12 and created a new one and just in case I named it postgres12. I updated docker-compose.yml and voila everything started working again. I have never done any manual user updates, so somehow something messed up the postgres user in that volume.

Thanks @eddex ! Solution 1 fixed my issue.

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows

Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)

Option 3: Change the port of the docker container

Do you maybe also know a solution for Mac OSX? I already spent hours on this bad connection error for this docker issue…

In my case (dockerizing a rails app) I had to delete tmp/db folder and rerun docker-compose up for me to solve the issue.

almost went crazy because I have tried even reinstalling docker to solve this issue, turns out it was because I forgot I installed a local postgresql. 🤪 thanks @eddex

Today, I have decided to try and re-create the volume. So I deleted this postgres-v12 and created a new one and just in case I named it postgres12. I updated docker-compose.yml and voila everything started working again. I have never done any manual user updates, so somehow something messed up the postgres user in that volume.

It worked for me as well

I had the same issue with just running
docker run --name pg -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres and I eventually realised that the command was using the postgres docker image that I had locally, tagged as latest (rather than pulling the latest one from remote).
Run d
docker image ls

and see what images you currently have. Removing the postgres:latest image I had locally and re-running the command ended up with the latest image being pulled and the login working!

this worked for me… i just forward and expose 5435 on my machine. leave pg on 5432 inside the container. Then it’s easy to config 5435 or any other port for dev use.

services:
  database:
    image: "postgres" # use latest official postgres version
    env_file:
      - database.env # configure postgres
    volumes:
      - database-data:/var/lib/postgresql/data/ # persist data even if container shuts down
    ports:
      - 5435:5432
    expose:
      - "5435"

volumes:
  database-data: # named volumes can be managed easier using docker-compose

my database.env for reference

  PGUSER=postgres
  PGPASSWORD=postgres
  PGDATABASE=postgres
  POSTGRES_USER=postgres
  POSTGRES_PASSWORD=postgres
  POSTGRES_DB=postgres

yes it’s been redacted. i am not actually forcing the default values. just wanted to clarify the file structure and proper envs as noted here: https://www.postgresql.org/docs/9.3/libpq-envars.html

in addition to the others the docker container uses.

there seems to be no reason for 2 different sets of envs, but i haven’t thought through every use case.

so perhaps that’s a possible fix here is using the core PG envs and then update container docs to match.

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows

Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)

Option 3: Change the port of the docker container

You are so beautiful, THANKSS

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:
* Option 1: Stop the PostgreSQL service on Windows

* Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (`sudo apt install postgresql-client-common postgresql-client libpq-dev`)

* Option 3: Change the port of the docker container

You saved me tons of painful 72hours of battle with innocent Intellij IDE and plugins

I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.

I’m using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows

Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)

Option 3: Change the port of the docker container

Faced the same issue. Solved it by changing port mapping on docker-compose

services:
  db:
    image: postgres
    container_name: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: ***
      POSTGRES_USER: ***
      POSTGRES_DB: ***
    ports:
      - 5435:5432    ==> changed 5432 to 5435

@gustavorps I think the solution is an easy one to work around. Using @JustifydWarrior’s fix above:

    ports:
      - 5435:5432
    expose:
      - "5435"

5433 and 5434 are also reserved for more common uses, however 5435 is not a very common protocol by definition & is most often used as a second postgres port.

@bibhuticoder came up with the same solution.

I think someone should submit a PR to fix this, even if it is a second docker compose file, simply called by using the filename flag.

Disabling the default service, is the simplest solution, but that shouldn’t be the preferred workaround, many of us need both running.

I had the same problem . My solution for windows :

Stop docker container
win + r and type services.msc
Find postgressql and stop
Start your container again

I solved my problem with this way .

Envs are used by postgres initdb to create your postgresql instance. If you changed yours envs after first execution, you need to delete the postgres container to force a recreation using new envs. If you have defined a volume, you will also need to remove it.

My english is not very well, but i try help you.

In your project run

if exist a folder called database-data (name of the your volume of postgres of the docker-compose.yml), remove this folder and run again

  docker-compose up -d --force-recreate

The my work this way.

Or
if it didn’t work, try
Remove all volumes outside project with

  docker volume rm $(docker volume ls -q)

and run again
«`console`
docker-compose up -d —force-recreate
«

Below is my postgres:14 in the docker-compose.yml

I am writing Go Program in the microservice manner.
One day before POSTGRES_PASSWORD: password
was the password. Next day I changed POSTGRES_PASSWORD: secret
Now the said error came.

The folder /db-data/postgres/ which I mentioned below has content populated by docker.
Deleted that content and again run docker. Now ok.

postgres:
image: ‘postgres:14.0’
ports:
— «5432:5432»
restart: always
deploy:
mode: replicated
replicas: 1
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: secret
POSTGRES_DB: users
volumes:
— ./db-data/postgres/:/var/lib/postgresql/data/

@eddex Thanks a lot. I was having the same issue with my local Postgres installation and the container I was running by using docker. I just stopped the service on Windows and started working.

THanks it works for me

A uppercase character in the username will also trigger this error message…

Источник

On a new PostgreSQL server, the following error message was received, when a user tried to use psql:

$ psql -h 127.0.0.1 -U dbuser
Password for user dbuser: *****
psql: FATAL: password authentication failed for user «dbuser»
FATAL: password authentication failed for user «dbuser»

The logs showed the following entries:

[11834] dbuser@dbuser FATAL: password authentication failed for user «dbuser»
[11834] dbuser@dbuser DETAIL: Password does not match for user «dbuser».
Connection matched pg_hba.conf line 92: «host all all 127.0.0.1/32 md5»
[11835] dbuser@dbuser FATAL: password authentication failed for user «dbuser»
[11835] dbuser@dbuser DETAIL: Password does not match for user «dbuser».
Connection matched pg_hba.conf line 92: «host all all 127.0.0.1/32 md5»

Verifying authentication configuration

Obviously the first question in such a situation is: Was the entered password correct? And in the special case of PostgreSQL a second question should always come into mind: Is there a correct configuration in pg_hba.conf for the attempted authentication?

The authentication obviously correctly matched a line in pg_hba.conf, as could also be seen in the log file:

# grep 127.0.0.1 /etc/postgresql/9.6/main/pg_hba.conf
host all all 127.0.0.1/32 md5

After it was verified that the entered password was actually correct, the creation of the SQL user was once more analyzed. This turned out to be a manual user creation using an SQL query (old habits):

postgres=# CREATE USER dbuser WITH PASSWORD ‘mysecretpassword’;

Spotting the difference in user creation

When using the createuser command, a special parameter -e can be used. This will show the actual SQL queries sent to PostgreSQL:

postgres@pgserver:~$ createuser -P -E -e dbuser
Enter password for new role: *****
Enter it again: *****
SELECT pg_catalog.set_config(‘search_path’, », false)
CREATE ROLE dbuser ENCRYPTED PASSWORD ‘md5b3e88aa92b0943f1d2eed5cc618255e8’ NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

A significant difference is the usage of «ENCRYPTED PASSWORD» vs. «WITH PASSWORD» from the manual SQL query. Would that mean that the md5 password encryption (as defined in pg_hba.conf) didn’t work because the password from the manual user creation (using SQL query) was understood as plain text?

This can be verified again, now that the user was created using createuser:

$ psql -h 127.0.0.1 -p 5432 -U dbuser
Password for user dbuser: *****
psql (9.6.16)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type «help» for help.

dbuser=>

Indeed, the password authentication worked this time and the user is logged in!

Encrypted vs unencrypted passwords

The documentation of the createuser command shows that there are two different options available whether or not to use an encrypted password:

CREATE USER name [ [ WITH ] option [ … ] ]

where option can be:

Particularly these keywords are described as:

ENCRYPTED | UNENCRYPTED

These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.

It also says that if neither of these keywords was used, the system default would be applied. To check the current PostgreSQL setting whether or not to encrypt passwords by default, the following SQL query can be run:

postgres@pgserver:~$ psql -qAtc «SELECT name, setting FROM pg_settings WHERE name = ‘password_encryption'»
password_encryption|on

It can also be checked in the configuration file, in case the default should be changed:

root@pgserver:~# grep password_encryption /etc/postgresql/9.6/main/postgresql.conf
#password_encryption = on

TL;DR: Now it makes sense

The manual user creation using an SQL query did not contain either ENCRYPTED nor UNENCRYPTED keywords. This means that PostgreSQL automatically applied the default: ENCRYPTED. The given password («mysecretpassword») was therefore encrypted and stored in the database. Logins with exactly this plain password («mysecretpassword») would of course fail, because it does not match the encrypted stored value.

Although the manual user creation using an SQL query still works, the newer createuser command should be used primarily. This will avoid errors or mistakes from remembered SQL queries from earlier PostgreSQL versions. As mentioned, old habits.

Add a comment

Show form to leave a comment

Comments (newest first)

Mert from wrote on Jan 2nd, 2021:

Thank’s for clarification i was trying wrap my mind around this for whole evening. More people should be aware of subtle difference between authentication mechanisms, especially newcomers.

Источник

I am running postgresql-9.6 on Rhel7. I know that the default/root user doesnt have a password, however i will like to use phpPgAdmin to do my work. the problem is I have edited my pg_hba.conf file to look as follow, (based on various documentation I have read so far:

local   all             all                          password
# IPv4 local connections:
host    all             all       127.0.0.1/32       password
# IPv6 local connections:
host    all             all             ::1/128      ident

The only problem is that after this configuration even the default user postgres has lost access completely. When I try to log in as the root user with psql and and enter my computer's root user's password this is what what happens:

-bash-4.2$ psql
Password:
psql: FATAL:  password authentication failed for user "postgres"

but when I change the above configuration on the pg_hba.conf file to:

peer
ident
ident

I can log in as root however phpPgAdmin won’t let me in without a password.

asked Sep 18, 2018 at 0:25

I was in same situation. after installation on window machine I was getting below error:

C:Usersxxxxx>psql -U postgres

psql: FATAL:  password authentication failed for user "postgres"
password retrieved from file "C:UsersxxxxxAppDataRoaming/postgresql/pgpass
.conf"

To fix the above, I edited ‘pgpass.conf’ file with password I was using for the ‘postgres’ database, and it worked.

peterh

1,9877 gold badges27 silver badges40 bronze badges

answered Oct 6, 2018 at 10:20

This error can provide from the fact that you installed actively another version of Postgres or passively within a system update (and you may not be aware in this case).

The consequence is that you try to connect to the current version of database that has changed (since install or update) and having in fact a port number that has changed. Therefore you may need to check this port number :

isogladiator@edubuntu:~$ sudo /etc/init.d/postgresql status
9.3/main (port **5432**): online
9.5/main (port **5434**): online
11/main (port **5433**): online

You may be using standard port 5432 while it has become 5433 for example.

answered Feb 28, 2019 at 0:25

Источник

↓
Содержание статьи
↓

	psql command not found
	ERROR: character with byte sequence 0xd0 0x9a in encoding «UTF8» has no equivalent in encoding «WIN1252»
	ERROR: database «db» is being accessed by other users
	FATAL password authentication failed for user postgres
	ERROR: could not open file «/home/user…» for reading: Permission denied
	ERROR: COPY quote must be a single one-byte character
	ERROR: date/time field value out of range
	Job for postgresql.service failed because the control process exited with error code
	psql: could not connect to server: No such file or directory
	pg_basebackup: could not connect to server: No route to host
	Failed to stop postgresql.service: Unit postgresql.service not loaded
	ERROR: WAL level not sufficient for making an online backup
	NOTICE: WAL archiving is not enabled

Ошибки

psql command not found

Вы хотите запустить Postgres скрипт из bash

andrey@olegovich-10:/mnt/c/Users/olegovich$ psql -h localhost -p 5432 -U andrei

но получаете эту ошибку

-bash: psql: command not found

Это значит, что путь до Postgres не прописан в $PATH

Чтобы узнать, что прописано в $PATH достаточно сделать

echo $PATH

/home/andrei/bin:/home/andrei/.local/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/mnt/c/Program Files (x86)/Common Files/Oracle/Java/javapath_target_1128437:/mnt/c/ProgramData/Oracle/Java/javapath_target_5252250:/mnt/c/Windows/System32:/mnt/c/Windows:/mnt/c/Windows/System32/wbem:/mnt/c/Windows/System32/WindowsPowerShell/v1.0:/mnt/c/Program Files/OpenVPN/bin:/mnt/c/Program Files (x86)/Microsoft SQL Server/Client SDK/ODBC/130/Tools/Binn:/mnt/c/Program Files (x86)/Microsoft SQL Server/140/Tools/Binn:/mnt/c/Program Files (x86)/Microsoft SQL Server/140/DTS/Binn:/mnt/c/Program Files (x86)/Microsoft SQL Server/140/Tools/Binn/ManagementStudio:/mnt/c/Program Files/MiKTeX 2.9/miktex/bin/x64:/mnt/c/Users/andreyolegovich_ru/Documents/Software/axis2-1.6.2:/mnt/c/Users/andreyolegovich_ru/Documents/Software/axis2-1.6.2/bin:/mnt/c/Windows/System32/OpenSSH:/mnt/c/Program Files/TortoiseSVN/bin:/mnt/c/Program Files/Microsoft SQL Server/Client SDK/ODBC/130/Tools/Binn:/mnt/c/Program Files/Microsoft SQL Server/140/Tools/Binn:/mnt/c/Program Files/Microsoft SQL Server/140/DTS/Binn:/mnt/c/Program Files (x86)/Intel/Intel(R) Management Engine Components/DAL:/mnt/c/Program Files/Intel/Intel(R) Management Engine Components/DAL:/mnt/c/Program Files/TortoiseGit/bin:/mnt/c/Program Files/Git/cmd:/mnt/c/Program Files/nodejs:/mnt/c/Program Files/Intel/WiFi/bin:/mnt/c/Program Files/Common Files/Intel/WirelessCommon:/mnt/c/Program Files/PuTTY:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Continuum/anaconda3:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Continuum/anaconda3/Library/mingw-w64/bin:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Continuum/anaconda3/Library/bin:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Continuum/anaconda3/Scripts:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Python/Python36/Scripts:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Python/Python36:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Microsoft/WindowsApps:/mnt/c/Users/andreyolegovich_ru/AppData/Local/atom/bin:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Python/Python36-32:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Python/Python36-32/Scripts:/mnt/c/Program Files (x86)/Nmap:/mnt/c/Program Files (x86)/Mozilla Firefox:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Microsoft/WindowsApps:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Fiddler:/mnt/c/Program Files/JetBrains/PyCharm Community Edition 2018.3.2/bin:/mnt/c/Users/andreyolegovich_ru/AppData/Roaming/npm:/mnt/c/Program Files/Intel/WiFi/bin:/mnt/c/Program Files/Common Files/Intel/WirelessCommon:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Microsoft VS Code/bin:/snap/bin

ERROR: character with byte sequence 0xd0 0x9a in encoding «UTF8»

has no equivalent in encoding «WIN1252»

Скорее всего Вы создали базу данных, и даже смогли туда что-то импортировать, например, из .csv файла.

Но сделать SELECT * FROM table; уже не получается, потому что кодировка базы и кодировка файла не совпадают.

Возможно, Вы уже попробовали явно указать SET CLIENT_ENCODING TO ‘utf8’; при импорте файла. Но так как кодировка WIN1252
— это кодировка БД, способ не сработал.

Нужно привести файл и БД к одной кодировке — пересоздайте БД в utf8, например.

Как проверить кодировки я писал выше —

Проверка кодировок БД

Как указать кодировку при создании БД —

Создание БД

ERROR: database «db» is being accessed by other users

Если Вы делаете DROP DATABASE db; и получаете

ERROR: database «db» is being accessed by other users

DETAIL: There are 2 other sessions using the database.

Значит где-то ещё не закрыто подключение к БД. Например, Вы открывали её через pgAdmin.

Нужно найти это подключение и закрыть

FATAL password authentication failed for user postgres

Если вы логинитесь в pgAdmin, но не помните пароль — его можно поменять через терминал

sudo su — postgres

psql

postgres=# ALTER USER postgres PASSWORD ‘новый_пароль’;

ALTER ROLE

ERROR: could not open file «/home/user…» for reading: Permission denied

Если вы пытаетесь прочитать из файла, а получаете

ERROR: could not open file «/home/user/file.csv» for reading: Permission denied
HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql’s copy. SQL state: 42501

Значит у postgres недостаточно прав для чтения из файла. Простое добавление прав на чтение вроде

chmod +r file.csv

Проблему, скорее всего, не решит.

Как вариант — предлагаю переместить нужный файл в директорию /tmp

cp /home/user/file.csv /tmp

ERROR: COPY quote must be a single one-byte character

Если вы пытаетесь прочитать из файла, а получаете

ERROR: COPY quote must be a single one-byte character

SQL state: 0A000

Скорее всего присутствует какой-то лишний символ в QUOTE, например

QUOTE ‘»‘

Замените на

QUOTE ‘»‘

ERROR: date/time field value out of range

Если вы пытаетесь прочитать из .csv файла, а получаете

ERROR: date/time field value out of range: «» HINT: Perhaps you need a different «datestyle» setting. CONTEXT: «» SQL state: 22008

Скорее всего ваш текущий datestyle не совпадает с тем, который используется в .csv файле.

datestyle — это порядок записи даты. Может быть День — Месяц — Год (DDMMYYYY), Год — Месяц — День (YYYYMMDD) или,
например американский стиль Месяц — День — Год (MMDDYYYY)

Это всё актуально если тип столбца указан как дата date. Можно изменить тип на char тогда datestyle уже не нужно настраивать.

Стилей много и если они не совпадают — получается что месяц принимает значение больше 12.

Как вариант — можно перед выполнение скрипта временно изменить свой datestyle.

Например, если нужно импортировать данные из .csv с американским стилем — перед импортом добавьте

set datestyle to «US»;

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

Если вы выполнили

psql

И получили ошибку

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»?

Очень часто данная ошибка возникает вследствии того, что не была инициализирована база
данных.

Выполните

postgresql-setup initdb

Initializing database … OK

pg_basebackup: could not connect to server: could not connect to server: No route to host

Если вы пытаетесь сделать реплику

pg_basebackup -h 192.168.56.109 -U repluser -D /var/lib/pgsql/data —xlog-method=stream

pg_basebackup: could not connect to server: could not connect to server: No route to host
Is the server running on host «192.168.56.109» and accepting
TCP/IP connections on port 5432?

На мастере

sudo firewall-cmd —zone=public —add-port=5432/tcp —permanent

success

sudo firewall-cmd —reload

success

sudo firewall-cmd —list-ports

3389/tcp 5432/tcp

Failed to stop postgresql.service: Unit postgresql.service not loaded

Причин может быть много но среди новичков самая распространённая — попытка остановить postgresql
из под пользователя postges

Например, в моём терминале я по приглашению bash-4.2$ вижу, что зашёл как postgres

Нужно выполнить

exit

Приглашение изменится на

[andrei@localhost ~]$

И затем уже можно останавливать сервер

sudo systemctl stop postgresql

sudo systemctl status postgresql

● postgresql.service — PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: inactive (dead)

Jun 09 12:20:24 localhost.localdomain systemd[1]: Unit postgresql.service entered failed state.
Jun 09 12:20:24 localhost.localdomain systemd[1]: postgresql.service failed.
Jun 09 12:21:59 localhost.localdomain systemd[1]: Starting PostgreSQL database server…
Jun 09 12:22:00 localhost.localdomain systemd[1]: Started PostgreSQL database server.
Jun 10 19:10:02 localhost.localdomain systemd[1]: Stopping PostgreSQL database server…
Jun 10 19:10:03 localhost.localdomain systemd[1]: Stopped PostgreSQL database server.
Jun 10 22:14:18 localhost.localdomain systemd[1]: Starting PostgreSQL database server…
Jun 10 22:14:19 localhost.localdomain systemd[1]: Started PostgreSQL database server.
Jun 11 10:11:15 localhost.localdomain systemd[1]: Stopping PostgreSQL database server…
Jun 11 10:11:16 localhost.localdomain systemd[1]: Stopped PostgreSQL database server.

ERROR: WAL level not sufficient for making an online backup

Вы хотите настроить онлайн бэкап, например с помощью команды

-bash-4.2$ psql -c «SELECT pg_start_backup(‘replbackup’);»

Но получаете ошибку

ERROR: WAL level not sufficient for making an online backup

HINT: wal_level must be set to «archive» or «hot_standby» at server start.

Нужно узнать расположение конфигурационного файла

postgresql.conf

-bash-4.2$ su — postgres -c «psql -c ‘SHOW config_file;'»

Password:
config_file
————————————-
/var/lib/pgsql/data/postgresql.conf
(1 row)

vi /var/lib/pgsql/data/postgresql.conf

Нужно установить wal_level = hot_standby

NOTICE: WAL archiving is not enabled

Вы заканчиваете бэкап, например с помощью команды

psql -c «SELECT pg_stop_backup();»

Но получаете предупреждение

NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

Источник

Tip

Comments

Below is my postgres:14 in the docker-compose.yml

Verifying authentication configuration

Spotting the difference in user creation

Encrypted vs unencrypted passwords

TL;DR: Now it makes sense

Add a comment

Comments (newest first)

Ошибки

psql command not found

ERROR: character with byte sequence 0xd0 0x9a in encoding «UTF8» has no equivalent in encoding «WIN1252»

ERROR: database «db» is being accessed by other users

FATAL password authentication failed for user postgres

ERROR: could not open file «/home/user…» for reading: Permission denied

ERROR: COPY quote must be a single one-byte character

ERROR: date/time field value out of range

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

pg_basebackup: could not connect to server: could not connect to server: No route to host

Failed to stop postgresql.service: Unit postgresql.service not loaded

ERROR: WAL level not sufficient for making an online backup

NOTICE: WAL archiving is not enabled

ERROR: character with byte sequence 0xd0 0x9a in encoding «UTF8»

has no equivalent in encoding «WIN1252»