PostgreSQL数据库常用命令
一、数据库常用命令
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 并具有适当的权限