10 Common PostgreSQL Errors

栏目: IT技术 · 发布时间: 4年前

内容简介: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 installa

10 Common PostgreSQL Errors 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?

$ 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:

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:

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

$ 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.

sudo ./postgresql-12-setup initdb

3 – Initdb Failed Due to Directory ‘Invalid Permission’

$ 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

$ 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

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

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

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

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

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 A rchive Command Failure

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.


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

HTML5与CSS3基础教程(第7版)

HTML5与CSS3基础教程(第7版)

[美] Elizabeth Castro、[美] Bruce Hyslop / 望以文 / 人民邮电出版社 / 2013-1 / 59.00元

代表下一代网页编写技术的HTML5,为网页提供布局和格式的CSS3,这两者构成了Web开发的基石,也是Web程序员和设计师必须熟练掌握的最基本技能。 本书是风靡全球的HTML和CSS最佳入门教程的最新版,上一版单单英文版的销量就超过100万册,被翻译为十多种语言,并长期雄踞亚马逊书店计算机图书排行榜榜首。 最新的第7版秉承前一版直观、透彻、全面、循序渐进的讲授特色,仍然采用独特的双栏图......一起来看看 《HTML5与CSS3基础教程(第7版)》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

在线进制转换器
在线进制转换器

各进制数互转换器

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具