数据库的神操作
MongoDB的安装使用
MongoDB数据导入导出
MongoDB的全文检索
MongoDB在OMIM中实际应用(omimDataCol)
Mongodb的权限管理
MySQL安装教程-win10
MySQL操作(Linux)
PostgreSQL的操作
本文档使用 MrDoc 发布
-
+
up
down
首页
PostgreSQL的操作
# 安装 ## 非root安装 没有root权限安装 PostgreSQL(本地用户目录) ### 🧰 步骤一:准备工作 确保系统已安装以下依赖(如无权限,可跳过系统级依赖,后续用源码解决): ``` # 如果你能用 apt,先尝试安装依赖(可选) apt-get install build-essential libreadline-dev zlib1g-dev ``` 如果无权限,可以手动编译依赖(见后文)。 ### 📥 步骤二:下载源码 ``` cd ~ #wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz wget https://ftp.postgresql.org/pub/source/v16.9/postgresql-16.9.tar.gz tar -xzf postgresql-16.9.tar.gz cd postgresql-16.9 ``` ### ⚙️ 步骤三:配置安装路径(无 root) ``` ./configure --prefix=$HOME/postgres --without-readline --without-zlib ``` --prefix=$HOME/postgres 表示安装到用户目录下的 ~/postgres ,无需 root。 ### 🛠️ 步骤四:编译并安装 ``` make -j$(nproc) make install ``` ### 🗂️ 步骤五:初始化数据库 ``` mkdir -p ~/postgres/data ~/postgres/bin/initdb -D ~/postgres/data ``` ### 🚀 步骤六:启动 PostgreSQL 服务 ``` ~/postgres/bin/pg_ctl -D ~/postgres/data -l ~/postgres/logfile start ``` ### 🔐 步骤七:创建用户和数据库(可选) ``` ~/postgres/bin/createuser -s $USER ~/postgres/bin/createdb $USER ``` ### 🔗 步骤八:连接数据库 `~/postgres/bin/psql` ### ✅ 添加到环境变量(可选) 将以下内容添加到 ~/.bashrc 或 ~/.zshrc : ``` export PATH=$HOME/postgres/bin:$PATH export PGDATA=$HOME/postgres/data # 然后执行: source ~/.bashrc ``` 🧪 验证安装 bash psql --version # 输出例如:psql (PostgreSQL) 15.4 ## root安装【失败】 ``` sudo apt-get install postgresql postgresql-contrib ``` # 数据库基本操作 安装路径按前面无 root 方式举例为: ``` ~/postgres/bin/psql ~/postgres/data/ ``` 通过`psql` 进入数据库操作! ## 创建用户 ```sql -- 创建用户 CREATE USER secure_user WITH PASSWORD 'MyStrongPass!'; -- 可选:赋予创建数据库、超级用户等权限(按需) -- ALTER USER secure_user CREATEDB; ``` ## ✅ 启动数据库(若尚未启动) ```bash ~/postgres/bin/pg_ctl -D ~/postgres/data -l ~/postgres/logfile start # 重启 ~/postgres/bin/pg_ctl -D ~/postgres/data -l ~/postgres/logfile restart ``` ## ✅ 用超级用户登录 ```bash ~/postgres/bin/psql postgres ``` ## 修改用户密码 ```sql ALTER ROLE lvlh PASSWORD 'NewStrongPass!'; ``` 或者 ` ~/postgres/bin/psql -c "ALTER ROLE lvlh PASSWORD 'NewStrongPass!';"` ## 删除旧数据库 ```sql -- 强制断开其他会话 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'genome_variants_db'; -- 删除数据库 DROP DATABASE IF EXISTS genome_variants_db; ``` ## 创建数据库 ```sql CREATE DATABASE genome_variants_db; \c genome_variants_db ``` ## 创建不同角色(用户) ```sql -- 全权用户:owner,可读写 CREATE ROLE admin_user LOGIN PASSWORD 'AdminPass123'; -- 只读用户 CREATE ROLE read_only_user LOGIN PASSWORD 'ReadPass123'; ``` ## ✅ 将数据库属主改为全权用户 ```sql ALTER DATABASE genome_variants_db OWNER TO admin_user; ``` ## ✅ 切换到目标数据库,为只读用户授权 ```sql \c genome_variants_db ``` 6.1 把现有所有表/序列/视图的只读权限一次性赋予 ```sql -- 对现有对象 GRANT CONNECT ON DATABASE genome_variants_db TO read_only_user; GRANT USAGE ON SCHEMA public TO read_only_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_user; ``` 6.2 以后新建的对象也自动拥有只读权限(一劳永逸) ```sql ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only_user; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO read_only_user; ``` ## ✅ 退出 psql `quit`或者`\q` ```sql \q ``` ## 【用于创建本地库】实操: 需要创建一个数据库 名为genome_variants_db,并设置2个用户,一个lvlh具有所有权限,一个tester只有读取查询的权限; `~/postgres/bin/psql postgres`超级管理员登录 ```sql -- 1) 创建数据库(如已存在可忽略) CREATE DATABASE genome_variants_db; -- 2) 创建两个登录角色 CREATE ROLE lvlh LOGIN PASSWORD 'meifen1028'; CREATE ROLE tester LOGIN PASSWORD 'Tester_123'; -- 3) 将数据库属主改为全权用户 ALTER DATABASE genome_variants_db OWNER TO lvlh; -- 4) 切换到目标数据库 \c genome_variants_db -- 5) 为只读用户授予已有对象的查询权限 GRANT CONNECT ON DATABASE genome_variants_db TO tester; GRANT USAGE ON SCHEMA public TO tester; GRANT SELECT ON ALL TABLES IN SCHEMA public TO tester; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tester; -- 6) 为今后新建的对象自动赋予只读权限 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tester; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO tester; ``` ### 初始化数据库`genome_variants_db`: - 用已经写好的SQL文件,psql操作 ```sql \i variants_optimized.sql ``` - 直接命令行操作 `psql -U lvlh -d genome_variants_db -f variants_final.sql` ## ✅ 局域网/本机测试连接 - 全权用户 ```bash psql -h localhost -U admin_user -d genome_variants_db ``` - 只读用户 ```bash psql -h localhost -U read_only_user -d genome_variants_db ``` 只读用户尝试 `INSERT`/`UPDATE`/`DELETE` 会被拒绝。 ## 🔐 额外安全(可选) - 若只允许局域网 IP,可在 `pg_hba.conf` 里把 `192.168.0.0/16` 对应的行改成: ``` host genome_variants_db admin_user 192.168.0.0/16 md5 host genome_variants_db read_only_user 192.168.0.0/16 md5 ``` - 修改后重启: ```bash ~/postgres/bin/pg_ctl restart -D ~/postgres/data ``` # 局域网连接 要在局域网内(比如同一 Wi-Fi 或公司内网)让其他机器连接你这台 PostgreSQL,需要三步: ## ✅ 步骤 1:让 PostgreSQL 监听局域网 IP 编辑配置文件: `vim ~/postgres/data/postgresql.conf` 找到并修改: ``` listen_addresses = '*' # 默认是 localhost,改成 '*' 表示监听所有网卡 port = 5432 # 确保端口是你想要的(默认 5432) ``` 保存后退出,重启 PostgreSQL: `~/postgres/bin/pg_ctl restart -D ~/postgres/data` ## ✅ 步骤 2:允许局域网 IP 连接(修改认证配置) 编辑: `vim ~/postgres/data/pg_hba.conf` 在文件末尾新增一行(IPv4 局域网): ``` # TYPE DATABASE USER ADDRESS METHOD host all all 192.168.0.0/16 md5 ``` 说明: 保存后重启 PostgreSQL: `~/postgres/bin/pg_ctl restart -D ~/postgres/data` ## ✅ 步骤 3:开放防火墙端口(如有) 如果你机器有 ufw 防火墙: `sudo ufw allow 5432/tcp` 如果没有 root 权限,可以跳过这一步;只要系统没防火墙就行。 ## ✅ 步骤 4:从另一台机器连接测试 在局域网另一台电脑上(已安装 psql 或其他客户端),运行: `psql -h 你的局域网IP -p 5432 -U your_unix_user -d genome_variants_db` 例如: psql -h 192.168.1.100 -U alice -d genome_variants_db 系统会提示输入密码(如果你设置了)。 ## 🔍 如何查看本机局域网 IP? `ip a | grep 'inet 192.168'` # 连接错误问题 ## 用户未给数据库设置密码 > 用户 lvlh 没有设置过密码,而 pg_hba.conf 要求 md5 (密码)认证,于是登录失败。 会出现如下报错: ``` 2025-08-06 14:30:18.814 CST [26203] FATAL: password authentication failed for user "lvlh" 2025-08-06 14:30:18.814 CST [26203] DETAIL: User "lvlh" has no password assigned. Connection matched pg_hba.conf line 99: "host all all 192.168.0.0/16 md5" ``` 进入数据库设置密码即可(测试设置了test12345): ``` ~/postgres/bin/psql -U lvlh -d genome_variants_db ```
laihui126
2025年8月7日 09:18
分享文档
收藏文档
上一篇
下一篇
微信扫一扫
复制链接
手机扫一扫进行分享
复制链接
关于 MrDoc
觅道文档MrDoc
是
州的先生
开发并开源的在线文档系统,其适合作为个人和小型团队的云笔记、文档和知识库管理工具。
如果觅道文档给你或你的团队带来了帮助,欢迎对作者进行一些打赏捐助,这将有力支持作者持续投入精力更新和维护觅道文档,感谢你的捐助!
>>>捐助鸣谢列表
微信
支付宝
QQ
PayPal
下载Markdown文件
分享
链接
类型
密码
更新密码