Jacky's blog
Jacky
2024-06-18
目录

PostgreSQL

# env

以安装 16 版本为例子

  • brew search postgresql
  • brew install postgresql@16
  • brew services start postgresql@16
  • export PATH="/usr/local/opt/postgresql@16/bin:$PATH"
  • psql --version: 验证

# 入门指导

# 检查 PostgreSQL 服务状态

# 或者查看 Homebrew 安装信息
brew info postgresql@16
# 或
brew info postgresql

# 检查 PostgreSQL 是否在运行
brew services list | grep postgresql

# 或者检查进程
ps aux | grep postgres

# 检查 PostgreSQL 版本和安装路径
psql --version
which psql
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 数据库初始化

# 1. 停止 PostgreSQL 服务（如果正在运行）
brew services stop postgresql@16
# 或
brew services stop postgresql

# 2. 初始化数据库集群
initdb /usr/local/var/postgresql@16
# 或根据你的安装路径
initdb $(brew --prefix)/var/postgresql@16

# 2.1 也可以选择重新初始化
initdb -D /usr/local/var/postgresql@16 --locale=C --encoding=UTF8
# 或
initdb -D $(brew --prefix)/var/postgresql@16 --locale=C --encoding=UTF8

# 3. 启动服务
brew services start postgresql@16
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 使用 createdb 命令（如果服务已运行但无用户）

# 1. 检查是否有任何可用的数据库
psql -l

# 2. 如果服务运行但没有用户，尝试使用系统用户连接
psql -d postgres

# 3. 如果上面失败，尝试直接连接（不指定用户）
psql
1
2
3
4
5
6
7
8

# 创建用户&数据库

# 1. 尝试使用系统用户连接（Homebrew 安装的 PostgreSQL 通常允许）
psql -d postgres

# 2. 如果成功连接，创建 postgres 用户
CREATE ROLE postgres WITH LOGIN SUPERUSER;
CREATE DATABASE postgres OWNER postgres;

# 3. 或者创建你的用户
CREATE ROLE yingci WITH LOGIN SUPERUSER;
CREATE DATABASE yingci OWNER yingci;
1
2
3
4
5
6
7
8
9
10

# psql 命令

psql: PostgreSQL interactive terminal

# 获取帮助

  • psql --help

  • \h: 显示 SQL 命令的说明

  • \copyright: 显示发行条款

  • \? 显示 pgsql 命令的说明

  • \g 或者以分号(;)结尾以执行查询

# 常用

  • \du: 查看所有用户
  • \l: 查看所有数据库
  • \c {dbname}: 切换当前数据库
  • \dt: display tables
  • \d {tablename}: 查看指定表
  • SHOW data_directory;: 查看数据目录
  • \connect {user_name};: 切换到您的用户名
  • \conninfo: 查看当前连接的完整信息
  • \q: quit

# pgsql 命令说明

  • psql --help: help, 查看使用 option 等

# 创建与连接

USER_NAME=$(whoami)
psql -U $USER_NAME -d postgres -c "CREATE DATABASE $USER_NAME;"
psql -U $USER_NAME -d $USER_NAME
1
2
3

# 用户管理

# -- 1. 创建超级用户（完整权限）
CREATE ROLE admin WITH LOGIN SUPERUSER PASSWORD 'strong_password';

# -- 2. 创建普通用户（有限权限）
CREATE ROLE app_user WITH LOGIN PASSWORD 'password123';

# -- 3. 创建只能创建数据库的用户
CREATE ROLE db_creator WITH LOGIN CREATEDB PASSWORD 'password';

# -- 4. 创建可以创建其他用户的用户
CREATE ROLE user_manager WITH LOGIN CREATEROLE PASSWORD 'password';

# -- 5. 创建只读用户
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';
# -- 然后需要单独授予 SELECT 权限

# -- 6. 创建你的用户（推荐）
CREATE ROLE yingci WITH LOGIN SUPERUSER PASSWORD 'your_password';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
属性 说明 权限范围
SUPERUSER 超级用户 所有权限，可绕过权限检查
CREATEDB 创建数据库 可创建新数据库
CREATEROLE 创建角色 可创建其他用户/角色
LOGIN 登录权限 可以连接到数据库
REPLICATION 复制权限 可用于流复制
BYPASSRLS 绕过行级安全 绕过行级安全策略

# 系统信息函数

PostgreSQL 提供了多个系统信息函数，用于查询当前会话和数据库的信息。

# 用户和数据库信息

-- 当前用户信息。 最后一定要带;
SELECT current_user;        -- 当前执行 SQL 的用户名
SELECT session_user;        -- 会话用户名（连接时使用的用户名）
SELECT user;                -- current_user 的别名

-- 当前数据库信息
SELECT current_database();  -- 当前连接的数据库名

-- 组合查询
SELECT 
    current_user AS 当前用户,
    current_database() AS 当前数据库;#### 连接信息

-- 服务器连接信息
SELECT inet_server_addr();  -- 服务器 IP 地址
SELECT inet_server_port();  -- 服务器端口号

-- 客户端连接信息
SELECT inet_client_addr();  -- 客户端 IP 地址
SELECT inet_client_port(); -- 客户端端口号

-- 连接 ID
SELECT pg_backend_pid();    -- 当前会话的后端进程 ID#### 版本和配置信息

-- 版本信息
SELECT version();           -- PostgreSQL 版本详细信息

-- 配置参数
SHOW data_directory;        -- 数据目录
SHOW config_file;           -- 配置文件路径
SHOW server_version;        -- 服务器版本号#### 时间和日期信息

-- 当前时间
SELECT now();               -- 当前时间戳（带时区）
SELECT current_timestamp;   -- 当前时间戳（now() 的别名）
SELECT current_date;        -- 当前日期
SELECT current_time;       -- 当前时间

-- 时区信息
SELECT timezone('UTC', now());  -- 转换为 UTC 时间
SHOW timezone;                  -- 显示当前时区#### 常用查询示例

-- 查看完整的连接信息
SELECT 
    current_user AS 用户,
    current_database() AS 数据库,
    inet_client_addr() AS 客户端IP,
    inet_server_addr() AS 服务器IP,
    now() AS 当前时间;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

# python的支持

import pandas as pd
from sqlalchemy import create_engine, types
import os

# 方式1：使用系统用户（推荐，无需密码）
username = os.getenv('USER')
engine = create_engine(f'postgresql://{username}@localhost:5432/postgres')

# 方式2：使用指定用户（需要密码时）
# engine = create_engine('postgresql://用户名:密码@localhost:5432/数据库名')

# 定义表结构
dtype_mapping = {
    'ts_code': types.VARCHAR(20),
    'com_name': types.VARCHAR(200),
    'com_id': types.VARCHAR(50),
    'chairman': types.VARCHAR(100),
    'manager': types.VARCHAR(100),
    'secretary': types.VARCHAR(100),
    'reg_capital': types.NUMERIC(20, 2),
    'setup_date': types.DATE,
    'province': types.VARCHAR(50),
    'city': types.VARCHAR(50),
    'introduction': types.TEXT,
    'website': types.VARCHAR(200),
    'email': types.VARCHAR(100),
    'office': types.VARCHAR(500),
    'business_scope': types.TEXT,
    'employees': types.INTEGER,
    'main_business': types.TEXT,
    'exchange': types.VARCHAR(20)
}

# 存储数据（使用 begin() 自动管理事务， 不需要手动执行 conn.commit() & conn.close() 了）
try:
    with engine.begin() as conn:
        _stock_company.to_sql(
            name='stock_company',
            con=conn,
            if_exists='replace',  # 'replace': 替换表, 'append': 追加数据, 'fail': 表存在则报错
            index=False,
            dtype=dtype_mapping,
            chunksize=1000,
            method='multi'
        )
    print(f"成功存储 {len(_stock_company)} 条数据")
    
    # 验证数据（可选）
    df_check = pd.read_sql('SELECT COUNT(*) as count FROM stock_company', engine)
    print(f"表中现有数据条数：{df_check['count'].iloc[0]}")
    
except Exception as e:
    print(f"存储数据失败：{e}")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

# 远程访问

todo

上次更新: 2026/05/29, 17:56:50
sqlite
redis 使用指南

← redis 使用指南→

最近更新
01
cloudeflare
06-07
02
Android 端口转发（Forward & Reverse） 的实操与避坑全指南
04-29
03
VisiData 终极生存指南(vd)
04-27
更多文章>
Theme by Vdoing | Copyright © 2019-2026 Jacky | MIT License