Sometimes PostgreSQL users get errors and warnings and they are unable to understand why. To cater to these situations, this blog will cover some common errors and warnings in PostgreSQL. In some cases, it is a user setting problem or query error, but in other cases, it can be a PostgreSQL bug. But, it is quite rare to be a PostgreSQL bug, and therefore it is really important to differentiate between user error and PostgreSQL bug. Here is a list of some common PostgreSQL errors, with symptoms and solutions.
1 – Is the PostgreSQL Server Running Locally and Accepting?
1 2 3 4 | $ psql postgres psql: error: could not connect to server: 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"? |
This usually occurs when the PostgreSQL server is not running, but in some cases, you can get a similar error even when the PostgreSQL server is still running. There are multiple ways to check whether the server is running or not, depending on the installation and operating system. Here are some steps you can perform to check:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | sudo service postgresql-12 status Redirecting to /bin/systemctl status postgresql-12.service postgresql-12.service - PostgreSQL 12 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; disabled; vendor preset: disabled) Active: active (running) since Sun 2020-05-31 23:55:39 UTC; 8s ago Docs: https://www.postgresql.org/docs/12/static/ Process: 32204 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 32209 (postmaster) CGroup: /system.slice/postgresql-12.service ├─32209 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/ ├─32211 postgres: logger ├─32213 postgres: checkpointer ├─32214 postgres: background writer ├─32215 postgres: walwriter ├─32216 postgres: autovacuum launcher ├─32217 postgres: stats collector └─32218 postgres: logical replication launcher |
The status of the PostgreSQL service shows it and all its subprocess processes are running. The second reason for the error could be the port number, as the default port of PostgreSQL is 5432. If PostgreSQL is configured to run on a different port, then the user needs to specify the port number (with some exceptions). Here is the way to check the port number:
1 2 3 4 | sudo service postgresql-12 status | grep port Redirecting to /bin/systemctl status postgresql-12.service LOG: listening on IPv6 address "::1", port 5432 listening on IPv4 address "127.0.0.1", port 5432 |
2 – Initdb Cannot Be Run as Root
1 | $ initdb -D data initdb: error: cannot be run as root Please log in (using, e.g., "su") as the (unprivileged) user that will own the server process. |
The initdb command is used to initialize the PostgreSQL cluster. Sometimes people try to use that by root, the user which can cause the said error. It’s very simple to switch the unprivileged user using us and initdb the cluster. You cannot “initdb” using superuser, so change the user that owns the server process and then do initdb. Or, you can use postgresql-12-setup to initialize the cluster.
1 | sudo ./postgresql-12-setup initdb |
3 – Initdb Failed Due to Directory ‘Invalid Permission’
1 2 3 4 | $ initdb -D data running bootstrap script ... FATAL: data directory "data" has invalid permissions [1885] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750). child process exited with exit code 1 initdb: removing data directory "data" |
The directory should have permission u=rwx (0700) or u=rwx,g=rx (0750) to perform initdb. Either you can initialize the cluster into another directory or change the permission of the directory to u=rwx (0700) or u=rwx,g=rx (0750).
4 – Object Permission
1 2 3 4 | $ SELECT * FROM TEST; ERROR: permission denied for table test [1788] STATEMENT: select * from test; ERROR: permission denied for table test |
This error only happens when you create some object using one user and another user does have access to that object.
5 – Out of Disk Space Error
1 2 3 | ERROR: could not extend file "base/30122/331821": No space left on device HINT: Check free disk space. |
PostgreSQL initializes its cluster into $PGDATA. It is very important to keep an eye on that directory and free up some space before that drive runs out of it. There are some ways to optimize the situation, like:
- Free some space on the disk
- Point pg_wal to another disk, and in that case, all walls will generate to another disk, and data is distributed among multiple disks.
- Create a tablespace on another disk and create a table in that tablespace. You can divide your data between multiple disks.
6 – Replication Standby Issue
1 2 | FATAL: database system identifier differs between the primary and standby [20595] DETAIL: The primary's identifier is 6832398539310516067, the standby's identifier is 6832397799517112074. |
You are replicating a server to another which is not a copy of the original. You need to configure master replication and table a base backup using pg_basebackup and start the slave.
7 – The Server Terminated
1 2 3 4 | server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed |
This one is a critical error, and in that case, you need to identify the cause. If possible, generate the stack trace and consult your service provider.
8 – Out of Memory Issue
1 2 | ERROR: out of memory 2020-05-08 DETAIL: Failed on request of size 1880. |
Every system has a limited amount of memory. When there is no memory left, PostgreSQL’s memory allocation functions start failing. Please check your configuration and limitations of your hardware.
9 – OOM Killer
1 2 | Out of memory: Kill process 1766 (postmaster) score 890 or sacrifice child Killed process 1766, UID 26,(postmaster) total-vm: 24384508 kB, anon-rss:14376288kB, file-rss:138616kB |
When there is not enough memory left, the OOM killer invokes and kills the PostgreSQL process. Some people disable that process, but it is not recommended. You need to check the memory setting according to your hardware.
10 – Replication Archive Command Failure
1 2 3 | cp: cannot create regular file '/usr/local/wal/000000010000000000000001': No such file or directory LOG: archive command failed with exit code 1 DETAIL: The failed archive command was: cp pg_wal/000000010000000000000001 /usr/local/pgsql-logical/wal/000000010000000000000001 |
This error appears when you have specified the destination directory for WAL in archive_command, which does not exist. You need to create /usr/local/wal/ file or specify the directory which does exist.
Our white paper “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.
One of the most common error is “connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host “XX.XXX.XX.XXX”, user “XXX”, database “XXX”, SSL off…”
You make it 11, thanks for that.