This is my current setup on my home laptop:

  • OSX El Capitan.
  • Installed postgresql 9.6 via postgres.app.
  • Symlinked the database files to an external HD (connected via USB).
  • Inserting data via the psycopg2 python module in a jupyter notebook.

I have a long list of files to insert to various tables. I've made sure to turn off logging and there are no indexes assigned to any of the tables. There's plenty of space on the external HD.

Eventually the upload would hang at some particular file. I would be able to select from other tables in psql except for the table that file was being uploaded to. Examined tables pg_prepared_xacts and pg_locks and nothing's there. Attempting to drop the corrupted table would just sit there. However, I wouldn't be able to do a clean shutdown of the server on postgres.app due to the error pg_ctl: server does not shut down, even though trying to access via the psql shows that the server's shut off. I would try to restart the laptop, and it'd just sort of freeze with all the applications closed. After doing a hard reboot, postgres.app would complain about not being able to start the server due to some fatal error.

At a complete loss as to what's going on. Any clues?

Edit: I've tried killing the active COPY process, which reads from STDIN to no avail via:

  • SELECT pg_cancel_backend(__pid__);
  • SELECT pg_terminate_backend(__pid__);
  • kill __pid__

Console.app not showing anything in logs. When I force a shutdown via postgres.app this is what shows up in the logs:

LOG:  received fast shutdown request
LOG:  aborting any active transactions
FATAL:  terminating autovacuum process due to administrator command
LOG:  autovacuum launcher shutting down
FATAL:  terminating connection due to administrator command
FATAL:  terminating autovacuum process due to administrator command
FATAL:  the database system is shutting down

Turns out the issue was the format of the external HD. It was in NTFS, which was accessed via Paragon NTFS, and for whatever reason the connection would simply screw up after a few hours. I reformatted the external HD into Mac OS Extended (Journaled) format and all problems went away.

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