The CKAN postgres database might take a lot of spaces in the Linux system. Today I notice the disk of our CKAN system is almost full. So I have to move the entire databse to an external disk. As you know, any modification of database could be dangerous. It takes me whole afternoon to deal with and I recorded the key steps of the migration. Here we go through how to migrate the CKAN postgres database in Ubuntu 16.04.
Preparation
Check current database location, usually located at /var/lib/postgresql by default:
sudo -u postgres psql
SHOW data_directory;
\q
Stop CKAN and postgres service:
service apache2 stop
service nginx stop
service postgresql stop
Copy data from original location to target location:
cp -R /var/lib/postgresql NEW_LOCATION
Change permission:
chmod 700 -R NEW_LOCATION
chown -R postgres:postgres NEW_LOCATION
Target Database to New Location
Solution 1: Update Database Configuration
Edit /etc/postgresql/9.5/main/postgresql.conf and update the new location:
data_directory = '[NEW_LOCATION]'
Start the postgres service:
service postgresql start
Check the log of postgres to make sure it is successfully started:
tail -f /var/log/postgresql/postgresql-9.5-main.log
Remove the old database if needed:
rm -rf /var/lib/postgresql
Solution 2: Link to the new location
I tried to soft link the default directory to the new location but failed for some reason, might be caused by the permission issue:
ln -s NEW_LOCATION /var/lib/postgresql
It should work with the correct permission configuration but need to be further tested.
Automount Disk (for external disk only)
Get the UUID of the external disk:
blkid
Copy the UUID of the target disk, edit /etc/fstab and add the following line:
UUID=[UUID_OF_TARGET_DISK] [MOUNT_POINT] ext4 defaults 0 0
Q&A
I ran into this issue while checking the new database location with sudo -u postgres psql:
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
or
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"?
A potential solution is removing postmaster.pid in the postgres database directory. I am not sure how did I solved this issue, maybe by removing the postmaster.pid or updating the permission.
According to https://stackoverflow.com/questions/42653690/psql-could-not-connect-to-server-no-such-file-or-directory-5432-error, the permission error would lead this error:
root@uddi:/etc/postgresql/9.5/main# pg_ctlcluster 9.5 main start
Error: Config owner (postgres:123) and data owner (root:0) do not match, and config owner is not root
Please make sure the owner of data directory and its subdirectories is postgres.
References
How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 16.04