Postgresql: database running out of space.

What we are going to do here are the following:

Trying to import a database and getting this.

psql:VCDBackUp:89387594: ERROR:  could not extend file "base/16384/25122": wrote only 4096 of 8192 bytes at block 156

HINT:  Check free disk space.

psql:VCDBackUp:89387601: ERROR:  could not extend file "base/16384/25123": No space left on device

HINT:  Check free disk space.

Basically, df -h showing 100% used

• mount a new drive /media/data
• copy 9.3 data path to /media/data/data
• create symlink to point to the new place.
• chown the /media/data to postgresql:postgresql

Verifying what to move. Basically, the "data" directory under 9.3
-bash-4.1$ ls
9.2  9.3  backup  logfile
-bash-4.1$ cd 9.3
-bash-4.1$ ls
backups  data  pgstartup.log

Move the data to /media/data
-bash-4.1$ mv data /media/data/

Changing the owner of the data directory as /media/data is currently own by root.

root@postgres6ec-001 media]# ls
data
[root@postgres6ec-001 media]# chown postgres:postgres data

Create symlink

-bash-4.1$ pwd
/var/lib/pgsql/9.3
-bash-4.1$ ln -s /media/data/data /var/lib/pgsql/9.3/data
-bash-4.1$ ls -las
total 16
4 drwx------. 3 postgres postgres 4096 Apr 26 15:36 .
4 drwx------. 5 postgres postgres 4096 Mar  2  2015 ..
4 drwx------. 2 postgres postgres 4096 Jul 23  2014 backups
0 lrwxrwxrwx. 1 postgres postgres   16 Apr 26 15:36 data -> /media/data/data
4 -rw-------. 1 postgres postgres 3676 May  5  2015 pgstartup.log
-bash-4.1$

Verify if the intended 9.3 postgresql database is not up and running.

-bash-4.1$ ps -ef|grep postgres
avahi     1852     1  0  2015 ?        00:00:13 avahi-daemon: running [postgres6ec-001.local]
postgres  2127     1  0  2015 ?        00:07:38 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data
postgres  2129  2127  0  2015 ?        00:00:00 postgres: logger process
postgres  2131  2127  0  2015 ?        00:00:15 postgres: checkpointer process
postgres  2132  2127  0  2015 ?        00:05:04 postgres: writer process
postgres  2133  2127  0  2015 ?        00:05:00 postgres: wal writer process
postgres  2134  2127  0  2015 ?        00:06:28 postgres: autovacuum launcher process
postgres  2135  2127  0  2015 ?        00:09:54 postgres: stats collector process



Start up the postgresql 9.3 under port new port 5433 as 5432 is used by 9.2.

bash-4.1nohup /usr/pgsql-9.3/bin/postgres -p 5433 -D /var/lib/pgsql/9.3/data &


Check the new 9.3 database status
-bash-4.1$ ps -ef|grep postgres
avahi     1852     1  0  2015 ?        00:00:13 avahi-daemon: running [postgres6ec-001.local]
postgres  2127     1  0  2015 ?        00:07:38 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data
postgres  2129  2127  0  2015 ?        00:00:00 postgres: logger process
postgres  2131  2127  0  2015 ?        00:00:15 postgres: checkpointer process
postgres  2132  2127  0  2015 ?        00:05:04 postgres: writer process
postgres  2133  2127  0  2015 ?        00:05:00 postgres: wal writer process
postgres  2134  2127  0  2015 ?        00:06:28 postgres: autovacuum launcher process
postgres  2135  2127  0  2015 ?        00:09:54 postgres: stats collector process
postgres 13442 13285  0 15:55 pts/2    00:00:00 /usr/pgsql-9.3/bin/postgres -p 5433 -D /var/lib/pgsql/9.3/data
postgres 13443 13442  0 15:55 ?        00:00:00 postgres: logger process
postgres 13445 13442  0 15:55 ?        00:00:00 postgres: checkpointer process
postgres 13446 13442  0 15:55 ?        00:00:00 postgres: writer process
postgres 13447 13442  0 15:55 ?        00:00:00 postgres: wal writer process
postgres 13448 13442  0 15:55 ?        00:00:00 postgres: autovacuum launcher process
postgres 13449 13442  0 15:55 ?        00:00:00 postgres: stats collector process



Add the new disk (/dev/sdb1) to automount on start up.



-bash-4.1$ cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Tue May 15 13:38:30 2012
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/VolGroup-lv_root /                       ext4    defaults        1 1
UUID=be520c4e-c945-485c-b956-5bba238ef87c /boot                   ext4    defaults        1 2
/dev/mapper/VolGroup-lv_swap swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0

/dev/sdb1       /media/data                     ext4    defaults        0 0