Linux环境下安装PostgreSQL

软件安装1年前 (2023)更新 apegeek

808 0 0

基于源代码安装

1. 编译安装

1.1 卸载历史版本

通过如下命令可查看系统中已经安装的历版本信息。

rpm -qa | grep postgresql

执行如下命令，删除历史版本记录。

yum remove postgresql*

完成后，可以find是否有遗留的安装信息，如果有，可以使用rm删除(小心使用rm)。

1.2 下载安装包

点击PostgreSQL (opens new window)下载安装包程序。

1.3 配置安装

将下载的程序包上传至/opt目录下，并解压。

cd /opt

tar -zvxf postgresql-10.5.tar.gz

mv postgresql-10.5 pgsql

cd pgsql

1.3.1 安装依赖

yum -y install -y gcc gcc-c++
yum -y install -y readline-devel
yum -y install -y readline;
yum -y install -y uuid uuid-devel
yum -y install -y perl-ExtUtils-Embed
yum -y install -y openssl openssl-devel
yum -y install -y libxml2 libxml2-devel
yum -y install -y libxslt libxslt-devel
yum -y install -y tcl tcl-devel
yum -y install -y python python-devel
yum -y install -y pam pam-devel

1.3.2 配置

cd /opt/pgsql

./configure --prefix=/opt/pgsql --with-pgport=2435 --with-perl --with-tcl --with-openssl --with-pam  --without-ldap --with-libxml  --with-libxslt  --enable-thread-safety  --with-wal-blocksize=16 --with-uuid=ossp --with-blocksize=16 --enable-dtrace --enable-debug

1.3.3 编译&安装

cd /opt/pgsql

gmake world && gmake install-world

完成后在/opt目录下会出现pgsql安装目录。

2. 用户及目录

2.1 添加用户组

创建名称为postgres的用户组，并将用户postgres添加到改组。

groupadd postgres 

useradd -g postgres postgres

2.2 目录与授权

2.2.1 数据目录

mkdir -p /opt/pgsql/data

数据目录授权将目录授权给用户组postgres中的postgres用户，赋予写权限。

chown postgres:postgres /opt/pgsql/data -R

2.2.2 表空间

mkdir -p /opt/pgsql/tbs/uav

读写权限

chmod 755 /opt/pgsql/tbs/uav

授权用户授权目录写权限给用户组postgres下的postgres用户。

chown postgres:postgres /opt/pgsql/tbs/uav -R

注意：以上操作均使用系统用户root进行操作。

3. 用户配置

切换用户为postgres

su - postgres

配置.bash_profile文件

vi ~/.bash_profile

在文件末尾加入如下内容:

# 安装目录
export PGHOME=/opt/pgsql
# 数据目录
export PGDATA=/opt/pgsql/data
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
alias rm='rm  -i'
alias ll='ls -lh'
# 启动项别名
alias pg_start='pg_ctl start -D $PGDATA'
# 重启服务别名
alias pg_restart='pg_ctl restart -D $PGDATA'
# 关闭项别名
alias pg_stop='pg_ctl stop -D $PGDATA -m fast'

刷新使文件生效

source ~/.bash_profile

4. 初始话&启动服务

4.1 初始化

su postgres

initdb -D /opt/pgsql/data

4.2 启动服务

pg_start

5. 资源创建

5.1 登录数据库

切换系统当前用户为postgres，进入pg命令行。

su postgres

psql

5.2 创建数据库用户

这里创建名为apegeek，密码为apegeek@uav的用户。

create user apegeek password 'apegeek@uav';

5.3 创建表空间

确保表空间目录已经创建，且postgres用户有写权限。如有疑问可通过3.1.4.4了解创建授权过程。
这里创建了名为tbs_uav的表空间，它的所有者为用户apegeek。

create tablespace tbs_uav owner apegeek location '/opt/pgsql/tbs/uav/';

5.4 创建数据库

创建名称为uav的数据库，隶属于表空间uav，所有者为apegeek。

create database uav owner apegeek template template1 tablespace uav;

5.5 用户授权

使用postgres用户登录pg数据库。调整用户apegeek为超级用户。

alter user apegeek with superuser;

将数据库uav所有权限授权给用户apegeek。

grant all privileges on database uav to apegeek;

退出postgres用户。

\q

5.6 配置优化

5.6.1 postgresql.conf

切换为root用户，编辑postgresql.conf

vi /opt/pgsql/data/postgresql.conf

修改配置项
- listen_addresses = ‘*’
- max_connections = 500
- superuser_reserved_connections = 20

5.6.2 pg_hba.conf

切换为root用户，编辑 pg_hba.conf

vi /opt/pgsql/data/pg_hba.conf

在尾部追加如下内容

host  all  all  0.0.0.0/0  md5

5.7 创建Schema

5.7.1 创建

使用已经创建的apegeek用户登录uav数据库。

psql -U apegeek -d uav -h 127.0.0.1 -p 端口

create schema if not exists apegeek authorization apegeek;

以上步骤对于初学者熟悉PG相关操作有一定帮助，实际部署可使用uav-script模块下的initdb脚本快速实现。

5.8.2 修改Search Path

登录目标数据库，执行以下命令：

alter user apegeek set search_path to apegeek;

show search_path;

6. 数据库配置

6.1 postgresql.conf

切换为root用户，编辑postgresql.conf

vi /opt/pgsql/data/postgresql.conf

修改配置项
- listen_addresses = ‘*’
- max_connections = 300
- superuser_reserved_connections = 20

6.2 pg_hba.conf

切换为root用户，pg_hba.conf

vi /opt/pgsql/data/pg_hba.conf

在尾部追加

host all all 0.0.0.0/0 md5

6.3 重启服务

su postgres

pg_restart

7. 基于安装包安装

7.1 安装RPM包

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

7.2 安装PG Server

yum install postgresql10-server

7.3 数据库初始话

/usr/pgsql-10/bin/postgresql-10-setup initdb

7.4 设置开机启动

systemctl enable postgresql-10.service

7.5 重新载入配置

systemctl daemon-reload

7.6 启动服务

systemctl start postgresql-10.service

7.7 登录数据库

su postgres

psql

7.8 修改默认用户密码

alter user postgres password 'apegeekuav';

7.9 修改配置

切换为root用户

su root

postgresql.conf

vi /var/lib/pgsql/10/data/postgresql.conf

修改配置项：

port = 24359
listen_addresses = ‘*’
max_connections = 300
superuser_reserved_connections = 20

pg_hba.conf

vi /var/lib/pgsql/10/data/pg_hba.conf

在文件末尾加入如下内容：

host all all 0.0.0.0/0 md5

7.10 重启服务

systemctl restart postgresql-10.service

7.11 验证配置

su postgres

psql -p 24359

8. 其他

8.1 开启UUID

登录数据库

psql -U apegeek -d uav -h 127.0.0.1 -p 5432

安装扩展

create extension “uuid-ossp” 通过脚本导入函数 psql -d uav -f /opt/pgsql/share/extension/uuid-ossp–1.1.sql

select uuid_generate_v1();
select uuid_generate_v1mc();
select uuid_generate_v4();

出现错误 ERROR: function uuid_generate_v4() does not exist SQL 错误 [42883]: ERROR: function uuid_generate_v4() does not exist 建议：No function matches the given

8.2 命令

8.2.1 查看活动会话

select * from pg_stat_activity where datname = 'apegeek';

8.2.2 杀死指定进程

select pg_terminate_backend('pid');

8.2.3 删除用户

drop user apegeek;

8.2.4 转义字符

针对`符号，需要配置backslash_quote = on

8.3 数据库技巧

8.3.1 自增主键

<selectKey keyProperty="id" resultType="int" order="BEFORE">
    SELECT nextval('t_job_qrtz_trigger_group_id_seq'::regclass) as id
</selectKey>

#8.4 PostGis

8.4.1 安装工具包

yum install wget net-tools epel-release -y

8.4.2 安装PostGis

yum install postgis25_10 postgis25_10-client -y

8.4.3 安装扩展工具

yum install ogr_fdw10 -y

8.4.4 在数据库中启用PostGis

psql -d postgres -c "CREATE EXTENSION postgis;"

psql -d postgres -c "CREATE EXTENSION postgis_topology;"

psql -d postgres -c "CREATE EXTENSION postgis_sfcgal;"

psql -d postgres -c "CREATE EXTENSION fuzzystrmatch;"

psql -d postgres -c "CREATE EXTENSION postgis_tiger_geocoder;"

psql -d yourdatabase -c "CREATE EXTENSION address_standardizer;"

8.5 监控模块

pg_stat_statements模块提供了pg的监控能力。

8.5.1 编译

cd /opt/psql/contrib

make

make install

8.5.2 安装插件

以postgres用户登录psql客户端。

create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();

8.5.3 配置

vi /opt/pgsql/data/postgresql.conf

在顶部加入如下内容后，重启数据服务。

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

8.5.4 SQL性能分析

8.5.4.1 最耗时的SQL

select * from pg_stat_statements order by total_time desc limit 5;

8.5.4.2 查询读取Buffer次数最多的SQL

select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

8.5.44.3 查出使用表扫描最多的表

select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;

8.5.4.4 查询当前正在运行的访问到上述表的慢查询

select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';

8.5.5 处理慢SQL

对于上面的方法查出来的慢SQL，首先需要做的可能是Cancel或Kill掉他们，使业务先恢复。

select pg_cancel_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();

select pg_terminate_backend(pid) from pg_stat_activity where  query like '%<query text>%' and pid != pg_backend_pid();

# 软件安装

文章版权归作者所有，未经允许请勿转载。

基于源代码安装Nginx

Dai, Geek

672 0

Linux环境安装JDK

Dai, Geek

703 0

Redis 安装

Dai, Geek

822 0

基于Nginx Rtmp流媒体服务器搭建

Dai, Geek

735 0

FTP文件服务器搭建

Dai, Geek

808 0

暂无评论

暂无评论...

Linux环境下安装PostgreSQL

1. 编译安装

1.1 卸载历史版本

1.2 下载安装包

1.3 配置安装

1.3.1 安装依赖

1.3.2 配置

1.3.3 编译&安装

2. 用户及目录

2.1 添加用户组

2.2 目录与授权

2.2.1 数据目录

2.2.2 表空间

3. 用户配置

4. 初始话&启动服务

4.1 初始化

4.2 启动服务

5. 资源创建

5.1 登录数据库

5.2 创建数据库用户

5.3 创建表空间

5.4 创建数据库

5.5 用户授权

5.6 配置优化

5.6.1 postgresql.conf

5.6.2 pg_hba.conf

5.7 创建Schema

5.7.1 创建

5.8.2 修改Search Path

6. 数据库配置

6.1 postgresql.conf

6.2 pg_hba.conf

6.3 重启服务

7. 基于安装包安装

7.1 安装RPM包

7.2 安装PG Server

7.3 数据库初始话

7.4 设置开机启动

7.5 重新载入配置

7.6 启动服务

7.7 登录数据库

7.8 修改默认用户密码

7.9 修改配置

7.10 重启服务

7.11 验证配置

8. 其他

8.1 开启UUID

8.2 命令

8.2.1 查看活动会话

8.2.2 杀死指定进程

8.2.3 删除用户

8.2.4 转义字符

8.3 数据库技巧

8.3.1 自增主键

#8.4 PostGis

8.4.1 安装工具包

8.4.2 安装PostGis

8.4.3 安装扩展工具

8.4.4 在数据库中启用PostGis

8.5 监控模块

8.5.1 编译

8.5.2 安装插件

8.5.3 配置

8.5.4 SQL性能分析

8.5.4.1 最耗时的SQL

8.5.4.2 查询读取Buffer次数最多的SQL

8.5.44.3 查出使用表扫描最多的表

8.5.4.4 查询当前正在运行的访问到上述表的慢查询

8.5.5 处理慢SQL

FTP文件服务器搭建

基于Nginx Rtmp流媒体服务器搭建

相关文章

暂无评论

相关文章

热门标签