postgresql日常维护-CSDN博客

函数以字节为单位返回数据库的大小
postgres=#SELECT pg_database_size('mydb');
pg_database_size
7594499
(1行记录)
pg_size_pretty()函数将字节转为更易于阅读值
postgres=# SELECT pg_size_pretty(pg_database_size('mydb'))):
pg_size_pretty
7417 kB
(1行记录)

数据表操作

列出表

常见用法

mydb=#\dt;列出表(显示search_path中模式里的表,默认public)

mydb=\d;列出表，视图和序列

mydb=\d+

mydb=#\dt my_schema.*列出指定模式下的表

mydb=#\dt *.*查看当前数据库的所有表(包括系统表)

mydb=#SELECT * FROM pg_tables WHERE schemaname public

使用SLQL方式列出当前数据库中public模式下的所有表及其详细信息

创建表

postgres=# create table test(id int, name char(10),age int):
CREATE TABLE

复制表

要将已有的table_name表复制为新表 new_table,包括表结构和数据,请使用以下语句
CREATE TABLE new_table AS TABLE table name;

删除表

postgres=# drop table_test2;(表名)

查看表结构

postgres=# \d_test;

模式操作命令

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

创建模式

在当前库postgres中创建名为hr的模式
postgres=# CREATE SCHEMA hr;
CREATE SCHEMA

默认模式

PostgreSQL每个数据库都有一个默认模式public。

如果创建对象(表、视图等)时不指定模式,默认会放在public 模式中。

通过search_path参数可以设置模式的搜索优先级(类似PATH环境变量)
postgres=# SHOW search_path;

删除模式

删除空模式
postgres=# DROP SCHEMA hr;

强制删除模式及其所有对象
postgres=# DROP SCHEMA hr CASCADE;

查看所有模式

元命令列出当前库中所有模式
postgres=# \dn

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

postgres=# SELECT schema_name FROM informationschema. schemata;

在指定模式中创建表

在postgres库中的hr模式下创建一个名为employees的库
postgres=#CREATE TABLE hr.employees (id SERIAL PRIMARY KEY, name TEXT)

切换当前模式

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

切换到单个schema
SET search_path TO new schema;

切换到多个schema(按优先级顺序)
SET search_path TO hr, public;
表示优先搜索hr模式,其次public

查看当前所在schema

SELECT current schema

查看搜索路径

postgres=# SHOW search_path;

postgresql的模式隔离性

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;
设置search_path切换默认模式(不需显式指定模式名)
mydb=#SET search_path TO schemal;
mydb=8ELECT*FROM users;--默认访问 schemal.userrs
mydb=#SET search_path TO schema2;
mydb=#SELECT*FROM users;--默认访问 schema2.ussers

数据操作

添加数据

在postgres库,新建表test
postgres=# create table test(id int, name char(10),ageint)
postgres=#insertintotest values(1, zhangsan',18);

查询数据

postgres=# select * from test;

修改数据

postgres=# update test set age=20 where id=1;
postgres=# select * from test;

删除数据

postgres=# delete from test where id=l;
postgres=# select * from test;

备份与恢复

PostgreSQL数据库应当被定期地备份。虽然过程相当简单,但清晰地理解其
底层技术和假设是非常重要的。
有三种不同的基本方法来备份PostgreSQL数据:
SQL转储
文件系统级备份
连续归档
每一种都有其优缺点,我们主要以SQL转储为主。

sql转储

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

从转储中恢复

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