Postgresql 笔记

PostgreSQL简介

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)

主播已被shell操作击毙（悲）

安装

yum -y install postgresql-server
ps: 只装postgre是用不了的

初始化数据库

postgresql-setup --initdb
systemctl start postgresql

进入数据库

sudo -u postgres psql

示例

[root@localhost ~]# sudo -u postgres psql
could not change directory to "/root": 权限不够
psql (13.16)
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

基本使用

查看数据库 `\l`

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

进入指定数据库 `\c dbname`

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".

创建数据库 `CREATE DATABASE NAME`

postgres=# create database test;
CREATE DATABASE

创建表

在数据库中创建表时，可以使用 CREATE TABLE 命令。例如，创建一个名为 test 的表：

CREATE TABLE test (id SERIAL PRIMARY KEY,name VARCHAR(10));

示例：

postgres=# CREATE TABLE test1(id SERIAL PRIMARY KEY, sex INT, name VARCHAR(10));
CREATE TABLE

查看表

进入指定数据库后，可以使用 \dt 命令查看该数据库中的所有表：

postgres=# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test  | table | postgres
 public | test1 | table | postgres
(2 rows)

查看表结构 `\d`

\d table_name

插入数据

使用 INSERT INTO 命令向表中插入数据

INSERT INTO test (name) VALUES ('Alice');

查询数据

使用 SELECT 命令查询表中的数据

postgres=# SELECT * FROM test;
 id | name  
----+-------
  1 | Alice
  2 | Alice
(2 rows)

更新数据

使用 UPDATE 命令更新表中的数据。例如，更新 test表中 id 为 1 的记录：

postgres=# UPDATE test SET name = 'Block' WHERE id = 1;
UPDATE 1
postgres=# SELECT * FROM test;
 id | name  
----+-------
  2 | Alice
  1 | Block
(2 rows)

加密数据

当你需要将明文的密码（或者其他）加密为MD5等

update set password = md.sha256.sha512(password)；

删除数据

使用 DELETE 命令删除表中的数据。例如，删除 test表中 id 为 1 的记录：

postgres=# DELETE FROM test WHERE id = 1;
DELETE 1
postgres=# SELECT * FROM test;
 id | name  
----+-------
  2 | Alice
(1 row)

删除表

使用 DROP TABLE 命令删除表。例如，删除 test表：

postgres=# DROP TABLE test;
DROP TABLE
postgres=# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | test1 | table | postgres
(1 row)

创建用户

分为两种方法

user 可登录的服务器角色
role 不可登录的用户

CREATE USER username WITH LOGIN PASSWORD 'password';
CREATE ROLE username WITH LOGIN PASSWORD 'password';

修改用户密码

ALTER USER username WITH PASSWORD 'password'；

删除用户

DROP ROLE name;

若该用户无法删除，要撤销其权限后再删除否则会报错无法删除"myuser"因为有其它对象倚赖它

用户权限管理

授权

grant 权限 on 给予权限的对象 to 被授权的用户名;
# 授权的库前面要加database
# 授权单个表的权限需要进入该表对应的数据库

撤销

revoko 权限 on 给予权限的对象 from 被授权的用户名;
# 撤销单个表的权限需要进入该表对应的数据库，否则会报错

角色属性（权限）

登录权限 (Login) ：
- 允许角色登录到数据库。
超级用户权限 (Superuser) ：
- 赋予角色超级用户权限，使其可以执行任何操作，包括修改系统表。
创建数据库权限 (CreateDB) ：
- 允许角色创建新的数据库。
创建角色权限 (CreateRole) ：
- 允许角色创建新的数据库角色。
创建临时表权限 (Temporary) ：
- 允许角色创建临时表。
连接限制 (Connection Limit) ：
- 限制角色同时连接到数据库的最大数量。
继承权限 (Inherit) ：
- 允许角色继承其他角色的权限。
授权权限 (Grant) ：
- 允许角色授权给其他角色权限。
修改其他角色权限 (Alter Any Role) ：
- 允许角色修改其他角色的属性。
删除其他角色权限 (Drop Any Role) ：
- 允许角色删除其他角色。
超时限制 (Password Expiry) ：
- 设置角色密码的过期时间。
加密方式 (Encrypted) ：
- 指定角色密码是否加密存储。

导入导出备份

导入

\copy 表名 from '导入文件路径' with (delimiter '间隔符',format 文件类型);

# 例子
\copy userinfo from '/var/postgresql/users.txt' WITH(DELIMITER',',FORMAT text);

# 文件格式示例
3,user3,2004-6-3,man,user3
4,user4,2004-6-4,man,user4

导出

配置

vim /var/lib/pgsql/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5(改了这里)
# IPv6 local connections:
host    all             all             ::1/128                 md5(不该前面加密的密码导不出来)

进入你要备份的表对应的库

# 进入数据库
\c userdb;

# 授权
grant all on userinfo to myuser;
grant usage, slect on sequence public.userinfo_id_se to myuser;

备份

退出数据库后操作

pg_dump -U 用户名 -h 主机名（ip） 备份的库 > 目的文件
pg_dump -U myuser -h 127.0.0.1 userinfo > userinfo.sql

还原数据库

psql -U 用户名 -h 主机 -d 需要还原的库 -f 备份文件.sql
psql -U myuser -h 127.0.0.1 -d userdb -f userdb.sql

退出 `psql`

使用 \q 命令退出 psql：

postgres=# \q
[root@localhost ~]#

Shell操作数据库（实操）

题目要求：

备份数据库userdb到/var/postgresql/userdb.sql
备份数据表userinfo记录到/var/postgresql/userinfo.sql，字段之间用','分隔

#!/bin/bash

# 设置环境变量
export PGUSER=myuser          # 设置PostgreSQL用户名为myuser
export PGHOST=127.0.0.1       # 设置PostgreSQL主机为本地主机
export PGPORT=5432            # 设置PostgreSQL端口为5432
export PGDATABASE=userdb      # 设置PostgreSQL数据库名为userdb

# 定义备份目录
BACKUP_DIR="/var/postgresql"

# 创建数据库（如果数据库不存在）
psql -c "CREATE DATABASE $PGDATABASE;"

# 备份数据库userdb到/var/postgresql/userdb.sql  
pg_dump -U $PGUSER -h $PGHOST -d $PGDATABASE -f $BACKUP_DIR/userdb.sql

# 备份数据表userinfo记录到/var/postgresql/userinfo.sql，字段之间用','分隔
psql -c "\COPY (SELECT * FROM userinfo) TO '$BACKUP_DIR/userinfo.csv' WITH CSV DELIMITER ';';"

AlexBlog

Postgresql 笔记

PostgreSQL简介

安装

初始化数据库

进入数据库

基本使用

查看数据库 `\l`

进入指定数据库 `\c dbname`

创建数据库 `CREATE DATABASE NAME`

创建表

查看表

查看表结构 `\d`

插入数据

查询数据

更新数据

加密数据

删除数据

删除表

创建用户

修改用户密码

删除用户

用户权限管理

角色属性（权限）

导入导出备份

导入

导出

备份

还原数据库

退出 `psql`

Shell操作数据库（实操）

AlexBlock

没有评论

Postgresql 笔记

PostgreSQL简介

安装

初始化数据库

进入数据库

基本使用

查看数据库 \l

进入指定数据库 \c dbname

创建数据库 CREATE DATABASE NAME

创建表

查看表

查看表结构 \d

插入数据

查询数据

更新数据

加密数据

删除数据

删除表

创建用户

修改用户密码

删除用户

用户权限管理

角色属性（权限）

导入导出备份

导入

导出

备份

还原数据库

退出 psql

Shell操作数据库（实操）

AlexBlock

没有评论

查看数据库 `\l`

进入指定数据库 `\c dbname`

创建数据库 `CREATE DATABASE NAME`

查看表结构 `\d`

退出 `psql`