6

What To Do When a Data File in PostgreSQL Goes Missing

 1 year ago
source link: https://www.percona.com/blog/what-to-do-when-a-data-file-in-postgresql-goes-missing/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

What To Do When a Data File in PostgreSQL Goes Missing

June 26, 2023

Abhishek Deb

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:

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:

                        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:

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.

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:

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:

  1. 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.
  2. Perform PITR if we have backup configured with proper archive backup.
  3. Restore database “percona” if we have proper backup configured using pgBackRest backup.
  4. 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!

Share This Post!

Subscribe
Connect with
guest
Label
0 Comments

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK