PostgreSQL 安装与基础使用

在你的 VPS 上用 apt 安装 PostgreSQL,创建角色与数据库,掌握 psql 常用元命令,并按需开启远程访问。

PostgreSQL 是一款成熟、稳定、以标准 SQL 兼容性著称的开源关系型数据库,非常适合部署在你自己的服务器或 VPS 上。本文以 Ubuntu/Debian 为例,带你从零完成安装与基础操作。

安装

在 Ubuntu/Debian 上,官方仓库即自带 PostgreSQL,直接用 apt 安装即可:

sudo apt update
sudo apt install -y postgresql postgresql-contrib

安装完成后服务会自动启动。可以确认一下运行状态:

sudo systemctl status postgresql

安装过程会自动创建一个名为 postgres 的系统用户和同名数据库超级用户。

首次连接

PostgreSQL 默认使用 peer 认证,即系统用户名要与数据库角色名一致。所以第一次进入命令行,要借助 postgres 系统账号:

sudo -u postgres psql

进入后提示符会变成 postgres=#,这就是交互式客户端 psql。输入 \q 可以退出。

常用元命令

psql 里以反斜杠开头的是元命令,不是 SQL,不用加分号:

\l —— 列出所有数据库
\du —— 列出所有角色(用户)
\c dbname —— 切换到某个数据库
\dt —— 列出当前数据库的所有表
\d tablename —— 查看某张表的结构
\? —— 查看元命令帮助

创建角色与数据库

PostgreSQL 里「用户」统称为角色(role)。下面创建一个可登录的角色并设置密码,再建一个归它所有的数据库:

CREATE ROLE appuser WITH LOGIN PASSWORD 'your_strong_password';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

给已存在角色改密码:

ALTER ROLE appuser WITH PASSWORD 'new_password';

之后就能用该角色连接指定数据库:

psql -h 127.0.0.1 -U appuser -d appdb

允许远程访问

默认 PostgreSQL 只监听本机(localhost),外部连不上。要开放需改两个文件(路径中的 16 是版本号,按实际调整)。

第一步,编辑 postgresql.conf,让它监听所有网卡:

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'

第二步,编辑 pghba.conf,添加一条允许远程网段的规则:

sudo nano /etc/postgresql/16/main/pg_hba.conf

host    all    all    0.0.0.0/0    scram-sha-256

改完重启服务生效:

sudo systemctl restart postgresql

生产环境请把 0.0.0.0/0 收窄为可信 IP,并在防火墙放行 5432 端口。

与 MySQL 的简单区别

用户模型:MySQL 的账号是 user@host;PostgreSQL 用角色,登录能力由 LOGIN 属性决定。
默认端口:PostgreSQL 是 5432,MySQL 是 3306。
客户端:PostgreSQL 用 psql,元命令是 \l、\dt;MySQL 用 mysql,对应 SHOW DATABASES;、SHOW TABLES;。
库/表切换:PostgreSQL 用 \c dbname 切库;MySQL 用 USE dbname;。
标准兼容:PostgreSQL 对 SQL 标准和复杂查询、事务的支持更严格、更完整。

小结

在 Ubuntu/Debian 上,apt install postgresql 即可完成安装,首次经 sudo -u postgres psql 进入,用 CREATE ROLE/CREATE DATABASE 建好角色与库,靠 \l \du \dt 快速查看现状。需要远程时,记得同时改 listenaddresses 与 pghba.conf 并重启,同时用防火墙和精确网段把访问面收窄,数据库才安全可用。