Postgres errors

Errors

psql command not found

You want to run a Postgres script from bash

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

but you get this error

-bash: psql: command not found

This means that the path to Postgres is not set in $PATH

To find out what is set in $PATH, it is enough to run

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"

Most likely you created a database and even managed to import something into it, for example from a .csv file.

But SELECT * FROM table; no longer works, because the encoding of the database and the encoding of the file do not match.

Perhaps you have already tried to specify SET CLIENT_ENCODING TO 'utf8'; explicitly when importing the file. But since the WIN1252 encoding is the encoding of the database, the method did not work.

The file and the database must be brought to the same encoding - recreate the database in utf8, for example.

I wrote above how to check the encodings - Checking database encodings

How to specify the encoding when creating a database - Creating a database

ERROR: database "db" is being accessed by other users

If you run DROP DATABASE db; and get

ERROR: database "db" is being accessed by other users
DETAIL: There are 2 other sessions using the database.

It means that a connection to the database is still open somewhere. For example, you opened it through pgAdmin.

You need to find that connection and close it

FATAL password authentication failed for user postgres

If you log in to pgAdmin but do not remember the password - it can be changed through the terminal

sudo su - postgres
psql
postgres=# ALTER USER postgres PASSWORD 'new_password';

ALTER ROLE

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

If you are trying to read from a file and get

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

It means postgres does not have enough permissions to read from the file. Simply adding read permissions such as

chmod +r file.csv

will most likely not solve the problem.

As an option - I suggest moving the needed file to the directory /tmp

cp /home/user/file.csv /tmp

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

If you are trying to read from a file and get

ERROR: COPY quote must be a single one-byte character
SQL state: 0A000

Most likely there is some extra character in QUOTE, for example

QUOTE '\"'

Replace it with

QUOTE '"'

ERROR: date/time field value out of range

If you are trying to read from a .csv file and get

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

Most likely your current datestyle does not match the one used in the .csv file.

datestyle is the order in which the date is written. It can be Day - Month - Year (DDMMYYYY), Year - Month - Day (YYYYMMDD) or, for example the American style Month - Day - Year (MMDDYYYY)

All this is relevant if the column type is specified as the date type. You can change the type to char, then datestyle no longer needs to be configured.

There are many styles and if they do not match - it turns out that the month takes a value greater than 12.

As an option - you can temporarily change your datestyle before running the script.

For example, if you need to import data from a .csv with the American style - before importing add

set datestyle to "US";

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

If you ran

psql

And got the error

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

Very often this error occurs because the database was not initialized.

Run

postgresql-setup initdb

Initializing database ... OK

Read more about initializing a PostgreSQL database using the example for CentOS Linux in the article

«Installing PostgreSQL on CentOS»

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

If you are trying to make a replica

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?

On the master

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

There can be many reasons, but among beginners the most common is an attempt to stop postgresql from the postges user

For example, in my terminal, by the bash-4.2$ prompt I see that I logged in as postgres

You need to run

exit

The prompt will change to

[andrei@localhost ~]$

And then you can already stop the server

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

You want to set up an online backup, for example with the command

-bash-4.2$ psql -c "SELECT pg_start_backup('replbackup');"

But you get the error

ERROR: WAL level not sufficient for making an online backup
HINT: wal_level must be set to "archive" or "hot_standby" at server start.

You need to find the location of the configuration file 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

You need to set wal_level = hot_standby

NOTICE: WAL archiving is not enabled

You are finishing the backup, for example with the command

psql -c "SELECT pg_stop_backup();"

But you get a warning

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

Article author: Andrei Olegovich