How can I move postgresql data to another directory on Ubuntu over Amazon EC2?

Stop the server.

Copy the datadir while retaining permissions - use cp -aRv.

Then (easiest, as it avoids the need to modify initscripts) just move the old datadir aside and symlink the old path to the new location.

Thanks for the accepted answer. Instead of the symlink you can also use a bind mount. That way it is independent from the file system. If you want to use a dedicated hard drive for the database you can also mount it normally. to the data directory.

I did the latter. Here are my steps if someone needs a reference. I ran this as a script on many AWS instances.

# stop postgres serversudo service postgresql stop# create new filesystem in empty hard drivesudo mkfs.ext4 /dev/xvdb# mount itmkdir /tmp/pgsudo mount /dev/xvdb /tmp/pg/# copy the entire postgres home dir contentsudo cp -a /var/lib/postgresql/. /tmp/pg# mount it to the correct directorysudo umount /tmp/pgsudo mount /dev/xvdb /var/lib/postgresql/# see if it is mounted mount | grep postgres# add the mount point to fstabecho "/dev/xvdb /var/lib/postgresql ext4 rw 0 0" | sudo tee -a /etc/fstab# when database is in use, observe that the correct disk is being usedwatch -d grep xvd /proc/diskstats

A clarification. It is the particular AMI that you used that sets ubuntu as the default user, this may not apply to other AMIs.

In essence if you are trying move data manually, you will probably need to do so as the root user, and then make sure its available to whatever user postgres is running with.

You also do have the option of snapshotting the volume and increasing the size of the a volume created from the snapshot. Then you could replace the volume on your instance with the new volume (You probably will have to resize the partition to take advantage of all the space).