异地也能查数据：从零搭建 PostgreSQL 私有实验环境

异地也能查数据从零搭建 PostgreSQL 私有实验环境把数据库装到服务器上之后接下来经常遇到一个很实际的问题人在另一台电脑前怎样运行自己的查询脚本导出文件再传回来数据容易落后只在服务器终端里操作又不方便继续处理结果。这次我用星空组网连接 Mac 和 Ubuntu把六笔虚构订单放进 PostgreSQL再从 Mac 用 Python 读取汇总结果。本文从官网登录入口开始走完设备连接、创建项目、部署数据库、建表授权和跨设备查询。读者需要有一台能够管理的 Ubuntu、一台安装 Python 的电脑以及基本的终端操作能力。所有订单都是演示数据不涉及真实客户以下是本次实际环境的记录不是数据库生产部署模板。一、先分清连接、存储和查询星空组网负责让两端通过虚拟地址互访PostgreSQL 负责存储数据、验证数据库账号和执行 SQLPython 则负责发起查询。三者各有职责安装组网客户端之后数据库仍然需要单独部署、设置权限并启动。选择订单汇总作为例子是因为它能同时检验网络和数据逻辑连接建立只是第一步还要确认访问的是预期数据库、使用的是预期账号并且筛选条件确实排除了未支付订单。读者最终得到的不是一张“服务已启动”的截图而是一组能自行核对的数字。角色本次设备与地址执行的工作数据库端Ubuntu192.168.188.5创建项目、运行容器、管理数据库查询端Mac192.168.188.1运行 Python、读取汇总结果数据库连接labdb15432/TCP仅允许指定查询端及只读账号表中的地址来自本次设备的组网状态。跟做时要换成你自己的虚拟 IP并同时调整数据库配置、防火墙和 Python 脚本不能只改其中一处。本文没有使用 Ubuntu 的公网地址作为数据库连接地址也没有在云平台新增公网数据库入站端口。二、从官网登录让两端先连起来打开星空组网官网从“后台管理”进入登录页面。没有账号的读者先按页面流程注册已有账号直接登录。截图保留空白表单实际账号、密码和验证码由本人填写不应放进教程截图。图1官网提供后台入口、下载入口和使用文档。图2本次采集的登录入口。实际操作复用已有登录状态没有为了截图重新注册账号。在后台设备管理中为两端准备各自的成员Mac 可参考官方 macOS 教程Ubuntu 可参考官方 Linux 教程安装客户端再使用对应成员信息连接。两台设备不要混用同一份成员身份。这里复用了已经配置好的 Mac 和 Ubuntu所以后面的操作重点是核对地址及连接状态没有重复添加成员。图3Mac 当前已连接虚拟地址为 192.168.188.1/24。图4Ubuntu-Board 显示在线虚拟地址为 192.168.188.5本次为转发模式。这里没有把“在线”直接当成数据库可用的证据。它只说明设备已连接到组网网络后面还要分别检查服务监听、端口访问和账号权限。截图显示转发模式因此本文也不把测试结果描述成 P2P 直连更不据此推断吞吐量或延迟。三、核验 Ubuntu创建独立项目以下服务器命令在 Ubuntu 的管理终端执行本次使用 root 会话使用普通账号的读者可按已有管理权限进入sudo -i。开始前检查系统、资源、组网地址和目标端口lsb_release-dsip-4-briefaddr show StarVPNfree-mdf-h/docker--versiondockercompose version ss-ltn( sport :15432 )本次环境是 Ubuntu 22.04 LTS、x86_64Docker 29.1.3、Compose 2.40.3。预检时可用内存约 633 MiB、磁盘剩余约 17 GiB15432 端口没有监听。如果你的机器已经有服务占用该端口应换一个空闲端口并同步修改后面的配置。图5先确认目标机器、组网地址和可用资源再部署数据库。创建新目录并进入。mkdir没有使用覆盖逻辑若目录已存在先确认里面是什么不要直接替换旧项目。mkdir/opt/postgres-lab-20260919cd/opt/postgres-lab-20260919图6项目位于独立目录官方镜像中的 PostgreSQL 实际版本为 17.11。四、配置数据库只监听组网地址在项目目录保存下面的compose.yaml。本例采用 Linux 的 host 网络模式由 PostgreSQL 自己绑定组网 IP 和 15432 端口因此没有额外配置ports。内存上限和连接数按这次少量查询的实验设置不能直接套用于高并发业务。name:postgres-labservices:db:image:postgres:17.11-alpinenetwork_mode:hostrestart:nomem_limit:256mshm_size:64menvironment:POSTGRES_DB:labdbPOSTGRES_PASSWORD_FILE:/run/secrets/db_admin_passwordPOSTGRES_INITDB_ARGS:--auth-localpeer--auth-hostscram-sha-256secrets:-db_admin_passwordvolumes:-pgdata:/var/lib/postgresql/data-./pg_hba.conf:/etc/postgresql/pg_hba.conf:rocommand:-postgres--c-listen_addresses192.168.188.5--c-port15432--c-hba_file/etc/postgresql/pg_hba.conf--c-shared_buffers32MB--c-max_connections20--c-work_mem2MBhealthcheck:test:[CMD,gosu,postgres,pg_isready,-p,15432,-U,postgres,-d,labdb]interval:10stimeout:3sretries:6secrets:db_admin_password:file:./secrets/admin_password.txtvolumes:pgdata:同一目录保存pg_hba.conf。第一行允许容器中的同名系统用户通过本地 socket 管理数据库网络连接只放行指定 Mac、labdb数据库和lab_reader角色其余明确拒绝。认证规则按顺序匹配维护时不要随意在前面增加宽泛的允许项。PostgreSQL 官方认证文档解释了各列的含义。# Unix socket: only the matching local OS user may connect. local all all peer # Only this demo reader may connect from this Mac virtual address. host labdb lab_reader 192.168.188.1/32 scram-sha-256 host all all 0.0.0.0/0 reject host all all ::/0 reject管理员密码通过本地文件交给容器避免出现在 Compose 正文、终端历史或截图里。将下面的脚本保存为set_admin_password.py然后运行按提示输入两遍自行设置的密码。importgetpassimportosfrompathlibimportPath pathPath(secrets/admin_password.txt)path.parent.mkdir(mode0o700,exist_okTrue)passwordgetpass.getpass(Admin password (12 characters; hidden): )confirmgetpass.getpass(Repeat admin password: )iflen(password)12orpassword!confirmor\ninpassword:raiseSystemExit(Password mismatch or fewer than 12 characters; nothing saved.)fdos.open(path,os.O_WRONLY|os.O_CREAT|os.O_EXCL,0o600)withos.fdopen(fd,w)asoutput:output.write(password)path.chmod(0o444)# Parent directory stays 0700; container needs read access.print(Admin password saved privately. No credentials printed.)python3 set_admin_password.pydockercompose config--quietdockercompose pulldockercompose up-d--wait密码目录权限为 0700文件设为容器可读只在本机受限目录保存。这里的 Compose secret 是文件挂载不是额外的加密保险箱不要把secrets目录打包上传。初始化密码只在空数据目录第一次创建数据库时生效后续修改文件不会自动修改数据库账号密码。官方镜像说明也特别说明了初始化变量的生效范围。图7本次容器启动成功并显示 Healthy。远程认证是否成功仍需后面的实际查询验证。本机 UFW 处于开启状态因此增加了一条限定网卡、来源、目标及端口的规则。下面只适用于本文地址执行前应确认自己的接口名称和查询端范围。host 网络在这里使用的是 Linux 宿主机网络不要把这份服务器配置原样搬到桌面容器环境后仍假定网络行为完全相同。ufw allowinon StarVPN from192.168.188.1\to192.168.188.5 port15432proto tcp\commentpostgres-lab-demoss-ltn( sport :15432 )五、建表、写入数据再给查询端只读权限把下面内容保存成init.sql。金额使用numeric(10,2)订单状态限制为已支付或待支付六笔记录特意保留一笔待支付订单方便验证后续筛选确实起作用。\setON_ERROR_STOPonBEGIN;REVOKEALLONDATABASElabdbFROMPUBLIC;REVOKECREATEONSCHEMApublicFROMPUBLIC;CREATETABLEpublic.orders(idintegerPRIMARYKEY,regiontextNOTNULL,amountnumeric(10,2)NOTNULLCHECK(amount0),statustextNOTNULLCHECK(statusIN(paid,pending)));INSERTINTOpublic.ordersVALUES(1,华东,129.00,paid),(2,华东,299.00,paid),(3,华南,89.00,paid),(4,华南,199.00,pending),(5,华北,159.00,paid),(6,华北,59.00,paid);CREATEROLE lab_reader LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;GRANTCONNECTONDATABASElabdbTOlab_reader;GRANTUSAGEONSCHEMApublicTOlab_reader;GRANTSELECTONpublic.ordersTOlab_reader;COMMIT;CONNECT解决能否进入数据库USAGE解决能否使用 schemaSELECT则限定对这张表的读取。三者不是互相替代的权限。相关授权语义可对照官方 GRANT 文档。本例没有给lab_reader写入权限也没有把它设置为管理员。权限控制来自数据库授权而不是仅靠 Python 程序“约定只查不改”。dockercomposeexec-T--userpostgres db\psql-p15432-Upostgres-dlabdb-vON_ERROR_STOP1init.sqldockercomposeexec--userpostgres db\psql-p15432-Upostgres-dlabdb-c\password lab_reader第二条命令会要求输入两遍只读账号密码。它与星空成员密码、数据库管理员密码属于不同账号不要混淆。--user postgres指的是容器里的系统用户配合前面设置的 peer 认证-U postgres才是数据库角色。脚本使用事务包住建表与授权中途失败会回滚这一批操作已经成功初始化后不要反复执行建表脚本。这里授权的是已经存在的 orders 表。以后新建其他表并不会自动继承这条 SELECT 授权如果查询需求扩大需要由管理员明确增加授权再做对应验证。也不要为了省掉一次授权直接给查询账号所有表的写入权限。图8六笔订单已写入权限列表中 lab_reader 只有读取权限。六、回到 Mac用 Python 读取真实结果下面的操作切换到 Mac 本地终端。先建一个独立工作目录和虚拟环境安装本次实测的 Psycopg 版本。不要在 Ubuntu 远程终端执行后又把结果当成跨设备访问。mkdirpostgres-query-democdpostgres-query-demo python3-mvenv .venvsource.venv/bin/activate python-mpipinstallpsycopg[binary]3.3.6保存下面的query_orders.py。脚本用隐藏输入获取只读密码设置连接与语句超时并输出实际账号、客户端地址和服务器地址。SQL 的参数单独传给驱动不能改成拼接用户输入的字符串这是Psycopg 参数化查询文档所说明的用法。importgetpassimportsysimportpsycopg HOST192.168.188.5PORT15432SQL SELECT region, COUNT(*), SUM(amount) FROM public.orders WHERE status %s GROUP BY region ORDER BY SUM(amount) DESC defrun_query(password:str)-int:try:withpsycopg.connect(hostHOST,portPORT,dbnamelabdb,userlab_reader,passwordpassword,connect_timeout5,application_nameorders-demo,options-c statement_timeout5000,)asconn:withconn.cursor()ascur:cur.execute(SELECT current_user, inet_client_addr(), inet_server_addr())print(user / client / server:,*cur.fetchone())cur.execute(SQL,(paid,))print(region | orders | total)forregion,count,totalincur.fetchall():print(f{region}|{count}|{total:.2f})exceptpsycopg.Errorasexc:print(fDatabase request failed:{type(exc).__name__},filesys.stderr)print(Check route, service, firewall, credentials and pg_hba.conf.,filesys.stderr)return1return0if__name____main__:raiseSystemExit(run_query(getpass.getpass(lab_reader password: )))python query_orders.py输入lab_reader的密码后已支付订单应按金额降序显示华东两笔共 428.00华北两笔共 218.00华南一笔共 89.00。合计五笔、735.00那笔 199.00 的待支付订单不会被计算进去。金额从 PostgreSQL 的精确数值类型传到 Python 后仍保留小数精度打印时统一展示两位小数便于逐行核对。这个差异比单纯看到“连接成功”更能帮助判断 SQL 是否写对。图9实际调用查询代码返回的结果连接两端分别为 192.168.188.1 和 192.168.188.5。我还用同一连接参数进行了批量验收核对六笔原始记录和金额、检查参数化筛选、尝试写入并回滚同时测试错误密码、访问其他数据库及远程管理员连接。批量验证代码用于验收正文保留读者需要的查询示例。图10九项检查的真实结果写入检查即使意外被允许也会主动回滚避免污染演示数据。七、按请求顺序排错保留后续收尾方法连接超时时先检查两端客户端、虚拟地址和服务器监听再查 UFW 是否允许当前查询端。组网地址变化后旧的来源限制不会自动跟着更新。若服务只监听本地回环地址即使两台设备都在线远端也无法直接访问。提示密码认证失败时先核对数据库账号及密码看到pg_hba.conf rejects connection检查来源地址、角色和数据库名是否符合规则不要立即把规则放宽到所有地址。能连接但查询失败再检查表名、schema 和SELECT权限permission denied for table orders出现在本例写入测试中是预期结果。图11本次数据库只监听组网地址的 15432 端口防火墙规则限定 Mac 来源。如果只是暂停实验在 Ubuntu 项目目录执行dockercompose stop需要继续时先确认组网地址已就绪再执行docker compose up -d --wait。本例没有配置自动重启避免服务器开机时组网地址尚未出现就反复启动。数据库数据放在独立命名卷中停止容器不会清除订单但持久化不等于备份不应把唯一数据副本放在实验卷里。不再需要远程查询时可以撤销本次专用规则ufw delete allowinon StarVPN from192.168.188.1\to192.168.188.5 port15432proto tcp\commentpostgres-lab-demo本文没有配置 PostgreSQL 自身的 TLS连接依赖组网通道不要把同一配置改成公网监听。到这里我们完成的是一个可复现的小型私有数据库实验两台设备通过组网地址连接查询端拥有明确的只读权限数据结果和拒绝访问都经过了实际检查。后续若接入真实业务再根据需求补充 TLS、备份恢复、监控和资源规划。