PostgreSQL_安装部署

一、Windows系统下安装

1.下载安装包

登录PostgreSQL: Downloads官网：

选择14.12版本，点击下载：

2.安装PostgrSQL14.12

双击exe安装包程序，准备安装：

选择安装路径：

选择想安装的工具：

选择数据存储路径：

设置超管密钥：

设置端口，一般是5432：

Locale建议使用C的本地化规则：

确认配置信息：

开始执行安装：

3.调整PostgreSQL配置

3.1pg_hba.conf 客户端身份验证规则配置

在pg_hba.conf文件中添加以下配置：

# postgres for localhost: local all postgres scram-sha-256 host all postgres 127.0.0.1/32 scram-sha-256 host all postgres 0.0.0.0/0 reject # remote connections: host all all 0.0.0.0/0 scram-sha-256

3.2postgresql.conf 服务参数配置

在postgresql.conf中主要调整以下配置：

listen_addresses = '*' # 监听地址 port = 5432 # 端?号 max_connections = 1000 # 最?连接数 superuser_reserved_connections = 10 # 预留给超管?户的连接数 password_encryption = scram-sha-256 # 密码加密?式 shared_buffers = 1024MB # 允许使?的内存，通常设置为物理内存的25% timezone = 'Asia/Shanghai' # 时区，根据实际项?地理位置修改 log_timezone = 'Asia/Shanghai' # ?志时区，根据实际项?地理位置修改

4.重启PostgreSQL服务

二、Linux系统下安装（以CentOS7为例）

1.安装依赖环境

yum install -y perl-devel perl-ExtUtils-Embed systemd-devel readline-devel uuid-devel zlib-devel clang-devel llvm-devel perlExtUtils-Embed tcl-devel libicu-devel libxml2-devel libxslt-devel python-devel python3-devel gcc gcc-c++ llvm3.9-devel openssl-devel lz4-devel pam-devel openldap-devel cmake bison flex

2.规划存储路径

mkdir /opt/pgsql/source -p # pgsql源码包存放路径 mkdir /opt/pgsql/extensions -p # pgsql插件存放路径 mkdir -p /mnt/data/pgsql/pgsql5432 # pgsql数据?录 mkdir -p /mnt/data/pgsql/backup/{backup-db,backup-tmp} # pgsql备份?录

3.PG环境配置

3.1创建用户和用户组、主目录

useradd -d /home/postgres -s /bin/bash -U -m postgres

3.2配置用户环境变量

cat >> /home/postgres/.bash_profile << EOF # PostgreSQL export PGHOME=/usr/local/pgsql export PATH=$PGHOME/bin:$PATH export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PGDATA=/mnt/data/pgsql/pgsql5432 export PGHOST=/tmp export PGPORT=5432 EOF

3.3授权相关目录

chmod 700 /mnt/data/pgsql/pgsql5432 chmod -R 700 /mnt/data/pgsql/backup chown postgres:postgres -R /opt/pgsql /mnt/data/pgsql

4.下载二进制安装包

wget -P /opt/pgsql/source https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz --no-check-certificate

5.包完整性校验

md5sum postgresql-14.8.tar.gz # MD5校验值：05a8078ee17d4f00779138767b802065 sha256sum postgresql-14.8.tar.gz # SHA256校验值：a3c32ff8168832d9637eb870f6e98f98506797fe5942555d70cd77558949a844

操作如下图所示：

6.解压与编译安装

tar xf postgresql-14.8.tar.gz #解压 cd postgresql-14.8 #进到主目录下 #下面进行编译与安装 ./configure --with-systemd --with-uuid=ossp --with-perl --with-python --with-tcl --with-icu --with-openssl --with-libxml --with-libxslt --with-lz4 --prefix=/opt/pgsql/pgsql-14.8 make -j 4 world && make -j 4 install-world ln -s /opt/pgsql/pgsql-14.8 /usr/local/pgsql //创建软链接

7.安装检查与验证

su - postgres #进到postgres用户下 cd /opt/pgsql/source/postgresql-14.8/ #进入pg主目录 make check # 安装成功后，测试?下编译的功能是否正常，全部正常会在末尾输出ALL tests passed

8.初始化数据库

建议：字符编码使UTF8，本地化使C，认证式使scram-sha-256

initdb -E UTF8 --locale=C -U postgres -W -A scram-sha-256 --data-checksums

9.初始化配置文件

cd /mnt/data/pgsql/pgsql5432/ mv postgresql.conf postgresql.conf.bak mv pg_hba.conf pg_hba.conf.bak touch postgresql.conf pg_hba.conf

创建两个同名文件，配置如下：

pg_hba.conf：

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all scram-sha-256 # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 # replication connections: local replication all scram-sha-256 host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 # postgres for localhost: local all postgres scram-sha-256 host all postgres 127.0.0.1/32 scram-sha-256 host all postgres 0.0.0.0/0 reject # remote connections: host all all 0.0.0.0/0 scram-sha-256

postgresql.conf：

listen_addresses = '*' port = 5432 max_connections = 1000 superuser_reserved_connections = 10 unix_socket_directories = '/tmp' tcp_keepalives_idle = 180 tcp_keepalives_interval = 10 tcp_keepalives_count = 3 password_encryption = scram-sha-256 shared_buffers = 1024MB temp_buffers = 8MB max_prepared_transactions = 50 work_mem = 4MB maintenance_work_mem = 64MB dynamic_shared_memory_type = posix max_worker_processes = 8 wal_level = logical fsync = on synchronous_commit = remote_write wal_sync_method = fsync full_page_writes = on max_wal_size = 5GB min_wal_size = 80MB max_wal_senders = 30 max_replication_slots = 10 hot_standby = on //允许只读 max_logical_replication_workers = 4 log_destination = 'stderr' //表示 PostgreSQL 将日志信息输出到标准错误输出流 logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%a.log' log_file_mode = 0600 log_rotation_age = 1d log_truncate_on_rotation = on log_checkpoints = on log_timezone = 'Asia/Shanghai' autovacuum = on //自动回收 idle_session_timeout = 1200000 datestyle = 'iso, mdy' timezone = 'Asia/Shanghai' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english'

10.配置systemd服务托管

cat > /usr/lib/systemd/system/pgsql.service << EOF [Unit] Description=PostgreSQL database server 14 After=network-online.target Wants=network-online.target [Install] WantedBy=multi-user.target [Service] Type=forking User=postgres Group=postgres Environment=PGPORT=5432 Environment=PGDATA=/mnt/data/pgsql/pgsql5432 ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s TimeoutSec=300 Restart=on-failure RestartSec=3 OOMScoreAdjust=-1000 LimitNOFILE=65535 LimitNPROC=65535 EOF

11.启动pgsql.service

systemctl daemon-reload systemctl enable pgsql systemctl start pgsql systemctl status pgsql

12.连接postgresql数据库测试

su - postgres #进入postgres用户下 psql -h 127.0.0.1 -p 5432 postgres #连接数据库

这里执行 pqsql 和 psql -h 127.0.0.1 -p 5432 postgres 命令是同样的效果

如果想修改postgres用户的密码，可以使用以下语句进行修改：

postgres=# alter user postgres with password '123'; ALTER ROLE postgres=# quit