PostgreSql13 部署
1. 修改linux系统配置
1.1、修改/etc/sysctl.conf
shmall 是全部允许使用的共享内存大小，shmmax 是单个段允许使用的大小。这两个可以设置为内存的 90%。例如 16G 内存，16 * 1024 * 1024 * 1024 * * 90% = 15461882265，shmall 的大小为 15461882265/4k(getconf PAGESIZE可得到) = 3774873。
echo "kernel.shmall = 3774873" >> /etc/sysctl.conf echo "kernel.shmmax= 15461882265" >> /etc/sysctl.conf echo "kernel.shmmni = 4096" >> /etc/sysctl.conf echo "kernel.sem = 250 32000 32 128" >> /etc/sysctl.conf echo "fs.file-max = 7672460" >> /etc/sysctl.conf echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf echo "net.ipv4.ip_local_port_range = 9000 65000" >> /etc/sysctl.conf echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf echo "net.core.wmem_max = 4194304" >> /etc/sysctl.conf echo "net.ipv4.tcp_max_syn_backlog = 4096" >> /etc/sysctl.conf echo "net.core.netdev_max_backlog = 10000" >> /etc/sysctl.conf echo "net.ipv4.tcp_timestamps = 0" >> /etc/sysctl.conf echo "net.ipv4.tcp_tw_recycle=1" >> /etc/sysctl.conf echo "net.ipv4.tcp_timestamps=1" >> /etc/sysctl.conf echo "net.ipv4.tcp_keepalive_time = 72" >> /etc/sysctl.conf echo "net.ipv4.tcp_keepalive_probes = 9" >> /etc/sysctl.conf echo "net.ipv4.tcp_keepalive_intvl = 7" >> /etc/sysctl.conf echo "vm.zone_reclaim_mode=0" >> /etc/sysctl.conf echo "vm.dirty_background_bytes = 40960000" >> /etc/sysctl.conf echo "vm.dirty_ratio = 80" >> /etc/sysctl.conf echo "vm.dirty_expire_centisecs = 6000" >> /etc/sysctl.conf echo "vm.dirty_writeback_centisecs = 50" >> /etc/sysctl.conf echo "vm.swappiness=1" >> /etc/sysctl.conf echo "vm.overcommit_memory = 2" >> /etc/sysctl.conf echo "vm.overcommit_ratio = 90" >> /etc/sysctl.conf echo "success"
生效
sysctl -p
1.2、修改/etc/security/limits.conf
echo "* soft nofile 131072" >> /etc/security/limits.conf echo "* hard nofile 131072" >> /etc/security/limits.conf echo "* soft nproc 131072" >> /etc/security/limits.conf echo "* hard nproc 131072" >> /etc/security/limits.conf echo "* soft core unlimited" >> /etc/security/limits.conf echo "* hard core unlimited" >> /etc/security/limits.conf echo "* soft memlock -1" >> /etc/security/limits.conf echo "* hard memlock -1" >> /etc/security/limits.conf
1.3、修改/etc/security/limits.d/*
rm -f /etc/security/limits.d/*
1.4、关闭selinux
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/sysconfig/selinux
1.5、检查Linux机器上的大页面设置
sudo sysctl -w vm.nr_hugepages=76
1.6、重启计算机
reboot
1.7、卸载
yum remove postgresql*
2、安装postgres，从官方网站
2.1、yum安装
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install -y postgresql13-server postgresql13-contrib
2.2、离线安装
将包上传到linxu服务器文件夹下（4个软件包，2个依赖包）
执行命令安装：
rpm -ivh libxslt-1.1.28-6.0.1.an7.x86_64.rpm libicu-50.2-4.an7.x86_64.rpm postgresql13-13.9-1PGDG.rhel7.x86_64.rpm postgresql13-libs-13.9-1PGDG.rhel7.x86_64.rpm postgresql13-contrib-13.9-1PGDG.rhel7.x86_64.rpm postgresql13-server-13.9-1PGDG.rhel7.x86_64.rpm
如图，安装完成
3、初始化数据库
3.1、初始化操作：
mkdir -p /data/pgsql/13/data mkdir -p /home/postgres vim /home/postgres/pp.txt chown -R postgres. /data/pgsql/13/data chmod 700 /data/pgsql/13/data -R echo -e "Univalsoft_2022" > /home/postgres/pp.txt su -c '/usr/pgsql-13/bin/initdb -D /data/pgsql/13/data -A md5 -E utf8 --locale=C --pwfile=/home/postgres/pp.txt' postgres sed -i "s/Environment=PGDATA=\/var\/lib\/pgsql\/13\/data\//Environment=PGDATA=\/data\/pgsql\/13\/data\//g" /usr/lib/systemd/system/postgresql-13.service systemctl daemon-reload
3.2、设置开机自启动，并启动
sudo systemctl enable postgresql-13 sudo systemctl start postgresql-13 systemctl status postgresql-13
3.3、启用远程数据库连接
sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" /data/pgsql/13/data/postgresql.conf sed -i "s/#port = 5432/port = 5432/g" /data/pgsql/13/data/postgresql.conf sed -i "s/max_connections/#max_connections/g" /data/pgsql/13/data/postgresql.conf sed -i '/#max_connections/ i\max_connections = 2000' /data/pgsql/13/data/postgresql.conf sed -i "s/log_filename = 'postgresql-%a.log'/log_filename = 'postgresql-%m-%d.log'/g" /data/pgsql/13/data/postgresql.conf sed -i "s/#log_statement = 'none'/#log_statement = 'all'/g" /data/pgsql/13/data/postgresql.conf sed -i "s/#log_min_duration_statement = -1/#log_min_duration_statement = 3000/g" /data/pgsql/13/data/postgresql.conf
3.4、设置PostgreSQL接受远程连接 /var/lib/pgsql/13/data/pg_hba.conf
echo "host all all 0.0.0.0/0 md5" >> /data/pgsql/13/data/pg_hba.conf
sudo systemctl restart postgresql-13
4、防火墙设置
4.1、防火墙允许端口5432可访问
firewall-cmd --add-port=5432/tcp --permanent firewall-cmd --reload
使用navicat连接地址为本机ip，端口号：5432，用户名： postgres，密码：Univalsoft_2022
4.2、修改数据库用户密码
- 自动生成一个linux系统用户postgres：管理数据库的系统用户
- 数据库用户postgres：数据库超级管理员
- 此用户的默认数据库为postgres
- 可有修改下默认postgres数据库用户的密码：
su - postgres psql alter role postgres with password 'Univalsoft_2022';
4.3、修改linux系统postgres用户密码（可选操作）
#步骤一：删除用户postgres的密码 sudo passwd -d postgres #步骤二：设置用户postgres的密码 sudo -u postgres passwd
4.4、数据库创建及修改
创建数据库
create database testdb;
删除数据库
drop database testdb;
重命名数据库(该数据库必须没有活动的连接)
alter database testdb rename to newname;
以其他数据库为模板创建数据库（表结构、数据都会复制）
create database newdb template testdb;
4.5、数据库用户创建和授权
建立新的数据库用户 create user zhangsan with password '123456'; create user admin with SUPERUSER password '123456' create role lisi with login password '123456' valid until '2021-06-30' 为新用户建立数据库 create database testdb owner zhangsan; 把新建的数据库权限赋予新用户 grant all privileges on database testdb to zhangsan;
4.6、删除用户
drop user zhangsan
4.7、修改密码
alter user zhangsan with password '123456'
4.8、查看所有的用户
\du
5、备份
mkdir /home/postgres vi /home/postgres/backup_db.sh
#!/bin/bash backuppath=/data/pg_backup if [ ! -d $backuppath ] ;then mkdir /data/pg_backup && echo "备份目录创建成功!" else echo "备份目录已存在!" fi cd $backuppath /usr/pgsql-13/bin/pg_dump -F c -O --username=dbuser dbname=test -h 127.0.0.1 -p 5432 -f $(date +%Y%m%d_%H_%M_%S).dump echo "Remove 7 days age files" find $backuppath -type d -o -type f -mtime +7 -exec rm -rf {} \; echo "Finish Backup ..."
5.1、设置sh文件的执行权限
chmod +x /home/postgres/backup_db.sh
测试是否执行 /home/postgres/backup_db.sh
5.2、设置定时执行
命令行输入crontab -e会自动打开一个空文件： crontab -e 输入a或者i可以进入编辑状态，可输入任务代码 0 1 * * * /home/postgres/backup_db.sh 每天凌晨一点执行该shell Crontab格式 *分 *时 *日 *月 *周 执行的命令 编辑结束后，按下ESC键，输入:wq即可 查看状态(必须是启用的状态) systemctl status crond 若修改计划执行时间，则需要重启crond systemctl restart crond
6. 进入PostgreSQL数据库
[root@db-postgresql13 ~]# su - postgresLast login: Tue Jun 22 11:13:17 CST 2021 on pts/1 # 查看postgresql版本 -bash-4.2$ psql --version psql (PostgreSQL) 13.3 # 进入数据库 -bash-4.2$ psql psql (13.3) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) postgres=#
7. 下面是 13 安装日志
================================================================================================================================================================================================================================================= Package 架构 版本 源 大小 ================================================================================================================================================================================================================================================= 正在安装: postgresql13-contrib x86_64 13.7-1PGDG.rhel7 pgdg13 613 k postgresql13-server x86_64 13.7-1PGDG.rhel7 pgdg13 5.4 M 为依赖而安装: postgresql13 x86_64 13.7-1PGDG.rhel7 pgdg13 1.4 M postgresql13-libs x86_64 13.7-1PGDG.rhel7 pgdg13 384 k 事务概要 ================================================================================================================================================================================================================================================= 安装 2 软件包 (+2 依赖软件包) 总下载量：7.8 M 安装大小：33 M Downloading packages: 警告：/var/cache/yum/x86_64/7/pgdg13/packages/postgresql13-13.7-1PGDG.rhel7.x86_64.rpm: 头V4 DSA/SHA1 Signature, 密钥 ID 442df0f8: NOKEY ] 408 kB/s | 1.7 MB 00:00:15 ETA postgresql13-13.7-1PGDG.rhel7.x86_64.rpm 的公钥尚未安装 (1/4): postgresql13-13.7-1PGDG.rhel7.x86_64.rpm | 1.4 MB 00:00:05 (2/4): postgresql13-libs-13.7-1PGDG.rhel7.x86_64.rpm | 384 kB 00:00:00 (3/4): postgresql13-contrib-13.7-1PGDG.rhel7.x86_64.rpm | 613 kB 00:00:06 (4/4): postgresql13-server-13.7-1PGDG.rhel7.x86_64.rpm | 5.4 MB 00:00:10 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 总计 492 kB/s | 7.8 MB 00:00:16 从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG 检索密钥 导入 GPG key 0x442DF0F8: 用户ID : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>" 指纹 : 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 软件包 : pgdg-redhat-repo-42.0-24.noarch (installed) 来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Running transaction check Running transaction test Transaction test succeeded Running transaction 正在安装 : postgresql13-libs-13.7-1PGDG.rhel7.x86_64 1/4 正在安装 : postgresql13-13.7-1PGDG.rhel7.x86_64 2/4 正在安装 : postgresql13-server-13.7-1PGDG.rhel7.x86_64 3/4 正在安装 : postgresql13-contrib-13.7-1PGDG.rhel7.x86_64 4/4 验证中 : postgresql13-server-13.7-1PGDG.rhel7.x86_64 1/4 验证中 : postgresql13-contrib-13.7-1PGDG.rhel7.x86_64 2/4 验证中 : postgresql13-13.7-1PGDG.rhel7.x86_64 3/4 验证中 : postgresql13-libs-13.7-1PGDG.rhel7.x86_64 4/4 已安装: postgresql13-contrib.x86_64 0:13.7-1PGDG.rhel7 postgresql13-server.x86_64 0:13.7-1PGDG.rhel7 作为依赖被安装: postgresql13.x86_64 0:13.7-1PGDG.rhel7 postgresql13-libs.x86_64 0:13.7-1PGDG.rhel7 完毕！
