1 前提条件

linux PostgreSQL语法

创建数据库用户"xxx"

--使用postgres用户执行下面的脚本
psql -h localhost -U postgres
--创建数据库demo用户"xxx"
create user xxx with password '123' nocreatedb;  
--获取用户OID,此语句只能使用postgres用户执行
select oid from pg_authid where rolname='xxx';
--OID:965873在第二节中需要用到

--创建表空间，所有者为用户"xxx"
create tablespace demo owner xxx location '/data/demo' with(effective_io_concurrency=8);
--创建索引表空间，所有者为用户"xxx"
create tablespace idxdemo owner xxx location '/data/demo' with(effective_io_concurrency=8);

--创建数据库，所有者为用户"xxx"
create database demo
	with owner = xxx						--指定用户xxx为此库的所有者
			template template0			
			encoding 'UTF8'			
			LC_COLLATE 'zh_CN.UTF-8'
			LC_CTYPE 'zh_CN.UTF-8'
			connection limit = -1;
			
--回收schema的public权限
revoke all on schema public from public;
--授予用户"xxx" schema public的所有权限
grant all on schema public to xxx;
--从demo数据库收回public的所有权限
revoke all on database demo from public;
revoke all on database postgres from public;

vacuum freeze verbose analyze;

创建架构后要回收public权限，默认是授予任意用户public权限的。回收public权限后，当其它数据库用户打开本数据库时，打开架构时会报错，也就是无法查看数据库中的任何对象。

drop schema if exists history;
create schema history;
revoke all on schema history from public;
grant all on schema history to xxx;

以后在数据库“demo”创建所有对象都使用“xxx”用户创建。

2 清空数据库

965873就是创建用户时系统分配的OID

--删除索引
do $$
	declare 
		v_owner oid;
		v_rec record;
		v_rec1 record;
		v_argtypes text;
	begin
		v_owner := 965873;
		for v_rec in with cte as(
							select  
								(select nspname from pg_namespace where oid=t1.relnamespace),
								relname
							from pg_class as t1		
							inner join pg_index as t2 on t2.indexrelid=t1.oid and indisprimary=false
							where relowner=965873 and relkind='i' order by t1.oid desc
					)select * from cte where nspname!='pg_toast' loop		
				execute format('drop index if exists %s.%s;',v_rec.nspname,v_rec.relname);	
				--raise notice  '%', format('drop index if exists %s.%s;',v_rec.nspname,v_rec.relname);
		end loop;
	end;
$$;

--删除函数
do $$
	declare 
		v_owner oid;
		v_rec record;
		v_rec1 record;
		v_argtypes text;
	begin
		v_owner := 965873;
		for v_rec in select oid,proname,pronargs,proargtypes,prorettype from pg_proc as t1 where proowner=v_owner loop
			v_argtypes := '';
			if( 2279 = v_rec.prorettype ) then --触发器函数
				for v_rec1 in select t2.relname, t1.tgname 
					from pg_trigger as t1
					inner join pg_class as t2 on t2.oid = t1.tgrelid
					where tgfoid=v_rec.oid loop
						execute format('drop trigger if exists %s on %s;',v_rec1.tgname, v_rec1.relname);
						execute format('drop function if exists %s();',v_rec.proname);
						--raise notice  '%',  format('drop trigger if exists %s on %s;',v_rec1.tgname, v_rec1.relname);
						--raise notice  '%',  format('drop function if exists %s();',v_rec.proname);
				end loop;
			else
				if( v_rec.pronargs > 0 ) then --有参数的函数
					for i in 1..array_length(v_rec.proargtypes,1) loop
						v_argtypes := format('%s%s,',v_argtypes,(select typname from pg_type where oid=v_rec.proargtypes[i-1]));			
					end loop;
					v_argtypes := regexp_replace(v_argtypes,',$','');
					execute format('drop function if exists %s(%s);',v_rec.proname,v_argtypes);		
					--raise notice  '%',  format('drop function if exists %s(%s);%s',v_rec.proname,v_argtypes,v_rec.proargtypes);		
				else
					execute format('drop function if exists %s();',v_rec.proname);	
					--raise notice  '%',  format('drop function if exists %s();',v_rec.proname);
				end if;
			end if;
		end loop;
	end;
$$;

--删除视图和物化视图
do $$
	declare 
		v_owner oid;
		v_rec record;
	begin
		v_owner := 965873;
		--视图
		for v_rec in select (select nspname from pg_namespace where oid=t1.relnamespace),t1.relname from pg_class as t1
							where relowner=v_owner and relkind='v'   order by oid desc loop		
				execute format('drop view if exists %s.%s;',v_rec.nspname,v_rec.relname);	
				--raise notice  '%', format('drop view if exists %s.%s;',v_rec.nspname,v_rec.relname);	
		end loop;

		-- --物化视图 
		for v_rec in select (select nspname from pg_namespace where oid=t1.relnamespace),t1.relname from pg_class as t1
						where relowner=v_owner and relkind='m'  loop		
				execute format('drop materialized view if exists %s.%s;',v_rec.nspname,v_rec.relname);	
				--raise notice  '%', format('drop materialized view if exists %s.%s;',v_rec.nspname,v_rec.relname);	
		end loop;
	end;
$$;

--删除普通表
do $$
	declare 
		v_owner oid;
		v_rec record;
	begin
		v_owner := 965873;

		for v_rec in select (select nspname from pg_namespace where oid=t1.relnamespace),t1.relname from pg_class as t1
								where relowner=v_owner and relkind='r'  order by oid desc loop		
				execute format('drop table if exists %s.%s;',v_rec.nspname,v_rec.relname);	
				raise notice  '%', format('drop table if exists %s.%s;',v_rec.nspname,v_rec.relname);	
		end loop;
	end;
$$;


--删除组合类型
do $$
	declare 
		v_owner oid;
		v_rec record;
		v_rec1 record;
		v_argtypes text;
	begin
		v_owner := 965873;
		for v_rec in select (select nspname from pg_namespace where oid=t2.typnamespace),t2.typname from pg_class as t1
								inner join pg_type as t2 on t2.oid=t1.reltype
								where relowner=v_owner and relkind='c'   order by t1.oid desc loop		
				execute format('drop type if exists %s.%s;',v_rec.nspname,v_rec.typname);	
				--raise notice  '%', format('drop type if exists %s.%s;',v_rec.nspname,v_rec.typname);	
		end loop;
	end;
$$;

--删除枚举
do $$
	declare 
		v_owner oid;
		v_rec record;
		v_rec1 record;
		v_argtypes text;
	begin
		v_owner := 965873;
		for v_rec in select (select nspname from pg_namespace where oid=t1.typnamespace),t1.typname
							from pg_type as t1 where typowner=v_owner and typtype='e'  order by oid desc loop		
				execute format('drop type if exists %s.%s;',v_rec.nspname,v_rec.typname);	
				--raise notice  '%', format('drop type if exists %s.%s;',v_rec.nspname,v_rec.typname);	
		end loop;
	end;
$$;

--删除模式
do $$
	declare 
		v_owner oid;
		v_rec record;
		v_rec1 record;
		v_argtypes text;
	begin
		v_owner := 965873;
		for v_rec in select nspname from pg_namespace where nspowner=v_owner  order by oid desc loop		
				execute format('drop schema if exists %s;',v_rec.nspname);	
				--raise notice  '%', format('drop schema if exists %s;',v_rec.nspname);
		end loop;
	end;
$$;

vacuum  freeze verbose  analyze;