CentOS7系统下安装PostgreSQL17数据库

系统CentOS7.9关闭了系统防火墙firewalld服务和SELinux一、安装1、下载安装包wgethttps://ftp.postgresql.org/pub/source/v17.4/postgresql-17.4.tar.gz2、解压安装包tar-xfpostgresql-17.4.tar.gz3、安装依赖yuminstallgcc gcc-cmakelibicu-devel bison flex readline-devel zlib-devel4、编译安装cdpostgresql-17.4/ ./configure--prefix/usr/local/pgsqlmakemakeinstall5、添加数据库用户并创建数据存储目录useraddpostgresmkdir/usr/local/pgsql/datachownpostgres:postgres /usr/local/pgsql/data6、配置环境变量vim/etc/profile.d/pgsql.shexportPGHOME/usr/local/pgsqlexportPGDATA/usr/local/pgsql/dataexportPATH$PGHOME/bin:$PATH#使用环境变量生效source/etc/profile.d/pgsql.sh7、 系统参数优化vim/etc/sysctl.conf#添加以下内容vm.nr_hugepages6144#使用参数生效sysctl-p8、数据库初始化#切换到postgres用户su- postgres#执行数据初始化/usr/local/pgsql/bin/initdb-D/usr/local/pgsql/data#退出postgres用户exit9、修改配置文件vim/usr/local/pgsql/data/postgresql.conf#修改以下配置listen_addresses*max_connections300shared_buffers4GB work_mem64MB maintenance_work_mem1GB effective_cache_size12GB wal_buffers16MB checkpoint_completion_target0.9listen_addresses监听地址默认使用localhost使用’*表示所有max_connections决定允许的最大数据库连接数。过多的连接会增加系统开销和资源竞争。通常可以使用连接池工具如PgBouncer来控制并发连接数shared_buffers这是PostgreSQL用于缓存表数据的共享内存区域通常建议设置为物理内存的25%-40%。如果设置过低会导致频繁的磁盘访问设置过高则会占用操作系统内存减少可用的文件缓存work_mem每个查询操作如排序、哈希表所使用的内存。这个参数是每个查询连接单独分配的因此需要根据查询复杂度和并发量合理设置。如果过小查询需要频繁进行磁盘交换过大会导致内存不足。典型值在10MB-100MB之间maintenance_work_mem控制PostgreSQL在执行维护操作时使用的内存大小比如创建索引、VACUUM。推荐设置为较大的值尤其是在大规模数据集上操作时effective_cache_sizePostgreSQL根据此参数判断系统可用的文件系统缓存大小从而决定是否使用索引扫描或全表扫描。建议设置为物理内存的50%-75%wal_buffers建议设置为shared_buffers的1/32用于缓冲WAL数据避免频繁写入磁盘checkpoint_completion_target设置为接近1的值可以平滑WAL日志写入压力减少突发I/O操作。10、启动服务(1) 使用自带脚本方式启动#使用root用户执行以下命令#复制源码包里的脚本至etc/init.d目录下并加执行权限cp./contrib/start-scripts/linux /etc/init.d/postgresqlchmodx /etc/init.d/postgresql#启动服务servicepostgresql start#设置开机启动chkconfig--addpostgresql(2) 使用systemd进行管理service文件如下vim/etc/systemd/system/postgresql.service[Unit]DescriptionPostgreSQL database serverDocumentationman:postgres(1)Afternetwork-online.target[Service]TypeforkingUserpostgresGrouppostgresEnvironmentPGDATA/usr/local/pgsql/dataExecStart/usr/local/pgsql/bin/pg_ctl start-D${PGDATA}ExecStop/usr/local/pgsql/bin/pg_ctl stop-D${PGDATA}-mfastExecReload/home/postgres/bin/pg_ctl reload-D${PGDATA}TimeoutSec300s[Install]WantedBymulti-user.target systemctl daemon-reload systemctl start postgresql如果使用Typenotify要求服务器配置时使用./configure --with-systemd构建(3) 直接使用命令行启动su - postgres/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data11、配置远程访问权限vim/usr/local/pgsql/data/pg_hba.conf#添加以下内容管理员用户postgres不允许远程连接hostall postgres0.0.0.0/0 rejecthostall all0.0.0.0/0 password#然后重启数据库servicepostgresql restart二、常用操作1、登录数据库#切换到postgres用户su- postgres psql#或在root用户下使用以下命令psql-Upostgres2、查看当前登录用户/数据库postgres# \cYou are now connected to databasepostgresas userpostgres.postgres# select user;user ---------- postgres(1row)postgres# select current_user;current_user -------------- postgres(1row)postgres# select current_database();current_database ------------------ postgres(1row)3、创建数据库postgres# create database mydb;CREATE DATABASE4、切换数据库postgres# \c mydbYou are now connected to databasemydbas userpostgres.mydb#\l列举数据库\dt列举表\d tblname查看表结构\di查看索引5、创建角色与用户并授权PostgreSQL使用角色的概念管理数据库访问权限。一个角色可以被看成是一个数据库用户或者是一个数据库用户组这取决于角色被怎样设置。角色可以拥有数据库对象例如表和函数并且能够把那些对象上的权限赋予给其他角色来控制谁能访问哪些对象。此外还可以把一个角色中的成员资格授予给另一个角色这样允许成员角色使用被赋予给另一个角色的权限。角色的概念把“用户”和“组”的概念都包括在内。在PostgreSQL版本 8.1 之前用户和组是完全不同的两种实体但是现在只有角色。任意角色都可以扮演用户、组或者两者。(1) 创建用户postgres# CREATE USER testuser with password t123456;CREATE ROLECREATE USER等同于CREATE ROLE 但CREATE USER默认包含LOGIN而CREATE ROLE不包含。如下两者等价创建具有登录权限的角色CREATE ROLE name LOGIN;CREATEUSERname;查看角色信息\du或\du查看也可以查看系统表select * from pg_roles;(2) 创建schema与用户名同名【租户】postgres# \c mydb;You are now connected to databasemydbas userpostgres.mydb# create schema testuser;CREATE SCHEMA(3) 授权用户的schema所有权限mydb# GRANT ALL PRIVILEGES ON SCHEMA testuser TO testuser;GRANT(4) 普通用户登录psql-Utestuser-dmydb6、回收所有授权(1) 回收用户在SCHEMA下所有表的所有权限postgres# REVOKE ALL PRIVILEGES ON SCHEMA testuser FROM testuser;