POSTGRCSQL - 技术教程文章
PostgreSQL逻辑备份恢复--pg_dump导出及psql导入案例【代码】
数据库导入导出是最常用的功能之一。PostgreSQL的备份工具可以使用pg_dump及pg_dumpall。可以通过pg_dump --help获取其使用方法。这里不对其做过多介绍。主要介绍在使用pg_dump及恢复过程中遇到的一个问题。 1、问题使用pg_dump -c导出后，通过psql导入时报下面的错误： ERROR: relation "t1" already exists ERROR: duplicate key value violates unique constraint "t1_pkey" ERROR: multiple primary keys for table "t1" ar...
postgresql分布式集群之citus【代码】【图】
今天，利用大家的休息时间分享postgresql分布式集群，利用Citus实现分库分表。 一、Citus是什么citus是PG的一个sharding插件，可以把PG变成一个分布式数据库。目前在苏宁有大量的生产应用跑在citus+pg的环境中。大家可以看it大咖视频。citus是一款基于PostgreSQL的开源分布式数据库,自动继承了PostgreSQL强大的SQL支持能力和应用生态（不仅仅是客户端协议的兼容还包括服务端扩展和管理工具的完全兼容）。 和其他类似的基于PostgreS...
PostgreSQL基础知识分享【代码】
1、架构 PostgreSQL是一个关系数据库，采用c/s架构。一个PostgreSQL会话包括： 服务端进程——postgres:用于管理数据库文件，并接受来自客户端的数据库连接， 同时代替客户端执行数据库操作。客户端2、命令 （1）创建数据库createdb -U postgres mydb（2）连接数据库psql -U postgres mydb（3）元命令（内部命令）\h 可以查看SQL命令语法上的说明，后面不加SQL命令，会列出所有SQL命令 \? 可以查看所有的元命令 \l 查看所有数据库 ...
Postgresql-11.X 性能优化详解【代码】【图】
postgres 性能优化 系统优化 修改 /etc/grub.conf 关闭 numa=off ，修改磁盘IO调度方式 elevator=deadline 修改方法： grubby --update-kernel=ALL --args="transparent_hugepage=never" --args="elevator=deadline" 验证： grubby --info=ALL 返回 args="ro crashkernel=auto rhgb quiet numa=off elevator=deadline" 关闭内存大页 hugepage 验证是否开启方法1： 如果以下文件不存在，则是THP已经从内核中移除。 /sys/kernel...
PostgreSQL还原.bak文件
1.win+R输入cmd 2.cd C:\PostgreSQL\10\bin 3.还原之前首先在postgresql中新建数据库，作为目标数据库 4.在postgresql对目标数据库执行create extension postgis;语句，确保空间数据加载正常,同时生成表spatial_ref_sys 5.psql -h localhost -U postgres -p 5432 -d DBtext -f "E:\DBtext .bak" 6.用户 postgres 的口令:输入密码回车
PostgreSQL用户密码如何通过md5加密存储，是否加了salt【代码】
一、PG用户的密码如何通过md5加密，并且是否加了salt？本文将从源码角度跟踪分析。 PG用户通过md5加密时，加了salt，而这个salt是用户名字符串。 二、源码分析 CreateRole：shadow_pass = encrypt_password(Password_encryption, stmt->role,password);|-- pg_md5_encrypt(password, role, strlen(role),encrypted_password);| |-- memcpy(crypt_buf, passwd, passwd_len);| | memcpy(crypt_buf + passwd_len, role, strlen(...
PostgreSQL 给数组排序【代码】
PostgreSQL 支持数组，可是没有对数据内部元素进行排序的一个函数。 ?今天我分别用PLPGSQL和PLPYTHONU写了一个。演示样例表结构：t_girl=# \d test_array;Table "ytt.test_array"Column | Type | Modifiers --------+-----------+---------------------------------------------------------id | integer | not null default nextval('test_array_id_seq'::regclass)st...
PostgreSQL9.6+PostGIS2.3学习笔记（一）导入shp文件【图】
一. 建库以及准备工作：（使用pgAdmin4直接建库） 打开pgAdmin4,如下图所示create–>Database 输入database的名字，如下图，输入完成即可选择save进行保存。这样基本上就可以了，其他选项可以根据自身需要进行选择。 给新建的库添加扩展，需要登录psql进行操作： 图中1：选择自己对应的server,Database等，如果不需要更改直接回车进入下一项，如果需要更改就需要在其后面填写修改的内容。如图中所示，我们要进入到需要扩展的Data...
PostgreSQL 允许远程访问设置方法
安 装PostgreSQL数据库之后，默认是只接受本地访问连接。如果想在其他主机上访问PostgreSQL数据库服务器，就需要进行相应的配置。 配置远 程连接PostgreSQL数据库的步骤很简单，只需要修改data目录下的pg_hba.conf和postgresql.conf。 pg_hba.conf：配置对数据库的访问权限， postgresql.conf：配置PostgreSQL数据库服务器的相应的参数。 下面 介绍配置的步骤： 1.修改pg_hba.conf文件，配置用户的访问权限（#开头的行是注释内容）...
PostgreSQL的递归查询(with recursive)【代码】
开发有需求，说需要对一张地区表进行递归查询，Postgres中有个 with recursive的查询方式，可以满足递归查询(一般>=2层)。 测试如下：create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); insert into tb values('002' , 0 , '浙江省'); insert into tb values('001' , 0 , '广东省'); insert into tb values('003' , '002' , '衢州市'); insert into tb values('004' , '002' , '杭州市') ; insert into t...
centos7 postgresql 自定义PGDATA与systemd 改变数据文件存放位置
自定义PGDATA与systemd 默认情况下，在CentOS 7上，PostgreSQL v10数据目录位于/ var / lib / pgsql / 10 / data中。 这是一个简单的技巧，可以轻松地将其放置在其他地方，而无需使用符号链接。 首先，安装PostgreSQL 10：yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-7.noarch.rpm yum install -y postgresql10-server 如果您希望将数据放在（例如）/ pgdata / 10 ...
在SequoiaSQL（Postgresql）中配置 Spark （Hive）元数据库
SequoiaSQL 和 Spark 的安装配置放在在这里不赘述。 Thrift server 最好是配合 Hive 元数据库使用。所以这里讲一下如何配置，并启动 Thrift server ，最后可以用 beeline 命令行比较方便清爽的使用 Spark SQL 。下面是配置步骤。 1. Postgresql JDBC 驱动 下载 Postgresql jdbc驱动： https://jdbc.postgresql.org/download.html 注意驱动版本不仅跟Postgresql版本相关，还跟 jdk 版本相关。 下载之后，上传pos...
用客户端命令psql连接 PostgreSQL 不输入密码的方法【代码】
用客户端命令psql连接 PostgreSQL 不输入密码的方法 用psql命令连接远程的PostgreSql数据库时，在命令中不能传密码，只能连接后，再按照提弹出密码输入提示输入密码(这一点感觉很别扭，在Mysql上就可以在命令中传入密码)，如果这样的话就涉及到和服务器的交互过程（或者称之为会话）。如下所示，输入命令后提示你输入密码： eg:在 192.168.1.22 连接数据库 ZY-BigData, 弹出密码提示 [zhu@localhost ~]$ psql -h 192.168.8....
POSTGRESQL数据库报： FATAL: no pg_hba.conf entry for host 的解决方案【图】
在数据目录下修改 IPv4 local connections增加IP段的描述 如下图之后使用postgres用户在安装目录bin下执行 ./pg_ctl restart -D /opt/PostgreSQL/10/data/重启数据库
Postgresql获得表字段结构信息包含注释
Select * From ( Select a.relname FTableName, b.attname FFieldName, format_type(b.atttypid,b.atttypmod) as FFieldType, col_description(b.attrelid,b.attnum) FFieldDesc, b.attnum FNo, b.attnotnull FNotNull from (select oid, * from pg_class where relnamespace=2200 and reltype>0) a left join (select * From pg_catal...
PostgreSQL如何删除不使用的xlog文件【代码】【图】
一、问题经常会在复制的时候遇到这样的问题，需要复制的xlog文件找不到了。那么xlog文件什么时候删除？又会删除多少保留多少个xlog文件？都有哪些xlog文件需要保留？本文将从原理上对这些问题进行解读。 二、原理每次checkpoint后都会根据需要删除或者回收不再需要的xlog文件。1、首先估算两次checkpoint之间产生的xlog量，根据这个量会计算出未来最大的日志文件号从而回收不再需要的文件将其重命名为未来即将使用的日志文件号：1....
PostgreSQL备份工具：pg_rman编译安装【代码】
文章目录概述准备工作postgres环境需要的 packages获取源码包版本错误的报错编译安装回归测试 概述 pg_rman 是一个专门为pg打造的在线物理备份工具，类似于oracle的rman，今天，我们就来学习一下如何编译安装一个pg_rman 准备工作 postgres环境 既然是为了备份pg而存在的工具，我们安装使用它，首先需要有pg环境，最好是编译安装。不管是root还是postgres用户都要配置好 需要的 packages zlib-devel 直接 yum install zlib-devel 即...
postgreSQL 玩转josnb (长期更新)
---恢复内容开始--- json和jsonb的操作符操作符 右操作数类型 描述 示例 结果-> int 获取JSON数组元素（索引从0开始） select [{"a":"foo"},{"b":"bar"},{"c":"baz"}]::json->2; {"c":"baz"}-> text 通过键获取值 select {"a": {"b":"foo"}}::json->a; {"b":"foo"}->> int获取JSON数组元素为 textselect [1,2,3]::json->>2; 3->> text 通过键获取值为text select {"a":1,"b":2}::json->>b; 2#> text[]在指定的路径获取JSON对象sele...
Postgresql 密码设置【代码】【图】
今天下午 陷进去了 其实很简单的一个事情结果浪费了 接近一个小时. 做事情必须要细致一些. 自己的确做的不好. 这里面简单说一下pg_hba.conf 和 postgresql 密码的一些设置问题. 1. windows 机器 安装pg 时 有一个 让输入密码的地址 这个 直接输入密码就可以了. 然后如果想修改密码的话 可以在命令行里面处理.: 然后修改 data 目录下面的 : pg_hba.conf 注意里面的配置文件内容:The first field is the connection type: "loca...
PostgreSQL checkpoint--shutdown
PostgreSQL在shutdown时会进行checkpoint。其流程如下。 1、在主进程中，会首先注册一个信号处理函数reaper，用于向checkpoint等子进程发送信号。向checkpoint进程发送SIGUSR2信号 PostmasterMain(int argc, char argv[])pqsignal_no_restart(SIGCHLD, reaper); / handle child termination */reaper：while ((pid = waitpid(-1, &exitstatus, WNOHANG)) > 0){...if (pid == CheckpointerPID){...SignalChildren(SIGUSR2);}...}....
CentOS7(64) yum安装、配置PostgreSQL 11
一、安装postgresql11 1、Install the repository RPM: 添加RPMyum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm 2、Install the client packages: 安装PostgreSQL11yum install postgresql11 3、Optionally install the server packages: 安装服务包 yum install postgresql11-server 4、Optionally initialize the database and enable automatic start: 初...
postgresql之json操作
--string 转 json select {"Items":[{"Id":1,"InitStartDate":"2018-07-01T00:00:00","InitEndDate":"2018-11-13T11:46:59.461722+08:00","CurrentStartDate":"","ProcessPercent":0,"State":0}]}::jsonb into task_config ; --timestamp 转 字符串，字符串 转 jsonselect concat(", to_char(start_date,YYYY-MM-DD), ")::jsonb into startj_date;select concat(", to_char(end_date,YYYY-MM-DD), ")::jsonb into endj_date;--修...
postgresql优势
PostgreSQL 是一个自由的对象-关系数据库服务器（数据库管理系统），功能很强大。包括了可以说是目前世界上最丰富的数据类型的支持，比如 IP 类型和几何类型等等。发现很多读者都问过这样一个问题：如果打算为项目选择一款免费、开源的数据库，那么你可能会在MySQL与PostgreSQL之间犹豫不定。针对这个问题，我们采访到了即将在Postgres中国用户2016大会上作分享的平安科技数据库技术部总监汪洋，请他从实践者的角度给出一些经验分享...
postgresql恢复数据, 自增处理
postgresql恢复数据, 自增处理pg中有一个自增类型, serial serial类型会创建一个序列, sequence 使用pg_dump备份数据并不会记录sequence的最大值 在恢复数据时, 序列会重建, 起始值为0解决这个问题, 要么在pg_dump时指定生成insert语句 insert语句会触发sequence, 理所当然的会将序列最大值恢复 默认是copy csv格式 insert的速度 会比copy慢些 要么手动指定序列最大值我的做法 drop function if exists restore_seq (); create or ...
PostgreSQL pg_ctl start超时分析【代码】
一、问题pg_ctl start启动时报错退出：pg_ctl：server did not start in time。超时时间是多少？从什么时候到哪个阶段算超时？ 二、分析：该信息打印位置，从后面代码段do_start函数中可以看出1、pg_ctl start调用start_postmaster启动PG的主进程后，每隔0.1ms检查一次postmaster.pid文件，是否已写入ready/standby2、总共会检查600次，即从启动主进程后，最多等待60s，如果没有写入ready/standby则打印上述日志并退出3、默认等待时...
postgresql复制参考
1.创建复制用户：CREATE USER repl WITH replication login password repl; 2.编辑recovery文件：cat /pgdata/10/data/recovery.confstandby_mode = onprimary_conninfo = host=192.168.56.202 port=5432 user=postgres application_name=db3 user=repl password=repl keepalives_idle=60 keepalives_interval=5 keepalives_count=5restore_command = recovery_target_timeline = latest 3.在db3上执行，ip：192.168.56.203：pg_b...
一种简单方便的postgresql插件multicorn安装方法 ---yum install multicorn10.x86_64
Yum 安装multicorn的方法，一下子解决了之前make&make install方式安装的各种问题必须记录一下。 1.yum search multicorn----找到需要的包 2.Yum install 包名 ————》done [root@localhost elk]# yum install multicorn10.x86_64 Loaded plugins: fastestmirror Setting up Install Process Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * epel: mirrors.aliyun.com * extras: m...
centos6.7安装单节点postgresql
系统版本：cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.7 (Santiago)默认安装PGsql8.2版本数据存放目录/usr/openv/pg_data我这里安装的是9.4版本，所以如果安装的卸载即可# yum remove postgresql-server在下面网站找到自己需要的版本http://download.postgresql.org/pub/repos/yum/https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-6.7-x86_64/postgresql94-9.4.18-1PGDG.rhel6.x86_64.rp...
postgresql插入汉字报错
错误信息：peimsmdb=# select 我;ERROR: character with byte sequence 0xe6 0x88 0x91 in encoding "UTF8" has no equivalent in encoding "LATIN1"查看客户端字符集:peimsmdb=# show client_encoding; client_encoding ----------------- UTF8peimsmdb=# show server_encoding; server_encoding ----------------- LATIN1思路：因为没有修改环境变量，导致安装完数据库，数据库默认字符集变成LATIN1，如下所示。postgres=# \l ...
postgresql9.6主从高可用源码环境编译配置详解
系统版本：centos78核32G内存主从服务器IP：192.168.125.33 postgreSQL master192.168.125.34 postgreSQL slave1、创建数据库管理账户# groupadd pggroup# useradd -g pggroup pguser # passwd pguser2、安装依赖包yum install readline-devel zlib-devel -y如果不安装以上两个包，编译postgresql就会报缺少相应包的错误，导致无法通过。yum install postgresql-contribhttp://mirror.centos.org/centos/7/os/x86_64/Packages/uuid...