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: Starting PostgreSQL database server... Jan 24 19:10:04 omiday.can.local postgresql-check-db-dir: Directory "/var/lib/pgsql/data" is missing or empty. Jan 24 19:10:04 omiday.can.local postgresql-check-db-dir: Use "/usr/bin/postgresql-setup --initdb" Jan 24 19:10:04 omiday.can.local postgresql-check-db-dir: to initialize the database cluster. Jan 24 19:10:04 omiday.can.local postgresql-check-db-dir: See /usr/share/doc/postgresql/README.rpm-dist for more information. Jan 24 19:10:04 omiday.can.local systemd: postgresql.service: Control process exited, code=exited status=1 Jan 24 19:10:04 omiday.can.local systemd: Failed to start PostgreSQL database server. Jan 24 19:10:04 omiday.can.local systemd: postgresql.service: Unit entered failed state. Jan 24 19:10:04 omiday.can.local systemd: 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:
psql: could not connect to server: No such file or directory
[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"?
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.
psql: FATAL: Peer authentication failed for user "postgres"
[root@omiday ~]# psql -U postgres psql: FATAL: Peer authentication failed for user "postgres"
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:
Log in as the postgres user:
[root@omiday ~]# su - postgres [postgres@omiday ~]$ psql psql (9.6.6) Type "help" for help. postgres=#
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
Reload the service and test:
[root@omiday ~]# psql -U postgres psql (9.6.6) Type "help" for help. postgres=#
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?
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.
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.
psql: FATAL: no pg_hba.conf entry for host "192.168.0.11", user "webuser", database "webapp", SSL off
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.
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: syntax error at or near "grant"
Grant is one of the PostgreSQL reserved keywords
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: cannot drop table cust because other objects depend on it
A client attempted removing the table cust that has child tables.
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: invalid input syntax for type numeric: "b" at character 26
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);
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.
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.