一部分关于pg的小知识

1、修改监听的IP和端口号 需要重启生效。
在数据目录下编辑postgresql.conf文件，可以修改IP和端口号。
2、logging_collector = on;    日志收集是否打开。   Syslogger（系统日志）进程
3、log_directory = 'pg_log';    日志的目录
4、日志切换和覆盖的方式
log_filename= 'postgresql-%Y-%m-%d_%H%M%S.log'     文件名
log_truncate_on_rotation = off                                            是否循环覆盖
log_rotation_age = 1d                                                          切换日志的间隔
log_rotation_size = 10M                                                       日志最大值
5、SQL语句：
select * from student where age>22 order by age;      查询表
drop table student;                                                        删除表
delete from
create table student(no int primary key,student_name varchar(40),age int,class_no int);   创建表
insert into student values(8,'张四',14,4);                        插入数据
insert into student_bak select * from student;              表复制
select * from student where no=1 union select * from student_bak where no=2;
查询，使用union链接两个SQL语句。会合并数据，如果不想合并，请使用union all。
truncate table student_bak;  和删除表delete差不多，执行结果相同，但是功能不同。delete是一行行的删除数据，truncate相当于是直接重新定义一个新的表，原先表的内容直接丢弃了。
6、psql      安装pg时，会建立一个与初始化数据库时的操作系统用户同名的数据库用户，并且是数据库的超级用户，在这个用户下登录数据库执行操作系统认证，所以不需要输入用户名密码。（也可以通过修改pg_hba.conf文件来要求输入密码。）
7、psql -l  查看所有数据库
   create database testdb;   新建数据库
   \c testdb;    连接数据库
psql -h 192.168.1.11（目标端，也就是数据库所在服务器的IP地址） -p 5432 testdb postgres
psql 连接数据库的方法
也可以由环境变量指定，指定后直接psql与以上命令相同。
8、
比\d更详细的信息                \d+  tablename
匹配的表                              \dt
索引                                     \di
序列                                     \ds
视图                                     \dv
函数                                     \df
schema                                \dn
所有表空间                            \db 
所有的角色或用户                 \du(\dg)
表的权限分配                         \dp tablename  （\z）
9、\timing on   SQL执行时间计时
经过简单测试，本次会话有效，退出后无效。
10、\encoding       指定客户端的字符编码            例如：\encoding    utf8 
11、
\pset border 0; 输出内容无边框
\pset border 1; 边框只在内部
\pset border 2; 内外都有
经过简单测试，本次会话有效，退出后无效。
12、\x 将表中的行拆分为单行展示。
        \i <文件名>执行存储在外部文件中的sql语句。（在psql交互模式下）
以下是在命令行模式下执行的命令：
 psql -x -f getrunsql   -x就相当于在psql交互模式下运行\x命令。
\echo 输出信息，一般用于sql脚本的文件中输出信息。
\echo ====
select * from student;
\echo ====
13、psql中事务自动提交。如果不想自动提交，有两种方式，一种是begin;然后执行命令，最后commit或者是rollback。
另一种方式，直接关闭自动提交的功能。
\set AUTOCOMMIT off (必须大写，小写不报错，但是不生效。)
\echo :AUTOCOMMIT检查是否生效。
经过简单测试，本次会话有效，退出后无效。
14、psql -E postgres 指定数据库 并且显示"\"开头的命令执行的实际SQL打印出来。如下图：
\set ECHO_HIDDEN on|off   用来关闭或打开这个功能。

15、网络地址类型：
cidr IPv4或IPv6的网络地址
inet IPv4或IPv6的网络地址或主机地址
macaddr 以太网MAC地址
16、
PG中一个实例可以有多个数据库，一个数据库只能属于一个实例。
oracle中一个实例只能有一个数据库，一个数据库可以有多个实例。（RAC）
17、
pg中模式可以理解为一个命名空间或者是目录。只要有权限，每个模式的对象可以互相调用。
这与Mysql中database的概念等同。
oracle中一个用户就对应一个schema。
18、查看当前用户
select * from current_user;
20、
create table baby (like child including all); 
以child为模板创建新表，并且复制所有。
21、pg中临时表在会话结束时消失。
oracle不同，oracle中只是临时表中的数据消失，而临时表还在。
22、
create table student( class_no int)inherits (persons);
继承表中，查询父表时，会把子表的数据也查不出来，反之则不行。
父表的检查约束和非空约束会自动继承，其他约束不会。
如果想要仅查询父表的数据，可以使用select * from only persons;
23、语句级触发器和行级触发器不同。
触发器函数中  TG_OP是特殊变量，代表DML操作类型。
before 和after
pg中删除触发器要指定on table。
drop trigger name on table;
pg9.3以后开始支持事件触发器，主要用于弥补之前不支持DDL触发器的功能。
只有超级用户才可以创建和修改事件触发器。
24、
pg中表空间实际上是为表制定一个存储的目录，在创建数据库时可以
为数据库指定默认表空间，创建表和索引的时候也可以指定表空间，这样
表、索引就可以存储到表空间对应的目录了。
create tablespace tablespace_name owner user_name location 'directory';
创建表空间并且指定属主和目录。
25、
视图使复杂的查询易于理解和使用，可以屏蔽一部分敏感字段。
索引可以加快对表中记录的查找和排序，但会增加数据库的存储空间，同时
插入和更新数据会花费时间，因为索引要随之更新。
并发创建索引。 concurrently
26、
pg中角色和用户是没有太大区别的。
create user name 创建的用户默认有login权限，
create role name  没有login权限，没有其他任何区别。
27、pg中的权限按以下层次进行管理。
①首先管理负载用户特殊属性上的权限，如超级用户的权限，创建数据库的权限，创建用户的权限，login的权限，等等。
②然后是在数据库中创建模式的权限。
③接着是在模式中创建数据库对象的权限，如创建表、创建索引等等
④之后是查询表、往表中插入数据、更新表、删除表中数据的权限。
⑤最后是操作表中某些字段的权限。
28、
对于一组相关操作通常需要其全部成功或全部失败，关系型数据库中，这组相关操作称之为事务。
事务的四个特性。
原子性：事务必须以一个整体单元的形式进行，对于其数据的修改，要么全部执行，要么全都不执行。
一致性：事务完成时，必须使所有的数据都保持一致状态。
隔离性：事务查看数据时数据所处的状态，要么是另一事务修改之前的状态，要么是修改之后的状态。
持久性：事务完成后面对系统的影响是永久性的。即使季候出现致命的系统故障，数据也将一直保持。
29、
pg中与其他数据库最大的区别是，大多数DDL可以包含在一个事务中，而且也是可以回滚的。
30、
select pid,usename,client_addr,client_port from pg_stat_activity;
查看链接数据库的进程id和用户，和客户端地址。
31、只有在配置文件postgres.conf中修改logging_collect设置为on时，主进程才会启动SysLogger（系统日志）辅助进程。
pg中，BgWriter（后台写）进程把共享内存中的脏页写到磁盘上。
WAL 是Write Ahead Log的缩写。预写式日志。之前简称xlog。修改数据前，必须把这些修改操作记录到磁盘中。 WAL日志保存在pg_log下，默认大小16MB。会自动覆盖，会产生多个。
PgArch（归档）进程：WAL日志会被循环使用，所以较早时间的WAL日志会被覆盖。PgArch归档进程会在覆盖前把WAL日志备份出来。PITR（Point-In-Time-Recoery）技术 全量备份+WAL日志，可以将数据库向前推到全量备份后的任意一个时间点。
AutoVacuum（自动清理）进程：PG中delete后，旧的数据不会立即被删除，只有当没有并发事务读后才会被清除掉，这个清楚工作就是AutoVacuum进程来完成的。
Pgstat（统计数据收集）进程，主要是数据的统计收集工作。主要用于查询优化时的代价估算，系统表pg_statistic中存储了收集的各类统计信息。
32、
postgresql.conf  数据库实例的主配置文件，基本上所有的配置参数都在此文件中。
pg_hba.conf       认证配置文件，配置了允许哪些IP的主机访问数据库，认证方法是什么等信息。
base：                默认表空间的目录。
pg_log:               系统日志目录。查询一些系统错误时，可以查看此目录下的日志文件。
pg_xlog：           WAL日志的目录。 
33、
pg_ctl是一个实用工具
  ● 可以初始化pogtgresql数据库实例
  ● 启动、终止或者重启postgresql数据库服务
  ● 查看postgresql数据库服务的状态
  ● 让数据库实例重新读取配置文件
  ● 允许给一个指定的进程发送信号
  ● 在windows平台下允许为数据库实例注册一个系统服务或取消这个系统服务。
34、
windows平台下注册服务和取消服务的命令如下
pg_ctl register 
pg_ctl unregister 
-N servicename： 要注册的系统服务名
-P password： 用户启动服务的密码      
-U ：用于启动服务的用户名
35、
PostgreSQL的配置参数是在postgresql.conf文件中集中管理的，喂鱼数据库实例的目录下（$PGDATA），大小写不敏感。所有配置参数都在数据库的系统视图pg_settings中。

internal：只读参数，初始化实例时已经确定。不出现在postgres.conf中。
postmaster：改变参数的值需要重启postgresql实例。在postgres.conf文件中改变后，需要重启。
sighup：可以在postgres.conf中改变参数值，不用重启数据库，只需向postmaster进程发送sighup信号，重新装载配置新的参数就可以。
backend：大致同上，但是已有连接中，这些参数不变，只在新连接中生效。
superuser：只会影响自身的新连接。
user:除了普通用户都可以改变外，这类参数与superuser类没区别。
36、数据库备份有多种方式：逻辑备份和物理备份。
物理备份包括pg本身提供的WAL日志之上的热备份功能，以及使用物理存储快照备份。
逻辑备份：
pg_dump、pg_dumpall差不多，不过后者是将一个postgresql数据库集群全部转储到一个脚本文件。
pg_dump 可生成归档格式的备份文件，与pg_restore配合使用。
pg_dump -h host 指定运行服务器的主机名。
                -p port 指定端口号
                -U username 连接用户名
                 dbname 指定数据库名
pg_dump restore 和pg_dump差不多用法。
有个不同点是-d dbname 指定数据库。
37、
解压目录下：/opt/postgresql-9.4.4/contrib  放着很多工具，   直接make。
然后make install   
然后select * from pg_available_extensions;查看安装的扩展工具
create extension name;
就可以使用了。
38、
参数log_statement_stat控制是否输出所有SQL语句的统计信息。
默认的PostgreSQL配置中，autovacuum守护进程是打开的,他能自动的分析表，并收集标的统计信息。当autovacuum关闭时，需要周期性的运行ANALYZE命令，比较常用的策略是每天在数据库比较空闲的时候运行一次VACUUM和ANALYZE。ANALYZE会在表上加一个读锁。
39、
oid是字段生成的序列值是全局的。pg官方不鼓励用户创建表中使用oid字段。
ctid表示数据行在它所处的表内的物理位置。ctid字段的类型是tid。
40、多版本并发控制(MVCC)
数据库中并发访问数据时保证数据一致性的方法。
原理：
为了规避数据不一致问题，使用读写锁，但是读写不能并发。为了让读写并发，使用MVCC。
方法一：
写新数据时，将旧数据移到一个单独的地方，如回滚段，其他人读数据是，从回滚段中把旧的数据读出来。
方法二：
写新数据时，旧数据不删除，而是把新数据插入。（PostgreSQL数据库使用的这种，oralce和Mysql用的第一种。）