一、数据库常用命令
1.登录 PostgreSQL 数据库：
psql -U postgres -h localhost -p 5432 mydatabase
2.登陆到指定数据库
psql -U root -d mydatabase;
3.查看表和数据：
3.1 列出所有数据库：
\l
3.2 连接到数据库：
\c mydatabase
3.3 列出当前数据库的所有表：
\dt
3.4 查看某个表的内容：
SELECT * FROM mytable LIMIT 10;
3.5 退出 psql：
\q
3.6 列出所有用户
\du
3.7 创建用户并设置密码
CREATE USER postgres WITH PASSWORD 'your_password';
3.8 修改指定用户的密码
ALTER USER username WITH PASSWORD 'new_password';
4.备份数据库并包含创建数据库命令：
pg_dump -U postgres -h localhost -p 5432 -F c -b -v -C -f /path/to/backup/aibox_cloud.backup mydatabase
参数解释：
pg_dump：用于备份 PostgreSQL 数据库的工具。
-U postgres：指定数据库用户名为 postgres。
-h localhost：指定数据库服务器的主机名为 localhost。
-p 5432：指定数据库服务器的端口号为 5432。
-F c：指定备份文件格式为自定义格式（custom）。这种格式支持压缩和并行恢复。
-b：包含大对象（blobs）在备份中。
-v：启用详细模式，显示备份过程中的详细信息（verbose）。
-C：在备份文件中包含创建数据库的命令（--create）。
-f /path/to/backup/aibox_cloud.backup：指定输出备份文件的路径和文件名为 /path/to/backup/aibox_cloud.backup。
mydatabase：要备份的数据库名。
5.恢复包含创建数据库命令的备份文件：
pg_restore -U postgres -h localhost -p 5432 -C -d postgres -v /path/to/backup/aibox_cloud.backup
参数解释：
pg_restore：用于恢复由 pg_dump 创建的备份文件的工具。
-U postgres：指定数据库用户名为 postgres。
-h localhost：指定数据库服务器的主机名为 localhost。
-p 5432：指定数据库服务器的端口号为 5432。
-C：在恢复过程中创建数据库。如果备份文件中包含了创建数据库的命令（--create）。
-d postgres：指定连接的目标数据库。在使用 -C 选项时，这个数据库仅用作连接，并在其中执行创建和恢复新数据库的操作。通常使用默认的 postgres 数据库。
-v：启用详细模式，显示恢复过程中的详细信息（verbose）。
/path/to/backup/aibox_cloud.backup：要恢复的备份文件的路径和文件名。
二、docker容器里禁止直接登陆pg数据库，修改为登陆时输入密码
1.说明：在容器中无需密码即可登录 PostgreSQL 数据库的原因通常与 PostgreSQL 的身份验证方法配置有关。在 PostgreSQL 的配置文件中，可能设置了信任（trust）模式，允许在特定条件下无需密码即可访问数据库。
2.PostgreSQL 身份验证方法
PostgreSQL 支持多种身份验证方法，包括：
trust：允许无密码登录。
password：要求密码登录。
md5：要求 MD5 加密的密码。
peer：仅允许本地 Unix 用户登录。
ident：根据客户端的操作系统用户名进行认证。
这些配置通常在 PostgreSQL 配置文件 pg_hba.conf 中设置。该文件通常位于 PostgreSQL 数据目录下。
3. 修改 pg_hba.conf 配置文件
找到并编辑 pg_hba.conf 文件，将所有 trust方法修改为 password。该文件通常位于 PostgreSQL 数据目录下，可以通过如下方式找到：
sudo find / -name pg_hba.conf
以下是一个示例配置：
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all password
# IPv4 local connections:
host all all 127.0.0.1/32 password
# IPv6 local connections:
host all all ::1/128 password
# Allow replication connections from localhost, by a user with the replication privilege.
local replication all password
host replication all 127.0.0.1/32 password
host replication all ::1/128 password
4. 重启 PostgreSQL 服务
修改 pg_hba.conf 文件后，需要重启 PostgreSQL 服务以使更改生效。
对于系统服务
systemctl restart postgresql
对于容器中
如果你在 Docker 容器中运行 PostgreSQL，可以重启容器：
docker restart my_postgres_container
5. 设置 PostgreSQL 用户密码
确保你已经为 postgres 用户设置了密码。
切换到 postgres 用户：
sudo -i -u postgres
进入 psql 命令行界面：
psql
设置密码：
ALTER USER postgres PASSWORD 'your_password';
退出 psql：
\q
6. 登录 PostgreSQL 数据库
以下是几种通过命令行提供密码的方法。
方法 1：使用环境变量
使用 PGPASSWORD 环境变量来指定密码。
export PGPASSWORD='your_password'
psql -U postgres -h localhost -p 5432 -d your_database
方法 2：使用 .pgpass 文件
创建一个 .pgpass 文件，在其中保存数据库连接的配置信息和密码。
创建或编辑 .pgpass 文件：
nano ~/.pgpass
添加以下内容（每一行格式为：hostname:port:database:username:password）：
localhost:5432:your_database:postgres:your_password
设置 .pgpass 文件的权限，使其只有用户自己可以读取：
chmod 600 ~/.pgpass
使用 psql 连接到数据库（psql 会自动读取 .pgpass 文件中的信息）：
psql -U postgres -h localhost -p 5432 -d your_database
方法 3：通过命令行参数传递密码
在命令行中使用 PGPASSWORD 环境变量临时传递密码（适合一次性使用）：
PGPASSWORD='your_password' psql -U postgres -h localhost -p 5432 -d your_database
通过这些步骤，你可以配置 PostgreSQL 使用 password 认证方法，并在登录时提供密码。
三、给用户设置访问权限
要设置 PostgreSQL 中的权限以确保用户 myuser 仅能连接到数据库 mydatabase，你需要配置以下几个方面：
创建用户和数据库（如果尚未创建）
配置数据库权限
确保数据库访问控制规则设置正确
1. 创建用户和数据库
如果你尚未创建用户 myuser 和数据库 mydatabase，可以使用以下命令创建：
创建用户
CREATE USER myuser WITH PASSWORD 'myuser_password';
创建数据库
CREATE DATABASE mydatabase;
将用户 myuser 授予数据库 mydatabase 的访问权限
GRANT CONNECT ON DATABASE mydatabase TO myuser;
2. 配置表和其他权限
授予对特定表的访问权限
如果数据库中有表，你可以授予用户对这些表的特定权限：
\c mydatabase -- 连接到 mydatabase 数据库
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
SELECT: 读取数据
INSERT: 插入数据
UPDATE: 更新数据
DELETE: 删除数据
授予对特定模式的访问权限
如果你只想授予 myuser 对特定模式下的表的权限，你可以如下配置：
GRANT USAGE ON SCHEMA public TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
3. 确保访问控制设置正确
编辑 pg_hba.conf 文件
确保 pg_hba.conf 文件中的访问控制规则设置正确，允许 myuser 从指定 IP 地址或网段连接到 mydatabase 数据库。
# 允许 myuser 从特定 IP 地址连接到 mydatabase 数据库
host mydatabase myuser 192.168.1.0/24 md5
4. 重启 PostgreSQL 服务
修改 pg_hba.conf 文件后，需要重启 PostgreSQL 服务：
sudo systemctl restart postgresql
或者，如果你在 Docker 容器中运行 PostgreSQL，重启容器：
docker restart my_postgres_container
总结
创建用户和数据库（如果尚未创建）：
CREATE USER myuser WITH PASSWORD 'myuser_password';
CREATE DATABASE mydatabase;
GRANT CONNECT ON DATABASE mydatabase TO myuser;
配置权限：
\c mydatabase
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
编辑 pg_hba.conf 文件：
host mydatabase myuser 192.168.1.0/24 md5
重启 PostgreSQL 服务。
通过这些步骤，你可以确保用户 myuser 只能够连接到数据库 mydatabase 并具有适当的权限
