How To Transfer Postgres Data That Doesn't Have a .s.PGSQL.5432 Socket or /var/lib/pg_data Mount?

Question

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