环境:
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/
看情况开启checksums
./initdb -D /opt/pg13/data/ –data-checksums
6.修改配置文件
[postgres@host134 data]$ cd /opt/pg13/data
[postgres@host134 data]$ vi postgresql.conf
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 shared_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_line_prefix='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h' wal_log_hints=on
full_page_writes=on
需要创建归档目录
[postgres@host134 data]$ mkdir -p /opt/pg13/archivelog
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 -h localhost -U postgres -p 5432 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 -h localhost -U postgres -p 5432 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
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之前的归档
这里只能删除archivelog目录下的符合条件的归档,若该目录下还有子目录,比如安装日期创建文件夹的目录 ，是需要指定日期目录才能删除的，比如:
pg_archivecleanup -d /opt/pg13/archivelog/20221020 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;
pg参数建议生成工具
https://pgtune.leopard.in.ua/#/
https://zhuanlan.zhihu.com/p/333201734
16.创建只读账号
使用超级账号登录创建只读用户
create user hxlread with password ‘postgres’;
使用数据库属主的账号登录并赋予权限
psql -h 192.168.1.134 -U hxl -d db_test
grant select on tb_test to hxlread;
使用只读账号登录
psql -h 192.168.1.134 -U hxlread -d db_test
###############安装常用的扩展######################################################
1.安装postgres_fdw扩展
进入到解压后的源码目录
[root@host134 postgres_fdw]#cd /soft/postgresql/postgresql-13.8/contrib/postgres_fdw
[root@host134 postgres_fdw]# make
[root@host134 postgres_fdw]# make install
安装好后会在extension有对应的文件,如:postgres_fdw.control
[root@host134 extension]# pwd
/opt/pg13/share/postgresql/extension
[root@host134 extension]# ls -al
total 20
drwx—— 2 postgres postgres 110 Oct 27 10:30 .
drwx—— 6 postgres postgres 4096 Oct 21 11:24 ..
-rw-r–r– 1 postgres postgres 658 Oct 21 11:24 plpgsql–1.0.sql
-rw-r–r– 1 postgres postgres 193 Oct 21 11:24 plpgsql.control
-rw-r–r– 1 root root 507 Oct 27 10:30 postgres_fdw–1.0.sql
-rw-r–r– 1 root root 172 Oct 27 10:30 postgres_fdw.control
执行:
postgres=# create extension postgres_fdw;
CREATE EXTENSION
2.安装dblink扩展
[root@host134 dblink]# cd /soft/postgresql/postgresql-13.8/contrib/dblink
[root@host134 dblink]# make
[root@host134 dblink]# make install
3.修改权限目录
[root@host134 extension]# ls -al
total 40
drwx—— 2 postgres postgres 211 Oct 27 10:37 .
drwx—— 6 postgres postgres 4096 Oct 21 11:24 ..
-rw-r–r– 1 root root 419 Oct 27 10:37 dblink–1.0–1.1.sql
-rw-r–r– 1 root root 2832 Oct 27 10:37 dblink–1.1–1.2.sql
-rw-r–r– 1 root root 6645 Oct 27 10:37 dblink–1.2.sql
-rw-r–r– 1 root root 170 Oct 27 10:37 dblink.control
-rw-r–r– 1 postgres postgres 658 Oct 21 11:24 plpgsql–1.0.sql
-rw-r–r– 1 postgres postgres 193 Oct 21 11:24 plpgsql.control
-rw-r–r– 1 root root 507 Oct 27 10:30 postgres_fdw–1.0.sql
-rw-r–r– 1 root root 172 Oct 27 10:30 postgres_fdw.control
可以看到刚才安装的扩展权限都是root的,需要修改成postgres
[root@host134 postgresql]# chown -R postgres:postgres ./extension/
今天的文章源码独立部署是什么意思_源码部署分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/52901.html
