当前位置： 首页 > news >正文

PostgreSQL日志维护

news 2025/5/23 18:51:15

目录

1：登录数据库

2：数据库操作

3：数据表操作

4：模式操作命令

5：数据操作

6：备份与恢复

7：远程连接

8：重置密码

1：登录数据库

su - postgres		#切换到程序用户
#启动程序
/usr/local/pgsql/bin/psql		
或
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
psql		#登录数据库

2：数据库操作

2.1：列出库

#方法一：
\l#方法二：
\l+		#+：扩展输出#方法三：
select datname from pg_database;		#查看pg中的库

在postgreSQL的交互式终端psql中，“\”开头的命令称为元命令（类似MySQL的show语句），用于快速管理数据库。常见元命令有：

\c 或 \connect [dbname] [username]        连接数据库

\q                                                                                     退出 psql

\l 或 \l+                           列出所有数据库

\dt 或 \dt+ [pattern]                   列出当前数据库的表

\d [table_name]                                                                查看表结构

\dn 或 \dn+                          列出模式（Schema）

\du 或 \dg                          列出用户/角色

\df [pattern]                                                                      列出函数

\di [pattern]                                                                      列出索引

\dv [pattern]                                                                     列出视图

\dF                                                                                   列出外键约束

2.2：创建库

create database 库名；

2.3：删除库

drop database 库名；		#切换到另一库进行库删除

2.4：切换库

\c 库名

2.5：查看库大小

select pg_database_size('库名')；

3：数据表操作

3.1：列出表

#列出表的常用方法：
\dt；#列出表，视图和序列
\d 表名；
\d+#列出指定模式下的表
\dt 表名.*#查看当前数据库的所有表
\dt *.*

3.2：创建表

create table test();#查看当前模式下的表：
select * 模式名.模式中的表名；          #默认模式public

3.3：复制表

create table 新表名 as table 表名；

3.4：删除表

删除表：drop table 表名；
删除库：drop database 库名；		#切换到另一库进行库删除

3.5：查看表

#查看当前模式下的表：
select * 模式名.模式中的表名；          #默认模式public

4：模式操作命令

4.1：创建模式

CREATE SCHEMA schema_name;
-- 示例：创建名为 `analytics` 的模式
CREATE SCHEMA analytics;#可选参数：指定所有者、权限等。
CREATE SCHEMA schema_name AUTHORIZATION user_name;

4.2：默认模式

#PostgreSQL 的默认模式是 public，新创建的表、视图等对象默认位于此模式。#修改默认模式：通过设置 search_path（见 4.8），将其他模式设为优先搜索路径：-- 设置当前会话的默认模式为 `analytics`
SET search_path TO analytics, public;
-- 永久修改用户的默认模式（需执行 ALTER ROLE）
ALTER ROLE user_name SET search_path = analytics, public;

4.3：删除模式

-- 删除空模式
DROP SCHEMA schema_name;
-- 强制删除模式及其所有对象（级联删除）
DROP SCHEMA schema_name CASCADE;
-- 示例：删除 `analytics` 模式及其所有表、视图等
DROP SCHEMA analytics CASCADE;

4.4：查看所有模式

\dn  -- 列出所有模式
\dn+ -- 显示模式的详细信息（所有者、权限等）

4.5：在指定模式中创建表

#方法 1：显式指定模式名
CREATE TABLE schema_name.table_name (id SERIAL PRIMARY KEY,data TEXT
);
-- 示例：在 `analytics` 模式中创建表 `logs`
CREATE TABLE analytics.logs (id INT, log_time TIMESTAMP);#方法 2：通过 search_path 设置默认模式（见 4.2），之后直接创建表即可。

4.6：切换当前模式

#通过修改 search_path 切换当前会话的有效模式：-- 切换为 `analytics` 模式（仅当前会话有效）
SET search_path TO analytics;
-- 切换后，新创建的表默认位于此模式
CREATE TABLE new_table (...);

4.7：查看当前所在schema

SELECT current_schema(); -- 返回当前有效模式（即 `search_path` 中的第一个模式）

4.8：查看搜索路径(search path)

SHOW search_path; -- 显示当前搜索路径
-- 示例输出：`"$user", public, analytics`#修改搜索路径：
SET search_path TO schema1, schema2, public; -- 临时修改
ALTER ROLE user_name SET search_path = schema1, public; -- 永久修改

4.9：postgreSQL的模式隔离性

  • 隔离性特性：

    • 不同模式中的对象（表、视图、函数等）彼此独立，即使同名也不会冲突。

    • 访问其他模式的对象需显式指定模式名（如 schema_name.table_name）或将该模式加入 search_path。

#模式权限独立于数据库权限，需单独授权：-- 授权用户 `user1` 访问 `analytics` 模式
GRANT USAGE ON SCHEMA analytics TO user1;
-- 授权用户 `user1` 在 `analytics` 模式中创建表
GRANT CREATE ON SCHEMA analytics TO user1;

5：数据操作

5.1：添加数据

#插入单条数据
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);
-- 示例：向 `users` 表插入一条记录
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');#插入多条数据
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2),(值3, 值4);
-- 示例：批量插入用户数据
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'),('Charlie', 'charlie@example.com');#从其他表复制数据
INSERT INTO 目标表 (列1, 列2)
SELECT 列A, 列B FROM 源表 WHERE 条件;
-- 示例：将 `old_users` 表中状态为活跃的用户复制到 `users`
INSERT INTO users (name, email)
SELECT name, email FROM old_users WHERE status = 'active';

5.2：查询数据

SELECT * FROM 表名; -- 查询所有列
SELECT 列1, 列2 FROM 表名; -- 查询指定列
-- 示例：查询 `users` 表的 `name` 和 `email`
SELECT name, email FROM users;

5.3：修改数据

#更新单列
UPDATE 表名 SET 列名 = 新值 WHERE 条件;
-- 示例：将用户 Alice 的邮箱更新
UPDATE users SET email = 'new_alice@example.com' WHERE name = 'Alice';#更新多列
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;
-- 示例：更新用户状态和最后登录时间
UPDATE users 
SET status = 'inactive', last_login = NOW() 
WHERE last_login < '2023-01-01';

5.4：删除数据

#删除指定行
DELETE FROM 表名 WHERE 条件;
-- 示例：删除状态为注销的用户
DELETE FROM users WHERE status = 'deleted';#清空表
TRUNCATE TABLE 表名; -- 快速清空表（不可回滚）
TRUNCATE TABLE 表名 CASCADE; -- 级联清空关联表

6：备份与恢复

有三种不同的基本方法来备份postgresql数据：

SQL转储

文件系统级备份

连续归档

6.1：SQL转储

用途：备份单个数据库，生成 SQL 脚本或自定义格式文件。
命令格式：

pg_dump [选项] 数据库名 > 输出文件

常用选项：

-U username：指定用户名。

-h hostname：数据库服务器地址（默认本地）。

-p port：数据库端口（默认 5432）。

-F format：输出格式（p 纯文本，c 自定义压缩格式，d 目录格式）。

--create：在脚本中包含 CREATE DATABASE 命令。

-C：同 --create。

-j N：并行备份（仅目录格式支持）。

6.2：从转储中恢复

恢复工具：

  • psql：用于恢复纯文本 SQL 备份。

  • pg_restore：用于恢复自定义格式（-Fc）或目录格式备份。

# 先创建数据库（若备份不含 --create）
createdb -U myuser newdb
psql -U myuser -d newdb -f mydb_backup.sqlpsql -U myuser -d postgres -f mydb_backup.sql

6.3：使用pg_dumpall

用途：备份整个 PostgreSQL 集群（所有数据库、全局对象如角色、表空间）。
命令格式：

pg_dumpall [选项] > 输出文件

常用选项：

-U username：超级用户权限（必须）。

--globals-only：仅备份全局对象（角色、表空间）。

7：远程连接

7.1：修改postgresql监听地址

将 listen_addresses 修改为 '*'（监听所有IP）或指定IP，例如

listen_addresses = '*'  # 允许所有远程连接

7.2：配置访问权限

编辑 pg_hba.conf 文件
路径通常与 postgresql.conf 同级，添加一行规则：

# 格式：host <数据库> <用户> <IP范围> <认证方式>
host    all    all    0.0.0.0/0    md5

  • 0.0.0.0/0 允许所有IPv4地址；::/0 允许所有IPv6。

  • md5 表示需要密码认证（推荐），trust 为无密码（不安全）。

7.3：重启服务

启动服务：systemctl restart postgresql   或
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
检查服务状态：ss -tnl
关闭防火墙：

7.4：验证远程连接

  1. 检查服务监听状态
    运行以下命令确认 PostgreSQL 正在监听公共接口：

    netstat -an | grep 5432输出应包含 0.0.0.0:5432 或 :::5432。

  2. 测试远程连接
    从另一台机器使用 psql 或客户端工具连接：

    psql -h <服务器IP> -U <用户名> -d <数据库名>

8：重置密码

8.1：备份配置文件

  1. 定位 PostgreSQL 配置文件目录（通常位于 /etc/postgresql/<版本>/main/ 或 /var/lib/pgsql/data/）。

  2. 备份关键配置文件：

sudo cp /path/to/postgresql/data/pg_hba.conf /path/to/postgresql/data/pg_hba.conf.backup
sudo cp /path/to/postgresql/data/postgresql.conf /path/to/postgresql/data/postgresql.conf.backup#使用 SHOW hba_file; 在 psql 中查询 pg_hba.conf 路径。

8.2：修改配置文件

#编辑 pg_hba.conf，将认证方式改为 trust（允许本地无密码登录）：
sudo nano /path/to/postgresql/data/pg_hba.conf#找到以下行并修改：
- local   all   all   md5
+ local   all   all   trust

8.3：重启服务

# Systemd 系统
sudo systemctl restart postgresql# 或使用 pg_ctl
sudo pg_ctl restart -D /path/to/postgresql/data

8.4：修改密码

#无密码登录到 psql：
psql -U postgres  # 无需输入密码#执行密码修改命令：
ALTER USER 用户名 WITH PASSWORD '新密码';#退出 psql：
\q

8.5：恢复pg_hba.conf配置文件

#还原备份的配置文件：
sudo mv /path/to/postgresql/data/pg_hba.conf.backup /path/to/postgresql/data/pg_hba.conf#再次重启 PostgreSQL 服务：
sudo systemctl restart postgresql

http://www.xdnf.cn/news/565957.html

相关文章：

  • 阿里云合集（不定期更新）
  • 适合初学者的 Blender 第二部分
  • 1.4 C++之运算符与表达式
  • leetcode hot100刷题日记——8.合并区间
  • java综合交易所13国语言，股票，区块链，外汇，自带客服系统运营级，有测试
  • Circle宣布Circle Payments Network主网上线
  • digitalworld.local: VENGEANCE靶场
  • 网络框架二次封装：基于Kotlin的高扩展性网络请求框架完整实现
  • 网工每日一练
  • 禁止window安全中心乱删文件
  • HarmonyOS基础组件:Button三种类型的使用
  • 百度Q1财报：总营收325亿元超预期 智能云同比增速达42%
  • OceanBase 开发者大会，拥抱 Data*AI 战略，构建 AI 数据底座
  • 开源STM32F429汽车仪表盘基于LVGL界面
  • 游戏引擎学习第303天:尝试分开对Y轴和Z轴进行排序
  • SkyWalking 报错：sw_profile_task 索引缺失问题分析与解决
  • 【Java学习笔记】main方法
  • 虚幻基础：摄像机
  • 真实世界中的贝叶斯网络：Bootstrap、模型平均与非齐次动态的科研应用
  • GPT 等decoder系列常见的下游任务
  • 【VSCode】安装与 ssh 免密登录
  • 基于Springboot + vue3实现的流动摊位管理系统
  • curl: (35) Peer reports incompatible or unsupported protocol version.
  • 弱网服务器群到底有什么用
  • [案例七] NX二次开发标识特征的导入与布尔运算
  • YOLO训练输入尺寸代表什么 --input_width 和 --input_height 参数
  • java 集合总结
  • 小黑黑prompt表述短语积累1
  • 作物遗传与种质创新利用全国重点实验室-随笔10
  • CNN vs ViT：图像世界的范式演进