psql
siwluxuefeng
后知后觉
展开
-
开窗函数应用
1.编写一个 SQL 查询,查找所有至少连续出现三次的数字。leetcode:https://leetcode-cn.com/problems/consecutive-numbers/+----+-----+| Id | Num |+----+-----+| 1 | 1 || 2 | 1 || 3 | 1 || 4 | 2 || 5 | 1 |...原创 2020-04-29 00:30:06 · 413 阅读 · 0 评论 -
Postgrsql之alter table
ALTER TABLEContents [hide] 1 名字2 语法概要3 描述4 参数5 注意6 示例7 兼容性名字ALTER TABLE -- 修改一个表的定义语法概要 ALTER TABLE [ ONLY ] name [ * ] action [, ... ] ALTE转载 2018-02-05 18:02:50 · 721 阅读 · 0 评论 -
Postgresql之WAL log机制
WAL机制简介WAL即 Write-Ahead Logging,是一种实现事务日志的标准方法。WAL 的中心思想是先写日志,再写数据,数据文件的修改必须发生在这些修改已经记录在日志文件中之后。采用WAL日志的数据库系统在事务提交时,WAL机制可以从两个方面来提高性能:多个client写日志文件可以通过一次 fsync()来完成日志文件是顺序写的,同步日志的开销要远比同步数据页的开销要小总体来说,使...转载 2018-03-30 15:48:54 · 2999 阅读 · 0 评论 -
Postgresql之CheckPoint机制
checkpoint又名检查点,一般checkpoint会将某个时间点之前的脏数据全部刷新到磁盘,以实现数据的一致性与完整性。目前各个流行的关系型数据库都具备checkpoint功能,其主要目的是为了缩短崩溃恢复时间,以Oracle为例,在进行数据恢复时,会以最近的checkpoint为参考点执行事务前滚。而在WAL机制的浅析中,也提过PostgreSQL在崩溃恢复时会以最近的checkpoint...转载 2018-03-30 15:49:14 · 1621 阅读 · 1 评论 -
Postgresql之从崩溃状态中恢复
wal_level:控制wal存储的级别。wal_level决定有多少信息被写入到WAL中。 默认值是最小的(minimal),其中只写入从崩溃或立即关机中恢复的所需信息。replica 增加 wal 归档信息 同时包括 只读服务器需要的信息。(9.6 中新增,将之前版本的 archive 和 hot_standby 合并) logical 主要用于logical decoding 场景推测:只记...转载 2018-03-30 16:00:46 · 3029 阅读 · 0 评论 -
Postgresql9.6进程及体系结构
从上面的体系结构图可以看出来,PG使用经典的C/S架构,进程架构。在服务器端有主进程、服务进程、子进程、共享内存以及文件存储几大部分,下面着重讲述服务器端的进程部分:1. Postmaster主进程和服务进程当PG数据库启动时,首先会启动Postmaster主进程。这个进程是PG数据库的总控制进程,负责启动和关闭数据库实例。实际上Postmaster进程是一个指向postgres命令的链接,如下:...转载 2018-03-30 13:16:15 · 565 阅读 · 0 评论 -
Postgresql之通过wal日志恢复数据到任意时间点
wal日志备份目录规划1234/home/mnt2/postgresql_basebak/data #基础备份目录/home/mnt2/postgresql_recover/data #还原目录/home/mnt2/postgresql_increase_basebak/ #每个月基础备份目录/home/mnt2/postgresql_walbak/ #wal日志备份目录将mnt2下所有内容赋权...原创 2018-03-30 13:31:14 · 4751 阅读 · 1 评论 -
Postgresql多行转一行
postgres=# select * from book2; id | name | tag ----+------+----- 1 | java | aa 1 | java | bb 1 | java | cc 2 | C++ | dd 2 | C++ | ee (5 rows) string_agg(字段名,分隔符)postgres=# sele...转载 2018-03-27 12:59:17 · 1363 阅读 · 0 评论 -
Postgresql事务笔记
事务:由一个或多个sql语句组成,使系统从一个一致状态到另一个一致状态,或者两种状态的更新都同时工作,或同种状态都应该回滚。ACID属性:A 原子性:作为一个整体,要么都做,要么都不做;C 一致性:从一个一致的状态到另一个一致的状态;I 隔离性:用户的操作不会受到同一时间段内其他用户操作的影响。D:持久性:事务提交后,即使操作系统故障、硬件故障等,它仍将保持一个一致的状态。实现方法:使用预写式日志...原创 2018-03-27 17:11:41 · 455 阅读 · 0 评论 -
Postgresql用户权限笔记
用户与权限:CREATE USER test WITH PASSWORD 'test';CREATE DATABASE testdb OWNER test;建用户和角色CREATE USER my_user;CREATE ROLE my_role;用户可以登录psql -U my_user -d postgres角色不能登录,以下语句会报错psql -U my_role -d postgres需要...原创 2018-03-27 17:45:19 · 790 阅读 · 0 评论 -
Postgresql之数据库优化参数设置
比较常见的几个数据库参数配置:1.share_buffer大的shared_buffers需要大的checkpoint_segments,同时需要申请更多的System V共享内存资源. 并且增加共享内存管理的开销.这个值不需要设的太大, 因为PostgreSQL还依赖操作系统的文件系统cache来提高读性能, 另外, 写操作频繁的数据库这个设太大反而会增加checkpoint压力.在9.4版本中...原创 2018-03-28 15:27:15 · 1433 阅读 · 0 评论 -
Postgresql数组类型的简单实用
1.建表MH=> create table test2(id int,while_list text[]);CREATE TABLEMH=> insert into test2 values(1,'{aa,bb,cc}');INSERT 0 1MH=> select * from test2; id | while_list ----+------------ ...原创 2018-04-19 20:50:20 · 3137 阅读 · 0 评论 -
Postgresql的pl/pgql用法--将多条执行语句作为一个事务
DO $$ DECLARE v_id bigint; begin INSERT INTO r_test_a (name, value, unit, mode, uid, create_ts) SELECT 't_t','','',2,0, extract(epoch from now())::bigint WHERE NOT EXISTS (SELECT id FROM r_...原创 2018-05-09 15:03:29 · 2742 阅读 · 0 评论 -
Postgresql之autovacuum分析及表的垃圾数据查看
转载:http://blog.163.com/digoal@126/blog/static/163877040201343031118890/PostgreSQL数据库日常维护需要维护哪些东西, 和数据库中的业务类型有莫大的关系.PostgreSQL的并发控制简单来说是通过多tuple版本, tuple infomask信息, 事务提交状态以及事务snapshot来实现的.转载 2018-02-05 13:37:06 · 1125 阅读 · 0 评论 -
Postgresql AutoVacuum系统自动清理进程
《PostgreSQL数据库内核分析》第2章PostgreSQL的体系结构,本章从宏观上对PostgreSQL的控制和处理流程进行了简要介绍,说明了各个模块之间是如何协同工作,以使得整个数据库系统能够稳定、正确地处理用户的各种操作和请求的。至于每个模块是如何各司其职,其内部具体是如何运作的,将会在后续的章节进行专门的介绍。本节为大家介绍AutoVacuum系统自动清理进程。AD:51CTO转载 2018-02-04 18:54:20 · 699 阅读 · 0 评论 -
postgres创建索引分析
查看每列的散列程度select tablename,attname,n_distinct,correlation from pg_stats where tablename='table_name';n_distinct解释 -1表示唯一,也就是说这个列的每一行都不一样. >=1时,表示这个列有多少唯一值. correlation解释 表示该列与数据堆存储的线原创 2017-12-15 11:33:55 · 1017 阅读 · 0 评论 -
postgresql日期相关的截取和计算
select half_month ,case when half_month like '%15' then all_pv/15 when half_month like '%28' then all_pv/13 when half_month like '%30' then all_pv/15 when half_month like '%31' then al原创 2017-05-31 20:46:51 · 3124 阅读 · 0 评论 -
postgresql随笔
1,.cast(cast(b.f_date as text) as date )+ 3652.本机按照postresql默认用户名密码用户名:postgres密码:postgres数据库:postgres原创 2017-06-05 19:04:46 · 530 阅读 · 0 评论 -
将Excel中的数据转换成sql Insert语句
excel表格中有A、B、C三列数据,希望导入到数据库users表中,对应的字段分别是name,sex,age ,在你的excel表格中增加一列,利用excel的公式自动生成sql语句,方法如下:1、在你的excel表格中增加一列2、在第一行的D列,就是D1中输入公式: =CONCATENATE("INSERT INTO PERSONS_SERIAL_NUMBER(PERSONS_NO,原创 2017-09-24 17:29:18 · 757 阅读 · 0 评论 -
PostgreSQL的递归查询(with recursive)
转载:https://my.oschina.net/Kenyon/blog/55137开发有需求,说需要对一张地区表进行递归查询,Postgres中有个 with recursive的查询方式,可以满足递归查询(一般>=2层)。 测试如下:create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); inse转载 2017-09-01 15:47:39 · 745 阅读 · 0 评论 -
postgresql在json类型上建索引进行测试
建表:create table jtest01(id int,jdoc json);----------------------------------------------------------------------------------create or replace function random_string(INTEGER)RETURNS TEXT AS$BOD原创 2017-10-11 16:49:56 · 2422 阅读 · 0 评论 -
Postgresql逻辑结构整理
数据库:创建数据库:create database name[with owner = user_name --指定新建数据库属于哪个用户,不指定则属于当前执行命令的用户;template = template --模板名,不指定则默认使用 template1.encoding = encoding --数据库使用的字符编码, 编码与区域设置必须与模板相一致。template原创 2017-10-17 21:27:25 · 1256 阅读 · 0 评论 -
postgresql核心架构--安装后的目录介绍
进程和内存架构图:主进程:Postmaster 位于安装目录的bin目录下,主进程是整个数据库实例的总控进程,负责启动和关闭该数据库实例。可以运行postmaster、postgres命令并加上合适的参数启动数据库,实际上postmaster命令是一个指向postgres的链接。更多的时候是通过pg_ctl来启动数据库,pg_ctl也是通过运行postgres来启动数据库的。原创 2017-10-18 17:48:47 · 2414 阅读 · 0 评论 -
postgresql在centos上安装与配置
解压二进制包建 /usr/postgresql目录tar -xzvf postgresql-9.6.5-1-linux-x64-binaries.tar.gz-------------------------------------------------------------(1)新增postgres用户组:groupadd postgres(2)新增postgres用原创 2017-09-22 10:04:49 · 469 阅读 · 0 评论 -
psql常用的命令
psql命令:1.拷贝数据到excel;postgres=# \copy (select * from user_test) to H:\user_test.csv with (FORMAT CSV);COPY 5Time: 0.996 mstestdb2=# \copy (select * from t_user) to /home/postgres/test.csv wit原创 2017-10-10 17:05:03 · 2389 阅读 · 0 评论 -
Postgresql服务管理
服务的启停和创建启停切换到bin目录下:pg_ctl命令启动,重启,关机[postgres@hserver1 bin]$ ./pg_ctl -D /usr/postgresql/psql/data startserver starting[postgres@hserver1 bin]$ LOG: database system was shut down at 2017-原创 2017-10-20 16:00:25 · 1776 阅读 · 0 评论 -
postgresql数据类型
select * from t_user;--类型输入与转换select 1,1.343, 'hello world';--类型名+单引号select int'1' + int'2';--cast方式select cast(5 as int),cast('2014--7-17' as date);--双冒号的方式select '5'::int,'2017-原创 2017-10-11 15:18:31 · 1692 阅读 · 0 评论 -
Postgresql执行计划学习
Postgresql执行计划:testdb2=# explain select * from test; QUERY PLAN -------------------------------------------------------- Seq Scan on test (cost=0.00..1原创 2017-10-23 13:51:47 · 671 阅读 · 0 评论 -
vacuum清理数据库
转载地址:http://www.91linux.com/html/article/guanli/20070621/2902.html1. 综述为了保持所安装的 PostgreSQL 服务器平稳运行, 我们必须做一些日常性的维护工作。我们在这里讨论的这些工作都是经常重复的事情, 可以很容易地使用标准的 Unix 工具,比如cron 脚本来实现。 不过,设置合适的脚本以及检查它们是否成功执转载 2017-11-14 20:44:43 · 2721 阅读 · 0 评论 -
psql命令参考
psql命令行快速参考转载:http://blog.csdn.net/dyx1024/article/details/6912530psql的命令语法是:psql [options] [dbname [username]]psql命令行选项以及它们的意思在表1-1中列出。使用以下命令可以看到psql完整的选项列表:$ psql --help转载 2017-05-12 09:33:29 · 663 阅读 · 0 评论