theCodingInterface

Developer's Guide to PostgreSQL on Linux: Changing the Default Data Directory

By Adam McQuistan in PostgreSQL 10/06/2019 Comment

Introduction

In this fifth article demonstrates how to change the default data directory of a PostgreSQL version 11 database cluster running on the Linux Ubuntu 18 LTS and CentOS 7 operating systems. This article shifts focus to improving the scalability, robustness and, performance made possible by moving the data directory to it's own area of the file system. Isolating the data directory also reduces file access contention with other OS activities and allows for easier resizing.

Series Contents

Installation
psql interactive shell
Creating Databases
Creating Accounts and Making Connections
Changing the Default Data Directory (you're here)
Using Explain and Analyze (coming soon)
Utilitizing Indexes (coming soon)
Creating Backups (coming soon)
Restoring From Backups (coming soon)

Changing the Default Data Directory on Ubuntu

First things first I identify the location of the default data directory by querying the database cluster for the value of data_directory as the postgres Linux superuser.

$ whoami
postgres
$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# SHOW data_directory;
       data_directory        
-----------------------------
 /var/lib/postgresql/11/main
(1 row)

postgres=# \q

It is important to take note of the ownership and permissions of the data directory because I need to make sure the new directory matches them.

# ls -l /var/lib/postgresql/11/main
total 84
-rw------- 1 postgres postgres    3 Sep 28 03:18 PG_VERSION
drwx------ 7 postgres postgres 4096 Oct  5 02:24 base
drwx------ 2 postgres postgres 4096 Sep 28 04:38 global
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_commit_ts
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_dynshmem
drwx------ 4 postgres postgres 4096 Oct  5 14:55 pg_logical
drwx------ 4 postgres postgres 4096 Sep 28 03:18 pg_multixact
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_notify
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_replslot
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_serial
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_snapshots
drwx------ 2 postgres postgres 4096 Oct  5 14:55 pg_stat
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_subtrans
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_tblspc
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_twophase
drwx------ 3 postgres postgres 4096 Sep 28 03:18 pg_wal
drwx------ 2 postgres postgres 4096 Sep 28 03:18 pg_xact
-rw------- 1 postgres postgres   88 Sep 28 03:18 postgresql.auto.conf
-rw------- 1 postgres postgres  130 Sep 28 03:18 postmaster.opts

Afterwards I need to stop the postgresql service which I do as root (or any other user with sudo privilleges).

# whoami
root
# systemctl stop postgresql
# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Sat 2019-10-05 14:55:39 UTC; 9s ago
  Process: 30500 ExecReload=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 17504 (code=exited, status=0/SUCCESS)

Sep 28 03:18:45 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Started PostgreSQL RDBMS.
Oct 05 01:32:01 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Reloading PostgreSQL RDBMS.
Oct 05 01:32:01 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Reloaded PostgreSQL RDBMS.
Oct 05 01:33:21 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Reloading PostgreSQL RDBMS.
Oct 05 01:33:21 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Reloaded PostgreSQL RDBMS.
Oct 05 01:52:53 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Reloading PostgreSQL RDBMS.
Oct 05 01:52:53 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Reloaded PostgreSQL RDBMS.
Oct 05 13:46:23 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Reloading PostgreSQL RDBMS.
Oct 05 13:46:23 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Reloaded PostgreSQL RDBMS.
Oct 05 14:55:39 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Stopped PostgreSQL RDBMS.

For this example I have attached to my Ubuntu server an isolated storage volume and mounted it to the /pgdata directory which will be the target for transfering the default data directory to.

# lsblk -o NAME,FSTYPE,SIZE,MOUNTPOINT
NAME    FSTYPE  SIZE MOUNTPOINT
sda     ext4     20G /pgdata
vda              25G 
├─vda1  ext4   24.9G /
├─vda14           4M 
└─vda15 vfat    106M /boot/efi

Before transferring any files I must make the /pgdata directory have the same ownership and permissions as the default data directory.

# chown postgres:postgres /pgdata
# chmod 700 /pgdata

Next I use rsync file transfer program to copy contents of the data directory to the /pgdata volume as follows.

rsync -av /var/lib/postgresql/11/main /pgdata

Then I update the data_directory variable in the /etc/postgresql/11/main/postgresql.conf config file to point to the new directory.

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/pgdata/main'         # use data in another directory

Now I can start the postgresql service and query the database cluster to show the data_directory to make sure the change was successful.

# whoami
root
# systemctl start postgresql
# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sun 2019-10-06 02:46:08 UTC; 8s ago
  Process: 30500 ExecReload=/bin/true (code=exited, status=0/SUCCESS)
  Process: 2379 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 2379 (code=exited, status=0/SUCCESS)

Oct 06 02:46:08 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Starting PostgreSQL RDBMS...
Oct 06 02:46:08 ubuntu-s-1vcpu-1gb-nyc3-01 systemd[1]: Started PostgreSQL RDBMS.
# su - postgres
$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# SHOW data_directory;
 data_directory 
----------------
 /pgdata/main
(1 row)

postgres=#

Changing the Default Data Directory on CentOS

I start by identifying the location of the default data directory by querying the postgresql database cluster.

$ whoami
postgres
-bash-4.2$ psql
psql (11.5)
Type "help" for help.

postgres=# SHOW data_directory;
     data_directory     
------------------------
 /var/lib/pgsql/11/data
(1 row)

postgres=#

Then I take note of the ownership and permissions as they will need to be replicated in the new data directory location on disk.

 \q
-bash-4.2$ ls -l /var/lib/pgsql/11/data
total 60
drwx------. 7 postgres postgres    67 Oct  5 02:22 base
-rw-------. 1 postgres postgres    30 Oct  6 00:00 current_logfiles
drwx------. 2 postgres postgres  4096 Oct  5 02:20 global
drwx------. 2 postgres postgres   188 Oct  4 00:00 log
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_commit_ts
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_dynshmem
-rw-------. 1 postgres postgres  4269 Sep 28 03:40 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Sep 28 03:40 pg_ident.conf
drwx------. 4 postgres postgres    68 Oct  5 02:27 pg_logical
drwx------. 4 postgres postgres    36 Sep 28 03:40 pg_multixact
drwx------. 2 postgres postgres    18 Sep 28 03:41 pg_notify
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_replslot
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_serial
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_snapshots
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_stat
drwx------. 2 postgres postgres    63 Oct  6 02:56 pg_stat_tmp
drwx------. 2 postgres postgres    18 Sep 28 03:40 pg_subtrans
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_tblspc
drwx------. 2 postgres postgres     6 Sep 28 03:40 pg_twophase
-rw-------. 1 postgres postgres     3 Sep 28 03:40 PG_VERSION
drwx------. 3 postgres postgres    60 Sep 28 03:40 pg_wal
drwx------. 2 postgres postgres    18 Sep 28 03:40 pg_xact
-rw-------. 1 postgres postgres    88 Sep 28 03:40 postgresql.auto.conf
-rw-------. 1 postgres postgres 23873 Sep 28 03:40 postgresql.conf
-rw-------. 1 postgres postgres    58 Sep 28 03:41 postmaster.opts
-rw-------. 1 postgres postgres   104 Sep 28 03:41 postmaster.pid

Before going any further I must stop the postgresql-11 service.

# systemctl stop postgresql-11
# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: https://www.postgresql.org/docs/11/static/

Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 systemd[1]: Starting PostgreSQL 11 database server...
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.455 UTC [18179] LOG:  listening on IPv6 address "::1", port 5432
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.455 UTC [18179] LOG:  listening on IPv4 address "127.0.0.1", port 5432
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.459 UTC [18179] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.463 UTC [18179] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.479 UTC [18179] LOG:  redirecting log output to logging collector process
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 postmaster[18179]: 2019-09-28 03:41:01.479 UTC [18179] HINT:  Future log output will appear in directory "log".
Sep 28 03:41:01 centos-s-1vcpu-1gb-nyc3-01 systemd[1]: Started PostgreSQL 11 database server.
Oct 06 03:13:00 centos-s-1vcpu-1gb-nyc3-01 systemd[1]: Stopping PostgreSQL 11 database server...
Oct 06 03:13:00 centos-s-1vcpu-1gb-nyc3-01 systemd[1]: Stopped PostgreSQL 11 database server.

For demonstration I have attached a volume and mounted it to a /pgdata directory of the CentOS server which I will use to transfer the default data directory to for this tutorial.

# lsblk -o NAME,FSTYPE,SIZE,MOUNTPOINT
NAME   FSTYPE   SIZE MOUNTPOINT
sda    ext4      20G /pgdata
vda              25G 
└─vda1 xfs       25G /
vdb    iso9660  456K

Before moving any files I mirror the ownership and permissions from the default data directory to the new one at /pgdata.

# chown postgres:postgres /pgdata
# chmod 700 /pgdata

Next I transfer the default data directory to the new location using the rsync command.

# rsync -av /var/lib/pgsql/11/data /pgdata

When performing data directory transfers on a CentOS server I like to clean up the directory I've transferred the default data directory to by removing the config files that got copied over. This occurs because on a CentOS server they are placed in the same location as the default data directory but, it's important to understand that only the ones in the default data directory will be used after the transfer and having two copies floating around is a source of confusion.

# rm /pgdata/data/pg_hba.conf
# rm /pgdata/data/pg_ident.conf 
# rm /pgdata/data/postgresql.auto.conf 
# rm /pgdata/data/postgresql.conf

After this I update the data_directory variable of the /var/lib/pgsql/11/data/postgresql.conf config file to point to the new data directory.

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/pgdata/data'         # use data in another directory

Finally I start the postgresql-11 service and verify that the data directory has been moved by querying the data_directory variable again in the database cluster.

# systemctl start postgresql-11
# su - postgres
Last login: Sun Oct  6 02:54:30 UTC 2019 on pts/0
-bash-4.2$ psql
psql (11.5)
Type "help" for help.

postgres=# SHOW data_directory;
 data_directory 
----------------
 /pgdata/data
(1 row)

postgres=#

Resources for Learning More About PostgreSQL

A great, example driven, reference book on robust management of PostgreSQL is PostgreSQL High Availability Cookbook - Second Edition
A good companion book that is again example driven but focusing on performance is PostgreSQL High Performance Cookbook

thecodinginterface.com earns commision from sales of linked products such as the books above. This enables providing continued free tutorials and content so, thank you for supporting the authors of these resources as well as thecodinginterface.com

Conclusion

In this article I demonstrated how to change the default data directory to another directory that has been mounted with a separate disk volume. This is a common approach to enhance robustness of the PostgreSQL database resulting in reduced disk access contention among other processes and allows for easier resizing of the data storage area.

psql PostgreSQL Databases CentOS Ubuntu Linux

Share with friends and colleagues

[[ likes ]] likes