Volume and File System Architectural Layout

PostgreSQL
Audience
Public
Technology Integrations
Postgre SQL
Source Type
Documentation

PostgreSQL relies on the operating system with file systems and underlying volumes servicing the persistent storage of row and columnar data.

Note:

PostgreSQL refers to the installation or instance as a cluster. This guide assumes that an operating system will have at most one cluster at any one time.

A PostgreSQL cluster consists of the following files :

  • Configuration Files
  • Write-Ahead-Log (Online and Archived) Files
  • Tablespaces
  • Server Log Files

There are a number of ways in which the file system layout for a PostgreSQL cluster can be established. The primary recommendation is that all volumes excluding the Write-Ahead-Log archive location should be located on the same FlashArray. The following table highlights the different approaches to the file system layouts for a PostgreSQL cluster.

Default layout

All tablespaces , logs files , configuration files and the write-ahead-log will be placed in the same file system using the default directory structure for the operating system.

 

Microsoft Windows

Linux

PostgreSQL Community Edition

EnterpriseDB Advanced Server

Note:

The recommendation for volumes from FlashArray is to mount a volume to the following locations with the correct permissions set before installing PostgreSQL :

Microsoft Windows - C:\Program Files\PostgreSQL\ Permissions - PostgreSQL service account - READ Permissions on all directories leading up to the service directory, WRITE permissions are required only on the data directory)

Linux - /var/lib/pgsql(Permissions - user postgres, group postgres, drwx------)

Initializing PostgreSQL cluster at a different location

This assumes that there is a new PostgreSQL deployment with no data or significant configuration that needs to be located in a volume or file system which is not in the default location.

Note:

The steps will need to be repeated when performing package upgrades as the default PostgreSQL settings will overwrite these changes.

Microsoft Windows

In its default configuration with the PostgreSQL installation the data directory is created in C:\Program Files\PostgreSQL\<version>\ and owned by the Network Service. Initializing the cluster at a different storage location requires that a user with interactive logon permissions is present to perform command line and interactive operations. This user will also be given permissions on the intended destination location in order to operate the data directory.

  1. Set the permissions prior to initialization

When installing PostgreSQL from a installation manifest the minimum required users and groups will be automatically created.

The permissions for the C:\PGSQL directory need to be given to the user under which the postgresql service runs as. In a default environment this is the NETWORK SERVICE user , which needs to be changed to the user who will run the initdb application(any user with interactive logon capabilities). A directory for the data directory also needs to be created in the new location and given the appropriate permissions. In this example the DBTest user is given the permissions to the C:\PGSQL\ and C:\PGSQL\Data directories and is the user under which the PostgreSQL service will run.

  1. Initialize the PostgreSQL cluster at the new location

To initialize a new PostgreSQL cluster the initdb application is used.

The initdb application can be found in the installation directory for PostgreSQL (default location: C:\Program Files\PostgreSQL\<Version>) in the bin directory. It can then be executed with the -D argument to initialize the data directory at a new location (note do not use the root of a filesystem). Open PowerShell and execute the following , adjusting as required with additional arguments :


& 'C:\Program Files\PostgreSQL\13\bin\initdb.exe' -D C:\PGSQL\Data\

If everything is successful the output should be similar to the following :


The files belonging to this database system will be owned by user "DBtest".
This user must also own the server process.

The database cluster will be initialized with locale "English_United States.1252".
The default database encoding has accordingly been set to "WIN1252".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory C:/PGSQL/Data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... windows
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... US/Pacific
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    ^"C^:^\Program^ Files^\PostgreSQL^\13^\bin^\pg^_ctl^" -D ^"C^:^\PGSQL^\Data^\^\^" -l logfile start

  1. Change startup options and specify new data directory locations

The service used to automatically start the PostgreSQL cluster needs to be deleted and recreated with the correct command to target the new PostgreSQL data directory location. To avoid conflicts with the old data directory the existing service needs to be deleted :

In the Services management dialog the PostgreSQL cluster in this example is managed by the postgresql-x64-13 service :

This service needs to be deleted , which can be done in a command terminal like PowerShell (As an Administrator enter the cmd process) with the command :


sc.exe delete postgresql-x64-13

If successful the prompt should respond with the following message :


[SC] DeleteService SUCCESS

To recreate the service with the new settings use the following command in the same command terminal, adjusting the user (-U) and data directory (-D) as required :


& 'C:\Program Files\PostgreSQL\13\bin\pg_ctl.exe' register -N "postgresql-x64-13" -U "soln\DBTest" -D "C:\PGSQL\data" -w

This recreates the windows service with the correct data directory and user startup permissions.

Before starting the service for the first time the user must have the password entered for it , so that the service can start correctly. This can be done by going into the service properties and navigating to the Log On section. In the Log On section provide the password for the user account and select OK.

At this point the service can be started and will now use the new data directory location.

Linux

The postgres user which is a member of the postgres group is created with the home directory : /var/lib/pgsql/ (also the default location for the data directory under the /version/ folder) during installation from a package or package manager. This user will be used to provide the correct permissions for the intended destination location.

  1. Set the permissions prior to initialization

This user and group need to be given ownership of /pgsql and its contents (a directory called data needs to be created before running the initdb application) once a FlashArray volume has been mounted to it. This is done using the chown command :


chown -R postgres:postgres /pgsql

  1. Adjust startup and environment properties

The PGDATA environment variable needs to be changed so that it persists across sessions and is used correctly when starting the PostgreSQL cluster. For systemd based systems the service configuration file needs to be altered to ensure the new database directory is used.

Persistently set PGDATA environment variables

Note:

Note the PGDATA path in linux environments must not contain spaces.

The PGDATA environment variable is set in the following locations and needs to be changed :

~/.bash_profile


[ -f /etc/profile ] && source /etc/profile
PGDATA=/pgsql/data
export PGDATA
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

(SystemD) Change database directory

(As root) Identify the service file under which PostgreSQL is started in systemd (this is the .service file found after the Loaded section) :


systemctl status postgresql-13.service[root@DB-04 data]# systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: https://www.postgresql.org/docs/13/static/

Sep 01 06:39:34 DB-04 systemd[1]: Starting PostgreSQL 13 database server...
Sep 01 06:39:34 DB-04 postmaster[3980]: 2021-09-01 06:39:34.427 PDT [3980] LOG:  redirecting log output to logging collector process
Sep 01 06:39:34 DB-04 postmaster[3980]: 2021-09-01 06:39:34.427 PDT [3980] HINT:  Future log output will appear in directory "log".
Sep 01 06:39:34 DB-04 systemd[1]: Started PostgreSQL 13 database server.
Sep 02 01:37:28 DB-04 systemd[1]: Stopping PostgreSQL 13 database server...
Sep 02 01:37:28 DB-04 systemd[1]: postgresql-13.service: Killing process 3981 (postmaster) with signal SIGKILL.
Sep 02 01:37:28 DB-04 systemd[1]: Stopped PostgreSQL 13 database server.

Edit the service file , altering the Environment=PGDATA= line :


[Unit]
Description=PostgreSQL 13 database server
Documentation=https://www.postgresql.org/docs/13/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=/pgsql/data

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT


# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

[Install]
WantedBy=multi-user.target

Save the file and reload SystemD :


systemctl daemon-reload

(SELinux enabled systems only) - Change SELinux configuration to avoid service startup issues

Get the SElinux labeling for the default data directory with the following command:


ls -lh -Zd /var/lib/pgsql
drwx------ 3 postgres postgres system_u:object_r:postgresql_db_t:s0 data /var/lib/pgsql

Once you have the label change it with the following command:


semanage fcontext -a -t postgresql_db_t "/pgsql/data(/.*)?"

  1. Initialize the PostgreSQL cluster at the new location

The initdb application is apart of the postgresql-<version>-setup binary. This can be located in /usr/pgsql-<version>/bin. The data directory can only be initialized after changing the systemd startup object.

Before proceeding check the environment variable is correctly set through the use of the env command :


LANG=en_US.UTF-8
HISTCONTROL=ignoredups
HOSTNAME=DB-04
USER=postgres
PWD=/var/lib/pgsql
HOME=/var/lib/pgsql
PGDATA=/pgsql/data
MAIL=/var/spool/mail/postgres
SHELL=/bin/bash
TERM=xterm-256color
SHLVL=1
LOGNAME=postgres
PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
HISTSIZE=1000
LESSOPEN=||/usr/bin/lesspipe.sh %s
_=/usr/bin/env

At this point the cluster can be initialized at a new location using initdb executed as root :


/usr/pgsql-13/bin/postgresql-13-setup initdb

if successful the following output should be shown :


Initializing database ...

At this point the PostgreSQL cluster can be started.

Migrating existing database from default location to a different location

The configuration files, tablespaces and log files can be moved from the default location without data loss. This process requires downtime. The steps below will address the initialization of a new PostgreSQL cluster and the movement of a pre-existing one.

Note:

The steps will need to be repeated when performing package upgrades as the default PostgreSQL settings will overwrite these changes.

Microsoft Windows

Linux

The recommendation for a migration from the default location to a different one are to ensure the FlashArray volume has been formatted with a file system and mounted at the desired location. This example will assume that the new location is C:\PGSQL for Microsoft Windows and /pgsql for Linux operating systems.Both locations have a FlashArray volume mounted to them.

Microsoft Windows

This is disruptive and requires the PostgreSQL service to be stopped during this process. Any files should be copied and not moved during this process with the intention to preserve file integrity and existing permissions.

Note:

Only the data directory is moved as apart of this process. Application binaries and files not stored in the data directory should not be moved as apart of this process.

  1. Set the permissions on destination prior to migration

Permissions for the destination storage volume should be the same as the source from where the data directory is being copied.

In this example the data directory is found in C:\Program Files\PostgreSQL\13\ :

Viewing the permissions on the source data directory the DBTest@soln.local user has Full control of the folder:

Create a directory (in this example - data) in the root of the target filesystem and ensure it has the same permissions as the source directory :

  1. Stop the PostgreSQL cluster

In the Services Management Console identify the service responsible for PostgreSQL , right click on it and select Stop.

  1. Copy the contents of the data directory to the new location , preserving existing permissions

In a PowerShell terminal use the following command to copy the data folder and its contents to the intended destination :


xcopy.exe 'C:\Program Files\PostgreSQL\13\data' C:\PGSQL\data /O/X/E/H/K

  1. Change startup options and specify new data directory locations

The service used to automatically start the PostgreSQL cluster needs to be deleted and recreated with the correct command to target the new PostgreSQL data directory location. To avoid conflicts with the old data directory the existing service needs to be deleted :

In the Services management dialog the PostgreSQL cluster in this example is managed by the postgresql-x64-13 service :

This service needs to be deleted , which can be done in a command terminal like PowerShell (As an Administrator enter the cmd process) with the command :


sc.exe delete postgresql-x64-13

If successful the prompt should respond with the following message :


[SC] DeleteService SUCCESS

To recreate the service with the new settings use the following command in the same command terminal, adjusting the user (-U) and data directory (-D) as required :


& 'C:\Program Files\PostgreSQL\13\bin\pg_ctl.exe' register -N "postgresql-x64-13" -U "soln\DBTest" -D "C:\PGSQL\data" -w

This recreates the windows service with the correct data directory and user startup permissions.

Before starting the service for the first time the user must have the password entered for it , so that the service can start correctly. This can be done by going into the service properties and navigating to the Log On section. In the Log On section provide the password for the user account and select OK.

At this point the service can be started and will now use the new data directory location.

Linux

  1. Set the permissions on destination prior to migration

The postgres user which is a member of the postgres group is created with the home directory : /var/lib/pgsql/ (also the default location for the data directory under the /version/ folder) during installation from a package or package manager. This user will be used to provide the correct permissions for the intended destination location.

A FlashArray volume should be mounted and formatted with a file system before creating the folder and assigning the permissions.

Before assigning the permissions ensure that a directory (in this example - data) has been created. This folder will contain the data directory contents. This can be done using the mkdir command as the postgres or root user :


mkdir /pgsql/data

This user and group need to be given ownership of /pgsql and its contents if the folders have been created by root or another user. The permissions can be assigned using the chown command :


chown -R postgres:postgres /pgsql

  1. Stop the PostgreSQL cluster

In a terminal type the following command:


sudo systemctl stop postgresql-13.service

  1. Copy the contents of the data directory to the new location , preserving existing permissions

In a command terminal use the following command to copy the contents of the data directory to the intended destination:


rsync -av /var/lib/pgsql/13/data/ /pgsql/data/

  1. Adjust startup and environment properties

The PGDATA environment variable needs to be changed so that it persists across sessions and is used correctly when starting the PostgreSQL cluster. For systemd based systems the service configuration file needs to be altered to ensure the new database directory is used.

Persistently set PGDATA environment variables

Note:

Note the PGDATA path in linux environments must not contain spaces.

The PGDATA environment variable is set in the following locations and needs to be changed :

~/.bash_profile


[ -f /etc/profile ] && source /etc/profile
PGDATA=/pgsql/data
export PGDATA
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

(SystemD) Change database directory

(As root) Identify the service file under which PostgreSQL is started in systemd (this is the .service file found after the Loaded section) :


systemctl status postgresql-13.service[root@DB-04 data]# systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: https://www.postgresql.org/docs/13/static/

Sep 01 06:39:34 DB-04 systemd[1]: Starting PostgreSQL 13 database server...
Sep 01 06:39:34 DB-04 postmaster[3980]: 2021-09-01 06:39:34.427 PDT [3980] LOG:  redirecting log output to logging collector process
Sep 01 06:39:34 DB-04 postmaster[3980]: 2021-09-01 06:39:34.427 PDT [3980] HINT:  Future log output will appear in directory "log".
Sep 01 06:39:34 DB-04 systemd[1]: Started PostgreSQL 13 database server.
Sep 02 01:37:28 DB-04 systemd[1]: Stopping PostgreSQL 13 database server...
Sep 02 01:37:28 DB-04 systemd[1]: postgresql-13.service: Killing process 3981 (postmaster) with signal SIGKILL.
Sep 02 01:37:28 DB-04 systemd[1]: Stopped PostgreSQL 13 database server.

Edit the service file , altering the Environment=PGDATA= line :


[Unit]
Description=PostgreSQL 13 database server
Documentation=https://www.postgresql.org/docs/13/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=/pgsql/data

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT


# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

[Install]
WantedBy=multi-user.target

Save the file and reload SystemD :


sudo systemctl daemon-reload

(SELinux enabled systems only) - Change SELinux configuration to avoid service startup issues

Get the SElinux labeling for the default data directory with the following command:


ls -lh -Zd /var/lib/pgsql
drwx------ 3 postgres postgres system_u:object_r:postgresql_db_t:s0 data /var/lib/pgsql

Once you have the label change it with the following command:


semanage fcontext -a -t postgresql_db_t "/pgsql/data(/.*)?"

At this point the PostgreSQL cluster can be started and will use the new data directory location.

Separating database components into isolated file systems

This layout separates the storage objects for the cluster into separate volumes and file systems. This is useful for scenarios where quotas or more in depth reporting on the object types could be advantageous to database or storage administrators. The following guidelines can be used as a template for how each object could be implemented in a separate storage location :

Linux Location

Microsoft Windows Location

Cluster Objects

Recommended Capacity

/pgarchive

C:\PGSQL\pgarchive

Archive location for archive logs and continuous archiving.

> 2 x total cluster size

/pgbackup

C:\PGSQL\pgbackup

Location for all physical and logical backups.

(Total Database Size x retention period) + 10%

/pgcluster

C:\PGSQL\pgcluster

The default cluster data directory. Maps to the PGDATA environment variable. The online Write-Ahead-log should be located in the subfolder pg_xlog.

> 16GB + intended database size + 10%

/pglog

C:\PGSQL\pglog

The location of the server log files.

> 10GB , depending on log_rotation_age and log_rotation_size.

For each physical location there should be a corresponding volume on FlashArray :

Microsoft Windows

  1. Mount each volume for the specific database objects and provide the correct permissions

With the volumes connected to the initiator ensure that there is a file system and appropriate volume label created on each.

Each volume with a file system should be mounted to the relevant mount point or provided with a distinct drive letter.

Once mounted with a drive letter or at a specific volume ensure that the user who will own the PostgreSQL installation and service has Full control of each filesystem.

  1. Initialize the PostgreSQL cluster at the new location

To initialize a new PostgreSQL cluster the initdb application is used.

The initdb application can be found in the installation directory for PostgreSQL (default location: C:\Program Files\PostgreSQL\<Version>) in the bin directory. It can then be executed with the -D argument to initialize the data directory at a new location (note do not use the root of a filesystem). Open PowerShell and execute the following , adjusting as required with additional arguments :


& 'C:\Program Files\PostgreSQL\13\bin\initdb.exe' -D C:\PGSQL\pgcluster\Data

If everything is successful the output should be similar to the following :


The files belonging to this database system will be owned by user "DBtest".
This user must also own the server process.

The database cluster will be initialized with locale "English_United States.1252".
The default database encoding has accordingly been set to "WIN1252".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory C:/PGSQL/pgcluster/Data... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... windows
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... US/Pacific
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    ^"C^:^\Program^ Files^\PostgreSQL^\13^\bin^\pg^_ctl^" -D ^"C^:^\PGSQL^\pgcluster^\Data^\^\^" -l logfile start

  1. Change startup options and specify new data directory locations

The service used to automatically start the PostgreSQL cluster needs to be deleted and recreated with the correct command to target the new PostgreSQL data directory location. To avoid conflicts with the old data directory the existing service needs to be deleted :

In the Services management dialog the PostgreSQL cluster in this example is managed by the postgresql-x64-13 service :

This service needs to be deleted , which can be done in a command terminal like PowerShell (As an Administrator enter the cmd process) with the command :


sc.exe delete postgresql-x64-13

If successful the prompt should respond with the following message :


[SC] DeleteService SUCCESS

To recreate the service with the new settings use the following command in the same command terminal, adjusting the user (-U) and data directory (-D) as required :


& 'C:\Program Files\PostgreSQL\13\bin\pg_ctl.exe' register -N "postgresql-x64-13" -U "soln\DBTest" -D "C:\PGSQL\pgcluster\Data" -w

This recreates the windows service with the correct data directory and user startup permissions.

Before starting the service for the first time the user must have the password entered for it , so that the service can start correctly. This can be done by going into the service properties and navigating to the Log On section. In the Log On section provide the password for the user account and select OK.

  1. Alter log file and continuous archiving locations

In the data directory there should now be a postgres.conf file conbtaining relevant configuration parameters , this is where continuous archiving and the log files can be set using similar syntax to the following :

Continuous Archiving 


  archive_mode = on
  archive_command = 'cp %p C:\PGSQL\pgarchive\%f'
  wal_level = replica

Server Log file and rotation 


log_directory = 'C:\PGSQL\pglog\'
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'
log_rotation_age = 365d
log_rotation_size = 8GB
log_truncate_on_rotation = off
log_line_prefix = '%t c%

Linux

  1. Mount each volume for the specific database objects and provide the correct permissions

Ensure that each multipath device has a filesystem created for it and is mounted at the relevant locations.


Filesystem                                       Size  Used Avail Use% Mounted on
devtmpfs                                         252G     0  252G   0% /dev
tmpfs                                            252G     0  252G   0% /dev/shm
tmpfs                                            252G   11M  252G   1% /run
tmpfs                                            252G     0  252G   0% /sys/fs/cgroup
/dev/mapper/rhel-root                            630G  7.6G  623G   2% /
/dev/mapper/rhel-home                             73G  549M   72G   1% /home
/dev/dm-8                                       1014M  242M  773M  24% /boot
/dev/mapper/3624a93708488b6dac70f42a200021650p1  599M  6.9M  592M   2% /boot/efi
tmpfs                                             51G     0   51G   0% /run/user/0
/dev/mapper/3624a9370668f1ab9b15f4bc400014267    2.0T   15G  2.0T   1% /pgarchive
/dev/mapper/3624a9370668f1ab9b15f4bc400014268    4.0T   29G  4.0T   1% /pgbackup
/dev/mapper/3624a9370668f1ab9b15f4bc400014269    256G  1.9G  255G   1% /pgcluster
/dev/mapper/3624a9370668f1ab9b15f4bc40001426a     10G  104M  9.9G   2% /pglog

After each volume has been mounted in the relevant operating system it should be provided with the correct user permissions , for example in Linux the postgres user and postgres group should own the folder eacch volume is mounted to:


drwxr-xr-x     2 postgres postgres    6 Sep  6 06:37 pgarchive
drwxr-xr-x     2 postgres postgres    6 Sep  6 06:37 pgbackup
drwxr-xr-x     2 postgres postgres    6 Sep  6 06:37 pgcluster
drwxr-xr-x     2 postgres postgres    6 Sep  6 06:37 pglog

  1. Adjust startup and environment properties

The PGDATA environment variable needs to be changed so that it persists across sessions and is used correctly when starting the PostgreSQL cluster. For systemd based systems the service configuration file needs to be altered to ensure the new database directory is used.

Persistently set PGDATA environment variables

Note:

Note the PGDATA path in linux environments must not contain spaces.

The PGDATA environment variable is set in the following locations and needs to be changed :

~/.bash_profile


[ -f /etc/profile ] && source /etc/profile
PGDATA=/pgcluster/data
export PGDATA
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

(SystemD) Change database directory

(As root) Identify the service file under which PostgreSQL is started in systemd (this is the .service file found after the Loaded section) :


systemctl status postgresql-13.service[root@DB-04 data]# systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: https://www.postgresql.org/docs/13/static/

Sep 01 06:39:34 DB-04 systemd[1]: Starting PostgreSQL 13 database server...
Sep 01 06:39:34 DB-04 postmaster[3980]: 2021-09-01 06:39:34.427 PDT [3980] LOG:  redirecting log output to logging collector process
Sep 01 06:39:34 DB-04 postmaster[3980]: 2021-09-01 06:39:34.427 PDT [3980] HINT:  Future log output will appear in directory "log".
Sep 01 06:39:34 DB-04 systemd[1]: Started PostgreSQL 13 database server.
Sep 02 01:37:28 DB-04 systemd[1]: Stopping PostgreSQL 13 database server...
Sep 02 01:37:28 DB-04 systemd[1]: postgresql-13.service: Killing process 3981 (postmaster) with signal SIGKILL.
Sep 02 01:37:28 DB-04 systemd[1]: Stopped PostgreSQL 13 database server.

Edit the service file , altering the Environment=PGDATA= line :


[Unit]
Description=PostgreSQL 13 database server
Documentation=https://www.postgresql.org/docs/13/static/
After=syslog.target
After=network.target

[Service]
Type=notify

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=/pgcluster/data

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT


# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

[Install]
WantedBy=multi-user.target

Save the file and reload SystemD :


systemctl daemon-reload

(SELinux enabled systems only) - Change SELinux configuration to avoid service startup issues

Get the SElinux labeling for the default data directory with the following command:


ls -lh -Zd /var/lib/pgsql
drwx------ 3 postgres postgres system_u:object_r:postgresql_db_t:s0 data /var/lib/pgsql

Once you have the label change it with the following command:


semanage fcontext -a -t postgresql_db_t "/pgsql/data(/.*)?"

  1. Initialize the PostgreSQL cluster at the new location

The initdb application is apart of the postgresql-<version>-setup binary. This can be located in /usr/pgsql-<version>/bin. The data directory can only be initialized after changing the systemd startup object.

Before proceeding check the environment variable is correctly set through the use of the env command :


LANG=en_US.UTF-8
HISTCONTROL=ignoredups
HOSTNAME=DB-04
USER=postgres
PWD=/var/lib/pgsql
HOME=/var/lib/pgsql
PGDATA=/pgsql/data
MAIL=/var/spool/mail/postgres
SHELL=/bin/bash
TERM=xterm-256color
SHLVL=1
LOGNAME=postgres
PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
HISTSIZE=1000
LESSOPEN=||/usr/bin/lesspipe.sh %s
_=/usr/bin/env

At this point the cluster can be initialized at a new location using initdb executed as root :


/usr/pgsql-13/bin/postgresql-13-setup initdb

if successful the following output should be shown :


Initializing database ...

  1. Alter log file and continuous archiving locations

In the data directory there should now be a postgres.conf file containing relevant configuration parameters , this is where continuous archiving and the log files can be set using similar syntax to the following :

Continuous Archiving 


 archive_mode = on
 archive_command = 'cp %p /pgarchive/%f'
 wal_level = replica

Server Log file and rotation 


log_directory = '/pglog'
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'
log_rotation_age = 365d
log_rotation_size = 8GB
log_truncate_on_rotation = off
log_line_prefix = '%t c%

Separating database storage locations

This layout builds on any one of the previous architectures such as the #r_volume_and_file_system_architectural_layout_02__r_volume_and_file_system_architectural_layout_02-tbl1 , migrating existing database from default location to a different location or separating database components into isolated file systems.

Separating databases and the objects associated with those databases by volume uses one or more tablespaces. Each tablespace is created on its own volume , and then each database is created within a specific table space.

Note:

Tablespaces are cluster wide which means that multiple databases can be created on a single tablespace.

This layout suggests that a ratio of 1 database for each tablespace be maintained to take full advantage of FlashArray QoS and reporting capabilities.

This process can be performed with PostgreSQL online.

The process of separating database storage locations requires that the required FlashArray volumes are mounted and have the correct permissions set before any further steps are taken. Once the volumes are mounted and have the appropriate permissions the only requirements are to create a tablespace on one or more volumes and then to create the required databases, specifying the tablespace to be one of those created on the FlashArray volumes.

This example will create 4 databases , database01-04, on 4 separate tablespaces.

  1. Mount the volumes and set the correct permissions

A total of 5 volumes are created on FlashArray :

  • A cluster directory containing the cluster data directory and any other important objects such as the write-ahead-log
  • One volume for each database to be created

These volumes are formatted with a file system and mounted in a directory with the correct permissions(The user who owns the PostgreSQL service) assigned to the mount point :

Microsoft Windows

Linux 


Filesystem                                       1K-blocks     Used  Available Use% Mounted on
devtmpfs                                         263857112        0  263857112   0% /dev
tmpfs                                            263876116        0  263876116   0% /dev/shm
tmpfs                                            263876116    10652  263865464   1% /run
tmpfs                                            263876116        0  263876116   0% /sys/fs/cgroup
/dev/mapper/rhel-root                            660284416  7973384  652311032   2% /
/dev/mapper/3624a93708488b6dac70f42a200021650p2    1038336   250860     787476  25% /boot
/dev/mapper/rhel-home                             75878196   562088   75316108   1% /home
/dev/mapper/3624a93708488b6dac70f42a200021650p1     613184     7024     606160   2% /boot/efi
tmpfs                                             52775220        0   52775220   0% /run/user/0
/dev/mapper/3624a9370668f1ab9b15f4bc400014275   1073217536  7515756 1065701780   1% /var/lib/pgsql
/dev/mapper/3624a9370668f1ab9b15f4bc400014273   2146435072 14998272 2131436800   1% /postgres/database01
/dev/mapper/3624a9370668f1ab9b15f4bc400014274   2146435072 14998272 2131436800   1% /postgres/database02
/dev/mapper/3624a9370668f1ab9b15f4bc400014271   2146435072 14998272 2131436800   1% /postgres/database03
/dev/mapper/3624a9370668f1ab9b15f4bc400014272   2146435072 14998272 2131436800   1% /postgres/database04


drwxr-xr-x 2 postgres postgres 6 Sep  6 09:42 database01
drwxr-xr-x 2 postgres postgres 6 Sep  6 09:42 database02
drwxr-xr-x 2 postgres postgres 6 Sep  6 09:42 database03
drwxr-xr-x 2 postgres postgres 6 Sep  6 09:42 database04

  1. Create the tablespace for each volume

Using the CREATE TABLESPACE syntax the tablespace is created at a specific location. Once the tablespaces have been created using the /db+ command in psql the tablespaces and any associated properties should be viewable.

Microsoft Windows

Note:

For Microsoft Windows do not create the tablespace on the root of the volume , instead ensure there is a directory to contain the tablespace within that volume. Ensure the correct permissions are applied to that directory.

Create tablespace example :


CREATE TABLESPACE database01_tbps LOCATION 'C:\PGSQL\database01\data';
CREATE TABLESPACE database02_tbps LOCATION 'C:\PGSQL\database02\data';
CREATE TABLESPACE database03_tbps LOCATION 'C:\PGSQL\database03\data';
CREATE TABLESPACE database04_tbps LOCATION 'C:\PGSQL\database04\data';

/db+ output :


postgres=# \db+
                                             List of tablespaces
      Name       |  Owner   |         Location         | Access privileges | Options |  Size   | Description
-----------------+----------+--------------------------+-------------------+---------+---------+-------------
 database01_tbps | postgres | C:\PGSQL\database01\data |                   |         | 0 bytes |
 database02_tbps | postgres | C:\PGSQL\database02\data |                   |         | 0 bytes |
 database03_tbps | postgres | C:\PGSQL\database03\data |                   |         | 0 bytes |
 database04_tbps | postgres | C:\PGSQL\database04\data |                   |         | 0 bytes |
 pg_default      | postgres |                          |                   |         | 23 MB   |
 pg_global       | postgres |                          |                   |         | 561 kB  |
(6 rows)

Linux

Create tablespace example :


CREATE TABLESPACE database01_tbps LOCATION '/postgres/database01'; 
CREATE TABLESPACE database02_tbps LOCATION '/postgres/database02';
CREATE TABLESPACE database03_tbps LOCATION '/postgres/database03';
CREATE TABLESPACE database04_tbps LOCATION '/postgres/database04';

/db+ output :


postgres=# \db+
                                           List of tablespaces
      Name       |  Owner   |       Location       | Access privileges | Options |  Size   | Description
-----------------+----------+----------------------+-------------------+---------+---------+-------------
 database01_tbps | postgres | /postgres/database01 |                   |         | 0 bytes |
 database02_tbps | postgres | /postgres/database02 |                   |         | 0 bytes |
 database03_tbps | postgres | /postgres/database03 |                   |         | 0 bytes |
 database04_tbps | postgres | /postgres/database04 |                   |         | 0 bytes |
 pg_default      | postgres |                      |                   |         | 23 MB   |
 pg_global       | postgres |                      |                   |         | 559 kB  |
(6 rows)

  1. Create the databases , specifying the tablespace for each

Using the CREATE DATABASE syntax with the TABLESPACE property and name allows the database to be created in the specific tablespace. Using the /l+ command in psql should show the tablespace that the database is a member of.

Create database example :


CREATE DATABASE database01 TABLESPACE = database01_tbps;
CREATE DATABASE database02 TABLESPACE = database02_tbps;
CREATE DATABASE database03 TABLESPACE = database03_tbps;
CREATE DATABASE database04 TABLESPACE = database04_tbps;

/l+ output post database creation :


postgres=# \l+
                                                                       List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   |   Tablespace    |                Description

------------+----------+----------+-------------+-------------+-----------------------+---------+-----------------+------------------------------------------
--
 database01 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7753 kB | database01_tbps |
 database02 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7753 kB | database02_tbps |
 database03 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7753 kB | database03_tbps |
 database04 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7753 kB | database04_tbps |
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7901 kB | pg_default      | default administrative connection database
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7753 kB | pg_default      | unmodifiable empty database
            |          |          |             |             | postgres=CTc/postgres |         |                 |
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7753 kB | pg_default      | default template for new databases
            |          |          |             |             | postgres=CTc/postgres |         |                 |
(7 rows)