postgresql - script - listar usuarios postgres
Truncar todas las tablas en una base de datos de Postgres (8)
¿Podría usar SQL dinámico para ejecutar cada instrucción por turno? Probablemente tendrías que escribir un script PL / pgSQL para hacer esto.
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (sección 38.5.4. Ejecución de comandos dinámicos)
Necesito eliminar regularmente todos los datos de mi base de datos PostgreSQL antes de una reconstrucción. ¿Cómo haría esto directamente en SQL?
Por el momento, he logrado encontrar una declaración SQL que devuelve todos los comandos que necesito para ejecutar:
SELECT ''TRUNCATE TABLE '' || tablename || '';'' FROM pg_tables WHERE tableowner=''MYUSER'';
Pero no puedo ver una forma de ejecutarlos programáticamente una vez que los tengo.
En este caso, probablemente sea mejor tener una base de datos vacía que usar como plantilla y cuando necesite actualizar, elimine la base de datos existente y cree una nueva de la plantilla.
FrustratedWithFormsDesigner es correcto, PL / pgSQL puede hacer esto. Aquí está el guión:
CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
statements CURSOR FOR
SELECT tablename FROM pg_tables
WHERE tableowner = username AND schemaname = ''public'';
BEGIN
FOR stmt IN statements LOOP
EXECUTE ''TRUNCATE TABLE '' || quote_ident(stmt.tablename) || '' CASCADE;'';
END LOOP;
END;
$$ LANGUAGE plpgsql;
Esto crea una función almacenada (debe hacer esto solo una vez) que luego puede usar así:
SELECT truncate_tables(''MYUSER'');
Limpieza de la versión AUTO_INCREMENT :
CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
statements CURSOR FOR
SELECT tablename FROM pg_tables
WHERE tableowner = username AND schemaname = ''public'';
BEGIN
FOR stmt IN statements LOOP
EXECUTE ''TRUNCATE TABLE '' || quote_ident(stmt.tablename) || '' CASCADE;'';
IF EXISTS (
SELECT column_name
FROM information_schema.columns
WHERE table_name=quote_ident(stmt.tablename) and column_name=''id''
) THEN
EXECUTE ''ALTER SEQUENCE '' || quote_ident(stmt.tablename) || ''_id_seq RESTART WITH 1'';
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
Los cursores explícitos rara vez se necesitan en plpgsql. Simplemente use el cursor implícito más simple y más rápido de un bucle FOR :
Nota: Dado que los nombres de tabla no son únicos por base de datos, tiene que esquematizar los nombres de las tablas para asegurarse. Además, limito la función al esquema predeterminado ''público''. Adáptese a sus necesidades, pero asegúrese de excluir los esquemas del sistema pg_* y information_schema .
Tenga mucho cuidado con estas funciones. Destruyen tu base de datos. Agregué un dispositivo de seguridad para niños. Comenta la línea de RAISE NOTICE RAIZ y descomenta el EXECUTE para preparar la bomba ...
CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
RETURNS void AS
$func$
DECLARE
_tbl text;
_sch text;
BEGIN
FOR _sch, _tbl IN
SELECT schemaname, tablename
FROM pg_tables
WHERE tableowner = _username
AND schemaname = ''public''
LOOP
RAISE NOTICE ''%'',
-- EXECUTE -- dangerous, test before you execute!
format(''TRUNCATE TABLE %I.%I CASCADE'', _sch, _tbl);
END LOOP;
END
$func$ LANGUAGE plpgsql;
format() requiere Postgres 9.1 o posterior. En versiones anteriores, concatena la cadena de consulta de la siguiente manera:
''TRUNCATE TABLE '' || quote_ident(_sch) || ''.'' || quote_ident(_tbl) || '' CASCADE'';
Un solo comando, sin bucle
Como podemos TRUNCATE varias tablas a la vez, no necesitamos ningún cursor o bucle en absoluto:
Agregue todos los nombres de tablas y ejecute una sola declaración. Más simple, más rápido:
CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
RETURNS void AS
$func$
BEGIN
RAISE NOTICE ''%'',
-- EXECUTE -- dangerous, test before you execute!
(SELECT ''TRUNCATE TABLE ''
|| string_agg(format(''%I.%I'', schemaname, tablename), '', '')
|| '' CASCADE''
FROM pg_tables
WHERE tableowner = _username
AND schemaname = ''public''
);
END
$func$ LANGUAGE plpgsql;
Llamada:
SELECT truncate_tables(''postgres'');
Consulta refinada
Ni siquiera necesitas una función. En Postgres 9.0+ puede ejecutar comandos dinámicos en una declaración DO . Y en Postgres 9.5+ la sintaxis puede ser incluso más simple:
DO
$func$
BEGIN
RAISE NOTICE ''%'',
-- EXECUTE
(SELECT ''TRUNCATE TABLE '' || string_agg(oid::regclass::text, '', '') || '' CASCADE''
FROM pg_class
WHERE relkind = ''r'' -- only tables
AND relnamespace = ''public''::regnamespace;
);
END
$func$;
Acerca de la diferencia entre pg_class , pg_tables y information_schema.tables :
Acerca de los nombres de las tablas citadas y de los regclass :
Para uso repetido
Podría ser más simple y (mucho) más rápido crear una base de datos "plantilla" (llamémosla my_template ) con su estructura my_template y todas las tablas vacías. Luego pase por un ciclo DROP / CREATE DATABASE :
DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;
Esto es extremadamente rápido , porque Postgres copia toda la estructura en el nivel de archivo. No hay problemas de concurrencia u otros gastos generales que lo ralenticen.
Para eliminar los datos y preservar las estructuras de tabla en pgAdmin , puede hacer:
- Haga clic con el botón derecho en la base de datos -> copia de seguridad, seleccione "Sólo esquema"
- Suelta la base de datos
- Crea una nueva base de datos y nómbrala como la anterior
- Haga clic con el botón derecho en la nueva base de datos -> restaurar -> seleccione la copia de seguridad, seleccione "Solo esquema"
Puedes hacer esto con bash también:
#!/bin/bash
PGPASSWORD='''' psql -h 127.0.0.1 -Upostgres sng --tuples-only --command "SELECT ''TRUNCATE TABLE '' || schemaname || ''.'' || tablename || '';'' FROM pg_tables WHERE schemaname in (''cms_test'', ''ids_test'', ''logs_test'', ''sps_test'');" |
tr "//n" " " |
xargs -I{} psql -h 127.0.0.1 -Upostgres sng --command "{}"
Tendrá que ajustar los nombres de esquema, las contraseñas y los nombres de usuario para que coincidan con sus esquemas.
Si tengo que hacer esto, simplemente crearé un esquema sql de db actual, luego soltaré y crearé db, luego cargaré db con schema sql.
Debajo están los pasos involucrados:
1) Crear el volcado de esquema de la base de datos ( --schema-only )
pg_dump mydb -s > schema.sql
2) Bajar la base de datos
drop database mydb;
3) Crear una base de datos
create database mydb;
4) Esquema de importación
psql mydb < schema.sql