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

向右看齐 2022-01-26 05:59 277阅读 0赞

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

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