我的书签
添加书签
移除书签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 fileuserlist.txt: lists the users in the connection poolpgb_hba.conf: lists the access privileges of the connection pool usersdatabase.txt: lists the databases in the connection pool
Last modified 2022-06-04: fii en docs batch 2 (61bf601)
