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(1): Starting PostgreSQL RDBMS...
Jan 28 13:34:41 ubuntu systemd(1): Finished PostgreSQL RDBMS.
This is what is provided in the postgresql-12-main.log:
2021-01-28 13:17:23.344 UTC (1889) 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 (1889) LOG: listening on IPv4 address "0.0.0.0", port 5432
2021-01-28 13:17:23.362 UTC (1889) LOG: listening on IPv6 address "::", port 5432
2021-01-28 13:17:23.365 UTC (1889) LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-01-28 13:17:23.664 UTC (1899) LOG: database system was shut down at 2021-01-28 01:43:38 UTC
2021-01-28 13:17:24.619 UTC (1899) LOG: could not link file "pg_wal/xlogtemp.1899" to "pg_wal/000000010000000000000002": Operation not permitted
2021-01-28 13:17:24.670 UTC (1899) FATAL: could not open file "pg_wal/000000010000000000000002": No such file or directory
2021-01-28 13:17:24.685 UTC (1889) LOG: startup process (PID 1899) exited with exit code 1
2021-01-28 13:17:24.686 UTC (1889) LOG: aborting startup due to startup process failure
2021-01-28 13:17:24.708 UTC (1889) 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.