做网站要注意哪些问题,公司做的网站账务处理,地图网站设计,学室内装修设计需要什么条件目录 前言
基本使用
1. 登录数据库
2. 数据库操作
2.1 列出库
2.2 创建库
2.3 删除库
2.4 切换库
2.5 查看库大小
3. 数据表操作
3.1 列出表
3.2 创建表
3.3 复制表
3.4 删除表
3.5 查看表结构
4. 模式操作命令
4.1 创建模式
4.2 默认模式
4.3 删除模式
4.4…目录 前言
基本使用
1. 登录数据库
2. 数据库操作
2.1 列出库
2.2 创建库
2.3 删除库
2.4 切换库
2.5 查看库大小
3. 数据表操作
3.1 列出表
3.2 创建表
3.3 复制表
3.4 删除表
3.5 查看表结构
4. 模式操作命令
4.1 创建模式
4.2 默认模式
4.3 删除模式
4.4 查看所有模式
4.5 在指定模式中创建表
4.6 切换当前模式
4.7 查看当前所在 schema
4.8 查看搜索路径Search Path
4.9 PostgreSQL 的模式隔离性
5. 数据操作
5.1 添加数据
5.2 查询数据
5.3 修改数据
5.4 删除数据
6. 备份与恢复
6.1 SQL 转储
6.2 从转储中恢复
6.3 使用 pg_dumpall
7. 远程连接
7.1 修改 PostgreSQL 监听地址
7.2 配置访问权限
7.3 重启服务
7.4 验证远程连接
8. 重置密码
8.1 备份配置文件
8.2 修改配置文件
8.3 重启服务
8.4 修改密码
8.5 恢复 pg_hba.conf 配置文件
总结 前言
PostgreSQL 作为功能强大的开源关系型数据库在企业级开发与数据分析中应用广泛本内容围绕其日常维护展开涵盖从基础操作到远程连接、备份恢复、密码重置等关键技能助力使用者系统化掌握维护能力为高效管理数据库奠定基础。
基本使用
1. 登录数据库
Pgsql 登录时必须使用 postgres 用户登录后的命令提示符为 postgres#postgres 表示你当前所在的库。
[postgreslocalhost ~]$ psql
psql (15.4)
Type help for help.
2. 数据库操作
2.1 列出库
常用的三种方法如下
方法一使用元命令\l
postgres# \l在 PostgreSQL 的交互式终端 psq1 中 开头的命令称为元命令类似 MySQL 的 SHOW 语句用于快速管理数据库。 常用元命令有 \l列出所有数据库。 \c [数据库名]或\connect [数据库名]连接到指定数据库。 \dn列出所有模式Schema。 \db列出所有表空间。 \?显示 pgsq1 命令的说明元命令查询帮助。 \q退出 psql。 \dt列出当前数据库的所有表。 \d [TABLE]查看表结构。 \du列出所有用户。 方法二使用元命令\l
postgres# \l\l的输出比\l多了 Size、Tablespace 和 Description 列。表示扩展输出显示更多字段或详细信息。
方法三使用 SQL 命令
postgres# SELECT datname FROM pg_database;pg_database是系统表它存储了 PostgreSQL 实例中所有数据库的元信息如数据库名称、所有者、编码等。属于系统目录System Catalog类似 MySQL 的 information_schema但 PostgreSQL 的系统目录更底层且直接存储在 pg_catalog 模式中。pg_database 是系统目录表所以无论当前连接到哪个数据库该表始终可见。系统表默认属于 pg_catalog 模式而 pg_catalog 始终位于搜索路径search_path的首位。因此查询时无需显式指定模式如 pg_catalog.pg_database。
2.2 创建库
postgres# create database mydb;
CREATE DATABASE2.3 删除库
postgres# drop database mydb;
DROP DATABASE2.4 切换库
postgres# \c mydb
mydb# You are now connected to database mydb as user postgres.2.5 查看库大小
函数以字节为单位返回数据库的大小
postgres# select pg_database_size(mydb);pg_database_size
------------------7484207
(1 row) pg_size_pretty()函数将字节转为更易于阅读的值 postgres# select pg_database_size(mydb);pg_database_size
------------------7484207
(1 row)3. 数据表操作
3.1 列出表
列出表的常用方法
mydb# \dt;列出表显示 search_path 中模式里的表默认 public。mydb# \d列出表、视图和序列。mydb# \d列出详细信息。mydb# \dt my_schema.*列出指定模式下的表例如 my_schema。mydb# \dt*.*查看当前数据库的所有表包括系统表。mydb# SELECT * FROM pg_tables WHERE schemanamepublic;使用 SQL 方式列出当前数据库中 public 模式下的所有表及其详细信息。 pg_tables是视图属于 pg_catalog 模式但它是基于 pg_class 和 pg_namespace 的逻辑视图并非物理表。无需切换数据库直接查询 pg_catalog.pg_tables 即可获取当前数据库的表信息。
3.2 创建表
PostgreSQL 支持标准的 SQL 类型 int、smallint、real、double precision、char (N)、varchar (N)、date、time、timestamp 和 interval还支持其他的通用功能的类型和丰富的几何类型。PostgreSQL 中可以定制任意数量的用户定义数据类型。因而类型名并不是语法关键字除了 SQL 标准要求支持的特例外。
postgres# create table test (id int, name char(10), age int);
CREATE TABLE3.3 复制表
要将已有的 table_name 表复制为新表 new_table包括表结构和数据请使用以下语句
CREATE TABLE new_table AS TABLE table_name;例如
postgres# CREATE TABLE test2 AS TABLE test;
postgres# \dt架构模式 | 名称 | 类型 | 拥有者
------------------------------------------------hr | employees | 数据表 | postgreshr | test | 数据表 | postgreshr | test2 | 数据表 | postgres
(3行记录)3.4 删除表
postgres# drop table test2;3.5 查看表结构
postgres# \d test;栏位 | 类型 | 校对规则 | 可空的 | 预设
------------------------------------------------------------------------------------id | integer | | |name | character(10) | | |age | integer | | |4. 模式操作命令
在 PostgreSQL 中模式Schema是一个逻辑容器用于组织和管理数据库对象如表、视图、函数、索引等。它类似于文件系统中的文件夹帮助你在同一个数据库中分类存储不同的对象避免命名冲突并实现权限隔离。
4.1 创建模式
在当前库 postgres 中创建名为 hr 的模式
postgres# CREATE SCHEMA hr;
CREATE SCHEMA4.2 默认模式
PostgreSQL 每个数据库都有一个默认模式 public。如果创建对象表、视图等时不指定模式默认会放在 public 模式中。通过 search_path 参数可以设置模式的搜索优先级类似 PATH 环境变量
postgres# SHOW search_path;search_path
-------------$user,public
(1行记录)search_path 用于控制对象解析顺序避免每次查询都要写模式名。$user,public 表示优先查找当前用户同名模式再找 public 模式。
4.3 删除模式
删除空模式
postgres# DROP SCHEMA hr;
DROP SCHEMA强制删除模式及其所有对象
postgres# DROP SCHEMA hr CASCADE;
DROP SCHEMA4.4 查看所有模式
元命令列出当前库中所有模式
postgres# \dn名称 | 架构模式列表 | 拥有者
-----------------------------------------public | pg_database_owner |
(1行记录)SQL 查询列出当前库中所有模式
postgres# SELECT schema_name FROM information_schema.schemata;schema_name
---------------information_schemapg_catalogpg_toastpublic
(4行记录)4.5 在指定模式中创建表
未指定模式时创建的对象表视图等会按 search_path 顺序创建到第一个可用的模式中。在 postgres 库中的 hr 模式下创建一个名为 employees 的表
postgres# CREATE TABLE hr.employees (id int , name char(10));4.6 切换当前模式
切换模式也就是调整 search_path 的搜索范围。 切换到单个 schema
SET search_path TO new_schema;切换到多个 schema按优先级顺序
SET search_path TO hr, public;表示优先搜索 hr 模式其次 public。
4.7 查看当前所在 schema
postgres# SELECT current_schema();current_schema
----------------hr
(1行记录)4.8 查看搜索路径Search Path
postgres# SHOW search_path;search_path
-------------hr, public
(1行记录)4.9 PostgreSQL 的模式隔离性
PostgreSQL 的模式是数据库内的逻辑分组不同模式可以存在同名表。这也是和 mysql 的不同之处。跨模式查询需显式指定模式名如 schema1.users或通过 search_path 设置默认模式。无需切换数据库连接所有操作在同一数据库内完成。 步骤 1创建一个数据库
postgres# CREATE DATABASE mydb;
postgres# \c mydb步骤 2在数据库中创建两个模式
mydb# CREATE SCHEMA schema1;
mydb# CREATE SCHEMA schema2;步骤 3在每个模式中创建同名表并插入数据 mydb# CREATE TABLE schema1.users (id int);
mydb# INSERT INTO schema1.users VALUES (1);
mydb# CREATE TABLE schema2.users (id int);
mydb# INSERT INTO schema2.users VALUES(2);步骤 4跨模式查询
mydb# SELECT * FROM schema1.users;
mydb# SELECT * FROM schema2.users;设置 search_path 切换默认模式不需显式指定模式名
mydb# SET search_path TO schema1;
mydb# SELECT * FROM users; -- 默认访问schema1.users
mydb# SET search_path TO schema2;
mydb# SELECT * FROM users; -- 默认访问schema2.users5. 数据操作
5.1 添加数据
在 postgres 库新建表 test
postgres# create table test(id int,name char(10),age int);
CREATE TABLE
postgres# insert into test values(1,zhangsan, 18);
INSERT 0 15.2 查询数据
postgres# select * from test;id | name | age
-----------------1 | zhangsan | 18
(1行记录)5.3 修改数据
postgres# update test set age30 where id1;
UPDATE 1
postgres# select * from test;id | name | age
-----------------1 | zhangsan | 30
(1行记录)5.4 删除数据
postgres# delete from test where id1;
DELETE 1
postgres# select * from test;id | name | age
---------------
(0行记录)6. 备份与恢复
PostgreSQL 数据库应当被定期地备份。虽然过程相当简单但清晰地理解其底层技术和假设是非常重要的。有三种不同的基本方法来备份 PostgreSQL 数据SQL 转储、文件系统级备份、连续归档。每一种都有其优缺点我们主要以 SQL 转储为主。
6.1 SQL 转储
SQL 转储方法的思想是创建一个由 SQL 命令组成的文件当把这个文件回馈给服务器时服务器将利用其中的 SQL 命令重建与转储时状态一样的数据库。PostgreSQL 为此提供了工具pg_dump。这个工具的基本用法是
pg_dump dbname dumpfile正如你所见pg_dump把结果输出到标准输出。pg_dump是一个普通的 PostgreSQL 客户端应用尽管是个相当聪明的东西。这就意味着你可以在任何可以访问该数据库的远端主机上进行备份工作。但是请记住pg_dump不会以任何特殊权限运行。具体说来就是它必须要有你想备份的表的读权限因此为了备份整个数据库你几乎总是必须以一个数据库超级用户来运行它。 要声明pg_dump连接哪个数据库服务器使用命令行选项-h host和-p port。默认主机是本地主机或你的 PGHOST 环境变量指定的主机。类似地默认端口是环境变量 PGPORT 或如果 PGPORT 不存在内建的默认值。 和任何其他 PostgreSQL 客户端应用一样pg_dump默认使用与当前操作系统用户名同名的数据库用户名进行连接。要使用其他名字要么声明-U选项要么设置环境变量 PGUSER。请注意pg_dump的连接也要通过客户认证机制。 pg_dump对于其他备份方法的一个重要优势是pg_dump的输出可以很容易地在新版本的 PostgreSQL 中载入而文件级备份和连续归档都是极度的服务器版本限定的。pg_dump也是唯一可以将一个数据库传送到一个不同机器架构上的方法例如从一个 32 位服务器到一个 64 位服务器。 由pg_dump创建的备份在内部是一致的也就是说转储表现了pg_dump开始运行时刻的数据库快照且在pg_dump运行过程中发生的更新将不会被转储。pg_dump工作的时候并不阻塞其他的对数据库的操作。但是会阻塞那些需要排它锁的操作比如大部分形式的 ALTER TABLE
6.2 从转储中恢复
pg_dump生成的文本文件可以由psql程序读取。从转储中恢复的常用命令是
psql dbname dumpfile其中 dumpfile 就是pg_dump命令的输出文件。这条命令不会创建数据库 dbname你必须在执行psql前自己从 template0 创建例如用命令createdb -T template0 dbname。psql支持类似pg_dump的选项用以指定要连接的数据库服务器和要使用的用户名。非文本文件转储可以使用pg_restore工具来恢复。 在开始恢复之前转储库中对象的拥有者以及在其上被授予了权限的用户必须已经存在。如果它们不存在那么恢复过程将无法将对象创建成具有原来的所属关系以及权限有时候这就是你所需要的但通常不是。 默认情况下psql脚本在遇到一个 SQL 错误后会继续执行。你也许希望在遇到一个 SQL 错误后让psql退出那么可以设置ON_ERROR_STOP变量来运行psql这将使psql在遇到 SQL 错误后退出并返回状态 3
psql --set ON_ERROR_STOPon dbname infile不管怎样你将只能得到一个部分恢复的数据库。作为另一种选择你可以指定让整个恢复作为一个单独的事务运行这样恢复要么完全完成要么完全回滚。这种模式可以通过向psql传递-1或--single-transaction命令行选项来指定。 pg_dump和psql读写管道的能力使得直接从一个服务器转储一个数据库到另一个服务器成为可能例如
pg_dump -h host1 dbname | psql -h host2 dbname注意pg_dump产生的转储是相对于template0。这意味着在template1中加入的任何语言、过程等都会被pg_dump转储。结果是如果在恢复时使用的是一个自定义的template1你必须从template0创建一个空的数据库。 一旦完成恢复在每个数据库上运行ANALYZE是明智的举动这样优化器就有有用的统计数据了。
6.3 使用 pg_dumpall
pg_dump每次只转储一个数据库而且它不会转储关于角色或表空间因为它们是集簇范围的的信息。为了支持方便地转储一个数据库集簇的全部内容提供了pg_dumpall程序。pg_dumpall备份一个给定集簇中的每一个数据库并且也保留了集簇范围的数据如角色和表空间定义。该命令的基本用法是
pg_dumpall dumpfile转储的结果可以使用psql恢复
psql -f dumpfile postgres实际上你可以指定恢复到任何已有数据库名但是如果你正在将转储载入到一个空集簇中则通常要用 postgres。在恢复一个pg_dumpall转储时常常需要具有数据库超级用户访问权限因为它需要恢复角色和表空间信息。如果在使用表空间请确保转储中的表空间路径适合于新的安装。
7. 远程连接
7.1 修改 PostgreSQL 监听地址
默认 PostgreSQL 监听的地址是 127.0.0.1别的机器无法远程连接上所以需要调整修改 postgresql.conf 文件。 通过 dnf 安装的 pgsql 配置文件在/var/lib/pgsq1/data/postgresql.conf通过源码编译安装的 pgsql 配置文件在/usr/local/pgsq1/data/postgresql.conf
下面操作以 dnf 安装为例 更改第 60 行取消注释并把localhost改成 *
[rootbogon ~]# grep listen_addresses /var/lib/pgsq1/data/postgresql.conf
listen_addresses* # what IP address(es) to listen on;重启服务
[postgreslocalhost ~]$ pg_ctl -D /usr/local/pgsql/data -l logfile restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[rootlocalhost ~]# netstat -anpt |grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 19958/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 19958/postgres
7.2 配置访问权限
默认是只能本地访问 PostgreSQL 的我们需要在 pg_hba.conf 里面配置。找到 IPv4 local connections 这一行在这一行下面添加 [postgreslocalhost ~]$ vi /usr/local/pgsql/data/pg_hba.conf host all all 0.0.0.0/0 trusthost这指定了连接类型。host 表示该规则适用于通过 TCP/IP 进行的远程连接。如果是本地连接通常会使用 local。all这定义了哪些数据库可以接受这个规则。all 表示这个规则适用于所有数据库。也可以指定特定的数据库名例如 mydatabase。all这定义了哪些用户可以接受这个规则。all 表示这个规则适用于所有用户。也可以指定特定的用户名例如 myuser。0.0.0.0/0这定义了哪些客户端 IP 地址或 IP 地址范围可以接受这个规则。0.0.0.0/0 是一个特殊的 CIDR 表示法它表示任何 IP 地址即没有 IP 地址限制。也可以指定具体的 IP 地址如 192.168.1.100或者 IP 地址范围如 192.168.1.0/24。trust这定义了认证方法。trust 表示不需要密码或其他任何形式的认证客户端可以直接连接。这通常只在本地或受信任的网络环境中使用因为它允许任何人无需认证即可访问数据库。在生产环境中应该使用更安全的认证方法如 md5 或 password对于较新版本的 PostgreSQL建议使用 scram-sha-256。 如果不是设置的 trust而是选择了 md5 或 password 之类的需要有密码才行配置 PostgreSQL 密码流程如下
postgres# ALTER USER postgres WITH PASSWORD 123456;
ALTER ROLE7.3 重启服务
[postgreslocalhost ~]$ pg_ctl -D /usr/local/pgsql/data -l logfile restart7.4 验证远程连接
使用其它主机远程连接本机数据库设置的是 trust无密码可直接登录
[postgreslocalhost ~]$ psql -h 192.168.10.102
psql (15.4)
Type help for help. 如果设置的是 md5 或 password 之类的需要有密码才行
[postgreslocalhost ~]$ psql -h 192.168.10.102
Password for user postgres:
psql (15.4)
Type help for help.
8. 重置密码
8.1 备份配置文件
对 pg_hba.conf 文件进行备份
[rootlocalhost ~]# cp /var/lib/pgsq1/data/pg_hba.conf /var/lib/pgsq1/data/pg_hba.confbak8.2 修改配置文件
修改配置文件以信任本地连接不需要密码。将配置文件中的 scram-sha-256 或者 md5 修改为 trust
[rootlocalhost ~]# vim /var/lib/pgsq1/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md58.3 重启服务
[postgreslocalhost ~]$ pg_ctl -D /usr/local/pgsql/data -l logfile restart8.4 修改密码
登录数据库修改密码密码自定义
postgres# ALTER USER postgres WITH PASSWORD 123456789;
ALTER ROLE8.5 恢复 pg_hba.conf 配置文件
将 pg_hba.confbak 文件的内容覆盖 pg_hba.conf重启 PostgreSQL 数据库服务器重新登陆时如果提示输入密码则输入刚才修改的密码即可。
总结
本文围绕 PostgreSQL 日常维护系统讲解了基本使用登录、数据库及数据表操作、模式管理、数据操作、备份与恢复、远程连接配置及密码重置等内容助力读者掌握相关核心技能为数据库高效管理和后续高级功能学习奠基。
