Hi,
Currently there was some disk issue on our server in which some script is importing huge amount of data on the postgresql datbases which filled up the disk space on the machine and stopped all the services .
/ had only 20 GB of disk space .
Postgresql is installed in the directory /var/lib/pgsql/9.3/data for the postgresql version9.3 . The configuration file of postgresql is postgresql.conf, log files and all other important files are located in that directory
As /var is not a seperate partition, the data directory got filled and which inturn filled up the / partition.
So we need to either change the data directory to /attached which had almost 5 TB of disk space.
Changing the data directory of postgresql seems to be tedious task.
One work around for that issue is to move the data directory /var/lib/pgsql/9.3/data to /data and then create a symlink to /attached/data.
Below is the procedure for it
1) First stop the postgresql service
2)move the data directory to /attached by using the command
mv /var/lib/pgsql/9.3/data /attached
3)now create a sym link to /attached/data from /var/lib/pgsql/9.3/
ln -s /attached/data /var/lib/pgsql/9.3/data
So now the postgresql will check the configuration files and datas inside the same folder /var/lib/pgsql/9.3/data , but it is symlinked to /attached/data. So the actual files will store in /attached/data and won't fill up /.
4)Restart the postgresql service
/etc/init.d/postgresql-9.3 start
5)Test if it is working perfectly.
su - postgres
-bash-4.1$ ll
total 4
drwx------. 3 postgres postgres 4096 Oct 3 04:31 9.3
-bash-4.1$ psql
psql (9.3rc1)
Type "help" for help.
postgres=# show data_directory ;
data_directory
-------------------------
/var/lib/pgsql/9.3/data
(1 row)
postgres=#
Cheers
Syamkumar.M
Currently there was some disk issue on our server in which some script is importing huge amount of data on the postgresql datbases which filled up the disk space on the machine and stopped all the services .
/ had only 20 GB of disk space .
Postgresql is installed in the directory /var/lib/pgsql/9.3/data for the postgresql version9.3 . The configuration file of postgresql is postgresql.conf, log files and all other important files are located in that directory
As /var is not a seperate partition, the data directory got filled and which inturn filled up the / partition.
So we need to either change the data directory to /attached which had almost 5 TB of disk space.
Changing the data directory of postgresql seems to be tedious task.
One work around for that issue is to move the data directory /var/lib/pgsql/9.3/data to /data and then create a symlink to /attached/data.
Below is the procedure for it
1) First stop the postgresql service
2)move the data directory to /attached by using the command
mv /var/lib/pgsql/9.3/data /attached
3)now create a sym link to /attached/data from /var/lib/pgsql/9.3/
ln -s /attached/data /var/lib/pgsql/9.3/data
So now the postgresql will check the configuration files and datas inside the same folder /var/lib/pgsql/9.3/data , but it is symlinked to /attached/data. So the actual files will store in /attached/data and won't fill up /.
4)Restart the postgresql service
/etc/init.d/postgresql-9.3 start
5)Test if it is working perfectly.
su - postgres
-bash-4.1$ ll
total 4
drwx------. 3 postgres postgres 4096 Oct 3 04:31 9.3
-bash-4.1$ psql
psql (9.3rc1)
Type "help" for help.
postgres=# show data_directory ;
data_directory
-------------------------
/var/lib/pgsql/9.3/data
(1 row)
postgres=#
Cheers
Syamkumar.M
Hello!
ReplyDeleteThis method seems rather useful! But if we add a new big data, won't it be store in the previous directory? I mean, if we create a new data base, it will go to /var/lib/pgsql/9.3/data, and not in the /data...
once you create simlink, then the new database will be created in /data which is linked to /var/lib/pgsql/9.3/data, which means data actual data will be in /data, but data will also resides in /var/libpgsql/9.3/data as it is symlinked.
ReplyDeleteThanks
Also there is another option to change the postgresql data directory, that is you need to 1)stop postgresql
2)Edit the postgresql startup service(/etc/init.d/postgresql)
3)then search for the line PG_DATA and then change the value to a new directory. But you need to copy the data from your old data directory to new one before changing that variable
4)Then restart the service
Thanks! Everything is working now :D
DeleteGreat News , Glad that your issue has been resolved.
DeleteI would add :
ReplyDeletesudo chown -R postgres:postgres /attached/
sudo chmod -R 700 /attached/
sudo chown -h postgres:postgres /var/lib/pgsql/9.3/data
What do you Think ?
sudo chown -R postgres:postgres /attached/
Deletesudo chmod -R 755 /attached/ #before moving data instead of 700 to let other users read and explore this directory
sudo chown -h postgres:postgres /var/lib/pgsql/9.3/data