Oracle DBA的PostgreSQL之路
文章平均质量分 80
主要介绍Postgres&HGDB的基础知识,从入门开始。
丹心明月
路上,期待不一样的精彩。
展开
-
Views
3.2. ViewsRefer back to the queries in Section 2.6. Suppose the combined listing of weather records and city location is of particular interest to your application, but you do not want to type the q...翻译 2019-06-13 11:18:04 · 999 阅读 · 0 评论 -
Introduction
3.1. IntroductionIn the previous chapter we have covered the basics of using SQL to store and access your data in PostgreSQL. We will now discuss some more advanced features of SQL that simplify man...翻译 2019-06-10 17:11:24 · 656 阅读 · 1 评论 -
Deletions
2.9.DeletionsRows can be removed from a table using the DELETE command. Suppose you are no longer interested in the weather of Hayward. Then you can do the following to delete those rows from the t...翻译 2019-06-10 16:17:20 · 541 阅读 · 0 评论 -
Updates
2.8.UpdatesYou can update existing rows using the UPDATE command. Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as follows:UPD...翻译 2019-06-10 13:51:10 · 1187 阅读 · 0 评论 -
Aggregate Functions
2.7. Aggregate FunctionsLike most other relational database products, PostgreSQL supports aggregate functions. An aggregate function computes a single result from multiple input rows. For example, t...翻译 2019-06-10 12:27:43 · 1337 阅读 · 0 评论 -
Joins Between Tables
2.6. Joins Between TablesThus far, our queries have only accessed one table at a time. Queries can access multiple tables at once, or access the same table in such a way that multiple rows of the ta...翻译 2019-05-17 17:36:18 · 235 阅读 · 0 评论 -
Querying a Table
2.5. Querying a TableTo retrieve data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into a select list (the part that lists the columns to ...翻译 2019-05-17 14:37:09 · 297 阅读 · 0 评论 -
Populating a Table With Rows
2.4. Populating a Table With RowsThe INSERT statement is used to populate a table with rows:INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25,'1994-11-27');Note that all data types use r...翻译 2019-05-16 17:14:09 · 214 阅读 · 0 评论 -
Creating a New Table
2.3. Creating a New TableYou can create a new table by specifying the table name, along with all column names and their types:CREATE TABLE weather (city varchar(80),temp_lo int, -- low tempera...翻译 2019-05-16 16:36:20 · 346 阅读 · 0 评论 -
Concepts
2.2. ConceptsPostgreSQL is a relational database management system (RDBMS). That means it is a system for managing data stored in relations. Relation is essentially a mathematical term for table. Th...翻译 2019-05-16 15:20:41 · 739 阅读 · 0 评论 -
Introduction
2.1. IntroductionThis chapter provides an overview of how to use SQL to perform simple operations. This tutorial is only intended to give you an introduction and is in no way a complete tutorial on ...翻译 2019-05-15 15:20:04 · 603 阅读 · 0 评论 -
Accessing a Database
1.4. Accessing a DatabaseOnce you have created a database, you can access it by:• Running the PostgreSQL interactive terminal program, called psql, which allows you to interactivelyenter, edit, a...翻译 2019-05-15 14:25:07 · 323 阅读 · 0 评论 -
Creating a Database
1.3. Creating a DatabaseThe first test to see whether you can access the database server is to try to create a database. A running PostgreSQL server can manage many databases. Typically, a separa...翻译 2019-04-30 15:14:23 · 491 阅读 · 0 评论 -
Architectural Fundamentals
1.2. Architectural FundamentalsBefore we proceed, you should understand the basic PostgreSQL system architecture. Understanding how the parts of PostgreSQL interact will make this chapter somewhat c...翻译 2019-04-19 22:27:06 · 165 阅读 · 0 评论 -
Installation
1.1. InstallationBefore you can use PostgreSQL you need to install it, of course. It is possible that PostgreSQL is already installed at your site, either because it was included in your operating s...翻译 2019-04-19 17:49:33 · 1230 阅读 · 0 评论 -
Tutorial
Part I. TutorialWelcome to the PostgreSQL Tutorial. The following few chapters are intended to give a simple introduction to PostgreSQL, relational database concepts, and the SQL language to those w...翻译 2019-04-18 12:55:29 · 1354 阅读 · 0 评论 -
Bug Reporting Guidelines
When you find a bug in PostgreSQL we want to hear about it. Your bug reports play an important part in making PostgreSQL more reliable because even the utmost care cannot guarantee that every part of ...翻译 2019-04-18 11:08:22 · 516 阅读 · 0 评论 -
Further Information
Besides the documentation, that is, this book, there are other resources about PostgreSQL:Wiki The PostgreSQL wiki5 contains the project's FAQ6 (Frequently Asked Questions) list, TODO list, ...翻译 2019-04-17 11:00:52 · 448 阅读 · 0 评论 -
Conventions
The following conventions are used in the synopsis of a command: brackets ([ and ]) indicate optional parts. (In the synopsis of a Tcl command, question marks (?) are used instead, as is usual in Tcl....翻译 2019-04-17 10:24:31 · 900 阅读 · 0 评论 -
A Brief History of PostgreSQL
The object-relational database management system now known as PostgreSQL is derived from the POSTGRES package written at the University of California at Berkeley. With over two decades of development ...翻译 2019-04-16 22:13:37 · 951 阅读 · 0 评论 -
What is PostgreSQL?
PostgreSQL is an object-relational database management system (ORDBMS) based on POSTGRES,Version 4.2, developed at the University of California at Berkeley Computer Science Department.POSTGRES pioneer...翻译 2019-04-16 14:17:57 · 305 阅读 · 0 评论 -
Preface
This book is the official documentation of PostgreSQL. It has been written by the PostgreSQL developers and other volunteers in parallel to the development of the PostgreSQL software. It describes all...翻译 2019-04-16 13:50:29 · 477 阅读 · 0 评论 -
PostgreSQL 11.2 Documentation--版权说明及责任
Legal NoticePostgreSQL is Copyright © 1996-2019 by the PostgreSQL Global Development Group.Postgres95 is Copyright © 1994-5 by the Regents of the University of California.Permission to use, c...原创 2019-04-12 17:36:24 · 454 阅读 · 0 评论 -
PostgreSQL 11.2 Documentation学习启动
想提高自己的英语水平,昨晚逛手机,激动的想找一本全英文的书籍查看。找了很久,太简单的不屑于看,太难的又看不懂。很忧伤。忽然想起来,直接看数据库官方文档不就好了,正好一石两鸟,学习英语又精进技术,perfect。正好公司是基于开源PostgreSQL做国产数据库HGDB的基础软件公司,所以决定开始看官方文档。因为我起步比较晚,直接看是学习最新的版本,于是定下来:《PostgreSQL 1...原创 2019-04-12 17:35:05 · 410 阅读 · 0 评论 -
PostgreSQL体系结构
PostgreSQL数据库是由一系列位于文件系统上的物理文件组成,在数据库运行过程中,通过整套高效严谨的逻辑管理这些物理文件。通常将这些物理文件称为数据库,将管理这些物理文件的进程、进程管理的内存称为这个数据库的实例。在PostgreSQL的内部功能实现上,可以分为系统控制器、查询分析器、事务系统、恢复系统、文件系统这几部分。其中系统控制器负责接收外部连接请求,查询分析器对连接请求查询进行分...原创 2019-04-15 18:52:05 · 627 阅读 · 0 评论 -
postgresql与Oracle:空字符串与null
空字符串:两个单引号,中间无空格等任何内容在postgresql中,空字符串与null是不同的;而oracle中,空字符串与null等同。测试如下:postgresql中:postgres=# insert into testnull values(1,'feikong');INSERT 0 1postgres=# insert into testnull values(2,null原创 2017-06-20 16:00:30 · 8145 阅读 · 0 评论 -
postgresql的now()与Oracle的sysdate
postgresql的now()为当前事务开始时间,而Oracle的sysdate是当前时间。区别在于事务。postgresql中的now():postgres=# begin ;BEGINpostgres=# select now(); now ------------------------------- 2017-03-原创 2017-06-20 11:38:32 · 5045 阅读 · 0 评论 -
pg_buffercache
查看缓冲区缓存的内容:create extension pg_buffercache;select c.relname, count(1) as buffers from pg_class c join pg_buffercache b on b.relfilenode=c.relfilenode inner join pg_database d on (b.reldatabase=d.oi原创 2017-05-05 15:40:28 · 1075 阅读 · 0 评论 -
pg limit限制返回的行
limit 20:返回结果集中的前20行offset 20 limit 20:返回结果集中前40行中的后20行示例:创建测试表:postgres=# create table test_limit(id int,name text);CREATE TABLE插入数据:postgres=# insert into test_limitselect generate_series(1原创 2017-03-31 17:10:22 · 5779 阅读 · 0 评论 -
pg确定一张表最后被使用的时间
create or replace function table_file_access_info(IN schemaname text,IN tablename text,OUT last_access timestamp with time zone,OUT last_change timestamp with time zone) LANGUAGE plpgsql AS $func$DECL原创 2017-03-28 09:34:53 · 1706 阅读 · 0 评论 -
pg审计DDL
首先确认log_statement设置为了ddl或者mod或者all修改参数需要重新加载配置文件。在日志文件中查找所有出现的create、alter和drop命令:egrep -i "create|alter|drop" /log_dir如果开启了日志轮换,那么还需要在所有的日志文件中过滤这些内容。(是pg_log中的日志文件)测试:修改参数文件:[postgres@pg d原创 2017-03-13 09:55:08 · 1013 阅读 · 0 评论 -
将数据从CSV文件导入PG数据库
csv文件内容:[postgres@pg ~]$ vi PGOracle.csv 1,2,34,5,67,8,9创建了新表:postgres=# create table testim(col1 text,col2 text,col3 text);CREATE TABLE从文件导入数据:postgres=# copy testim from '/home/postgres原创 2017-03-09 16:18:02 · 10791 阅读 · 0 评论 -
PG索引创建与Oracle的不同
刚发现个有趣的地方,pg可以给同一表的相同列创建相同索引N次!太疯狂了!postgres=# create table testind(id int);CREATE TABLEpostgres=# create index on testind (id);CREATE INDEXpostgres=# create index on testind (id);CREATE IN原创 2017-03-09 14:49:05 · 1821 阅读 · 0 评论 -
PG表名测试
postgres=# create table "myTable" (id int);CREATE TABLEpostgres=# select * from mytablepostgres-# ;ERROR: relation "mytable" does not existLINE 1: select * from mytable ^p原创 2017-03-08 13:49:45 · 1166 阅读 · 2 评论 -
pg为特定用户组设置参数
为特定用户组设置参数:数据库里面的所有用户:alter database db_name set param=val;为某个用户:alter role role_name set param=val;为特定用户连接到特定数据库:alter role role_name in database db_name set param=val;简单示例:postgres=# alter d原创 2017-03-02 10:50:52 · 875 阅读 · 0 评论 -
PostgreSQL学习篇16.3 检查备库及流复制情况
检查异步流复制情况:主库查询:select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;postgres=# \d pg_stat_replication; View "pg_catalog.pg_stat_replication" Column |原创 2017-02-13 16:36:33 · 3633 阅读 · 0 评论 -
PostgreSQL学习篇16.2 同步流复制的standby数据库
从9.1后,提供了同步流复制的架构。同步复制要求在数据写入standby数据库后,事务的commit才返回,所以standby库出现问题时,会导致主库hang住。可以启动两个standby数据库,只要有一个是正常的,主库就不会hang住。但是因为资源限制,这里只配置一个standby库。环境:主机名IP地址角色数据目录pg186.168.100.14主库/PostgreSQL/原创 2017-02-10 22:03:38 · 1210 阅读 · 0 评论 -
psql: FATAL: the database system is starting up
pg9.6.1在搭建同步流复制的standby库时,启动备库时,报错:[postgres@pghs ~]$ psqlpsql: FATAL: the database system is starting up后经检查,是postgresql.conf中hot_standby没有设置:hot_standby = on成功: [postgres@pghs ~]$ psqlpsq原创 2017-02-10 21:55:15 · 19164 阅读 · 2 评论 -
PostgreSQL学习第十六篇 异步流复制Hot Standby的示例
配置环境:主机名IP地址角色数据目录pg186.168.100.14主库/PostgreSQL/9.6.1/datanpghs186.168.100.24standby/PostgreSQL/9.6.1/datahs主数据库的配置:允许主库接受流复制的连接pg_hba.conf中:host replication postgres 186原创 2017-02-09 16:03:32 · 966 阅读 · 0 评论 -
PostgreSQL学习篇15.1 数据库配置优化
内存配置优化:PG中与内存有关的配置参数:shared_buffers:共享缓存区的大小,相当于Oracle中的SGA,推荐为内存的1/4,不超过总内存的1/2。从PG9.3开始,共享内存已从System V方式改为了Posix方式和mmap方式,因此在pg9.3以后不需要配置Linux的shmmax和shmall参数,之前的版本还需要配置。shmmax:表示单个共享内存段可以创建的最大值,s原创 2017-02-08 09:40:04 · 1895 阅读 · 0 评论