How To Change PostgreSQL Data Folder Location on Ubuntu 16.04

PostgreSQLUbuntuFile System

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.

raja
Published on 21-Jan-2020 12:30:43
Advertisements