一、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 onlylocal 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-256host replication all 127.0.0.1/32 scram-sha-256host 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
复制代码 免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |