麒麟操作系统离线安装postgresql
一、前置依赖清单
# 在联网服务器上操作（其系统版本需与离线服务器保持一致）# 新建一个目录，并在该目录下执行下载命令mkdir-p/opt/pg-rpms&&cd/opt/pg-rpms# 安装下载工具（可在任意路径下执行）yuminstall-yyum-utils# 下载全部依赖及递归依赖，所有 rpm 包会保存到当前目录dnf download--resolve--alldeps--arch=x86_64 gcc gcc-c++makebison 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顺序安装（遇到依赖报错再调顺序，万不得已才nodeps）rpm-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# 安装目录、数据目录（按需调整路径，政务一般放/data）mkdir-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--locale=C-Upostgres-E UTF8：指定数据库编码为 UTF8-U postgres：指定超级用户为 postgres
初始化过程的详细日志如下：
The files belonging to this database system will be owned by user"postgres".This user must also own the server process. The database cluster will be initialized with locale"C".The default text search configuration will besetto"english".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 default"max_connections"...100selecting default"shared_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: enabling"trust"authenticationforlocalconnections 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--locale=C-Upostgres-E UTF8数据库编码 UTF8-U postgres设置超级用户 postgres
打印的详细日志
The files belonging to this database system will be owned by user"postgres".This user must also own the server process. The database cluster will be initialized with locale"C".The default text search configuration will besetto"english".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 default"max_connections"...100selecting default"shared_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: enabling"trust"authenticationforlocalconnections 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='*'port=5432max_connections=200shared_buffers=2GB# 物理内存的1/4，按需要改wal_buffers=16MB logging_collector=on log_directory='log'配置pg_hba.confhostall all127.0.0.1/32 scram-sha-256hostall all172.18.0.0/16 scram-sha-256九、systemd 服务文件（麒麟 systemd）
vi/etc/systemd/system/postgresql-18.service[Unit]Description=PostgreSQL18Database ServerDocumentation=https://www.postgresql.org/docs/After=network.target[Service]Type=forkingUser=postgresGroup=postgresEnvironment=PGDATA=/home/postgres/pg/dataEnvironment=PGHOME=/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}-sTimeoutSec=300[Install]WantedBy=multi-user.target加载、开机自启、启动：
sudosystemctl daemon-reloadsudosystemctl start postgresql-18sudosystemctlenablepostgresql-18sudosystemctl status postgresql-18十、初始化超级用户密码、测试连接
psql alter user postgres with password'Jky#!90opl;./';\q 退出客户端本地测试
psql-h127.0.0.1-Upostgres-dpostgres操作数据库
1.查看服务器上的字符集 locale-a|grepzh 有zh_CN.UTF-8说明服务器支持utf-8的字符集 // 创建普通用户(同时设置密码)create user auth_app with password'Jky#!&90opl;./';此时这个用户啥权限都没有，连登录数据库都还需要权限 //创建数据库（指定数据库的拥有者） create database auth owner=auth_app encoding='UTF-8'LC_COLLATE='zh_CN.UTF-8'LC_CTYPE='zh_CN.UTF-8'template='template0'connection 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.oid=d.objoid AND d.objsubid=0WHERE c.relkind='r'AND c.relnamespace=(SELECT oid FROM pg_namespace WHEREnspname='public')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.oid=a.attrelid JOIN pg_type t ON a.atttypid=t.oid LEFT JOIN pg_description d ON c.oid=d.objoid AND a.attnum=d.objsubid WHERE c.relname='sys_user'AND a.attnum>0ORDER BY a.attnum;
