Postgres SQL
文章平均质量分 63
文档搬运工
这个作者很懒,什么都没留下…
展开
-
postgres行列转换
Postgres行列转换示例原创 2023-05-16 15:14:56 · 417 阅读 · 0 评论 -
使用pg_verifybackup验证备份
将备份的表空间的内容,放在pg_tblspc下,而不是放在pg_tablespaces。或者,在解压表空间的备份文件的时候,直接解压在pg_tblspc,而不是解压在pg_tablespaces。数据库是pg_tblspc下是软连接,有数据,,pg_tablespaces下有实实在在的数据。-- 测试,当使用tar的方式备份的时候,如何使用pg_verifybackup来验证。--解压后,开始pg_verifybackup验证 ,测试过多次,失败,报错。-- 开始备份,以tar的方式。原创 2023-05-06 16:38:28 · 754 阅读 · 0 评论 -
postgres不完全恢复
在recovery_target、recovery_target_lsn、recovery_target_name、recovery_target_time和 recovery_target_xid 中, 最多只能使用一个,如果在配置文件中使用了多个,将会产生一个错误。-- 查看wal dump。-- 将backups里面的备份,拷贝到合适的位置(原来的位置,其实这三个文件的位置,还是在DATA下),进行解压。-- 再次恢复 ,恢复到第一次插入数据的状态,867 ,还原拿出来没有数据,只有表.原创 2023-05-06 09:52:24 · 582 阅读 · 0 评论 -
使用pg_basebackup备份
在recovery_target、recovery_target_lsn、recovery_target_name、recovery_target_time和recovery_target_xid中, 最多只能使用一个,如果在配置文件中使用了多个,将会产生一个错误。-- 从备份的结果看,16389.tar是新建立的表空间, base.tar和pg_wal.tar分别是pgdata目录下的base目录和归档目录的内容。--连接到数据库 ,数据库为只读模式 ,执行pg_wal_replay_resume()原创 2023-05-04 16:02:51 · 1099 阅读 · 0 评论 -
postgres创建分区表
- 确保参数enable_partition_pruning 为ON。1 创建分区表,后面加上PARTITION BY XXX (XXX )2 创建分区,建表语句后面加上 PARTITION OF XXX。-- 新增加一个6月份的分区,使数据能插入到分区。-- 创建分区表,指定分区键为logdate。-- 通过dt+ 命令,查看建立的分区表和分区。-- 插入6月份的数据 ,会提示没有分区。存放有关表如何被分区的信息。原创 2023-04-07 10:23:32 · 4050 阅读 · 0 评论 -
postgres分区表的创建-基于继承
- 插入2023年6月份的数据 ,直接报错 ,因为此时没有创建6月份的子表,触发器函数中也没有处理6月份数据的规则。-- 创建触发器 ,不带schema,创建的触发器,默认就在apps的schema中。-- 创建触发器函数,针对不同月份的数据,落入不同的子表,从而达到数据分区的效果。3 创建函数及触发器,使插入的数据根据规则,插入到对应的子表中。-- 查看父表和各个分区表的数据 ,可以看到1个父表,5个分区表。-- 通过视图查看分区表,查询不到基于继承建立的分区表。-- 创建6月份分区表,原创 2023-04-07 10:14:16 · 500 阅读 · 0 评论 -
Postgres中恢复删除的表数据
参考文档:pg9的文档:http://postgres.cn/docs/9.6/app-pgresetxlog.htmlhttp://postgres.cn/docs/9.6/pgxlogdump.htmlpg10的文档:http://postgres.cn/docs/10/pgwaldump.htmlhttp://postgres.cn/docs/10/app-pgresetwal.html-- 测试环境,PG9.4. 手头上电脑上有2年前的pg9,顺便测试下。如果是pg10及以上版本,则原创 2021-09-30 15:56:16 · 3119 阅读 · 0 评论 -
Pgpool-II + Watchdog 设置与测试
参考文档:Pgpool-II + Watchdog Setup Examplehttps://www.pgpool.net/docs/latest/en/html/example-configs.htmlhttps://www.pgpool.net/docs/latest/en/html/example-cluster.html已测试完成的功能1 当其中某一个节点上的pgpool失败时,VIP会自动漂移到另一个节点上,也就是watchdog起作用了2 关闭掉primary 数据库,stan原创 2021-08-31 14:43:12 · 7593 阅读 · 5 评论 -
pgpool-II的搭建与负载均衡
说明:本文主要测试PG-POOL的安装,PG-POOL的功能,failover功能和load balance功能.PG-POOL的复制和在线恢复功能不做测试( recovery_1st_stage等脚本),目前PG的逻辑备库的流复制可以满足复制、pg_rewind命令可以进行主备切换后的备库重建,想对比PG-POOL复杂的恢复脚本方便了很多(个人愚见,初步接触PG-POOL)。正常情况下,使用PG_POOL,分配一个VIP。外部应用连接到VIP。当其中一个节点down后,VIP会漂移。对应用来说无影原创 2021-08-27 15:43:06 · 1888 阅读 · 3 评论 -
Postgres主备切换及pg_rewind的使用
前言:经过测试,postgres的主备切换后,同步会出现问题,新的备库不会从主库同步数据过来。需要使用pg_rewind重新修复新的备库参考文档:http://postgres.cn/docs/10/app-pgrewind.html-- 主备信息PG版本 :10.15主库 192.168.2.80 主机名test备库 192.168.2.81 主机名test1-- 查看主备库的信息[root@test /opt/pgpool/bin]$pg_controldata...原创 2021-08-27 11:14:51 · 1849 阅读 · 0 评论 -
slony的安装与配置
-- PG数据库信息数据库版本,10.18 ,编译安装192.168.2.80 ,数据库名master192.168.2.81 ,数据库名slave--PG数据库安装与设置(略)--安装slony解压安装包,如果命令出错。则要安装bzip ,可以通过--help查看configure的帮助。configure使用到的一些文件夹,可以通过pg_config获取到yum install bzip2tar -jxvf slony1-2.2.5.tar.bz2 ./con...原创 2021-08-25 14:55:40 · 596 阅读 · 0 评论 -
POstgres的两阶段提交
-- 将max_prepared_transactions设置为大于0mydb=# show max_prepared_transactions; max_prepared_transactions --------------------------- 20(1 row)mydb=# -- 创建一个表create table t1 (id int primary key,name text);-- 启动一个事务,插入数据begin;insert into t1 ..原创 2021-08-20 11:01:10 · 716 阅读 · 0 评论 -
Postgres的临时表、临时视图
参考文档:http://postgres.cn/docs/11/sql-createtable.htmlhttp://postgres.cn/docs/10/sql-createview.html--- 基于会话的临时表(默认),会话结束,表消失-- 会话1中创建临时表,在当前会话中查看该表 ,可以看到该表的schema为pg_temp_5,可以访问该表create temporary table tmp_t1 (id int primary key,name text);mydb=#原创 2021-08-20 08:19:23 · 1756 阅读 · 0 评论 -
Postgres备库日志的停止与继续复制
参考文档:9.26.系统管理函数 (postgres.cn)表9.81中展示的函数空值恢复的进程。这些函数只能在恢复过程中被执行。表9.81.恢复控制函数名称 返回类型 描述 pg_is_wal_replay_paused() bool 如果恢复被暂停,为真。 pg_wal_replay_pause() void 立即暂停恢复(仅限于超级用户)。 pg_wal_replay_resume() void 如果恢复被暂停,重启之...原创 2021-07-31 11:13:49 · 1402 阅读 · 0 评论 -
postgres的brin索引
参考文档:65.1.简介 (postgres.cn)BRIN表示块范围索引。BRIN是为处理这样的表而设计的:表的规模非常大, 并且其中某些列与它们在表中的物理位置存在某种自然关联。一个块范围是一组在表中物理上相邻的页面,对于每一个块范围在 索引中存储了一些摘要信息。例如,一个存储商店销售订单的表可能有一个日期 列记录每个订单产生的时间,并且很多时候较早的订单项也将出现在表中较早的 地方。一个存储 ZIP 代码列的表中一个城市的所有代码可能自然地聚在一起。如果索引中存储的摘要信息与查询条件...原创 2021-07-29 14:34:54 · 294 阅读 · 0 评论 -
使用pg_rman 备份Postgres(全备、增量、备份归档)
参考文档:https://github.com/ossc-db/pg_rmanhttps://github.com/digoal/blog/blob/master/201608/20160826_01.md-- 版本信息pg_rman版本 1.3.12postgres版本 10.15-- pg_rman的安装(略)[root@test /root]$pg_rman --versionpg_rman 1.3.12[root@test /root]$[postgres@t...原创 2021-07-16 13:46:22 · 1336 阅读 · 0 评论 -
postgres中的postmaster.pid文件
参考文档:66.1.数据库文件布局 (postgres.cn)9.9.时间/日期函数和操作符 (postgres.cn)postmaster.pid 一个锁文件,记录着当前的 postmaster 进程ID(PID)、集簇数据目录路径、postmaster启动时间戳、端口号、Unix域套接字目录路径(Windows上为空)、第一个可用的listen_address(IP地址或者*,或者为空表示不在TCP上监听)以及共享内存段ID(服务器关闭后该文件不存在) -- 主库上的post...原创 2021-07-15 16:05:13 · 1772 阅读 · 0 评论 -
Postgres主备的创建
说明:Postgres的主备,也叫物理复制,和发布订阅不同的是,发布订阅可以针对某个表,主备物理复制是针对整个数据库的。有点类似SQL Server的主备和Oracle Data Guard。参考文档:http://www.postgres.cn/docs/10/warm-standby.html-- 主备信息PG 版本 10.15primay : 192.168.2.80standby : 192.168.2.81-- 安装主备库(略)-- 参数修改,只需要修改主库...原创 2021-07-14 14:24:18 · 1174 阅读 · 0 评论 -
Postgres的发布与订阅
-- 发布与订阅(个人感觉类似于Oracle的stream,和sqlserver的发布与订阅)参考文档:http://www.postgres.cn/docs/11/logical-replication.html发布端: 192.168.2.74 PG10.16 pub_t1 ;订阅端: 192.168.2.80 PG10.15 pub_t1 ;-- 创建一个数据库,作为订阅端mydb=# create database repdb;CREATE DATABASEmyd...原创 2021-07-13 16:53:10 · 2706 阅读 · 0 评论 -
postgres中的绑定变量 - prepare
参考文档:http://postgres.cn/docs/10/sql-prepare.html类似Oracle的绑定变量-- prepare语句PREPARE fooplan (int, int, text) AS INSERT INTO foo VALUES($1, $2, $3);EXECUTE fooplan(4, 2, 't');mydb=# PREPARE fooplan (int, int, text) ASmydb-# INSERT INTO foo .原创 2021-07-08 13:38:59 · 1278 阅读 · 0 评论 -
使用pg_upgrade对postgres进行升级
10.15 -- 10.16--参考文档:http://postgres.cn/docs/10/pgupgrade.htmlhttps://www.postgresql.org/ftp/source/v10.16/-- 当前版本mydb=# select version(); version .原创 2021-07-02 16:26:39 · 353 阅读 · 0 评论 -
postgres中表的继承
参考文档:http://postgres.cn/docs/10/ddl-inherit.htmlhttp://postgres.cn/docs/10/tutorial-inheritance.html-- 创建表create table p1(id int primary key, info text unique, c1 int check(c1>0), c2 int not null, c3 int unique);create table c1(like p1) inherit原创 2021-06-23 14:33:01 · 561 阅读 · 0 评论 -
postgres中的auto_explain
参考文档:http://www.postgres.cn/docs/11/auto-explain.html该功能,类似mysql中的慢查询 ++++++++++++++++++++++以下内容为官方文档内容++++++++++++++++++++++++auto_explain模块提供了一种方式来自动记录慢速语句的执行计划,而不需 要手工运行EXPLAIN。这在大型应用中追踪未被优化的查询时有用。该模块没有提供 SQL 可访问的函数。要使用它,简单地将它载入服务器。你可以把它载入到一个单.原创 2021-06-22 13:52:38 · 584 阅读 · 0 评论 -
Postgres的文件系统
本文主要介绍,查看数据库中各个对象的oid,各个对象的大小(数据库、表、索引等)。以及验证超过1G的对象是否会被自动分割。使用到的函数:pg_database_sizepg_relation_sizepg_indexes_sizepg_table_sizepg_total_relation_size使用到的工具:oid2name使用到的psql命令:\l+\dS+\diS+\dt+\db+-- 数据库版本mydb=# select * from version();..原创 2021-06-21 16:11:53 · 458 阅读 · 0 评论 -
查看postgres的执行计划
查看postgres版本-bash-4.2$ pg_config --versionPostgreSQL 10.15-bash-4.2$原创 2021-06-21 15:06:27 · 1133 阅读 · 0 评论 -
使用pgbench测试postgres数据库
参考文档:http://postgres.cn/docs/10/pgbench.html-- pg版本mydb=# select version(); version ------------------------------------------------------------------原创 2021-06-15 14:14:09 · 522 阅读 · 0 评论 -
Postgres性能和并发
参考文档: PostgreSQL 9 Administration Cookbook (第二版)中文版 第十章 性能和并发寻找执行缓慢的SQL语句(设置有点类似mysql的慢查询),将该参数修改为10秒,日志中会记录查询超过10秒的语句#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements # and their d原创 2021-02-18 15:29:18 · 1317 阅读 · 1 评论 -
postgres常规维护
参考文档: PostgreSQL 9 Administration Cookbook (第二版)中文版 第九章 常规维护postgresql提供vacuum工具命令。可以和analyze联合使用。不过在合适的时候,会被autovacuum自动执行。autovacuum默认是被启用的。--确定autovacuum是开启的,需要启用以下参数autovacuum = on --控制服务器是否运行自动清理启动器后台进程。默认为开启,不过要自动清理正常工作还需要启用track_counts。trac原创 2021-02-09 11:13:42 · 572 阅读 · 0 评论 -
Postgres的监控和诊断
参考文档: PostgreSQL 9 Administration Cookbook (第二版)中文版 第八章 监控和诊断select * from pg_stat_user_tables --和pg_stat_all_tables一样,但只显示用户表,当前数据库中每个表一行,显示有关访问指定表的统计信息select * from pg_stat_user_indexes --和pg_stat_all_indexes一样,但只显示系统表上的索引.-- 可以使用pgadmin快速查看数据库的当前状原创 2021-02-03 14:47:47 · 1248 阅读 · 0 评论 -
PostgreSQL 数据库管理
参考文档: PostgreSQL 9 Administration Cookbook (第二版)中文版 第七章 数据库管理-- 事务,要么全部成功,要么全部失败BEGIN;command 1;command 2;command 3;COMMIT;-- 在psql客户端,可以使用-1 ,--single-transaction 来表示使用事务bash $ psql -1 -f myscript.sqlbash $ psql --single-transaction -f mysc原创 2021-01-26 16:54:53 · 780 阅读 · 0 评论 -
Postgres安全相关
参考文档: PostgreSQL 9 Administration Cookbook (第二版)中文版--用户在新建立的时候,加上superuser属性,会变成超级用户,在pgadmin中的role下可以看到。create user bb superuser;alter user bb with password 'oracle';-- 移除用户的superuser属性alter user bb nosuperuser;mydb=# create user bb superuser;C原创 2021-01-22 15:22:39 · 497 阅读 · 0 评论 -
postgres的表和数据
参考文档:PostgreSQL 9 Administration Cookbook (第二版)中文版-- 对索引进行重命名ALTER INDEX badly_named_index RENAME TO tablename_status_idx;-- 处理使用双引号括起来的对象名 。在建表的时候,表名使用了双引号,则在查询的时候,也需要使用双引号。CREATE TABLE "Myemp" AS SELECT * FROM emp;mydb=# select count(*) f原创 2021-01-22 15:09:49 · 697 阅读 · 0 评论 -
Postgres的服务控制 && pgbouncer的安装与配置
参考文档: PostgreSQL 9 Administration Cookbook (第二版)中文版-- pg的关闭 (pt_ctl,具体可以查看帮助)pg_ctl -D datadir -m fast stop pg_ctl -D datadir stop -m immediate -- 相当O的shutdown abort -- 重新加载配置文件pg_ctl -D datadir reload select pg_reload_conf()-- 查看修改原创 2021-01-13 14:55:11 · 598 阅读 · 0 评论 -
postgres的配置
参考文档: PostgreSQL 9 Administration Cookbook (第二版)中文版为会话和事务设置参数-- 在会话中更改设置值set work_mem = '16MB'mydb=# set work_mem='16MB';SETmydb=# select name,setting,reset_val,source from pg_settings where source='session'; name | setting | reset_val | sour原创 2021-01-13 14:49:06 · 779 阅读 · 0 评论 -
Postgres学习之 入门 && 浏览数据库
参考文档《PostgreSQL 9 Administration Cookbook》(第二版)中文版第一章 迈出第一步-- 查看数据库的版本mydb=# select version(); version ------------------------------------------------原创 2021-01-08 16:52:33 · 494 阅读 · 0 评论 -
Postgres下dblink的使用 、FDW的使用(2021-01-26 update)
参考文档:《Postgres 10.1中文手册》-- 数据库版本为10.15postgres=# select version(); version -----------------------------------------------------------------------------原创 2021-01-06 17:08:47 · 1073 阅读 · 0 评论 -
使用pg_basebackup对Postgre进行备份与恢复
postgres 版本9.4.23暂时在Windows下对postgres进行备份恢复。在进行备份之前,在认证文件中增加一行,否则无法进行备份host replication postgres 127.0.0.1/24 md5开始使用pg_basebackup命令进行备份。当pgbak文件夹不存在的时候,备份的过程中会自动创建该...原创 2019-08-07 13:11:09 · 3993 阅读 · 0 评论 -
PostgreSQL的编译安装及主备搭建(基于copy数据方式 和 基于pg_basebackup方式)
PostgreSQL版本:9.4.23OS : redhat 7.4primary IP: 192.168.2.101Standby IP: 192.168.2.102-- 编译安装下载地址https://ftp.postgresql.org/pub/source/v9.4.23/安装包yum install gcc readline-devel zlib-devel...原创 2019-07-02 11:30:07 · 926 阅读 · 0 评论 -
PostgreSQL的安装与入门
PostgreSQL版本: 9.4redhat版本,6.10PostgreSQL的安装,可以参考官方文档:https://www.postgresql.org/download/linux/redhat/安装完毕后,进行一些简单的操作。登录到postgreSQL,查看当前用户,当前版本等信息[root@rac03 ~]# su - postgres-bash-4.1$ l...原创 2019-06-10 13:09:23 · 417 阅读 · 0 评论