Postgres数据库维护

时间：2022-02-05 14:47:44

1.全自动备份

需要在备份机上也安装postgres(最好同一个版本)

在postgres目录下建立密码保存文件(明码保存,所以保密很重要),如果不建立,则因为每次备份都要输入密码,不能进行自动备份

#创建.pgpass文件

touch .pgpass

#在文件中写入以下内容,可多行

#127.0.0.1:5432:dbName:dbUser:dbPassword

#127.0.0.1:5432:dbName2:dbUser2:dbPassword2

创建自动脚本,自动按月分文件夹

#!/bin/sh

. /etc/profile

. ~/.bash_profile

dirname=$(date +%Y%m)

filename=$(date +%Y%m%d%H%M%S)

mkdir -p /home/postgres/backup/$dirname/

pg_dump -h [ipAddr] -U [dbUser] [dbName]| gzip>/home/postgres/backup/$dirname/bk_$filename.gz

创建crontab自动执行即可

#去除限制导出

#--no-tablespaces 代表去除表空间限制(不导出表空间信息)

#-O 代表不导出对象/库的所有者信息

#-x 代表不导出ACL权限信息和表的权限信息

pg_dump -h 127.0.0.1 -U postgres --no-tablespaces -O -x -f sqlname.sql  dbname

2.恢复数据库

解压gz

#直接解压,不保留原gz文件

gunzip xxx.gz

#解压保留源gz文件

gunzip -c xxx.gz > xxx.sql

进去pgsql控制台

psql -U dbUser
#切换到待导入的数据库

\c dbName;

#导入sql文件

\i xxx.sql
#退出控制台
\q

3.PG查看当前进程 

#查看连接数和详情

select * from pg_stat_activity;

#查看最大连接数

show max_connections;

4.批量删除空闲进程

#使用PG账户在数据库客户端上删除

SELECT pg_terminate_backend(p.pid) FROM (

SELECT pid FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state = 'idle'

UNION ALL

SELECT pid FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state = 'idle in transaction' AND backend_xid IS NULL

) p

#直接到服务器上删除

ps -ef|grep postgres|grep idle |awk '{print $2}' | xargs kill

kill有两种方式，第一种是：
SELECT pg_cancel_backend(PID);
这种方式只能kill select查询，对update、delete 及DML不生效)

第二种是：
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作

5.系统表结构

PG_TABLES 查看所有表

查看表结构

SELECT a.attnum,

a.attname AS field,

t.typname AS type,

a.attlen AS length,

a.atttypmod AS lengthvar,

a.attnotnull AS notnull,

b.description AS comment

FROM pg_class c,

pg_attribute a

LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,

pg_type t

WHERE c.relname = 'udoc_saldiscount'

and a.attnum > 0

and a.attrelid = c.oid

and a.atttypid = t.oid

ORDER BY a.attnum;

 查看索引

SELECT

A.SCHEMANAME,

A.TABLENAME,

A.INDEXNAME,

A.TABLESPACE,

A.INDEXDEF,

B.AMNAME,

C.INDEXRELID,

C.INDNATTS,

C.INDISUNIQUE,

C.INDISPRIMARY,

C.INDISCLUSTERED,

D.DESCRIPTION

FROM

PG_AM B

LEFT JOIN PG_CLASS F ON B.OID = F.RELAM

LEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELID

LEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELID

LEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID,

PG_INDEXES A

WHERE

A.SCHEMANAME = E.SCHEMANAME AND A.TABLENAME = E.RELNAME AND A.INDEXNAME = E.INDEXRELNAME

AND E.SCHEMANAME = 'public'

XX .在线热备份（归档） -- 待研究

（一）备份
1，配置归档模式
配置归档需要编辑postgresql.conf文件，默认为与/usr/local/pgsql/data/目录下
vim /usr/local/pgsql/data/postgesql.conf
archive_mode = on
archive_command = ‘cp %p /usr/local/pgsql/backup/archived_log/%f’
注：%p要被归档的日志文件的路径，%f是要被归档的日志文件的文件名
2，启动数据库
pg_ctl –D /usr/local/pgsql/data start
3，创建数据库arch
createdb arch
4，创建表并插入记录
psql arch
arch=# create table tb(a int);
arch=# insert into tb(a) values(1);
5，创建备份
arch=# select pg_start_backup(‘baseline’);
6，备份整个data目录
tar –jcv –f /usr/local/pgsql/backup/baseline.tar.bz2 /usr/local/pgsql/data/
7，停止备份
psql arch
arch=# select pg_stop_backup();
8，插入新记录，然后切换日志，重复3次
arch=# insert into tb(a) values(2);
arch=# select pg_switch_xlog();
arch=# insert into tb(a) values(3);
arch=# select pg_switch_xlog();
arch=# insert into tb(a) values(4);
arch=# select pg_switch_xlog();
9，把/data/pg_xlog/下的WAL日志文件复制到预设的归档目录下，保证产生的WAL日志都已归档。
（二）恢复
1，停止数据库
pg_ctl –D /usr/local/pgsql/data/ stop
2，删除/data/
rm –r /usr/local/pgsql/data/
3，恢复备份
tar –jxv –f /usr/local/pgsql/backup/baseline.tar.bz2 –C /
4，清空/data/pg_xlog/目录下所有文件
rm –r /usr/local/pgsql/data/pg_xlog/
5，创建/pg_xlog/及其下面的archive_status目录
mkdir /usr/local/pgsql/data/pg_xlog/
mkdir /usr/local/pgsql/data/pg_xlog/archive_status
6，在/data/目录下创建recovery.conf
vim /usr/local/pgsql/data/recovery.conf
restore_command = ‘cp /usr/local/pgsql/backup/archived_log/%f “%p”’
7，启动数据库
pg_ctl –D /usr/local/pgsql/data/ start
一切正常的话数据库就会自动应用WAL日志进行恢复
8，查看数据库arch是否恢复
psql arch
arch=# select * from tb;
a
---
1
2
3
4
(4 rows)
至此，数据已经成功恢复！

相关文章