Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How To Change PostgreSQL Data Folder Location on Ubuntu 16.04
In this article, we will learn how to change or relocate the PostgreSQL Database data directory to the new location on Ubuntu 16.04. This database grows more frequently and depends upon the size of the company, as we needed more space and for security reasons we will change the data directory to the other volume or other location.
Prerequisites
- An Ubuntu machine with a non-root user with Sudo permission.
- A PostgreSQL server installed and working.
- A new volume or location where we want to move the database data location, the new location will be /mnt/data_vol/PostgreSQL as the data_vol is the new volume attached to the machine or server.
Changing the PostgreSQL Data Folder Location
Before we change the location of the PostgreSQL data location, we will first check the current settings on the PostgreSQL Server. Below is the command to verify the current data location settings on the server.
$ sudo –u postgre psql Output: psql (9.5.4) Type "help" for help. postgres=#
To verify the current data location settings on the server
postgres=# SHOW data_directory; Output: data_directory ------------------------------ /var/lib/postgresql/9.5/main (1 row) postgres=#
With the above command, we will come to know that the default data directory location is /var/lib/postgresql/9.5/main
Now, we will stop the PostgreSQL service to change the default location for the data folder
Below is the command to stop the PostgreSQL services.
$ sudo systemctl stop postgresql
Once we stop the PostgreSQL we will check the status of the PostgreSQL services with the below command
$ sudo systemctl status postgresql Output: postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese Active: inactive (dead) since Mon 2016-09-12 15:40:23 IST; 3s ago Process: 1553 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 1553 (code=exited, status=0/SUCCESS) Sep 07 19:27:27 ubuntu-16 systemd[1]: Starting PostgreSQL RDBMS... Sep 07 19:27:27 ubuntu-16 systemd[1]: Started PostgreSQL RDBMS. Sep 12 15:20:23 ubuntu-16 systemd[1]: Stopped PostgreSQL RDBMS.
Moving the Existing PostgreSQL data to the New Location
As we stopped the data we will now move the existing PostgreSQL data to the new location with rysnc command with -a and –v flags, -a preserves the files and folder permission at the new location and –v will display the verbose output.
Using rsync, we will create a new postgresql folder at new location here the new location means new mounted volume with data_vol at /mnt/data_vol and the permissions are retained with PostgreSQL so that we cannot get any permission issues while copying the files to the new location.
Below is the command to copy the data.
$ sudo rsync -av /var/lib/postgresql /mnt/data_vol/ Output: sending incremental file list postgresql/ postgresql/.pgpass postgresql/.psql_history postgresql/9.5/ postgresql/9.5/main/ postgresql/9.5/main/PG_VERSION postgresql/9.5/main/postgresql.auto.conf postgresql/9.5/main/postmaster.opts postgresql/9.5/main/base/ postgresql/9.5/main/base/1/ postgresql/9.5/main/global/3592_vm postgresql/9.5/main/global/3593 postgresql/9.5/main/global/4060 postgresql/9.5/main/global/4060_vm postgresql/9.5/main/global/4061 postgresql/9.5/main/global/6000 postgresql/9.5/main/global/6000_vm postgresql/9.5/main/global/6001 postgresql/9.5/main/global/6002 postgresql/9.5/main/global/pg_control postgresql/9.5/main/global/pg_filenode.map … … postgresql/9.5/main/pg_subtrans/0000 postgresql/9.5/main/pg_tblspc/ postgresql/9.5/main/pg_twophase/ postgresql/9.5/main/pg_xlog/ postgresql/9.5/main/pg_xlog/000000010000000000000001 postgresql/9.5/main/pg_xlog/archive_status/ sent 63,180,570 bytes received 36,410 bytes 42,144,653.33 bytes/sec total size is 63,053,465 speedup is 1.00
Once the database is copied we will rename the old data folder and we will keep this folder till we confirm the changes in later steps so that we cannot lose the data from the machine.
$ sudo mv /var/lib/postgresql/9.5/main /var/lib/postgresql_backup
Changing the Data folder Location on Postgresql Configuration Files
We can change the default data folder by editing the /etc/postgresql/9.5/main/postgresql.conf file and edit the data_directory.
$ sudo vi /etc/postgresql/9.5/main/postgresql.conf Output: …. …. …. #------------------------------------------------------------------------------ # FILE LOCATIONS #------------------------------------------------------------------------------ # The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir data_directory = '/mnt/data_vol/postgresql/9.5/main' # use data in another directory # (change requires restart) hba_file = '/etc/postgresql/9.5/main/pg_hba.conf' # host-based authentication file # (change requires restart) ident_file = '/etc/postgresql/9.5/main/pg_ident.conf' # ident configuration file …. …. ….
Once the new data folder is updated in the /etc/postgresql/9.5/main/postgresql.conf we needed to restart the server.
Restarting the PostgreSQL Server and Verify the Data folder Location
As we have to update the PostgreSQL configuration, we will restart the PostgreSQL services so that the configuration is applied.
$ sudo systemctl start postgresql
Once the servies gets, restarted we will now check the status of the PostgreSQL services with the below command –
$ sudo systemctl status postgresql postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pres Active: active (exited) since Mon 2016-09-12 16:57:32 IST; 12s ago Process: 22296 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 22296 (code=exited, status=0/SUCCESS) Sep 12 16:57:32 ubuntu-16 systemd[1]: Starting PostgreSQL RDBMS... Sep 12 16:57:32 ubuntu-16 systemd[1]: Started PostgreSQL RDBMS. Sep 12 16:57:39 ubuntu-16 systemd[1]: Started PostgreSQL RDBMS.
Once the service is restarted we will now verify the data folder location.
$ sudo –u postgre psql Output: psql (9.5.4) Type "help" for help. postgres=#
To verify the current data location settings on the server –
postgres=# SHOW data_directory; Output: data_directory ------------------------------ /mnt/data_vol/postgresql/9.5/main (1 row) postgres=#
Once we confirm that the data folder has been changed on PostgreSQL, we will now delete the old data with the below command.
$ sudo rm –rf /var/lib/postgresql_backup
In the above article we have learnt, how to change the PostgreSQL data folder to a new location where we can store more data, and can move the existing data along with the database to the new location.
2K+ Views
