postgresql安装及性能测试postgresql安装及性能测试 1. Postgresql介绍 Postgresq

postgresql安装及性能测试

1. Postgresql介绍

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

2. Postgresql安装

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

2.1 在Linux上安装Postgresql（以Ubuntu为例）

更新包列表并安装依赖：

sudo apt-get update sudo apt-get install wget ca-certificates
添加Postgresql仓库：

wget -qO - www.Postgresql.org/media/keys/… | sudo apt-key add - echo "deb apt.Postgresql.org/pub/repos/a… $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
更新包列表并安装Postgresql：

sudo apt-get update sudo apt-get install Postgresql Postgresql-contrib
启动Postgresql服务：

sudo systemctl start Postgresql
设置Postgresql在系统启动时自动启动：

sudo systemctl enable Postgresql

2.2 数据库管理操作

查看数据库版本:

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)
登录与登出数据库

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 # 退出数据库
查看数据库与用户

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
创建数据库用户并授权

创建用户并指定权限

postgres=# CREATE USER test WITH PASSWORD '123456'; CREATE ROLE postgres=# ALTER USER test WITH SUPERUSER; ALTER ROLE

删除用户

postgres=# DROP USER xxx;
查看数据库配置文件路径

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)
使用新创建用户登录

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认证方法的工作原理是：从内核中获取客户的操作系统用户名，并将其作为允许的数据库用户名（可选择用户名映射）。这种方法只支持本地连接。

修改认证方式

修改配置文件改为，将认证方式改为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=#

配置远程连接

修改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

使用Homebrew安装：

brew update brew install Postgresql
启动Postgresql服务：

brew services start Postgresql

2.3 在Windows上安装Postgresql

下载Postgresql：从Postgresql官方网站下载Windows版的Postgresql安装包。
运行安装程序：按照安装向导的指示进行安装，选择默认设置即可。
初始化数据库集群：安装程序会自动初始化数据库集群并启动Postgresql服务。

3. Postgresql性能测试方案

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

3.1 使用pgbench进行测试

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

初始化测试数据库：

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倍于默认规模的数据。

运行基准测试：

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: <builtin: TPC-B (sort of)> 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秒。

查看测试结果：pgbench会输出每秒事务数（tps）等性能指标。

3.2 使用sysbench进行测试

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

安装sysbench：

sudo apt-get install sysbench
准备测试数据：

创建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行数据。

运行基准测试：

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

清理测试数据

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性能的建议

硬件优化：使用SSD存储、增加内存和高性能CPU以提升I/O和计算能力。
配置优化：调整Postgresql配置文件Postgresql.conf中的参数，如shared_buffers、work_mem、maintenance_work_mem、effective_cache_size、checkpoint_segments等。
索引优化：创建合适的索引以加快查询速度，避免过多的索引影响写性能。
查询优化：使用EXPLAIN分析查询计划，优化SQL查询以减少不必要的开销。
连接池：使用连接池（如PgBouncer）来减少连接创建和销毁的开销，提高并发处理能力。
分区：对于大表，可以使用表分区来提高查询性能和管理效率。
VACUUM和ANALYZE：定期运行VACUUM和ANALYZE命令以维护数据库统计信息和清理垃圾数据，提高查询性能。

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

postgresql安装及性能测试