PostgreSQL【使用篇】01：PostgreSQL的一些基础操作【启停、数据库、schema、表、用户】

已于 2025-06-04 10:29:32 修改
阅读量827 收藏 27
点赞数 25
分类专栏： PostgreSQL 文章标签： 数据库 postgresql
于 2025-05-29 18:57:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_40612128/article/details/148313174
版权

一、启动和停止PostgreSQL

在安装完成PostgreSQL之后，有三种启停PostgreSQL的方式，需要进行配置一下，之前在安装PostgreSQL中也介绍过。

1、使用自带脚本方式启动【推荐】

（1）复制启动脚本

复制源码包里的脚本至etc/init.d目录下，并加执行权限

cd /root/postgresql-17.4
cp ./contrib/start-scripts/linux /etc/init.d/postgresql
chmod +x /etc/init.d/postgresql
chown -R postgres:postgres /usr/local/pgsql

在这里插入图片描述

（2）启动服务

su - postgres
service postgresql start
service postgresql status

使用postgres用户需要输入密码，root用户不需要，但建议使用postgres用户进行操作。

postgres

在这里插入图片描述

（3）设置开机启动

chkconfig --add postgresql

2、使用systemd进行管理【推荐】

（1）编辑postgresql.service文件

vim /etc/systemd/system/postgresql.service

添加如下内容：

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network-online.target

[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGDATA=/usr/local/pgsql/data
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA}
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -m fast
ExecReload=/home/postgres/bin/pg_ctl reload -D ${PGDATA}
TimeoutSec=300s

[Install]
WantedBy=multi-user.target

在这里插入图片描述

（2）加载以及启动

systemctl daemon-reload
systemctl start postgresql
systemctl status postgresql

在这里插入图片描述

3、直接使用命令行启动【不推荐】

su - postgres
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile status

在这里插入图片描述

二、登录和退出PostgreSQL

1、使用root用户登录

psql -U postgres

在这里插入图片描述

2、使用postgres用户登录

su - postgres
psql

在这里插入图片描述

三、常用的基础操作

1、修改密码

alter user pgtest3 with encrypted password '123456';

在这里插入图片描述

2、数据库相关

（1）查看有哪些数据库

\list
\l
\l+

在这里插入图片描述

SELECT datname FROM pg_database;

在这里插入图片描述

（2）创建数据库

create database mydb1;
create database mydb2;
create database mydb3;

在这里插入图片描述
再查看有哪些数据库

\l
\l+

在这里插入图片描述

（3）删除数据库

DROP DATABASE mydb2;
DROP DATABASE mydb3;

在这里插入图片描述

再查看有哪些数据库

\l
\l+

在这里插入图片描述

（4）切换数据库

\c mydb1

在这里插入图片描述

（5）查看当前在哪个数据库下

\c
\conninfo
SELECT current_database();

在这里插入图片描述
根据下面红色框位置也能看出来。
在这里插入图片描述

3、schema

Schema 是数据库内部的​​逻辑命名空间​​，类似于文件夹，用于组织和管理数据库对象（如表、视图、函数等）。

（1）创建schema

一般和用户名同名【租户】，实现多租户管理。

\c mydb1;
create schema pgtest1;
create schema pgtest2;
create schema pgtest3;

在这里插入图片描述

（2）查询schema

\dn
\dn+

在这里插入图片描述
或者，查询所有的schema：

SELECT nspname AS schema_name FROM pg_catalog.pg_namespace ORDER BY schema_name;

在这里插入图片描述
或者，只查询用户自定义的schema：

SELECT nspname AS schema_name FROM pg_catalog.pg_namespace WHERE nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast') AND nspname !~ '^pg_' ORDER BY schema_name;

在这里插入图片描述

（3）查询schema中的表、序列、索引

查看schema中的表

\dt pgtest1.*
\d  pgtest1.*

在这里插入图片描述

查看schema中序列

\ds pgtest1.*

在这里插入图片描述
查看schema中的索引

\di pgtest1.*

在这里插入图片描述

（4）查看schema中是否有对象

例如：表、序列、索引，一般在删除schema前会查询一下。

SELECT COUNT(*) FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = 'pgtest1';

在这里插入图片描述

（5）删除schema

删除空的schema

DROP SCHEMA IF EXISTS pgtest2;

在这里插入图片描述
强制删除非空的schema

DROP SCHEMA IF EXISTS pgtest3 CASCADE;

在这里插入图片描述
再次查询有哪些schema

\dn

在这里插入图片描述

4、用户相关

PostgreSQL使用角色的概念管理数据库访问权限。一个角色可以被看成是一个数据库用户或者是一个数据库用户组，这取决于角色被怎样设置。角色可以拥有数据库对象（例如，表和函数）并且能够把那些对象上的权限赋予给其他角色来控制谁能访问哪些对象。此外，还可以把一个角色中的成员资格授予给另一个角色，这样允许成员角色使用被赋予给另一个角色的权限。

角色的概念把“用户”和“组”的概念都包括在内。在PostgreSQL版本 8.1 之前，用户和组是完全不同的两种实体，但是现在只有角色。任意角色都可以扮演用户、组或者两者。

（0）查看当前连接用户

\c
select user;
select current_user;
select current_database();

在这里插入图片描述

（1）创建用户

CREATE USER pgtest1 with password 'admin';

在这里插入图片描述

CREATE USER等同于CREATE ROLE， 但CREATE USER默认包含LOGIN，而CREATE ROLE不包含。
如下两者等价，创建具有登录权限的角色：

CREATE ROLE pgtest2 with password 'admin' LOGIN;
CREATE USER pgtest3 with password 'admin';

在这里插入图片描述

（2）查看用户

查看有哪些用户/角色

\du
\du+ 
select * from pg_roles;

在这里插入图片描述

（3）查询用户拥有的对象

一般在删除用户的时候会先查询一下，如下pgtest1是新创建的，没有任何对象

SELECT * FROM pg_tables WHERE tableowner = 'pgtest1';

在这里插入图片描述

SELECT * FROM pg_tables WHERE tableowner = 'postgres';

而管理员用户postgres用户下就有很多对象
在这里插入图片描述

（4）查询用户拥有的对象类型

SELECT nspname AS schema, relname AS object, relkind AS type FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relowner = (SELECT oid FROM pg_roles WHERE rolname = 'pgtest1');

在这里插入图片描述

SELECT nspname AS schema, relname AS object, relkind AS type FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relowner = (SELECT oid FROM pg_roles WHERE rolname = 'postgres');

在这里插入图片描述

（5）转移用户对象

将用户拥有的所有对象转移给其他用户（如pgtest2），一般在删除用户的时候，可能会用到，将要删除的对象转移给其他用户。

REASSIGN OWNED BY pgtest1 TO pgtest2;

DROP USER pgtest1;

（6）删除用户

DROP USER IF EXISTS pgtest2;

在这里插入图片描述

（7）用户授权

授权用户的schema所有权限，一般schema和用户同名，实现多租户管理。

GRANT ALL PRIVILEGES ON SCHEMA pgtest1 TO  pgtest1;

在这里插入图片描述
授权用户schema下表的权限，注意，如果这个表不是pgtest1创建的，还要进行授权才行，例如授予表查询、更新、删除权限

GRANT SELECT, UPDATE, DELETE ON TABLE pgtest1.t2 TO pgtest1;

在这里插入图片描述

否则是没有权限进行增删改查操作的，只有授完权，才能进行查询，如下：
在这里插入图片描述

（8）回收权限

REVOKE ALL PRIVILEGES ON SCHEMA pgtest1 FROM pgtest1;

在这里插入图片描述

（9）普通用户登录

创建完普通用户后，可以使用普通用户进行登录。

psql -U pgtest1 -d mydb1

在这里插入图片描述

5、表相关

（1）创建表

不指定schem创建表的话，默认使用的是public这个schema

\c mydb1;
create table t1(id int primary key,name varchar(20));

在这里插入图片描述
在这里插入图片描述
指定schema创建表，指定schema为pgtest1

\c mydb1;
\dn
create table pgtest1.t2(id int primary key,name varchar(20));

在这里插入图片描述

\dt pgtest1.*

在这里插入图片描述

（2）查看当前数据库有哪些表

\dt

在这里插入图片描述

（3）查看表属于哪个schema

使用 information_schema.tables

SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 't1';

在这里插入图片描述
使用 pg_catalog.pg_tables

SELECT schemaname AS schema_name, tablename AS table_name FROM pg_catalog.pg_tables WHERE tablename = 't1';

在这里插入图片描述
使用 pg_catalog.pg_tables（模糊匹配）

SELECT schemaname, tablename FROM pg_catalog.pg_tables WHERE tablename LIKE '%t1%';

在这里插入图片描述

（4）查看表属于哪个用户

\dt

在这里插入图片描述

（5）查看表结构

\d t1;
\d+ t1;

在这里插入图片描述

（6）插入数据

INSERT INTO t1 (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eve'),
(6, 'Frank'),
(7, 'Grace'),
(8, 'Henry'),
(9, 'Ivy'),
(10, 'Jack');

在这里插入图片描述

（7）查询数据

select * from t1;

在这里插入图片描述

（8）删除数据

delete from t1 where id > 5;
select * from t1;

在这里插入图片描述

（9）删除表

select * from t4
DROP TABLE t4;
DROP TABLE IF EXISTS t4;

在这里插入图片描述

（9）清空表

select * from t5;
TRUNCATE t5;

在这里插入图片描述
清空表数据并重置自增 ID

TRUNCATE t5 RESTART IDENTITY;

（10）查询表有哪些字段

和查看表结构一样

\d t1;
\d+ t1;

在这里插入图片描述

（11）添加字段

alter table [表名] add column [字段名] [类型];

alter table t1 add column test1_column int;

在这里插入图片描述

（12）修改字段

alter table [表名] rename column [旧字段名] to [新字段名];

alter table t1 rename column test1_column to test1_column_change;

在这里插入图片描述

（13）删除字段

alter table [表名] drop column [字段名];

alter table t1 drop column test1_column_change;

在这里插入图片描述

四、其他

1、使用客户端进行连接

在这里插入图片描述
在这里插入图片描述

2、查看pg版本

pg_ctl --version

在这里插入图片描述

3、查询连接信息

select * from pg_stat_activity

在这里插入图片描述

4、查看连接数

SELECT datname, numbackends FROM pg_stat_database;

在这里插入图片描述

select count(*) from pg_stat_activity where datname='mydb1';

在这里插入图片描述

5、explain

EXPLAIN select * from t1;

在这里插入图片描述

PostgreSQL：语言基础与数据库操作
在技术的广袤天地里，本博客如精准罗盘。剖析前沿科技，深掘代码奥秘，以精炼笔触，带您穿越复杂技术迷宫，速达知识彼岸。
03-20 1795
PostgreSQL 15的新特性如MERGE命令、构造函数等，正在不断扩展其应用边界。向量相似度搜索：结合pgvector扩展实现AI特征检索分布式架构：使用Citus实现水平扩展时序数据优化：TimescaleDB的深度集成机器学习集成：MADlib库的统计分析应用。
【PostgreSQL教程】PostgreSQL 模式（SCHEMA）
No8g攻城狮的博客
07-27 465
PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。 一个模式可以包含视图、索引、数据类型、函数和操作符等。 相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。 使用模式的优势： - 允许多个用户使用一个数据库并且不会互相干扰。 - 将数据库对象组织成逻辑组以便更容易管理。 - 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。 模式类似于操作系统层的目录，但是模式不能嵌套。
postgresql 远程用户_等保2.0涉及的PostgreSQL数据库（上）
weixin_39967670的博客
11-26 893
登录数据库一般用户在pgsql服务器上会创建相应的postgres用户，我们切换到postgres用户来操作数据库，pgsql以postgres为默认用户。执行su - postgres切换后建议查看~/.bash_profile文件中是否将pgsql的bin目录加入环境变量中，方便使用pgsql相关命令有相应环境变量后，即可直接执行：psql -U postgres //连接pgsql serv...
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 21 章 数据库角色
weixin_34291004的博客
10-03 159
第21章数据库角色 目录 21.1. 数据库角色 21.2. 角色属性 21.3. 角色成员关系 21.4. 删除角色 21.5. 默认角色 21.6. 函数和触发器安全性 PostgreSQL使用角色的概念管理数据库访问权限。一个角色可以被看成是一个数据库用户或者是一个数据库用户组，这取决于角色被怎样设置。角色可以拥有数据库对象（例如...
等保2.0涉及的PostgreSQL
qq_23435961的博客
02-22 3502
1.查看postgresql.conf、pg_hba.conf文件确认是否开启SSL管理，如果开启均符合。2.未开启SSL情况下，去查看pg_hba.conf文件METHOD字段的配置信息鉴别数据：METHOD值为时鉴别数据传输过程中具有保密性，当值为password时，为不符合重要业务数据、重要个人信息：未开启SSL，不符合。当然不排除有其它可实现的手段，具体的可以找系统管理人员确认。至少需要确认备份的时间（如几点执行备份）、频率（如每日执行还是每周）、备份内容。
postgresql两个列模糊比较_PostgreSQL使用简易教程
weixin_39955825的博客
11-26 424
1. 一些基础概念postgreSQL ，或者说数据库，有个很重要的概念就是 Schema（模式）和 View（视图）、Materialized View（物化视图）。Schema，模式Schema 类似与分组，它可以将数据库对象组织到一起形成逻辑组，方便管理。我们在 postgreSQL 数据库中创建的任何对象(表、索引、视图和物化视图)都会在一个模式下被创建。如果未指定模式，这些对象将会在默认...
PostgreSQL用户角色
糊了hule的博客
07-22 325
PostgreSQL使用角色的概念管理数据库访问权限。角色可以拥有数据库对象（例如，表和函数）并且能够把那些对象上的权限赋予给其他角色。此外还可以把一个角色授予给另一个角色，这样角色的概念把“用户”和“组”的概念都包括在内。 数据库角色在概念上已经完全与操作系统用户独立开来。数据库角色在一个实例内是全局的（而不是独立数据库内）。命令： CREATE ROLE name; DROP ROLE name; 要决定现有角色的集合，检查pg_roles系统目录，例如： SELECT rolname FROM pg_
PostgreSQL教程(二)：模式Schema详解
09-10
`public`模式是PostgreSQL默认创建的一个特殊模式，它对所有数据库用户都是可见的。当你创建一个新表，如`CREATE TABLE products ( ... )`，实际上等同于`CREATE TABLE public.products ( ... )`，除非你明确指定了...
Debezium系列之：使用Debezium采集PostgreSQL数据库14版本数据到Kafka Topic的详细步骤，支持PostgreSQL数据库分区表的数据采集
zhengzaifeidelushang的博客
08-14 359
Debezium系列之：使用Debezium采集PostgreSQL数据库14版本数据到Kafka Topic的详细步骤，支持PostgreSQL数据库分区表的数据采集
Java使用JDBC连接postgresql数据库示例
08-26
Java 使用 JDBC（Java Database Connectivity）连接 PostgreSQL 数据库，可以实现各种数据库操作，如插入、更新、查询等。在本文中，我们将通过实例形式分析 Java 使用 JDBC 连接 PostgreSQL 数据库的相关操作技巧。...
【postgresql】角色（Role）
一个写了10年bug的程序员日常笔记。
07-15 1201
PostgreSQL 中，角色（Role）是一个重要的概念，用于管理数据库的访问权限和用户身份。角色可以被视为用户或组，具体取决于它们的配置方。
pg基本知识
zhangjikuan的专栏
03-19 5520
目录 base: 默认表空间 global: 共享系统表目录 pg_clog: commit log 目录 pg_log: 系统日志目录，查询系统错误时可看，alert日志，慢日志，全量sql都在此 pg_stat_tmp: 统计信息的存储目录 pg_tblsp: 指向各个用户自建表空间实际目录的链接文件 pg_twophase: 两阶段提交功能时分布式事务的存储目录 pg_xlog: WAL日...
postgresql 角色
lengyue365的专栏
06-07 501
http://www.php100.com/manual/PostgreSQL8/user-manag.html
PostgreSQL用户与角色简述
总想试试，万一成了呢？？
05-21 1039
PostgreSQL通过角色（role）来控制数据库的访问权限。角色可以拥有数据库对象（比如表、函数等），并允许将这些对象的权限授予其他角色，从而实现对象访问的控制
PostgreSQL数据库中的角色(Role)、用户（User）、模式（Schema）
SQLplusDB的小院
05-07 8462
用户（角色）主要用于权限管理，模式用于数据库对象的管理。
【PG基础】数据库命令及数据集簇
guye
08-10 1569
使用PostgreSQL数据库命令行交互工具psql登录数据库后，可以执行SQL命令或者psql提供的元命令。数据库初始化过程中创建整个数据库系统的数据集簇（目录）并创建两个模板数据库和默认用户数据库，该过程由initdb程序完成的，它属于一个独立的程序。
PostgreSQL用户角色及其属性介绍
热门推荐
Socrates的专栏
07-10 1万+
1.CREATE ROLE创建的用户默认不带LOGIN属性，而CREATE USER创建的用户默认带有LOGIN属性，如下：postgres=# CREATE ROLE pg_test_user_1; /*默认不带LOGIN属性*/ CREATE ROLE postgres=
postgresql角色和用户权限
归来仍少年
07-22 2554
角色属性 1.login 2.superuser 3.createdb 4.createrole 5.password 创建的角色有表操作的所有权限，如创建表，删除表，增删查改 权限列表含义 arwdDxt|all privileges r|select w|update a|insert d|delete D|truncte x|reference t|trigger X|execute U|usage C|create c|connect T|temporary ##创建角色 create role r
【PostgreSQL】实战篇——用户管理、角色和权限控制的高级用法及技巧
最新发布
thinking_chou的专栏
10-04 1990
遵循最小权限原则：确保用户仅获得其工作所需的最低权限。使用角色管理权限：通过角色管理权限而不是直接将权限授予用户，简化权限管理。定期审计：定期检查用户和角色的权限，确保没有过期或不必要的权限。启用行级安全性：在需要时使用行级安全性，以提供更细粒度的访问控制。使用强密码：确保用户使用强密码，并定期更改密码。监控和日志：启用数据库审计和日志记录，监控用户活动并及时发现异常行为。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

做一个有趣的人Zz

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值