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

Question

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 3 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.

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.

Answer 1

config file (partly):

---

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
                                        # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = ''                 # write an extra PID file
                                        # (change requires restart)

---

/etc/init.d/postgresql (partly):

NOTE: this is from a non-standard installation. YMMV

---

# Data directory
#PGDATA="/data/db/postgres"
#PGDATA="/data/db/postgres/pgdata"
#PGDATA="/data/db/postgres-12/pgdata"

PGDATA="/data/db/postgres-11/pgdata"

---

(when upgrading, I tend to keep the commented-out older setting for reference)

Note: the config-file is not edited, every path refers to the ConfigDir (by default)

Additionally, for Postgres on a Pi, I set:

---

random_page_cost = 1.1 
shared_buffers = 128MB
#work_mem = 4MB # keep the low default
effective_cache_size = 3GB # This is for a RaspberryPi-4
                           # for a Pi-3, I'd use ~700M

Answer 2

This is what the /etc/init.d/postgres file looks like:::

#!/bin/sh
set -e

### BEGIN INIT INFO
# Provides:             postgresql
# Required-Start:       $local_fs $remote_fs $network $time
# Required-Stop:        $local_fs $remote_fs $network $time
# Should-Start:         $syslog
# Should-Stop:          $syslog
# Default-Start:        2 3 4 5
# Default-Stop:         0 1 6
# Short-Description:    PostgreSQL RDBMS server
### END INIT INFO


# Setting environment variables for the postmaster here does not work; please
# set them in /etc/postgresql/<version>/<cluster>/environment instead.

[ -r /usr/share/postgresql-common/init.d-functions ] || exit 0

. /usr/share/postgresql-common/init.d-functions

# versions can be specified explicitly
if [ -n "$2" ]; then
    versions="$2 $3 $4 $5 $6 $7 $8 $9"
else
    get_versions
fi

case "$1" in
    start|stop|restart|reload)
        if [ "$1" = "start" ]; then
            create_socket_directory
        fi
        if [ -z "`pg_lsclusters -h`" ]; then
            log_warning_msg 'No PostgreSQL clusters exist; see "man pg_createcluster"'
            exit 0
        fi
        for v in $versions; do
            $1 $v || EXIT=$?
        done
        exit ${EXIT:-0}
        ;;
    status)
        LS=`pg_lsclusters -h`
        # no clusters -> unknown status
        [ -n "$LS" ] || exit 4
        echo "$LS" | awk 'BEGIN {rc=0} {if (match($4, "down")) rc=3; printf ("%s/%s (port %s): %s\n", $1, $2, $3, $4)}; END {exit rc}'
        ;;
    force-reload)
        for v in $versions; do
            reload $v
        done
        ;;
    *)
        echo "Usage: $0 {start|stop|restart|reload|force-reload|status} [version ..]"
        exit 1
        ;;
esac

exit 0

Answer 3

Okay, I think I've figured it out. Might be overkill but it works:

1. 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".

2. 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:

sudo apt install postgresql postgresql-contrib

3. Third, I followed this walkthrough until the end of step two, but before beginning step 2, I added a symbolic link from /var/lib/postgresql/12/main to /YOUR/MOUNT/POSITION/postgresql/12/main by executing:

ln -s /var/lib/postgresql/12/main /YOUR/MOUNT/POSITION/postgresql/12/main

4. Lastly, before restarting the postgres server, I used this website to help me better configure my server. Enter your specs and it should give you some useful configuration settings.

If I remember anything I've left out I'll try and come back and edit this post. Otherwise comment if anything doesn't make sense or is unclear.