CKAN Postgre Database Migration

 

Created on: 12/4/19
Last update: 12/4/19

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

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

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

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.

References

How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 16.04

Editing fstab to automount partitions at startup