一、前置依赖清单# 在联网服务器上操作其系统版本需与离线服务器保持一致# 新建一个目录并在该目录下执行下载命令mkdir-p/opt/pg-rpmscd/opt/pg-rpms# 安装下载工具可在任意路径下执行yuminstall-yyum-utils# 下载全部依赖及递归依赖所有 rpm 包会保存到当前目录dnf download--resolve--alldeps--archx86_64 gcc gcc-cmakebison flex readline-devel zlib-devel openssl-devel libxml2-devel libxslt-devel libicu-devel systemd-devel perl uuid-devel repotrack会把所有子依赖一并下载下来。离线安装时可直接执行rpm-ivh*.rpm--nodeps--force但该方式会忽略依赖校验请谨慎使用优先不加--nodeps。## 二、准备文件包拷贝到离线麒麟服务器## 三、安装依赖包bash# 上传rpm包到 /opt/pg_rpmmkdir-p/opt/pg_rpmcd/opt/pg_rpm# 优先顺序安装自动解决依赖麒麟dnf/yum支持本地目录源# 方式A推荐自动处理依赖dnfinstall./*.rpm-y# 如果dnf报错rpm顺序安装遇到依赖报错再调顺序万不得已才nodepsrpm-ivh*.rpm# 报错有依赖循环时rpm-ivh*.rpm--nodepsrepotrack会把所有子依赖一并下载下来。离线安装时可直接执行rpm -ivh *.rpm --nodeps --force但该方式会忽略依赖校验请谨慎使用优先不加--nodeps。二、准备文件包拷贝到离线麒麟服务器将上一步下载好的全部 rpm 包打包拷贝到离线麒麟服务器上供后续安装使用。gcc-vbison--versionflex--version# 检查devel头文件是否存在ls/usr/include/readline/ls/usr/include/zlib.hls/usr/include/openssl/四、创建 PG 运行用户、目录规范不能 root 跑 PG# 创建postgres用户无登录shelluseradd-M-s/sbin/nologin postgres# 安装目录、数据目录按需调整路径政务一般放/datamkdir-p/home/postgres/pg/apps# 编译安装目录mkdir-p/home/postgres/pg/data# 数据目录chown-Rpostgres:postgres /home/postgres/pg/apps /home/postgres/pg/datachmod700/home/postgres/pg/data五、解压源码、configure 编译重点参数依赖安装完成后可通过以下命令校验是否安装成功gcc-vbison--versionflex--version# 检查 devel 头文件是否存在ls/usr/include/readline/ls/usr/include/zlib.hls/usr/include/openssl/四、创建 PG 运行用户与目录出于安全规范PostgreSQL 不允许以 root 用户运行需单独创建运行用户与目录# 创建 postgres 用户不创建家目录且禁止登录useradd-M-s/sbin/nologin postgres# 创建安装目录与数据目录按需调整路径政务环境一般放在 /data 下mkdir-p/home/postgres/pg/apps# 编译安装目录mkdir-p/home/postgres/pg/data# 数据目录# 将目录属主改为 postgres并收紧数据目录权限chown-Rpostgres:postgres /home/postgres/pg/apps /home/postgres/pg/datachmod700/home/postgres/pg/data编译 contrib 扩展uuid、pg_stat_statements、pgcrypto 等常用插件必做cdcontribmake-j4makeinstallcd../六、环境变量配置postgres 用户# 编辑postgres的profilesu- postgres-s/bin/bashvi~/.bash_profile五、解压源码并编译安装# 上传源码包到 /opt 后解压cd/opttar-zxvfpostgresql-18.6.tar.gzcdpostgresql-18.6# 配置编译参数sudo./configure--prefix/home/postgres/pg/apps --with-openssl --with-libxml --with-libxslt --with-icu --with-systemd# 编译并安装sudomake-j4sudomakeinstall编译 contrib 扩展包含 uuid、pg_stat_statements、pgcrypto 等常用插件建议一并编译cdcontribmake-j4makeinstallcd../六、配置环境变量postgres 用户# 切换到 postgres 用户并编辑其 profilesu- postgres-s/bin/bashvi~/.bash_profile在文件中写入以下内容exportPGHOME/home/postgres/pg/appsexportPGDATA/home/postgres/pg/dataexportPATH$PGHOME/bin:$PATHexportLD_LIBRARY_PATH$PGHOME/lib:$LD_LIBRARY_PATH保存后使配置立即生效并验证版本source~/.bash_profile# 验证psql--version# 输出如下即表示配置成功psql(PostgreSQL)18.6让修改的配置文件立即生效source~/.bash_profile# 验证psql--version# 显示如下则表示成功psql(PostgreSQL)18.6七、初始化数据库集群仍以 postgres 用户执行initdb初始化数据目录initdb-D$PGDATA-EUTF8--localeC-Upostgres-E UTF8指定数据库编码为 UTF8-U postgres指定超级用户为 postgres初始化过程的详细日志如下The files belonging to this database system will be owned by userpostgres.This user must also own the server process. The database cluster will be initialized with localeC.The default text search configuration will besettoenglish.Data page checksums are enabled. fixing permissions on existing directory /home/postgres/pg/data... ok creating subdirectories... ok selecting dynamic shared memory implementation... posix selecting defaultmax_connections...100selecting defaultshared_buffers... 128MB selecting defaulttimezone... Asia/Shanghai creating configuration files... ok running bootstrap script... ok performing post-bootstrap initialization... ok syncing data to disk... ok initdb: warning: enablingtrustauthenticationforlocalconnections initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the nexttimeyou run initdb. Success. You can now start the database server using:initdb-D$PGDATA-EUTF8--localeC-Upostgres-E UTF8数据库编码 UTF8-U postgres设置超级用户 postgres打印的详细日志The files belonging to this database system will be owned by userpostgres.This user must also own the server process. The database cluster will be initialized with localeC.The default text search configuration will besettoenglish.Data page checksums are enabled. fixing permissions on existing directory /home/postgres/pg/data... ok creating subdirectories... ok selecting dynamic shared memory implementation... posix selecting defaultmax_connections...100selecting defaultshared_buffers... 128MB selecting defaulttimezone... Asia/Shanghai creating configuration files... ok running bootstrap script... ok performing post-bootstrap initialization... ok syncing data to disk... ok initdb: warning: enablingtrustauthenticationforlocalconnections initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the nexttimeyou run initdb. Success. You can now start the database server using:八、配置 postgresql.conf、pg_hba.conf配置postgresql.confvimpostgresql.conf listen_addresses*port5432max_connections200shared_buffers2GB# 物理内存的1/4按需要改wal_buffers16MB logging_collectoron log_directorylog配置pg_hba.confhostall all127.0.0.1/32 scram-sha-256hostall all172.18.0.0/16 scram-sha-256九、systemd 服务文件麒麟 systemdvi/etc/systemd/system/postgresql-18.service[Unit]DescriptionPostgreSQL18Database ServerDocumentationhttps://www.postgresql.org/docs/Afternetwork.target[Service]TypeforkingUserpostgresGrouppostgresEnvironmentPGDATA/home/postgres/pg/dataEnvironmentPGHOME/home/postgres/pg/appsExecStart/home/postgres/pg/apps/bin/pg_ctl start-D${PGDATA}-s-w-t300ExecStop/home/postgres/pg/apps/bin/pg_ctl stop-D${PGDATA}-s-mfastExecReload/home/postgres/pg/apps/bin/pg_ctl reload-D${PGDATA}-sTimeoutSec300[Install]WantedBymulti-user.target加载、开机自启、启动sudosystemctl daemon-reloadsudosystemctl start postgresql-18sudosystemctlenablepostgresql-18sudosystemctl status postgresql-18十、初始化超级用户密码、测试连接psql alter user postgres with passwordJky#!90opl;./;\q 退出客户端本地测试psql-h127.0.0.1-Upostgres-dpostgres操作数据库1.查看服务器上的字符集 locale-a|grepzh 有zh_CN.UTF-8说明服务器支持utf-8的字符集 // 创建普通用户(同时设置密码)create user auth_app with passwordJky#!90opl;./;此时这个用户啥权限都没有连登录数据库都还需要权限 //创建数据库指定数据库的拥有者 create database auth ownerauth_app encodingUTF-8LC_COLLATEzh_CN.UTF-8LC_CTYPEzh_CN.UTF-8templatetemplate0connection limit-1;//授权数据库连接 grant connect on database auth to auth_app;// 授权schema使用权限常用public grant usage on schema public to auth_app;//对这个库下已知的表做查询、新增、修改、删除的权限 grant select,insert,update,delete on all tablesinschema public to auth_app;//创建数据库的注释 comment on database auth is业务库权限库已经登录postgre库了现在客户端中切换auth的数据库\c auth //将.sql文件导入\i /home/postgres/auth_pg_导入脚本.sql;tableplus下使用查询所有表的及其注释 SELECT c.relname AS table_name, d.description AS table_comment FROM pg_class c LEFT JOIN pg_description d ON c.oidd.objoid AND d.objsubid0WHERE c.relkindrAND c.relnamespace(SELECT oid FROM pg_namespace WHEREnspnamepublic)ORDER BY c.relname;// 查询某一张表下的表结构 SELECT a.attname AS column_name, t.typname AS column_type, d.description AS column_comment FROM pg_class c JOIN pg_attribute a ON c.oida.attrelid JOIN pg_type t ON a.atttypidt.oid LEFT JOIN pg_description d ON c.oidd.objoid AND a.attnumd.objsubid WHERE c.relnamesys_userAND a.attnum0ORDER BY a.attnum;
