10.PostgreSQL日常维护_pgsql基础命令dn的解释和用途-优快云博客

本文链接：https://blog.youkuaiyun.com/lml4856/article/details/148106575

一.基本使用

1.登录数据库

pgsql登录时，必须使用postgres用户，登录后的命令提示符为“postgres=#”,postgres表示你当前所在的库

su - postgres

/usr/local/pgsql/bin/psql

2.数据库操作

2.1列出库

常见的三种方法如下：

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

常见元命令有：

\l:列出所有数据库

\c：[数据库名]或 \connect [ 数据库名]

\dn:列出所有模式（Schema）

\db:列出所有表空间

\?:显示pgsql命令的说明（元命令查询帮助）

\q:退出psql

\dt:列出当前数据库的所有表

\d[TABLE]:查看表结构

\du:列出所有用户

二：使用SQL命令

postgres=# select datname from pg_database;

pg_database是系统表：它存储了postgresql实例中所有数据库的·元信息（如数据库名称、所有者、编码等）。属于系统目录：类似MySQL的information_schema，但PostgreSQL的系统目录更底层且直接存储在pg_catalog中

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

三：postgres=# \l+

\l+的输出比\l多了size，tablespace和Description列

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

2.2创建库

postgres=# create database mydb;

2.3删除库

postgres=# drop database mydb;

2.4切换库

postgres=# \c mydb

2.5查看库大小

函数以字节为单位返回数据库的大小

postgres=# select pg_database_size('mydb');

postgres=#select pg_size_pretty(pg_database_size('mydb')); #pg_size_pretty函数将字节转为更易阅读值

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 schemaname='public'; （使用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)、data、time、timestamp和interval，还支持其他的通用功能的类型和丰富的几何类型。postgresql中可以任意数量的用户定义数据类型。因而类型名并不是语法关键字，除了SQL标准要求支持的特里外

postgres=# create table test(id int,name char(10),age int);

3.3复制表

要将已有的table_name表复制为新表new_table,包括表结构和数据，请使用以下语句

create table new_tables as tables table_name;

3.4删除表

postgres=# drop table test2;

3.5查看表结构

postgres=# \d test;

4.模式操作命令

在posstgresql中，模式（Schema）是一个逻辑容器，用于组织和管理数据库对象（如表、视图、函数、索引等）。它类似于文件系统中的文件夹，帮助你在同一个数据库中分类存储不同的对象，避免命名冲突，并实现权限隔离

4.1创建模式

在当前库postgres中创建名为hr的模式

postgres=# create schema hr；

4.2默认模式

postgreSQL每个数据库都有一个默认模式public。如果1创建对象（表、视图登）时不指定模式、默认会放在public模式中

通过search_path参数可以设置模式的搜索优先级（类似PATH环境变量）

postgres=# show search_path;

search_path用于控制对象解析顺序，避免每次查询都要写模式名

$user,public表示优先查找当前用户同名模式，再找public模式

4.3删除模式

删除空模式

postgres=# drop schema hr；

强制删除模式及其所有对象

postgres=# drop schema hr cascade；

4.4查看所有模式

元命令列出当前库中所有模式

postgres=# \dn

SQL查询，列出当前库中所有模式

postgres=# select schema_name from information_schema.schemata;

4.5在指定模式中创建表

未指定模式时，创建的对象（表、视图等）会按search_path顺序创建一个可用的模式中

在postgres库中的hr模式下创建一个名为employees的库

postgres=# create table hr.employees (id serial primary key,name text);

4.6切换当前模式

切换模式也就是调整search_path的搜索范围

切换到单个schema

set search_path to new_schema;

切换到多个schema（按优先级顺序）

set search_path to hr,public;

表示优先搜索hr模式，其次pulic

4.7查看当前所在schema

postgres=# select current_schema();

4.8查看搜索路径（search Path）

postgres=# show search_pach;

4.9PostgreSQL的模式隔离性

postgreSQL的模式是数据库内的逻辑分组，不同模式可以存在同名表。这也是和mysql的不同之处

跨模式查询需显示指定模式名（如schemal.users），或通过search_path设置默认模式

无需切换数据库连接，所有操作在同一数据库内完成

步骤1：创建一个数据库

创建数据库mydb：create database mydb；

切换到mydb：\c mydb

步骤2：在数据库中创建两个模式

创建模式schemal和schema2：

create schema schema1；

create schema schema2；

步骤3：在每个模式中创建同名表，并插入数据

在schema1中创建users表：

mydb=#create table schemal.users(id int);

mydb=#insert into schema1.users values(1);

在schema2中创建同名users表

mydb=#create table schema2.users(id int);

mydb=#insert into schema2.users values(2);

步骤4：跨模式查询

查询schema1.users和schema2.users（需显式指定模式名）

mydb=#select * from schema1.users;

mydb=#select * from schema2.users;

设置search_path切换默认模式（不需显式指定模式名）

mydb=#set seatch_path to schema1;

mydb=#select * from users; （默认访问schema1.users）

mydb=#set seatch_path to schema2;

mydb=#select * from users;

5.数据操作

5.1添加数据

在postfres库，新建表test

postgres=#create table test(id int,name char(10),age int);

postgres=#insert into test values(1,'zhangsan',18);

5.2查询数据

postgres=#select * from test;

5.3修改数据

postgres=# update test age=20 where id=1;

postgres=# select * from test;

5.4删除数据

postgres=# delete from test where id=1;

postgres=# select * from test;

6.备份与恢复

postgreSQL数据库应当被定期地备份，虽然过程相对简单，但清晰地理解其底层技术和假设是非常重要的。

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

SQL转储

文件系统级备份

连续归档

6.1SQL转储

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

pg_dump dbname > dumpfile

6.2从转储中恢复

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

其中 dumpfile 就是 pg_dump 命令的输出文件。这条命令不会创建数据库dbname，你必须在执行 psql 前自己从 template0 创建(例如，用命令 createdb-Ttemplate0 dbname)。psql 支持类似pg dump 的选项用以指定要连接的数据库服务器和要使用的用户名。参阅 psql的手册获取更多信息。非文本文件转储可以使用 pg restore 工具来恢复。

在开始恢复之前，转储库中对象的拥有者以及在其上被授予了权限的用户必须已经存在。如果它们不存在，那么恢复过程将无法将对象创建成具有原来的所属关系以及权限(有时候这就是你所需要的，但通常不是)。

默认情况下，psq1 脚本在遇到一个 SQL 错误后会继续执行。你也许希望在遇到一个 SQL 错误后让 psq1 退出,那么可以设置 ON ERROR STOP 变量来运行 psq1,这将使 psq1 在遇到 SQL 错误后退出并返回状态 3:
psql --set ON_ ERROR_ STOP=on dbname < infile
不管怎样，你将只能得到一个部分恢复的数据库。作为另一种选择，你可以指定让整个恢复作为一个单独的事务运行,这样恢复要么完全完成要么完全回滚这种模式可以通过向 psql 传递-1 或--single-transaction 命令行选项来指定。在使用这种模式时,注意即使是很小的一个错误也会导致运行了数小时的恢复被回滚。但是，这仍然比在一个部分恢复后手工清理复杂的数据库要更好。
pg dump 和 psq1 读写管道的能力使得直接从一个服务器转储一个数据库到另一个服务器成为可能，例如:
pg_dump -h host1 dbname | psql -h host2 dbname

注意：

pg_dump产生的转储是相当于template0.这意味着在template1中加入的任何语言、过程等都会被pg_dump转储，如果在恢复时使用的是一个自定义的template1，你必须从template0创建一个空的数据库

6.3使用pg_dumpall

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

pg_dumpall > dumpfile

转储的结果可以使用psql恢复：

psql -f dumpfile postgres

7.远程连接

7.1修改postgreSQL监听地址

默认postgreSQL监听地址是127.0.0.1，别的机器无法远程连接上，所以需要调整，修改postgreSQL.conf文件

通过dnf安装的pgsql配置文件位置：

/var/lib/pgsql/data/postgresql.conf

通过源码编译安装的pgsql配置文件位置：

/sur/local/pgsql/data/postgresql.conf

下面以dnf安装为例：

更改第60行，取消注释并把localhost改成*

grep 'listen_addresses' /var/lib/pgsql/data/postgresql.conf

重启服务

systemctl restart postgresql

ss -tnl

7.2配置访问权限

默认只能本地访问postgreSQL，我们需要在pg_hba.conf里面配置

找到IPv4 local connections这一行，在下面添加：

host all all 0.0.0.0/0 trust

vim /usr/local/pgsql/data/pg_hba.conf

host:这指定了连接类型。host 表示该规则适用于通过 TCP/IP 进行的远程连接。如果是本地连接，通常会使用 loca1。

all:这定义了哪些数据库可以接受这个规则。al1表示这个规则适用于所有数据库。你也可以指定特定的数据库名，例如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 表示不需要密码或其他任何形式的认证，客户端可以直接连接。这通常只在本地或受信任的网络环境中使用，因为它允许任何人无需认证即可访问数据库。请注意,使用 trust 认证方法允许任何 IP 地址连接到你的数据库，而不需要任何认证，这是非常不安全的。这通常只在开发或测试环境中使用,并且应该始终确保数据库服务器不暴露在不受信任的网络中在生产环境中，你应该使用更安全的认证方法，如md5或 password(对于较新版本的 PostgreSQL，建议使用scram-sha-256)

如果不是设置的trust，而是选择了md5或password之类的，需要有密码才行，配置 PostgreSQl密码流程如下

postgres=# ALTER USER postgres WITH PASSWORD '123456’;

ALTER ROLE