Postgres DB HDD needs expanding

Question

I have an Azure VM running Postgres on Unix, supplied by Bitnami: PostgreSQL 9.6.2-0 (Ubuntu 14.04)

The DB works fine and I am populating it, but after some time, the DB reaches about 25GB in size and then my code (populating the DB) crashes...

psycopg2.OperationalError: could not extend file "base/16450/79079": No space left on device
HINT:  Check free disk space.

I thought it would be a simple resize of my VM. Nope. The VM now has a HDD size of 400GB. Plenty of space.

I then tried my code and again, at 25GB, boom. No space.

The next thing I did was log into the VM via SSH and check the disks with df -h:

As can be seen above, /dev/sda1 is 30GB in size, of which 29GB is used. I suspect my Postgres is using this HDD... set automatically when I used the Bitnami Azure template.

You can see the 400GB HDD that I have set via the Azure portal: /dev/sdb1.

What can I do? I need a lot more data to go into my Postgres DB... and there is no visible way to change the size of /dev/sda1 via the portal.

Update

As requested, here is the contents of fstab before modification (default):

I changed /mnt to /opt/bitnami/postgresql/data (according to the path: https://stackoverflow.com/a/8237512/596841). When I updated, saved, quit and rebooted the server, it went back to the default (as seen above).

Answer 1

The easiest thing to do would be to mount /dev/sdb1 where your data lives. How to find where data lives here: (https://stackoverflow.com/questions/1137060/where-does-postgresql-store-the-database)

So once you know that stop your database and move the data to your /mnt directory.

Then you will place the larger drive where it needs to be for postgres to see it.

umount /dev/sdb1
mount /dev/sdb1 /original/path/to/data

Now you will likely need to update your /etc/fstab from /mnt to /original/path/to/data so that it will persist across reboots.

You can also use LVM and do this: https://kb.vmware.com/selfservice/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&externalId=1006371

I personally think the mount method is the easiest.

Answer 2

The VM now has a HDD size of 400GB

/dev/sdb1 is the temporary storage, this temporary storage must not be used to store data that you are not willing to lose. (reboot will lose temporary data)

I need a lot more data to go into my Postgres DB

We can use Azure portal to add a new disk to this VM, then mount this disk to this VM.

Add a new disk via Azure portal:

Mount /dev/sdc to Azure VM:

root@jasonvm:/home/jason# fdisk -l  #list all disks

Disk /dev/sdc: 429.5 GB, 429496729600 bytes   #we can find this new disk
255 heads, 63 sectors/track, 52216 cylinders, total 838860800 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Then use this command to mount this disk to Azure VM:

root@jasonvm:/home/jason# mkfs.ext4 /dev/sdc
root@jasonvm:/home/jason# mkdir -p /mnt/point1
root@jasonvm:/home/jason# mount /dev/sdc /mnt/point1
root@jasonvm:/home/jason# df -Th
Filesystem     Type      Size  Used Avail Use% Mounted on
udev           devtmpfs  1.7G   12K  1.7G   1% /dev
tmpfs          tmpfs     344M  348K  344M   1% /run
/dev/sda1      ext4       30G  873M   28G   4% /
none           tmpfs     4.0K     0  4.0K   0% /sys/fs/cgroup
none           tmpfs     5.0M     0  5.0M   0% /run/lock
none           tmpfs     1.7G     0  1.7G   0% /run/shm
none           tmpfs     100M     0  100M   0% /run/user
/dev/sdb1      ext4       50G   52M   47G   1% /mnt
/dev/sdc       ext4      394G   71M  374G   1% /mnt/point1
root@jasonvm:/home/jason# blkid    # list UUID
/dev/sr0: LABEL="rd_rdfe_stable.161107-1031" TYPE="udf" 
/dev/sda1: LABEL="cloudimg-rootfs" UUID="ce8fdc9c-fa56-49db-b69d-8c11634cbb7a" TYPE="ext4" 
/dev/sdb1: UUID="b0531bb0-dadf-4e20-a968-bd177989f7b8" TYPE="ext4" 
/dev/sdc: UUID="46c75dcc-51c8-4d53-8535-d4fc7bf3a5cb" TYPE="ext4" 
root@jasonvm:/home/jason# vi /etc/fstab   # add UUID to fstab, 
# CLOUD_IMG: This file was created/modified by the Cloud Image build process
UUID=ce8fdc9c-fa56-49db-b69d-8c11634cbb7a       /        ext4   defaults,discard        0 0
/dev/disk/cloud/azure_resource-part1    /mnt    auto    defaults,nobootwait,comment=cloudconfig 0       2
UUID=46c75dcc-51c8-4d53-8535-d4fc7bf3a5cb       /mnt/point1     ext4    defaults,nofail 0 0

Note:
we should add this disk to /etc/fstab, in this way, after reboot this VM we will not lost connection to this disk.

After mount new disk to Azure VM, then modify data_directory, we can follow those steps:

Editing the data_directory in postgresql.conf to point to the new location (e.g. /mnt/point1/postgresql/9.3/main)

Transferring contents of PG's data directory to /mnt/point1 via sudo rsync -av /opt/bitnami/postgresql/data /mnt/point1

Restarting posgtesql.