How to Decode the PostgreSQL Error Logs

PostgreSQL error reporting follows a style guide aimed at providing the database administrator with the information required to efficiently troubleshoot issues. Error messages normally contain a short description, followed by some detailed information, and a hint, if applicable, suggesting the solution. There are other fine details, explained in the guide, such as the use of past or present tense to indicate if the error is temporary or permanent.

Types of Errors and Severity Levels

When reporting errors, PostgreSQL will also return an SQLSTATE error code, therefore errors are classified into several classes. When reviewing the list of classes, note that success and warning are also logged by PostgreSQL to the error log — that is because logging_collector, the PostgreSQL process responsible for logging, sends all messages to stderr by default.

When the logging collector has not been initialized, errors are logged to the system log. For example, when attempting to start the service following the package installation:

[root@omiday ~]# systemctl start postgresql
Job for postgresql.service failed because the control process exited with error code.
See "systemctl  status postgresql.service" and "journalctl  -xe" for details.
[root@omiday ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Wed 2018-01-24 19:10:04 PST; 8s ago
Process: 1945 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=1/FAILURE)

Jan 24 19:10:04 omiday.can.local systemd[1]: Starting PostgreSQL database server...
Jan 24 19:10:04 omiday.can.local postgresql-check-db-dir[1945]: Directory "/var/lib/pgsql/data" is missing or empty.
Jan 24 19:10:04 omiday.can.local postgresql-check-db-dir[1945]: Use "/usr/bin/postgresql-setup --initdb"
Jan 24 19:10:04 omiday.can.local postgresql-check-db-dir[1945]: to initialize the database cluster.
Jan 24 19:10:04 omiday.can.local postgresql-check-db-dir[1945]: See /usr/share/doc/postgresql/README.rpm-dist for more information.
Jan 24 19:10:04 omiday.can.local systemd[1]: postgresql.service: Control process exited, code=exited status=1
Jan 24 19:10:04 omiday.can.local systemd[1]: Failed to start PostgreSQL database server.
Jan 24 19:10:04 omiday.can.local systemd[1]: postgresql.service: Unit entered failed state.
Jan 24 19:10:04 omiday.can.local systemd[1]: postgresql.service: Failed with result 'exit-code'.

When returning error messages to clients, and therefore logging to error log, messages are logged with a severity level that is controlled using the client_min_messages parameter. Logging to server log files is controlled by the parameter log_min_messages, while log_min_error_statement enables logging of SQL statements that cause an error of a specific severity level.

PostgreSQL can be configured to log at the following severity levels:

• PANIC: All database sessions are aborted. This is a critical situation that affects all clients.
• FATAL: The current session is aborted due to an error. The client may retry. Other databases in the cluster are not affected.
• LOG: Normal operation messages.
• ERROR: Failure to execute a command. This is a permanent error.
• WARNING: An event that, while not preventing the command to complete, may lead to failures if not addressed. Monitoring for warnings is a good practice in early detection of issues on both the server and application side.
• NOTICE: Information that clients can use to improve their code.
• INFO: Logs explicitly requested by clients.
• DEBUG1..DEBUG5: Developer information.

Note: Higher level messages include messages from lower levels i.e. setting the logging level to LOG, will instruct PostgreSQL to also log FATAL and PANIC messages.

Common Errors and How to Fix Them

What follows is a non exhaustive list:

Error Message

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

Cause

[root@omiday ~]# psql -U postgres
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"?

Resolution

Verify that the PostgreSQL service is running using operating system tools (ps, netstat, ss, systemctl) or check for the presence of postmaster.pid in the data directory.

Error Message

psql: FATAL:  Peer authentication failed for user "postgres"

Cause

[root@omiday ~]# psql -U postgres
psql: FATAL:  Peer authentication failed for user "postgres"

Resolution

The log file will contain a more detailed message to that effect:

LOG:  provided user name (postgres) and authenticated user name (root) do not match
FATAL:  Peer authentication failed for user "postgres"
DETAIL:  Connection  matched  pg_hba.conf  line  80:  "local  all  all  peer"

Follow these steps:

1. Log in as the postgres user:

   [root@omiday ~]# su - postgres
   [postgres@omiday ~]$ psql
   psql (9.6.6)
   Type "help" for help.
   
   postgres=#

2. Make the following change to pg_hba.conf that will allow the root user to log in without a password:

   --- a/var/lib/pgsql/data/pg_hba.conf
   +++ b/var/lib/pgsql/data/pg_hba.conf
   @@ -77,6 +77,7 @@
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # "local" is for Unix domain socket connections only
   +local   all             postgres                                trust
   local   all             all                                     peer
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            ident

3. Reload the service and test:

   [root@omiday ~]# psql -U postgres
   psql (9.6.6)
   Type "help" for help.
   
   postgres=#

Error Message

psql: could not connect to server: Connection refused
        Is the server running on host "192.168.0.11" and accepting
        TCP/IP connections on port 5432?

Cause

A client attempted a connection to the public IP address.

Note: This is an error returned to the client, in the example above psql. In case of a web application check the web server error log.

Resolution

Configure the service to listen on the public IP address:

Note: As a best practice use alter system rather than editing postgresql.conf.

postgres=# alter system set listen_addresses TO 'localhost,192.168.0.11';
ALTER SYSTEM

The alter system command has modified the postgresql.auto.conf as shown below:

--- a/var/lib/pgsql/data/postgresql.auto.conf
+++ b/var/lib/pgsql/data/postgresql.auto.conf
@@ -1,2 +1,3 @@
# Do not edit this file manually!
-# It will be overwritten by the ALTER SYSTEM command.
+# It will be overwritten by ALTER SYSTEM command.
+listen_addresses = 'localhost,192.168.0.11'

Restart the service and test:

[root@omiday ~]# psql -U webuser -h 192.168.0.11 webapp
psql: FATAL:  no pg_hba.conf entry for host "192.168.0.11", user "webuser", database "webapp", SSL off

We’ll address this error in the next topic.

Error Message

psql: FATAL:  no pg_hba.conf entry for host "192.168.0.11", user "webuser", database "webapp", SSL off

Cause

PostgreSQL service running on the IP address 192.168.0.11 is not configured to allow the user webuser to connect to the database webapp.

Resolution

Modify the access file pg_hba.conf to allow the connection:

--- a/var/lib/pgsql/data/pg_hba.conf
+++ b/var/lib/pgsql/data/pg_hba.conf
@@ -81,6 +81,7 @@
local   all             postgres                                trust
local   all             all                                     peer
# IPv4 local connections:
host    all             webuser         127.0.0.1/32            md5
+host    all             webuser         192.168.0.11/32         md5
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             webuser         ::1/128                 md5

Reload the service and test:

[root@omiday ~]# psql -U webuser -h 192.168.0.11 webapp
Password for user webuser:
psql (9.6.6)
Type "help" for help.

webapp=> c
You are now connected to database "webapp" as user "webuser".

Error Message

ERROR:  syntax error at or near "grant"

Cause

Grant is one of the PostgreSQL reserved keywords

Resolution

Reserved keywords must be quoted:

webapp=> create table "grant" (id numeric);
CREATE TABLE
And verify:
webapp=> d "grant"
     Table "public.grant"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | numeric |

webapp=>

Error Message

ERROR:  cannot drop table cust because other objects depend on it

Cause

A client attempted removing the table cust that has child tables.

Resolution

Review HINT in the log file:

ERROR:  cannot drop table cust because other objects depend on it
DETAIL:  table cust_region_1 depends on table cust
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
STATEMENT:  drop table cust;

Error Message

ERROR:  invalid input syntax for type numeric: "b" at character 26

Cause

The log file reveals an attempt to insert a value that doesn’t match the column type:

ERROR:  invalid input syntax for type numeric: "b" at character 26
STATEMENT:  insert into cust values ('b', 2);

Resolution

This is an application side error that must be corrected by developers, or if it was initiated by a client such as a DBA running psql. No action is required by the production DBA, since the full error message was also returned to the client.

Reviewing and Monitoring Logs

The most simple option is configuring PostgreSQL to use syslog via the log_destination parameter so logs can be shipped to your favorite centralized logging system (e.g. rsyslog) and then further processed there for alerting on specific error conditions.

Another tool, that requires a close to none setup is tail_n_mail, which works in combination with the cron daemon.

Yet another tool in this list is pgBadger that comes with a rich set of options for reporting, visualizing and analyzing not only the PostgreSQL log files but also the information logged by the statistics collector.

Moving up on the complexity scale, organization can benefit from investing the time and effort for setting up an ELK stack, which uses the Filebeat PostgreSQL module to generate alerts and reports.

Conclusion

Reviewing the error logs, being notified on critical issues, and having a versatile log management system that aids in troubleshooting is important in maintaining a healthy database environment. Fortunately, PostgreSQL provides a rich error management framework, reflected in the large variety of available products to choose from. The criteria for selecting the ones that best suit a specific environment must include not only the product features but also the technical expertise required.