javascript - 清除所有内容的 PostgreSQL 数据库

Question

我想从数据库中删除所有内容而不删除并重新创建数据库。

我发现删除所有模式、重新创建“public”并删除所有临时表是一个好的开始。与“原始”数据库相比，我是否删除了所有内容或遗漏了什么？

const systemSchemas = ['pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema']

return Database.connect(connectionString, db => {

  // DROP schemas except systemSchemas
  const schemas = await db.queryRows(`SELECT nspname AS name FROM pg_catalog.pg_namespace ORDER BY nspname`).map(row => row.name).filter(name => {
    return systemSchemas.includes(name) === false
  })
  await Promise.resolve(schemas).each(name => db.query(`DROP SCHEMA "${name}" CASCADE`))

  // DROP temporary tables
  const sql = `SELECT c.relname AS name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relpersistence = 't'`
  return db.queryRows(sql).each(row => db.query(`DROP TABLE IF EXISTS "${row.name}"`))

  // CREATE public schema
  return db.query(`CREATE SCHEMA public`)
  .then(() => db.query(`GRANT ALL ON SCHEMA public TO postgres`))
  .then(() => db.query(`GRANT ALL ON SCHEMA public TO public`))
})

Answer 1

您不能只排除 pg_temp_1 和 pg_toast_temp_1，因为可能会有更多的临时模式。似乎删除所有非系统架构(包括临时架构)就足够了，因此您应该只排除 pg_catalog、information_schema 和 pg_toast 而不是尝试删除临时表。

请注意，您可以在单个 DO Postgres 命令中执行此操作:

do $$
declare
    schema_name text;
begin
    for schema_name in
        select nspname
        from pg_catalog.pg_namespace 
        where nspname not in ('pg_catalog', 'information_schema', 'pg_toast')
    loop
        execute format('drop schema %s cascade', schema_name);
    end loop;
    create schema public;
    grant all on schema public to postgres;
    grant all on schema public to public;
    comment on schema public is 'standard public schema';
end;
$$;