postgresql安装配置

发表于 更新于 本文字数： 1k 阅读时长 ≈ 4 分钟

安装

前置依赖

系统是ubuntu 24.04 LTS，开机启动使用了systemd，所以先安装相关依赖包

1
sudo apt install libsystemd-dev

编译安装

官网推荐使用pre-packaged方式，稳定可靠，适合生产环境。不过我想用最新版，就直接用源码编译安装了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 进入源代码目录
cd postgresql-16.4

# 指定安装目录、设置支持systemd
./configure --prefix=/usr/local/postgresql-16.4 --with-systemd --with-ssl=openssl

# 编译安装
make
sudo make install

# 软连接，方便升级用
sudo ln -s /usr/local/postgresql-16.4 /usr/local/pgsql

# 创建用户
sudo adduser postgres

# 数据库数据存储目录，与app目录分离
sudo mkdir -p /var/lib/pgsql/data
sudo chown -R postgres /var/lib/pgsql

# 切换到postgres用户
su - postgres

# 初始化数据库目录、启动实例
/usr/local/pgsql/bin/initdb -D /var/lib/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
# 验证
/usr/local/pgsql/bin/psql
# 关闭实例
# /usr/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/data/ -l logfile stop

到这里，postgresql完成安装、初始化

systemd配置

开机启动配置systemd，添加配置文件/etc/systemd/system/postgresql.service，内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /var/lib/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity

[Install]
WantedBy=multi-user.target

使用systemd启动postgresql

1
2
3
sudo systemctl daemon-reload
sudo systemctl start postgresql
sudo systemctl enable postgresql

pgsql配置

以上完成pg安装，本地可以直接访问使用。接下来是配置数据库提供对外访问能力，如配置tcp监听地址、添加用户、配置权限等

进入pg的data数据存储目录，修改相关配置文件

tcp配置

修改postgresql.conf文件，配置监听地址

1
2
# 默认localhost，改为'*'
listen_addresses = '*'

auth配置

PS: pg的授权配置没有像mysql一样开箱即用，先简单配置下吧

修改pg_hba.conf文件，开放数据库/用户对外访问权限

1
2
3
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    blogdb          all             0.0.0.0/0               scram-sha-256
host    blogdb          all             ::0/0                   scram-sha-256

重启postgresql

1
sudo systemctl start postgresql

修改超级用户密码或者创建新角色并授权

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 1. 修改postgres密码
-- alter user postgres password 'your_password';

-- 2. 创建新角色并授权
\c blogdb;

CREATE ROLE blog_read_only WITH LOGIN PASSWORD 'your_password';
CREATE ROLE blog_read_write WITH LOGIN PASSWORD 'your_password';
CREATE ROLE blog_admin WITH LOGIN PASSWORD 'your_password';

GRANT SELECT ON ALL TABLES IN SCHEMA public TO blog_read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO blog_read_write;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO blog_admin;

curd example

完成了pg的安装配置，接下来就是写一个demo测试了，首先，找一个数据表导入

1
2
3
# 获取countries.csv数据
curl 'https://raw.githubusercontent.com/lukes/ISO-3166-Countries-with-Regional-Codes/master/all/all.csv' \
    --output ./countries.csv

创建数据库blogdb，并导入countries.csv

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
create database blogdb;
\c blogdb;

create table countries (
  id integer primary key generated always as identity,
  name text not null unique,
  alpha_2 char(2) not null,
  alpha_3 char(3) not null,
  numeric_3 char(3) not null,
  iso_3166_2 text not null,
  region text,
  sub_region text,
  intermediate_region text,
  region_code char(3),
  sub_region_code char(3),
  intermediate_region_code char(3)
);

copy countries (
  name,
  alpha_2,
  alpha_3,
  numeric_3,
  iso_3166_2,
  region,
  sub_region,
  intermediate_region,
  region_code,
  sub_region_code,
  intermediate_region_code
)
from '/var/lib/pgsql/data/countries.csv'
delimiter ',' csv header;

-- Check that the data got loaded into the table ok.
select * from countries limit 10;

-- Should say 249.
select count(*) from countries;

使用golang写一个简单的app，查询countries数据，到这里完成!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package main

import (
	"context"
	"fmt"

	"github.com/jackc/pgx/v5"
)

func main() {
	ctx := context.Background()

	conn, err := pgx.Connect(ctx, "postgres://postgres:your_password@192.168.33.10:5432/blogdb?sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer conn.Close(ctx)

	rows, err := conn.Query(ctx, "SELECT id, name FROM countries LIMIT 10")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		err = rows.Scan(&id, &name)
		if err != nil {
			panic(err)
		}
		fmt.Println(id, "-", name)
	}
}

其他

开启ssl

如果想要在本地提前体验ssl加密配置的过程，那么按照下面的流程，先生成self-signed证书、私钥，最后修改配置

1
2
3
4
5
6
7
8
9
10
11
su - postgres

# 进入数据存储目录
cd /var/lib/pgsql/data

# 生成self-signed ssl相关证书、私钥
openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"

# 移除other、group的访问权限
chmod og-rwx server.key

修改postgresql.conf文件，取消下面三行注释，需要保证证书放置在data目录，生产环境如果使用授权ca证书，修改的命令就不是ssl_cert_file以及ssl_key_file，按官网描述修改

1
2
3
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

最后，demo里dsn移除sslmode=disable，然后重新运行验证