postgresql error connecting after moving data directory

Question

EDIT-2

I found out that the database doesn't even start after making the file location change.

This is with the default file location:

$pg_isready
/var/run/postgresql:5432 - accepting connections
$pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

pg_lsclusters output is green.

After the file location has changed on postgresql.conf:

$pg_isready
/var/run/postgresql:5432 - no response
$pg_lsclusters
Ver Cluster Port Status Owner Data directory                           Log file
9.5 main    5432 down   root  /mnt/Data/postgresdb/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

Here the output is red.

Following this post here, I tried to start the cluster manually:

$pg_ctlcluster 9.5 main start
Warning: the cluster will not be running as a systemd service. Consider using systemctl:
  sudo systemctl start postgresql@9.5-main
Error: You must run this program as the cluster owner (root) or root

I tried the same command with sudo:

Error: Config owner (postgres:124) and data owner (root:0) do not match, and config owner is not root

Which again makes me think the problem might lie with permissions of the directory. The directory is owned by root whose ownership I am unable to change.

EDIT-1

I've been working on this and I'd like to distill this post further to give more specifics. This is my current situation:

1. I installed postgres: sudo apt-get install postgresql and postgresql-contrib
2. I used sudo -U postgres psql to get into the postgres shell (I'm not sure if this is what I need to do)
3. show data_directory returns: /var/lib/postgresql/9.5/main

The data directory is located in Ubuntu ext4 formatted hard drive. I also have a 1 TB NTFS formatted hard disk mounted on /mnt/Data (which is mounted automatically on boot). What I tried:

1. Stop the postgres service: sudo systemctl stop postgresql
2. Create a new directory /mnt/Data/postgresdb and copy contents of the previous main to this which gives me a full path of /mnt/Data/postgresdb/postgresql/9.5/main using: sudo rsync -av /var/lib/postgresql/ /mnt/Data/postgresdb/postgresql/
3. Edit /etc/postgresql/9.5/main/postgresql.conf to change data_directory from the path mentioned above to /mnt/Data/postgresdb/postgresql/9.5/main
4. Start the postgres service: sudo systemctl start postgresl
5. Run sudo -U postgres psql but get the error that was mentioned in the original post.

These are the permissions on the respective main directories:

        ls -l /var/lib/postgresql/9.5/
total 4.0K drwx------ 19 postgres postgres 4.0K Jan 16 12:40 main 
    ls -l /mnt/Data/postgresdb/postgresql/9.5/
total 4.0K drwxrwxrwx 1 root root 4.0K Jan 16 12:13 main

From the looks of it, the default directory is owned by "postgres" and the new directory is owned by root. However, when I try to change ownership to postgres: chown -R postgres main, it doesn't output any error, but the ownership doesn't change. I'm curious whether this is because this drive is NTFS formatted and is mounted.

Here is my /etc/fstab:

# /etc/fstab: static file system information.
#
# Use 'blkid' to print the universally unique identifier for a
# device; this may be used with UUID= as a more robust way to name devices
# that works even if disks are added and removed. See fstab(5).
#
# <file system> <mount point>   <type>  <options>       <dump>  <pass>
# / was on /dev/sda5 during installation
UUID=3f5a9875-89a3-4ce5-b778-9d7aaf148ed6 /               ext4    errors=remount-ro 0       1
# swap was on /dev/sda6 during installation
UUID=85c3f4d4-e450-435b-8dd6-cf1b2cbd8fc2 none            swap    sw              0       0
/dev/disk/by-label/Data /mnt/Data auto nosuid,nodev,nofail,x-gvfs-show 0 0

Any ideas on how I can go about fixing this?

ORIGINAL POST

Recently, I installed Postgresql for storing some data for my research. The dataset came with instructions on how to setup the data on a Postgresql database (if interested, more info on that here and here). I installed Postgresql and set up a "role" and used the script that was provided for loading the database. It worked but I underestimated the size of the dataset and the script quit saying there was no more space.

I have two drives on my computer a 250G SSD drive with Windows and Ubuntu installed (125G each). And a 1TB HDD NTFS formatted where I store my data. So I thought moving the database to a folder on the other drive would be helpful. I purged all the data and the database to start afresh and followed the instructions here to move the database directory. However, after moving the directory, when I try to connect using psql I get the following error:

 ~ psql -U username -d postgres                                                                                                                                                                   14:48:33
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"?

How can I fix this? I am running 64-bit Ubuntu 16.04 with Postgresql-9.5. As mentioned earlier, I moved the DB directory a NTFS formatted filesystem (not sure if that cause any problems).

Thanks.

Answer 1

Try changing the ownership of data directory like this Chown postgres:postgres /mnt/Data/postgresdb/postgresql/9.5/main And chmod 750 /mnt/Data/postgresdb/postgresql/9.5/main

Answer 2

As mentioned in the comments the NTFS was the problem. I ended up resizing my bigger hard drive with 100GB formatted as ext4 and was able to launch postgres with the new data directory without any problems.