Ubuntu 24.04 LTS安装和使用PostgreSQL 18详细过程步骤

PostgreSQL（简称Postgres）是一个开源、功能强大、先进、高性能且稳定的关系文档数据库系统，它扩展了SQL语言，并包含一系列用于安全数据存储和管理的功能。

PostgreSQL高效、可靠且可扩展，能够处理大量复杂的数据，并建立企业级和容错环境，同时确保高数据完整性。此外，它还具有高度可扩展性，具备高级索引、全文搜索等功能，并提供API，以便用户可以开发自己的解决方案来处理数据存储方面的挑战。

在本文中，小编将详细介绍在Ubuntu 24.04 LTS服务器上安装PostgreSQL 18 （已于2025年9月25日发布），并学习有效使用它的基本方法。

Ubuntu 24.04安装PostgreSQL

PostgreSQL默认随所有Ubuntu版本一同预装。不过，Ubuntu所包含的是PostgreSQL的特定 “快照版本”，该版本在该Ubuntu发行版的整个生命周期内都保持不变。

如果你希望使用更新的PostgreSQL版本，可以借助以下自动化的仓库配置脚本，它会自动设置官方的PostgreSQL Apt仓库：

sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

现在更新软件包列表并安装PostgreSQL （当前最新版本为PostgreSQL 18 ）：

sudo apt update
sudo apt install postgresql-18 postgresql-contrib-18

安装pgAdmin 4以进行PostgreSQL管理

如果你更喜欢使用图形界面来管理PostgreSQL数据库，那么可以安装pgAdmin 4，这是官方的PostgreSQL管理工具，允许你通过基于Web的仪表板管理数据库、用户、查询等。

在Ubuntu 24.04系统上，默认存储库中没有pgAdmin 4，因此将使用官方的pgAdmin存储库，命令如下：

sudo apt install -y curl ca-certificates gnupg
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

添加pgAdmin存储库后，即可按照如下所示方式安装pgAdmin 4：

# 同时安装到桌面和网页模式
sudo apt install pgadmin4

# 仅安装于桌面模式
sudo apt install pgadmin4-desktop

# 仅安装于网页模式
sudo apt install pgadmin4-web

# 如果安装了pgadmin4-web，请配置Web服务器：
sudo /usr/pgadmin4/bin/setup-web.sh

安装完成后，PostgreSQL服务应该会自动启动，可以通过运行以下命令确认其状态：

sudo systemctl status postgresql

要启用PostgreSQL在启动时自动启动（如果尚未启用的话）：

sudo systemctl enable postgresql

运行此命令后，可以在浏览器中打开pgAdmin ：

http://127.0.0.1/pgadmin4

关于PostgreSQL角色和数据库

在PostgreSQL中，客户端身份验证由配置文件/etc/postgresql/18/main/pg_hba.conf控制（版本号可能因安装而异）。

默认的认证方式对数据库管理员而言是"peer"，这意味着它会获取客户端的操作系统用户名，并检查该用户名是否与请求的数据库用户名匹配，以允许本地连接访问。

在安装过程中，会创建了一个名为“postgres”的系统用户帐户，但没有设置密码，该帐户也是默认的数据库管理员用户名。

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

1、了解PostgreSQL角色

在PostgreSQL中，数据库访问权限管理是通过角色来实现的。角色可以被视为单个数据库用户，也可以被视为一组数据库用户，具体取决于角色的设置方式。

默认角色也是postgres。重要的是，据库角色在概念上与操作系统用户完全无关，但在实际应用中它们可能存在关联（尤其是出于认证目的）。角色可以：

• 拥有数据库对象（表、视图、函数等）
• 将这些对象的权限分配给其他角色
• 将角色成员资格授予另一个角色（角色继承）

2、为PostgreSQL角色启用加密密码

要配置角色以使用加密密码而不是peer身份验证，则需要修改pg_hba.conf文件。将认证方式从peer更改为scram-sha-256（现代安全方法），或改为md5以使用密码认证。

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

找出类似以下的行：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer

改为：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256

这样既能保留postgres用户的peer身份验证，又能要求其他用户使用密码认证。

然后重启PostgreSQL服务以使更改生效：

sudo systemctl restart postgresql

在Ubuntu上使用PostgreSQL

一切设置完毕后，这时可以使用以下命令访问postgres系统帐户，其中-i标志告诉sudo以登录shell的形式运行目标用户的密码数据库条目所指定的shell：

sudo -i -u postgres
psql    # to launch the postgres shell program

要直接访问postgres shell，而无需先访问postgres用户帐户，请运行以下命令：

sudo -u postgres psql

你可以通过输入\q或按Ctrl+D来退出postgres shell：

\q

1、创建PostgreSQL数据库角色

使用以下命令创建新的用户角色：

CREATE ROLE ecscoupon;

要创建具有LOGIN属性的角色（具有LOGIN属性的角色可以视为与数据库用户相同）：

CREATE ROLE ecscoupon LOGIN;

或者使用CREATE USER命令，该命令默认包含登录功能：

CREATE USER ecscoupon;

2、创建带密码的角色

角色还可以设置密码，如果你将客户端身份验证方法配置为需要加密密码，则此操作至关重要：

CREATE ROLE ecscoupon WITH LOGIN PASSWORD 'secure_password_here';

或者使用CREATE USER语法：

CREATE USER ecscoupon WITH PASSWORD 'secure_password_here';

3、创建具有额外权限的角色

你可以创建具有超级用户权限的角色（请谨慎使用）：

CREATE ROLE admin WITH LOGIN PASSWORD 'admin_password' SUPERUSER;

或者创建一个可以创建数据库的角色：

CREATE ROLE developer WITH LOGIN PASSWORD 'dev_password' CREATEDB;

4、列出现有的PostgreSQL数据库角色

要列出现有用户角色，请使用以下任一命令：

\du    -- 显示实际用户及其详细信息

或者：

SELECT rolname FROM pg_roles;

查看更多详细信息，输入以下命令：

\du+

5、修改PostgreSQL数据库角色

更改角色密码，请输入以下命令：

ALTER ROLE ecscoupon WITH PASSWORD 'new_password';

向现有角色授予超级用户权限：

ALTER ROLE ecscoupon WITH SUPERUSER;

撤销超级用户权限：

ALTER ROLE ecscoupon WITH NOSUPERUSER;

6、删除PostgreSQL数据库角色

要删除现有用户角色，请使用DROP ROLE命令：

DROP ROLE ecscoupon;

注意：你不能删除拥有数据库对象的角色，必须先重新分配这些对象的所有权或删除这些对象。

7、创建PostgreSQL数据库

创建具有特定名称（例如ecscoupon用户）的角色后，就可以创建一个由该角色管理的数据库：

CREATE DATABASE ecscoupon;

创建由特定角色拥有的数据库：

CREATE DATABASE ecscoupon OWNER ecscoupon;

要创建具有特定编码的数据库：

CREATE DATABASE ecscoupon ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' OWNER ecscoupon;

8、授予角色权限

创建数据库后，授予该角色所有权限：

GRANT ALL PRIVILEGES ON DATABASE ecscoupon TO ecscoupon;

现在要管理数据库ecscoupon，请以ecscoupon角色访问postgres shell：

psql -U ecscoupon -d ecscoupon

如果系统提示输入密码，请输入你为该角色设置的密码。

9、创建PostgreSQL表

例如这里将创建一个名为authors的测试表，其中存储ecscoupon.com有关作者的信息：

CREATE TABLE authors (
    code      SERIAL PRIMARY KEY,
    name      VARCHAR(40) NOT NULL,
    city      VARCHAR(40) NOT NULL,
    joined_on DATE NOT NULL
);

注意：这里使用SERIAL作为主键，它会自动生成连续的数字，这比手动分配编号更实用。

10、将数据插入PostgreSQL表

创建表格后，向其中填充一些数据：

INSERT INTO authors (name, city, joined_on) VALUES 
    ('Ravi Saive', 'Mumbai', '2022-08-15'),
    ('Aaron Kili', 'Nairobi', '2024-03-20'),
    ('Matei Cezar', 'Bucharest', '2025-06-10');

11、从PostgreSQL表中查询数据

要查看表中存储的数据，请运行SELECT命令：

SELECT * FROM authors;

针对特定列：

SELECT name, city FROM authors;

带过滤功能：

SELECT * FROM authors WHERE city = 'Mumbai';

顺序方式：

SELECT * FROM authors ORDER BY joined_on DESC;

12、更新PostgreSQL表中的数据

修改现有数据：

UPDATE authors SET city = 'Delhi' WHERE name = 'Ravi Saive';

13、从PostgreSQL表中删除数据

要删除特定行：

DELETE FROM authors WHERE name = 'Ravi Saive';

14、列出PostgreSQL数据库表

你可以使用以下命令列出当前数据库中的所有表：

\dt

更多详细信息：

\dt+

查看表结构：

\d authors

15、修改PostgreSQL表结构

向现有表中添加新列：

ALTER TABLE authors ADD COLUMN email VARCHAR(100);

删除列：

ALTER TABLE authors DROP COLUMN email;

重命名列：

ALTER TABLE authors RENAME COLUMN code TO author_id;

16、删除/丢弃PostgreSQL表

要删除当前数据库中的表：

DROP TABLE authors;

级联删除（移除依赖对象）：

DROP TABLE authors CASCADE;

17、列出所有PostgreSQL数据库

要列出所有数据库，请使用以下任一命令：

SELECT datname FROM pg_database;

或者，需要更详细的描述：

\list

或者简写为：

\l

18、删除/丢弃PostgreSQL数据库

如果要删除数据库：

DROP DATABASE ecscoupon;

警告：此操作将永久删除数据库中的所有数据。

19、切换到另一个PostgreSQL数据库

可以轻松地在不同的数据库之间切换：

\connect database_name

或者简写为：

\c database_name

配置PostgreSQL以进行远程访问

默认情况下，PostgreSQL只接受来自localhost的连接。要允许远程连接，需要编辑postgresql.conf文件：

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

找出这行：

#listen_addresses = 'localhost'

改为：

listen_addresses = '*'

接下来，编辑pg_hba.conf文件：

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

添加一行代码以允许来自你的网络连接（将192.168.1.0/24替换为你的网络）：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          scram-sha-256

或者允许来自任何IP地址的请求（安全性较低）：

host    all             all             0.0.0.0/0               scram-sha-256

允许PostgreSQL通过防火墙：

sudo ufw allow 5432/tcp

最后，重启PostgreSQL：

sudo systemctl restart postgresql

PostgreSQL基本性能调优

为了在Ubuntu 24.04上获得更好的性能，请考虑调整postgresql.conf设置：

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

建议更改（根据服务器内存进行调整）：

shared_buffers = 256MB              # 大概内存的25%
effective_cache_size = 1GB          # 大概内存的50-75%
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4MB

更改完成后，请重启PostgreSQL：

sudo systemctl restart postgresql

备份和恢复PostgreSQL数据库

备份单个数据库：

pg_dump -U postgres tecmint > tecmint_backup.sql

备份所有数据库：

pg_dumpall -U postgres > all_databases_backup.sql

恢复数据库：

psql -U postgres tecmint < tecmint_backup.sql

实用PostgreSQL命令参考

以下是常用PostgreSQL命令，可供参考：

小结