目录
一：基本使用
1.登录数据库
Pgsql登录时，必须使用postgres用户，登录后的命令提示符为“postgres=#”
postgres表示你当前所在的库
[root@bogon ~]# su - postgres
[postgres@bogon ~]$ /usr/local/pgsql/bin/psql
psql (16.3)
Type "help" for help.
postgres=#2.数据库操作
2.1列出库
方法一：
postgres=# \l
在PostgreSQL的交互式终端psql中,"\"开头的命令称为元命令(类似
MySQL的SHOW语句),用于快速管理数据库
常用元命令有:
/l 列出所有数据库。
\c [数据库名]或\connect[数据库名]
\dn 列出所有模式(Schema)。
\db 列出所有表空间。
\? 显示pgsql命令的说明(元命令查询帮助)
\q 退出psql
\dt 列出当前数据库的所有表
\d [TABLE] 查看表结构
\du 列出所有用户方法二：
postgres=# \l+
\1+的输出比\l多了Size,Tablespace和Description列
+:扩展输出,显示更多字段或详细信息方法三：
使用SQL命令
postgres=# SELECT datname FROM pg_database;pg_database是系统表:它存储了PostgreSQL实例中所有数据库的元信息(如数据库名称、所有者、编码等)。
pg_database是系统目录表,所以无论当前连接到哪个数据库该表始终可见系统表默认属于pg_catalog模式,而pg_catalog始丝终位于搜索路径search path)的首位。
2.2创建库
postgres=# create database mydb;
CREATE DATABASE
2.3删除库
postgres=# drop database mydb;
DROP DATABASE
2.4切换库
postgres=# \c_mydb
You are now connected to database "mydb" as user "postgres"
mydb=#
2.5查看库大小
函数以字节为单位返回数据库的大小
postgres=# SELECT pg_database_size('mydb');
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';
使用SLQL方式列出当前数据库中 public 模式下的所有表及其详细信息
pg_tables 是视图:属于pg_catalog模式,但它是基于Fpg_class和pg_namespace的逻辑视图,并非物理表。无需切换数据库,直接查询pg_catalog.pg_tables即可获取当前数据库的表信息
3.2创建表
PostgreSQL支持标准的SQL类型int、smallint、real、double precisionchar(N)、varchar(N)、date、time、time、timestamp和interval,还支持其他的通用功能的类型和丰富的几何类型。PostgreSQL中可以定制任意数量的用户定义数据类型。因而类型名并不是语法关键字,除了SQL标准要要求支持的特例外
postgres=# create table test(id int, name char(10), ageint)
CREATE TABLE
3.3复制表
要将已有的table_name表复制为新表 new_table,包括表结构沟和数据,请
使用以下语句
CREATE TABLE new_table AS TABLE table_name;
例如:
postgres=# CREATE TABLE test2 AS TABLE test;
SELECT 0
postgres=# \dt
3.4删除表
postgres=# drop table_test2;
3.5查看表结构
postgres=# \d_test;
4.模式操作命令
在PostgreSQL中,模式(Schema)是一个逻辑容器,用于组织和管理数据库对象(如表、视图、函数、索引等)。它类似于文件系统中的文件夹,帮助你在同一个数据库中分类存储不同的对象,避免命名冲突,并实现权限隔离
4.1创建模式
在当前库postgres中创建名为hr的模式
postgres=# CREATE SCHEMA hr;
CREATE SCHEMA
4.2默认模式
PostgreSQL每个数据库都有一个默认模式public。
如果创建对象(表、视图等)时不指定模式,默认会放在pubblic 模式中
通过search_path参数可以设置模式的搜索优先级(类似PATH 环境变量)
postgres=# SHOW search_path;search_path用于控制对象解析顺序,避免每次查询都要写模式名
$user,public表示优先查找当前用户同名模式,再找public模式
4.3删除模式
删除空模式
postgres=# DROP SCHEMA hr;
DROP SCHEMA
强制删除模式及其所有对象
postgres=# DROP SCHEMA hr CASCADE;
DROP SCHEMA
4.4查看所有模式
元命令列出当前库中所有模式
postgres=# \dnSQL查询,列出当前库中所有模式
postgres=# SELECT schema_name FROM information_scherma. 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模式,其次public
4.7查看当前所在schema
postgres=# SELECT_current_schema
4.8查看搜索路径（Search Path）
postgres=# SHOW search_path;
4.9PostgreSQL的模式隔离性
PostgreSQL的模式是数据库内的逻辑分组,不同模式可以存在同名表。这也是和mysql的不同之处
跨模式查询需显式指定模式名(如schemal.users),或通过search_path设置默认模式。
无需切换数据库连接,所有操作在同一数据库内完成。
步骤一：创建一个数据库
创建数据库 mydb
postgres=#CREATE_DATABASE mydb;
切换到mydb
postgres=#\c_mydb
步骤二：在数据库中创建两个模式
创建模式schemal和schema2
mydb=#CREATE SCHEMA schemal;
mydb=#CREATE SCHEMA schema2;
步骤三：在每个模式中创建同名表，并插入数据
在schemal中创建users表
mydb=#CREATE TABLE schemal.users (id int);
mydb=#INSERT INTO schemal.users VALUES(1);
在schema2中创建同名 users表
mydb=#CREATE TABLE schema2.users (id int);
mydb=#INSERT INTO schema2. users VALUES (2);
步骤四：跨模式查询
查询schemal.users和schema2.users(需显式指定模式名)
mydb=#SELECT * FROM schemal.users;
mydb=#SELECT * FROM schema2.users;
设置searchpath切换默认模式(不需显式指定模式名)
mydb=#SET search_path TO schemal;
mydb=#SELECT*FROM users;--默认访问 schemal.users
mydb=#SET search_path TO schema2;
mydb=#SELECT*FROM users;--默认访问 schema2.userrs
5.数据操作
5.1添加数据
在postgres库,新建表test
postgres=# create table test(id int, name char(10),ageint);
CREATE TABLE
postgres=#insertintotest values(1, zhangsan',18);
INSERT 0 1
5.2查询数据
postgres=# select * from test;
5.3修改数据
postgres=# update test set age=20 where id=1;
UPDATE 1
postgres=# select * from test;
5.4删除数据
postgres=# delete from test where id=l;
DELETE 1
postgres=# select * from test;
6.备份与恢复
PostgreSQL数据库应当被定期地备份。虽然过程相当简单,但清晰地理解其
底层技术和假设是非常重要的。
有三种不同的基本方法来备份PostgreSQL数据:
> SQL转储
> 文件系统级备份
> 连续归档
每一种都有其优缺点,我们主要以SQL转储为主。
6.1SQL转储
SQL转储方法的思想是创建一个由SQL命令组成的文件,当把这个文件回馈给服务器时,服务器将利用其中的SQL命令重建与转储时状态一样的数据库。
PostgreSQL为此提供了工具pg_dump。这个工具的基本用法是:
pg_dump dbname > dumpfile
正如你所见,pg_dump把结果输出到标准输出。我们后面将看到这样做有什么用处。尽管上述命令会创建一个文本文件,pg_dump可以用其他格式创建文件以支持并行和细粒度的对象恢复控制。
6.2从转储中恢复
pg_dump生成文本文件可以有psql程序读取。从转储中恢复的常用命令是：
psql dbname < dumpfile
其中dumpfile就是pg_dump命令的输出文件。这条命令不会创建数据库dbname,你必须在执行psql前自己从template0创建(例如,用合令createdb-T template0 dbname)。psql支持类似pg_dump的选项用以指定要连接的数据库服务器和要使用的用户名。参阅psql的手册获取更多信息。非文本文件转储可以使用pg_restore工具来恢复。
在开始恢复之前,转储库中对象的拥有者以及在其上被授予了权限的用户必须已经存在。如果它们不存在,那么恢复过程将无法将对象创建成具有原来的所属关系以及权限(有时候这就是你所需要的,但通常不是)。
默认情况下,psql脚本在遇到一个SQL错误后会继续执行。你也许希望在遇到一个SQL错误后让psql退出,那么可以设置ON_ERROR_STOP变量来运行psql这将使psql在遇到SQL错误后退出并返回状态3:
psql --set ON_ERROR_STOP=on dbname < infile不管怎样，你将只能得到一个部分恢复的数据库。作为另一种选择，你可以指定让整个恢复作为一个单独的事务运作，这样恢复要么完全要么完全回滚。
pg_dump和psql读写管道的能力使得直接从一个服务器转储一个数据库到另一个服务器成为可能,例如:
pg_dump -h hostl dbname | psql -h host2 dbname注意:
pg_dump产生的转储是相对于template0。这意味着在 templatel中加入的任何语言、过程等都会被pg_dump转储。结果是,如果在恢复B时使用的是一个自定义的templatel,你必须从 template0创建一个空的数据库,正如上面的例子所示。
一旦完成恢复,在每个数据库上运行ANALYZE是明智的举动,这样优化器就有用的统计数据了。
6.3使用pg_dumpall
pg_dump每次只转储一个数据库,而且它不会转储关于角色或表空间(因为它们是集簇范围的)的信息。为了支持方便地转储一个数据居库集簇的全部内容提供了pgdumpall程序。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/pgsql/data/postgresql/conf
通过源码编译安装的pgsql配置文件在
/usr/local/pgsql/data/postgresql.conf
下面操作以dnf安装为例：
更改第60行,取消注释并把localhost改成*
[root@bogon~]# grep 'listen_addresses' /var/lib/pgsql/data/postgresql.conf
listen addresses=*.* # what IP address (es) to listen on;重启服务
[root@localhost~]# systemctl restart postgresql
[root@localhost~]# ss -tnl
7.2配置访问权限
默认是只能本地访问PostgreSQL的,我们需要在pg_hba.conf里面配置
找到IPv4local connections这一行,在这一行下面添加
host all all 0.0.0.0/0 trust[root@localhost ^]#vim /usr/local/pgsql/data/pg_hba. conf
# IPv4 local connections:
host all all 0.0.0.0/0 trust> host:这指定了连接类型。host表示该规则适用于通过TCP/IP进行的远程连接。如果是本地连接,通常会使用 local。
> all:这定义了哪些数据库可以接受这个规则。all表示这个规则适用于所有数据库。你也可以指定特定的数据库名,例如mydatabase。
> all:这定义了哪些用户可以接受这个规则。all表示这个;规则适用于所有用户。你也可以指定特定的用户名,例如myuser。
> 0.0.0.0/0:这定义了哪些客户端 IP 地址或IP地址范围可以接受这个规则。> trust:这定义了认证方法。trust表示不需要密码或其他任何形式的认证,客户端可以直接连接。
如果不是设置的trust，而是选择了md5 或password之类的，需要有密码才行，配置PostgreSQL密码流程如下：
postgres=# ALTER USER postgres WITH PASSWORD '123456'
ALTER ROLE
7.3重启服务
[root@localhost~]# systemctl start postgresql
7.4验证远程连接
使用其它主机远程连接本机数据库,设置的是trust,无密码,可直接登录
[postgres@localhost ~]$ psql -h 192.168.10.102
psql (16.3,server 15.12)
Type "help" for help.
postgres=#如果设置的是md5或password之类的需要有密码才行
[postgres@localhost ~]$ psql -h 192.168.10.102
Password for user postgres:
psql (16.3,server 15.12)
Type "help" for help.
postgres=#
8.重置密码
8.1备份配置文件
对pg_hba.conf文件,进行备份
[root@localhost~]# cp /var/lib/pgsql/data/postgresql.conf/var/lib/pgsql/data/postgresql.confbak
8.2修改配置文
修改配置文件以信任本地连接不需要密码。将配置文件中的scram-sha-256或
者md5修改为trust
[root@localhost~]# vim /var/lib/pgsql/data/postgresql.conf
8.3重启服务
[root@localhost ^]# systemctl restart postgresql
8.4修改密码
登录数据库修改密码,密码自定义
postgres=# ALTER USER postgres WITH PASSWORD 'new_password'
ALTER ROLE
8.5恢复pg_hba.conf配置文件
将postgresql.confbak文件的内容覆盖pg_hba.conf,重启PostgreSQL数据
库服务器,重新登陆时,如果提示输入密码,则输入刚才修改的密码即可