Postgresql安装及性能测试

postgresql安装及性能测试

1. Postgresql介绍

Postgresql是一款功能强大的开源对象关系型数据库管理系统(ORDBMS),以其稳定性、扩展性和标准的SQL支持而闻名。它支持复杂查询、外键、触发器、视图、事务完整性、多版本并发控制(MVCC)等特性,且具有丰富的扩展能力,可以通过插件扩展其功能。Postgresql适用于各种场景,从小型应用到大规模企业级应用。

2. Postgresql安装

Postgresql可以安装在多种操作系统上,包括Linux、macOS和Windows。以下是不同系统的安装步骤:

2.1 在Linux上安装Postgresql(以Ubuntu为例)

  1. 更新包列表并安装依赖
sudo apt-get update
sudo apt-get install wget ca-certificates
  1. 添加Postgresql仓库
wget -qO - https://www.Postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.Postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
  1. 更新包列表并安装Postgresql
sudo apt-get update
sudo apt-get install Postgresql Postgresql-contrib
  1. 启动Postgresql服务
sudo systemctl start Postgresql
  1. 设置Postgresql在系统启动时自动启动
sudo systemctl enable Postgresql

2.2 数据库管理操作

  1. 查看数据库版本:
test@ubuntu-svr:~$ sudo -u postgres psql -c "SELECT version();"
                                                                version
----------------------------------------------------------------------------------------------------------------------------------------
 Postgresql 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
  1. 登录与登出数据库
test@ubuntu-svr:~$ sudo su postgres     # 切换到postgres用户
postgres@ubuntu-svr:/home/test$ psql    # 通过psql工具进入数据库
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.
postgres=# \q     # 退出数据库
  1. 查看数据库与用户
test@ubuntu-svr:~$ sudo su postgres     # 切换到postgres用户
postgres@ubuntu-svr:/home/test$ psql    # 通过psql工具进入数据库
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# \l # 查看数据库列表,默认有三个
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------±---------±---------±----------------±------------±------------±-----------±----------±----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)

postgres=# \du # 查看数据库用户
List of roles
Role name | Attributes
-----------±-----------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS

  1. 创建数据库用户并授权
# 创建用户并指定权限
postgres=# CREATE USER test WITH PASSWORD '123456';
CREATE ROLE
postgres=# ALTER USER test WITH SUPERUSER;
ALTER ROLE

删除用户

postgres=# DROP USER xxx;

  1. 查看数据库配置文件路径
postgres@ubuntu-svr:~$ psql
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# show hba_file;
hba_file

/etc/postgresql/14/main/pg_hba.conf
(1 row)

postgres=# show config_file;
config_file

/etc/postgresql/14/main/postgresql.conf
(1 row)

  1. 使用新创建用户登录
# pg数据库默认连接的认证方式是peer。在postgres用户下无法使用test数据库用户连接
postgres@ubuntu-svr:/home/test$ psql -U test -d postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "test"
# 在test系统用户下使用test用户连接数据库
test@ubuntu-svr:~$ psql -U test -d postgres
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.

postgres=#

说明:

Peer认证方法的工作原理是:从内核中获取客户的操作系统用户名,并将其作为允许的数据库用户名(可选择用户名映射)。这种方法只支持本地连接。

  1. 修改认证方式

修改配置文件改为,将认证方式改为md5认证:

# 修改第二条local配置中的method为md5
test@ubuntu-svr:~$ sudo vim /etc/postgresql/14/main/pg_hba.conf
...
# Database administrative login by Unix domain socket
local   all             postgres                                peer

TYPE DATABASE USER ADDRESS METHOD

“local” is for Unix domain socket connections only

local all all md5

重启数据库

test@ubuntu-svr:~$ sudo systemctl restart postgresql

再次在postgres系统用户下使用test数据库用户连接成功。

test@ubuntu-svr:~ sudo su postgres postgres@ubuntu-svr:/home/test psql -U test -d postgres
Password for user test:
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type “help” for help.

postgres=#

  1. 配置远程连接
# 修改pg_hba.conf在最后位置增加如下配置,允许所有远程主机的数据库用户使用md5认证进行连接。
test@ubuntu-svr:~$ sudo vim /etc/postgresql/14/main/pg_hba.conf
...
# Allow all hosts connections via md5:
host    all             all             0.0.0.0/0               md5
...

修改postgresql.conf,修改listen_addresses为*,并取消注释。port和最大连接数根据需求进行自定义。

test@ubuntu-svr:~$ sudo vim /etc/postgresql/14/main/postgresql.conf

60 listen_addresses = ‘’ # what IP address(es) to listen on;
61 # comma-separated list of addresses;
62 # defaults to ‘localhost’; use '
’ for all
63 # (change requires restart)
64 port = 5432 # (change requires restart)
65 max_connections = 100 # (change requires restart)

重启数据库

test@ubuntu-svr:~$ sudo systemctl restart postgresql

在另外一台机器上测试连接。确认已经关闭数据库所在机器的防火墙或者放行规则。

[root@localhost test]# psql -h 192.168.226.128 -p 5432 -U test -d postgres
Password for user test:
psql (9.2.24, server 14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
WARNING: psql version 9.2, server version 14.0.
Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type “help” for help.

2.2 在macOS上安装Postgresql

  1. 使用Homebrew安装
brew update
brew install Postgresql
  1. 启动Postgresql服务
brew services start Postgresql

2.3 在Windows上安装Postgresql

  1. 下载Postgresql:从Postgresql官方网站下载Windows版的Postgresql安装包。

  2. 运行安装程序:按照安装向导的指示进行安装,选择默认设置即可。

  3. 初始化数据库集群:安装程序会自动初始化数据库集群并启动Postgresql服务。

3. Postgresql性能测试方案

为了测试Postgresql的性能,可以使用内置的基准测试工具pgbench,或者第三方工具如sysbench

3.1 使用pgbench进行测试

pgbench是Postgresql自带的基准测试工具,可以模拟多种负载来测试数据库性能。

  1. 初始化测试数据库
test@ubuntu-svr:~$ sudo -u postgres createdb pgbench
test@ubuntu-svr:~$ sudo -u postgres pgbench -i -s 10 pgbench
dropping old tables...
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 0.47 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.85 s (drop tables 0.02 s, create tables 0.01 s, client-side generate 0.48 s, vacuum 0.18 s, primary keys 0.17 s).

这个命令会创建一个名为pgbench的测试数据库,并初始化测试数据,-s 10表示生成10倍于默认规模的数据。

  1. 运行基准测试
test@ubuntu-svr:~$ sudo -u postgres pgbench -c 10 -j 2 -T 60 pgbench
pgbench (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type: 
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
duration: 60 s
number of transactions actually processed: 311888
latency average = 1.924 ms
initial connection time = 9.363 ms
tps = 5198.015078 (without initial connection time)

这个命令会以10个并发连接、2个线程运行测试,持续时间为60秒。

  1. 查看测试结果pgbench会输出每秒事务数(tps)等性能指标。

3.2 使用sysbench进行测试

sysbench是一个多线程基准测试工具,支持多种数据库的性能测试,包括Postgresql。

  1. 安装sysbench
sudo apt-get install sysbench
  1. 准备测试数据
# 创建sysbench测试库
test@ubuntu-svr:~$ sudo -u postgres createdb testdb
# 生成测试数据,根据环境替换pgsql用户名和密码
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-user=test --pgsql-password=123456 --pgsql-db=testdb --tables=10 --table-size=1000000 /usr/share/sysbench/oltp_read_write.lua prepare

这个命令会在名为testdb的数据库中创建10个表,每个表包含1,000,000行数据。

  1. 运行基准测试
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-user=test --pgsql-password=123456 --pgsql-db=testdb --tables=10 --table-size=1000000 --threads=10 --time=60 /usr/share/sysbench/oltp_read_write.lua run

这个命令会以10个线程运行读写测试,持续时间为60秒。测试结果如下:

sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 10
Initializing random number generator from current time

Initializing worker threads…

Threads started!

SQL statistics:
queries performed:
read: 1190546
write: 340154
other: 170080
total: 1700780
transactions: 85039 (1417.01 per sec.)
queries: 1700780 (28340.26 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 60.0121s
total number of events: 85039

Latency (ms):
min: 1.05
avg: 7.05
max: 63.49
95th percentile: 13.46
sum: 599901.95

Threads fairness:
events (avg/stddev): 8503.9000/116.42
execution time (avg/stddev): 59.9902/0.00

  1. 清理测试数据
sysbench --db-driver=pgsql --pgsql-host=localhost --pgsql-user=test --pgsql-password=123456 --pgsql-db=testdb --tables=10 --table-size=1000000 --threads=10 --time=60 /usr/share/sysbench/oltp_read_write.lua cleanup

4. 优化Postgresql性能的建议

  1. 硬件优化:使用SSD存储、增加内存和高性能CPU以提升I/O和计算能力。

  2. 配置优化:调整Postgresql配置文件Postgresql.conf中的参数,如shared_bufferswork_memmaintenance_work_memeffective_cache_sizecheckpoint_segments等。

  3. 索引优化:创建合适的索引以加快查询速度,避免过多的索引影响写性能。

  4. 查询优化:使用EXPLAIN分析查询计划,优化SQL查询以减少不必要的开销。

  5. 连接池:使用连接池(如PgBouncer)来减少连接创建和销毁的开销,提高并发处理能力。

  6. 分区:对于大表,可以使用表分区来提高查询性能和管理效率。

  7. VACUUM和ANALYZE:定期运行VACUUM和ANALYZE命令以维护数据库统计信息和清理垃圾数据,提高查询性能。

通过合理的配置和优化,可以显著提升Postgresql的性能,满足高并发、低延迟的数据处理需求。

本文由mdnice多平台发布


这是一个从 https://juejin.cn/post/7369117524481507378 下的原始话题分离的讨论话题