本地PostgreSQL访问Google Cloud Storage的认证及CSV导入问题

PostgreSQL的COPY FROM是服务器进程执行读取操作，而非客户端。你的GCS文件URL无法被PostgreSQL服务器进程访问，且系统的postgres用户没有你通过ADC配置的GCS访问权限，这是核心问题。

方案一：流式导入（适合大文件，无需本地存储） #

利用psql的\copy（客户端侧操作）结合gsutil流式读取GCS文件，直接导入PostgreSQL，全程无需下载文件到本地。

import subprocess

# 替换为你的实际参数
db_config = {
    "dbname": "<your_database>",
    "user": "<your_user>",
    "password": "<your_password>",
    "host": "<your_host>",
    "port": "<your_port>"
}
gcs_file_uri = "gs://<my_project>/<my_file.csv>"  # 使用GCS的gs://格式URI
target_table = "<MY_TABLE>"

# 构造psql命令，通过标准输入导入数据
psql_cmd = [
    "psql",
    f"dbname={db_config['dbname']}",
    f"user={db_config['user']}",
    f"password={db_config['password']}",
    f"host={db_config['host']}",
    f"port={db_config['port']}",
    "-c",
    f"\\copy {target_table} FROM STDIN WITH CSV"
]

# 用gsutil cat读取GCS文件，通过管道传给psql
with subprocess.Popen(["gsutil", "cat", gcs_file_uri], stdout=subprocess.PIPE) as gs_process:
    subprocess.run(psql_cmd, stdin=gs_process.stdout, check=True)

方案二：本地临时文件导入（适合小文件） #

先将GCS文件下载到本地，再通过客户端侧的\copy导入（无需修改文件权限），或修改权限后用服务器侧COPY。

步骤1：下载文件到本地 #

gsutil cp gs://<my_project>/<my_file.csv> /tmp/my_file.csv

步骤2：Python脚本导入（客户端侧\copy） #

import psycopg2

conn = psycopg2.connect(
    database="<your_database>",
    user="<your_user>",
    password="<your_password>",
    host="<your_host>",
    port="<your_port>"
)
cursor = conn.cursor()

# 使用copy_expert执行客户端侧\copy命令
cursor.copy_expert(
    "\\copy <MY_TABLE> FROM '/tmp/my_file.csv' WITH CSV",
    open('/tmp/my_file.csv', 'r')
)

conn.commit()
conn.close()

方案三：PostgreSQL GCS扩展（适合长期批量导入） #

安装gcs_fdw扩展，让PostgreSQL直接访问GCS，无需额外客户端操作。

步骤1：安装扩展（Ubuntu环境） #

# 替换为你的PostgreSQL版本，比如14
sudo apt install postgresql-14-gcs-fdw

步骤2：配置PostgreSQL #

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS gcs_fdw;

-- 创建GCS外部服务器
CREATE SERVER gcs_server FOREIGN DATA WRAPPER gcs_fdw OPTIONS (
    project_id '<your-gcp-project>'
);

-- 配置用户映射（用服务账号密钥文件，或依赖ADC）
CREATE USER MAPPING FOR postgres SERVER gcs_server OPTIONS (
    credentials '/path/to/your-service-account-key.json'
);

-- 创建外部表映射GCS CSV文件（需匹配表结构）
CREATE FOREIGN TABLE gcs_csv_table (
    col1 TEXT,
    col2 INT,
    -- 按CSV列定义字段
    col3 DATE
) SERVER gcs_server OPTIONS (
    uri 'gs://<my_project>/<my_file.csv>',
    format 'csv'
);

-- 导入到本地表
INSERT INTO <MY_TABLE> SELECT * FROM gcs_csv_table;

内容的提问来源于stack exchange，提问作者sal