精华内容
下载资源
问答
  • pgsql中对索引的操作

    千次阅读 2019-06-06 09:57:29
    查看表中的索引的信息 select * from pg_indexes where tablename='prm_prom_bill_rule'; 或者 select * from pg_statio_all_indexes where relname=‘tbname‘; tablename 中填充需要查看的表的索引的信息。 ...

    查看表中的索引的信息

    select * from pg_indexes
    
    where tablename='prm_prom_bill_rule';  
    
    SELECT
        tablename,
        indexname,
        indexdef
    FROM
        pg_indexes
    WHERE
        tablename = 'prm_prom_bill_cate_brand' ;
    
    或者     select * from pg_statio_all_indexes where relname=‘tbname‘;

     

     设置自增索引的初始值:

    select setval('prm_letter_id_seq', 2535);
    
     select setval('t_custom_model_id_seq',1,false);

    tablename 中填充需要查看的表的索引的信息。

    Create Unique Index prm_prom_bill_rule_id_un On prm_prom_bill_rule(bill_rule_id, bill_id);
    

    创建索引 

     prm_prom_bill_rule_id_un 创建的索引的名字
    
    prm_prom_bill_rule 表的名字
    
    bill_rule_id, bill_id 表中关联的字段
    
    DROP INDEX index;   
    
    index中填充的就为你想要删除的索引的信息
    
    无法删除DBMS为主键约束和唯一约束自动创建的索引
    eg:
    Create  Index prom_rule_id_prm_prom_rule_index On prm_prom_rule(prom_rule_id);
    

    删除索引的方法:

    Drop Index idx_tb_user_UNQ Cascade;
    
    Cascade表示级联删除,表示引用这个的地方也会被删除
    
    创建索引可以添加where条件
    
    Create Unique Index idx_tb_user_UNQ On tb_user(login_name,group_id) WHERE delete_flag = '0';
    
    上面的sql表示delete_flag=0的数据才有唯一索引约束。

     

    展开全文
  • psql 删除唯一索引

    千次阅读 2018-05-07 17:43:00
    唯一索引, 直接用drop index 方式删除异常,需要用删除约束的方式删除 alter table xx drop constraint indexName 转载于:https://my.oschina.net/yukong/blog/1808342

    唯一索引, 直接用drop index 方式删除异常,需要用删除约束的方式删除

    alter table xx drop constraint indexName

    转载于:https://my.oschina.net/yukong/blog/1808342

    展开全文
  • (2)创建唯一索引 create unique index /*创建唯一索引*/ CREATE UNIQUE INDEX uniqididx ON book (bookid); (3)创建单列索引 /*创建单列索引*/ CREATE INDEX bkcmtidx ON book (comment); (4)创建组合索引 /*...

    【PostgreSQL函数】数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数

    《一、数学函数》

    6.2.1-绝对值函数ABS(x) 和 圆周率函数PI()

    select ABS(2),ABS(-3.3),pi();

    6.2.2-平方根函数SQRT(x) 和 求余函数MOD(x,y)

    select sqrt(9),sqrt(40),mod(31,8),mod(45.5,6);

    6.2.3-获取整数的函数 CEIL(x)、CEILING(x)、FLOOR(x)

    select ceil(-3.35),ceiling (3.35),floor(-3.35),floor(3.35);

    6.2.4-四舍五入函数 ROUND(x) 和 ROUND(x,y)

    select round(-1.14),round(-1.67),round(1.14),round(1.66),round(1.38,1),round(1.38,0),round(232.38,-1),round(232.38,-2);

    6.2.5-符号函数 SIGN(x)

    select sign(-21),sign(0),sign(21);

    6.2.6-幂运算函数 POW(x,y) 、 POWER(x,y) 、 EXP(x)

    select pow(2,2),power(2,2),pow(2,-2),power(2,-2),exp(3),exp(-3),exp(0);

    6.2.7-对数运算函数 LOG(x)

    select log(3);

    6.2.8-角度与弧度相互转换的函数 RADIANS(x) 和 DEGREES(x)

    select radians(90),radians(180),degrees(pi()),degrees(pi()/2);

    6.2.9-正弦函数 SIN(x) 和反正弦函数 ASIN(x)

    select sin(1),round(sin(pi())),asin(0.841470984807897);

    select asin(3); /*会报错*/

    6.2.10-余弦函数 COS(x) 和 反余弦函数 ACOS(x)

    select cos(0),cos(pi()),cos(1),acos(1),acos(0),acos(0.54030230586814);

    6.2.11-正切函数 TAN(x)、反正切函数 ATAN(x)、余切 函数 COT(x)

    select tan(0.3),round(tan(pi()/4)),atan(0.309336249609623),atan(1),cot(0.3),1/tan(0.3),cot(pi()/4);

    《二、字符串函数》

    6.3.1-计算字符串字符数 char_lenth(str) 和字符串长度 length(str)

    select char_length('date1'),char_length('汉字'),length('date1'),length('汉字');

    (为毛我测试的汉字的length也是2?不解!!)

    6.3.2-合并字符串函数 concat(s1,s2,...) 和 concat_ws(x,s1,s2,...)

    select concat('PostgreSQL','9.15'),concat('Postgre',NULL,'SQL');

    select concat_ws('-','1st','2nd','3rd'),concat_ws('*','1st',NULL,'3rd');

    6.3.3-获取指定长度的字符串 left(s,n) 和 right(s,n)

    s

    select left('football',5),right('football',4);

    6.3.4-填充字符串的函数 lpad(s1,len,s2) 和 rpad(s1,len,s2)

    select lpad('hello',4,'?'),lpad('hello',10,'?'),rpad('hello',4,'?'),rpad('hello',10,'?');

    6.3.5-删除空格的函数 ltrim(s) 、 rtrim(s) 和 trim(s)

    select ltrim(' book '),rtrim(' book '),trim(' book ');

     

    6.3.6-删除指定字符串的函数 trim(s1 FROM s)

    select trim('xy' from 'xyboxyokxyxy');

    6.3.7-重复生成字符串的函数 repeat(s,n)

    select repeat('cd-',3),repeat('cd-',-2),repeat('cd-',NULL);

    6.3.8-字符串替换函数 replace(s,s1,s2)

    select replace('xxx.baidu.com','x','w');

    6.3.9-获取子串的函数 substring(s,n,len)

    select substring('breakfast',5),substring('breakfast',5,3),substring('breakfast',-3);

    6.3.10-匹配子串开始位置的函数 position(str1 IN str)

    select position('ball' in 'football');

    6.3.11-字符串反转的函数 reverse(s)

    select reverse('abcde');

     

    《三、日期和时间函数》

    6.4.1-获取当前日期current_date和获取当前时间current_time、localtime

    select current_date,current_time,localtime;

    6.4.2-获取当前日期和时间的函数current_timestamp、localtimestamp和now()

    select current_timestamp,localtimestamp,now();

    6.4.3-获取日期指定值extract(type FROM d)

    /*提取年份、月份、日期*/

    select extract(year from timestamp '2015-12-20 10:23:49'),extract(month from now()),extract(day from now());

    /*一年中的第几天、一周中的星期几(0-6)、一年中的第几季度(1-4)*/

    select extract(doy from now()),extract(dow from now()),extract(quarter from now());

    6.4.4-日期和时间的运算操作(加减乘除)

    select date '2012-09-28' + integer '10';

    select date '2012-09-28' + interval '3 hour';

    select date '2012-09-28' + time '06:00';

    select timestamp '2012-09-28 02:00:00' + interval '10 hours';

    select date '2012-11-01' - date '2012-09-10';

    select date '2012-09-28' - integer '10';

    select 15 * interval '2 day';

    select 50 * interval '2 second';

    select interval '1 hour' / integer '2';

     

    《四、条件判断函数》

    又叫:控制流程函数。CASE...WHEN...THEN...END

    select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;

    select case when 1<0 then '正确' else '错误' end;

     

    《五、系统信息函数》

    6.6.1-获取PostgreSQL版本号 VERSION()

    select version();

    6.6.2-获取用户名 USER和CURRENT_USER

    select user,current_user;

     

    《六、加密函数》

    6.7.1-加密函数 MD5(str)

    select md5('mypwd');

    6.7.2-加密函数 ENCODE(str,pswd_str)

    select encode('secret','hex'),length(encode('secret','hex'));

    6.7.3-解密函数 DECODE(crypt_str,pswd_str)

    select decode('736563726574','hex');

     

    《七、改变数据类型的函数CAST》

    CAST(x AS type)

    select cast(100 as char(2));

     

    【PostgreSQL索引】

    1. 索引简介

    分类:B-tree(常用)、Hash(性能较弱,不建议使用)、GiST、GIN

    2.创建索引 CREATE INDEX

    create table book(

    bookid int not null,

    bookname varchar(255) not null,

    authors varchar(255) not null,

    info varchar(255) null,

    comment varchar(255) null,

    year_publication date not null

    );

     

    (1)创建普通索引 create index

    /*创建普通索引*/

    CREATE INDEX bknameidx ON book(bookname);

    (2)创建唯一索引 create unique index

    /*创建唯一索引*/

    CREATE UNIQUE INDEX uniqididx ON book (bookid);

    (3)创建单列索引

    /*创建单列索引*/

    CREATE INDEX bkcmtidx ON book (comment);

    (4)创建组合索引

    /*创建组合索引*/

    CREATE INDEX bkauandinfoidx ON book (authors,info);

     

    2.重命名索引 ALTER INDEX ... RENAME TO ...

    /*重命名索引*/

    ALTER INDEX bkauandinfoidx RENAME TO abcd;

     

    3.删除索引 DROP INDEX

    /*删除索引*/

    DROP INDEX abcd;

    展开全文
  • pgsql

    2020-10-13 17:23:16
    复杂查询 外键 触发器 可更新的视图 事务完整性 多版本并发控制 另外,PostgreSQL可以用许多方法进行扩展,比如通过增加新的: 数据类型 函数 操作符 聚合函数 索引方法 过程语言 并且,因为许可证的灵活,任何人都...


    PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES, Version 4.2为基础的对象关系型数据库管理系统(ORDBMS)。
    PostgreSQL是最初伯克利代码的一个开放源码的继承者。它支持大部分SQL标准并且提供了许多其它现代特性:
    复杂查询
    外键
    触发器
    可更新的视图
    事务完整性
    多版本并发控制

    另外,PostgreSQL可以用许多方法进行扩展,比如通过增加新的:
    数据类型
    函数
    操作符
    聚合函数
    索引方法
    过程语言
    并且,因为许可证的灵活,任何人都可以以任何目的免费使用、修改、分发PostgreSQL,不管是私用、商用、还是学术研究使用。

    pgpool安装

    安装参考
    https://www.jianshu.com/p/ef183d0a9213
    https://my.oschina.net/u/3308173/blog/900093 配置文件参考
    https://www.xiaomastack.com/2019/08/16/postgresql%E9%9B%86%E7%BE%A4/ bigdata参考

    安装前准备:
    chmod +s /bin/ping
    chmod +s /sbin/ifup
    chmod +s /sbin/ip
    chmod +s /sbin/ifconfig
    chmod +s /sbin/arping

    yum install http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-release-3.7-1.noarch.rpm
    yum -y install pgpool-II-pg96
    yum -y install pgpool-II-pg96-debuginfo
    yum -y install pgpool-II-pg96-devel
    yum -y install pgpool-II-pg96-extensions
    chown -R postgres:postgres /etc/pgpool-II

    cd /data/pgsql/
    [postgres@slave2 pgsql]$ mkdir pgpool
    [postgres@slave2 pgsql]$ mkdir log
    mkdir /data/pgsql/log/pgpool_log
    mkdir /data/pgsql/pgpool/dog

    ssh-keygen -t rsa 回车回车回车
    ssh-copy-id root@slave2 //远程的ip
    ssh-copy-id root@master2 //远程的ip

    配置文件:注意配置主从ip要一致 用主机名就不要用ip,用ip就不用主机名

    # CONNECTIONS
    listen_addresses = '*'
    port = 9999
    pcp_listen_addresses = '*'
    pcp_port = 9898
    
    # - Backend Connection Settings -
    
    backend_hostname0 = 'master'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_data_directory0 = '/home/postgres/data'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    
    backend_hostname1 = 'slave'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/home/postgres/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    
    # - Authentication -
    enable_pool_hba = on
    pool_passwd = 'pool_passwd'
    
    # FILE LOCATIONS
    pid_file_name = '/opt/pgpool/pgpool.pid'
    
    replication_mode = off
    load_balance_mode = on
    master_slave_mode = on
    master_slave_sub_mode = 'stream'
    
    sr_check_period = 5
    sr_check_user = 'repuser'
    sr_check_password = 'repuser'
    sr_check_database = 'postgres'
    
    #------------------------------------------------------------------------------
    # HEALTH CHECK 健康检查
    #------------------------------------------------------------------------------
    
    health_check_period = 10 # Health check period
                                       # Disabled (0) by default
    health_check_timeout = 20
                                       # Health check timeout
                                       # 0 means no timeout
    health_check_user = 'postgres'
                                       # Health check user
    health_check_password = 'nariadmin' #数据库密码
                                       # Password for health check user
    health_check_database = 'postgres'
    #必须设置,否则primary数据库down了,pgpool不知道,不能及时切换。从库流复制还在连接数据,报连接失败。
    #只有下次使用pgpool登录时,发现连接不上,然后报错,这时候,才知道挂了,pgpool进行切换。
    
    
    #主备切换的命令行配置
    #------------------------------------------------------------------------------
    # FAILOVER AND FAILBACK
    #------------------------------------------------------------------------------
    
    failover_command = '/opt/pgpool/failover_stream.sh %H '
    
    #------------------------------------------------------------------------------
    # WATCHDOG
    #------------------------------------------------------------------------------
    
    # - Enabling -
    use_watchdog = on
    # - Watchdog communication Settings -
    
    wd_hostname = 'master'
                                        # Host name or IP address of this watchdog
                                        # (change requires restart)
    wd_port = 9000
                                        # port number for watchdog service
                                        # (change requires restart)
    # - Virtual IP control Setting -
    
    delegate_IP = 'vip'
                                        # delegate IP address
                                        # If this is empty, virtual IP never bring up.
                                        # (change requires restart)
    if_cmd_path = '/sbin'
                                        # path to the directory where if_up/down_cmd exists
                                        # (change requires restart)
    if_up_cmd = 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0'
                                        # startup delegate IP command
                                        # (change requires restart)
                                        # eth1根据现场机器改掉
    if_down_cmd = 'ifconfig eth1:0 down'
                                        # shutdown delegate IP command
                                        # (change requires restart)
                                        # eth1根据现场机器改掉
    # -- heartbeat mode --
    
    wd_heartbeat_port = 9694
                                        # Port number for receiving heartbeat signal
                                        # (change requires restart)
    wd_heartbeat_keepalive = 2
                                        # Interval time of sending heartbeat signal (sec)
                                        # (change requires restart)
    wd_heartbeat_deadtime = 30
                                        # Deadtime interval for heartbeat signal (sec)
                                        # (change requires restart)
    heartbeat_destination0 = 'slave'
                                        # Host name or IP address of destination 0
                                        # for sending heartbeat signal.
                                        # (change requires restart)
    heartbeat_destination_port0 = 9694
                                        # Port number of destination 0 for sending
                                        # heartbeat signal. Usually this is the
                                        # same as wd_heartbeat_port.
                                        # (change requires restart)
    heartbeat_device0 = 'eth1'
                                        # Name of NIC device (such like 'eth0')
                                        # used for sending/receiving heartbeat
                                        # signal to/from destination 0.
                                        # This works only when this is not empty
                                        # and pgpool has root privilege.
                                        # (change requires restart)
                                        # eth1根据现场机器改掉
    # - Other pgpool Connection Settings -
    
    other_pgpool_hostname0 = 'slave' #对端
                                        # Host name or IP address to connect to for other pgpool 0
                                        # (change requires restart)
    other_pgpool_port0 = 9999
                                        # Port number for othet pgpool 0
                                        # (change requires restart)
    other_wd_port0 = 9000
                                        # Port number for othet watchdog 0
                                        # (change requires restart)
    ···
    

    配置pcp.conf:
    pg_md5 -p postgres 创建密钥
    写入pcp.conf
    postgres:e8a48653851e28c69d0506508fb27fc5 -->该串字符,即为刚刚生成的md5密钥
    pool_hba.conf:
    host all all 0.0.0.0/0 trust

    启动:
    pgpool -n -d > /tmp/pgpool.log 2>&1 &
    登陆
    psql -p 9999 -h 10.10.80.2
    查看状态
    postgres=# show pool_nodes;
    在这里插入图片描述
    停止:
    pgpool -m fast stop
    效果:
    主库pgpool宕机 转移虚拟ip
    在这里插入图片描述
    停止主库:
    在这里插入图片描述
    等待一段时间后 (主从切换需要时间)
    在这里插入图片描述
    启动原master后:加回集群
    pcp_attach_node -d -U postgres -h 10.10.80.2 -p 9898 -n 0
    在这里插入图片描述
    备注:
    启动pg
    postgres -D /data/pgsql/data >/tmp/pgsql.log 2>&1 &
    关闭pg
    pg_ctl stop -m fast

    查看复制状态
    select * from pg_stat_replication;
    区别主备
    ps -ef | grep wal
    wal 日志发送进程"wal sender process",说明是主库。
    wal 日志接收进程"wal receiver process" ,说明是备库;

    pgsql流复制

    数据库流复制
    9.0开始支持1+n的异步流复制 一主多从
    9.1开始支持1+1+n的同步和异步流复制 主库必须等待其中的一个从提交相同事务
    9.2开始支持级联流复制 A-B-C
    9.3开始支持跨平台的流复制协议 增加了时间线文件传输协议,支持自动切换时间线

    pg_controldata命令查看当前数据库状态
    Database cluster state: in production 代表主库
    Database cluster state: in archive recovery 代表从库

    top -c -u postgres #查看复制进程 sender、receiver

    必须配置的配置项:
    主节点:
    archive_mode=on
    wal_level=logical
    wal_keep_segments=256 #wal日志文件个数超过多少之后开始轮巡覆盖
    archive_command=‘DATE=date +%Y%m%d; DIR="/data/pgsql/log/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f’
    max_wal_senders = 32允许多少个流复制协议的连接 相当于有多少个从库 至少为1

    同步流复制:
    synchronous_standby_names=’’ 指定同步流复制节点的名称 recovery.conf中配置的replication_name
    synchronous_commit =on 同步提交参数, 控制事务提交后返回客户端是否成功的策略
    1 为on且没有开启同步备库的时候,会当wal日志真正刷新到磁盘永久存储后才会返回客户端事务已提交成功,
    2 当为on且开启了同步备库的时候(设置了synchronous_standby_names),必须要等事务日志刷新到本地磁盘,并且还要等远程备库也提交到磁盘才能返回客户端已经提交. 对于重要事务打开此参数 进行主从同步

    standby节点:
    hot_standby=on #允许从库在恢复时查询
    max_standby_archive_delay=300s
    max_standby_streaming_deay=300s 查询和日志重放冲突等待
    wal_receiver_status_interval =1 #多久向主节点发送一次同步信息,接收日志时也会发送同步信息,所以该参数是最长的时间,最短的时间和接收频率有关

    同步流复制类似于mysql的半同步复制

    流复制演示;
    1.首先确认./pg_config --configure 安装的配置主从相同
    2.配置主库
    archive_mode=on
    wal_level=logical
    wal_keep_segments=256
    archive_command=‘DATE=date +%Y%m%d; DIR="/data/pgsql/log/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f’
    max_wal_senders = 32
    synchronous_standby_names=’*’
    #切换从库用
    hot_standby=on
    max_standby_archive_delay=300s
    max_standby_streaming_deay=300s
    wal_receiver_status_interval =1
    配置 pg_hba.conf
    host replication replica 0.0.0.0/0 md5
    3.新建角色
    create role replica login replication encrypted password ‘replica’;
    4.配置密码文件(主从都做)
    vim /data/pgsql/data/.pgpass
    10.10.80.41:5432:replication:replica:replica
    10.10.80.55:5432:replication:repl:123456

    chmod 400 .pgpass
    5.制作备份 (从库)
    pg_basebackup -F p -D /data/pgsql/data/ -h slave2 -p 5432 -U replica
    6.修改从库配置文件名 表示我是一个standby节点 要持续不停的做恢复
    mv recovery.done recovery.conf
    vim recovery.conf
    recovery_target_timeline = ‘latest’
    primary_conninfo = ‘host=10.10.80.60 port=5432 user=replica password=replica’
    standby_mode = on

    7.启动从库
    postgres -D /data/pgsql/data >/tmp/pgsql.log 2>&1 &
    8.主库修改 查看从库变化
    psql -h 127.0.0.1 -U eren -d mydb
    select * from pg_stat_replication;

    查看主从进程
    top -c -u postgres

    备份与恢复

    SQL转储的方法是创建一个文件,里面都是SQL命令, 当把这个文件回馈给服务器时,将重建与转储时状态一样的数据库。 PostgreSQL为这个用途提供了pg_dump工具。 这条命令的基本用法是:
    pg_dump dbname > outfile
    pg_dump缺省时用 与当前操作系统用户名同名的数据库用户名进行连接。 要覆盖这个名字,要么声明-U选项, 要么设置环境变量PGUSER。请注意pg_dump 的连接也和普通客户应用一样要通过客户认证机制
    pg_dump超过后边描述的其它备份方法的一个重要优点 是pg_dump的输出通常可以 重新载入PostgreSQL新版本, 然而文件级别备份和连续归档都因 服务器版本而异。pg_dump是 将传输数据库到另一台机器体系结构工作时唯一的方法
    由pg_dump创建的备份在内部是一致的, 也就是说,在pg_dump运行的时候转储的是数据库的快照。 pg_dump工作的时候并不阻塞其它的对数据库的操作 (但是会阻塞那些需要排它锁的操作,比如ALTER TABLE)。

    从转储中恢复
    psql dbname < infile
    这里的infile 就是通过pg_dump命令的文件输出。 这条命令不会创建dbname数据库, 你必须在执行psql前自己从 template0创建(也就是用createdb -T template0 dbname命令)
    在开始运行恢复之前,目标库和所有在转储出来的库中拥有对象的用户, 以及曾经在某些对象上被赋予权限的用户都必须已经存在。如果这些不存在, 那么恢复将失败,因为恢复过程无法把这些对象恢复成原有的所有权和/或权限。
    缺省时,psql脚本将在遇到错误的时候仍然继续执行。 你可能希望运行带有ON_ERROR_STOP变量设置的 psql以改变操作,并且如果发生SQL错误则带有 退出状态码3的psql退出。

    psql --set ON_ERROR_STOP=on dbname < infile

    你可以将整个恢复过程当成一个单独的事务,这样就能够保证要么全部恢复成功, 要么全部回滚。可以通过向psql传递-1或者–single-transaction命令行 参数达到此目的。
    pg_dump和psql可以通过管道读写, 这样我们就可能从一台主机上将数据库目录转储到另一台主机上,比如:

    pg_dump -h host1 dbname | psql -h host2 dbname

    使用pg_dumpall
    pg_dump在一个时间只转储一个单独的数据库, 它不转储有关角色或表空间信息(因为这些是集群范围,而不是每个数据库)。 为了支持 方便转储整个数据库集群的全部内容。 因此我们提供了pg_dumpall程序。 pg_dumpall备份一个给出的集群中 的每个数据库,同时还确保保留像角色和表空间这样的全局数据状态。 这个命令的基本用法是:
    pg_dumpall > outfile

    生成的转储可以用psql恢复:
    psql -f infile postgres

    使用压缩转储. 使用你熟悉的压缩程序(比如gzip):
    pg_dump dbname | gzip > filename.gz
    使用下面命令恢复:
    gunzip -c filename.gz | psql dbname
    或者:
    cat filename.gz | gunzip | psql dbname
    使用split. split允许用下面的方法把输出分解成操作系统可以接受的大小。 比如,让每个块大小为1MB:
    pg_dump dbname | split -b 1m - filename
    用下面命令恢复:
    cat filename* | psql dbname

    文件系统级别备份
    另一个备份的策略是直接拷贝PostgreSQL用于存放数据库数据的文件。
    你可以用自己喜欢的任何常用文件系统备份的方法, 例如:

    tar -cf backup.tar /usr/local/pgsql/data
    不过,你要受到两个限制,令这个方法不那么实用,或者至少比pg_dump的方法逊色一些:
    1.为了进行有效的备份,数据库服务器必须被关闭。 像拒绝所有连接这样的折衷的方法是不行的
    2.如果你曾经深入了解了数据库在文件系统布局的细节, 你可能试图从对应的文件或目录里备份几个表或者数据库。 这样做是没用的,因为包含在这些文件里的信息只是部分信息。 还有一半信息在提交日志文件pg_clog/*里面, 它包含所有事务的提交状态。 只有拥有这些信息,表文件的信息才是可用的。
    所以文件系统的备份只适用于一个数据库集群的完整恢复。

    在线备份以及即时恢复(PITR)
    在任何时候,PostgreSQL都在集群的数据目录的pg_xlog/ 子目录里维护着一套预写日志(WAL)。 这些日志记录着每一次对数据库的修改细节。 这些日志存在是为了防止崩溃: 如果系统崩溃,数据库可以通过"重放"上次检查点以来的日志记录以恢复数据库的完整性。 但是,日志的存在让它还可以用于第三种备份数据库的策略: 我们可以组合文件系统备份与WAL文件的备份。如果需要恢复,我们就恢复备份, 然后重放备份了的WAL文件,把备份恢复到当前的时间。这个方法对管理员来说, 明显比以前的方法更复杂,但是有非常明显的优势 wal日志类似于mysql的binlog

    抽象来看,一个运行着的PostgreSQL系统生成一个无限长的WAL日志序列。 系统物理上把这个序列分隔成WAL段文件,通常每段16M 这些段文件的名字是数值命名的,这些数值反映他们在抽取出来的 WAL 序列中的位置。在不适用WAL归档的时候,系统通常只是创建几个段文件然 后"循环"使用它们,方法是把不再使用的段文件的名字重命名为更高的段编号。
    物理备份实现:

    
    
    1.首先要开启归档:设置vim /data/pgsql/data/postgresql.conf
    archive_mode=on
    wal_level=logical
    archive_command='DATE=`date +%Y%m%d`; DIR="/data/pgsql/log/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'
    %p表示xlog文件名$PGDATA的相对路径
    %f表示xlog文件名
    2.创建归档目录 mkdir -p /data/pgsql/log
    chown -R postgres:postgres /data/pgsql/log 
    3.配置修改后需要重启数据库生效
    pg_ctl stop -m fast
    postgres -D /data/pgsql/data >/tmp/pgsql.log 2>&1 & 
    4.测试归档是否正常
    >checkpoint;
    >select pg_switch_xlog(); 
    查看配置的目录(/data/pgsql/log/$DATE)中是否有新的wal日志,若有则成功 
    在开启归档并且归档正常了之后,就可以做在线的物理备份了
    一种是通过pg_basebackup命令
    还有一种是在数据库里直接调用一条命令 然后拷贝数据文件 
    先看一下pg_basebackup是怎么备份的
    pg_basebackup是用过流复制协议来备份的,我们需要先创建一个用户拥有replication或超级用户权限 
    1.创建角色
    create role rep nosuperuser replication login connection limit 32 encrypted password 'rep123';
    2.配置pg_hba.conf
    host replication rep 0.0.0.0/0 md5
    #意为允许rep用户在所有ip使用流复制协议,密码使用md5加密
    pg_ctl reload
    #使配置生效
    3.支持异地备份 在其他机器备份(10.10.80.55):
    mkdir `date +%F`;pg_basebackup -F t -x -D ./`date +%F` -h 10.10.80.60 -p 5432 -U rep 
    4.备份完成后 查看备份目录
    数字目录代表表空间的备份包 pg_tblspc中软连接的id 
    5.归档日志需要手动拷贝到备份机器
    ------- 
    另外一种方法来备份
    连接到数据库终端 通过语句打开强制检查点
    select pg_start_backup(now()::text);
    此时已经开始了备份,会在$PGDATA目录中生成一个标签backup_label 
    也可以通过 \df *.*backup*
    select pg_is_in_backup();
    查看是否正在进行备份,如果返回t 说明在备份 
    此时就可以手动拷贝$PGDATA、表空间文件进行备份 
    备份完成之后 运行
    select pg_stop_backup();
    来停止备份 
    ---- 
    测试还原 
    备份之后创建一张表
    create table abc(id int);
    insert into abc values (1);
    checkpoint;
    select pg_switch_xlog(); 
    停止数据库
    pg_ctl stop -m fast
    删除$PGDATA目录文件、表空间文件 
    拷贝回备份文件 到对应目录 并解压
    tar xvf base.tar 
    配置还原文件
    cp /data/pgsql/2345/share/recovery.conf.sample /data/pgsql/data/recovery.conf 
    restore_command = 'cp /data/pgsql/log/20190925/%f %p'
    恢复到最后的时间点,就不需要配置还原点 
      
    赋权拷贝过来的目录
    chown -R postgres:postgres /data/pgsql/ 
    启动数据库 检查恢复情况 
    注:通常,恢复将在所有可用的WAL段中进行,从而将数据库还原到当前时间点(或在可用的WAL段中尽可能接近)。因此,正常恢复将以“找不到文件”消息结束,错误消息的确切文本取决于您选择的restore_command。您还可能在恢复开始时看到名为00000001.history之类的文件的错误消息。这也是正常现象,在简单的恢复情况下并不表示有问题; 
    -----------
    默认支持三种还原点
    recovery_target_name=''
    recovery_target_time=''
    recovery_target_xid='' 
    recovery_target_name是我们自己创建的,如果又多个重复命名的还原点,遇到第一个则停止
    如:创建一个还原点叫2019
    select pg_create_restore_point('2019');
    此时在xlog中会插入一条这个还原点的信息,如果下次想要恢复到这个点的话 就在recovery_target_name中配置这个点就可以了 
    recovery_target_time 代表想要还原到哪个时间 这个时间可以通过
    select now(); 来获取格式
    在同一个时间点,有可能有多个事务commit/abord 所以参数recovery_target_inclusive 如果设置为ture 表示恢复所有在这个时间点提交的事务 如果为false 那么只在第一个事务提交之后截止记录 
    
    recovery_target_xid表示还原到指定的xid,以commit或abord的xid到达为准
    查看当前的xid:
    select txid_current(); 
     
    

    客户端验证

    当客户端应用程序连接到数据库服务器时,它指定要连接的pgsql数据库用户名,就像以特定用户身份登陆unix计算机一样,在sql环境中,活动数据库用户名确定对应数据库对象的访问权限,因此必须限制哪些数据库用户可以连接
    我们始终使用database user来表示“具有LOGIN权限的role”。
    PostgreSQL提供了许多不同的客户端身份验证方法。可以基于(客户端)主机地址,数据库和用户来选择用于认证特定客户端连接的方法。
    pg_hba.conf的文件
    客户端身份验证由配置文件控制,配置文件通常名为pg_hba.conf,并存储在数据库集群的数据目录中。(HBA代表基于主机的身份验证。)当initdb初始化数据目录时,将安装默认的pg_hba.conf文件。但是,可以将身份验证配置文件放在其他位置;

    pg_hba.conf文件的常用格式是一组记录,每行一条。空白行将被忽略, 井号#开头的注释也被忽略。记录不能跨行存在。 一条记录是由若干用空格和/或制表符分隔的字段组成。如果字段用引号包围,那么它可以包含空白。 在数据库、用户或地址文件中引用一个关键词(如,all 或 replication) 使这个词失去它的特殊含义,只是用这个名字匹配一个数据库、用户或主机。

    每条记录声明一种连接类型、一个客户端 IP 地址范围(如果和连接类型相关的话)、 一个数据库名、一个用户名字、对匹配这些参数的连接使用的认证方法。第一条匹配连接类型、 客户端地址、连接请求的数据库名和用户名的记录将用于执行认证。这个处理过程没有 "跨越"或者"回头"的说法:如果选择了一条记录而且认证失败, 那么将不再考虑后面的记录。如果没有匹配的记录,那么访问将被拒绝。

    每条记录可以是下面七种格式之一:
    local database user auth-method [auth-options]
    host database user address auth-method [auth-options]
    hostssl database user address auth-method [auth-options]
    hostnossl database user address auth-method [auth-options]
    host database user IP-address IP-mask auth-method [auth-options]
    hostssl database user IP-address IP-mask auth-method [auth-options]
    hostnossl database user IP-address IP-mask auth-method [auth-options]
    在这里插入图片描述
    因为认证时系统是为每个连接请求顺序检查pg_hba.conf里的记录的, 所以这些记录的顺序是非常关键的。通常,靠前的记录有比较严的连接匹配参数和比较弱的认证方法, 而靠后的记录有比较松的匹配参数和比较严的认证方法。比如,我们一般都希望对本地 TCP/IP 连接使用 trust认证,而对远端的 TCP/IP 连接要求口令。在这种情况下我们将trust 认证方法用于来自 127.0.0.1 的连接,这条记录将出现在允许更广泛的客户端 IP 地址的使用口令认证的记录前面。

    在启动和主服务器进程收到SIGHUP 信号的时候,系统都会重新装载pg_hba.conf文件。 如果你在活跃的系统上编辑了该文件,就必须通知主服务器(使用pg_ctl reload 或kill -HUP)重新加载该文件。
    手动生效文件:pg_ctl reload -D DATADIR
    数据库角色
    PostgreSQL使用角色的概念管理数据库访问权限。可以将角色视为数据库用户或一组数据库用户,具体取决于角色的设置方式。角色可以拥有数据库对象(例如,表和函数),并可以将这些对象的权限分配给其他角色,以控制谁有权访问哪些对象。此外,可以将角色的成员资格授予另一个角色,从而允许成员角色使用分配给另一个角色的权限。

    创建一个角色,使用 SQL 命令CREATE ROLE执行:
    CREATE ROLE name;
    要删除一个现有角色,使用类似的DROP ROLE命令:
    DROP ROLE name;
    为了方便,程序createuser和dropuser 提供了对了这些 SQL 命令的封装。我们可以在 shell 命令上直接调用它们:
    createuser name
    dropuser name
    要检查现有角色的集合,可以检查pg_roles系统表,比如:
    SELECT rolname FROM pg_roles;

    为了能创建初始数据库系统,新建立的数据库总是包含一个预定义的"超级用户"角色, 并且缺省时(除非在运行initdb时更改过) 他将和初始化该数据库集群的用户有相同的名称。通常,这个角色名叫postgres。 为了创建更多角色,你必须首先以这个初始用户角色连接。
    每一个和数据库的连接都必须用一个角色身份进行,这个角色决定在该连接上的初始权限。 特定数据库连接的角色名是在初始化连接请求的时候声明的。比如,psql 程序使用-U命令行选项声明它代表的角色。

    一个数据库角色可以有一系列属性,这些属性定义他的权限,以及与客户认证系统的交互。
    只有具有LOGIN属性的角色才可以用作数据库连接的初始角色名。 一个带有LOGIN属性的角色可以认为是和"数据库用户"相同的事物。 要创建一个具有登录权限的角色,用下列之一:
    CREATE ROLE name LOGIN;
    CREATE USER name;
    除了CREATE USER默认赋予LOGIN之外,CREATE USER 等价于CREATE ROLE(默认不赋予CREATE ROLE)。

    数据库超级用户超越所有权限检查。要创建数据库超级用户, 用CREATE ROLE name SUPERUSER命令。 你必须用已经是超级用户的角色执行这条命令。

    角色要想创建数据库,必须明确给出权限。 要创建这样的角色,用CREATE ROLE name CREATEDB命令

    角色要想创建角色,必须明确给出权限(对于超级用户是例外,因为他们超越所有权限检查)。 要创建这样的角色,用CREATE ROLE name CREATEROLE 命令。一个带有CREATEROLE权限的角色也可以更改和删除其它角色, 以及给其它角色赋予或者撤销成员关系

    角色要想启动流复制,必须明确给出权限 用于流复制的角色必须总是拥有LOGIN权限。要创建这样的角色, 使用CREATE ROLE name REPLICATION LOGIN命令。

    在创建角色的时候可以这样声明一个口令:CREATE ROLE name PASSWORD ‘string’。
    一个角色的属性可以在创建后用ALTER ROLE 修改

    要删除一个组角色,用DROP ROLE命令:
    由于角色可以拥有数据库对象并且可以拥有访问其他对象的权限,因此删除角色通常不仅仅是快速DROP ROLE的问题。必须首先删除该角色拥有的任何对象或将其重新分配给其他所有者; 并且必须撤消授予该角色的任何权限。简而言之,删除已用于拥有对象的角色的最常用方法是:
    REASSIGN OWNED BY doomed_role TO successor_role;
    DROP OWNED BY doomed_role;
    – 在集群的每个数据库中重复上述命令
    DROP ROLE doomed_role;

    用户权限管理:
    GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, …] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, …]
    | ALL TABLES IN SCHEMA schema_name [, …] }
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, …] )
    [, …] | ALL [ PRIVILEGES ] ( column_name [, …] ) }
    ON [ TABLE ] table_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { { USAGE | SELECT | UPDATE }
    [, …] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, …]
    | ALL SEQUENCES IN SCHEMA schema_name [, …] }
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, …] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, …] ] ) [, …]
    | ALL FUNCTIONS IN SCHEMA schema_name [, …] }
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { { SELECT | UPDATE } [, …] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { { CREATE | USAGE } [, …] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, …]
    TO { [ GROUP ] role_name | PUBLIC } [, …] [ WITH GRANT OPTION ]
    GRANT role_name [, …] TO role_name [, …] [ WITH ADMIN OPTION ]
    GRANT { SELECT | INSERT | ALL [PRIVILEGES] }
    ON PROTOCOL protocolname
    TO username
    管理数据库
    在与数据库服务器连接的时候,应用应该在它的连接请求里指明它想连接的数据库名称。 不允许在一次连接里访问多个数据库(不过没有限制一个应用可以建立的连接数量)。
    要查看现有数据库的集合,可以检查系统表pg_database,比如
    SELECT datname FROM pg_database;
    psql程序的\l元命令和-l 命令行选项也可以用来列出现存的数据库。

    使用SQL命令CREATE DATABASE创建数据库:
    CREATE DATABASE 名称 ;
    为方便起见,您可以从shell执行一个程序来创建新的数据库createdb。
    createdb dbname

    有时候你想为其它人创建一个数据库,并且使他应该成为新数据库的所有者, 这样他就可以自己配置和管理这个数据库。要实现这个目标,使用下列命令中的一条:
    CREATE DATABASE dbname OWNER rolename;
    用于 SQL 环境,或:
    createdb -O rolename dbname
    用于命令行。只有数据库的超级用户才能为其它用户创建数据库。

    CREATE DATABASE实际上是通过拷贝一个现有的数据库进行工作的。缺省时, 它拷贝名为template1的标准系统数据库。 所以该数据库是创建新数据库的"模板"。如果你给template1增加对象, 这些对象将被拷贝到随后创建的用户数据库中。这样的行为允许节点对数据库中的标准套件进行修改。 比如,如果你把过程语言PL/Perl安装到template1里, 那么你在创建用户数据库的时候它们就会自动可得,而不需要额外的动作。
    系统里还有名为template0的第二个标准系统数据库, 这个数据库包含和template1初始时一样的数据内容,也就是说, 只包含标准的PostgreSQL对象。在数据库集群初始化之后, 我们不应该对template0做任何修改。通过告诉CREATE DATABASE使用 template0而不是template1进行拷贝,你可以创建一个"纯净" 的用户数据库
    要通过拷贝template0的方法创建一个数据库,可使用:
    CREATE DATABASE dbname TEMPLATE template0;
    用于 SQL 环境,或:
    createdb -T template0 dbname
    用于 shell 环境。

    我们可以创建额外的模板数据库,而且实际上我们可以在一个集群中通过将 CREATE DATABASE的模板声明为相应的数据库名拷贝任何数据库。不过, 我们必需明白,这个功能并非一般性的"COPY DATABASE"工具。 实际上,在拷贝操作的过程中,源数据库必需是空闲状态(没有正在处理的数据修改事务)。 如果在CREATE DATABASE开始的时候存在其它连接,那么操作将会失败, 在拷贝期间,到源数据库的新连接都被阻止。

    删除数据库
    数据库是用DROP DATABASE命令删除的:
    DROP DATABASE name;
    只有数据库的所有者或者超级用户才可以删除数据库。 删除数据库会删除数据库中包括的所有对象。数据库的删除是不可恢复的。
    你不能在与目标库连接的时候执行DROP DATABASE命令。不过, 你可以和其它数据库连接,包括template1数据库。template1 也是你删除集群中最后一个库的唯一方法。
    为了方便,有一个在 shell 上运行的删除数据库的dropdb程序:
    dropdb dbname
    它和createdb不一样,没有缺省删除的数据库名称。

    表空间

    PostgreSQL里的表空间允许数据库管理员在文件系统里定义那些代表数据库对象的文件存放位置。 一旦创建了表空间,那么就可以在创建数据库对象的时候引用它。
    通过使用表空间,管理员可以控制一个PostgreSQL安装的磁盘布局。 这么做至少有两个用处。首先,如果初始化集群所在的分区或者卷用光了空间, 而又不能扩展,那么表空间可以在一个不同的分区上创建和使用, 直到系统可以重新配置。

    第二,表空间允许管理员根据数据库对象的使用模式安排数据位置,从而优化性能。比如, 一个很频繁使用的索引可以放在非常快并且非常可靠的磁盘上,比如一种非常贵的固态设备。而同时, 一个存储归档的数据,很少使用的或者对性能要求不高的表可以存储在一个便宜但比较慢的磁盘系统上。

    尽管位于PostgreSQL主数据目录之外,表空间作为数据库集群不可分割的一部分, 不能被看做是自主采集数据文件的。 它们依赖于包含在主数据目录中的元数据,并且因此不能附属于不同的数据库集群或单独备份。 相似的,如果你丢失了一个表空间(文件删除,磁盘失败等等), 数据库集群可能会变成不可读的或者不能启动。 将一个表空间放置在临时文件系统上(比如内存盘)会使整个集群的可靠性有风险。

    要定义一个表空间,使用CREATE TABLESPACE命令,比如:
    CREATE TABLESPACE fastspace LOCATION ‘/ssd1/postgresql/data’;
    这个位置必须是一个现有的空目录,并且属于PostgreSQL系统用户。
    创建表空间本身必须用数据库超级用户身份进行,但之后你就可以允许普通数据库用户利用它了。 要做这件事情,在表空间上给这些用户授予CREATE权限。

    表、索引和整个数据库都可以放在特定的表空间里。想要这么做的话, 在给定表空间上有CREATE权限的用户必须把表空间的名字以一个参数的形式传递给相关的命令。 比如,下面的命令在表空间space1上创建一个表:

    CREATE TABLE foo(i int) TABLESPACE space1;
    另外,还可以使用default_tablespace参数:

    SET default_tablespace = space1;
    CREATE TABLE foo(i int);

    与一个数据库相关联的表空间用于存储该数据库的系统表。另外, 如果没有给出TABLESPACE子句,并且没有通过default_tablespace 或temp_tablespaces(视情况而定)指定其他选项,那么在数据库中创建表, 索引和临时文件时使用的是缺省表空间。如果创建数据库时没有给它声明一个表空间, 那么它使用与它拷贝的模版数据库相同的表空间。

    当数据库集群初始化时,自动创建两个表空间。pg_global表空间用于共享的系统表。 pg_default是template1和template0数据库的缺省表空间 (因此,这个表空间也将是任何其它数据库的缺省表空间,除非在CREATE DATABASE 中通过TABLESPACE子句重写)。

    创建了表空间之后,它就可以用于任何数据库,只要请求的用户有足够权限。 这意味着除非我们把使用这个表空间的所有数据库里的所有对象都删除掉,否则我们不能删除该表空间。

    要删除一个空的表空间,使用DROP TABLESPACE命令。

    检查pg_tablespace 系统表就可以获取现有的表空间,比如

    SELECT spcname FROM pg_tablespace;
    psql程序的\db元命令也可以用于列出现有表空间。

    服务器设置及操作

    与外部可访问的任何服务器守护程序一样,建议在单独的用户帐户下运行PostgreSQL。此用户帐户应仅拥有服务器管理的数据,不应与其他守护程序共享。
    要将Unix用户帐户添加到系统,请查找命令useradd或adduser。经常使用用户名postgres
    创建数据库集群
    数据库集群是由正在运行的数据库服务器的单个实例管理的数据库集合。初始化之后,数据库集群将包含名为postgres的数据库,该数据库用作实用程序,用户和第三方应用程序使用的默认数据库。数据库服务器本身不需要postgres数据库存在,但许多外部实用程序假定它存在。初始化期间在每个集群中创建的另一个数据库称为template1。顾名思义,这将用作随后创建的数据库的模板; 它不应该用于实际工作。

    要初始化数据库集群,请使用与PostgreSQL一起安装的命令initdb。数据库集群的所需文件系统位置由-D选项指示,例如
    $ initdb -D /usr/local/pgsql/data
    请注意,您必须在登录PostgreSQL用户帐户时执行此命令

    提示:作为-D选项的替代方法,您可以设置环境变量PGDATA。

    或者,您可以通过pg_ctl程序运行initdb,如下所示:
    $ pg_ctl -D /usr/local/pgsql/data initdb

    如果您使用pg_ctl启动和停止服务器,这可能更直观,因此pg_ctl将是您用于管理数据库服务器实例的唯一命令。
    initdb将尝试创建您指定的目录(如果该目录尚不存在)。当然,如果initdb没有在父目录中写入的权限,这将失败。一般建议PostgreSQL用户不仅拥有数据目录,还拥有其父目录的权限
    如果数据目录存在且已包含文件,initdb将拒绝运行; 这是为了防止意外覆盖现有安装。

    由于数据目录包含存储在数据库中的所有数据,因此必须保护其免受未经授权的访问。因此,initdb撤销除PostgreSQL用户之外的所有人的访问权限

    启动数据库服务器

    在任何人都可以访问数据库之前,您必须启动数据库服务器。数据库服务器程序称为postgres。该Postgres的程序必须知道在哪里可以找到它应该使用数据。这是通过-D选项完成的。因此,启动服务器的最简单方法是:

    $ postgres -D /usr/local/pgsql/data

    这将使服务器在前台运行。这必须在登录PostgreSQL用户帐户时完成。如果没有-D,服务器将尝试使用环境变量PGDATA命名的数据目录。如果没有提供该变量,它将失败。

    通常情况下,最好在后台启动postgres。为此,使用通常的Unix shell语法为:

    $ postgres -D /usr/local/pgsql/data >logfile 2>&1 &

    pg_ctl start -l logfile

    将在后台启动服务器并将输出放入指定的日志文件中。该-D选项这里为具有相同的含义Postgres的。pg_ctl也能够停止服务器。

    通常,您需要在计算机启动时启动数据库服务器。自动启动脚本是特定于操作系统的,但是无论您做什么,服务器必须由PostgreSQL用户帐户运行,而不是由root或任何其他用户运行 因此,您可能应该使用su postgres -c’…'来形成命令 例如:

    su postgres -c’pg_ctl start -D /usr/local/pgsql/data -l serverlog’

    服务器启动失败

    下面我们将更详细地解释一些最常见的错误消息

    在这里插入图片描述

    解释:您尝试在已经运行的同一端口上启动另一台服务器。但是,如果内核错误消息 Address already in use或者其中的一些变体,可能存在不同的问题。例如,尝试在保留端口号上启动服务器可能会产生如下情况:

    在这里插入图片描述

    在这里插入图片描述

    可能意味着内核对共享内存大小的限制小于PostgreSQL尝试创建的工作区(本例中为4011376640字节)。或者它可能意味着您根本没有配置System-V样式的共享内存支持。作为临时解决方法,您可以尝试使用小于正常数量的缓冲区(shared_buffers)启动服务器。您最终需要重新配置内核以增加允许的共享内存大小。如果请求的总空间超过内核限制,则尝试在同一台计算机上启动多个服务器时,您可能也会看到此消息。

    在这里插入图片描述

    不意味着你已经用完了磁盘空间。这意味着你的内核对System V信号量的限制小于PostgreSQL想要创建的数量。

    客户端连接问题
    在这里插入图片描述

    如上所示 尝试TCP / IP通信时 一个常见的错误是忘记配置服务器以允许TCP / IP连接
    在这里插入图片描述

    在尝试与本地服务器进行Unix域套接字通信时,您将获得此信息
    最后一行有助于验证客户端是否正在尝试连接到正确的位置。如果实际上没有服务器在那里运行,则内核错误消息通常也是Connection refused 或 No such file or directory
    其他错误消息,如连接超时 可能表明存在更多基本问题,例如缺乏网络连接。
    关闭服务器
    最简单方法:
    [postgres@localhost ~]$ pg_ctl stop
    waiting for server to shut down… done
    server stopped
    与oracle相同,在关闭时也可采用不同的模式,简介如下:
    SIGTERM
    不再允许新的连接,但是允许所有活跃的会话正常完成他们的工作,只有在所有会话都结束任务后才关闭。这是智能关闭。

    SIGINT
    不再允许新的连接,向所有活跃服务器发送 SIGTERM(让它们立刻退出),然后等待所有子进程退出并关闭数据库。这是快速关闭。

    SIGQUIT
    令 postgres 向所有子进程发送 SIGQUIT 并且立即退出(所有子进程也会立即退出),而不会妥善地关闭数据库系统。这是立即关闭。这样做会导致下次启动时的恢复(通过重放 WAL 日志)。我们推荐只在紧急的时候使用这个方法。

    SIGKILL
    此选项尽量不要使用,这样会阻止服务器清理共享内存和信号灯资源,那样的话你只能在启动服务器之前自己手工做这件事。另外,SIGKILL 直接把 postgres 杀掉,而不会等它把信号中继给它的子进程,因此我们还需要手工杀掉每个独立子进程。

    使用方法举例:
    [postgres@localhost ~]$ pg_ctl stop -o SIGTERM
    LOG: received smart shutdown request
    LOG: autovacuum launcher shutting down
    waiting for server to shut down…LOG: shutting down
    LOG: database system is shut down
    done
    server stopped

    pg_ctl程序提供了一个方便的接口发送这些信号关闭服务器。或者,您可以在非Windows系统上使用kill直接发送信号。的PID的的postgres的过程可以使用发现的PS节目,或从文件postmaster.pid在数据目录。例如,要快速关机:
    $ kill -INT head -1 /usr/local/pgsql/data/postmaster.pid

    重要提示:最好不要使用SIGKILL来关闭服务器。这样做会阻止服务器释放共享内存和信号量,这可能必须在启动新服务器之前手动完成。此外,SIGKILL杀死postgres进程而不让它将信号转发到其子进程,因此有必要手动杀死各个子进程。

    服务器配置

    设置参数
    所有参数名称都不区分大小写。每个参数都采用以下五种类型之一的值:布尔值,字符串,整数,浮点或枚举(枚举)。
    设置这些参数的最基本方法是编辑postgresql.conf文件,该文件通常保存在数据目录中。初始化数据库群集目录时,将安装默认副本。例如
    在这里插入图片描述

    每行指定一个参数。名称和值之间的等号是可选的。也可以是空白 ,空行被忽略 散列标记(#)将该行的其余部分指定为注释。不是简单标识符或数字的参数值必须是单引号。要在参数值中嵌入单引号,请写两个引号(首选)或反斜杠引号。
    修改配置文件后需要pg_ctl reload 生效
    可以通过sql修改参数
    PostgreSQL提供了三个SQL命令来建立配置默认值
    ALTER SYSTEM命令提供了一种SQL可访问的方法来更改全局默认值; 它在功能上等同于编辑postgresql.conf。
    在ALTER DATABASE命令允许全局设置要在每个数据库覆盖。
    该ALTER ROLE命令允许全局和每个数据库的设置与用户特定的值所覆盖。
    使用ALTER DATABASE和ALTER ROLE设置的值仅在启动新数据库会话时应用。它们覆盖从配置文件或服务器命令行获取的值,并构成会话其余部分的默认值。请注意,某些设置在服务器启动后无法更改,因此无法使用这些命令(或下面列出的命令)进行设置

    一旦客户端连接到数据库,PostgreSQL就会提供两个额外的SQL命令(和等效函数)来与会话本地配置设置进行交互:
    SHOW命令允许所有参数的当前值的检查
    SET命令允许可以在本地被设置为会话这些参数的当前值的修改; 它对其他会话没有影响。
    除了在数据库或角色级别设置全局默认值或附加替代之外,您还可以通过shell工具将设置传递给PostgreSQL。服务器和libpq客户端库都通过shell接受参数值。

    在服务器启动期间,可以通过-c命令行参数将参数设置传递给postgres命令。例如,

    postgres -c log_connections = yes -c log_destination =‘syslog’

    以这种方式提供的设置会覆盖通过postgresql.conf或ALTER SYSTEM设置的设置,因此不需重新启动服务器就无法全局更改它们。
    具体参数:
    文件位置
    data_directory(string)
    指定用于数据存储的目录。此参数只能在服务器启动时设置。

    config_file(string)
    指定主服务器配置文件(通常称为postgresql.conf)。此参数只能在postgres命令行上设置。

    hba_file(string)
    指定基于主机的身份验证的配置文件(通常称为pg_hba.conf)。此参数只能在服务器启动时设置。

    ident_file(字符串)
    指定用户名映射的配置文件(通常称为pg_ident.conf)。此参数只能在服务器启动时设置。

    external_pid_file(string)
    指定服务器应创建以供服务器管理程序使用的其他进程ID(PID)文件的名称。此参数只能在服务器启动时设置。
    连接设置:
    listen_addresses(string)
    指定服务器要侦听来自客户端应用程序的连接的TCP / IP地址。该值采用逗号分隔的主机名和/或数字IP地址列表的形式。特殊条目*对应于所有可用的IP接口。条目0.0.0.0允许侦听所有IPv4地址,并且::允许侦听所有IPv6地址。如果列表为空,则服务器根本不监听任何IP接口,在这种情况下,只能使用Unix域套接字连接到它。默认值为localhost 此参数只能在服务器启动时设置。

    port (integer)
    服务器侦听的TCP端口; 默认为5432。请注意,相同的端口号用于服务器侦听的所有IP地址。此参数只能在服务器启动时设置。

    max_connections (integer)
    确定与数据库服务器的最大并发连接数。默认值通常为100个连接 此参数只能在服务器启动时设置。
    运行备用服务器时,必须将此参数设置为与主服务器上相同或更高的值。否则,备用服务器中将不允许查询。

    superuser_reserved_connections (integer)
    确定PostgreSQL超级用户为连接保留的连接的数量。最多有max_connections个连接可以同时处于活动状态,所以活动并发连接数至少为
    max_connections减去superuser_reserved_connections。
    只接受超级用户的新连接,并且不接受新的复制连接。
    默认值为三个连接。该值必须小于max_connections的值。此参数只能在服务器启动时设置。

    unix_socket_directories (string)
    指定服务器要侦听来自客户端应用程序的连接的Unix域套接字的目录。可以通过列出用逗号分隔的多个目录来创建多个套接字。条目之间的空格被忽略; 如果需要在名称中包含空格或逗号,请用双引号括住目录名称。空值指定不侦听任何Unix域套接字,在这种情况下,只能使用TCP / IP套接字连接到服务器。默认值通常为/ tmp,但可以在构建时更改。此参数只能在服务器启动时设置。

    unix_socket_group (string)
    设置Unix域套接字的拥有组。(套接字的拥有用户始终是启动服务器的用户。)结合参数unix_socket_permissions,这可以用作Unix域连接的附加访问控制机制。默认情况下,这是空字符串,它使用服务器用户的默认组。此参数只能在服务器启动时设置。

    unix_socket_permissions (integer)
    设置Unix域套接字的访问权限 数字必须以0(零)开头。默认权限为0777,表示任何人都可以连接。合理的替代方案是0770(仅用户和组,另见unix_socket_group)和0700(仅用户) 此参数只能在服务器启动时设置。

    安全和身份验证:
    authentication_timeout (integer)
    完成客户端身份验证的最长时间,以秒为单位 如果潜在客户端在这么长时间内未完成身份验证协议,则服务器会关闭连接。这可以防止挂起的客户端无限期地占用连接。默认值为1分钟(1min)。此参数只能在postgresql.conf文件或服务器命令行中设置

    ssl (boolean)
    启用SSL连接。 默认为关闭。此参数只能在服务器启动时设置。只有TCP / IP连接才能进行SSL通信

    ssl_ca_file (string)
    指定包含SSL服务器证书颁发机构(CA)的文件的名称。默认值为空,表示未加载CA文件,并且不执行客户端证书验证。

    ssl_cert_file (string)
    指定包含SSL服务器证书的文件的名称。默认值为server.crt。

    ssl_crl_file (string)
    指定包含SSL服务器证书吊销列表(CRL)的文件的名称。默认值为空,表示未加载CRL文件。

    ssl_key_file (string)
    指定包含SSL服务器私钥的文件的名称。默认值为server.key。

    ssl_ciphers (string)
    指定允许在安全连接上使用的SSL密码套件列表。默认值为HIGH:MEDIUM:+ 3DES:!aNULL。除非您有特定的安全要求,否则通常是合理的。

    ssl_prefer_server_ciphers (bool)
    指定是否使用服务器的SSL密码首选项,而不是客户端。默认值为true

    ssl_ecdh_curve (string)
    指定要在ECDH密钥交换中使用的曲线的名称。

    password_encryption (boolean)
    如果在CREATE USER或ALTER ROLE中指定了密码而未写入ENCRYPTED或UNENCRYPTED,则此参数确定是否要加密密码。默认为打开(加密密码)。否则密码将明文存储

    krb_server_keyfile (string)
    设置Kerberos服务器密钥文件的位置。

    krb_caseins_users (boolean)
    设置是否应对不区分大小写的GSSAPI用户名进行处理。默认为关闭(区分大小写)

    db_user_namespace (boolean)
    此参数启用每个数据库的用户名。它默认是关闭的。此参数只能在postgresql.conf文件或服务器命令行中设置。
    如果启用此选项,则应将用户创建为username @ dbname
    db_user_namespace导致客户端和服务器的用户名表示不同。身份验证检查始终使用服务器的用户名完成,因此必须为服务器的用户名而不是客户端配置身份验证方法。由于md5在客户端和服务器上都使用用户名加盐处理,因此md5不能与db_user_namespace一起使用。
    资源消耗:
    shared_buffers (integer)
    设置数据库服务器用于共享内存缓冲区的内存量。默认值通常为128兆字节(128MB)此设置必须至少为128k字节 但是,通常需要明显高于最小值的设置才能获得良好的性能 shared_buffers的合理起始值是系统内存的25%

    huge_pages (enum)
    启用/禁用大内存页面的使用。有效值为try(默认值),on和off。

    temp_buffers (integer)
    设置每个数据库会话使用的最大临时缓冲区数。这些是仅用于访问临时表的会话本地缓冲区。默认值为8兆字节(8MB)。

    max_prepared_transactions (integer)
    设置可以同时处于“准备”状态的最大事务数 。将此参数设置为零(这是默认值)将禁用准备事务功能。
    如果您不打算使用准备好的事务,则应将此参数设置为零以防止意外创建准备好的事务。如果您正在使用准备好的事务,则可能希望max_prepared_transactions至少与max_connections一样大,以便每个会话都可以准备好待处理的事务。
    运行备用服务器时,必须将此参数设置为与主服务器上相同或更高的值。否则,备用服务器中将不允许查询。

    work_mem (integer)
    指定在写入临时磁盘文件之前内部排序操作和哈希表要使用的内存量。该值默认为4兆字节(4MB)。

    maintenance_work_mem (integer)
    指定维护操作要使用的最大内存量,例如VACUUM,CREATE INDEX和ALTER TABLE ADD FOREIGN KEY。它默认为64兆字节(64MB)。

    replacement_sort_tuples (integer)
    当要排序的元组数小于此数时,排序将使用替换选择而不是快速排序生成其第一个输出运行。这在内存受限的环境中可能很有用 默认值为150,000个元组。

    autovacuum_work_mem (integer)
    指定每个autovacuum工作进程使用的最大内存量。它默认为-1,表示应该使用maintenance_work_mem的值

    max_stack_depth (integer)
    指定服务器执行堆栈的最大安全深度。默认设置是2兆字节(2MB)

    dynamic_shared_memory_type (enum)
    指定服务器应使用的动态共享内存实现。

    temp_file_limit(integer)
    指定进程可用于临时文件的最大磁盘空间量,该值以千字节为单位,-1(默认值)表示无限制。只有超级用户才能更改此设置。

    max_files_per_process(integer)
    设置允许每个服务器子进程的最大同时打开文件数。默认值为一千个文件。
    基于成本的真空延迟:
    在执行VACUUM和ANALYZE命令期间,系统维护一个内部计数器,用于跟踪执行的各种I / O操作的估计成本。当累计成本达到极限(由vacuum_cost_limit指定)时,执行操作的过程将按照vacuum_cost_delay的指定休眠一小段时间。然后它将重置计数器并继续执行。
    此功能的目的是允许管理员减少这些命令对并发数据库活动的I / O影响。在很多情况下,像VACUUM和ANALYZE这样的维护命令很快完成并不重要; 但是,这些命令通常非常重要,不会严重干扰系统执行其他数据库操作的能力。基于成本的真空延迟为管理员提供了实现这一目标的方法。
    对于手动发出的VACUUM命令,默认情况下禁用此功能。要启用它,请将vacuum_cost_delay变量设置为非零值。

    vacuum_cost_delay(整数)
    超出成本限制时进程将休眠的时间长度(以毫秒为单位)。默认值为零,这将禁用基于成本的真空延迟功能。
    日志:

    wal_level (enum) minimal, replica, or logical

    wal_level确定将多少信息写入WAL。默认值为minimal,仅写入从崩溃或立即关闭中恢复所需的信息。副本添加WAL归档所需的日志记录以及在备用服务器上运行只读查询所需的信息。最后,逻辑添加了支持逻辑解码所需的信息。每个级别都包含所有较低级别记录的信息。此参数只能在服务器启动时设置。

    在minimal级别,可以安全地跳过某些批量操作的WAL记录,这可以使这些操作更快(参见第14.4.7节)。可以应用此优化的操作包括:

    CREATE TABLE AS

    CREATE INDEX

    CLUSTER

    COPY

    但是minimal的WAL不包含足够的信息来重建基本备份和WAL日志中的数据,因此必须使用replica 或更高版本来启用WAL归档(archive_mode)和流复制。

    在logical级别,记录与replica相同的信息,以及允许从WAL中提取逻辑变更集所需的信息。使用logical级别将增加WAL卷,特别是如果为REPLICA IDENTITY FULL配置了许多表并且执行了许多UPDATE和DELETE语句。

    在9.6之前的版本中,此参数还允许值archive和hot_standby。这些仍然被接受但映射到replica。

    fsync(布尔值)

    如果启用此参数,PostgreSQL服务器将尝试通过发出fsync()系统调用或各种等效方法来确保将更新物理写入磁盘(请参阅wal_sync_method)。这可确保数据库群集在操作系统或硬件崩溃后可以恢复到一致状态。

    虽然关闭fsync通常会带来性能优势,但在发生电源故障或系统崩溃时,这可能会导致无法恢复的数据损坏。因此,如果您可以轻松地从外部数据重新创建整个数据库,则建议关闭fsync。

    synchronous_commit(enum)

    指定在命令向客户端返回“成功”指示之前,事务提交是否将等待WAL记录写入磁盘。有效值为on,remote_apply,remote_write,local和off。默认且安全的设置为on 如果为off在向客户端报告成功和真正保证事务不受服务器崩溃影响之间可能存在延迟 最大延迟是wal_writer_delay的三倍

    与fsync不同的是,将该参数设置为off不会产生任何数据库不一致的风险:操作系统或数据库崩溃可能导致一些最近提交的事务丢失,但是数据库状态将与这些事务被完全终止时的状态相同。因此,当性能比事务持久性的确切确定性更重要时,关闭synchronous_commit可能是一个有用的选择。

    如果synchronous_standby_names非空,则此参数还控制事务提交是否将等待其WAL记录复制到备用服务器。设置为on时,提交将等待,直到来自当前同步备用数据库的回复表明它们已收到事务的提交记录并将其刷新到磁盘。这确保了事务不会丢失,除非主数据库和所有同步备用数据库都遭受其数据库存储的损坏。当设置为remote_apply时,提交将等待,直到来自当前同步备用数据库的回复表明它们已收到事务的提交记录并应用它,以便它对备用数据库上的查询可见。设置为时remote_write,commit将等待,直到来自当前同步备用数据库的回复表明它们已收到事务的提交记录并将其写入其操作系统。即使PostgreSQL的备用实例崩溃,此设置也足以确保数据保留,但如果备用数据库遇到操作系统级崩溃,则此设置不足,因为数据未必在备用数据库上达到稳定存储。最后,设置local会导致提交等待本地刷新到磁盘,而不是等待复制。在使用同步复制时通常不需要这样做,但是为了完整性而提供。

    如果synchronous_standby_names是空的,在设置上,remote_apply,remote_write和地方都提供相同的同步水平:提交事务只能等待本地刷新到磁盘。

    此参数可以随时更改; 任何一个事务的行为由提交时生效的设置决定。因此,可以并且有用的是使某些事务同步提交而其他事务异步提交。例如,要在默认值相反的情况下异步提交单个多语句事务,请在事务中发出SET LOCAL synchronous_commit TO OFF。

    wal_sync_method(enum)

    用于强制WAL更新到磁盘的方法。如果fsync关闭则此设置无关紧要,因为WAL文件更新根本不会被强制删除。可能的值是:

    open_datasync(使用open()选项O_DSYNC写入WAL文件)

    fdatasync(fdatasync()每次提交时调用)

    fsync(fsync()每次提交时调用)

    fsync_writethrough(fsync()在每次提交时调用,强制写入任何磁盘写入缓存)

    open_sync(使用open()选项O_SYNC写入WAL文件)

    full_page_writes(布尔值)

    当此参数打开时,PostgreSQL服务器在检查点之后首次修改该页面时,将每个磁盘页面的全部内容写入WAL。这是必需的,因为在操作系统崩溃期间正在处理的页面写入可能仅部分完成,从而导致包含旧数据和新数据混合的磁盘上页面。通常存储在WAL中的行级更改数据将不足以在崩溃后恢复期间完全恢复此类页面。存储整页图像可确保页面可以正确恢复,但代价是增加必须写入WAL的数据量。(因为WAL重放总是从检查点开始,所以在检查点之后的每个页面的第一次更改期间执行此操作就足够了。因此,降低整页写入成本的一种方法是增加检查点间隔参数。)

    关闭此参数可加快正常操作,但可能会在系统出现故障后导致无法恢复的数据损坏或静默数据损坏。风险类似于关闭fsync,虽然较小,但应仅根据为该参数推荐的相同情况关闭。

    wal_log_hints(boolean)

    当此参数打开时,PostgreSQL服务器会在检查点之后第一次修改该页面时将每个磁盘页面的全部内容写入WAL,即使对于所谓的提示位的非关键修改也是如此。

    如果启用了数据校验和,则提示位更新始终是WAL记录的,并忽略此设置。您可以使用此设置来测试如果您的数据库启用了数据校验和,将会发生多少额外的WAL日志记录。

    此参数只能在服务器启动时设置。默认值为off。

    wal_compression(布尔值)

    启用此参数后,PostgreSQL服务器会在full_page_writes打开时或基本备份期间压缩写入WAL的整页图像。在WAL重放期间将压缩页面图像。默认值为off。只有超级用户才能更改此设置。

    打开此参数可以减少WAL卷而不会增加不可恢复的数据损坏的风险,但代价是在WAL日志记录期间和WAL重放期间的解压缩上花费了一些额外的CPU。

    wal_buffers(整数)

    用于尚未写入磁盘的WAL数据的共享内存量。

    WAL缓冲区的内容在每次事务提交时写入磁盘,因此极大的值不太可能提供显着的好处。但是,将此值设置为至少几兆字节可以提高许多客户端一次提交的繁忙服务器的写入性能。默认设置-1选择的自动调整在大多数情况下应该给出合理的结果。

    wal_writer_delay(整数)

    指定WAL写入器刷新WAL的频率。在刷新WAL之后,它会休眠wal_writer_delay毫秒,除非被异步提交的事务唤醒。如果最后一次刷新发生的时间小于wal_writer_delay毫秒,并且此后生成的wal字节数少于wal_writer_flush_after,那么WAL只会写入操作系统,而不会刷新到磁盘。默认值是200毫秒(200毫秒)。

    wal_writer_flush_after(整数)

    指定WAL写入器刷新WAL的频率。如果最后一次刷新发生的时间小于wal_writer_delay毫秒,并且此后生成的wal字节数少于wal_writer_flush_after,那么WAL只会写入操作系统,而不会刷新到磁盘。如果wal_writer_flush_after设置为0,则立即刷新WAL数据。默认值为1MB。

    commit_delay(整数)

    commit_delay在启动WAL刷新之前添加以微秒为单位的时间延迟。

    如果系统负载足够高,使得额外的事务在给定的时间间隔内准备好提交,则可以通过允许大量事务通过单个WAL刷新提交来提高组提交吞吐量。但是,它还会使每次WAL刷新的延迟达到commit_delay微秒。因为如果没有其他事务准备好提交就会浪费延迟,只有在即将启动刷新时至少commit_siblings其他事务处于活动状态时才会执行延迟。此外,如果禁用fsync,则不会执行延迟。默认的commit_delay为零(没有延迟)。

    commit_siblings(整数)

    执行commit_delay延迟之前要求的最小并发打开事务数。较大的值使得更有可能在延迟间隔期间至少一个其他事务将准备好提交。默认值为五个事务。

    checkpoint_timeout(整数)

    自动WAL检查点之间的最长时间,以秒为单位。有效范围介于30秒和1天之间。默认值为五分钟(5分钟)。增加此参数可能会增加崩溃恢复所需的时间。

    checkpoint_completion_target(浮点)

    指定检查点完成的目标,作为检查点之间总时间的一小部分。默认值为0.5。

    checkpoint_flush_after(整数)

    只要在执行检查点时写入了多于checkpoint_flush_after个字节,请尝试强制操作系统向底层存储发出这些写入。这样做将限制内核页面缓存中的脏数据量,减少在检查点结束时发出fsync时停止的可能性,或者操作系统在后台以较大批量写回数据的可能性。有效范围介于0(禁用强制写回)和2MB之间。Linux上的默认值为256kB,其他地方为0

    如果检查点段文件填充引起的检查点之间的距离比这更近(这意味着应该提高max_wal_size),则向服务器日志写入一条消息。默认值是30秒(30秒)。0禁用警告。如果checkpoint_timeout小于checkpoint_warning,则不会生成警告。

    max_wal_size(整数)

    使WAL增长到自动WAL检查点之间的最大大小。这是一个软限制; 在特殊情况下,WAL大小可能超过max_wal_size,例如在高负载,失败的archive_command或高wal_keep_segments设置下。默认值为1 GB。增加此参数可能会增加崩溃恢复所需的时间。

    min_wal_size(整数)

    只要WAL磁盘使用率低于此设置,旧的WAL文件总是被回收以供将来在检查点使用,而不是被删除。这可用于确保保留足够的WAL空间来处理WAL使用中的峰值,例如在运行大批量作业时。默认值为80 MB。

    复制:
    这些设置控制内置流复制功能的行为 。服务器将是主服务器或备用服务器。主站可以发送数据,而备用站始终是复制数据的接收器。当使用级联复制(参见第26.2.7节)时,备用服务器也可以是发送者和接收者。参数主要用于发送和备用服务器,但某些参数仅在主服务器上有意义。

    max_wal_senders(整数)
    指定来自备用服务器或流式基本备份客户端的最大并发连接数(即,同时运行的WAL发送器进程的最大数量)。默认值为零,表示禁用复制。WAL发送者进程计算连接总数, 因此参数不能高于max_connections。 流客户端突然断开会导致单独的连接槽,直到超时, 所以这个参数应该设置的比预期的客户端的最大数量稍高一些, 这样断开的客户端可以立即重新连接。

    max_replication_slots(整数)
    指定服务器可以支持的最大复制槽数(请参阅第26.2.6节)。默认值为零。必须将wal_level设置为replica或更高版本才能使用复制槽。将其设置为低于当前现有复制插槽数的值将阻止服务器启动。

    wal_keep_segments(整数)
    指定保存在pg_xlog目录中的过去日志文件段的最小数量,以防备用服务器需要获取它们以进行流复制。每个段通常是16兆字节。如果连接到发送服务器的备用服务器落后超过wal_keep_segment段,则发送服务器可能删除备用服务器仍然需要的WAL段,在这种情况下复制连接将终止。下游连接最终也会因此而失败。(不过,如果使用了WAL archiving,备用服务器可以通过从archive中获取段来恢复。)

    这只设置pg_xlog中保留的最小段数;系统可能需要为WAL归档保留更多的段,或者从检查点恢复。如果wal_keep_segment为零(缺省值),系统不会为备用目的保留任何额外的段,因此,备用服务器可用的旧WAL段的数量是前一个检查点位置和WAL归档状态的函数。此参数只能在postgresql.conf文件或服务器命令行中设置。

    wal_sender_timeout(整数)
    终止非活动状态超过指定毫秒数的复制连接。这对于发送服务器检测备用崩溃或网络中断很有用。值为零会禁用超时机制。此参数只能在postgresql.conf文件或服务器命令行中设置。默认值为60秒。

    track_commit_timestamp(bool)
    记录事务的提交时间。此参数只能在postgresql.conf文件或服务器命令行中设置。默认值为off。

    synchronous_standby_names(string)
    指定可支持同步复制的备用服务器列表 将有一个或多个有源同步备用数据库; 在这些备用服务器确认收到其数据后,将允许等待提交的事务继续进行。如果任何当前的同步备用数据库因任何原因断开连接,它将立即被替换为次高优先级备用数据库。指定多个备用名称可以实现非常高的可用性。
    此参数使用以下任一语法指定备用服务器列表:
    num_sync(standby_name [,…])
    standby_name [,…]
    其中num_sync是事务需要等待回复的同步备用数,而standby_name是备用服务器的名称。例如,设置3(s1,s2,s3,s4)使事务提交等待,直到从备用服务器s1,s2,s3和s4中选择的三个更高优先级的备用数据库接收到它们的WAL记录。

    如果此处未指定同步备用名称,则不启用同步复制,并且事务提交不会等待复制。这是默认配置。即使启用了同步复制,也可以将单个事务配置为不等待复制,方法是将synchronous_commit参数设置为local或off。

    vacuum_defer_cleanup_age(整数)
    指定VACUUM和HOT更新将推迟清除死行版本的事务数。默认值为零事务,这意味着可以尽快删除死行版本,也就是说, 它们不再对任何打开的事务可见。
    您还应该考虑在备用服务器上设置hot_standby_feedback作为使用此参数的替代方法。
    这不会阻止已达到old_snapshot_threshold指定年龄的死行的清除。

    备用服务器参数
    这些设置控制要接收复制数据的备用服务器的行为。它们在主服务器上的值是无关紧要的。
    hot_standby(boolean)
    指定是否可以在恢复期间连接和运行查询, 默认值为off。

    max_standby_archive_delay(整数)
    当Hot Standby热备是活跃时,这个参数决定取消与应用的WAL项冲突的备用查询之前,备用服务器应等待多久。 max_standby_archive_delay适用于当从WAL归档中读取WAL数据时, 。默认值为30秒。如果未指定,则单位为毫秒。值-1允许备用数据库永远等待冲突查询完成。此参数只能在postgresql.conf文件或服务器命令行中设置。

    请注意,max_standby_archive_delay与取消前查询可以运行的最长时间不同; 相反,它是允许应用任何一个WAL段数据的最大总时间。因此,如果一个查询在WAL段中早先导致显着延迟,则后续冲突查询将具有更少的宽限时间。

    max_standby_streaming_delay(整数)
    当Hot Standby处于活动状态时,此参数确定备用服务器在取消与即将应用的WAL条目冲突的备用查询之前应等待的时间,如第26.5.2节中所述。当通过流复制接收WAL数据时,应用max_standby_streaming_delay。默认值为30秒。如果未指定,则单位为毫秒。值-1允许备用数据库永远等待冲突查询完成。此参数只能在postgresql.conf文件或服务器命令行中设置。

    请注意,max_standby_streaming_delay与取消前查询可以运行的最长时间不同; 相反,它是从主服务器收到WAL数据后允许应用的最大总时间。因此,如果一个查询导致显着延迟,则后续冲突查询将具有更少的宽限时间,直到备用服务器再次赶上。

    wal_receiver_status_interval(整数)
    指定备用数据库上WAL接收器进程的最小频率,以将有关复制进度的信息发送到主备用数据库或上游备用数据库,使用pg_stat_replication视图可以查看该进程。备用数据库将报告它已写入的最后一个事务日志位置,它已刷新到磁盘的最后位置以及它已应用的最后一个位置。此参数的值是报告之间的最大间隔(以秒为单位)。每次写入或刷新位置发生更改时发送更新,或者至少按此参数指定的频率发送更新。因此,应用位置可能略微落后于真实位置。将此参数设置为零会完全禁用状态更新。此参数只能在postgresql.conf中设置文件或服务器命令行。默认值为10秒。

    hot_standby_feedback(布尔值)
    指定热备用服务器是否将向主服务器或上游备用服务器发送有关当前在备用服务器上执行的查询的反馈。此参数可用于消除由清理记录引起的查询取消,但可能导致某些工作负载的主数据库膨胀。每个wal_receiver_status_interval不会比一次更频繁地发送反馈消息。默认值为off。此参数只能在postgresql.conf文件或服务器命令行中设置。

    如果正在使用级联复制,则反馈将在上游传递,直到最终到达主数据库。除了通过上游之外,备用服务器不会使用他们收到的反馈。

    此设置不会覆盖主服务器上old_snapshot_threshold的行为; 备用数据库上超过主要年龄阈值的快照可能会变为无效,从而导致备用数据库上的事务被取消。这是因为old_snapshot_threshold旨在提供对死行可能导致膨胀的时间的绝对限制,否则由于备用数据库的配置而会违反该行。

    wal_receiver_timeout(整数)
    终止非活动状态超过指定毫秒数的复制连接。这对于接收备用服务器检测主节点崩溃或网络中断很有用。值为零会禁用超时机制。此参数只能在postgresql.conf文件或服务器命令行中设置。默认值为60秒。

    wal_retrieve_retry_interval(整数)
    指定在重试检索WAL数据之前,当WAL数据从任何源(流复制,本地pg_xlog或WAL归档)不可用时,备用服务器应等待的时间。此参数只能在postgresql.conf文件或服务器命令行中设置。默认值为5秒。如果未指定,则单位为毫秒。

    此参数在恢复中的节点需要控制等待新WAL数据可用的时间量的配置中非常有用。例如,在归档恢复中,通过减少此参数的值,可以在检测新的WAL日志文件时使恢复更具响应性。在具有低WAL活动的系统上,增加它会减少访问WAL归档所需的请求数量,这在云环境中是有用的,例如在考虑基础设施访问次数的云环境中。

    监控数据活动

    pgsql用于收集统计信息的进程
    stats collector process
    数据库再启动之后 就可以看到该进程 代码对应的是 “src/backend/postmaster/pgstat.c”
    数据库的统计信息是放在shared buffer中的 初始化数据库initdb时回向其中填充0,如果已经有统计信息则调用函数pgstat_read_statsfiles 来读取上一次数据库关闭时记录的一些历史信息( G P D A T A / p g s t a t ) 除 了 写 入 到 内 存 中 还 会 写 入 到 一 个 临 时 的 目 录 中 这 个 目 录 在 配 置 文 件 中 配 置 s t a t s t e m p d i r e c t o r y 数 据 库 正 常 关 闭 时 会 把 统 计 信 息 从 这 个 临 时 目 录 拷 贝 到 GPDATA/pgstat) 除了写入到内存中还会写入到一个临时的目录中 这个目录在配置文件中配置stats_temp_directory 数据库正常关闭时会把统计信息从这个临时目录拷贝到 GPDATA/pgstatstatstempdirectoryGPDATA/pgstat中 确保统计信息不会丢失

    统计信息的收集纬度配置 配置文件:
    1.track_actibities 布尔 收集sql执行开始时间以及sql语句的内容,默认打开
    2.track_activitu_query_size int 指定统计信息允许存储sql语句的长度 默认1024 超出截断
    3.track_counts 布尔 计数器是否打开 如新增行数删除行数等autovacuum进程需要用到这部分信息 不能关闭
    analyze table1 分析表
    select *,now() from pg_stat_all_tables where relname=‘table1’;

    autovacuum 记录改变超过某些阈值的时候会触发自动的analyze或vacuum
    该阈值和配置文件参数有关:
    autovacuum = on 打开自动垃圾回收
    autovacuum_vacuum_threshold = 50 a
    autovacuum_analyze_threshold = 50 a
    autovacuum_vaccum_scale_factor =0.2 b
    autovacuum_analyze_scale_factor =0.2 b
    阈值=a+b*c
    c=select reltuples from pg_class where relname=‘test’;

    4.track_io_timing 布尔 收集io操作的时间开销 对于数据库有额外的开销
    pf_test_timing -d 10 查看十秒钟会浪费多少纳秒 nsec
    5.track_functions enum 跟踪函数的调用次数和时间开销 默认关闭
    6.update_process_title 布尔 查看进程的command的更新 默认打开

    查看当前活动的会话信息
    select query,query_start from pg_stat_activity where state<>‘idle’;

    查看sql语句级别的统计信息
    需要用到pg_stat_statements插件 此插件非常有用
    配置文件配置:
    shared_reload_libraries = ‘pg_stat_statements’
    pg_stat_statements.max = 1000 #最多跟踪1000条sql
    pg_stat_statements.track = all #跟踪所有sql

    create extension pg_stat_statements;
    按照耗时倒叙输出
    select * from gp_stat_statements order by total_time desc limit1 offset 0;
    调用次数倒叙输出
    select * from gp_stat_statements order by calls desc ;
    单次sql执行时间
    select * from gp_stat_statements order by total_time/calls desc ;

    按shared buffer 未命中倒序输出
    select * from gp_stat_statements order by shared_blks_read desc ;

    查看bgwriter的统计信息 pg_stat_bgwriter
    checkpoints_timed 多少个计划的checkpoints

    (配置文件中配置指定:
    checkpoint_segments=3 #产生3个wal日志文件后执行checkpoint
    checkpoint_timeout=5min #五分钟没有活动 执行checkpoint
    此时记录的checkpoint会记录到checkpoints_timed)

    checkpoints_req 请求的checkpoint
    checkpoint_write_time
    checkpoint_sync_time
    buffers_checkpiont
    buffers_clean
    maxwritten_clean
    buffers_backend 该值太大说明shared buffer不够或bgwrite 休眠时间太长
    buffers_backend_fsync
    buffers_alloc
    stats_reset 上次的重置时间

    查看数据库的统计信息,如数据库的事务提交次数,回滚次数,w未命中数据块读,命中读,行的统计信息(扫描输出、增删改),临时文件等

    select tup_returned,tup_fetched from pg_stat_database where datname=‘mydb’
    pg_stat_database表:
    datname 数据库名
    datid 数据库id
    numbackends 后台链接进程
    xact_commit 提交事务数(自从上次重置信息以来)
    xact_rollback 回滚事务数
    blks_read 读了多少个数据块 未命中读
    blks_his 命中读 在shared buffer中的书库
    tup_returned 指扫描了多少行
    tup_fetched 指输出多少行
    tup_inserted 插入行数
    tup_updated 更新行数
    tup_deleted 删除行数
    conflicts 冲突次数 数据库查询和复制的recovery发生的冲突
    temp_file 创建了多少个临时文件
    temp_bytes 临时文件的大小
    deadlocks 发生了多少次死锁

    表级统计信息
    pg_stat_all_tables
    relid 表id
    schemaname 表所属schema
    relname 表名
    sql_scan 通过全表扫描扫描的数据块
    sql_tup_read 全表扫描时返回了多少行
    idx_scan 索引扫描的数据块
    idx_tup_fetch 索引扫描返回的行数
    n_tup_ins insert了多少行
    n_tup_upd update了多少行
    n_tup_del delete了多少行
    n_tup_hot_upd 有多少行时hot的update
    n_live_tup 有多少live行
    n_dead_tup 有多少行已经dead
    last_vacuum 最后一次vacuum的时间
    last_autovacuum 最后一次自动vacuum的时间
    last_analyze 最后一次analyze的时间
    last_autoanalyze 最后一次自动analyze的时间
    vacuum_count 一共发生了多少次vacuum
    autovacuum_count 一共发生了多少次自动vacuum
    analyze_count 一共发生了多少次analyze
    autoanalyze_count 一共发生了多少次自动analyze

    索引相关信息表
    pg_stat_all_indexes
    idx_scan 通过此索引扫描了多少次
    idx_tup_read 扫描了多少行
    idx_tup_fetch 返回了多少行

    io统计信息表 pg_statio_all_tables
    heap_blks_read 未命中shared_buffer的读
    heap_blks_hit 命中shared_buffer的读
    idx_blks_read 索引块未命中读
    idx_blks_hit
    toast_blks_read 这个表上的toast表块未命中读
    toast_blks_his
    tidx_blks_read 这个表上的toast表索引块未命中读
    tidx_blks_hit

    索引io相关信息表 pg_statio_all_indexes
    idx_blks_read 此索引未命中shared_buffer的读
    idx_blks_hit 此索引命中shared_buffer的读

    函数统计信息,调用次数,总时间开销
    必须先打开track_function函数
    pg_stat_user_functions
    calls 调用次数
    total_time 总时间开销
    self_time 仅仅自己调用的部分的时间开销 如A函数中包含了一个B函数

    流复制统计信息 pg_stat_replication (在主节点查询)
    pid
    usesysid
    usename 从库连接的用户名
    application_name
    client_addr 客户端ip
    client_hostname 客户端主机名
    client_port 客户端端口
    backend_start 对应的主节点的启动时间
    state 当前的状态
    sent_location wal日志发送到从库的位置
    write_location wal日志写位置
    flush_location wal日志刷新到磁盘位置
    replay_location wal日志重放recoery的位置
    sync_priority 同步节点的优先级
    sync_state sync/async 同步节点/异步节点

    standby的冲突统计视图
    从库在apply log时可能与正在standby执行的sql发生冲突
    例如查询的表在wal日志中有truncate的信息,那么就发生了冲突

    展开全文
  • 大部分数据库表都有一个承接的功能就是某个表每条记录的唯一性,通过唯一性来保证这张表的数据是不重复的。使用的场景很多,例如银行,每个人只能开一个1类账户,怎么来保证所有的人来银行开账户都是...
  • 现期望多个列组合数据表示点记录在数据表里唯一,结局办法就是加多列组合唯一索引。 本文以col1, col2和col3三列组合为唯一索引。数据表名为table_name 这时如果使用: alter table table_name add unique &...
  • 1 单字段唯一键 我们知道beego orm中可以这样设置 Name string `orm:"unique"` 2 多字段唯一键 ...2 代码中配置(注意必须自动建表,且如果已建好的表要先删除) // 多字段唯一键 fu...
  • postgresql 查看索引、创建、删除索引

    万次阅读 2018-11-03 10:13:29
    查看索引 select * from pg_indexes where tablename='tbname'; 或者 select * from pg_statio_all_indexes where relname='tbname'; 创建索引 create index tbl_bb_index on tbl_bb(id,name); 注:tbl_bb 位...
  • pgsql分表

    千次阅读 2014-06-05 10:58:22
    同时在其上定义任何索引或者唯一约束也没有意义。 创建几个 "子" 表,每个都从主表上继承。 通常,这些表将不会对从主表继承过来集合增加任何字段。 我们将把子表称作分区,尽管它们就是普通的  ...
  • pgsql的存储引擎

    千次阅读 2020-10-17 16:58:50
    在PostgreSQL中存在Tuple和Index Tuple两种数据存储格式,其中Tuple为我们组织普通数据的方式,而Index Tuple则是我们组织索引数据所采用的方式。 #1. Tuple 和 Index Tuple. 存储的过程: (1) 读取元组时所需要的...
  • pgsql常用命令

    千次阅读 2015-07-03 16:06:53
    \unset 名字 取消(删除)内部变量 \w [文件名] 将当前查询缓冲区写出到文件 \x 在扩展输出之间切换 (目前是 关闭) \z [模式] 列出表访问权限 (和 \dp 一样) \! [命令] 在 shell 里执行命令或者开始...
  • PGSQL并发控制

    2021-10-14 16:58:53
    当前,UPDATE情况下考虑的为可以在其上有唯一索引、可用于外键的列(所以不考虑部分索引和表达式索引),不过将来这种机制可能会改变。 FOR NO KEY UPDATE 行为与FOR UPDATE类似,不过锁级别低一些;该锁不会阻塞在...
  • 在创建完索引之后的表,执行插入、更新和删除操作时,索引需要更新,故耗时会成倍增加。 2.索引管理 2.1创建索引 创建索引时,不能包括schema模式名,因为索引默认被创建在其基表所在的模式中,创...
  • raster2pgsql命令参数详解

    千次阅读 2020-12-08 15:21:42
    raster2pgsql -? 小技巧: 打印出来的帮助信息凡是类似-s <srid>这佯在参数后带<>的,都是表示此参数后要带输入信息. USAGE: raster2pgsql [<options>] <raster>[ <raster>[ ...]] [[&...
  • PostgreSQL 11 新特性之分区索引

    千次阅读 2019-01-07 18:01:52
    PostgreSQL 11 支持基于分区表创建索引,并且自动为每个分区创建具有相同属性的索引。PostgreSQL 11 还支持分区表上的唯一约束(主键和唯一键)。
  • postgreSql 1、准备 1.1. 安装 1.1.1. 安装postgreSql数据库 ...CALL( 调用过程)、MERGE(合并...索引(index):为了增加查询速度而对表字段附加的一种标识 数据行:在pgsql中被称为Tuple,其他数据库称为row
  • pgsql 系统字段

    千次阅读 2014-06-05 10:06:51
    在唯一约束(或者唯一索引)存在的时候,系统会注意不去生成一个和现有行相同的 OID。 (当然,只有在表中的数据行少于 2  32  (40 亿)行的时候才是可能的, 而实际上表中的行最好远比这个小,要不性能就会受...
  • pgsql 表继承

    千次阅读 2014-06-05 10:55:05
    继承特性的一个严重的局限性是索引(包括唯一约束)和外键约束只施用于单个表, 而不包括它们的继承的子表。这一点不管对引用表还是被引用表都是事实,因此,在上面的例子里: 如果我们声明  cities...
  • pgsql对象标识符类型

    2017-02-15 17:34:33
    帮Python找“对象” 【技术直播】揭开...pgsql对象标识符类型 2014-06-05 15:33 463人阅读 评论(0) 收藏 举报 本文章已收录于: 分类: web开发(136) 作者同类文章X 数据
  • pgsql的sql实例(持续更新)

    千次阅读 2019-08-01 23:43:22
    pgsql常用的sql以及对应的实例。 二、具体实例 1.pgsql总结:函数 (1)连接字符:concat或者|| 实例: -》SELECT concat(student_de.id,student_de.NAME) hebing from student_de INNER join studentbak on ...
  • pgsql--应用进阶

    2020-03-12 10:26:29
    文章目录joininner joinleft join,right join,full outer joinunionunionunion all别名触发器索引alter table修改表结构truncate table 删除表数据,而不删除表结构 join inner join yuangong表 id | name | ...
  • oracle转pgsql的建表函数

    千次阅读 2019-09-07 12:32:37
    这里简单的转换了一下oracle到pgsql的建表转换,主键做了转换,外键,索引,触发器等还没有转。。。。。 create or replace function long_to_char(p_owner varchar2, p_tab varchar2, cid in number) return ...
  • 2pgsql体系结构

    千次阅读 2020-02-22 12:18:58
    且有些文件之间会关联 默认Pg所有数据都存储在其数据目录里 常用环境变量PGDATA引用   OID 整个数据集簇中唯一地标识一个数据库对象, 数据库、表、索引、视图、元组、类型 Pg提供Oid类型表示 无符号整   OID从1, ...
  • 请注意,和这个字段相关的索引和表约束也会被自动删除。 如果任何表之外的对象依赖于这个字段, 你必须说  CASCADE ,比如,外键参考,视图等等。 ALTER COLUMN TYPE 这种类型改变表中一个字段的类型。该字段...
  • PG创建or修改索引

    千次阅读 2019-09-18 06:23:59
    pgsql中的索引不能重名,重名的创建失败。创建二级索引的命令:create index CONCURRENTLY idx_abc on tb1(a,b);注意:reindex 重建索引的过程是阻塞的,一般大表不建议使用这个命令,可以重建一个索引,然后删除老...
  • pgSQL笔记07-性能优化

    2020-05-25 14:25:07
    【性能优化】 一、优化查询 1.分析查询语句explain explain analyze select * from .../*然后,在person表的name字段加上索引,再进行explain */ create index index_name ON person(name); explain analyz
  • pgsql分区表有一个笔者非常喜欢的特性,分区列是自由的,不和唯一约束绑定,在诸如SQL Server、MySQL中,当你尝试把普通表转换为分区表时,会强制要求你把分区列加到唯一性约束中(主键、唯一约束/索引)...
  • 3.pgsql数据类型学习

    2020-02-11 17:35:14
    弄好pgsql的环境后,我大概玩了一下,并且数了下里面的数据类型,发现至少有一百多种,WTF? PostgreSQL 有着丰富的内置数据类型可用。用户还可以使用CREATE TYPE命令增加新的数据类型(摘抄官网)。 PostgreSQL...
  • PostgreSQL 索引

    千次阅读 2018-05-22 15:44:40
    索引一、索引的介绍1、索引的含义和特点 索引用于快速找出在某一列中有某一特定值的行。索引是对数据库表中一列或多列的值进行排序的一种结构,... 优点: ①、通过创建唯一索引,可以保证数据库表中每一行数...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 962
精华内容 384
关键字:

pgsql删除唯一索引