PostgreSQL日常维护

最新推荐文章于 2025-05-22 16:41:47 发布

zhcong_

最新推荐文章于 2025-05-22 16:41:47 发布

阅读量840

点赞数 11

文章标签：数据库

本文链接：https://blog.csdn.net/zhcong_/article/details/148107049

版权

（4）客户端创建用户postgres并切换用户

（5）连接

一，基本使用

1，登录数据库

先切换用户，再登录

2，数据库操作

方法一

在PostgreSQL的交互式终端psql中，“\”开头的命令称为元命令(类似MySQL的SHOW语句)，用于快速管理数据库常用元命令有:

\l	列出所有数据库。
\c	[数据库名]或connect[数据库名
\dn	列出所有模式(Schema)。
\db	列出所有表空间。
\?	显示pgsql命令的说明(元命令查询帮助)
\q	退出psql
\dt	列出当前数据库的所有表
\d [TABLE]	查看表结构
\du	列出所有用户

方法二

\l+的输出比\多了Size,Tablespace 和 Deseription 列

+:扩展输出，显示更多字段或详细信息

方法三

pg database是系统表:它存储了PostgreSQL实例中所有数据库的元信息(如pg database数据库名称、所有者、编码等)。属于系统目录(SystemCatalog):类似MySQL的 information_schema，但PostgreSQL的系统目录更底层且直接存储在pg_catalog 式中。

pg_database是系统目录表，所以无论当前连接到哪个数据库，该表始终可见系统表默认属于pg_catalog模式，而pg_catalog始终位于搜索路径(searchpath)的首位。因此，查询时无需显式指定模式(pg catalog.pg_database)。

（1）创建库

（2）删除库

（3）切换库

3，数据库操作

（1）创建表

（2）列出表

4，模式操作命令

在Postgres中，模式(5chem)是一个辑容器，用于组织和管理据库对象(如表、视图、函数、索引等)。它类似于文件系统中的文件头，帮作在同一个数据库中分天存储不同的对象，避免命名冲，并实现权离

（1）创建模式

（2）默认模式

PostgreSQL每个数据库都有一个默认模式public。如果创建对象(表、视图等)时不指定模式，默认会放在public模式中
通过searchpath参数可以设置模式的搜索优先级(类似PAH环量:

search_path用于控制对象解析顺序，避免每次查询都要写模式名$user，public表示优先查找当前用户同名模式，再找public模式

（3）查看所有模式

（4）查看当前所在schema

5，PostgreSQL的模式隔离性

PostgreSQl的模式是数据库内的逻辑分组，不同模式可以存在同名表。这也是和mysql的不同之处
跨模式查询需显式指定模式名(如schema1.users),或通过searchpath设置默认模式。
无需切换数据库连接，所有操作在同一数据库内完成。

（1）切换数据库mydb中

（2）在数据库中创建两个模式

（3）在每个模式中创建同名表，并插入数据

（4）跨模式查询

6，备份与恢复

PostgreSQL数据库应当被定期地备份。虽然过程相当简单，但清晰地理解其底层技术和假设是非常重要的。
有三种不同的基本方法来备份PostgreSQL数据:

SQL转储

文件系统级备份
连续归档

每一种都有其优缺点，我们主要以SQL转储为主。

（1）SQL转储

SQL转储方法的思想是创建一个由SQL命令组成的文件，当把这个文件回馈给服务器时，服务器将利用其中的SQL命令重建与转储时状态一样的数据库。PostgreSQ为此提供了工具pgdump。这个工具的基本用法是:

删除库

正如你所见，pg_dump把结果输出到标准输出。我们后面将看到这样做有什么用处。尽管上述命令会创建一个文本文件，pgdump可以用其他格式创建文件以支持并行和细粒度的对象恢复控制。
Pg_dump是一个普通的PostgreSQL客户端应用(尽管是个相当聪明的东西)这就意味着你可以在任何可以访问该数据库的远端主机上进行备份工作。但是请记住pg_dump不会以任何特殊权限运行。具体说来，就是它必须要有你想备份的表的读权限，因此为了备份整个数据库你几乎总是必须以一个数据库超级用户来运行它(如果你没有足够的特权来备份整个数据库，你仍然可以使用诸如-nschema或-ttable选项来备份该数据库中你能够访问的部分)。
要声明pg_dump连接哪个数据库服务器，使用命令行选项-hhost和-pport。默认主机是本地主机或你的PGHOST环境变量指定的主机。类似地,默认端口是环境变量 PGPORT或(如果 PGPORT不存在)内建的默认值。(服务器通常有相同的默认值，所以还算方便。)
和任何其他 PostgreSQL客户端应用一样，pg_dump 默认使用与当前操作系统用户名同名的数据库用户名进行连接。要使用其他名字，要么声明-U选项，要么设置环境变量PGUSER。请注意pgdump的连接也要通过客户认证机制。
pg_dump对于其他备份方法的一个重要优势是，pg_dump的输出可以很容易地在新版本的PostgreSQL中载入，而文件级备份和连续归档都是极度的服务器版本限定的。pgdump也是唯一可以将一个数据库传送到一个不同机器架构上的方法，例如从一个32位服务器到一个64位服务器由pg_dump创建的备份在内部是一致的，也就是说,转储表现了pg_dump开始运行时刻的数据库快照,且在pg_dump运行过程中发生的更新将不会被转储(但是会阻塞那些需要pg_dump工作的时候并不阻塞其他的对数据库的操作。排它锁的操作，比如大部分形式的ALTERTABLE)

（2）从转储中恢复

注意：恢复之前先创建一个mydb库

pg_dump生成的文本文件可以由psq1程序读取。从转储中恢复的常用命令是:

登录数据库查看

其中dumpfile就是pg_dump命令的输出文件。这条命令不会创建数据库dbname，你必须在执行psql前自己从template0创建(例如，用命令createdb-Ttemplate0 dbname)。psql支持类似pg_dump的选项用以指定要连接的数据库服务器和要使用的用户名。参阅psql的手册获取更多信息。非文本文件转储可以使用pg_restore 工具来恢复。储可以使用pg_restore 工具来恢复。
在开始恢复之前，转储库中对象的拥有者以及在其上被授予了权限的用户必须已经存在。如果它们不存在，那么恢复过程将无法将对象创建成具有原来的所属关系以及权限(有时候这就是你所需要的，但通常不是)。
默认情况下，psql脚本在遇到一个SQL错误会继续执行。你也许希望在遇到一个 SQL 错误后让 psql退出,那么可以设置ON ERROR STOP变量来运行 psql,这将使psq1在遇到SQL错误后退出并返回状态3:

不管怎样，你将只能得到一个部分恢复的数据库。作为另一种选择，你可以指定让整个恢复作为一个单独的事务运行,这样恢复要么完全完成要么完全回滚这种模式可以通过向 psql传递-1或--single-transaction 命令行选项来指定。在使用这种模式时,注意即使是很小的一个错误也会导致运行了数小时的恢复

（3）使用pg_dumpall

pg_dump每次只转储一个数据库，而且它不会转储关于角色或表空间(因为它们是集簇范围的)的信息。为了支持方便地转储一个数据库集簇的全部内容，提供了p8dumpall程序。pg_dumpa1l备份一个给定集簇中的每一个数据库，并且也保留了集簇范围的数据，如角色和表空间定义。该命令的基本用法是:

(实际上，你可以指定恢复到任何已有数据库名，但是如果你正在将转储载入到一个空集簇中则通常要用(postgres)。在恢复一个pg_dumpall转储时常常需要具有数据库超级用户访问权限，因为它需要恢复角色和表空间信息。如果你在使用表空间，请确保转储中的表空间路径适合于新的安装。
pg_dumpal1工作时会发出命令重新创建角色、表空间和空数据库，接着为每一个数据库pg_dump。这意味着每个数据库自身是一致的，但是不同数据库的快照并不同步。
集簇范围的数据可以使用pgdumpa1l的-globals-only选项来单独转储。如果在单个数据库上运行pg_dump命令，上述做法对于完全备份整个集簇是必需的。