'Could not connect to server when PostgreSQL at new storage location
I am trying to change the data directory of my PostgreSQL db on my Raspberry pi 4.
The steps I performed are:
Installing PostgreSQL:
sudo apt install postgresql
Stopping service:
sudo service postgresql stop
Move database files (which I tryed in two ways)
sudo mv /var/lib/postgresql/11/main /mnt/storage/postgres
OR
sudo rsync -av /var/lib/postgresql/11/main /mnt/storage/postgres
Adapting postgresql.conf
data_directory = '/mnt/storage/postgres'
listen_addresses = '*'
Starting service:
sudo service postgresql start
With the command "psql" I receive the following output:
psql: 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"?
Additional Information:
$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2022-02-04 17:12:29 CET; 31s ago
Process: 21169 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 21169 (code=exited, status=0/SUCCESS)
Feb 04 17:12:29 MyPi systemd[1]: Starting PostgreSQL RDBMS...
Feb 04 17:12:29 MyPi systemd[1]: Started PostgreSQL RDBMS.
$cat /etc/os-release
PRETTY_NAME="Raspbian GNU/Linux 10 (buster)"
NAME="Raspbian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=raspbian
ID_LIKE=debian
HOME_URL="http://www.raspbian.org/"
SUPPORT_URL="http://www.raspbian.org/RaspbianForums"
BUG_REPORT_URL="http://www.raspbian.org/RaspbianBugs"
$ psql -V
psql (PostgreSQL) 11.14 (Raspbian 11.14-0+deb10u1)
$ findmnt
...
/mnt/storage /dev/sda1 fuseblk rw,relatime,user_id=0,group_id=0,default_permissions,allow_other,blksize=4096
...
Update:
$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
11 main 5432 down root /mnt/storage/postgres/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
The location I want to use for the database is an external drive. I played also around with permission and ownership of the mounted external drive. One issue I am experiencing here is that the permissions always return to their initial state after I changed them.
$ cat /etc/fstab
...
PARTUUID=96082d9d-0e40-4ac9-821b-78d65ae2c3f0 /mnt/storage ntfs-3g auto,users,permissions 0 0
...
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
