首页 > 数据库 >postgresql简单使用

postgresql简单使用

时间：2024-06-20 17:44:17浏览次数：28  
标签：postgresql data 数据库 简单 pgsql pg 使用 ctl mydb

postgresql.conf #系统、数据库配置文件
pg_hha.conf #客户机连接文件

pg_ctl 和pgsql
一、pg_ctl 服务管理命令
pg_ctl 是 PostgreSQL 提供的控制服务启动、停止、重启等操作的命令行工具。

常用选项和示例

  命令	选项	描述	示例
  启动 PostgreSQL 服务	start	启动数据库服务	pg_ctl start -D /usr/local/pgsql/data
  停止 PostgreSQL 服务	stop	停止数据库服务	pg_ctl stop -D /usr/local/pgsql/data
  重启 PostgreSQL 服务	restart	重启数据库服务	pg_ctl restart -D /usr/local/pgsql/data
  重新加载配置	reload	重新加载配置文件	pg_ctl reload -D /usr/local/pgsql/data
  查看服务状态	status	查看数据库服务状态	pg_ctl status -D /usr/local/pgsql/data
  守护进程模式	-l <log_file>	指定日志文件	pg_ctl start -D /usr/local/pgsql/data -l logfile
  指定配置文件	-o <options>	传递启动参数	pg_ctl start -D /usr/local/pgsql/data -o "-p 5433"
  指定主目录	-D <data_directory>	数据库主目录	pg_ctl start -D /usr/local/pgsql/data

示例

启动 PostgreSQL 服务

pg_ctl start -D /usr/local/pgsql/data

停止 PostgreSQL 服务

pg_ctl stop -D /usr/local/pgsql/data

重启 PostgreSQL 服务

pg_ctl restart -D /usr/local/pgsql/data

重新加载配置文件（无须重启服务）

pg_ctl reload -D /usr/local/pgsql/data

查看 PostgreSQL 服务状态

pg_ctl status -D /usr/local/pgsql/data

启动服务并记录日志

pg_ctl start -D /usr/local/pgsql/data -l logfile

启动服务并指定配置文件参数

pg_ctl start -D /usr/local/pgsql/data -o "-p 5433"

二、pgsql 命令行工具选项
pgsql 是 PostgreSQL 的命令行界面工具，用于执行 SQL 语句、管理数据库对象和运行脚本。

常用选项和示例

选项	              描述	      示例
-d <dbname>	指定要连接的数据库	psql -d mydb
-h <host>	指定数据库服务器的主机名	psql -h localhost -d mydb
-p <port>	指定连接的端口	psql -p 5432 -d mydb
-U <username>	指定连接的用户名	psql -U postgres -d mydb
-f <file>	从文件中执行 SQL 脚本	psql -f script.sql -d mydb
-c <command>	直接执行 SQL 命令	psql -c "SELECT * FROM mytable;" -d mydb
--set=VAR=value (-v)	设置变量，用于脚本中的变量替换	psql -v ON_ERROR_STOP=1 -f script.sql -d mydb
-l	列出所有数据库	psql -l
-W	强制输入密码	psql -W -d mydb

示例

连接到名为 mydb 的数据库

psql -d mydb

使用特定用户名连接数据库

psql -U postgres -d mydb

指定主机和端口连接

psql -h localhost -p 5432 -d mydb

从文件执行 SQL 脚本

psql -f /path/to/script.sql -d mydb

执行 SQL 命令

psql -c "SELECT * FROM mytable;" -d mydb

列出所有数据库

psql -l

强制要求输入密码

psql -W -d mydb

设置显示信息格式
\x 查询带显示，类似msyql\G

\timing on #显示执行时长

\set VERBOSITY verbose #显示详细信息，带错误源代码位置

\i /opt/test.sql

连接
postgresql.conf #系统、数据库配置文件
listen_addres="all" 设定监听本机ip

pg_hha.conf #客户机连接文件

TYPE DATABASE USER ADDRESS METHOD

type：local#本地 host#tcp/ip连接，包括ssl和ssh hostssl#必须使用ssl加密连接
databse：all #能访问什么数据库
user all #登录的用户,all为所以用户都可以登录
address ip
method #验证方法

image

关闭数据库
pg_ctl stop -D /psql/data/ -ms|mf|mi
smart：ms 等待所有连接关闭
fast：mf 快速关闭，让已有事务回滚
immediate：立即关闭数据库 相当于kill -9

利用 .pgpass文件实现免密码远程登录
#hostname:port:databse:username:password
10.0.0.200:5432:testdb:postgres:root
chmod 600 .pgpass #建议600
psql -U postgres -h 10.0.0.200 -d testdb -w #需要跟指定文件匹配才行

数据库操作

image

示例：Schema 与表操作
创建 Schema 和表

CREATE SCHEMA company;
CREATE TABLE company.employees (
	id SERIAL PRIMARY KEY,
	name VARCHAR(100),
	position VARCHAR(100)
);

插入数据

INSERT INTO company.employees (name, position) VALUES ('Alice', 'Engineer');
INSERT INTO company.employees (name, position) VALUES ('Bob', 'Manager');

查询数据

SELECT * FROM company.employees;

更新数据

UPDATE company.employees SET position = 'Senior Engineer' WHERE name = 'Alice';

删除数据

DELETE FROM company.employees WHERE name = 'Bob';

创建视图

CREATE VIEW company.engineers AS
SELECT id, name FROM company.employees WHERE position = 'Engineer';

查询视图

SELECT * FROM company.engineers;

用户权限
#与mysql不同的是，添加了角色（组），权限由角色管理，当用户需要时则将角色赋予用户。并且创建用户可以完全不带权限

用户（Role）管理

操作 命令 示例
创建角色 CREATE ROLE role_name; CREATE ROLE readonly;
创建用户 CREATE USER user_name WITH PASSWORD 'password'; CREATE USER alice WITH PASSWORD 'secret';
修改用户密码 ALTER USER user_name WITH PASSWORD 'new_password'; ALTER USER alice WITH PASSWORD 'newsecret';
修改角色属性 ALTER ROLE role_name WITH option_name; ALTER ROLE readonly WITH LOGIN;
删除角色 DROP ROLE role_name; DROP ROLE readonly;
列出角色 \du \du
显示角色信息 SELECT * FROM pg_roles WHERE rolname='role_name'; SELECT * FROM pg_roles WHERE rolname='alice';

角色属性

属性 说明
LOGIN 允许角色作为用户登录到数据库。
SUPERUSER 赋予角色超级用户权限。
CREATEDB 允许角色创建数据库。
CREATEROLE 允许角色创建和管理其他角色。
REPLICATION 允许角色进行流复制。
CONNECTION LIMIT 设定角色的最大连接数。
BYPASSRLS 允许角色绕过行级安全策略（RLS）。

常见权限类型

权限 说明
SELECT 允许读取数据。
INSERT 允许插入数据。
UPDATE 允许修改数据。
DELETE 允许删除数据。
TRUNCATE 允许截断表。
REFERENCES 允许创建外键引用。
TRIGGER 允许创建触发器。
USAGE 允许使用序列或模式（Schema）。
EXECUTE 允许执行函数或过程。
CREATE 允许创建数据库对象。
CONNECT 允许连接数据库。

权限分配和回收

操作 命令 示例
分配权限 GRANT privilege ON object TO role; GRANT SELECT ON mytable TO readonly;
回收权限 REVOKE privilege ON object FROM role; REVOKE SELECT ON mytable FROM readonly;
授予所有权限 GRANT ALL PRIVILEGES ON object TO role; GRANT ALL PRIVILEGES ON DATABASE mydb TO alice;
回收所有权限 REVOKE ALL PRIVILEGES ON object FROM role; REVOKE ALL PRIVILEGES ON DATABASE mydb FROM alice;
查看对象权限 \dp [object_name] \dp mytable
查看角色权限 \du+ role_name \du+ alice

实际操作示例
创建用户

CREATE USER bob WITH PASSWORD 'password123';

创建角色

CREATE ROLE data_analyst;

授予角色登录权限

ALTER ROLE data_analyst WITH LOGIN;

将用户添加到角色

GRANT data_analyst TO bob;

创建数据库并授予权限

CREATE DATABASE company_db;
GRANT ALL PRIVILEGES ON DATABASE company_db TO data_analyst;

创建表并分配权限

CREATE TABLE company_db.employees (
	id SERIAL PRIMARY KEY,
	name VARCHAR(100),
	position VARCHAR(100)
);
GRANT SELECT, INSERT ON company_db.employees TO data_analyst;

查看权限

\dp company_db.employees

PostgreSQL 的进程结构

Postmaster（主进程）：

Postmaster 进程是 PostgreSQL 的管理进程，负责启动和管理其他所有子进程。它在数据库服务器启动时首先启动，负责监听客户端连接、启动新的服务进程、管理系统资源以及处理信号（如关闭请求）。
客户端连接进程（Backend Process）：

每个客户端连接到数据库时，Postmaster 进程会为其生成一个新的 Backend Process。这个进程专门处理与该客户端相关的所有 SQL 操作（查询、插入、更新、删除等）。每个连接都有其独立的 Backend Process，这样可以隔离不同连接之间的操作，增强稳定性和安全性。

辅助进程（Auxiliary Processes）：

辅助进程负责处理数据库运行中的各种后台任务。常见的辅助进程包括：
WAL Writer（预写式日志进程）：
负责将内存中的 WAL (Write-Ahead Logging) 日志写入磁盘。WAL 日志记录了所有对数据库的更改，在系统崩溃时用于数据恢复。#类似事务日志；假设在脏数据未写入时数据库宕机，可通过WAL还原未宕机前的数据库状态

Checkpoint Process（检查点进程）：
定期将内存中的脏数据页刷新到磁盘，并更新检查点信息。检查点减少了恢复时间，因为系统在崩溃后只需要从最近的检查点开始重放 WAL 日志。
#当事务完成时会有一个检查点
Background Writer（后台写进程）：
负责将后台缓冲区中的脏数据页异步地刷新到磁盘，以减少检查点时的 I/O 峰值负载。#简单说就是将脏数据等到一定数量在写入磁盘
Autovacuum Process（自动清理进程）：
定期执行 VACUUM 操作，清理无效的元组，防止表膨胀，保持表性能。它还负责自动分析以更新表的统计信息。
Stats Collector（统计信息收集进程）：
收集数据库操作的统计信息，如表和索引的使用情况。这些统计数据用于优化器来决定最优的查询执行计划。
Archiver Process（归档进程）：
负责将已完成的 WAL 日志文件复制到指定的归档位置，用于备份和灾难恢复。#二进制日志

sqllogger（系统日志进程）
#默认未开启，通过配置文件postgresql.conf设置参数logging_collect设置为on开启进程
它从postmaster主进程、所有的服务进程以及其他辅助进程收集所有的stder输出，并将这些输出写入到日志文件中
startup进程
用于数据库恢复进程
session（会话进程）
每一个用户发起连接后，一旦验证成功，postmaster进程就会fork一个新的子进程负责连接此用户
#表现形式：postgres postgres[local]idie

进程间通信机制：

PostgreSQL 使用多种机制在进程之间进行通信和协作：
共享内存（Shared Memory）：
PostgreSQL 各进程共享一个全局的内存区域，用于存储元数据、缓存页等。共享内存是各进程交换数据的重要手段，如缓存管理和锁信息。
信号（Signals）：
进程间使用信号机制来通知和同步操作。例如，Postmaster 可以通过信号通知 Backend Process 进行某些动作（如关闭连接），或者辅助进程间协调动作（如后台写进程通知检查点进程）。
锁机制（Locks）：
PostgreSQL 使用锁来保证数据一致性和并发控制。锁可以防止多个进程同时修改同一数据，避免竞争条件。常见的锁包括行级锁、表级锁和页级锁。
消息队列（Message Queues）：
进程间可以通过消息队列传递指令或数据，确保任务的顺序执行和资源的有效利用。

select name,setting,short_desc from pg_setting where name="prot";
show port

pg_ident.conf与pg_hba.conf

pg_ident.conf
map1 test dba

pg_hba.conf
local all all ident map=map1
#使用ident，如果不添加map，则进入mysql时需要数据库用户与本机用户想匹配，而加入map（加入规则），则按规则；比如test对应dba用户和数据库，则使用test用户时作为dba用户进入数据库

多个事件组成一个动作，多个动作组成一个事务

WAL日志（在线日志[类事务日志]归档日志[类二进制日志]）
日志位置：$PGDATA/pg_wa/ #PG10之前未pg_xlog

归档日志：#开启需要以下参数
wal_level =replica
archive_mode=on
archive_command='[ ! -f /archive/%f ] && cp %p /archive/%f' #设置日志复制存储，不存在则复制到指定位置；可scp 存储到其他主机

备份

1. 逻辑备份与还原
逻辑备份将数据库对象和数据导出为 SQL 脚本或其他可读格式，这种方法最常用的是使用 pg_dump 和 pg_restore 工具。

1.1 pg_dump - 逻辑备份工具
pg_dump 可以导出整个数据库或其中的某些对象，支持多种格式，包括纯文本格式、压缩的归档格式、目录格式等。

基本用法：

pg_dump -U username -W -F format -f output_file database_name
-U 指定用户名。
-W 提示输入密码。
-F 指定输出格式：p (纯文本), c (压缩归档), d (目录)。
-f 指定输出文件。
database_name 是要备份的数据库名称。
示例：

备份数据库 mydb 到压缩归档文件 mydb.bak
pg_dump -U myuser -W -F c -f mydb.bak mydb
1.2 pg_restore - 逻辑还原工具
pg_restore 用于从 pg_dump 生成的归档文件或目录格式文件中还原数据库。

基本用法：
pg_restore -U username -W -d database_name -F format backup_file
-d 指定目标数据库名称。
-F 指定备份文件格式：c (压缩归档), d (目录)。
backup_file 是要还原的备份文件。
示例：
将 mydb.bak 还原到 mydb 数据库
pg_restore -U myuser -W -d mydb -F c mydb.bak
1.3 psql - 直接执行 SQL 脚本
对于纯文本格式的备份，可以直接使用 psql 工具执行 SQL 脚本进行还原。

基本用法：
psql -U username -W -d database_name -f backup_file.sql
示例：
执行 SQL 脚本 mydb.sql 还原到 mydb 数据库
psql -U myuser -W -d mydb -f mydb.sql
2. 物理备份与还原
物理备份直接复制数据库文件，可以更快地进行大规模数据的备份和恢复，常用于热备份和容灾。

2.1 pg_basebackup - 物理备份工具
pg_basebackup 是一个用于生成 PostgreSQL 数据目录的物理备份的工具。它通常用于设置流复制或全备份。

基本用法：
pg_basebackup -U username -D target_directory -Ft -z
-U 指定用户名。
-D 指定目标目录。
-Ft 指定备份格式为 tar。
-z 启用压缩。
示例：
备份数据库到 /backups/mydb 目录，并压缩为 tar 格式
pg_basebackup -U myuser -D /backups/mydb -Ft -z
2.2 文件系统级别的备份
通过复制数据库的数据目录进行备份。在进行物理备份时，确保数据库在只读模式或已停止，以防止数据不一致。
基本步骤：
停止数据库服务（如果不能使用流复制）。
pg_ctl -D /var/lib/pgsql/data stop
使用 rsync 或 cp 命令复制数据目录。
rsync -av /var/lib/pgsql/data /backups/mydb/
重启数据库服务。
pg_ctl -D /var/lib/pgsql/data start
2.3 还原物理备份
将备份的数据目录恢复到数据库服务器，通常包括以下步骤：
停止数据库服务：
pg_ctl -D /var/lib/pgsql/data stop
删除或重命名现有的数据目录：
mv /var/lib/pgsql/data /var/lib/pgsql/data.old
解压或复制备份的数据目录到数据库的原始位置：
tar -xzvf /backups/mydb/base.tar.gz -C /var/lib/pgsql/data
或者
rsync -av /backups/mydb/ /var/lib/pgsql/data/
确保权限正确，然后重启数据库：
chown -R postgres:postgres /var/lib/pgsql/data
pg_ctl -D /var/lib/pgsql/data start

标签：postgresql,data,数据库,简单,pgsql,pg,使用,ctl,mydb
From： https://www.cnblogs.com/civetcat/p/18252157

相关文章

  • HTML表格使用全指南，真的很详细
    HTML表格基础与语义化：构建结构化数据展示HTML表格是展示结构化数据的强大工具。通过合理的语义化标签使用，开发者可以创建出既美观又易于理解的表格。本文将介绍HTML表格的基本结构和语法，以及如何使用<caption>、<thead>、<tbody>、<tfoot>和<th>标签进行表格的语义化。HTML表......
  • 使用xtrabackup加密备份
    1.生成密钥串echo"123456"|md5sumf447b20a7fcbf53a5d5be013ea0b15af-#注意后面的中划线不算2.方式1-使用key#备份xtrabackup--user=mysqlbackup--password='Abc@12345678'--backup--compress--compress-threads=4--encrypt=AES256--encrypt-key=&......
  • 使用Ollama+OpenWebUI本地部署阿里通义千问Qwen2 AI大模型
    ......
  • 2024最好的轻资产创业项目有哪些 低成本高收益简单易操作的看这里
    在2024年，轻资产创业赛道展现出多样化的趋势，这些赛道不仅顺应了时代潮流，还充分展现了创新与差异化的发展思路。以下是一些值得关注的轻资产创业赛道：首先，APP拉新工作室成为了一个热门的创业项目。随着移动互联网的普及，APP的下载量和活跃度成为了衡量其成功与否的重要指标。因此......
  • Swagger的基本使用（快速入门）
    目录一、环境配置（1）导入依赖：（一般导入依赖都会报错，所以大家也可以借鉴一下这篇文章：swagger配置报错）（2）启动类添加注解二、Swagger配置介绍（1）基本配置 （2）设置扫描的包三、Swagger常用注解（1）@Api（2）@ApiOperation（3）@ApiParam（4）@ApiModel（5）@ApiIgnore（6）@ApiImplicitParam在使......
  • 1-STM32F103+ESP8266+ML307(中移4G Cat1)--硬件使用说明
    <p><iframename="ifd"src="https://mnifdv.cn/resource/cnblogs/ZLIOTB/ML307/my.html"frameborder="0"scrolling="auto"width="100%"height="1500"></iframe></p> 实物图 板载说......
  • React项目中使用轻量富文本编辑器
    React项目中使用轻量富文本编辑器安装npminstallreact-quill创建编辑器组件//src/MyQuillEditor.jsimportReact,{useState}from'react';importReactQuillfrom'react-quill';import'react-quill/dist/quill.snow.css';//引入样式constMyQuillEdi......
  • gocv安装教程（简单版）
    环境：Windowsopencv：未安装官网教程介绍：参考gocv官网教程进行安装即可，用gocv官网给的一键脚本进行安装安装MinGW-W64和cmakeMingw版本在8.1以上，手动安装需要添加环境变量cmake官网scoop用户可以直接运行以下指令安装scoopinstallmingwscoopinstallcmake一键安装open......
  • C#开发-集合使用和技巧（八）集合中的排序Sort、OrderBy、OrderByDescending
    C#开发-集合使用和技巧（八）集合中的排序Sort、OrderBy、OrderByDescendingList<T>.Sort()方法签名使用场景示例升序实现效果降序实现效果IEnumerable<T>.OrderBy()方法签名使用场景示例实现效果Enumerable<T>.OrderByDescending()使用场景示例实现效果总结在C#中，L......
  • 第二篇 使用Prometheus对网络进行监控
    1、Prometheus简介        Prometheus是一款开源系统监控和报警工具，最初由SoundCloud开发，现为CNCF（CloudNativeComputingFoundation）的项目。        主要特点：                   （1）多维数据模型：Prometheus使用带有时间戳的度量数据......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99