1 应用商城 安装数据库
基础设置：
映射路径：
版本：推荐最新版本
用户名：(大坑，配置文件中很多 postgres 配置，所以建议用户名使用postgres)
密码：（注意密码不可以有特殊字符，否则会有很多坑！！！）
开放端口外部访问：
连接数、最大内存、最大核数
2 设置远程连接配置
1.已安装 -> 数据库 -> 进入安装目录 -> /data/；
2.修改远程连接配置：pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
# 只有系统用户允许直接访问 peer
local all all peer
# 远程连接 host 加密方式 scram-sha-256 连接地址暂时允许所有 0.0.0.0/0
host all all 0.0.0.0/0 scram-sha-256
3. 配置允许访问的IP postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
# *号代表所有
listen_addresses = '*'
4.配置日志 postgresql.conf
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
# This is used when logging to stderr:
# 开启日期
logging_collector = on # Enable capturing of stderr, jsonlog,
# and csvlog into log files. Required
# to be on for csvlogs and jsonlogs.
# (change requires restart)
# These are only used if logging_collector is on:
# 日志路径
log_directory = 'log' # directory where log files are written,
# 日志命名 # can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# 天为单位 # begin with 0 to use octal notation
log_rotation_age = 1d # Automatic rotation of logfiles will
# 日志文件的最大存储 # happen after that time. 0 disables.
log_rotation_size = 10MB # Automatic rotation of logfiles will
# happen after that much log output.
# 开启覆盖存储 # 0 disables.
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
3 远程访问
1 查看所有 容器
docker ps
2. 远程连接容器
docker exec -it 1Panel-postgresql-MqQf sh
3.切换用户
su - postgres
4.进入pg数据库操作命令
2f561a28c92c:~$ \psql
5.查看命令信息
postgres=# \help create user
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/17/sql-createuser.html
小提示
\help 查看数据库级别的命令
\? 查看服务级别的命令
4 构建用户
1 构建超级管理员
postgres=# create user okyun with SUPERUSER PASSWORD '1xxxxxxxxxxxxxx';
注意： 结尾要添加";"结束 .成功结果如下：
CREATE ROLE
2 只有用户没有用户据对应的数据库不允许远程访问
创建数据库：
postgres=# CREATE DATABASE okyun;
注意：命令必须大写 结尾要添加";"结束.成功结果如下：
CREATE DATABASE
3 切换数据库
postgres=# \c okyun
You are now connected to database "okyun" as user "postgres".
okyun=#
3 查看所有用户信息
okyun=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
okyun | Superuser
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
5 数据存储结构
6 创建schema
okyun=# create schema okyun;
CREATE SCHEMA
7 新建普通用户并设置权限
1 设置普通用户
okyun=# create user biao with password '1xxxxxxx'
2 设置schema下所有表的权限给 biao 用户(不允许删除，只能 查、增、改)
okyun=# grant select,insert,update on all tables in schema okyun to biao
GRANT
8 远程连接跳过本地连接权限的检查
ast login: Tue Oct 28 18:39:08 2025 from 127.0.0.1
hubiao@hubiao-ideacentre-Y700-34ISH:~$ docker exec -it 1Panel-postgresql-MqQf psql -h localhost -U biao -d postgres -W
Password:
psql (17.6)
Type "help" for help.
postgres=>
9 之前创建的schema 拥有者非biao
可以将管理员创建的schema 表结构，分配权限给执行普通用户，防止物理删除！！
10 图形化界面 pgAdmin
支持简体中文
11 使用图形化界面
右键点击 -> 选中 PSQL TOOL -> 命令操作工具；
右键点击 -> 选中 Query TOOL -> SQL操作工具；
