The End Goal

To somehow combat the issues listed so I can transfer stored data from the old database node (that has an improper postgres mount) to the new one that is currently bringing in new data (that is properly set up) (or to magically wave fairy dust on the old database and hope that it fixes itself)

Summary of the Issues:

I have a Ubuntu 16.04 database node that fails into initramfs cli when rebooting; the node will try to reach a UUID of a device that is not specified in /etc/fstab or /boot/grub/grub.cfg, and will incur the errors CREATE group disk not found and Gave up waiting for root device. It cannot find the device that is mounted on /, and that device's UUID is what's specified in /etc/fstab and /boot/grub/grub.cfg where (to my knowledge) it should be grabbing from.

The lead SRE was able to get the database node stood up on a temporary drive using the second harddisk that was already on the node, and I was able to create a separate database node that has postgres set up and actively bringing in new data, however I still need to get the data off the old database node.

  • The postgres database on the old node is mounted on / directory, which is the directory having issues. The data seems to be under /var/lib/postgresql/9.5/main/base instead of the standard /var/lib/pg_data
  • I cannot use psql on the old node to view the tables or export data because the .s.PGSQL.5432 socket and .s.PGSQL.5432 lock are missing from /var/run/postgresql as shown by this psql error. There are no active connections for postgres right now and I highly believe that's due to the socket and lock missing
  • I cannot add a new storage disk to the old node; using echo "- - -" > /sys/class/scsi_host/host0/scan and cycling between host0, host1, host2 does not update any storage devices on the node, and rebooting just sends the node back into the initramfs cli where the / mounted directory device is not found.
  • There are no backups or disaster recovery nodes to support this database node; the only thing keeping the data on this node alive is a snapshot (with memory) on vsphere to fall back to if something goes wrong with the node. Hindsight 20/20 (and 2020 was a trash year) I wish I had done a snapshot with memory in the first place before this all went down, but I had been informed from past jobs that the best practice is not to snapshot memory

What I've Tried:

  • Tried adding a new storage device, but using echo "- - -" > /sys/class/scsi_host/host0/scan shenanigans doesn't update the device list
  • Tested a purge and reinstall on another test node to see if it would bring back the .s.PGSQL.5432 socket (as recommended by other questions on this site); purge and reinstall will wipe the data and tables, and there's no pg_data mount that separates the data from the main files being blown away. I've read about some solutions on this site around recreating the socket file (and sometimes the pid) that involve the purge remove, but iirc those mostly mention that pg_data is mounted and won't be touched as the other postgres directories are blown away.
  • Tried recreating the .s.PGSQL.5432 socket manually so the node can use postgres connections, but haven't found a good way to actually make it a socket. I might be able to make a lock file but I won't even be able to test if it works without /var/run/postgresql/.s.PGSQL.5432 in place
  • Also was going to try linking /tmp to /var/run/postgresql as some solutions suggested (for when the socket exists in /tmp) but the socket doesn't exist in /tmp either
  • I'm not sure if I'd be able to separate out the data under /var/lib/postgresql/9.5/main/base since there aren't any other drives currently big enough to hold that info, and as stated before I'm not able to add additional drives or extensions to existing drives unless I am able to reboot without landing in initramfs cli land again, and repartitioning via cfdisk doesn't seem to apply without needing to reboot the node
  • You have to start the database server, then it will create a socket in the directory (or directories) indicated by the unix_socket_directories parameter in postgresql.conf. Aug 24, 2022 at 8:00
  • Unfortunately starting the database server does not solve the issue. The postgresql service is hosted on the node that I'm having issues with, starting and stopping that service on the node doesn't create that new socket. The postgresql service will be in an active state, but the postgresql@9.5-main service enters into a failed state. The reason it lists is that some data is likely corrupted and it is trying to automatically recover, but it's been trying to recover for about a week by now Aug 25, 2022 at 14:59
  • Try to start PostgreSQL manually with pg_ctl (see the documentation) and look at the error messages. Look into the log file. If you have data corruption or you cannot figure out the problem easily, hire a PostgreSQL expert. Aug 25, 2022 at 15:17
  • If the filesystem containing the Postgres data directory is unavailable (which I understand to be the case, but your post is quite confusing, so I might be wrong), there's no point in starting Postgres as it won't serve you the missing data.
    – mustaccio
    Aug 25, 2022 at 17:58
  • I apologize for the confusion of the post, it's difficult for me to determine what precisely is the issue regarding the database so I tried listing the issues I'm experiencing that are likely blocking me from saving as much data as I can. The directory is present (under /var/lib/postgresql/9.5/main/base) but I can't use psql to get to it because of the errors (logs say likely corrupted). I've also tried pg_ctlcluster commands to start up the postgres instance, but no luck there. I'm aware the data may be non-savable but have been exhausting as many resources to save as much of it as I can Aug 25, 2022 at 18:27


Your Answer

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

Browse other questions tagged or ask your own question.