postgresql13源码安装部署

标签：opt postgres 部署 pg13 host134 postgresql13 ## 源码 log

环境:
Os:Centos 7
DB:13.8

1.下载安装介质
下载地址:
源码下载地址: https://www.postgresql.org/ftp/source/
二进制下载地址(新版本的没有二进制下载,只能使用源码安装):https://www.enterprisedb.com/download-postgresql-binaries
我这里下载的介质是:
postgresql-13.8.tar.gz

2.安装依赖包
yum install readline
yum install gcc
yum -y install -y readline-devel
yum install zlib-devel

3.源码安装
[root@host134 soft]# tar -xvf postgresql-13.8.tar.gz
[root@host134 soft]# cd postgresql-13.8
[root@host134 soft]#mkdir -p /opt/pg13 ##这里是指定安装目录
[root@host134 soft]#./configure --prefix=/opt/pg13
[root@host134 soft]#make
[root@host134 soft]#make install

4.创建相应的用户
[root@localhost opt]# groupadd postgres
[root@localhost opt]# useradd -g postgres postgres

5.创建数据及日志目录，并做相应授权
[root@localhost soft]#mkdir -p /opt/pg13/{data,log}
[root@localhost soft]#chown -R postgres:postgres /opt/pg13

6.初始化数据库
#su - postgres
[postgres@host134 /]$ cd /opt/pg13/bin
[postgres@host134 bin]$ ./initdb -D /opt/pg13/data/

7.启动数据库
[postgres@host134 bin]$ cd /opt/pg13/bin
[postgres@localhost bin]$./pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

8.登陆使用

[postgres@host134 bin]$cd /opt/pg13/bin
[postgres@host134 bin]$ ./psql
psql (13.8)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

9.设置环境变量

su - postgres
[postgres@localhost ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/pg13/bin

export PATH

10.修改postgres用户的访问密码并测试建库建表
PostgreSQL 数据库默认会创建一个postgres的数据库用户作为数据库的管理员，默认密码为空，我们需要修改为指定的密码，这里设定为postgres.

su - postgres
[postgres@host134 ~]$ psql
psql (13.8)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE
postgres=# select * from pg_shadow ;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | md53175bce1d3201d16594cebf9d7eb3f9d |          | 
(1 row)

创建用户
postgres=# create user hxl with password 'postgres';
CREATE ROLE

创建数据库
create database db_test encoding = 'utf8' owner hxl; -- 创建数据库指定字符集和属主

将数据库得权限,全部赋给某个用户
grant all on database db_test to hxl;

11.配置postgresql允许远程访问
只需要修改data目录下的pg_hba.conf和postgresql.conf这两个文件:
pg_hba.conf: 配置对数据库的访问权限;
postgresql.conf: 配置PostgreSQL数据库服务器的相应的参数

su - postgres
vi /opt/pg13/data/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5

重新加载配置文件
su - postgres
[postgres@host134 ~]$ pg_ctl -D /opt/pg13/data reload
server signaled

修改postgresql.conf
vi /opt/pg13/data/postgresql.conf

listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 1000 ##修改链接数默认是100
修改该改参数需要重启动

pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

然后通过navicate for postgresql 即可进行连接

12.创建表并写入数据
登录提示输入密码

[postgres@host134 ~]$ psql -h 192.168.1.134 -U hxl -d db_test
Password for user hxl: 
psql (13.8)
Type "help" for help.


create table tb_test
(
id bigserial primary key not null,
name varchar(20),
createtime timestamp default current_timestamp,
modifytime timestamp default current_timestamp
);

insert into tb_test(name) values('name1');
insert into tb_test(name) values('name2');
insert into tb_test(name) values('name3');
insert into tb_test(name) values('name4');
insert into tb_test(name) values('name5');
db_test=> select * from tb_test;
 id | name  |         createtime         |         modifytime         
----+-------+----------------------------+----------------------------
  1 | name1 | 2022-10-18 11:32:33.649901 | 2022-10-18 11:32:33.649901
  2 | name2 | 2022-10-18 11:32:33.665863 | 2022-10-18 11:32:33.665863
  3 | name3 | 2022-10-18 11:32:33.691182 | 2022-10-18 11:32:33.691182
  4 | name4 | 2022-10-18 11:32:33.771843 | 2022-10-18 11:32:33.771843
  5 | name5 | 2022-10-18 11:32:34.496502 | 2022-10-18 11:32:34.496502
(5 rows)

13.开启归档模式
查看当前是在归档模式
[postgres@host134 ~]$ psql -h 192.168.1.134 -U postgres
Password for user postgres:
psql (13.8)
Type "help" for help.

postgres=# show archive_mode;
archive_mode
--------------
off
(1 row)

可以看到当前没有在归档模式

创建归档目录
su - postgres
mkdir -p /opt/pg13/archivelog

vi /opt/pg13/data/postgresql.conf

wal_level = replica
archive_mode = on
##archive_command = 'cp %p /opt/pg13/archivelog/%f' ##pa_wal目录的归档日志会拷贝到该目录
archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg13/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f' #按照日期目录归档,wal日志会自动拷贝到该目录

需要重新启动
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log stop
pg_ctl -D /opt/pg13/data -l /opt/pg13/log/postgres.log start

查看归档情况
select pg_switch_wal();

归档切换
select pg_switch_wal();
select pg_switch_wal();

[postgres@host134 archivelog]$ ls
000000010000000000000006 000000010000000000000007 000000010000000000000008 000000010000000000000009 20221018
这个时候会看到自动创建了日志目录,日期目录有相应的归档日志
[postgres@host134 20221018]$ ls
00000001000000000000000A 00000001000000000000000B 00000001000000000000000C

删除归档
su - postgres
[postgres@host134 pg_wal]$ pg_controldata /opt/pg13/data ##可以查看当前使用的wal Latest checkpoint's REDO WAL file: 00000001000000000000000A
[postgres@host134 pg_wal]$ pg_archivecleanup -d /opt/pg13/archivelog 00000001000000000000000A ##删除00000001000000000000000A之前的归档

14.设置开机自启动
请参考:
https://www.cnblogs.com/hxlasky/p/16802748.html

15.几个重要参数
show shared_buffers; ##建议值是设置成硬件内存的25%,不超过内存的50%
show work_mem; ##根据链接数生成配置
show wal_buffers; ##推荐值16MB
show effective_cache_size; ##推荐内存的1/2
show max_connections;
show maintenance_work_mem; ##推荐值4096MB
show fsync;
show wal_sync_method;
show synchronous_commit; ##默认为on
show default_statistics_target; ##默认100
show maintenance_work_mem;
show checkpoint_completion_target;
show effective_io_concurrency;
show min_wal_size;
show max_wal_size;
show max_worker_processes;
show max_parallel_workers;
show random_page_cost;
show log_min_duration_statement;

参数配置例子

listen_addresses='*'
port=5432
max_connections=1000

####内存相关#########
work_mem=10MB
maintenance_work_mem=2GB
max_locks_per_transaction=1024
max_wal_size=64GB
checkpoint_timeout=30min
checkpoint_completion_target=0.9
hared_buffers=RAM*0.25GB             #RAM指内存,最大设置为64GB
effective_cache_size=RAM*0.5GB    #RAM指内存(free -g)

##归档部分
wal_level=replica
archive_mode=on
archive_command = 'DATE=`date +%Y%m%d`;DIR="/opt/pg13/archivelog/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f'

######慢查询部分#####################
logging_collector=on
log_directory = '/opt/pg13/log'                  ##指定具体目录,否则会默认在pgdata目录下创建log目录
log_destination='stderr'
log_min_duration_statement=1000                  ##超过1秒的慢查询都会记录
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = off                   ##是否覆盖
log_rotation_age = 1d                            ##每天生成
log_rotation_size = 10MB                         ##每个日志大小
#log_statement = all 和 log_min_duration_statement = 5000   
#根据需要两者设置其一即可
#如果两个都进行了配置默认所有SQL全部打印，log_min_duration_statement设置失效
##log_statement = all                              #需设置跟踪所有语句，否则只能跟踪出错信息
log_min_duration_statement = 1000                  #milliseconds,记录执行5秒及以上的语句
log_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h'

pg参数建议生成工具
https://pgtune.leopard.in.ua/#/
https://zhuanlan.zhihu.com/p/333201734

标签：opt,postgres,部署,pg13,host134,postgresql13,##,源码,log
From： https://www.cnblogs.com/hxlasky/p/16804949.html

postgresql13源码安装部署

相关文章

赞助商

阅读排行