rust web 使用 POSTGRESQL
POSTGRESQL
POSTGRESQL 服务与配置
-
确认PostgreSQL服务是否正在运行
$ sudo systemctl status postgresql -
如果服务未运行，启动它
$ sudo systemctl start postgresql -
检查 socket 文件是否存在
$ ls /var/run/postgresql/.s.PGSQL.5432 -
默认端口 5432
-
日志
/var/log/postgresql/ -
数据库服务器的认证配置文件
/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
-
连接
$ 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
-
-
配置文件
$ 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 -
创建数据库
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 -
创建数据库
rustwebdevpostgres=# create database rustwebdev; CREATE DATABASE postgres=#
-
-
查看创建的数据库
rustwebdevpostgres=# \l
创建 TABLE
- 默认在
postgresdatabase 中创建,可切换 database
-
创建表
questionspostgres=# 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 -
创建表
answerspostgres=# 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=# \dtList of relationsSchema | Name | Type | Owner
--------+-----------+-------+----------public | answers | table | postgrespublic | 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)}}