服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - postgresql 12版本搭建及主备部署操作

postgresql 12版本搭建及主备部署操作

2021-03-25 21:03小胡豆 PostgreSQL

这篇文章主要介绍了postgresql 12版本搭建及主备部署操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

postgresql 12版本主备部署

环境搭建

centos 7+

postgresql 12.0

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# 网络检查
ping -c2 baidu.com
#关闭防火墙，selinux
systemctl stop firewalld && sudo systemctl disable firewalld
sed -ri s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config
setenforce 0
### 配置阿里云yum源
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak
curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
#echo "isntalling tools"
yum -y install vim net-tools wget ntpdate
yum -y groupinstall "Development Tools"
# 时间同步
echo "*/10 * * * * /usr/sbin/ntpdate ntp.aliyun.com > /dev/null 2>&1" >>/etc/crontab
# 加快ssh连接
sed -ri s/"#UseDNS yes"/"UseDNS no"/g /etc/ssh/sshd_config
systemctl restart sshd
## 环境准备，根据自身需要，减少或者增加
yum install -y wget gcc gcc-c++ epel-release llvm5.0 llvm5.0-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel
## 下载、解压，安装
cd /usr/local/src
wget https://ftp.postgresql.org/pub/source/v12.0/postgresql-12.0.tar.gz
tar -xvzf postgresql-12.tar.gz
cd postgresql-12
./configure --prefix=/usr/local/pgsql/12 --enable-nls --with-python --with-tcl --with-gssapi --with-icu --with-openssl --with-pam --with-ldap --with-systemd --with-libxml --with-libxslt
make && make install
## 创建目录和用户,以及配置环境变量
groupadd postgres
useradd -g postgres postgres
## 密码设置在引号内输入自己的密码
echo "postgres" | passwd --stdin postgres
mkdir -p /data/pgdata/{data,archivedir}
chown -R postgres:postgres /usr/local/pgsql -R
chown postgres:postgres /data/pgdata -R
echo "export PATH=/usr/local/pgsql/12/bin:$PATH" >/etc/profile.d/pgsql.sh
source /etc/profile.d/pgsql.sh

环境部署已经完成,接下来我们来搭建postgresql的主备

主备搭建

 

备库搭建

以下的操作均在postgres用户下进行

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
su - postgres
##初始化
initdb -D /data/pgdata/data -U postgres --locale=en_US.UTF8 -E UTF8
cp /data/pgdata/data/{pg_hba.conf,pg_hba.conf.bak}
## 允许repl用户进行流复制
cat <<-eof >>/data/pgdata/data/pg_hba.conf
host all    all    0.0.0.0/0    md5
host replication  repl   192.168.0.0/22   trust
eof
cp /data/pgdata/data/{postgresql.conf,postgresql.conf.bak}
##配置postgresql.conf
cat <<-eof >/data/pgdata/data/postgresql.conf
listen_addresses = '*'     
port = 5432
max_connections = 100
superuser_reserved_connections = 10
full_page_writes = on
wal_log_hints = off
max_wal_senders = 50
hot_standby = on
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S'
log_rotation_age = 1d
log_rotation_size = 10MB
log_statement = 'mod'
log_timezone = 'PRC'
timezone = 'PRC'
unix_socket_directories = '/tmp'
shared_buffers = 512MB
temp_buffers = 16MB
work_mem = 32MB
effective_cache_size = 2GB
maintenance_work_mem = 128MB
#max_stack_depth = 2MB
dynamic_shared_memory_type = posix
## PITR
full_page_writes = on
wal_buffers = 16MB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f'
archive_timeout = 60s
eof
## 启动数据库
pg_ctl -D /data/pgdata/data -l /data/pgdata/data/serverlog start
## 创建用户进行流复制连接
psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'postgres';"
psql -U postgres -c "CREATE USER repl WITH PASSWORD 'postgres' REPLICATION;"

备库搭建

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
## 备份复制，备库配置，
pg_basebackup -h 192.168.3.62 -p 5432 -U repl -w -Fp -Xs -Pv -R -D /data/pgdata/data
根据主库的postgresql.conf 配置文件 修改参数配置:
1, listen_addresses = '* '
2, wal_level = hot_standby
3, max_connections = xxx, # 一般比主库大一些.
4,hot_standby = on
5, max_standby_streaming_delay = 30s
6. wal_receiver_status_interval = 10s
7, hot_standby_feedback = on # 如果有错误的数据复制,是否向 主进行范例.
### 在postgre.auto.conf 添加 application_name =slave1,配置如下
primary_conninfo = 'application_name=slave1 user=repl passfile=''/home/postgres/.pgpass'' host=192.168.3.62 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
## 启动备库
pg_ctl -D /data/pgdata/data start

写在最后（状态检查）

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
##状态检查
### 回到主库查看状态
$ psql -c "select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication;"
 client_addr | usename |   backend_start   | application_name | sync_state | sync_priority
--------------+---------+-------------------------------+------------------+------------+---------------
 192.168.3.61 | repl | 2019-10-16 15:34:45.130732+08 | slave1   | async  |    0
(1 row)
$ psql -c "select client_addr, sync_state from pg_stat_replication;"
 client_addr | sync_state
--------------+------------
 192.168.3.61 | async
(1 row)
##备库只读, 所以任何操作, 都会告警失败:
postgres=# drop database pgtest;
ERROR: cannot execute DROP DATABASE in a read-only transaction
## 查看主备的状态
#主
ps -ef |grep sender
postgres 92996 51692 0 15:34 ?  00:00:00 postgres: walsender repl 192.168.3.61(53958) streaming 0/8000060
postgres 101845 20470 0 15:45 pts/2 00:00:00 grep --color=auto sender
#备
ps -ef |grep recover
postgres 98311 98309 0 15:34 ?  00:00:00 postgres: startup recovering 000000010000000000000008
postgres 108029 21054 0 15:46 pts/2 00:00:00 grep --color=auto recover
#通过此命令判断主备
pg_controldata -D /data/pgdata/data/ |grep state
in archive recovery ## 表示从库
in production  ## 表示主库

补充：PostgreSQL 12：新功能和增强功能

介绍

PostgreSQL 12是世界上最受欢迎和功能最丰富的开源数据库的下一个主要发行版。PostgreSQL 12的稳定版本计划于2019年末发布。版本12的Beta 4已于2019年9月12日发布。

本文将引导您完成最重要的新功能的摘要。PostgreSQL 12中的增强功能和重大更改。

新功能和增强功能

 

CTE增强

在PostgreSQL中，CTE是优化屏障（外部查询限制不传递给CTE），数据库在CTE内部评估查询，并缓存结果（即具体化的结果）和外部where子句，这意味着在处理外部查询时，要么执行完整表扫描，要么执行完整索引查找。对于大表来说，这会导致糟糕的性能。

?
1
2
WITH AllUsers AS (SELECT * FROM Users)
SELECT * FROM AllUsers WHERE Id = 100;

为了克服这个问题，您可以在PostgreSQL中将该查询重写为：

?
1
2
WITH UserRecord AS (SELECT * FROM Users WHERE Id = 100)
SELECT * FROM UserRecord;

 

PostgreSQL 12通过引入查询优化器提示来解决此问题，以使我们能够控制是否应实现CTE：MATERIALIZED，NOT MATERIALIZED。因此，可以将查询重写如下，以获得更好的性能。

?
1
2
WITH AllUsers AS NOT MATERIALIZED (SELECT * FROM Users)
SELECT * FROM AllUsers WHERE Id = 100;

这有效地内联了CTE表达式，并显着提高了性能。

生成列

SQL Server和Oracle长期以来都有计算列，而PostgreSQL中缺少计算列使从其他数据库的迁移变得非常困难。PostgreSQL 12引入了生成列(Generated Columns)来解决这个问题。

生成列对于列就像视图对于表一样。该列的值始终是根据表中的其他列计算的。生成的列可以是虚拟的或存储的。虚拟列的值是在查询期间动态计算的，它们不会占用存储空间。已存储的列的值已预先计算并存储为表数据的一部分。PostgreSQL当前仅实现存储的生成列。

GENERATED ALWAYS AS子句用于创建生成的列

?
1
2
3
4
5
CREATE TABLE Employee (
 ...,
 dob timestamp,
 age integer GENERATED ALWAYS AS (date_part('year', CURRENT_TIME - dob)) STORED
);

在PostgreSQL 11.x和更早的版本中，唯一的方法是将age声明为普通列并编写触发器以在插入和更新期间填充该列

?
1
2
3
4
5
6
CREATE OR REPLACE FUNCTION calc_age() RETURNS TRIGGER AS $body$
BEGIN
 NEW.age := date_part('year', CURRENT_TIME - NEW.dob::timestamp);
 RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

计算列的SQL Server语法

将此与SQL Server语法进行对比。SQL Server中没有特殊的关键字来声明计算列。您只需在AS子句后指定使我们成为计算列的表达式。

?
1
2
3
4
5
6
-- SQL Server Syntax
CREATE TABLE Employee (
 ...,
 dob datetime,
 age AS DATEDIFF(year,dob,GETDATE()) PERSISTED
);

请注意PERSISTED 子句与PostgreSQL的STORED 子句等效。SQL Server还支持非持久的计算列，只是不指定PERSISTED 子句。

那么，生成列与带有DEFAULT子句的普通列有何不同？

1、如果未提供其他值，则在首次插入行时对列默认值进行一次评估。每当行发生更改且无法覆盖时，生成的列都会更新。

2、可以在INSERT或UPDATE语句中为具有DEFAULT约束的列提供值。生成的列不能被赋予值，它们总是被计算的。

3、列的默认值不能引用表的其他列，而生成的列则专门用于引用该表。

4、列默认值可以使用易变函数，例如random()或current_time，生成的列不能使用。

重新索引改进

REINDEX CONCURRENTLY可以重建索引而不会阻止对其表的写入。CONCURRENTLY子句先前仅在CREATE INDEX语句中可用。

?
1
2
3
4
CREATE TABLE test (x INTEGER);
INSERT INTO test SELECT generate_series(1, 1000000);
CREATE INDEX i_test ON test (x);
REINDEX INDEXCONCURRENTLY i_test;

在早期版本的PostgreSQL中，REINDEX在大表上经常引起阻塞写入和严重的性能问题。

默认情况下启用JIT编译

如果服务器是在其支持下构建的，则默认情况下启用即时（JIT）编译。请注意，默认情况下不构建此支持，但是在配置构建时必须明确选择。这对于数据仓库查询特别有用。

查询并行性改进

在SERIALIZABLE隔离模式下，允许并行查询。以前，在此模式下禁用并行性。

PL/pgSQL增强

允许在相同的PL/pgSQL函数中使用与这些命令同名的SQL命令和变量。例如，允许一个调用变量comment存在于调用COMMENT SQL命令的函数中。以前，此组合导致解析错误。

SQL / JSON路径查询

PostgreSQL 12现在允许按照SQL：2016标准中的SQL / JSON规范执行JSON路径查询。JSON路径表达式使您可以评估各种算术表达式和函数，并比较JSON文档中的值，类似于XML的XPath表达式。可以使用GIN索引来加速这些表达式，从而改善跨JSON数据的查找执行。

分区改进

在处理带有成千上万个分区的表的命令时，只需使用所有可用分区的子集，PostgreSQL 12的性能就会得到显著提高。与以前的版本相比，INSERT和COPY到分区表的速度也要快得。 ATTACH PARTITION现在可以执行，而不会阻止对分区表的并发查询。现在可以将分区表称为外键。分区边界现在可以是表达式。

拼写改进

PostgreSQL 12现在支持对ICU提供的排序规则（也称为“非确定性排序规则”），不区分大小写和不区分重音的比较。使用这些排序规则时，可以为比较和排序提供便利，但是由于排序规则可能需要对字符串进行其他检查，因此也可能导致性能下降。

MCV统计信息现在支持多列

CREATE STATISTICS命令允许对多列进行最常用值统计。以前，多列只记录了一个相关值。

?
1
2
CREATE STATISTICS stts3 (mcv) ON state, city FROM zipcodes;
ANALYZE zipcodes;

从旧版本迁移

1、PostgreSQL 12中的某些更改可能会影响与早期版本的兼容性。

recovery.conf不再使用，并且如果该文件存在，服务器将不会启动。recovery.signal和standby.signal文件现在用于切换到非主要模式。trigger_file已重命名为promove_trigger_file。standby_mode设置已被删除。

2、在新的btree索引中，最大索引条目长度减少了八个字节，以改善重复条目的处理。这意味着REINDEX对先前版本的索引pg_upgrade 进行的操作可能会失败。

3、如果没有提供参数列表，并存在多个匹配对象，DROP IF EXISTS FUNCTION/ PROCEDURE/ AGGREGATE/ ROUTINE以产生一个错误

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。

原文链接：https://blog.csdn.net/hoofire/article/details/102663324

  • PostgreSQL
  • 搭建
  • 主备

    • 延伸 · 阅读

    精彩推荐
    • PostgreSQLPostgreSQL标准建表语句分享

      PostgreSQL标准建表语句分享

      这篇文章主要介绍了PostgreSQL标准建表语句分享，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      码上得天下7962021-02-27
    • PostgreSQL分布式 PostgreSQL之Citus 架构

      分布式 PostgreSQL之Citus 架构

      节点 Citus 是一种 PostgreSQL 扩展，它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群，允许 PostgreSQL 保存比单...

      未知802023-05-07
    • PostgreSQLpostgresql 数据库中的数据转换

      postgresql 数据库中的数据转换

      postgres8.3以后，字段数据之间的默认转换取消了。如果需要进行数据变换的话，在postgresql数据库中，我们可以用"::"来进行字段数据的类型转换。...

      postgresql教程网12482021-10-08
    • PostgreSQLPostgresql查询效率计算初探

      Postgresql查询效率计算初探

      这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Postgresql具有一定的参考学习价...

      轨迹4622020-05-03
    • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

      深入理解PostgreSQL的MVCC并发处理方式

      这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

      PostgreSQL教程网3622020-04-25
    • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

      RDS PostgreSQL一键大版本升级技术解密

      一、PostgreSQL行业位置 （一）行业位置 在讨论PostgreSQL（下面简称为PG）在整个数据库行业的位置之前，我们先看一下阿里云数据库在全球的数据库行业里的...

      未知1192023-05-07
    • PostgreSQLPostgresql开启远程访问的步骤全纪录

      Postgresql开启远程访问的步骤全纪录

      postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

      我勒个去6812020-04-30
    • PostgreSQLpostgresql 中的to_char()常用操作

      postgresql 中的to_char()常用操作

      这篇文章主要介绍了postgresql 中的to_char()常用操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      J符离13432021-04-12
    最近更新
    编辑推荐
    阅读排行
    热门标签
    5432 　 postgresql命令 　 数据库教程 　 入门命令 　 死锁进程 　 PostgreSQL教程 　 表继承 　 事物隔离 　 性能提升技巧 　 PL/pgSQL 　 过程语言 　 partitioning 　 PostgreSQL安装教程 　 跨库访问 　 postgresql数据库 　 并发处理 　 MVCC 　 Citus 　 OID 　 XID 　 表空间大小 　 Postgre 　 phpPgAdmin 　 phpPgAdmin错误 　 phpPgAdmin问题 　 phpPgAdmin配置文件 　 app应用 　 PostgreSQL配置 　 Postgresql下载 　 PostgreSQL入门教程 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图