部署postgresql 12.3

记录下postgresql 12.3部署过程。

# 安装postgresql

# 安装基础依赖
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum clean all && yum makecache fast
yum -y install systemd-devel bash-completion bash-completion-extras \
               wxBase pgagent_11 gcc make perl-ExtUtils-Embed readline-devel \
               zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel \
               openssl-devel tcl-devel python-devel openssh-clients

# 内核优化
# 注: kernel.shmmax的值取决于你的系统配置，我机器是16G内存，故设置为13958643712 B（13G）
cat >> /etc/sysctl.conf << EOF
kernel.shmmax = 13958643712
kernel.shmall = 4194304
kernel.shmmni = 4096
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.sem = 50100 64128000 50100 1280
EOF

sysctl -p

# 最大可打开文件数及进程数等
$ cat >> /etc/security/limits.conf << EOF
*               soft    nofile          65536
*               hard    nofile          65536
*               soft    nproc           65536
*               hard    nproc           65536
*               soft    memlock         unlimited
*               hard    memlock         unlimited
EOF

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

# 创建数据库运行用户及数据存放目录
$ useradd postgres
echo Y51KEBzU | passwd --stdin postgres
mkdir -p /apps/usr/postgres/data

1
2
3
4

# 安装pgsql
wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v12.3/postgresql-12.3.tar.gz
curl https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v12.3/postgresql-12.3.tar.gz.md5 | md5sum -c

tmp_dir=$(mktemp -d)
tar zxf postgresql-12.3.tar.gz -C ${tmp_dir} --strip-components=1
cd ${tmp_dir}
./configure \
        --prefix=/apps/usr/postgres \
        --with-pgport=5432 \
        --with-systemd \
        --with-perl \
        --with-tcl \
        --with-python \
        --with-openssl \
        --with-pam \
        --with-libxml \
        --with-libxslt \
        --enable-thread-safety \
        --with-wal-blocksize=16 \
        --with-blocksize=8
gmake world && gmake install-world

cat >> /etc/profile << "EOF"
export PGPORT=5432
export PGDATA=/apps/usr/postgres/data
export PGHOME=/apps/usr/postgres
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
EOF
source /etc/profile 
chown -R postgres /apps/usr/postgres



# 查看sql版本
$ psql --version
psql (PostgreSQL) 12.3

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

# 切换至postgres用户初始化数据库

$ su - postgres
# 设置postgresql默认用户密码，使用openssl指令生成密码文件
openssl rand -base64 20 | cut -c 6-15 > /home/postgres/.psqlpasswd
initdb -D /apps/usr/postgres/data/ --pwfile=/home/postgres/.psqlpasswd -A md5
# --pwfile: 指定密码文件
# -A: 指定登录验证方式，默认为trust，表示无需密码即可登录到数据库
# 注：data目录可以不存在，但绝对不允许其目录下有数据，这样将会初始化失败。

1
2
3
4
5
6
7

注: 若想添加为系统服务，使用systemctl指令来控制服务的启停，可以参考官方文档 (opens new window)。如下（个人觉得没必要配置）：

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data  # 注意这里替换为你的实际路径
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 启动前配置

cd /apps/usr/postgres/data
cp postgresql.conf{,.bak}   # 备份默认配置文件
cat /apps/usr/postgres/data/postgresql.conf   # 修改配置文件如下

listen_addresses = '*'   # *表示监听所有地址
port = 5432      # 监听端口
max_connections = 1000    # 指定最大连接数
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 1GB   # 设置共享内存缓冲区的内存量，建议值为系统总内存的25％
temp_buffers = 80MB    # 设置用于每个数据库会话中的临时缓冲区的最大内存量
max_files_per_process = 65535   # 设置允许每个服务器子进程同时打开的文件的最大数量。不能大于ulimt -n的数值
bgwriter_delay = 100ms 
bgwriter_lru_maxpages = 1000
bgwriter_flush_after = 0
synchronous_commit = off
wal_level = replica
archive_mode = on
# 以下路径替换为实际备份路径
archive_command = 'test ! -f /apps/usr/postgres/backup/incre/%f && cp %p /apps/usr/postgres/backup/incre/%f'
full_page_writes = on
wal_buffers = -1
wal_writer_delay = 100ms
wal_writer_flush_after = 256kB
checkpoint_timeout = 30min
max_wal_size = 5GB
min_wal_size = 1GB
log_destination = 'stderr'
logging_collector = on
log_directory = 'logs'
log_filename = 'postgresql-%Y-%m-%d_%H.log'
log_file_mode = 0640
log_rotation_age = 1d 
log_rotation_size = 100MB
log_truncate_on_rotation = off
log_min_messages = notice
log_min_error_statement = notice
log_min_duration_statement = 3s
log_checkpoints = on
log_connections = on
log_error_verbosity = verbose
log_line_prefix = '%m '
log_timezone = 'PRC'
track_activities = on
log_autovacuum_min_duration = 3s 
autovacuum_max_workers = 4
autovacuum_naptime = 45s
autovacuum_vacuum_scale_factor = 0.1 
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 1600000000 
autovacuum_multixact_freeze_max_age = 1600000000
vacuum_freeze_table_age = 1500000000
vacuum_multixact_freeze_table_age = 1500000000
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
default_text_search_config = 'pg_catalog.english'

$ vim /apps/usr/postgres/data/pg_hba.conf   # 添加访问权限
host    all             all             192.168.20.5/24           md5

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

关于配置文件的优化，可以参考官方文档 (opens new window)。

$ pg_ctl start     # 启动数据库
$ ss -lnput | grep 5432     # 确定端口在监听
tcp    LISTEN     0      128       *:5432                  *:*                   users:(("postgres",pid=31928,fd=3))
tcp    LISTEN     0      128      :::5432                 :::*                   users:(("postgres",pid=31928,fd=4))


# 访问测试
$ cat ~/.psqlpasswd     # 查看密码
S1w4ktZhUV
$ psql -h 127.0.0.1
Password for user postgres:      # 输入查看到的密码
postgres=# \l    # 查看所有库
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 调整linux大页面

使用大的页面可以减少使用大量连续的内存块时的开销，尤其是当使用较大的shared_buffers值时。要在PostgreSQL中使用此功能，需要一个带有CONFIG_HUGETLBFS=y和的内核CONFIG_HUGETLB_PAGE=y。还必须调整内核设置vm.nr_hugepages。如果要估计所需的大页面数，请在未启用大页面的情况下启动PostgreSQL，然后进行一下操作:

$ pg_pid=$(head -1 $PGDATA/postmaster.pid)
$ pmap ${pg_pid} | awk '/rw-s/ && /zero/ {print $2}'
1140976K
$ grep ^Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB

1
2
3
4
5

计算: 1140976 / 2048 约等于 558 ，那么内核参数应配置如下:

$ echo "vm.nr_hugepages=558" >> /etc/sysctl.conf 
$ sysctl -p
$ grep Huge /proc/meminfo   # 验证大页面分配情况
AnonHugePages:     61440 kB
HugePages_Total:     558
HugePages_Free:      558
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

1
2
3
4
5
6
7
8
9

如果机器上的其他程序也需要大页面，则较大的设置将是适当的。

# docker compose部署postgres

version: '3.8'

services:
  postgres:
    image: postgres:16.4
    container_name: postgres16
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres123
      POSTGRES_DB: testdb
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./postgres_data:/var/lib/postgresql/data
      - ./postgres_bak:/data/postgres_bak
    ports:
      - "5432:5432"
    restart: unless-stopped
    command:
      - "postgres"
      - "-c"
      - "max_connections=100"
      - "-c"
      - "shared_buffers=256MB"
      - "-c"
      - "effective_cache_size=1GB"
      - "-c"
      - "maintenance_work_mem=64MB"
      - "-c"
      - "checkpoint_completion_target=0.9"
      - "-c"
      - "wal_buffers=16MB"
      - "-c"
      - "default_statistics_target=100"
      - "-c"
      - "random_page_cost=1.1"
      - "-c"
      - "effective_io_concurrency=200"

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

原文链接：pgsql实现免密登录 | Ray (opens new window)

上次更新: 2024/12/25, 15:38:21

← oracle数据库用户创建，删除，加锁，解锁 postgresql备份脚本→