centos7-postgreSQL-12
一、简介
PostgreSQL是一个功能强大的开源的关系型数据库，底层基于c实现
PostgreSQL社区特别活跃，基本是三个月发一版，允许跨版本升级
PostgreSQL的官方地址：https://www.postgresql.org
PostgreSQL的国内社区：http://www.postgres.cn/v2/home以下基于12.x稳定版本安装配置,centos7.8
二、安装
# 下载rpm包
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装postgresql，postgresql12-contrib附加功能包
yum install -y postgresql12-server postgresql12-contrib
# 初始化数据库
/usr/pgsql-12/bin/postgresql-12-setup initdb
# 启动并设置开机自启
systemctl enable postgresql-12 --now
三、 验证
# postgreSQL不推荐使用root管理，在安装postgreSQL后，他默认会给你创建一个用户：postgres，无密码，可以修改密码：ALTER USER postgres WITH PASSWORD '新密码';
● 以超级用户身份登录到postgreSQL数据库： sudo -u postgres psql
● 查看有那些库： \l，如下，有三个库，分别是postgres，template0, template1
● 退出postgreSQL： \q
[root@node1 rhd]# sudo -u postgres psql
psql (12.19)
输入 "help" 来获取帮助信息.
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)
# 注意： 先切换到： su postgres,再通过命令进入：psql，也是ok的
四、配置
[root@node1 rhd]# ll /var/lib/pgsql/12/data/
总用量 60
drwx------ 5 postgres postgres 41 6月 4 08:45 base
-rw------- 1 postgres postgres 30 6月 4 08:46 current_logfiles
drwx------ 2 postgres postgres 4096 6月 4 08:47 global
drwx------ 2 postgres postgres 32 6月 4 08:46 log
drwx------ 2 postgres postgres 6 6月 4 08:45 pg_commit_ts
drwx------ 2 postgres postgres 6 6月 4 08:45 pg_dynshmem
-rw------- 1 postgres postgres 4516 6月 4 08:45 pg_hba.conf
-rw------- 1 postgres postgres 1636 6月 4 08:45 pg_ident.conf
drwx------ 4 postgres postgres 68 6月 4 09:54 pg_logical
drwx------ 4 postgres postgres 36 6月 4 08:45 pg_multixact
drwx------ 2 postgres postgres 18 6月 4 08:46 pg_notify
drwx------ 2 postgres postgres 6 6月 4 08:45 pg_replslot
drwx------ 2 postgres postgres 6 6月 4 08:45 pg_serial
drwx------ 2 postgres postgres 6 6月 4 08:45 pg_snapshots
drwx------ 2 postgres postgres 63 6月 4 09:54 pg_stat
drwx------ 2 postgres postgres 6 6月 4 09:54 pg_stat_tmp
drwx------ 2 postgres postgres 18 6月 4 08:45 pg_subtrans
drwx------ 2 postgres postgres 6 6月 4 08:45 pg_tblspc
drwx------ 2 postgres postgres 6 6月 4 08:45 pg_twophase
-rw------- 1 postgres postgres 3 6月 4 08:45 PG_VERSION
drwx------ 3 postgres postgres 60 6月 4 08:45 pg_wal
drwx------ 2 postgres postgres 18 6月 4 08:45 pg_xact
-rw------- 1 postgres postgres 88 6月 4 08:45 postgresql.auto.conf
-rw------- 1 postgres postgres 26736 6月 4 08:45 postgresql.conf
-rw------- 1 postgres postgres 58 6月 4 08:46 postmaster.opts
# 从上图可以看到，postgreSQL的核心文件，都属于postgres用户，所以，操作的时候，尽可能的别用root用户，容易出错，尽可能先切换到postgres用户，再去操作。
注意操作的是postgresql.conf 和 pg_hba.conf
4.1 配置postgresql.conf
postgresql.conf 是postgreSQL的主要配置文件，它包含了许多数据库服务器的全局设置选项。通过修改这个文件，您可以配置数据库服务器的各种参数，如监听地址、端口号、最大连接数、日志记录等。这个文件的修改通常需要重启数据库服务器才能生效。
修改/var/lib/pgsql/12/data/postgresql.conf配置文件，初始配置如下：
#listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 5432 # (change requires restart)默认情况下，PostgreSQL只允许localhost连接，直接配置为*即可
#listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 5432 # (change requires restart)
4.2 配置pg_hba.conf
pg_hba.conf是PostgreSQL的身份验证配置文件，它定义了那些客户端可以连接到数据库服务器以及如何进行身份验证。通过修改这个文件，您可以控制访问数据库的权限，配置不同用户的身份验证方法，以及定义访问控制规则。修改pg_hba.conf文件后，通常需要重新加载配置或重启数据库服务器才能使用更改生效。
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 ident host replication all ::1/128 ident模板参数说明：
● type: local代表本地连接，host代表可以指定连接的ADDRESS
● database： 数据库名，如果写all,代表所有库都可以连接
● user：连接的用户，可以写all，代表所有用户
● address：代表那些IP地址可以连接
● method：加密方式，一般直接md5我们安装上面的模板定义一个，支持远程用户访问，如下：
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 ident host all all 0.0.0.0/0 md5 # 添加该行 # IPv6 local connections: host all all ::1/128 ident # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 ident host replication all ::1/128 ident # 注意：配置尽量写在前面，因为是从上往下匹配的重启
systemctl restart postgresql-12.service
4.3 配置数据库的日志
/var/lib/pgsql/12/data/postgresql.conf,里面有关于日志的配置，如下：
#------------------------------------------------------------------------------ # REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - log_destination = 'stderr' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # (change requires restart) # These are only used if logging_collector is on: log_directory = 'log' # directory where log files are written, # can be absolute or relative to PGDATA log_filename = 'postgresql-%a.log' # log file name pattern, # can include strftime() escapes #log_file_mode = 0600 # creation mode for log files, # begin with 0 to use octal notation log_truncate_on_rotation = on # If on, an existing log file with the # same name as the new log file will be # truncated rather than appended to. # But such truncation only occurs on # time-driven rotation, not on restarts # or size-driven rotation. Default is # off, meaning append to existing files # in all cases. log_rotation_age = 1d # Automatic rotation of logfiles will # happen after that time. 0 disables. log_rotation_size = 0 # Automatic rotation of logfiles will # happen after that much log output. # 0 disables.PostgreSQL默认情况下，只保存7天的日志，循环覆盖，默认参数如下:
# 代表日志是开启的。 logging_collector = on # 日志存放的路径，默认放到当前目录下的log里 log_directory = 'log' # 日志的文件名，默认是postgresql为前缀，%a表示把星期作为后缀 log_filename = 'postgresql-%a.log' # 日志文件会被覆盖 log_truncate_on_rotation = on # 一天一个日志文件 log_rotation_age = 1d # 日志文件，没有大小限制 log_rotation_size = 0
五、配置数据目录
PostgreSQL数据库安装完毕后，须要进行初始化才能使用。其中，对于数据库的数据目录初始化有两种方式：
第一种是，初始化数据库时指定数据库的数据目录
第二种是，初始化时没有指定数据库的数据目录，后期进行数据库的数据目录迁移。
5.1 安装时指定(修改)数据库数据目录
● 如果数据库安装完毕后，还未初始化数据库，也未启动数据库，则可直接使用如下方式来指定数据库的数据目录，不再需要做迁移工作。
● 使用如下命令，首先在数据分区目录创建存储PG数据库的数据目录"/mnt/rhd/data/pgsql/12/data",然后赋予postgres相应的权限。
mkdir /mnt/rhd/data/pgsql/12/data -p chown -R postgres:postgres /mnt/rhd/data/pgsql chmod -R 700 /mnt/rhd/data/pgsql● PG数据库数据目录创建完毕后，切换到postgress用户工作环境，使用initdb程序，指定数据库目录，初始化数据库
su - postgres cd /usr/pgsql-12/bin/ ./initdb -D /mnt/rhd/data/pgsql/12/data解下来根据5.3后续步骤安装剩余配置
5.2 安装后迁移数据库数据目录
如果PG数据库已经安装完毕，并使用了默认参数初始化数据库，处于日常正常使用状态时，向迁移数据库的数据目录，则须要进行如下7个步骤的操作。与第一种方式的不同点，主要体现在，创建的数据分区目录不同，此外，需要递归复制数据库旧的目录下下的文件到新的新的目录下
5.2.1 创建新的数据目录
mkdir /mnt/rhd/data/pgsql -p
5.2.2 停止PostgreSQL数据库服务
systemctl stop postgresql-12.service
5.2.3 拷贝postgresSQL数据目录到新目录，并赋予权限
rsync -ar /var/lib/pgsql/* -t /mnt/rhd/data/pgsql/
chown -R postgres:postgres /mnt/rhd/data/pgsql
chmod -R 700 /mnt/rhd/data/pgsql/
5.2.4 修改系统服务中PostgreSQL数据库的Environment配置
vim /usr/lib/systemd/system/postgresql-12.service
# 将以下配置修改
#Environment=PGDATA=/var/lib/pgsql/12/data/
Environment=PGDATA=/mnt/rhd/data/pgsql/12/data
5.2.5 重新读取PostgreSQL的系统服务配置
systemctl daemon-reload
5.2.6 修改PostgreSQL数据库软件级配置文件
修改监听地址
/mnt/rhd/data/pgsql/12/data/postgresql.conf listen_addresses = '*' # what IP address(es) to listen on;修改 ipv4访问方式
# 在IPV4处新增 /mnt/rhd/data/pgsql/12/data/pg_hba.conf host all all 0.0.0.0/0 md5
5.2.7 设置PostgreSQL数据库密码
# 切换到postgres用户工作环境，使用psql程序，进入pg数据库的shell环境，从而使用\password命令设置新的密码 su - postgres cd /usr/pgsql-12/bin/ ./psql \password [输入您的新密码] [再次输入，确认新密码]
5.3 重启数据库
systemctl restart postgresql-12.service
六、postgreSQL操作命令
# 进入数据库
/usr/pgsql-12/bin/psql -U postgres -p 5432 -d postgres -h 192.168.150.60
# 查询现有数据库
postgres=# \l
# 进入某个数据库
postgres=# \c gitlab
您现在已经连接到数据库 "gitlab",用户 "postgres".
# 查看数据库中的表
gitlab=# \d
# 查看表的结构
gitlab=# \d users
# 查询表中的数据
gitlab=# SELECT * from users;
七、备库配置
# 两台服务器配置密钥登录，以下配置按需修改
# 安装
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql12-server postgresql12-contrib
# 同步数据，-U 用户， -D 数据目录 ， -h 主机名
[root@node ~]# pg_basebackup -h master -U postgres -D /var/lib/pgsql/12/data/ -X stream -P
# 修改权限
chown -R postgres /var/lib/pgsql/12/data/
# 添加配置文件
[root@node ~]# tail /var/lib/pgsql/12/data/postgresql.conf
# Add settings for extensions here
# 连接主库同步，12版本后将recovery文件整合到postgresql.conf中
recovery_target_timeline = 'latest'
primary_conninfo = 'host=192.168.150.61 port=5432 user=postgres password=gitlab@123'
promote_trigger_file = 'failover.now'
# 创建文件，指定该服务器为备库(有该文件表示是备库)
touch /var/lib/pgsql/12/data/standby.signal
# 启动服务
systemctl start postgresql-12.service
# 主库创建测试，备库是否同步
[root@master ~]# su - postgres
上一次登录：四 6月 13 10:32:20 CST 2024pts/0 上
-bash-4.2$ psql
psql (12.19)
输入 "help" 来获取帮助信息.
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)
postgres=# create database test1;
CREATE DATABASE
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(4 行记录)
# 备库
[root@node ~]# su - postgres
-bash-4.2$ \l
-bash: l: 未找到命令
-bash-4.2$ psql
psql (12.19)
输入 "help" 来获取帮助信息.
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(4 行记录)
八、配置文件
# 主库，(按照自己的需要修改参数)
[root@master ~]# egrep -v "^#|^$" /var/lib/pgsql/12/data/postgresql.conf
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
max_connections = 200 # (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# 0 selects the system default
# 0 selects the system default
# 0 selects the system default
# 0 selects the system default
shared_buffers = 128MB # min 128kB
# (change requires restart)
# (change requires restart)
# (change requires restart)
# supported by the operating system:
# mmap
# sysv
# windows
# (change requires restart)
dynamic_shared_memory_type = posix # the default is the first option
# supported by the operating system:
# posix
# sysv
# windows
# mmap
# (change requires restart)
# in kB, or -1 for no limit
# (change requires restart)
# can be used in parallel operations
# (change requires restart)
wal_level = replica # minimal, replica, or logical
# (change requires restart)
# (turning this off can cause
# unrecoverable data corruption)
# off, local, remote_write, remote_apply, or on
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux and FreeBSD)
# fsync
# fsync_writethrough
# open_sync
wal_log_hints = on # also do full page writes of non-critical updates
# (change requires restart)
# (change requires restart)
max_wal_size = 1GB
min_wal_size = 80MB
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp "%p" "/var/lib/pgsql/12/backups/%f"' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
# number of seconds; 0 disables
# placeholders: %p = path of file to restore
# %f = file name only
# e.g. 'cp /mnt/server/archivedir/%f %p'
# (change requires restart)
# consistent state is reached
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# just after the specified recovery target (on)
# just before the recovery target (off)
# (change requires restart)
# (change requires restart)
# (change requires restart)
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
max_replication_slots = 10 # max number of replication slots
# (change requires restart)
# (change requires restart)
# method to choose sync standbys, number of sync standbys,
# and comma-separated list of application_name
# from standby(s); '*' = all
# (change requires restart)
# (change requires restart)
hot_standby = on # "off" disallows queries during recovery
# (change requires restart)
# when reading WAL from archive;
# -1 allows indefinite delay
# when reading streaming WAL;
# -1 allows indefinite delay
# 0 disables
# query conflicts
# communication from master
# in milliseconds; 0 disables
# retrieve WAL after a failed attempt
# (change requires restart)
# and query more expensive than this;
# -1 disables
# more expensive than this; -1 disables
# query is more expensive than this;
# -1 disables
# JOIN clauses
# force_custom_plan
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
log_directory = 'log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# are logged regardless of their duration. 1.0 logs all
# statements from all transactions, 0.0 never logs.
log_line_prefix = '%m [%p] ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'Asia/Shanghai'
# (change requires restart)
# requires track_counts to also be on.
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
# (change requires restart)
# vacuum
# analyze
# (change requires restart)
# before forced vacuum
# (change requires restart)
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
# autovacuum, -1 means use
# vacuum_cost_limit
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
# only default tablespace
# before index cleanup, 0 always performs
# index cleanup
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
# abbreviations. Currently, there are
# Default
# Australia (historical usage)
# India
# You can create your own file in
# share/timezonesets/.
# selects precise output mode
# encoding
lc_messages = 'zh_CN.UTF-8' # locale for system error message
# strings
lc_monetary = 'zh_CN.UTF-8' # locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8' # locale for number formatting
lc_time = 'zh_CN.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.simple'
# (change requires restart)
# (change requires restart)
# (max_pred_locks_per_transaction
# / -max_pred_locks_per_relation) - 1
# data?
# (change requires restart)
# a directory, e.g., 'conf.d'
# 备库
[root@node ~]# egrep -v "^#|^$" /var/lib/pgsql/12/data/postgresql.conf
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
max_connections = 200 # (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# 0 selects the system default
# 0 selects the system default
# 0 selects the system default
# 0 selects the system default
shared_buffers = 128MB # min 128kB
# (change requires restart)
# (change requires restart)
# (change requires restart)
# supported by the operating system:
# mmap
# sysv
# windows
# (change requires restart)
dynamic_shared_memory_type = posix # the default is the first option
# supported by the operating system:
# posix
# sysv
# windows
# mmap
# (change requires restart)
# in kB, or -1 for no limit
# (change requires restart)
# can be used in parallel operations
# (change requires restart)
wal_level = replica # minimal, replica, or logical
# (change requires restart)
# (turning this off can cause
# unrecoverable data corruption)
# off, local, remote_write, remote_apply, or on
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux and FreeBSD)
# fsync
# fsync_writethrough
# open_sync
wal_log_hints = on # also do full page writes of non-critical updates
# (change requires restart)
# (change requires restart)
max_wal_size = 1GB
min_wal_size = 80MB
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp "%p" "/var/lib/pgsql/12/backups/%f"' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
# number of seconds; 0 disables
# placeholders: %p = path of file to restore
# %f = file name only
# e.g. 'cp /mnt/server/archivedir/%f %p'
# (change requires restart)
# consistent state is reached
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# just after the specified recovery target (on)
# just before the recovery target (off)
# (change requires restart)
# (change requires restart)
# (change requires restart)
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
max_replication_slots = 10 # max number of replication slots
# (change requires restart)
# (change requires restart)
# method to choose sync standbys, number of sync standbys,
# and comma-separated list of application_name
# from standby(s); '*' = all
# (change requires restart)
# (change requires restart)
hot_standby = on # "off" disallows queries during recovery
# (change requires restart)
# when reading WAL from archive;
# -1 allows indefinite delay
# when reading streaming WAL;
# -1 allows indefinite delay
# 0 disables
# query conflicts
# communication from master
# in milliseconds; 0 disables
# retrieve WAL after a failed attempt
# (change requires restart)
# and query more expensive than this;
# -1 disables
# more expensive than this; -1 disables
# query is more expensive than this;
# -1 disables
# JOIN clauses
# force_custom_plan
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
log_directory = 'log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# are logged regardless of their duration. 1.0 logs all
# statements from all transactions, 0.0 never logs.
log_line_prefix = '%m [%p] ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'Asia/Shanghai'
# (change requires restart)
# requires track_counts to also be on.
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
# (change requires restart)
# vacuum
# analyze
# (change requires restart)
# before forced vacuum
# (change requires restart)
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
# autovacuum, -1 means use
# vacuum_cost_limit
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
# only default tablespace
# before index cleanup, 0 always performs
# index cleanup
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
# abbreviations. Currently, there are
# Default
# Australia (historical usage)
# India
# You can create your own file in
# share/timezonesets/.
# selects precise output mode
# encoding
lc_messages = 'zh_CN.UTF-8' # locale for system error message
# strings
lc_monetary = 'zh_CN.UTF-8' # locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8' # locale for number formatting
lc_time = 'zh_CN.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.simple'
# (change requires restart)
# (change requires restart)
# (max_pred_locks_per_transaction
# / -max_pred_locks_per_relation) - 1
# data?
# (change requires restart)
# a directory, e.g., 'conf.d'
recovery_target_timeline = 'latest'
primary_conninfo = 'host=192.168.150.61 port=5432 user=postgres password=gitlab@123'
promote_trigger_file = 'failover.now'