备份和恢复

Postgres 备份数据库会将数据库中所有数据导出形成一个 xxx.sql, 恢复时直接执行sql创建数据到数据库中。

备份:

pg_dump -U USER_NAME -p PORT -h SERVER_IP -W DB_NAME | gzip > backup.gz

恢复:

gunzip -c backup.gz | psql NEW_DB

安全策略

绝对不用在shell中对postgres用户进行passwd操作, 因为UNIX 用户 "postgres" 是被锁定的,这意味这不能使用password来登陆此用户, 一旦使用 passwd postgres命令设置了密码,postgres用户就会被解除锁定

postgresql有默认的db管理员用户,名字也叫postgres,我们应该修改的是这个密码

sudo -u postgres psql postgres
# \password postgres
Enter new password:

查看产生锁的SQL 和用户

执行以下SQL

SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,
         kl.pid AS blocking_pid, ka.usename AS blocking_user,
         a.current_query AS blocked_statement
  FROM pg_catalog.pg_locks bl
       JOIN pg_catalog.pg_stat_activity a
       ON bl.pid = a.procpid
       JOIN pg_catalog.pg_locks kl
            JOIN pg_catalog.pg_stat_activity ka
            ON kl.pid = ka.procpid
       ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid
  WHERE NOT bl.granted;


select
 locktype, database, relation, page, tuple, transactionid, virtualtransaction, mode, granted,
 relname, relkind,
 datname, usename,
 case
  when length(current_query) > 40
    then substring(current_query for 20) || '...'
         || substring(current_query from (length(current_query) - 19) for 20)
  else current_query
 end as query,
 waiting, query_start, client_addr, client_port
from pg_locks
 left outer join pg_class on oid = relation
 left outer join pg_stat_activity on pid = procpid
order by query_start desc

查找执行超过一定时间的sql语句

修改postgresql.conf文件

#log_min_duration_statement = -1
改为
log_min_duration_statement = 100  ## 单位毫秒

重启数据库服务, service postgresql reload

查看log, 文件位置在 /var/lib/pgsql/data/pg_log/

修改postgres用户密码

通常不建议这么做, 因为postgres用户尽可能只允许本地登录

ALTER USER Postgres WITH PASSWORD '123';