rust web 使用 POSTGRESQL

POSTGRESQL

POSTGRESQL 服务与配置

1. 确认PostgreSQL服务是否正在运行

   $ sudo systemctl status postgresql
   

2. 如果服务未运行，启动它

   $ sudo systemctl start postgresql
   

3. 检查 socket 文件是否存在

   $ ls /var/run/postgresql/.s.PGSQL.5432
   

4. 默认端口 5432

5. 日志 /var/log/postgresql/​

6. 数据库服务器的认证配置文件 /etc/postgresql/16/main/pg_hba.conf​

POSTGRESQL 默认用户

• POSTGRESQL 数据库默认创建管理员账户 postgres​
• 安装 POSTGRESQL 会创建一个默认的 LINUX 用户 postgres​

Linux 系统默认用户

• 删除用户 postgres​ 的历史密码

  $ sudo passwd -d postgres
  

• 重新设置用户 postgres​ 的密码

  └─$ sudo -u postgres passwd              
  New password: 
  Retype new password: 
  passwd: password updated successfully
  

默认管理员账户

• 登录 POSTGRESQL​

  $ sudo -u postgres psql -U postgres -p 5432
  

• 修改管理员账户 postgres​ 的密码

  postgres=# alter user postgres with password 'password';
  ALTER ROLE
  postgres=# 
  

创建 DATABASE

1. 连接

   $ psql postgres
   psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "kali" does not exist
   

   • 切换用户连接数据库 : 将当前用户 kali​ 切换为 postgres​

     $ sudo -s -u postgres
     kali% psql postgres
     WARNING:  database "postgres" has a collation version mismatch
     DETAIL:  The database was created using collation version 2.37, but the operating system provides version 2.38.
     HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
     psql (16.3 (Debian 16.3-1+b1))
     Type "help" for help.
     
     postgres=# \q
     kali% exit
     
     

2. 配置文件

   $ ps -ef  | grep postgresql                   
   postgres  764908       1  0 22:05 ?        00:00:00 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
   

3. 创建数据库

   postgres=# create database rustwebdev;
   ERROR:  template database "template1" has a collation version mismatch
   DETAIL:  The template database was created using collation version 2.37, but the operating system provides version 2.38.
   HINT:  Rebuild all objects in the template database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
   

   • 数据库 template1​ 使用的排序规则版本是 2.37​ , 但操作系统提供 2.38​ . 因此版本不匹配 . 方案如下.

     $ sudo -u postgres psql -U postgres -d template1                                                   
     [sudo] password for kali: 
     WARNING:  database "template1" has a collation version mismatch
     DETAIL:  The database was created using collation version 2.37, but the operating system provides version 2.38.
     HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
     psql (16.3 (Debian 16.3-1+b1))
     Type "help" for help.
     
     template1=# reindex database template1 ;
     REINDEX
     template1=# alter database template1 refresh collation version ;
     NOTICE:  changing version from 2.37 to 2.38
     ALTER DATABASE
     template1=# \q
     

   • 创建数据库 rustwebdev​

     postgres=# create database rustwebdev;
     CREATE DATABASE
     postgres=# 
     

4. 查看创建的数据库 rustwebdev​

   postgres=# \l
   

创建 TABLE

• 默认在 postgres database 中创建,可切换 database

• 创建表 questions​

  postgres=# CREATE TABLE IF NOT EXISTS questions(id serial PRIMARY KEY, 
  title VARCHAR (255) NOT NULL,
  content TEXT NOT NULL,
  tags TEXT [],
  created_on TIMESTAMP NOT NULL DEFAULT NOW()
  );
  CREATE TABLE
  

• 创建表 answers​

  postgres=# CREATE TABLE IF NOT EXISTS answers(id serial PRIMARY KEY, 
  postgres(# content TEXT NOT NULL,
  postgres(# created_on TIMESTAMP NOT NULL DEFAULT NOW(),
  postgres(# corresponding_question integer REFERENCES questions
  postgres(# );
  CREATE TABLE
  

查看 TABLE

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

删除 TABLE

postgres-# drop table answers, questions;
ERROR:  syntax error at or near "drop"
LINE 2: drop table answers, questions;
        ^
postgres=# DROP table answers, questions;
DROP TABLE
postgres=# \dt
Did not find any relations.

连接数据库与查看用户

查看用户与密码

$ sudo -u postgres psql -U postgres -p 5432 -d postgres
postgres= SELECT *  FROM pg_user;

• 默认连接数据库 postgres​

查看用户信息与密码​

postgres= SELECT rolname,rolpassword  FROM pg_authid;

连接指定数据库

• 通过参数 -d​ 指定其他数据库名​

$ sudo -u postgres psql -U postgres -p 5432 -d rustwebdev

sqlx = “0.8.0”

• rust 中可用该 crate 与 POSTGRESQL 通信‍#

sqlx = {version = "0.8.0", features = ["runtime-tokio-rustls", "migrate", "postgres"]}

建立与 POSTGRESQL 连接

• 建立连接池 max_connections 指定池中连接数

 let db_pool = match PgPoolOptions::new()
            .max_connections(5)
            .connect("postgres://postgres:password@localhost:5432/rustwebdev")
            .await
        {
            Ok(pool) => pool,
            Err(e) => panic!("Couldn't establish DB connection:{}", e),
            }

查询数据

match sqlx::query("SELECT * from questions LIMIT $1 OFFSET $2")
            .bind(limit)
            .bind(offset)
            .map(|row: PgRow| Question {
                id: QuestionId(row.get("id")),
                title: row.get("title"),
                content: row.get("content"),
                tags: row.get("tags"),
            })
            .fetch_all(&self.connection)
            .await
        {
            Ok(questions) => Ok(questions),
            Err(e) => {
                tracing::event!(tracing::Level::ERROR, "{:?}", e);

                Err(Error::DatabaseQueryError)
            }
        }

插入数据

match sqlx::query("INSERT INTO questions (title, content, tags) VALUES ($1, $2, $3) RETURNING id, title, content, tags")
            .bind(new_question.title)
            .bind(new_question.content)
            .bind(new_question.tags)
            .map(|row:PgRow|Question{
                id:QuestionId(row.get("id")),
                title:row.get("title"),
                content:row.get("content"),
                tags:row.get("tags")
            }).fetch_one(&self.connection).await{
            Ok(question)=>Ok(question),
            Err(e)=>{
                tracing::event!(tracing::Level::ERROR, "{:?}", e);
                Err(Error::DatabaseQueryError)
            },
        }

删除数据

match sqlx::query("DELETE FROM questions WHERE id = $1")
            .bind(question_id)
            .execute(&self.connection)
            .await
        {
            Ok(_) => Ok(true),
            Err(e) => {
                tracing::event!(tracing::Level::ERROR, "{:?}", e);
                Err(Error::DatabaseQueryError)
            }
        }

更新数据

match sqlx::query("UPDATE questions SET title = $1, content = $2, tags = $3 WHERE id = $4 RETURNING id, title, content, tags")
            .bind(question.title)
            .bind(question.content)
            .bind(question.tags)
            .bind(question_id)
            .map(|row:PgRow| Question{
                id:QuestionId(row.get("id")),
                title:row.get("title"),
                content:row.get("content"),
                tags: row.get("tags"),
            }).fetch_one(&self.connection).await{
            Ok(question)=>Ok(question),
            Err(e)=> {
                tracing::event!(tracing::Level::ERROR, "{:?}", e);
                Err(Error::DatabaseQueryError)
            }

        }