Skip to main content
Pure Technical Services

PostgreSQL on FlashArray Implementation and Best Practices

Currently viewing public documentation. Please login to access the full scope of documentation.

PostgreSQL is an open-source relational database management system (RDBMS) that can be acquired directly from postgresql.org or the GitHub mirror of the PostgreSQL GIT repository

Due to its open-source nature , there are a number of PostgreSQL forks and deployment types. Some deployment types and forks are :

  • PostgreSQL - The original open source object-relational database system.  
  • Greenplum Database - A PostgreSQL derived database licensed using Apache2 with a focus on analytics.
  • EDB Postgres Advanced Server - A commercial fork of PostgreSQL developed and supported by EnterpriseDB. Offers improved security , performance and productivity tools. 
  • Postgres Pro DBMS - A commercial fork of PostgreSQL developed and supported by Postgres Professional. Offers improved performance, scalability and maintainability options. 
  • Postgres-X2 - A symmetric PostgreSQL cluster for scale out environments. 

Hardware Environment Requirements 

Block storage on FlashArray can be accessed using a number of protocols. These protocols and their requirements are listed as follows :

Protocol Requirements
Fibre Channel Protocol (FCP) for SCSI A fiber channel fabric with an established zone between the initiator (PostgreSQL deployment system) and host (FlashArray).
Internet Protocol for SCSI (iSCSI) A high speed (Min 10Gb/s speed capability) TCP/IP network with the initiator (PostgreSQL deployment system) and target(FlashArray) having either routable or direct line-of-sight connectivity between assigned addresses. 
NVM Express over RoCEv2 Fabric (NVMe-oF/RoCEv2)

The transport for RoCEv2 uses theIPv4 user datagram protocol (UDP) which requires a lossless network. The converged Ethernet network switches are required to support industry standard congestion control mechanisms. 

The guides on how to configure the switches and topologies can be found at these locations :

The PostgreSQL deployment system will require dedicated Host Bus Adapters (HBA) which support RoCEv2. 

More information on supported hardware and configurations for NVMe-oF/RoCEv2 can be found in the NVMe-oF Support Matrix.

NVM Express over Fibre Channel Fabric (FC-NVMe)

NVMe/FC uses standard Fibre Channel mechanisms for the transport of NVMe commands. 

More information on supported hardware and configurations for NVMe/FC can be found in the NVMe-oF Support Matrix.

NVM Express over Transmission Control Protocol (NVMe/TCP)

NVMe/TCP use standard TCP mechanisms for the transport of NVMe commands. 

More information on supported hardware and configurations for NVMe/TCP can be found in the NVMe-oF Support Matrix.

Recommended Configuration for PostgreSQL on FlashArray 

Operating System Requirements

PostgreSQL can be deployed on a number of platforms. A platform consists of a CPU architecture and operating system. The current supported platforms can be found in the PostgreSQL documentation. 

The relevant operating system and associated recommendations can be found below : 

Operating System Recommendations 
Red Hat Enterprise Linux/ CentOS / Oracle Linux/Ubuntu/SUSE/Debian

The recommended settings for Linux operating systems can be found in the Linux best practices for FlashArray.

In addition to the existing best practices ensure that the number of requests per volume is set to a higher value. Increasing this value allows individual volumes to service more storage requests at any single time. 

Add the following to the udev rules file for FlashArray to increase the maximum number of requests for a single volume: 

Set DM devices number of requests to 1024 or higher. This uses 1MB of memory per request
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="3624a937*", ATTR{queue/nr_requests}="1024" 
Microsoft Windows Server  The best practices for Microsoft Windows can be set using the Test-WindowsBestPractices Cmdlet

File System Configuration 

The recommended file systems for PostgreSQL with FlashArray are NTFS (Microsoft Windows) , XFS (Linux) and EXT4 (Linux).  

Microsoft Windows 

The default allocation unit size (4K) for NTFS filesystems works well with PostgreSQL.

Linux 
XFS  

When using the XFS file system the default options are typically all that is required. 

XFS file systems can be created on a FlashArray volume using the command:

mkfs.xfs /dev/mapper/<device>

The default options in /etc/fstab should be used with XFS in the majority of cases. Using noatime can yield additional performance benifits in some scenarios. 

/dev/mapper/<device> /mountpoint xfs defaults 0 0

If using iSCSI connectivity to FlashArray ensure the _netdev,nofail options are present:

/dev/mapper/<device> /mountpoint xfs _netdev,nofail 0 0

For systems using the Linux Kernel 4.13 or later the "nobarrier" option is deprecated for XFS. To increase the possible performance of the volume add the following udev rule to the existing rules file:

FCP and iSCSI 

ACTION=="add|change", KERNEL=="sd*[!0-9]", SUBSYSTEM=="block", ENV{ID_VENDOR}=="PURE", ATTR{queue/write_cache}="write through"
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="3624a937*", ATTR{queue/write_cache}="write through"

 NVMe-oF connectivity

ACTION=="add|change", KERNEL=="nvme*[!0-9]", SUBSYSTEM=="block", ATTR{queue/write_cache}="write through"
ACTION=="add|change", KERNEL=="dm-[0-9]*", SUBSYSTEM=="block", ENV{DM_NAME}=="eui.00723ec7b5b427*", ATTR{queue/write_cache}= "write through"

Once the rules have been added reload and apply them using the udevadm utility:

 udevadm control --reload-rules && udevadm trigger
EXT4  

The EXT4 file system should be created with the "-o discard" option to enable automating unmap. 

mkfs.ext4 -o discard /dev/mapper/<device>

With EXT4 the volume can be mounted using /etc/fstab with the default options but to increase the possible performance the "noatime" and "barrier=0" options should be used. 

/dev/mapper/<device> /mountpoint ext4 noatime,barrier=0 0 0

If using iSCSI connectivity to FlashArray ensure the _netdev,nofail options are present:

/dev/mapper/<device> /mountpoint ext4 _netdev,nofail,noatime,barrier=0 0 0

Adding an entry to /etc/fstab

Entries in /etc/fstab can be done using the device path or the UUID of the filesystem.

To get the UUID of a file system use the blkid command and match the device to the respective UUID.

[root@DB-01 ~]# blkid
/dev/sdb: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sda: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sdc: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/mapper/3624a93708488b6dac70f42a20001ec55: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/sdd: PTUUID="91360acd-7332-47d9-9027-0300c7e3a081" PTTYPE="gpt"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p1: UUID="D759-B5A6" BLOCK_SIZE="512" TYPE="vfat" PARTLABEL="EFI System Partition" PARTUUID="8f317ff7-97f5-48ab-83d8-f54d647fb390"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p2: UUID="97d13054-20c7-436e-b6ab-ef8b8f3ce46b" BLOCK_SIZE="512" TYPE="xfs" PARTUUID="2644a548-dde5-4ac8-8679-c100011a0d78"
/dev/mapper/3624a93708488b6dac70f42a20001ec55p3: UUID="GXujto-h3yl-V3hq-jFZs-3xd6-RhLB-0vFf8l" TYPE="LVM2_member" PARTUUID="380fbf39-eb6e-4bf0-ae25-f4abd6f02803"
/dev/mapper/rhel-root: UUID="5547b1a3-590a-4d54-9d0b-710714ca7e52" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/rhel-swap: UUID="70470107-9368-4d32-82d8-7620f1cdc665" TYPE="swap"
/dev/mapper/rhel-home: UUID="0e08dae8-3d93-451c-a021-b3fe58f9b464" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme0n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011884: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme1n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme2n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n1: UUID="36202f29-95e8-49d2-a200-4057712b9236" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n2: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n3: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n4: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/nvme3n5: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011890: UUID="489e7195-46b5-466a-8770-9aa7938c8afb" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c400011898: UUID="0f4da946-a86e-4fd3-bcd3-0d74d88ef83b" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c4000118a0: UUID="9a5e14cc-b226-4f7c-91a5-f8397a73b5bd" BLOCK_SIZE="512" TYPE="xfs"
/dev/mapper/eui.00668f1ab9b15f4b24a937c4000118a1: UUID="41e4a009-b98a-4d48-9d69-f45e717485e2" BLOCK_SIZE="512" TYPE="xfs"

 Then use the UUID in place of the device path (works for both ext4 and XFS):

UUID=489e7195-46b5-466a-8770-9aa7938c8afb /mountpoint ext4 _netdev,nofail,noatime,barrier=0 0 0

Volume and File System Architectural Layout 

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

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 

clipboard_e8e0b20d14c9d8c9725fc9b67f3aad30d.png clipboard_e0bb135ba2d7e7f2b275d92c8b8a79e05.png
EnterpriseDB Advanced Server clipboard_e53ccfea1f98ff190ef3882062007dc32.png clipboard_e9302d33cba06b402c400db3f2b8f5fe5.png

 

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. 

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. 

clipboard_e4f0dfa121a68cb9a96e41c70219500b6.png

2. 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
3. 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 :

clipboard_ece7e98f4ce31f9ed54866281a18f09fe.png

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. 

clipboard_e2efba4abd23f72f60daa2c7fa497711b.png

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. 

clipboard_e76db2d9c24477fb3de990294a8fb05bf.png

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
2. 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 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(/.*)?"
3.  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. 

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

Microsoft Windows Linux
clipboard_efd29357c51901d246dd56f1160db442d.png clipboard_e69996481c1e70707a8ed1b6c7543cee5.png

 

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. 

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\ :

clipboard_e5c60e8921bb5e57bbba6a5c45e293a92.png

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

clipboard_edd09d8512ae6010aba94342983f4e6ee.png

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 : 

clipboard_e113f6f76509dc99e6fb6eb9007a04edb.png

 

2. Stop the PostgreSQL cluster

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

clipboard_e98f33c4f0ebe2ab5965d01b0b6b687b4.png

3. 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
4. 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 :

clipboard_ece7e98f4ce31f9ed54866281a18f09fe.png

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. 

clipboard_e2efba4abd23f72f60daa2c7fa497711b.png

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. 

clipboard_e76db2d9c24477fb3de990294a8fb05bf.png

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
2. Stop the PostgreSQL cluster

In a terminal type the following command:

sudo systemctl stop postgresql-13.service
3. 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/ 
4. 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 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 :

clipboard_e4423fd722f18384ae097efaf1b2995a5.png

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. 

clipboard_ef45733480a43463a73f66b29e48d2821.png

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

clipboard_e92ffa14d3f15e9b2e1ab2fd2b66e1c3f.png

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. 

clipboard_e6ee9eeb00f9c744b0d96b9c84d1c4019.png

 

2. 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
3. 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 :

clipboard_ece7e98f4ce31f9ed54866281a18f09fe.png

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. 

clipboard_e2efba4abd23f72f60daa2c7fa497711b.png

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. 

clipboard_e76db2d9c24477fb3de990294a8fb05bf.png

4. 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
2. 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 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(/.*)?"
3.  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 ... 
4. 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 default layout , 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. 

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 

clipboard_ebb649c045e37f6f070cf75e2b90801c4.png

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 

clipboard_ee14ccc393ab730b5bcd51057ef593977.png

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
2. 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 

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)
3. 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)

PostgreSQL Parameters

Below are the recommendations for each parameter found in the PostgreSQL configuration file (postgresql.conf)

Parameter

Description

Recommended Value

shared_buffers

This is the most important parameter which impacts the performance directly. It basically sets the amount of memory the database server uses for shared memory buffers.

25-40% of server memory. 

**max_wal_size

This parameter sets the WAL to grow to between checkpoints. Where shared_buffers is set to higher values , max_wal_size should be increased as well.

10GB-100GB

huge_pages

With huge pages set in memory it will create 2MB pages and not the standard 4KB page size. This improves virtual memory management where the kernel keeps a table containing a mapping of virtual memory addresses to physical addresses. With each transaction, the kernel needs to load related mapping and if the page size is small then lot of pages must be loaded. This causes a decrease in performance and thus it is recommended to set hub_pages to on. 

on

wal_level

This sets the amount of information written to the write-ahead-log. 

Performance : "minimal"

Data Integrity : "replica"

synchronous_commit

This parameter specifies whether each transaction or WAL records are to be written to disk before the command returns success to the client. 

Performance : "off"

Data Integrity : "on"

**checkpoint_timeout

This parameter controls the maximum time between automatic checkpoints. 

For production it is recommended to set for 5 minutes. For performance-based systems set it to 30 minutes or more.

Performance : > 30 minutes

Data Integrity : < 5 minutes

 

Check_point_completion target

This parameter specifies the time spent flushing dirty buffers during checkpoint. Setting this will slow down the checkpoint IO as checkpoints will take longer.

0.2-0.5.

effective _io_concurrency

This sets the number of concurrent disk I/O operations in PostgreSQL. Increasing this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel. Increasing this value can increase query performance. 

256-512

wal_sync_method

This parameter determines which method to use for WAL updates out to disk. 

fdatasync() 

Max_worker_processes

Max_parallel_workers

These parameters help PostgreSQL to scale vertically, especially when parallel queries are run.  It is best to keep both the parameters to the same value.

Set to the same as the number of physical cores in the database server. 

wal_compression

This parameter controls the compression of full-page writes written ito the write-ahead-log. 

off