PostgreSQL on FlashArray Implementation and Best Practices
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 layoutAll 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.
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 locationThis 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 WindowsIn 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 initializationWhen 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. 2. Initialize the PostgreSQL cluster at the new locationTo 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 locationsThe 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. LinuxThe 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 initializationThis 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 propertiesThe 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 locationThe 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 locationThe 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.
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 WindowsThis 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 migrationPermissions 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 :
2. Stop the PostgreSQL clusterIn the Services Management Console identify the service responsible for PostgreSQL , right click on it and select Stop. 3. Copy the contents of the data directory to the new location , preserving existing permissionsIn 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 locationsThe 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. Linux1. Set the permissions on destination prior to migrationThe 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 clusterIn 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 permissionsIn 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 propertiesThe 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 systemsThis 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 :
For each physical location there should be a corresponding volume on FlashArray : Microsoft Windows1. Mount each volume for the specific database objects and provide the correct permissionsWith 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.
2. Initialize the PostgreSQL cluster at the new locationTo 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 locationsThe 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. 4. Alter log file and continuous archiving locationsIn 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% Linux1. Mount each volume for the specific database objects and provide the correct permissionsEnsure 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 propertiesThe 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 locationThe 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 locationsIn 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 locationsThis 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 permissionsA total of 5 volumes are created on FlashArray :
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 2. Create the tablespace for each volumeUsing 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 eachUsing 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 |