There are many queue_promotion_n tables where n is from 1 to 100.
There is an error on the 73 table with a fairly simple query
SELECT count(DISTINCT queue_id)
FROM "queue_promotion_73"
WHERE status_new > NOW() - interval '3 days';
ERROR: could not open file «base/16387/357386324.1» (target block
200005): No such file or directory
Uptime DB 23 days. How to fix it?
asked Jul 4, 2018 at 13:54
1
- Check that you have up-to-date backups (or verify that your DB replica is in sync)
- PostgreSQL wiki recommends stopping DB and
rsyncwhole all PostgreSQL files to a safe location.
File where the table is physically stored seems to be missing. You can check where PostgreSQL stores data on disk using:
SELECT pg_relation_filepath('queue_promotion_73');
pg_relation_filepath
----------------------
base/16387/357386324
(1 row)
If you are sure that your hard drives/RAID controller works fine, you can try rebuilding the table. It is a good idea to try this on a replica or backup snapshot of the database first.
VACUUM FULL queue_promotion_73;
Check again the relation path:
SELECT pg_relation_filepath('queue_promotion_73');
it should be different and hopefully with all required files.
The cause could be related to a hardware issue, make sure to check DB consistency.
answered Apr 20, 2019 at 22:05
TombartTombart
30.6k16 gold badges123 silver badges137 bronze badges
The OPs SQL doesn’t seem to include any calls to any custom procedural language functions, so this may or may not be relevant, but this error can also be caused by procedural language functions changing the current directory. See my other answer here:
https://stackoverflow.com/a/76653616/2239798
answered Jul 10 at 12:16
Как я «лечил» ERROR: could not open file »base/33264/49743»: No such file or directory
04.07.2018
Администрирование, Администрирование данных 1С
»base/33264/49743»:, could, Directory, ERROR:, file, No, not, open, or, such
После восстановления убитого жесткого диска появилась ошибка базы 1С при попытки выгрузить базу в dt: ERROR: could not open file »base/33264/49743»: No such file or directory.
В один прекрасный вечер произошла неприятная ситуация, сервер физически перестал запускаться. Сисадмин после осмотра объявил о неисправности обоих дисков из зеркального рейда. Просто как выиграть в лотерею))). Бекап базы был не очень далекий, но все-таки хотелось восстановить базу 1С полностью, чтобы не потерять даже одного дня работы. Диск отвезли в специализированную контору, и за ночь они выудили что смогли с этих дисков.
Естественно, не обошлось без потерь. На сервере стоял сервер PostgreSQL и, следовательно, меня интересовала папка из его рабочего каталога data. На новом компьютере установил postgresql с нуля. той же версии, что и стоял на упавшем сервере, с теми же настройками. Остановил службу чистоустановленного postresql , заменяю папку data в рабочем каталоге postreSQL (обычно это находится примерно там — C:Program Files (x86)PostgreSQL9.0.3-3.1C), восстановленной специалистами с битого диска. Запускаем службу PostgreSQL. У меня она не сразу запустилась. После некоторых экспериментов выяснил, что при копировании папки слетели права на нее и служба не могла ее прочитать и не стартовала из-за этого. Настроил права на каталог data — все взлетело))) Чудо, даже 1С запустился конфигуратор)))
А вот дальше ждал неприятный сюрприз. При попытке выгрузить базу в dt вылетала ошибка СУБД ERROR: could not open file »base/33264/49743»: No such file or directory. Тестирование и исправление вылетало с той же ошибкой. Видимо специалисты не все файлы таблиц postgresql восстановили.
Я решил проблему следующим образом. Сохранил структуру конфигурации в cf файл. При тестировании и исправлении по строке состояния заметил, на каком объекте падает тестирование. У меня это оказался регистр накопления. Я его удалил, обновил базу данных. а потом заменил конфигурацию базы данных на сохраненную ранее в cf. При таких действиях таблица создастся заново, но данные из нее будут потеряны.
Мне повезло, что пострадала только одна таблица, и это оказался регистр накопления. Его легко можно заполнить заново, перепроведя документы. Если бы пострадал какой нибудь справочник или документ, было бы, конечно, хуже, данные оттуда уже нельзя было бы восстановить так просто. Могла также пострадать какая-нибудь системная таблица 1С и тогда конфигурация вообще не открылась бы. Мой путь решения не идеален, но в моем случае помог восстановить базу полностью. Может, кому поможет мой опыт))
Recently we faced an issue that SQL Server was not coming up online in one of our environment.
We found below error messages in Windows application event logs:
Open failed: Could not open file E:\TempDB\tempdb.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
Unable to open the physical file "E:\TempDB\tempdb.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
CREATE DATABASE failed. Some file names listed could not be created.
As we have a clear message, while SQL Service starts, it was not able to create the TEMPDB as the mdf file is being used by another process.
When it happens, it will not start the SQL Service even we manually start it through services.msc. However, we identified, the service will come online once we restart the server.
We started our investigation verifying whether more than one instances are pointing to the same directory for TEMPDB files. Though we have multiple instances on the environment, we made it sure those instances are configured separately and properly. Once we confirmed the SQL Server configuration, we took our attention to other services especially with AntiVirus services. We found that there is Mcafee software running, but unfortunately, we could not find any reference on ProcessExplorer as well. However, we decided to stop the McAfee and see if the issue is related to the AnitVirus scan. Now, it is almost more than two weeks and we did not experience the issue.
Takeaway, if you find similar issue, Instead of disabling or stopping the services related Antivirus, we can exclude certain files associated with SQL Server from the scan.
Here are few directories that can be excluded from anti-virus scanning tool:
1. C:\Windows\System32\LogFiles
2. C:\Windows\System64\LogFiles
3. C:\windows\System32\GroupPolicy\
4. C:\windows\temp
5. C:\windows\SoftwareDistribution\Datastore
6. C:\windows\SoftwareDistribution\Datastore\Logs
7. C:\\Temporary ASP.NET Files
8. C:\Users\\AppData\Local\Temp
9. C:\Users\Default\AppData\Local\Temp
10. C:\ProgramData\Microsoft\SQL Server
11. C:\Program Files\Microsoft SQL Server
12. Exclude SQL Server Data Files (*.MDF, *.LDF, *.NDF)
13. Exclude SQL Server Backup Files (*.TRN, *.BAK)
14. Exclude Full-Text Catalog Files (*.SLS)
15. Exclude sqlmangr.exe
16. Exclude sqlservr.exe
Please give your feedback if this post helps you!!!
The machine was restarted while I was shrinking a database and since then the SQL Server (MSSQLSERVER) not starting. After reading the blogs I replaced the master.mdf and mastlog.ldf files from the templates folder with the same file, assuming master database was corrupted as I was shrinking this.
However the problem remains and the error log file gives the following message,
Starting up database 'msdb'.
Starting up database 'mssqlsystemresource'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 17207, Severity: 16, State: 1.
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.
File activation failure. The physical file name "E:\sql12_main_t.obj.x86Release\sql\mkmastr\databases\mkmastr.proj\MSDBLog.ldf" may be incorrect.
The resource database build version is 12.00.2000. This is an informational message only. No user action is required.
Starting up database 'model'.
I can clearly say that there is no E:\ partition on my machine. Can you please help me to fix the path which I assume is the error now?
I have some strange error at the end of the pg_basebackup
pg_basebackup: error: could not open file XXXX Is a directory
I wonder why there is such errors (for each of my DB backup) and if there is any issue with the backup himself (when i will try to restore)
3.3.0 Barman by EnterpriseDB (www.enterprisedb.com)
PostgreSQL 14 (but it not seems release related)
The command seems to terminate correctly BTW
`2023-02-21 00:00:46,587 [4127125] barman.backup INFO: Starting backup using postgres method for server XXXXXXXXXX in /XXX/XXXX/XXXXX/XXXXXXXXXX/base/20230221T000046
2023-02-21 00:00:46,602 [4127125] barman.backup_executor INFO: 16385, XXXXXXX, /XX/XXXXX/XXXXX
2023-02-21 00:00:46,608 [4127125] barman.backup_executor INFO: Backup start at LSN: 68/FA013138 (0000000100000068000000FA, 00013138)
2023-02-21 00:00:46,608 [4127125] barman.backup_executor INFO: Starting backup copy via pg_basebackup for 20230221T000046
2023-02-21 00:00:46,824 [4127125] barman.backup_executor INFO: pg_basebackup: initiating base backup, waiting for checkpoint to complete
2023-02-21 00:00:46,861 [4127125] barman.backup_executor INFO: pg_basebackup: checkpoint completed
2023-02-21 00:01:48,623 [4127125] barman.backup_executor INFO: NOTICE: pg_stop_backup complete, all required WAL segments have been archived
2023-02-21 00:01:48,623 [4127125] barman.backup_executor INFO: pg_basebackup: syncing data to disk …
2023-02-21 00:01:59,801 [4127125] barman.backup_executor INFO: pg_basebackup: error: could not open file «/XXX/XXXX/XXXXX/XXXXXXXXXX/base/20230221T000046/data/pg_tblspc/16385»: Is a directory
2023-02-21 00:01:59,848 [4127125] barman.backup_executor INFO: pg_basebackup: error: could not open file «/XXX/XXXX/XXXXX/XXXXXXXXXX/base/20230221T000046/data/pg_tblspc/16385»: Is a directory
2023-02-21 00:01:59,848 [4127125] barman.backup_executor INFO: pg_basebackup: base backup completed
2023-02-21 00:01:59,855 [4127125] barman.backup_executor INFO:
2023-02-21 00:01:59,856 [4127125] barman.backup_executor WARNING: pg_basebackup does not copy the PostgreSQL configuration files that reside outside PGDATA. Please manually backup the following files:
2023-02-21 00:01:59,856 [4127125] barman.backup_executor INFO: Copy done (time: 1 minute, 13 seconds)
2023-02-21 00:01:59,858 [4127125] barman.backup_executor INFO: Finalising the backup.
2023-02-21 00:01:59,886 [4127125] barman.postgres INFO: Restore point ‘barman_20230221T000046’ successfully created
2023-02-21 00:02:07,361 [4127125] barman.backup INFO: Backup size: 2.0 GiB
2023-02-21 00:02:07,362 [4127125] barman.backup INFO: Backup end at LSN: 68/FC015C48 (0000000100000068000000FC, 00015C48)
2023-02-21 00:02:07,362 [4127125] barman.backup INFO: Backup completed (start time: 2023-02-21 00:00:46.608726, elapsed time: 1 minute, 20 seconds)
2023-02-21 00:02:07,782 [4127125] barman.wal_archiver INFO: Found 2 xlog segments from streaming for XXXXXXXXXX. Archive all segments in one run.
2023-02-21 00:02:07,783 [4127125] barman.wal_archiver INFO: Archiving segment 1 of 2 from streaming: XXXXXXXXXX/0000000100000068000000FB
2023-02-21 00:02:08,528 [4127125] barman.wal_archiver INFO: Archiving segment 2 of 2 from streaming: XXXXXXXXXX/0000000100000068000000FC`