一、数据库常用命令

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 并具有适当的权限

@博客园@Leonardo-li

psql命令 清除命令行记录

在使用 PostgreSQL 的命令行接口 psql 时，你可能会发现历史记录中填满了不必要的命令。为了清除这些历史记录，可以使用以下方法：

如果你使用的是 Unix-like 系统，可以通过清空 ~/.psql_history 文件来清除历史记录。在终端中运行以下命令：

echo > ~/.psql_history

如果你使用的是 Windows 系统，历史记录通常保存在 !cmds 文件中，你可以通过清空这个文件来清除历史记录：

> type nul > !cmds

请注意，这些命令会立即清除当前用户的历史记录，不会影响其他用户的历史记录文件。如果你需要从 psql 会话内部清除当前会话的历史记录，可以使用以下命令：

\!rm .psql_history

或者在 Windows 上：

\!del !cmds

请在运行这些命令时小心，因为它们会永久删除文件内容。