【数据库】postgres数据库命令
1 postgres数据库命令操作
# 登录数据库:
psql -h 服务器 -p 端口号 -U 用户名 -d 数据库 --password
psql -h localhost -p 5432 -U postgres --password
psql -h localhost -p 5432 -U jinjin --password
# 创建数据库:
create database mydata1;
# 查看数据库:
\l
# 切换数据库:
\c mydata1;
# 删除数据库:
drop database mydata1;
# 退出当前数据库:
\q
# 创建表
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
# 插入数据:
INSERT INTO users (username, email, age, created_at)
VALUES ('john_doe', 'john@example.com', 30, CURRENT_TIMESTAMP),
('jane_doe', 'jane@example.com', 28, '2022-04-10 12:00:00');
# 插入部分内容:
INSERT INTO users (username)
VALUES ('jinjin'),
('xiaohua');
# 查看数据库中所有表
\dt
# 查看表结构
\d 表名
# 更新数据
UPDATE users
SET age = 31,
email = 'john_updated@example.com'
WHERE username = 'jinjin';
# 查询数据
SELECT * FROM users;
2 postgres数据库备份
2.1 基本使用
psql --version
pg_dump --version
- 导出单表数据
使用pg_dump时,可以通过-t参数指定要备份的表名。例如,要导出名为my_table的表,可以使用以下命令:
pg_dump -h host_name -U username -p port_number -d database_name -t my_table > my_table.sql
pg_dump -h 127.0.0.1 -U postgres -p 5432 -d postgres -t dify > dify.sql
这里,host_name是数据库服务器的主机名或IP地址,username是数据库用户名,port_number是数据库端口(PostgreSQL默认是5432),database_name是数据库名称。
2. 导出多个表数据
如果需要导出多个表,可以多次使用-t参数。例如,导出my_table和another_table:
pg_dump -h host_name -U username -p port_number -d database_name -t my_table -t another_table > tables.sql
- 只导出表结构
使用-s参数可以只导出表结构(schema),而不包括数据。例如:
pg_dump -h host_name -U username -p port_number -d database_name -s -t my_table > my_table_structure.sql
- 只导出数据
虽然pg_dump没有直接的参数只导出数据而不包括表结构,但可以通过组合使用参数和后期处理来实现。不过,一个常用的方法是使用pg_dump导出整个表,然后删除其中的表创建语句。
2.2 高级选项
- 指定编码:使用-E参数可以指定导出数据的编码格式,如UTF8。
- 使用INSERT语句:通过–inserts参数,pg_dump会以INSERT语句的形式导出数据,这有助于将数据导入到其他类型的数据库系统(尽管性能可能不如默认的COPY方法)。
- 压缩输出:可以通过重定向输出到gzip等工具来压缩导出的SQL文件,例如pg_dump … | gzip > backup.sql.gz。
2.3 注意事项
- 备份期间避免DDL操作:在备份过程中,尽量避免执行DDL(数据定义语言)操作,如CREATE TABLE、ALTER TABLE等,因为这些操作可能会锁表,导致备份失败。
- 备份时间选择:尽量选择业务低峰期进行备份,以避免对业务造成影响。
- 备份空间管理:合理设置备份周期和备份空间,避免不必要的费用和资源浪费。