4

I've been working on a postgres database, with my data directory on my 8TB external USB drive. Occasionally, I need to restart the postgres instance to change configuration settings, but for some reason I can never get the service to restart.

  • Everything works fine in Windows 7
  • Can't restart postgres in Windows 10
  • Data directory in D:\postgresdata\
  • Database is empty at the moment, just the default postgres instance

  • When windows starts, the database engine starts automatically, and functions correctly

  • Running "show data_directory" returns D:/postgresdata/
  • Opening services.msc and restarting my postgres service starts and stops, never successfully starting.
  • Running pg_ctl.exe start -D D:\postgresdata fails to start with these errors:

FATAL: could not access status of transaction 0 DETAIL: Could not open file "pg_notify/0000": Invalid argument.

Things I've tried:

  • I've tested the same scenario on my C: drive, and it always starts and restarts correctly.
  • I've run chkdsk on the D: drive, nothing is wrong with it.
  • I've played around with permissions on D: and nothing makes any difference.
  • I've made a second partition from my C: drive and verified that a data directory on it always starts and restarts correctly.
  • Initializing a new database with pg_ctl init -D D:\someotherpath\ on the D: drive does not work either, same error as restarting in general.

I'm at a loss for what else to try. The weirdest thing is how the system always starts up and works at system boot time, but not in any other circumstance.

Update: I attempted to run postgres against a USB thumb drive and the error is the same.

migrated from stackoverflow.com Oct 17 '18 at 8:48

This question came from our site for professional and enthusiast programmers.

  • Did you try ejecting and reinserting the usb between stop and start of postgresql? – Eelke Oct 6 '18 at 6:08
  • Yes, unplugging and plugging it back in had no effect – Rob G Oct 8 '18 at 18:47
  • 1
    Tried it my self with pg10 (bigsql installer) on sandisk usb3 stick. Have no windows service registered just used pg_ctl for starting and stopping works fine. When you stop postgres is the last log line database system is shut down? – Eelke Oct 10 '18 at 16:57
  • 1
    When you manually ran pg_ctl.exe did you use the same user account as the one that runs the postgres service? – Eelke Oct 10 '18 at 17:07
  • environment path? – Slumdog Oct 11 '18 at 0:35
2

What type of partitioning scheme and USB chipset (USB-sata bridge) does the drive use? There are all sorts of unexpected limits you can still hit on a modern USB drive with certain partition types/sizes and chipsets. I've run into issues with 2TB limits imposed by some USB drive controllers. You can format a partition larger than that but when you try to read/write to a higher block number you get errors.

You can also hit limits imposed by the older MSDOS partition scheme which can be overcome by reformatting as GPT (obviously move/backup your data first).

There's a more indepth explanation here: here and here

So your solution might simply be to buy a more modern/high-end external enclosure.

Another possibility is you're hitting some sort of bug related to writeback caching. You could try adjusting the drive cache settings as described in this article

The fact it works in win7 but not win10 could also indicate a bad driver but not sure if there's anything you can do about that other than check the manufacturers website.

You can test all of the above theories by trying to write some really big files to the drive and then testing the files for corruption, one way to do that is to record a giant MPEG with your webcam or VLC screen capture. To be a useful test the files need to be really big (like 2TB+)

0

Maybe the permissions are right, but the drive may be in a read-only mode or the postgresql data folder may be just read-only.

  • I double checked that none of the files in the folder are checked as 'Read Only', and I've even tried assigning all permissions to all users, not only the postgres user. One weird thing that I think points against that is that the engine starts fine when the computer first boots, but won't start again after windows has been running at all. – Rob G Oct 10 '18 at 16:29
  • 1
    After the boot, does it point to the same data directory? When the computer boots, just check the process and what data dir is it using. – Mahesh H Viraktamath Oct 11 '18 at 9:11
0

From a security software perspective, removable drives may be subject to more aggressive heuristic/behavioral scanning than traditional fixed drives.

You need to put in place anti-virus/anti-malware exclusions on the Postgres data and binaries directories/executable, including the built-in Windows Defender

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.