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.
Best Answer
Okay, I think I've figured it out. Might be overkill but it works:
First thing I did was format and mount my 1TB SSD. Here is a good video for a walkthrough for formatting to ext4 and mounting. The difference between the video is that I've updated the fstab file to check my SSD during bootup or "0 2" at the end of the SSD mount options instead of "0 0".
Secondly, I installed Postgres. Here is a good walkthrough for that. The directions provided in that blog were more than I needed, but a good walkthrough nonetheless. I simply installed Postgres with:
/var/lib/postgresql/12/mainto/YOUR/MOUNT/POSITION/postgresql/12/mainby executing: