PostgreSQL10安装部署和初始化

发表于 | 更新于: | 阅读次数:
字数统计: 3.1k

说明

  • 仅记录安装部署和初始化过程
  • 操作系统使用的CentOS-7.6.1810 x86_64
  • PostgreSQL版本号为10.8
  • 虚拟机配置1CPU 2G内存 20G系统盘 30G数据盘
  • 【根据自己的环境灵活调整，生产环境请务必提供独立数据盘！】

环境准备

关闭防火墙

1
systemctl disable --now firewalld.service

关闭SELINUX

1
2
setenforce 0
sed -e 's,^SELINUX=.*,SELINUX=disabled,' -i /etc/selinux/config

sysctl参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
cat > /etc/sysctl.d/99-pgsql.conf <<EOF
# 表示最大限度使用物理内存，物理内存用满再用swap
vm.swappiness = 0
# 端口最大的监听队列的长度，默认值128
net.core.somaxconn=4096
# 最大文件句柄数
fs.file-max = 1048576
# 最大文件打开数
fs.nr_open = 1048576
# 单个共享内存段的最大值，这里设置为物理内存大小的一半
# 计算方式 2048 / 2 * 1024 * 1024 = 1073741824
kernel.shmmax = 1073741824
# kernel.sem对应四个值分别为SEMMSL、SEMMNS、SEMOPM、SEMMNI
# SEMMSL 每个信号集的最大信号数量
# SEMMNS 用于控制整个 Linux 系统中信号（而不是信号集）的最大数
# SEMOPM 内核参数用于控制每个 semop 系统调用可以执行的信号操作的数量
# SEMMNI 内核参数用于控制整个 Linux 系统中信号集的最大数量
# 这里套用Oracle 11gR2的最小要求值
kernel.sem = 250 32000 100 128
# 最大的TCP数据接收窗口（字节）
net.core.rmem_max = 4194304
# 最大的TCP数据发送窗口（字节）
net.core.wmem_max = 4194304
# 默认的TCP数据接收窗口大小（字节）
net.core.rmem_default = 262144
# 默认的TCP数据发送窗口大小（字节）
net.core.wmem_default = 262144
# 配置主动发起连接时，端口范围，默认值32768-60000
net.ipv4.ip_local_port_range = 9000 65535
# 当系统pagecache脏页达到系统内存dirty_ratio的百分比值时，会阻塞新的写请求
# 直到内存脏页落盘
# 默认值为30
vm.dirty_ratio = 80
EOF

limits参数

1
2
3
4
5
6
7
8
9
10
cat > /etc/security/limits.d/99-pgsql.conf <<EOF
* soft nofile 655360
* hard nofile 655360
* soft nproc 655360
* hard nproc 655360
* soft stack unlimited
* hard stack unlimited
* soft   memlock    250000000
* hard   memlock    250000000
EOF

准备数据盘

确认数据盘

1
2
3
4
5
6
7
lsblk
# 输出示例
NAME   MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda      8:0    0  20G  0 disk
├─sda1   8:1    0   1G  0 part /boot
└─sda2   8:2    0  19G  0 part /
sdb      8:16   0  30G  0 disk

分区

使用fdisk /dev/sdb格式化硬盘，默认已做4K对齐

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
fdisk /dev/sdb
# 输出示例
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xa5fd5a2b.

Command (m for help): p

Disk /dev/sdb: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xa5fd5a2b

   Device Boot      Start         End      Blocks   Id  System

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-62914559, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-62914559, default 62914559):
Using default value 62914559
Partition 1 of type Linux and of size 30 GiB is set

Command (m for help): p

Disk /dev/sdb: 32.2 GB, 32212254720 bytes, 62914560 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xa5fd5a2b

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1            2048    62914559    31456256   83  Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
1
2
3
4
5
6
7
8
lsblk
# 输出示例
NAME   MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda      8:0    0  20G  0 disk
├─sda1   8:1    0   1G  0 part /boot
└─sda2   8:2    0  19G  0 part /
sdb      8:16   0  30G  0 disk
└─sdb1   8:17   0  30G  0 part

格式化分区

1
2
3
4
5
6
7
8
9
10
11
mkfs.xfs -f -b size=4096 /dev/sdb1
# 输出示例
meta-data=/dev/sdb1              isize=512    agcount=4, agsize=1966016 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=7864064, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=3839, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

创建数据目录

1
mkdir -p /data

挂载数据盘

1
mount -t xfs -o nolargeio,noatime,nodiratime /dev/sdb1 /data

修改/etc/fstab

追加一行记录

1
/dev/sdb1 /data xfs nolargeio,noatime,nodiratime 0 0

安装PostgreSQL

准备YUM源

社区配置YUM源的文档

1
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

由于默认的源服务器地址在国外，修改为国内镜像地址可以改善下载速度

1
yum install -y https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

默认源服务器地址是PostgreSQL社区的服务器，这里替换成清华大学的镜像源地址

1
sed -e 's,download.postgresql.org/pub,mirrors4.tuna.tsinghua.edu.cn/postgresql,g' -i /etc/yum.repos.d/pgdg-redhat-all.repo

安装PostgreSQL10

必装
1
yum install -y postgresql10 postgresql10-server

选装

1
yum install -y postgresql10-contrib postgresql10-test

PostgreSQL也有基于WebUI的图形管理工具pgAdmin，具体怎么用，自己看文档

windows版的看这里

容器化部署看这里

1
yum install -y pgadmin4

初始化数据库

  • 使用默认数据目录

默认数据目录在/var/lib/pgsql/10/data

1
/usr/pgsql-10/bin/postgresql-10-setup initdb
  • 使用自定义数据目录

这里自定义数据目录为/data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 修改数据目录权限
chown -R postgres:postgres /data
# 修改服务启动脚本的数据目录
# 服务脚本默认的Environment=PGDATA=/var/lib/pgsql/10/data
# 不改这个会导致服务启动失败
sed -e 's,^Environment=PGDATA=.*,Environment=PGDATA=/data,' -i /usr/lib/systemd/system/postgresql-10.service
systemctl daemon-reload
# 切换到postgres用户
su - postgres
# 修改默认PGDATA环境变量
sed -e 's,^PGDATA.*,PGDATA=/data,' -i ~/.bash_profile
# 应用变量
source ~/.bash_profile
# 初始化数据库
/usr/pgsql-10/bin/initdb --encoding=UTF-8  \
                         --local=en_US.UTF8 \
                         --username=postgres \
                         --pwprompt \
                         --pgdata=$PGDATA \
                         --data-checksums

启动PostgreSQL

1
2
systemctl enable postgresql-10.service
systemctl start postgresql-10.service

验证数据库

查看数据库进程

1
2
3
4
5
6
7
8
9
10
ps -ef | grep postgres
# 输出示例
postgres  13558      1  0 13:25 ?        00:00:00 /usr/pgsql-10/bin/postmaster -D /data
postgres  13560  13558  0 13:25 ?        00:00:00 postgres: logger process
postgres  13562  13558  0 13:25 ?        00:00:00 postgres: checkpointer process
postgres  13563  13558  0 13:25 ?        00:00:00 postgres: writer process
postgres  13564  13558  0 13:25 ?        00:00:00 postgres: wal writer process
postgres  13565  13558  0 13:25 ?        00:00:00 postgres: autovacuum launcher process
postgres  13566  13558  0 13:25 ?        00:00:00 postgres: stats collector process
postgres  13567  13558  0 13:25 ?        00:00:00 postgres: bgworker: logical replication launcher

查看数据库监听端口

1
2
3
4
netstat -antupl | grep 5432
# 输出示例
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      13558/postmaster
tcp6       0      0 ::1:5432                :::*                    LISTEN      13558/postmaster

切换用户

1
su - postgres

登录数据库

在postgres用户下可以直接免密码登录到数据库

1
psql -U postgres

查看数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF8 | zh_CN.UTF8 |
 template0 | postgres | UTF8     | zh_CN.UTF8 | zh_CN.UTF8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF8 | zh_CN.UTF8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

配置PostgreSQL数据库

配置文件路径

配置文件是放在数据目录，懒得记路径可以这么做

1
2
3
4
5
6
7
8
9
su - postgresql
cd $PGDATA
ls
# 输出示例
base              pg_dynshmem    pg_notify     pg_stat_tmp  pg_wal                postmaster.pid
current_logfiles  pg_hba.conf    pg_replslot   pg_subtrans  pg_xact
global            pg_ident.conf  pg_serial     pg_tblspc    postgresql.auto.conf
log               pg_logical     pg_snapshots  pg_twophase  postgresql.conf
pg_commit_ts      pg_multixact   pg_stat       PG_VERSION   postmaster.opts

配置文件说明

postgresql.conf

数据库服务的配置文件

默认配置文件条目非常多，可以根据注释来配置对应选项

也可以查看在线版本

这里简单做一下配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()".  Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
#data_directory = 'ConfigDir'
#hba_file = 'ConfigDir/pg_hba.conf'
#ident_file = 'ConfigDir/pg_ident.conf'
#external_pid_file = ''

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*'
port = 5432
# max_connections默认是100
max_connections = 500
superuser_reserved_connections = 3

# - TCP settings -
# 默认值为0，即使用系统默认值
#tcp_keepalives_idle = 0
#tcp_keepalives_interval = 0
#tcp_keepalives_count = 0
#tcp_user_timeout = 0

# - Authentication -
# 认证超时时间，默认1min
authentication_timeout = 30s
# 密码加密算法，默认md5
password_encryption = md5

# GSSAPI using Kerberos
#krb_server_keyfile = ''
#krb_caseins_users = off

# - SSL -
ssl = off
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 128MB
#huge_pages = try
#temp_buffers = 8MB
#max_prepared_transactions = 0
#work_mem = 4MB
#maintenance_work_mem = 64MB
#autovacuum_work_mem = -1
#max_stack_depth = 2MB
#shared_memory_type = mmap
dynamic_shared_memory_type = posix

# - Disk -
#temp_file_limit = -1

# - Kernel Resources -
#max_files_per_process = 1000
#shared_preload_libraries = ''

# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200

# - Background Writer -
#bgwriter_delay = 200ms
#bgwriter_lru_maxpages = 100
#bgwriter_lru_multiplier = 2.0
#bgwriter_flush_after = 0

# - Asynchronous Behavior -
#effective_io_concurrency = 1
#max_worker_processes = 8
#max_parallel_maintenance_workers = 2
#max_parallel_workers_per_gather = 2
#parallel_leader_participation = on
#max_parallel_workers = 8
#old_snapshot_threshold = -1
#backend_flush_after = 0

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
wal_level = replica
fsync = on
synchronous_commit = on
wal_sync_method = fdatasync
#full_page_writes = on
#wal_compression = off
# 如需要用pg_rewind修复WAL的timeline, 需要打开wal_log_hints
# 但是开启它会导致写wal变多, 请斟酌
wal_log_hints = off
#wal_init_zero = on
#wal_recycle = on
#wal_buffers = -1
#wal_writer_delay = 200ms
#wal_writer_flush_after = 1MB
#commit_delay = 0
#commit_siblings = 5

# - Checkpoints -
#checkpoint_timeout = 5min
max_wal_size = 1GB
min_wal_size = 80MB
#checkpoint_completion_target = 0.5
#checkpoint_flush_after = 0
#checkpoint_warning = 30s

# - Archiving -
#archive_mode = off
#archive_command = ''
#archive_timeout = 0

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
max_wal_senders = 10
# wal_keep_segments可以设置大一点，每个segments大小16MB
# 这样在备库不会因为wal receiver启动太慢导致所需wal在主库被删除
wal_sender_timeout = 60s
#max_replication_slots = 10
#track_commit_timestamp = off

# - Master Server -
#synchronous_standby_names = ''
#vacuum_defer_cleanup_age = 0

# - Standby Servers -
#hot_standby = on
#max_standby_archive_delay = 30s
#max_standby_streaming_delay = 30s
#wal_receiver_status_interval = 10s
#hot_standby_feedback = off
#wal_receiver_timeout = 60ss
#wal_retrieve_retry_interval = 5s

# - Subscribers -
#max_logical_replication_workers = 4
#max_sync_workers_per_subscription = 2

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -
#seq_page_cost = 1.0
#random_page_cost = 4.0
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.005
#cpu_operator_cost = 0.0025
#parallel_tuple_cost = 0.1
#parallel_setup_cost = 1000.0
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB

# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5
#geqo_pool_size = 0
#geqo_generations = 0
#geqo_selection_bias = 2.0
#geqo_seed = 0.0

# - Other Planner Options -
#default_statistics_target = 100
#constraint_exclusion = partition
#cursor_tuple_fraction = 0.1
#from_collapse_limit = 8
#join_collapse_limit = 8
#force_parallel_mode = off

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y%m%d.log'
#log_file_mode = 0600
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#syslog_sequence_numbers = on
#syslog_split_messages = on

# - When to Log -
#log_min_messages = warning 
#log_min_error_statement = error
log_min_duration_statement = 0 

# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
log_checkpoints = on 
log_connections = on 
log_disconnections = on 
log_duration = on 
log_error_verbosity = verbose
#log_hostname = off
log_line_prefix = '%m [%p]: user=%u,db=%d '
log_lock_waits = on
#log_statement = 'none'
#log_replication_commands = off
#log_temp_files = -1
log_timezone = 'PRC'

# - Process Title -
#cluster_name = ''
#update_process_title = on

#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
track_activities = on
track_counts = on
track_io_timing = on
track_functions = none
track_activity_query_size = 1024
stats_temp_directory = 'pg_stat_tmp'

# - Statistics Monitoring -
log_parser_stats = on
log_planner_stats = on
log_executor_stats = on
log_statement_stats = off

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_multixact_freeze_max_age = 400000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#client_min_messages = notice
#search_path = '"$user", public'
#default_tablespace = ''
#temp_tablespaces = ''
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0
#lock_timeout = 0
#idle_in_transaction_session_timeout = 0
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#vacuum_multixact_freeze_min_age = 5000000
#vacuum_multixact_freeze_table_age = 150000000
#bytea_output = 'hex'
#xmlbinary = 'base64'
#xmloption = 'content'
#gin_fuzzy_search_limit = 0
#gin_pending_list_limit = 4MB

# - Locale and Formatting -
datestyle = 'iso, ymd'
#intervalstyle = 'postgres'
timezone = 'PRC'
#timezone_abbreviations = 'Default'
#extra_float_digits = 0
#client_encoding = sql_ascii

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'			# locale for system error message strings
lc_monetary = 'en_US.UTF-8'			# locale for monetary formatting
lc_numeric = 'en_US.UTF-8'			# locale for number formatting
lc_time = 'en_US.UTF-8'				# locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'

# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#session_preload_libraries = ''

#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64
#max_pred_locks_per_transaction = 64
#max_pred_locks_per_relation = -2
#max_pred_locks_per_page = 2

#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#operator_precedence_warning = off
#quote_all_identifiers = off
#standard_conforming_strings = on
#synchronize_seqscans = on

# - Other Platforms and Clients -
#transform_null_equals = off

#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off
#restart_after_crash = on
#data_sync_retry = off


#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------
#include_dir = ''
#include_if_exists = ''
#include = ''

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here

pg_hba.conf

基于主机认证的配置文件

默认的配置文件好多注释，看着不方便

简化之后如下

1
2
3
4
5
6
7
8
9
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# 默认配置数据库主机以socket、127.0.0.1、::1的方式连接数据库可以跳过认证阶段直接登录数据库
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
# 配置app_user可以基于用户名密码+MD5加密算法从0.0.0.0/0访问数据库服务，并且是能访问所有databases
host    all             all             0.0.0.0/0               md5
# 配置app_user可以基于用户名密码+MD5加密算法从*.exmpale.com访问数据库服务，只能访问app_db1和app_db2
#host    app_db1,app_db2 app_user        .exmpale.com            md5

pg_hba.conf的配置选项非常多，更详细的资料可以查看配置文件自带的注释或者查看pgsql中文社区PostgreSQL10的中文版手册，链接在这

激活数据库配置

  • 注释里没提及change requires restart，可以通过reload的方式加载配置
1
2
su - postgresql
/usr/pgsql-10/bin/pg_ctl reload -D $PGDATA
  • 注释里明确列明了change requires restart，只能restart的方式重启数据库才能生效
1
2
su - postgresql
/usr/pgsql-10/bin/pg_ctl restart -D $PGDATA

PostgreSQL日志

日志路径

默认路径

在数据目录的log目录里面，%PGDATA/log

1
log_filename = 'postgresql-%a.log'

其中%a是指星期几的缩写作为标识，例如postgresql-Mon.log、postgresql-Tue.log

自定义文件名

postgresql.conf里面的参数控制日志的文件名

1
log_filename = 'postgresql-%Y%m%d.log'

日志轮转

在postgresql.conf可以定义这些参数

  • 每天生成一个日志文件
1
2
3
4
log_filename = 'postgresql-%Y%m%d.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
  • 每天一个日志文件，只保留7天日志，循环覆盖之前的日志
1
2
3
4
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0

增加PATH变量

切换到postgres用户

1
su - postgres

修改~/.bash_profile

1
2
PGHOME='/usr/pgsql-10'
export PATH=${PGHOME}/bin:${PATH}