# 1. 服务器配置
– CPU：8核以上
– 内存：16GB以上
– 存储：500GB以上SSD
– 网络：千兆网卡以上

# 2. 操作系统安装
$ yum install -y oraclelinux-release-el9
$ yum update -y
$ yum install -y epel-release

# 3. 系统配置
$ vi /etc/sysctl.conf

# 内存管理
vm.swfgappiness = 1
vm.overcommit_memory = 2

# 文件系统
fs.file-max = 6815744

# 网络
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096

# 应用
$ sysctl -p

# 4. 存储配置
$ fdisk /dev/sdb
$ mkfs.xfs /dev/sdb1
$ mkdir /postgresql
$ echo “/dev/sdb1 /postgresql xfs defaults 0 0” >> /etc/fstab
$ mount -a

# 1. 安装PostgreSQL
$ yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ yum install -y postgresql18-server postgresql18-contrib

# 2. 初始化数据库
$ /postgresql/fgapp/bin/postgresql-18-setup initdb

# 3. 配置PostgreSQL
$ vi /postgresql/fgdata/postgresql.conf

# 基本配置
listen_addresses = ‘*’
port = 5432
max_connections = 100

# 内存配置
shared_buffers = 4GB
work_mem = 32MB
maintenance_work_mem = 1GB

# 存储配置
effective_cache_size = 12GB
random_page_cost = 1.1

# WAL配置
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB

# 检查点
checkpoint_completion_target = 0.9

# 4. 配置pg_hba.conf
$ vi /postgresql/fgdata/pg_hba.conf

# 本地连接
local all all trust

# IPv4 local connections:
host all all 127.0.0.1/32 trust

# IPv4 network connections:
host all all 192.168.1.0/24 md5

# 5. 启动PostgreSQL
$ systemctl start postgresql-18
$ systemctl enable postgresql-18

# 1. 创建数据库
$ su – postgres
$ psql

postgres=# CREATE DATABASE fgedudb;
CREATE DATABASE

# 2. 创建用户
postgres=# CREATE USER fgedu WITH PASSWORD ‘fgedu_pass’;
CREATE ROLE

postgres=# GRANT ALL PRIVILEGES ON DATABASE fgedudb TO fgedu;
GRANT

# 3. 创建表空间
postgres=# CREATE TABLESPACE fgedutbs LOCATION ‘/postgresql/fgdata/tbs’;
CREATE TABLESPACE

# 4. 创建测试表
postgres=# \c fgedudb
fgedudb=# CREATE TABLE fgedu_users (
id serial PRIMARY KEY,
name text NOT NULL,
email text NOT NULL,
registration_date timestamp NOT NULL
);
CREATE TABLE

fgedudb=# CREATE TABLE fgedu_orders (
id serial PRIMARY KEY,
order_date date NOT NULL,
customer_id integer NOT NULL,
amount numeric(10,2) NOT NULL,
status text NOT NULL
);
CREATE TABLE

# 5. 插入测试数据
fgedudb=# INSERT INTO fgedu_users (name, email, registration_date) VALUES
(‘User 1’, ‘user1@fgedu.net.cn’, NOW()),
(‘User 2’, ‘user2@fgedu.net.cn’, NOW()),
(‘User 3’, ‘user3@fgedu.net.cn’, NOW());
INSERT 0 3

fgedudb=# INSERT INTO fgedu_orders (order_date, customer_id, amount, status) VALUES
(NOW(), 1, 100.00, ‘completed’),
(NOW(), 2, 200.00, ‘pending’),
(NOW(), 3, 150.00, ‘completed’);
INSERT 0 3

# 1. 安装应用服务器
$ yum install -y httpd php php-pgsql

# 2. 配置应用
$ mkdir /var/www/html/fgedu
$ vi /var/www/html/fgedu/index.php

用户列表

‘;
echo ‘

‘;
echo ‘

‘;

while ($row = pg_fetch_assoc($result)) {
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
}
echo ‘

ID	Name	Email	Registration Date
‘ . $row[‘id’] . ‘	‘ . $row[‘name’] . ‘	‘ . $row[’email’] . ‘	‘ . $row[‘registration_date’] . ‘

‘;

// 查询订单数据
$result = pg_query($conn, ‘SELECT * FROM fgedu_orders’);
if (!$result) {
die(‘查询失败: ‘ . pg_last_error());
}

echo ‘

订单列表

‘;
echo ‘

‘;
echo ‘

‘;

while ($row = pg_fetch_assoc($result)) {
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
echo ‘

‘;
}
echo ‘

ID	Order Date	Customer ID	Amount	Status
‘ . $row[‘id’] . ‘	‘ . $row[‘order_date’] . ‘	‘ . $row[‘customer_id’] . ‘	‘ . $row[‘amount’] . ‘	‘ . $row[‘status’] . ‘

‘;

pg_close($conn);
?>

# 3. 启动Apache
$ systemctl start httpd
$ systemctl enable httpd

# 4. 测试应用
# 打开浏览器，访问 http://fgedu.localhost/fgedu

# 1. 数据库功能测试
$ psql -U fgedu -d fgedudb

fgedudb=> SELECT * FROM fgedu_users;
id | name | email | registration_date
—-+——–+———————+————————
1 | User 1 | user1@fgedu.net.cn | 2026-04-06 10:00:00
2 | User 2 | user2@fgedu.net.cn | 2026-04-06 10:00:00
3 | User 3 | user3@fgedu.net.cn | 2026-04-06 10:00:00
(3 rows)

fgedudb=> SELECT * FROM fgedu_orders;
id | order_date | customer_id | amount | status
—-+————+————-+——–+————
1 | 2026-04-06 | 1 | 100.00 | completed
2 | 2026-04-06 | 2 | 200.00 | pending
3 | 2026-04-06 | 3 | 150.00 | completed
(3 rows)

# 2. 应用功能测试
# 打开浏览器，访问 http://fgedu.localhost/fgedu
# 验证用户列表和订单列表是否正确显示

# 1. 插入大量数据
$ psql -U fgedu -d fgedudb

fgedudb=> INSERT INTO fgedu_users (name, email, registration_date)
SELECT ‘User ‘ || i, ‘user’ || i || ‘@fgedu.net.cn’, NOW()
FROM generate_series(4, 10000) i;
INSERT 0 9997

fgedudb=> INSERT INTO fgedu_orders (order_date, customer_id, amount, status)
SELECT NOW() – (i || ‘ days’)::interval,
(i % 10000) + 1,
(i % 1000) + 100.00,
CASE WHEN i % 2 = 0 THEN ‘completed’ ELSE ‘pending’ END
FROM generate_series(1, 100000) i;
INSERT 0 100000

# 2. 执行性能测试
fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE customer_id = 1000;
QUERY PLAN
——————————————————————————————————–
Seq Scan on fgedu_orders (cost=0.00..1795.00 rows=10 width=44) (actual time=0.015..0.500 rows=10 loops=1)
Filter: (customer_id = 1000)
Rows Removed by Filter: 99990
Planning Time: 0.123 ms
Execution Time: 0.510 ms

# 3. 创建索引后测试
fgedudb=> CREATE INDEX idx_fgedu_orders_customer_id ON fgedu_orders (customer_id);
CREATE INDEX

fgedudb=> EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE customer_id = 1000;
QUERY PLAN
——————————————————————————————————–
Index Scan using idx_fgedu_orders_customer_id on fgedu_orders (cost=0.29..8.31 rows=10 width=44) (actual time=0.010..0.015 rows=10 loops=1)
Index Cond: (customer_id = 1000)
Planning Time: 0.150 ms
Execution Time: 0.020 ms

# 1. 权限测试
$ psql -U postgres

postgres=# CREATE USER test_user WITH PASSWORD ‘test_pass’;
CREATE ROLE

postgres=# \c fgedudb
fgedudb=# SELECT * FROM fgedu_users;
ERROR: permission denied for table fgedu_users

# 2. 连接测试
$ psql -h 192.168.1.10 -U fgedu -d fgedudb
Password for user fgedu:
psql (18.3)
Type “help” for help.

fgedudb=> SELECT * FROM fgedu_users;
id | name | email | registration_date
—-+——–+———————+————————
1 | User 1 | user1@fgedu.net.cn | 2026-04-06 10:00:00
2 | User 2 | user2@fgedu.net.cn | 2026-04-06 10:00:00
3 | User 3 | user3@fgedu.net.cn | 2026-04-06 10:00:00
(3 rows)