We have faced different levels of corruption related to databases in PostgreSQL. Our colleague has written multiple blogs on the subject; please refer to the below links for more:
- The Ultimate Guide to Database Corruption: Part 1 – An Overview
- The Ultimate Guide to Database Corruption: Part 2 – B-Tree Index Corruption
In this blog, we will be discussing the scenario where a data file related to a table goes missing, maybe due to OS (hardware problem) or due to human interruption, which causes the deletion of some data file unintentionally at the OS level. Though it is not at all recommended to touch the /data/base/ directory and go through files under this /var/lib/postgresql/14/main/base/, however, sometimes it happens.
Our current database was running fine with the below structure:
1 2 3 4 5 6 7 8 9 10 | List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+---------+---------+-----------------------+---------+------------+-------------------------------------------- percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 9561 kB | pg_default | postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 8553 kB | pg_default | default administrative connection database template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 8401 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 8553 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (4 rows) |
Somehow we are getting the below error message in PostgreSQL logs:
1 2 3 4 5 6 7 8 | 2023-06-14 09:58:06.408 UTC [4056] LOG: listening on IPv4 address "127.0.0.1", port 5432 2023-06-14 09:58:06.412 UTC [4056] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2023-06-14 09:58:06.423 UTC [4057] LOG: database system was shut down at 2023-06-14 09:58:04 UTC 2023-06-14 09:58:06.432 UTC [4056] LOG: database system is ready to accept connections 2023-06-16 10:00:58.130 UTC [35062] postgres@percona ERROR: could not open file "base/16384/16391": No such file or directory 2023-06-16 10:00:58.130 UTC [35062] postgres@percona STATEMENT: select * from test limit 1; 2023-06-16 10:01:59.191 UTC [35224] postgres@percona ERROR: could not open file "base/16384/16391": No such file or directory 2023-06-16 10:01:59.191 UTC [35224] postgres@percona STATEMENT: select * from test limit 1; |
Upon checking, we found it was due to one file ( base/16384/16391) being removed. So we need to check whether this base/16384/16391 file is available in /base location.
1 2 | postgres@ip-172-xx-xx-xx:~/14/main$ ls -l base/16384/16391 ls: cannot access 'base/16384/16391': No such file or directory |
Also, we can check at the DB level by using this SQL Query:
1 2 3 4 5 6 | percona=# SELECT relid, relname FROM pg_catalog.pg_statio_user_tables WHERE relid = '16391'; relid | relname -------+--------- 16391 | test (1 row) |
From the above, we have identified that the file for the table “test” with relid 16391 got deleted. We need to identify whether it was deleted manually by mistake or was due to hardware failure.
In case of hardware failure, first, we need to fix the hardware issue or migrate our database to new hardware and then perform a restore, as mentioned below.
To restore, we can follow either of below approaches:
- If the standby server is there and the issue does not persist on that, then we can fix the issue by taking logical backup from the standby server, dropping the table on primary, and recreating it using the backup taken.
- Perform PITR if we have backup configured with proper archive backup.
- Restore database “percona” if we have proper backup configured using pgBackRest backup.
- If we have a table-level logical backup, then we can restore the “test” table backup, and our database will be in good shape.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
Abdul Sayeed co-authored this article.
