0

macOS Sonoma 14.2.1 (23C71), just updated to 14.5 (23F79)

Story

I had a python script (norgis ALKIS-Import) importing a lot of Data into my Postgres Database over the weekend. When I came back today the database was still running but I couldn't access it. I got this error message:"base/5/2703": No such file or directory. After restarting my Mac it wouldn't launch anymore.

Backups

I have a Directly Attached Storage with RAID 10 where I do daily and monthly backups using a rsync shell script and LaunchAgents. It just copies the var-16 folder over to the big storage. I tried launching every single daily backup (7) and the monthly backup and they all have the same error. I also had a backup which was a few weeks old but I get the same error. My Mind just can't explain why the heck all of my backups are just damaged without me touching any of them.

What I've tried

  • I created a new Postgres Server on the same Mac and it runs perfectly fine.
  • I ran the Server on another Mac (it's stored on an external ssd) but still got the same error message.

This is the log output from the postgresql.log file after launching the server.

2024-07-22 14:29:58.728 CEST [9382] LOG:  listening on IPv6 address "::", port 5432
2024-07-22 14:29:58.728 CEST [9382] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-07-22 14:29:58.729 CEST [9382] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-07-22 14:29:58.744 CEST [9382] LOG:  could not open directory "pg_tblspc/.DS_Store/PG_16_202307071/pgsql_tmp": Not a directory
2024-07-22 14:29:58.744 CEST [9382] LOG:  could not open directory "pg_tblspc/.DS_Store/PG_16_202307071": Not a directory
2024-07-22 14:29:58.745 CEST [9385] LOG:  database system was interrupted while in recovery at 2024-07-22 14:23:35 CEST
2024-07-22 14:29:58.745 CEST [9385] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2024-07-22 14:30:03.952 CEST [9385] LOG:  database system was not properly shut down; automatic recovery in progress
2024-07-22 14:30:03.966 CEST [9385] FATAL:  could not open directory "pg_tblspc/.DS_Store/PG_16_202307071": Not a directory
2024-07-22 14:30:03.967 CEST [9382] LOG:  startup process (PID 9385) exited with exit code 1
2024-07-22 14:30:03.967 CEST [9382] LOG:  aborting startup due to startup process failure
2024-07-22 14:30:03.967 CEST [9382] LOG:  database system is shut down

I also don't understand why it wants do something in a .DS_Store directory. Aren't these made and used by macOS?

Edit

If anyone knows why all of my backups got damaged as well please answer that. Where did the .DS_Stores come from and why did postgres try to do something with the .DS_Store files? Thank you!

Improve this question

2 Answers 2

Reset to default
1

I do daily and monthly backups using a rsync shell script and LaunchAgents.

Please, please, please tell me that you shut your database down (or put it into "backup mode") when taking such "Backups". If you don't, then anything you copy from under your running database might well prove utterly useless.

Database != Files

Remember that most of what PostgreSQL does happens in the memory of a running process. Your Data only gets written out to disk "some time" later and can be written to any part of the data files. If your rsync is halfway through reading a file and PostgreSQL chooses to write data into the start of that file, then your "backup" will be missing that data.

Recommendation: Use Database Tools for Database Jobs.

Improve this answer
5
  • Damn... My script doesn't shut the database down. I set it to make the backup at 3 AM so that no one would be writing to the database while it's doing the backup. I use Navicat Premium but I didn't want to do a pg_dump of every single database (almost 20) because it's too many backups. But I guess that would be the better alternative, right? I need to schedule to do a backup once a day of every database in the Server.
    – QGuy
    Commented Jul 22 at 14:30
  • 1
    Doing so would give you recoverable Backups, which you currently DO NOT HAVE. After a clean shutdown is the ONLY time you should think about your databases having files. Putting the databases into "backup mode" to take your backups is the next best thing (and commonly used). This tells PostgreSQL to stop writing /anything/ to disk, but that includes the WAL files, so your database is effectively "hung" as far as changes are concerned, until you release it again.
    – Phill W.
    Commented Jul 22 at 14:38
  • I have my database on a 2tb external ssd and the backups go onto a 6tb RAID DAS. Can I also do that with the backup mode?
    – QGuy
    Commented Jul 22 at 14:52
  • 1
    And should I use pg_dump or pg_basebackup?
    – QGuy
    Commented Jul 22 at 14:59
  • Start with the Documentation for the basics: dev.mysql.com/doc/refman/8.4/en/backup-and-recovery.html Then, ALWAYS think about Recovery first - Backups are just a /tool/ to support Recovery (otherwise, your Backups have almost no purpose at all!)
    – Phill W.
    Commented Jul 23 at 12:48
0

So in my log there was an error with some directories/files in the pg_tblspc directory:

2024-07-22 14:29:58.744 CEST [9382] LOG:  could not open directory "pg_tblspc/.DS_Store/PG_16_202307071/pgsql_tmp": Not a directory
2024-07-22 14:29:58.744 CEST [9382] LOG:  could not open directory "pg_tblspc/.DS_Store/PG_16_202307071": Not a directory

I just copied this folder from a working server and replaced it in the faulty one. I think this happened because of some .DS_Store files created by macOS in that folder.

Now it starts up and works again!! :)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .