Could Not Read Block 1922 in File "Base

Sometimes PostgreSQL users get errors and warnings and they are unable to understand why. To cater to these situations, this web log will cover some mutual errors and warnings in PostgreSQL. In some cases, information technology is a user setting problem or query error, but in other cases, it tin can be a PostgreSQL bug. But, it is quite rare to be a PostgreSQL bug, and therefore information technology is really important to differentiate between user error and PostgreSQL bug. Hither is a list of some common PostgreSQL errors, with symptoms and solutions.

1 – Is the PostgreSQL Server Running Locally and Accepting?

$ psql postgres psql : mistake : 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. At that place are multiple ways to check whether the server is running or not, depending on the installation and operating organization. Here are some steps you can perform to check:

1 2 3 4 5 6 seven 8 nine 10 11 12 13 14 xv xvi 17

sudo service postgresql-12 status Redirecting to /bin/systemctl condition postgresql-12.service postgresql-12.service - PostgreSQL 12 database server Loaded : loaded (/usr/lib/systemd/system/postgresql-12.service ; disabled ; vendor preset : disabled ) Active : agile ( 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 : /organisation .slice/postgresql-12.service ├─32209 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/ ├─32211 postgres : logger ├─32213 postgres : checkpointer ├─32214 postgres : groundwork writer ├─32215 postgres : walwriter ├─32216 postgres : autovacuum launcher ├─32217 postgres : stats collector └─32218 postgres : logical replication launcher

The condition of the PostgreSQL service shows information technology 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 unlike port, then the user needs to specify the port number (with some exceptions). Hither 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 "::i" , port 5432 listening on IPv4 accost "127.0.0.one" , port 5432

2 – Initdb Cannot Exist Run equally Root

$ initdb -D data initdb : mistake : 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 employ that by root, the user which tin cause the said error. It's very uncomplicated to switch the unprivileged user using u.s. and initdb the cluster. You cannot "initdb" using superuser, so change the user that owns the server process then do initdb. Or, you can use postgresql-12-setup to initialize the cluster.

sudo ./postgresql-12-setup initdb

iii – Initdb Failed Due to Directory 'Invalid Permission'

$ initdb -D data running bootstrap script . . . FATAL : data directory "data" has invalid permissions [ 1885 ] Detail : Permissions should exist u=rwx ( 0700 ) or u=rwx , one thousand=rx ( 0750 ) . child process exited with exit lawmaking 1 initdb : removing information directory "data"

The directory should have permission u=rwx (0700) or u=rwx,thou=rx (0750) to perform initdb. Either you can initialize the cluster into another directory or alter the permission of the directory to u=rwx (0700) or u=rwx,g=rx (0750).

four – Object Permission

$ SELECT * FROM Examination ; Error : permission denied for tabular array examination [ 1788 ] STATEMENT : select * from exam ; ERROR : permission denied for tabular array exam

This error only happens when yous create some object using one user and another user does have access to that object.

5 – Out of Disk Space Error

Fault : could non extend file "base/30122/331821" : No space left on device HINT : Check gratis disk space .

PostgreSQL initializes its cluster into $PGDATA. It is very important to keep an eye on that directory and gratis up some space earlier that drive runs out of it. There are some ways to optimize the situation, like:

• Complimentary some space on the disk
• Point pg_wal to another deejay, and in that example, all walls will generate to another disk, and data is distributed among multiple disks.
• Create a tablespace on another disk and create a tabular array in that tablespace. You tin split up your data betwixt multiple disks.

6 – Replication Standby Issue

FATAL : database organisation identifier differs between the primary and standby [ 20595 ] Detail : The master 's identifier is 6832398539310516067, the standby' s identifier is 6832397799517112074.

You are replicating a server to some other which is not a copy of the original. You lot need to configure main replication and tabular array a base of operations backup using pg_basebackup and commencement the slave.

7 – The Server Terminated

server airtight the connection unexpectedly This probably ways the server terminated abnormally before or while processing the asking . The connectedness to the server was lost . Attempting reset : Failed

This ane is a disquisitional error, and in that case, you need to identify the cause. If possible, generate the stack trace and consult your service provider.

eight – Out of Retentivity Upshot

Mistake : 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 retention left, PostgreSQL's retention allocation functions start failing. Delight cheque your configuration and limitations of your hardware.

9 – OOM Killer

Out of memory : Kill procedure 1766 ( postmaster ) score 890 or cede child Killed process 1766 , UID 26 , ( postmaster ) total-vm : 24384508 kB , anon-rss : 14376288kB , file-rss : 138616kB

When there is non plenty memory left, the OOM killer invokes and kills the PostgreSQL process. Some people disable that process, but it is not recommended. Yous need to check the memory setting according to your hardware.

ten – Replication Archive Command Failure

cp : cannot create regular file '/usr/local/wal/000000010000000000000001' : No such file or directory LOG : archive command failed with exit code one 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 be. You need to create /usr/local/wal/ file or specify the directory which does be.

---

Our white paper "Why Choose PostgreSQL?" looks at the features and benefits of PostgreSQL and presents some applied usage examples. Nosotros too examine how PostgreSQL can be useful for companies looking to migrate from Oracle.

Download PDF

monteroyarstions.blogspot.com

Source: https://www.percona.com/blog/2020/06/05/10-common-postgresql-errors/

Share this post