Postgresql stream复制高可用集群搭建——筑梦之路_pg stream

规划：
 
复制技术：PostgreSQL Stream
 
主从架构：一主两从
 
数据库版本：13.6
 
操作系统：Centos 7.9
 
192.168.50.10	Centos 7	13.6	主	软件+DB
192.168.50.11	Centos 7	13.6	从	软件
192.168.50.12	Centos 7	13.6	从	软件
 
部署流程为：
1、在三个节点分别安装PG软件；
2、在作为主节点的节点上初始化数据库；
3、修改postgresql.conf，pg_hba.conf配置文件；
4、通过pg_basebackup做主备数据恢复，
5、启动备机实例，完成部署。
 
在所有节点上安装PG软件，只在作为主节点的节点上初始化数据库
 
#在所有节点安装同版本的PG
 
 
 
#配置主节点
 
##创建用户，命令中已经指明权限包括：REPLICATION和LOGIN
 
CREATE USER repusr
 REPLICATION
 LOGIN
 CONNECTION LIMIT 2
 ENCRYPTED PASSWORD 'repusr';
 
##修改postgresql.conf
 
listen_addresses = '*' 
archive_mode = on
archive_command = 'cp %p /opt/pgsql/pg_archive/%f'
wal_level = hot_standby 
max_wal_senders = 10
wal_sender_timeout = 60s
max_connections = 100
wal_log_hints = on
 
##修改pg_hba.conf，增加两台从服务器的白名单信息，这里我们暂时用相对较弱的md5加密策略
 
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    replication     repusr          192.168.50.11/32       md5
host    replication     repusr          192.168.50.12/32       md5
 
##重启数据库
pg_ctl restart -D $PGDATA
 
#从节点通过pg_basebackup恢复数据并建立主从关系
 
分别在两台从节点上执行，需要注意两点：
 
使用postgres用户；
 
从机上要提前建好$PGDATA目录，权限要求为700或者750，如果时其他权限会报权限过大问题。
 
$ pg_basebackup -h 192.168.50.10 -U repusr -D /data/pgdata -X stream  -P -R
Password: 
122597/122597 kB (100%), 1/1 tablespace
-bash-4.2$ ls -lrt
 
-X表示以stream方式包含所需的WAL文件；
 
-P表示显示进度；
 
-R表示为replication写配置信息；
 
#启动从库，验证主从关系
 
pg_ctl start -D /data/pgdata
 
 
#登录主库，验证主从关系
 
$ psql
 
postgres=# \x
 
postgres=# select * from pg_stat_replication;
 
 

psql元命令是指以反斜线开头的命令，元命令使我们可以更便捷地管理数据库，比如列出中数据库各种对象的名称等，不需要书写sql语句，直接使用元命令就可以简单地查看
 
\?:  查看元命令的帮助。
 
\l: 列出所有数据库。
 
\encoding: 查看字符集。
 
\encoding 字符集: 设置字符集。
 
\password user_name: 修改用户密码。
 
\x: 以列显示的开关。相当于mysql中的\G。执行一次为打开，再执行一次为关闭。
 
\timing on｜off: 设置是否显示执行时长。
 
\set AUTOCOMMIT on|off: 打开/关闭自动提交功能。
 
\conninfo: 显示连接信息。
 
\! : 执行shell命令。如：\! date， 输出当前日期。
 
\i filename: 执行filename文件中的sql语句，也可用psql -s filename。
 
\q: 退出psql命令行环境。
 
\e：打开文本编辑器。
 
## \pset
\pset border 0/1/2：设置执行结果的边框样式。
 
\pset border 0: 输出内容无边框，无任何|
\pset border 1: 边框只在内部有，无外边框
\pset border 2: 内外都有边框
 
\gexec：将当前查询缓冲区发送到服务器，然后将查询输出（如果有的话）的每一行的每一列都要作为要执行的SQL语句处理。这个用法可参考：Psql之\gexec命令。
 
 
## \c
\c: 查看当前数据库和用户。查询当前数据库等同于select current_database();查询当前用户等同于select current_user;
\c db_name: 进入指定的数据库。
\c database user_name : 切换到某个数据库下某个角色
 
## \d
\dn: 列出当前库下所有schema。
\d: 查看当前数据库下的所有表、视图和序列。
\dt: 只查看数据库中的所有表。
\d tb_name： 查看表结构定义。
\dt+ tb_name: 查看表大小等属性。
\db: 查看表空间。
\du: 列出所有用户及其用户权限。
\ds: 查看用户自定义序列。
\df: 查看用户自定义函数。
 
怎么查看查看元命令执行的具体sql语句？
 
在psql启动命令行中加入-E。
例：/usr/local/postgresql/bin/psql -h 127.0.01 -p 5432 -d postgres -E
 
如果是已经登入服务器，可以通过 \set ECHO_HIDDEN on|off 开启或关闭输出执行SQL的功能。
 
## 查看数据库版本。
select version();
 
## 查看表空间
select * from pg_tablespace;
 
## 大小相关的。
1)查看表空间大小
select pg_tablespace_size('pg_default');
 
2)查看各个表空间的大小
select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;
 
3)查看DB大小
select pg_size_pretty(pg_database_size(db_name)); 
 
4)查看所有数据库的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;
 
5)查看表大小
select pg_size_pretty(pg_relation_size(table_name))
 
6)按占空间大小，顺序查看所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
 
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='public' group by 1;
 
7)按占空间大小，顺序查看索引大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
 
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||indexname))/1024/1024) "Size_MB" from pg_indexes where schemaname='public' group by 1;
 
## 查看各数据库数据创建时间
select datname,(pg_stat_file(format('%s/%s/PG_VERSION',case when spcname='pg_default' then 'base' else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/' end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;
 
## 索引相关
1）创建索引。
CREATE INDEX index_name ON table_name (column_name, ...);
 
2) 并发创建索引（在线创建索引）。
CREATE INDEX CONCURRENTLY
 
3）删除索引
DROP INDEX idx_name; 
drop index concurrently idx_name;
 
4）查看表的所有索引信息
select * from pg_indexes where tablename='student';
 
5）显示关于访问特定索引的I/O统计信息。
select * from pg_statio_all_indexes where relname='events';
 
6）显示索引类型
select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in (
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'events');
 
7）显示索引大小
SELECT c.relname,c2.relname, c2.relpages*8 as size_kb
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'events' AND
c.oid = i.indrelid AND
c2.oid = i.indexrelid
ORDER BY c2.relname; 
 
## 查看表的约束
select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'events';
 
## 查看表所对应的数据文件路径与大小
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'events';
 
## 序列相关
1）查看序列
select * from information_schema.sequences where sequence_schema = 'public';
 
2）创建序列：
create sequence seq_user_camera_version increment by 1 minvalue 1 no maxvalue start with 1;
 
3）建表，并用上面的序列作为主键自增序列
CREATE TABLE public.user_camera_version (
    id int4 NOT NULL DEFAULT nextval('seq_user_camera_version'::regclass),
    user_id int4 NULL,
    user_type varchar(1) NULL,
    hardware_version varchar(100) NULL,
    software_version varchar(100) NULL,
    modify_date timestamp NULL,
    CONSTRAINT user_camera_version_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
) ;
 
## 函数相关
1）查看所有用户自定义函数。
\df 
或
SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
 
 
2）查看函数定义
select oid,* from pg_proc where proname = 'insert_platform_action_exist'; 
select * from pg_get_functiondef(oid);
 
3）创建函数
CREATE FUNCTION add1(integer, integer) RETURNS integer
 
        AS 'select $1 + $2;'
 
        LANGUAGE SQL
 
        IMMUTABLE
 
RETURNS NULL ON NULL INPUT;
 
## 查看视图
select * from pg_views where schemaname = 'public';
select * from information_schema.views where table_schema = 'public';
 
## 锁相关
1）查看锁等待信息。
select * from pg_locks where granted is not true;
 
2）查看会话。
select * from pg_stat_activity;
 
## 查看postgresql数据库启动时间
select pg_postmaster_start_time();
 
## 查询当前客户端的端口号
select inet_client_port();
 
查看与当前会话相关联的服务器进程ID
select pg_backend_pid();
 
查看配置文件最后一次载入时间
select pg_conf_load_time
 
 
## 查看参数文件
show config_file;
show hba_file;
show ident_file;
 
## 查看当前会话的参数值
show all;
 
## 查看参数值
select * from pg_settings;
 
## 查看某个参数值,比如参数work_mem
show work_mem
 
##修改某个参数值,比如参数work_mem
alter system set work_mem='8MB'
 
--使用alter system命令将修改postgresql.auto.conf文件，而不是postgresql.conf，这样可以很好的保护postgresql.conf文件，加入你使用很多alter system命令后搞的一团糟，那么你只需要删除postgresql.auto.conf，再执行pg_ctl reload加载postgresql.conf文件即可实现参数的重新加载。
 
 
## 查看是否开启归档
show archive_mode;
 
## 运行日志相关
--运行日志包括Error信息，定位慢查询SQL，数据库的启动关闭信息，checkpoint过于频繁等的告警信息。
show logging_collector;--启动日志收集
show log_directory;--日志输出路径
show log_filename;--日志文件名
show log_truncate_on_rotation;--当生成新的文件时如果文件名已存在，是否覆盖同名旧文件名
show log_statement;--设置日志记录内容
show log_min_duration_statement;--运行XX毫秒的语句会被记录到日志中，-1表示禁用这个功能，0表示记录所有语句，类似mysql的慢查询配置
 
## 用户和权限相关
1）创建用户
create user u2 with login CREATEROLE CREATEDB password 'u2';
create user u2  password 'u2';
 
2）修改数据库owner
ALTER DATABASE name OWNER TO new_owner;
 
 
3）设置用户对某个数据库的所有权限
GRANT ALL PRIVILEGES ON DATABASE database_name to username;
 
4）授予用户CONNECT到数据库的权限
GRANT CONNECT ON DATABASE database_name TO username;
 
 
5） 授予public模式中所有表的所有权限给用户。
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
 
6）授予public模式中所有序列的所有权限给用户:
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO username;

查看表的建表语句SQL
 
CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
  RETURNS text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
BEGIN
    FOR column_record IN 
        SELECT 
            b.nspname as schema_name,
            b.relname as table_name,
            a.attname as column_name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
            CASE WHEN 
                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                 FROM pg_catalog.pg_attrdef d
                 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                              FROM pg_catalog.pg_attrdef d
                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
            ELSE
                ''
            END as column_default_value,
            CASE WHEN a.attnotnull = true THEN 
                'NOT NULL'
            ELSE
                'NULL'
            END as column_not_null,
            a.attnum as attnum,
            e.max_attnum as max_attnum
        FROM 
            pg_catalog.pg_attribute a
            INNER JOIN 
             (SELECT c.oid,
                n.nspname,
                c.relname
              FROM pg_catalog.pg_class c
                   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relname ~ ('^('||p_table_name||')$')
                AND pg_catalog.pg_table_is_visible(c.oid)
              ORDER BY 2, 3) b
            ON a.attrelid = b.oid
            INNER JOIN 
             (SELECT 
                  a.attrelid,
                  max(a.attnum) as max_attnum
              FROM pg_catalog.pg_attribute a
              WHERE a.attnum > 0 
                AND NOT a.attisdropped
              GROUP BY a.attrelid) e
            ON a.attrelid=e.attrelid
        WHERE a.attnum > 0 
          AND NOT a.attisdropped
        ORDER BY a.attnum
    LOOP
        IF column_record.attnum = 1 THEN
            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
        ELSE
            v_table_ddl:=v_table_ddl||',';
        END IF;
 
        IF column_record.attnum <= column_record.max_attnum THEN
            v_table_ddl:=v_table_ddl||chr(10)||
                     '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
        END IF;
    END LOOP;
 
    v_table_ddl:=v_table_ddl||');';
    RETURN v_table_ddl;
END;
$BODY$
  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

查看当前事务锁等待、持锁信息的SQL
 
with    
t_wait as    
(    
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   
),   
t_run as   
(   
  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   
  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   
  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   
    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   
),   
t_overlap as   
(   
  select r.* from t_wait w join t_run r on   
  (   
    r.locktype is not distinct from w.locktype and   
    r.database is not distinct from w.database and   
    r.relation is not distinct from w.relation and   
    r.page is not distinct from w.page and   
    r.tuple is not distinct from w.tuple and   
    r.virtualxid is not distinct from w.virtualxid and   
    r.transactionid is not distinct from w.transactionid and   
    r.classid is not distinct from w.classid and   
    r.objid is not distinct from w.objid and   
    r.objsubid is not distinct from w.objsubid and   
    r.pid <> w.pid   
  )    
),    
t_unionall as    
(    
  select r.* from t_overlap r    
  union all    
  select w.* from t_wait w    
)    
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   
string_agg(   
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||   
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||   
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||    
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||    
'SQL (Current SQL in Transaction): '||chr(10)||  
case when query is null then 'NULL' else query::text end,    
chr(10)||'--------'||chr(10)    
order by    
  (  case mode    
    when 'INVALID' then 0   
    when 'AccessShareLock' then 1   
    when 'RowShareLock' then 2   
    when 'RowExclusiveLock' then 3   
    when 'ShareUpdateExclusiveLock' then 4   
    when 'ShareLock' then 5   
    when 'ShareRowExclusiveLock' then 6   
    when 'ExclusiveLock' then 7   
    when 'AccessExclusiveLock' then 8   
    else 0   
  end  ) desc,   
  (case when granted then 0 else 1 end)  
) as lock_conflict  
from t_unionall   
group by   
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;