Join Log in
Score:0
FRALEWHALE avatar Ubuntu

Error connecting to postgressql after moving to new drive

cn flag
FRALEWHALE

Real head scratcher today that I am hoping someone might be able to help out with. I have an Ubuntu 22.04 LTS server and I am trying to install and configure postgressql on the server. The intent is for it to be used as an archive for older, larger DBs that don't need to be quickly accessed.

We built the server with two disks (virtual disks) one smaller one for the OS and then a second (2TB drive) for the postgres DBs to be stored. I was able to add the second disk and then format it to NTFS and then mount it to /mnt/Postgres_DB_Archive_MP/. I am not 100% certain I did it properly, so that might be apart of the issues I am running into. I used this guide on how to add the disk via Cockpit

Anyways, I followed this guide to install and configure postgres. I was able to switch to the postgres user, connect, create a new user and a new DB all without issues. However this had the data directory being stored on the OS drive.

So then I followed this guide to migrate the data over to the larger drive. I was able to follow the steps without issue, however when I go to switch to the postgres user and then run psql I get the following error:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

Which then led me on a number of hunts to try and find out what it might be. I used this thread to get me to the /var/log/postgresql/postgresql/14/main.log file which didn't have any useful information in it other then error code 1 and that the DB was down. Here is the full output:

2022-11-02 15:52:10.035 EDT [3875] LOG:  starting PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on x86_64-p
2022-11-02 15:52:10.035 EDT [3875] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-11-02 15:52:10.038 EDT [3875] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-11-02 15:52:10.047 EDT [3876] LOG:  database system was shut down at 2022-11-02 15:52:07 EDT
2022-11-02 15:52:10.056 EDT [3875] LOG:  database system is ready to accept connections
2022-11-02 15:55:08.792 EDT [3875] LOG:  received fast shutdown request
2022-11-02 15:55:08.797 EDT [3875] LOG:  aborting any active transactions
2022-11-02 15:55:08.801 EDT [3875] LOG:  background worker "logical replication launcher" (PID 3882) exited
2022-11-02 15:55:08.801 EDT [3877] LOG:  shutting down
2022-11-02 15:55:08.838 EDT [3875] LOG:  database system is shut down

Then I tried using pg_lsclusters to start the database, and the command appeared to run successfully however the DB still showed a status of down.

Ver Cluster Port Status Owner Data directory              Log file
14  main    5432 down   root  /mnt/Postgres_DB_Archive_MP /var/log/postgresql/postgresql-14-main.log

Any ideas what I am doing wrong? Or what I can do to just install postgres on the secondary drive in the first place? Since posting this thread, I have completely removed postgres and plan on starting fresh using any advice I can find.

90
0 + 3
in flag
matigo
Silly question, but why use NTFS? That’s a Microsoft file system, which will result in some very different directory and file permissions being implemented. Databases really don’t like it when resources are not correctly owned. As for the database itself, was the original system running the exact same version? If not, an update may be needed. Databases cannot run on older engines, though, as there is no backwards compatibility contingencies. Have you checked `/var/log` for more specific error messages?
0
Reply
FRALEWHALE avatar
cn flag
FRALEWHALE
@matigo not a silly question at all. We used NTFS because all of our other systems are Windows (primarily a windows shop) and some documentation suggested to use NTFS for better compatibility - although now I am questioning that logic. Not sure what the original system was running, I was going to check that once I got the server migrated to the new disk. However our DBAs were able to move files over when I had it on the OS drive, so I am assuming `psql 14`. Yes I have checked `var/log/` it is in my OP. Nothing really helpful.
0
Reply
in flag
matigo
If the server is to run a Linux-based system, then NTFS will do more harm than good when it comes to database operation. You’ll find it slower and more prone to occasional I/O hiccups than is warranted. Ext4 (or ZFS) would be better options. Alternatively, there are PostgreSQL Server installations for Windows if that is what everyone is more familiar with
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.