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用的第一种。)
一部分关于pg的小知识
最新推荐文章于 2024-06-20 20:59:47 发布