postgresql比对数据库结构、导入、导出等脚本

-----------------------------强行删除数据库-----------------------------

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='dbname' AND pid<>pg_backend_pid();  
DROP DATABASE dbname;

-----------------------------创建数据库实例-----------------------------

create database dbname owner postgres;

-----------------------------导入、导出（需要输入密码）-----------------------------

#备份结构 
pg_dump -h 172.16.100.209  -p 9999 -U postgres -s khb_001_hazcj_080 > /tmp/khb_001_hazcj_080.backup
#备份数据 
pg_dump -h 172.16.100.209  -p 9999 -U postgres -a khb_001_hazcj_080 > /tmp/khb_001_hazcj_080_data.backup  
#恢复结构
psql -d test -h 192.168.171.129 -p 5432 -U postgres -f /tmp/khb_001_hazcj_080.backup 
#恢复数据
psql -d test -h 192.168.171.129 -p 5432 -U postgres -f /tmp/khb_001_hazcj_080_data.backup 
#说明：-h主机名 -p端口 -U用户名 -s只备份机构 -a只备份数据

-----------------------------导入、导出（无需输入密码，但是pg_hba.conf需要设置trust）-----------------------------

#备份结构
pg_dump -s "host=172.16.100.103 port=5432 user=postgres password=postgres dbname=postgres" > /tmp/test.backup
#备份数据
pg_dump -a "host=172.16.100.103 port=5432 user=postgres password=postgres dbname=postgres" > /tmp/test_data.backup
#恢复结构
psql "host=192.168.171.129 port=5432 user=postgres password=postgres dbname=test2" -f /tmp/test.backup
#恢复数据
psql "host=192.168.171.129 port=5432 user=postgres password=postgres dbname=test2" -f /tmp/test_data.backup

-----------------------------比对数据库表名、字段、字段类型、是否为空-----------------------------

SELECT R.relname,string_agg (R.fields, ';' ORDER BY R.fields ) AS filedtext 
FROM
	(
	SELECT T.relname,concat ( T.attnum, '|', T.TYPE, '|', T.NAME, '|',T.NOTNULL) AS fields 
	FROM
		(
		SELECT 
		  A.attnum,
			C.relname,
			concat_ws('',T.typname,SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' )) AS TYPE,
			A.attname AS NAME,
			A.attnotnull AS NOTNULL 
		FROM
			pg_class AS C,
			pg_attribute AS A,
			pg_type AS T
		WHERE
			A.attrelid = C.oid 
			AND A.atttypid = T.oid
			AND A.attnum > 0 
		) T INNER JOIN ( SELECT tablename FROM pg_tables WHERE schemaname = 'public' ) K ON K.tablename = T.relname 
	ORDER BY T.attnum 
	) R 
GROUP BY R.relname ORDER BY R.relname