Postgres基础操作备忘录
插件安装
地理空间扩展PostGIS
sudo apt install postgis
创建postgis扩展
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
查看是否安装成功
\dx postgis
中文全文搜索扩展zhparser
安装步骤
-
CREATE EXTENSION zhparser;
-
share_preload_library配置参数zhparser
-
重启实例
效果示例
- 使用like查询包含’科技’的记录
- 基于中文全文索引查询包含’科技’的记录
数据导入导出
数据迁移
场景1：服务器A上有一份数据库，希望迁移到服务器B中
将数据从一个数据库迁移到另一个数据库。这可以通过使用 PostgreSQL 的 pg_dump 命令来备份源数据库，然后使用 pg_restore 命令来恢复数据到目标数据库。
# 备份源数据库
pg_dump -U postgres -h localhost -p 5432 -w -F p -f /path/to/backup.sql source_db
# 恢复数据到目标数据库
pg_restore -U postgres -h localhost -p 5432 -d target_db -1 /path/to/backup.sql
注意，pg_dump和server的版本不一致时可能无法成功导出:
在服务器运行pg_restore命令:
pg_restore -U postgres -h localhost -p 5432 -d industry industry
执行上述命令前，需先创建对应数据库，如果缺失插件，pg_restore会报错提示，另外也要注意数据库是否关联到某个角色，若有需要提前在待导入数据库中创建对应角色方可进行数据库导入：
Tips: 通过pg_dump导出数据库时，可通过-F参数执行导出文件类型
如-F d表示以目录形式导出数据库文件， -F p表示以纯文本格式导出数据库文件（默认为sql文件）。
数据库安装或升级
数据库安装
pgsql各版本源码路径：Index of /pub/source/
场景1: 如何通过编译源码安装postgres？
步骤1: 下载源码并解压
axel https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz
步骤2: 编译
./configure --prefix=YOUR_PREFIX_PATH/postgresql16.2 --without-icu
make
make install
数据库升级
假如希望将postgres升级版本，可用pg_upgrade
用户创建及权限设置
用户创建
场景1: 希望新建一个具有写权限的用户，但其无法删除数据，也无法变更表结构
