Debian PostgreSQL使用教程有哪些

Debian系统下PostgreSQL使用教程汇总

一、PostgreSQL安装教程

1. 基础安装（通过Debian官方仓库）

• 更新系统：运行sudo apt update && sudo apt upgrade -y确保系统包为最新版本。
• 安装PostgreSQL及扩展：执行sudo apt install postgresql postgresql-contrib，其中postgresql-contrib包含常用工具（如pg_dump、pg_restore）。
• 验证安装：安装完成后，通过pg_lsclusters查看数据库集群状态，或systemctl status postgresql检查服务是否运行。

2. 通过PostgreSQL官方仓库安装（推荐最新版本）

• 添加官方仓库：创建/etc/apt/sources.list.d/pgdg.list文件，写入deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main（$(lsb_release -cs)自动匹配Debian版本，如bookworm）。
• 导入GPG密钥：运行wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -添加官方密钥。
• 安装指定版本：更新包列表后，执行sudo apt install postgresql-14（替换为所需版本号，如13、15）。

二、基础配置教程

1. 切换至postgres用户

PostgreSQL安装后会创建同名系统用户postgres，用于管理数据库。通过sudo -i -u postgres切换至该用户。

2. 修改默认密码

默认情况下，postgres用户无密码或密码为空。切换至postgres用户后，运行psql进入交互终端，执行\password postgres设置密码。

3. 创建数据库与用户

• 方式一：通过psql终端：
  创建用户（指定加密密码）：CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
  创建数据库（指定所有者）：CREATE DATABASE mydb OWNER myuser;
  授权用户访问数据库：GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
  退出终端：\q。
• 方式二：通过shell命令：
  创建用户：sudo -u postgres createuser --superuser myuser（--superuser表示超级用户，可根据需求省略）；
  创建数据库：sudo -u postgres createdb -O myuser mydb（-O指定所有者）；
  设置用户密码：sudo -u postgres psql -c "\password myuser"。

4. 配置远程访问（可选）

• 修改postgresql.conf：编辑/etc/postgresql/<version>/main/postgresql.conf，找到listen_addresses并设置为'*'（允许所有IP连接）或指定IP（如'192.168.1.100'）。
• 修改pg_hba.conf：编辑/etc/postgresql/<version>/main/pg_hba.conf，在文件末尾添加host all all 0.0.0.0/0 scram-sha-256（允许所有IP通过scram-sha-256加密密码访问，生产环境建议限制为特定网段，如192.168.1.0/24）。
• 重启服务：执行sudo systemctl restart postgresql使配置生效。
• 开放防火墙：若启用ufw，运行sudo ufw allow 5432/tcp允许PostgreSQL端口（默认5432）的流量。

三、日常操作教程

1. 登录PostgreSQL

• 使用psql终端：
  以postgres用户登录：sudo -u postgres psql；
  以普通用户登录：psql -U myuser -d mydb -h 127.0.0.1 -p 5432（-U指定用户，-d指定数据库，-h指定服务器IP，-p指定端口）。
• 退出终端：输入\q或按Ctrl+D。

2. 基本数据库操作

• 查看所有数据库：\l（或SELECT datname FROM pg_database;）。
• 连接数据库：\c mydb（切换至mydb数据库）。
• 查看当前数据库：SELECT current_database();。
• 查看当前用户：SELECT current_user;。
• 创建表：在目标数据库中执行CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));（SERIAL为自增主键）。
• 插入数据：INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');。
• 查询数据：SELECT * FROM users;。
• 退出psql：\q。

3. 备份与恢复

• 备份数据库：sudo -u postgres pg_dump mydb > mydb_backup.sql（将mydb数据库导出至mydb_backup.sql文件）。
• 恢复数据库：sudo -u postgres psql mydb < mydb_backup.sql（将mydb_backup.sql文件导入至mydb数据库）。

4. 用户权限管理

• 授予数据库所有权限：GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;。
• 授予表特定权限：GRANT SELECT, INSERT, UPDATE ON TABLE mytable TO myuser;（允许用户myuser对mytable进行查询、插入、更新操作）。
• 撤销权限：REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;（撤销用户myuser对mydb的所有权限）。