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​ 的历史密码$ sudopasswd-d postgres
• 重新设置用户 postgres​ 的密码└─$ sudo-u postgres passwd New password: Retype new password: passwd: password updated successfully

默认管理员账户

• 登录 POSTGRESQL​$ sudo-u postgres psql -U postgres -p5432
• 修改管理员账户 postgres​ 的密码postgres=# alter user postgres with password 'password';ALTER ROLEpostgres=#

创建 DATABASE

1. 连接$ psql postgrespsql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "kali" does not exist- 切换用户连接数据库 : 将当前用户 kali​ 切换为 postgres​$ sudo-s-u postgreskali% psql postgresWARNING: database "postgres" has a collation version mismatchDETAIL: 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=# \qkali% exit
2. 配置文件$ ps-ef|grep postgresql postgres 7649081022:05 ? 00:00:00 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -cconfig_file=/etc/postgresql/16/main/postgresql.conf
3. 创建数据库postgres=# create database rustwebdev;ERROR: template database "template1" has a collation version mismatchDETAIL: 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 mismatchDETAIL: 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 ;REINDEXtemplate1=# alter database template1 refresh collation version ;NOTICE: changing version from 2.37 to 2.38ALTER DATABASEtemplate1=# \q- 创建数据库 rustwebdev​postgres=# create database rustwebdev;CREATE DATABASEpostgres=#
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 questionspostgres(# );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 =matchPgPoolOptions::new().max_connections(5).connect("postgres://postgres:password@localhost:5432/rustwebdev").await{Ok(pool)=> pool,Err(e)=>panic!("Couldn't establish DB connection:{}", e),}

查询数据

matchsqlx::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)}}

插入数据

matchsqlx::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)},}

删除数据

matchsqlx::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)}}

更新数据

matchsqlx::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)}}