postgresql 12版本搭建及主备部署操作
postgresql12版本主备部署
环境搭建
centos7+
postgresql12.0
#网络检查 ping-c2baidu.com #关闭防火墙,selinux systemctlstopfirewalld&&sudosystemctldisablefirewalld sed-ris/SELINUX=enforcing/SELINUX=disabled/g/etc/selinux/config setenforce0 ###配置阿里云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.repohttp://mirrors.aliyun.com/repo/Centos-7.repo #echo"isntallingtools" yum-yinstallvimnet-toolswgetntpdate yum-ygroupinstall"DevelopmentTools" #时间同步 echo"*/10****/usr/sbin/ntpdatentp.aliyun.com>/dev/null2>&1">>/etc/crontab #加快ssh连接 sed-ris/"#UseDNSyes"/"UseDNSno"/g/etc/ssh/sshd_config systemctlrestartsshd ##环境准备,根据自身需要,减少或者增加 yuminstall-ywgetgccgcc-c++epel-releasellvm5.0llvm5.0-develclanglibicu-develperl-ExtUtils-Embedreadlinereadline-develzlibzlib-developensslopenssl-develpam-devellibxml2-devellibxslt-developenldap-develsystemd-develtcl-develpython-devel ##下载、解压,安装 cd/usr/local/src wgethttps://ftp.postgresql.org/pub/source/v12.0/postgresql-12.0.tar.gz tar-xvzfpostgresql-12.tar.gz cdpostgresql-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&&makeinstall ##创建目录和用户,以及配置环境变量 groupaddpostgres useradd-gpostgrespostgres ##密码设置在引号内输入自己的密码 echo"postgres"|passwd--stdinpostgres mkdir-p/data/pgdata/{data,archivedir} chown-Rpostgres:postgres/usr/local/pgsql-R chownpostgres:postgres/data/pgdata-R echo"exportPATH=/usr/local/pgsql/12/bin:$PATH">/etc/profile.d/pgsql.sh source/etc/profile.d/pgsql.sh
环境部署已经完成,接下来我们来搭建postgresql的主备
主备搭建
备库搭建
以下的操作均在postgres用户下进行
su-postgres ##初始化 initdb-D/data/pgdata/data-Upostgres--locale=en_US.UTF8-EUTF8 cp/data/pgdata/data/{pg_hba.conf,pg_hba.conf.bak} ##允许repl用户进行流复制 cat<<-eof>>/data/pgdata/data/pg_hba.conf hostallall0.0.0.0/0md5 hostreplicationrepl192.168.0.0/22trust 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/serverlogstart ##创建用户进行流复制连接 psql-Upostgres-c"ALTERUSERpostgresWITHPASSWORD'postgres';" psql-Upostgres-c"CREATEUSERreplWITHPASSWORD'postgres'REPLICATION;"
备库搭建
##备份复制,备库配置, pg_basebackup-h192.168.3.62-p5432-Urepl-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=slave1user=replpassfile=''/home/postgres/.pgpass''host=192.168.3.62port=5432sslmode=prefersslcompression=0gssencmode=preferkrbsrvname=postgrestarget_session_attrs=any' ##启动备库 pg_ctl-D/data/pgdata/datastart
写在最后(状态检查)
##状态检查 ###回到主库查看状态 $psql-c"selectclient_addr,usename,backend_start,application_name,sync_state,sync_priorityFROMpg_stat_replication;" client_addr|usename|backend_start|application_name|sync_state|sync_priority --------------+---------+-------------------------------+------------------+------------+--------------- 192.168.3.61|repl|2019-10-1615:34:45.130732+08|slave1|async|0 (1row) $psql-c"selectclient_addr,sync_statefrompg_stat_replication;" client_addr|sync_state --------------+------------ 192.168.3.61|async (1row) ##备库只读,所以任何操作,都会告警失败: postgres=#dropdatabasepgtest; ERROR:cannotexecuteDROPDATABASEinaread-onlytransaction ##查看主备的状态 #主 ps-ef|grepsender postgres9299651692015:34?00:00:00postgres:walsenderrepl192.168.3.61(53958)streaming0/8000060 postgres10184520470015:45pts/200:00:00grep--color=autosender #备 ps-ef|greprecover postgres9831198309015:34?00:00:00postgres:startuprecovering000000010000000000000008 postgres10802921054015:46pts/200:00:00grep--color=autorecover #通过此命令判断主备 pg_controldata-D/data/pgdata/data/|grepstate inarchiverecovery##表示从库 inproduction##表示主库
补充:PostgreSQL12:新功能和增强功能
介绍
PostgreSQL12是世界上最受欢迎和功能最丰富的开源数据库的下一个主要发行版。PostgreSQL12的稳定版本计划于2019年末发布。版本12的Beta4已于2019年9月12日发布。
本文将引导您完成最重要的新功能的摘要。PostgreSQL12中的增强功能和重大更改。
新功能和增强功能
CTE增强
在PostgreSQL中,CTE是优化屏障(外部查询限制不传递给CTE),数据库在CTE内部评估查询,并缓存结果(即具体化的结果)和外部where子句,这意味着在处理外部查询时,要么执行完整表扫描,要么执行完整索引查找。对于大表来说,这会导致糟糕的性能。
WITHAllUsersAS(SELECT*FROMUsers) SELECT*FROMAllUsersWHEREId=100;
为了克服这个问题,您可以在PostgreSQL中将该查询重写为:
WITHUserRecordAS(SELECT*FROMUsersWHEREId=100) SELECT*FROMUserRecord;
PostgreSQL12通过引入查询优化器提示来解决此问题,以使我们能够控制是否应实现CTE:MATERIALIZED,NOTMATERIALIZED。因此,可以将查询重写如下,以获得更好的性能。
WITHAllUsersASNOTMATERIALIZED(SELECT*FROMUsers) SELECT*FROMAllUsersWHEREId=100;
这有效地内联了CTE表达式,并显着提高了性能。
生成列
SQLServer和Oracle长期以来都有计算列,而PostgreSQL中缺少计算列使从其他数据库的迁移变得非常困难。PostgreSQL12引入了生成列(GeneratedColumns)来解决这个问题。
生成列对于列就像视图对于表一样。该列的值始终是根据表中的其他列计算的。生成的列可以是虚拟的或存储的。虚拟列的值是在查询期间动态计算的,它们不会占用存储空间。已存储的列的值已预先计算并存储为表数据的一部分。PostgreSQL当前仅实现存储的生成列。
GENERATEDALWAYSAS子句用于创建生成的列
CREATETABLEEmployee( ..., dobtimestamp, ageintegerGENERATEDALWAYSAS(date_part('year',CURRENT_TIME-dob))STORED );
在PostgreSQL11.x和更早的版本中,唯一的方法是将age声明为普通列并编写触发器以在插入和更新期间填充该列
CREATEORREPLACEFUNCTIONcalc_age()RETURNSTRIGGERAS$body$ BEGIN NEW.age:=date_part('year',CURRENT_TIME-NEW.dob::timestamp); RETURNNEW; END; $body$LANGUAGEplpgsql;
计算列的SQLServer语法
将此与SQLServer语法进行对比。SQLServer中没有特殊的关键字来声明计算列。您只需在AS子句后指定使我们成为计算列的表达式。
--SQLServerSyntax CREATETABLEEmployee( ..., dobdatetime, ageASDATEDIFF(year,dob,GETDATE())PERSISTED );
请注意PERSISTED子句与PostgreSQL的STORED子句等效。SQLServer还支持非持久的计算列,只是不指定PERSISTED子句。
那么,生成列与带有DEFAULT子句的普通列有何不同?
1、如果未提供其他值,则在首次插入行时对列默认值进行一次评估。每当行发生更改且无法覆盖时,生成的列都会更新。
2、可以在INSERT或UPDATE语句中为具有DEFAULT约束的列提供值。生成的列不能被赋予值,它们总是被计算的。
3、列的默认值不能引用表的其他列,而生成的列则专门用于引用该表。
4、列默认值可以使用易变函数,例如random()或current_time,生成的列不能使用。
重新索引改进
REINDEXCONCURRENTLY可以重建索引而不会阻止对其表的写入。CONCURRENTLY子句先前仅在CREATEINDEX语句中可用。
CREATETABLEtest(xINTEGER); INSERTINTOtestSELECTgenerate_series(1,1000000); CREATEINDEXi_testONtest(x); REINDEXINDEXCONCURRENTLYi_test;
在早期版本的PostgreSQL中,REINDEX在大表上经常引起阻塞写入和严重的性能问题。
默认情况下启用JIT编译
如果服务器是在其支持下构建的,则默认情况下启用即时(JIT)编译。请注意,默认情况下不构建此支持,但是在配置构建时必须明确选择。这对于数据仓库查询特别有用。
查询并行性改进
在SERIALIZABLE隔离模式下,允许并行查询。以前,在此模式下禁用并行性。
PL/pgSQL增强
允许在相同的PL/pgSQL函数中使用与这些命令同名的SQL命令和变量。例如,允许一个调用变量comment存在于调用COMMENTSQL命令的函数中。以前,此组合导致解析错误。
SQL/JSON路径查询
PostgreSQL12现在允许按照SQL:2016标准中的SQL/JSON规范执行JSON路径查询。JSON路径表达式使您可以评估各种算术表达式和函数,并比较JSON文档中的值,类似于XML的XPath表达式。可以使用GIN索引来加速这些表达式,从而改善跨JSON数据的查找执行。
分区改进
在处理带有成千上万个分区的表的命令时,只需使用所有可用分区的子集,PostgreSQL12的性能就会得到显著提高。与以前的版本相比,INSERT和COPY到分区表的速度也要快得。ATTACHPARTITION现在可以执行,而不会阻止对分区表的并发查询。现在可以将分区表称为外键。分区边界现在可以是表达式。
拼写改进
PostgreSQL12现在支持对ICU提供的排序规则(也称为“非确定性排序规则”),不区分大小写和不区分重音的比较。使用这些排序规则时,可以为比较和排序提供便利,但是由于排序规则可能需要对字符串进行其他检查,因此也可能导致性能下降。
MCV统计信息现在支持多列
CREATESTATISTICS命令允许对多列进行最常用值统计。以前,多列只记录了一个相关值。
CREATESTATISTICSstts3(mcv)ONstate,cityFROMzipcodes; ANALYZEzipcodes;
从旧版本迁移
1、PostgreSQL12中的某些更改可能会影响与早期版本的兼容性。
recovery.conf不再使用,并且如果该文件存在,服务器将不会启动。recovery.signal和standby.signal文件现在用于切换到非主要模式。trigger_file已重命名为promove_trigger_file。standby_mode设置已被删除。
2、在新的btree索引中,最大索引条目长度减少了八个字节,以改善重复条目的处理。这意味着REINDEX对先前版本的索引pg_upgrade进行的操作可能会失败。
3、如果没有提供参数列表,并存在多个匹配对象,DROPIFEXISTSFUNCTION/PROCEDURE/AGGREGATE/ROUTINE以产生一个错误
以上为个人经验,希望能给大家一个参考,也希望大家多多支持毛票票。如有错误或未考虑完全的地方,望不吝赐教。
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。