Postgresql学习笔记(二)

一、使用psql

psql   psql test  --登录test
psql -l 列出数据库
\c  连接数据库
\d 查看表
psql -h 192.168.56.11 -p 5432 -d test postgres
\h --命令
\d t  表结构 desc
\dt  \di \dv \ds \df
\timing on   set timing on
\dn  schema
\db  表空间
\du  \dg 用户、角色 两个命令一个意思
\dp \z  权限分配情况
\encoding gbk  \encoding utf8 设置客户端字符集
\pset broder 2\0\1  --设置显示边框
\pset format unaligned  分隔符格式化
\pset fieldsep '\t'  分隔符为tab
\pset format unaligned 
\o  111.txt 输出到文件  spool
\x 转置
\e 123.sql  编辑文件 ed
\ef 编辑函数
\ev  编辑视图
\reset 
\echo   
\i a.sql 相当与oracle的@
\? 显示帮助

1.1、技巧

使用技巧
\d  tab补全
自动提交
1、begin;  commit;
2、\set AUTOCOMMIT off 关闭自动提交，注意大小写
在启动psql的命令行中加上“-E”参数，就可以把psql中各种以“\”开头的命令执行的实际SQL语句打印出来，
已运行的psql中显示了某个命令实际执行的SQL语句后又想关闭此功能，可以使用“\set ECHO_HIDDEN on|off”命令

二、逻辑结构管理

2.1、创建数据库

CREATE DATABASE name
  [ [ WITH ] [ OWNER [=] user_name ]
    [ TEMPLATE [=] template ]
    [ ENCODING [=] encoding ]
    [ LC_COLLATE [=] lc_collate ]
    [ LC_CTYPE [=] lc_ctype ]
    [ TABLESPACE [=] tablespace ]
    [ CONNECTION LIMIT [=] connlimit ] ]

2.2、修改数据库

ALTER DATABASE name RENAME TO new_name。 --重命名
ALTER DATABASE name OWNER TO new_owner。
ALTER DATABASE name SET TABLESPACE new_tablespace。--设置表空间
ALTER DATABASE name SET configuration_parameter {TO |=} {value|DEFAULT}。
ALTER DATABASE name SET configuration_parameter FROM CURRENT。 --修改参数
ALTER DATABASE name RESET configuration_parameter。--和oracle reset同一个意思
ALTER DATABASE name RESET ALL。

alter database test CONNECTION LIMIT 10; --控制连接数

DROP DATABASE [ IF EXISTS ] name;--删除数据库

select * from pg_stat_activity where DATNAME = 'test'; --相当于v$session
--修改数据库时提示有会话连接，查找并踢出会话

2.3 模式

CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ]
ALTER SCHEMA name RENAME TO newname
ALTER SCHEMA name OWNER TO newowner
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
create table test01(id int, note varchar(20));
create table test01(id int primary key, note varchar(20));
create table test02(id1 int, id2 int, note varchar(20), CONSTRAINT pk_test02 primary key(id1,id2));
create table test03(id1 int, id2 int, id3 int, note varchar(20), CONSTRAINT pk_test03 primary key(id1,id2), CONSTRAINT uk_test03_id3 UNIQUE(id3));
CREATE TABLE child(name varchar(20), age int, note text,  CONSTRAINT ck_child_age CHECK(age <18));

2.4、用户和角色

create role name with xxxx;
create user name with xxxx;

SUPERUSER|NOSUPERUSER
CREATEDB|NOCREATEDB
CREATEROLE|NOCREATEROLE
CREATEUSER|NOCREATEUSER
INHERIT|NOINHERIT
LOGIN|NOLOGIN：创建出来的用户是否有LOGIN权限，可以临时禁止用户的LOGIN权限，此时用户无法连接到数据库。
CONNECTION LIMIT connlimit：这个参数指明了该用户可以使用的并发连接的数量。默认值是“-1”，表示没有限制
ENCRYPTED|UNENCRYPTED]PASSWORD 'password'：用于控制存储在系统表中的口令是否加密
VALID UNTIL 'timestamp'：密码失效时间，如果不指定该子句，那么口令将永远有效。
IN ROLE role_name [,...]：指定用户成为哪些角色的成员，请注意，没有任何选项可以把新角色添加为管理员，只有使用独立的GRANT命令才行
ROLE role_name [,...]：role_name将成为这个新建的角色的成员

2.5、权限管理

GRANT some_privileges ON database_object_type object_name TO role_name;
REVOKE some_privileges ON database_object_type object_name FROM role_name;
REVOKE  CREATE  ON SCHEMA public from public;
CREATE USER readonly with password 'query';
GRANT SELECT ON  ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to readonly;

GRANT SELECT ON  ALL TABLES IN SCHEMA other_schema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema grant select on tables to readonly;
pg_ctl reload -D $PGDATA

三、启动和关闭

postgres -D /home/osdba/pgdata &
pg_ctl -D /home/osdba/pgdata start

pg_ctl stop -D DATADIR -m smart：表示Smart Shutdown关机模式。 nomal
pg_ctl stop -D DATADIR -m fast：表示Fast Shutdown关机模式。 immedaite
pg_ctl stop -D DATADIR -m immediate：表示Immediate Shutdown关机模式 abort

直接向数据库的主进程发送的signal信号有以下3种。
·SIGTERM：发送此信号为Smart Shutdown关机模式。
·SIGINT：发送此信号为Fast Shutdown关机模式。
·SIGQUIT：发送此信号为Immediate Shutdown关机模式。

pg_ctl init[db] [-s] [-D datadir] [-o options]

 -s：只打印错误和警告信息，不打印提示性信息。
 -D datadir：指定数据库实例的数据目录。
 -o options：直接传递给initdb命令的参数，具体可见initdb命令的帮助信息。
 
pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c]
其参数说明如下。
·start：启动数据库实例。
·-w：等待启动完成。
·-t：等待启动完成的等待秒数，默认为60秒。
·-s：只打印错误和警告信息，不打印提示性信息。
·-D datadir：指定数据库实例的数据目录。
·-l：把服务器日志输出附加在filename文件上，如果该文件不存在则自动创建。
·-o options：声明要直接传递给postgres的选项，具体可见postgres命令的帮助信息。
·-p path：指定postgres可执行文件的位置。默认postgres可执行文件来自与pg_ctl相同的目录，不必使用该选项，除非进行一些特殊的操作，或者产生postgres执行文件找不到的错误。
·-c：提高服务器的软限制（ulimit -c），尝试允许数据库实例在发生某些异常时产生一个coredump文件，以便进行问题定位和故障分析。

pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]
其参数说明如下。
·-W：不等待数据库停止，就返回命令。
·-m：指定停止的模式。
select pg_backend_pid();
postgres --single -D /home/osdba/pgdata postgres  --单用户模式 oracle的限制模式

四、参数配置

4.1、参数类型

• internal：内部参数，初始化后一般不能修改
• postmaster：改变这类参数的值需要重启PostgreSQL实例。在postgresql.onf文件中可改变这些参数后，需要重启PostgreSQL实例修改才能生效。
• sighup：在postgresql.conf文件中改变这类参数的值不需要重启数据库，只需要向postmaster进程发送SIGHUP信号，让其重启装载配置新的参数值就可以。当然postmaster进程接收到SIGHUP信号后，也会向它的子进程发送SIGHUP信号，让新的参数值在所有的进程中生效。
• backend：在postgresql.conf文件中更改这类设置无须重新启动服务器，只需要向postmaster发送一个SIGHUP信号，让它重新读取postgresql.conf文件中新的配置值，但新的配置值只会出现在修改之后的新连接中，已有的连接中该参数的值不会改变。这类参数的值也可以在新建连接时由连接的一些参数改变。例如，通过libpq的PGOPTIONS环境变量可以改变本连接的配置值。
• superuser：这类参数可以由超级用户使用SET命令来改变，如检测死锁的超时时间的参数“deadlock_timeout”。而超级用户改变此参数值时只会影响自身的sesssion配置，不会影响其他用户关于此参数的配置。向Postmaster进程发送SIGHUP信号也只会影响后续创建的连接，不会影响已有的连接。
• user：这类参数可以由普通用户使用SET命令来改变本连接中的配置值。除了普通用户也可以改变外，这类参数与superuser类参数没有区别。alter session

4.2、连接参数

• listen_addresses：监听的ip，listener.ora文件，精细控制可以修改pg_hba.conf，相当于与sqlnet.ora
• port 端口
• max_connections：oracle的processes参数
• superuser_reserved_connections：为PostgreSQL超级用户连接而保留的连接数。默认值是“3” -prelim
• unix_socket_directory:服务器监听客户端连接的UNIX域套接字目录。该参数只能在编译时修改。默认值通常是“/tmp”
• unix_socket_group:UNIX域套接字的所属组
• unix_socket_permissions：设置UNIX域套接字的访问权限。改变该参数需要重启数据库服
• tcp_keepalives_idle:表示在一个TCP连接中空闲多长时间后会发送一个keepalive报文。默认值为“0”
• tcp_keepalives_interval:integer类型，在一个空闲TCP连接中，定义在发送第一个TCP keepalive包后，如果在该参数给定的时间间隔内没有收到对端的回包，则开始发送第二个TCP keepalive包，若在给定的时间间隔内仍未收到回包的话则发送第三个keepalive包，直到达到tcp_keepalives_count次后都没有收到回包，则认为连接已中断，关闭连接
• tcp_keepalives_count:空闲TCP连接上，发送keepalive包后，如果一直没有收到对端的回包，最多发送keepalive次报文后就认为TCP连接已中断。

4.3、内存参数

• shared_buffers：内存的25%，设置数据库服务器将使用的共享内存缓冲区数量，此缓冲区为数据块的缓存使用。此缓冲区是放在共享内存中的。类似buffer_cache
• temp_buffers：设置每个数据库会话使用的临时缓冲区的最大数目 pga或uga之类的参数
• work_mem：声明内部排序操作和Hash表在开始使用临时磁盘文件之前使用的内存数目 sort_area或者temp表空间
• maintenance_work_mem：声明在维护性操作，比如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY中使用的最大内存数以KB为单位 类似sga与temp表空间，配置AutoVacuum后，达到autovacuum_max_workers的时间，内存会被分配，因此也不要将默认值设置得太大，而当需要手动执行上述操作时，可以使用SET命令把此参数值设置得大一些。
• max_stack_depth：声明服务器的执行堆栈的最大安全深度

4.4 WAL日志参数

• wal_level：enum类型，可以选择的值为“minimal”“replica”“logical”，此配置项决定了多少信息写入WAL日志中。改变该参数需要重启数据库服务。默认值是“minimal”，即只写入在数据库崩溃或突然关机后进行恢复时所需要的信息。设置为“replica”，则会添加一些备库只读查询时需要的信息。如果要搭建物理备库，需要把此参数设置为“replica”；如果需要使用逻辑同步，需要把此参数设置为“logical”
• fsync:是否使用fsync()系统调用（或等价调用）把文件系统中的脏页刷新到物理磁盘，确保数据库能在操作系统或者硬件崩溃的情况下恢复到一致的状态。改变该参数需要重新装载配置文件。此参数默认值为“on”
• synchronous_commit:声明提交一个事务是否需要等待其把WAL日志写入磁盘后才返回，默认值是“on”。有点像dg配置中的affrim参数
• wal_sync_method：用来指定向磁盘强制更新WAL日志数据的方法
• full_page_writes：打开该选项时，PostgreSQL服务器会在检查点（checkpoints）之后对页面进行第一次修改时将整个页面写到WAL日志中。这样做是因为在操作系统崩溃过程中可能只有部分页面写入磁盘了，从而导致在同一个页面中会有新旧数据混合的情况。在崩溃后的恢复期，如果WAL日志中没有记录完整的页，且页中的数据是新旧混合的，则无法完全恢复该页。把完整的页面保存在WAL日志中就可以直接使用WAL日志中的页覆盖坏页（包含新旧混合的数据）以完成恢复工作。此参数的默认值为“on”，为了数据安全，通常使用该默认设置。
• wal_buffers:log_buffer参数
• wal_writer_delay:指定wal writer process把WAL日志写入磁盘的周期。
• commit_delay:指定向WAL缓冲区写入记录和将缓冲区刷新到磁盘上之间的时间延迟，以微秒为单位
• commit_siblin gs：在执行commit_delay延迟时要求同时打开的最小并发事务数。默认是“5”。