10 Common PostgreSQL Errors

common postgresql errorsSometimes 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?

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:

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:

2 – Initdb Cannot Be Run as Root

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.

3 – Initdb Failed Due to Directory ‘Invalid Permission’

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

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

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

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

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

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

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