Sample Header Ad - 728x90

Why is my Postgres database working for a while and then not able to “start server” once restarted?

1 vote
1 answer
965 views
Recently, I've started playing around with an old Raspberry Pi 3 b+, and I thought it would be good practice to host a Postgres database on my local network and use it for whatever I want to work through. I understand that running Postgres on a Raspberry Pi with 1GB of memory is not ideal and can take a toll on the SDcard, but I've updated the postgresql.conf file and specified that the data directory path is to utilize a 1TB SSD. Additionally, I've installed zram and log2ram to try and curb some of the overhead on SDcard. Overview of tech I'm working with: - Raspberry Pi 4 B - Postgres 12 - Ubuntu server 20.04 (no gui, only working from terminal) - 1TB SSD Yesterday, I was writing to the Postgres db from a python notebook without any issue, but once I restarted the Raspberry Pi, I was unable to reach the db from DataGrip and would receive the following error from my terminal in Ubuntu:
psql: error: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
I checked the status of the postgres server and that seemed to be alright...:
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2021-01-28 13:34:41 UTC; 20min ago
    Process: 1895 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1895 (code=exited, status=0/SUCCESS)

Jan 28 13:34:41 ubuntu systemd: Starting PostgreSQL RDBMS...
Jan 28 13:34:41 ubuntu systemd: Finished PostgreSQL RDBMS.
This is what is provided in the postgresql-12-main.log:
2021-01-28 13:17:23.344 UTC  LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-01-28 13:17:23.362 UTC  LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-01-28 13:17:23.362 UTC  LOG:  listening on IPv6 address "::", port 5432
2021-01-28 13:17:23.365 UTC  LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-01-28 13:17:23.664 UTC  LOG:  database system was shut down at 2021-01-28 01:43:38 UTC
2021-01-28 13:17:24.619 UTC  LOG:  could not link file "pg_wal/xlogtemp.1899" to "pg_wal/000000010000000000000002": Operation not permitted
2021-01-28 13:17:24.670 UTC  FATAL:  could not open file "pg_wal/000000010000000000000002": No such file or directory
2021-01-28 13:17:24.685 UTC  LOG:  startup process (PID 1899) exited with exit code 1
2021-01-28 13:17:24.686 UTC  LOG:  aborting startup due to startup process failure
2021-01-28 13:17:24.708 UTC  LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.
I think it could have something to do with permissions. If I try uploading a ~30MB csv to the postgres db, it will crash and the server will not start again because files cannot be written to the pg_wal directory. The only file in that directory is 000000010000000000000001 and does not move on to 000000010000000000000002 etc. while writing to a table. Please let me know if you have any questions or if you would like for me to include any additional information. I appreciate any pointers you may have for head ahead of time.
Asked by IamTrying (11 rep)
Feb 5, 2021, 02:44 PM
Last activity: Mar 18, 2021, 12:44 AM