PGSQL File Hierarchy Structure

PostgreSQL File Hierarchy Structure

Postgres FHS

The following parameters are related to the PostgreSQL database dir:

  • pg_dbsu_home: Postgres default user’s home dir, default is /var/lib/pgsql.
  • pg_bin_dir: Postgres binary dir, defaults to /usr/pgsql/bin/.
  • pg_data: Postgres database dir, default is /pg/data.
  • pg_fs_main: Postgres main data disk mount point, default is /export.
  • pg_fs_bkup: Postgres backup disk mount point, default is /var/backups (optional, you can also choose to backup to a subdirectory on the primary data disk).
  1. #------------------------------------------------------------------------------
  2. # Create Directory
  3. #------------------------------------------------------------------------------
  4. # this assumes that
  5. #   /pg is shortcut for postgres home
  6. #   {{ pg_fs_main }} contains the main data             (MUST ALREADY MOUNTED)
  7. #   {{ pg_fs_bkup }} contains archive and backup data   (MUST ALREADY MOUNTED)
  8. #   cluster-version is the default parent folder for pgdata (e.g pg-test-12)
  9. #------------------------------------------------------------------------------
  10. # default variable:
  11. #     pg_fs_main = /export           fast ssd
  12. #     pg_fs_bkup = /var/backups      cheap hdd
  13. #
  14. #     /pg      -> /export/postgres/pg-test-12
  15. #     /pg/data -> /export/postgres/pg-test-12/data
  16. #------------------------------------------------------------------------------
  17. - name: Create postgresql directories
  18.   tags: pg_dir
  19.   become: yes
  20.   block:
  21.     - name: Make sure main and backup dir exists
  22.       file: path={{ item }} state=directory owner=root mode=0777
  23.       with_items:
  24.         - "{{ pg_fs_main }}"
  25.         - "{{ pg_fs_bkup }}"
  27.     # pg_cluster_dir:    "{{ pg_fs_main }}/postgres/{{ pg_cluster }}-{{ pg_version }}"
  28.     - name: Create postgres directory structure
  29.       file: path={{ item }} state=directory owner={{ pg_dbsu }} group=postgres mode=0700
  30.       with_items:
  31.         - "{{ pg_fs_main }}/postgres"
  32.         - "{{ pg_cluster_dir }}"
  33.         - "{{ pg_cluster_dir }}/bin"
  34.         - "{{ pg_cluster_dir }}/log"
  35.         - "{{ pg_cluster_dir }}/tmp"
  36.         - "{{ pg_cluster_dir }}/conf"
  37.         - "{{ pg_cluster_dir }}/data"
  38.         - "{{ pg_cluster_dir }}/meta"
  39.         - "{{ pg_cluster_dir }}/stat"
  40.         - "{{ pg_cluster_dir }}/change"
  41.         - "{{ pg_backup_dir }}/postgres"
  42.         - "{{ pg_backup_dir }}/arcwal"
  43.         - "{{ pg_backup_dir }}/backup"
  44.         - "{{ pg_backup_dir }}/remote"

PG Binary FHS

On RedHat/CentOS, the default installation location for the Postgres distribution is:

  1. /usr/pgsql-${pg_version}/

The installation playbook automatically creates a soft link to the currently installed version. For example, if version 14 of Postgres is installed, there are.

  1. /usr/pgsql -> /usr/pgsql-14

Therefore, the default pg_bin_dir is /usr/pgsql/bin/, and this path is added to the PATH environment variable for all users in /etc/profile.d/pgsql.sh.

PG Data FHS

Pigsty assumes at least one primary data disk (pg_fs_main) and an optional backup data disk (pg_fs_bkup) on the single node used to deploy the database instance. Usually, the primary data disk is a high-performance SSD, while the backup disk is a high-capacity inexpensive HDD.

  1. #------------------------------------------------------------------------------
  2. # Create Directory
  3. #------------------------------------------------------------------------------
  4. # this assumes that
  5. #   /pg is shortcut for postgres home
  6. #   {{ pg_fs_main }} contains the main data             (MUST ALREADY MOUNTED)
  7. #   {{ pg_fs_bkup }} contains archive and backup data   (MAYBE ALREADY MOUNTED)
  8. #   {{ pg_cluster }}-{{ pg_version }} is the default parent folder 
  9. #    for pgdata (e.g pg-test-14)
  10. #------------------------------------------------------------------------------
  11. # default variable:
  12. #     pg_fs_main = /export           fast ssd
  13. #     pg_fs_bkup = /var/backups      cheap hdd
  14. #
  15. #     /pg      -> /export/postgres/pg-test-14
  16. #     /pg/data -> /export/postgres/pg-test-14/data

PG Cluster FHS

  1. # basic
  2. {{ pg_fs_main }}     /data                      # contains all business data (pg,consul,etc..)
  3. {{ pg_dir_main }}    /data/postgres             # contains postgres main data
  4. {{ pg_cluster_dir }} /data/postgres/pg-test-14  # contains cluster `pg-test` data (of version 13)
  5.                      /data/postgres/pg-test-14/bin            # binary scripts
  6.                      /data/postgres/pg-test-14/log            # misc logs
  7.                      /data/postgres/pg-test-14/tmp            # tmp, sql files, records
  8.                      /data/postgres/pg-test-14/conf           # configurations
  9.                      /data/postgres/pg-test-14/data           # main data directory
  10.                      /data/postgres/pg-test-14/meta           # identity information
  11.                      /data/postgres/pg-test-14/stat           # stats information
  12.                      /data/postgres/pg-test-14/change         # changing records
  14. {{ pg_fs_bkup }}     /var/backups                      # contains all backup data (pg,consul,etc..)
  15. {{ pg_dir_bkup }}    /var/backups/postgres             # contains postgres backup data
  16. {{ pg_backup_dir }}  /var/backups/postgres/pg-test-14  # contains cluster `pg-test` backup (of version 13)
  17.                      /var/backups/postgres/pg-test-14/backup   # base backup
  18.                      /var/backups/postgres/pg-test-14/arcwal   # WAL archive
  19.                      /var/backups/postgres/pg-test-14/remote   # mount NFS/S3 remote resources here
  21. # links
  22. /pg             -> /data/postgres/pg-test-14                 # pg root link
  23. /pg/data        -> /data/postgres/pg-test-14/data            # real data dir
  24. /pg/backup      -> /var/backups/postgres/pg-test-14/backup   # base backup
  25. /pg/arcwal      -> /var/backups/postgres/pg-test-14/arcwal   # WAL archive
  26. /pg/remote      -> /var/backups/postgres/pg-test-14/remote   # mount NFS/S3 remote resources here

Pgbouncer FHS

Pgbouncer is run using the Postgres user, and the config file is located in /etc/pgbouncer. The config file includes.

  • pgbouncer.ini: the main config file
  • userlist.txt: lists the users in the connection pool
  • pgb_hba.conf: lists the access privileges of the connection pool users
  • database.txt: lists the databases in the connection pool

Last modified 2022-06-04: fii en docs batch 2 (61bf601)