PGSQL目录结构

PostgreSQL所使用的目录层次结构

Postgres目录结构

以下参数与PostgreSQL数据库目录相关

  • pg_dbsu_home：Postgres默认用户的家目录，默认为/var/lib/pgsql
  • pg_bin_dir：Postgres二进制目录，默认为/usr/pgsql/bin/
  • pg_data：Postgres数据库目录，默认为/pg/data
  • pg_fs_main：Postgres主数据盘挂载点，默认为/export
  • pg_fs_bkup：Postgres备份盘挂载点，默认为/var/backups（可选，也可以选择备份到主数据盘上的子目录）
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二进制目录结构

在RedHat/CentOS上，默认的Postgres发行版安装位置为

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

安装剧本会自动创建指向当前安装版本的软连接，例如，如果安装了14版本的Postgres，则有：

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

因此，默认的pg_bin_dir为/usr/pgsql/bin/，该路径会在/etc/profile.d/pgsql.sh中添加至所有用户的PATH环境变量中。

PG数据目录结构

Pigsty假设用于部署数据库实例的单个节点上至少有一块主数据盘（pg_fs_main），以及一块可选的备份数据盘（pg_fs_bkup）。通常主数据盘是高性能SSD，而备份盘是大容量廉价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数据库集簇目录结构

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配置文件结构

Pgbouncer使用Postgres用户运行，配置文件位于/etc/pgbouncer。配置文件包括：

  • pgbouncer.ini，主配置文件
  • userlist.txt：列出连接池中的用户
  • pgb_hba.conf：列出连接池用户的访问权限
  • database.txt：列出连接池中的数据库

最后修改 2022-06-05: add pgsql/deploy document (34a3325)