本页内容
- 准备工作
- 步骤 1 - 安装 PostgreSQL
- 步骤 2 - 使用 PostgreSQL 角色与认证方式
- 步骤 3 - 创建新角色
- 步骤 4 - 创建数据库
- 步骤 5 - 启用远程访问
- 步骤 6 - 安装 pgAdmin 4
- 步骤 7 - 集成 uWSGI 与 pgAdmin
- 步骤 8 - 创建服务文件
- 步骤 9 - 安装 SSL
- 步骤 10 - 安装并配置 Nginx
- 步骤 11 - 配置防火墙
- 步骤 11 - 访问并配置 pgAdmin
- 总结
- 配置 Nginx
PostgreSQL（常简称 Postgres）是一款开源的对象关系型数据库管理系统，实现了 SQL 查询语言。它拥有可靠的事务处理以及无读锁的并发等高级特性，让我们得以构建容错环境和复杂应用程序。
pgAdmin 是 PostgreSQL 的一款开源管理工具。它支持包括 Windows、macOS 和 Linux 在内的多个平台。
本教程将教您如何在 Ubuntu 22.04 服务器上安装 PostgreSQL 14 与 pgAdmin 4。
准备工作
- 一台运行 Ubuntu 20.04 的服务器。
- 一个非 root 的 sudo 用户。
- 一个形如 pgadmin.example.com 的完全限定域名（FQDN）。
- 确保系统全部为最新。 $ sudo apt update $ sudo apt upgrade
- 您的系统需要安装一些软件包。 $ sudo apt install wget curl nano software-properties-common dirmngr apt-transport-https gnupg2 ca-certificates lsb-release ubuntu-keyring unzip -y 其中部分软件包可能已经在您的系统上安装过了。
一台运行 Ubuntu 20.04 的服务器。
一个非 root 的 sudo 用户。
一个形如 pgadmin.example.com 的完全限定域名（FQDN）。
确保系统所有内容都已更新。
$ sudo apt update
$ sudo apt upgrade
您的系统需要几个软件包。
$ sudo apt install wget curl nano software-properties-common dirmngr apt-transport-https gnupg2 ca-certificates lsb-release ubuntu-keyring unzip -y
其中部分软件包可能已经在您的系统上安装过了。
步骤 1 - 安装 PostgreSQL
Ubuntu 22.04 默认自带 PostgreSQL 14。要安装，请执行以下命令。
$ sudo apt install postgresql postgresql-contrib
postgresql-contrib 软件包包含一些额外的实用工具。
您也可以使用 PostgreSQL 官方的 APT 软件源进行安装。运行以下命令以添加 PostgreSQL 的 GPG 密钥。
$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql-key.gpg >/dev/null
将 APT 软件源添加到您的软件源列表中。
$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/postgresql-key.gpg arch=amd64] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
更新系统软件源。
$ sudo apt update
现在，您可以使用上述命令安装 PostgreSQL。
检查 PostgreSQL 服务的状态。
$ sudo systemctl status postgresql
? postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Mon 2022-05-16 11:20:35 UTC; 5 days ago
Process: 30544 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 30544 (code=exited, status=0/SUCCESS)
CPU: 2ms
May 16 11:20:35 postgresql systemd[1]: Starting PostgreSQL RDBMS...
May 16 11:20:35 postgresql systemd[1]: Finished PostgreSQL RDBMS.
您可以看到该服务默认已启用并正在运行。
步骤 2 - 使用 PostgreSQL 角色与认证方式
Postgres 使用一个称为“角色（role）”的概念来处理认证和数据库访问。一个角色代表一个用户或一组用户。Postgres 并不区分用户和组。
Postgres 支持多种认证方式，例如以下几种：
- Trust（信任） - 只要满足配置文件中定义的条件，即可在无需任何密码的情况下建立连接。
- Password（密码） - 顾名思义，访问时需要提供密码。
- Ident - 该方法仅支持 TCP/IP 连接，适用于远程连接。
- Peer - 与 Ident 相同，但仅用于本地连接。
默认情况下，Postgres 的安装使用 peer（对等）认证方式。在该方式下，它会将一个 Postgres 角色与同名的 Linux 系统账户关联起来。如果某个角色存在，您就可以使用同名用户名的 Linux 账户登录。
PostgreSQL 的安装会创建一个与默认角色关联的 postgres 用户账户。您可以使用以下命令切换到系统中的 postgres 账户。
$ sudo -i -u postgres
使用 psql 命令进入 PostgreSQL 命令行。
postgres@howtoforge:~$ psql
您将登录到 PostgreSQL 命令行。
psql (14.3 (Ubuntu 14.3-1.pgdg22.04+1))
Type "help" for help.
postgres=#
输入 exit 或 \q 来关闭命令行。
您也可以使用一条命令进入 PostgreSQL 命令行。以下命令会在 postgres 账户下运行命令行。退出命令行后，您将回到当前的系统用户。
$ sudo -u postgres psql
步骤 3 - 创建新角色
您可以使用 createuser 命令创建新角色，也可以从 psql 命令行中创建。要使用 createuser 命令，您需要先登录到 postgres 账户。如果已登录 postgres，请运行以下命令来创建一个新角色。
postgres@howtoforge:~$ createuser --interactive
--interactive 参数会提示您输入角色名称，并询问是否应赋予其超级用户权限。
您也可以直接从自己的账户运行该命令。
$ sudo -u postgres createuser --interactive
您将看到如下输出。
Enter name of role to add: navjot
Shall the new role be a superuser? (y/n) y
若要从 psql 命令行创建角色，请先进入命令行，然后执行以下命令。
postgres-# CREATE ROLE username WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'yourpassword';
这里我们创建的角色拥有超级用户、创建数据库、创建角色以及登录等权限。
您可以使用 \du 命令查看所有用户的列表。
postgres-# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
username | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
步骤 4 - 创建数据库
要创建数据库，我们将使用 createdb 命令。如果已登录 postgres 账户，请运行以下命令来创建一个新数据库。
postgres@howtoforge:~$ createdb howtoforge
您也可以从自己的账户运行该命令。
$ sudo -u postgres createdb howtoforge
您也可以使用 psql 命令行创建数据库。为此，请在命令行中运行以下命令。
postgres-# CREATE DATABASE howtoforge;
通过运行以下命令，我们可以为刚刚创建的用户授予该数据库的权限。
postgres-# GRANT ALL PRIVILEGES ON DATABASE howtoforge TO username;
您可以使用 \l 命令查看所有数据库的列表。
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
howtoforge| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | username=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
步骤 5 - 启用远程访问
默认情况下，PostgreSQL 服务器只监听 localhost 接口。要启用远程访问，请打开 /etc/postgresql/14/main/postgresql.conf 文件，取消 #listen_addresses = 'localhost' 这一行的注释，并按如下所示在“连接与认证（CONNECTIONS and AUTHENTICATION）”部分进行修改。
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
按下 Ctrl + X 并在提示时输入 Y 以保存文件。
重启 PostgreSQL 服务。
$ sudo systemctl restart postgresql
要验证更改，请运行以下命令。
$ ss -nlt | grep 5432
您将看到如下输出，显示 PostgreSQL 正在监听所有接口。
LISTEN 0 244 0.0.0.0:5432 0.0.0.0:*
LISTEN 0 244 [::]:5432 [::]:*
下一步是配置服务器以接受远程连接。这可以通过编辑 /etc/postgresql/14/main/pg_hba.conf 文件来完成。请打开该文件进行编辑。
$ sudo nano /etc/postgresql14/main/pg_hba.conf
在这里，您可以配置对单个或全部数据库的访问权限，并定义哪些用户可以通过哪些接口访问它们。要使密码认证生效，请将认证方式选择为 scram-sha-256。以下是一些可供使用的示例。
# TYPE DATABASE USER ADDRESS METHOD
# The username can access all databases from all locations using scram-sha256 password
host all username 0.0.0.0/0 scram-sha256
# The username can access only the howtoforge from all locations using scram-sha256 password
host howtoforge username 0.0.0.0/0 scram-sha256
# The username can access all databases from a trusted location (192.168.1.134) without a password
host all username 192.168.1.134 trust
完成后，按下 Ctrl + X 并在提示时输入 Y 以保存文件。
在远程访问生效之前，还剩最后一步。您需要开放防火墙的 5432 端口。请运行以下命令开放该端口。
$ sudo ufw allow 5432/tcp
这将允许任何 IP 地址访问 PostgreSQL。不过，您应当将其限制为可信的 IP 范围。为此，请改为运行以下命令。以下命令允许来自 192.168.1.0/24 子网的访问。
$ sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432
步骤 6 - 安装 pgAdmin 4
pgAdmin 既可以作为 Ubuntu 软件包安装，也可以通过 Python 安装。以 Ubuntu 软件包方式安装时会默认安装 Apache 服务器。不过，本教程将使用 Nginx。因此，我们将通过 Python wheel（Python 包）来安装它。
为 pgAdmin 创建数据和日志目录。
$ sudo mkdir /var/lib/pgadmin
$ sudo mkdir /var/log/pgadmin
为 pgAdmin 创建一个应用文件夹。
$ sudo mkdir /opt/pgadmin
切换到 root 用户。
$ sudo su -
首先，安装 Python 所需的几个重要软件包。
$ apt install build-essential python3-dev python3-venv python3-pip
创建一个 Python 虚拟环境并激活它。
$ python3 -m venv /opt/pgadmin/venv
$ source /opt/pgadmin/venv/bin/activate
安装 pgAdmin 和 uWSGI。
(venv) root@postgresql:-$ pip install --upgrade pip
(venv) root@postgresql:-$ pip install --upgrade setuptools
(venv) root@postgresql:-$ pip install wheel
(venv) root@postgresql:-$ pip install pgadmin4
(venv) root@postgresql:-$ pip install uwsgi
运行 pgAdmin 的安装文件。请检查路径。Ubuntu 22.04 默认自带 Python 3.10，因此它会出现在路径中。请确保选择正确路径。
(venv) root@postgresql:-$ python3 /opt/pgadmin/venv/lib/python3.10/site-packages/pgadmin4/setup.py
按照安装提示操作，并在此记下您的电子邮箱和密码。
NOTE: Configuring authentication for SERVER mode.
Enter the email address and password to use for the initial pgAdmin user account:
Email address:
[email protected]
Password:
Retype password:
pgAdmin 4 - Application Initialisation
======================================
停用虚拟环境。
(venv) root@postgresql:-$ deactivate
切换回普通用户。
(venv) root@postgresql:-$ exit
创建一个名为 pgadmin 的新系统用户，它将用于运行 pgAdmin 进程。
$ sudo adduser --system --group --home /var/lib/pgadmin --disabled-login --shell /usr/sbin/nologin pgadmin
由于我们将使用 Nginx，请将上述目录的所有权赋予 Nginx 以及新创建的用户。
$ sudo chown -R pgadmin:nginx /var/lib/pgadmin /var/log/pgadmin /opt/pgadmin
步骤 7 - 集成 uWSGI 与 pgAdmin
下一步是创建一个监听于 UNIX 套接字 /tmp/pgadmin.sock 的 WSGI 服务器。请在 /opt/pgadmin 文件夹中创建一个名为 pgadmin-uwsgi.ini 的 uWSGI 配置文件。
$ sudo nano /opt/pgadmin/pgadmin-uwsgi.ini
向其中添加以下代码。请确保路径中的 Python 版本与您系统中安装的版本一致。
[uwsgi]
socket = /var/lib/pgadmin/pgadmin4.sock
chdir = /opt/pgadmin/venv/lib/python3.10/site-packages/pgadmin4/
module = pgAdmin4:application
threads = 20
processes = 1
mount = /=pgAdmin4:app
manage-script-name = true
chmod-socket = 660
按下 Ctrl + X 并在提示时输入 Y 以保存文件。
步骤 8 - 创建服务文件
既然已经配置好 pgAdmin 和 uWSGI，我们需要为其创建一个 systemd 服务单元文件。该服务文件将让服务器在启动时自动启动 uWSGI 并提供 pgAdmin 4 应用服务。
创建并打开该服务文件进行编辑。
$ sudo nano /etc/systemd/system/pgadmin-uwsgi.service
在其中粘贴以下代码。
[Unit]
Description=pgadmin4 on uWSGI
Requires=network.target
After=network.target
[Service]
User=pgadmin
Group=nginx
Environment="PATH=/opt/pgadmin/venv/bin"
ExecStart=/opt/pgadmin/venv/bin/uwsgi --ini /opt/pgadmin/pgadmin-uwsgi.ini
[Install]
WantedBy=multi-user.target
按下 Ctrl + X 并在提示时输入 Y 以保存文件。
如您所见，我们选择了 nginx 作为运行 pgAdmin 进程的组，因为我们将使用它进行代理。
启用并启动 uWSGI 服务。
$ sudo systemctl enable pgadmin-uwsgi --now
检查该服务的状态。
$ sudo systemctl status pgadmin-uwsgi
步骤 9 - 安装 SSL
在配置 Nginx 之前，我们需要先配置 SSL 证书。
要使用 Let's Encrypt 安装 SSL 证书，我们需要下载 Certbot 工具。这里我们将使用 Snapd 包管理器来完成。
安装 Snap 安装器。
$ sudo apt install snapd
确保您的 Snapd 版本为最新。
$ sudo snap install core
$ sudo snap refresh core
安装 Certbot。
$ sudo snap install --classic certbot
使用以下命令创建指向 /usr/bin 目录的符号链接，以确保 Certbot 命令可以正常运行。
$ sudo ln -s /snap/bin/certbot /usr/bin/certbot
停止 Nginx 服务，因为它会干扰 Certbot 工具。
$ sudo systemctl stop nginx
生成 SSL 证书。
$ sudo certbot certonly --standalone --agree-tos --no-eff-email --staple-ocsp --preferred-challenges http -m
[email protected]
-d pgadmin.example.com
上述命令会将证书下载到您服务器上的 /etc/letsencrypt/live/pgadmin.example.com 目录。
生成 Diffie-Hellman 组证书。
$ sudo openssl dhparam -out /etc/ssl/certs/dhparam.pem 4096
为 Let's Encrypt 的自动续期创建一个质询 Web 根目录。
$ sudo mkdir -p /var/lib/letsencrypt
创建一个用于续期 SSL 的 Cron 任务。它每天运行一次，检查证书并在需要时续期。为此，首先创建文件 /etc/cron.daily/certbot-renew 并打开进行编辑。
$ sudo nano /etc/cron.daily/certbot-renew
粘贴以下代码。
#!/bin/sh
certbot renew --cert-name pgadmin.example.com --webroot -w /var/lib/letsencrypt/ --post-hook "systemctl reload nginx"
按下 Ctrl + X 并在提示时输入 Y 以保存文件。
更改该任务文件的权限，使其可执行。
$ sudo chmod +x /etc/cron.daily/certbot-renew
步骤 10 - 安装并配置 Nginx
Ubuntu 自带的是较旧版本的 Nginx。要安装最新版本，您需要下载官方的 Nginx 软件源。
导入 Nginx 的签名密钥。
$ curl https://nginx.org/keys/nginx_signing.key | gpg --dearmor \
| sudo tee /usr/share/keyrings/nginx-archive-keyring.gpg >/dev/null
添加 Nginx 稳定版的软件源。
$ echo "deb [signed-by=/usr/share/keyrings/nginx-archive-keyring.gpg arch=amd64] \
http://nginx.org/packages/ubuntu `lsb_release -cs` nginx" \
| sudo tee /etc/apt/sources.list.d/nginx.list
更新系统软件源。
$ sudo apt update
安装 Nginx。
$ sudo apt install nginx
验证安装结果。
$ nginx -v
nginx version: nginx/1.20.2
配置 Nginx
创建并打开 /etc/nginx/conf.d/pgadmin.conf 文件进行编辑。
$ sudo nano /etc/nginx/conf.d/pgadmin.conf
在其中粘贴以下代码。
server {
listen 80;
listen [::]:80;
server_name pgadmin.example.com;
return 301 https://$host$request_uri;
}
server {
listen 443 ssl http2;
listen [::]:443 ssl http2;
server_name pgadmin.example.com;
ssl_certificate /etc/letsencrypt/live/pgadmin.example.com/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/pgadmin.example.com/privkey.pem;
ssl_trusted_certificate /etc/letsencrypt/live/pgadmin.example.com/chain.pem;
ssl_session_cache builtin:1000 shared:SSL:10m;
ssl_session_timeout 5m;
ssl_session_tickets off;
ssl_protocols TLSv1.2 TLSv1.3;
ssl_ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384;
ssl_prefer_server_ciphers on;
ssl_ecdh_curve X25519:prime256v1:secp384r1:secp521r1;
ssl_stapling on;
ssl_stapling_verify on;
ssl_dhparam /etc/ssl/certs/dhparam.pem;
location /pgadmin4/ {
include /etc/nginx/uwsgi_params;
uwsgi_pass unix:/tmp/pgadmin4.sock;
}
}
按下 Ctrl + X 并在提示时输入 Y 以保存文件。
打开 /etc/nginx/nginx.conf 文件进行编辑。
$ sudo nano /etc/nginx/nginx.conf
在 include /etc/nginx/conf.d/*.conf; 这一行之前添加以下行。
server_names_hash_bucket_size 64;
按下 Ctrl + X 并在提示时输入 Y 以保存文件。
验证 Nginx 配置文件的语法。
$ sudo nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
重启 Nginx 服务以使新配置生效。
$ sudo systemctl restart nginx
步骤 11 - 配置防火墙
在访问 pgAdmin 之前，我们需要确保防火墙端口已开放。如果您使用的是 ufw 防火墙，请运行以下命令开放 HTTP 和 HTTPS 端口。
$ sudo ufw allow 80/tcp
$ sudo ufw allow 443/tcp
检查防火墙的状态。
$ sudo ufw status
您应该会看到类似的输出。
Status: active
To Action From
-- ------ ----
OpenSSH ALLOW Anywhere
80/tcp ALLOW Anywhere
443/tcp ALLOW Anywhere
5432/tcp ALLOW 192.168.1.0/24
OpenSSH (v6) ALLOW Anywhere (v6)
80/tcp (v6) ALLOW Anywhere (v6)
443/tcp (v6) ALLOW Anywhere (v6)
步骤 11 - 访问并配置 pgAdmin
在访问 pgAdmin 之前，我们需要为默认的 postgres 用户设置一个密码。
打开 psql 命令行。
$ sudo -u postgres psql
运行以下命令来设置密码。
postgres-# \password postgres
Enter new password for user "postgres":
Enter it again:
退出命令行。
postgres-# exit
同时，更改 UNIX 套接字的所有权。
$ sudo chown nginx:nginx /var/lib/pgadmin/pgadmin4.sock
在浏览器中打开 URL https://pgadmin.example.com，您将看到如下登录界面。
输入您在步骤 6 中创建的用户凭据进行登录，即可访问如下仪表盘。
下一步是向其添加本地 PostgreSQL 服务器。点击“Add New Server（添加新服务器）”按钮开始操作。
为连接指定一个名称，并切换到 Connection（连接）选项卡。
在 Hostname（主机名）处输入 localhost，端口字段留空。添加之前创建的密码，并勾选 Save password?（保存密码？）选项。点击 Save（保存）按钮完成服务器的添加。
完成后，您将看到如下属于您 PostgreSQL 服务器的仪表盘界面。
让我们创建一个新表，并向数据库中添加一些数据。
在仪表盘中，点击 PostgreSQL - Local 旁边的箭头符号以展开树形菜单。接着展开 Databases（数据库），点击您所添加数据库的名称（本示例中为 howtoforge），然后展开 Schemas（模式）。您应该会看到如下菜单。
右键单击 Tables（表）选项，然后依次选择 Create（创建）>> Table（表）菜单项，如图所示。
这将打开“创建表”弹出窗口，如下图所示。为表输入一个名称。
切换到 Columns（列）选项卡。在这里我们将为表定义一些基本列。
点击加号以添加第一列。我们将其命名为 ID，数据类型为 integer（整数）。每个表都应有一个主键。要将 ID 设为该表的主键，请打开 Primary key（主键）对应的开关。
接下来，我们再添加一列 Name，数据类型为 character varying（可变字符）。为了限制名称长度，我们将其长度定义为 30。目前这对于我们的示例已经足够。完成后点击 Save（保存）按钮以创建该表。
现在我们已经有了带一些列的表，下一步是向表中填入一些值。要添加数据，请在 Browser（浏览器）中右键单击您的表名，将光标移到 Scripts（脚本）上，然后选择 INSERT Script（INSERT 脚本）选项。
这将在仪表盘中打开一个新标签页，其中包含一个部分填写好的 INSERT 命令。将问号替换为您要填入的相应数据。以下是我们用于本表的示例数据。
INSERT INTO public."table-01"(
"ID", "Name")
VALUES (1, 'Navjot'), (2, Adam), (3, 'Roxy');
点击倾斜的三角形按钮以执行 SQL 查询并添加数据。
接下来，您可能想查看刚刚添加的数据。为此，请右键单击表名，选择 View/Edit Data（查看/编辑数据）选项，然后点击 All Rows（所有行）。
这将在仪表盘中打开另一个标签页，您的数据会显示在下方面板的 Data output（数据输出）选项卡中。
您终于使用 pgAdmin 创建了一个数据库并向其中添加了一些数据。使用 pgAdmin，您还能做更多的事情。
PostgreSQL 自带用于备份和恢复数据的实用工具。pgAdmin 原生支持它们，但要使其正常工作，您可能需要添加 Postgres 二进制文件的路径。为此，请打开顶部的 File（文件）菜单，并选择 Preferences（首选项）选项。
这将打开一个包含大量首选项的新弹出窗口。在左侧边栏中选择 Paths（路径）>> Binary paths（二进制路径）选项。接着向下滚动到弹出窗口中的 PostgreSQL Binary Path（PostgreSQL 二进制路径）部分。
在 PostgreSQL 14 server（PostgreSQL 14 服务器）条目前方输入 /usr/bin 作为路径，并勾选其对应的复选框。完成后点击 Save（保存）按钮。这样您就应该能够通过 pgAdmin 的界面使用备份和恢复命令了。
总结
您已经学会了如何在 Ubuntu 22.04 服务器上安装 PostgreSQL 和 pgAdmin。您还配置了 pgAdmin 以连接 PostgreSQL 服务并执行基本操作。如果您有任何疑问，请在下方评论区留言。