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