vacuum full for all databases
Dear Sirs
I'm about to write plpgsql function which will "vacuum full" all existing
databases. Below is an example how to get list of databases.
What should I write instead of "raise notice" ?
CREATE OR REPLACE FUNCTION vacuum_all() RETURNS integer
AS '
DECLARE
query text;
list RECORD;
BEGIN
FOR list IN SELECT datname FROM pg_catalog.pg_database WHERE NOT
datistemplate LOOP
raise notice ''datname = %'',list.datname;
END LOOP;
RETURN 0;
END;
'
LANGUAGE plpgsql;
Am Donnerstag, 2. September 2004 09:32 schrieb Ilia Chipitsine:
I'm about to write plpgsql function which will "vacuum full" all existing
databases. Below is an example how to get list of databases.What should I write instead of "raise notice" ?
You normally cannot access different databases from one database session. But
you could use dblink from contrib to overcome this restriction.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
Ilia Chipitsine wrote:
Dear Sirs
I'm about to write plpgsql function which will "vacuum full" all existing
databases. Below is an example how to get list of databases.What should I write instead of "raise notice" ?
raise notice ''datname = %'',list.datname;
Something like:
EXECUTE ''VACUUM FULL '' || list.datname;
--
Richard Huxton
Archonet Ltd
Ilia Chipitsine wrote:
Dear Sirs
I'm about to write plpgsql function which will "vacuum full" all existing
databases. Below is an example how to get list of databases.What should I write instead of "raise notice" ?
raise notice ''datname = %'',list.datname;
Something like:
EXECUTE ''VACUUM FULL '' || list.datname;
"vacuum full" accepts relation name, not database name
$ psql -U pgsql template1
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=# vacuum full site_b170;
ERROR: relation "site_b170" does not exist
template1=#
Show quoted text
--
Richard Huxton
Archonet Ltd
On Thu, 2 Sep 2004, Richard Huxton wrote:
Ilia Chipitsine wrote:
Dear Sirs
I'm about to write plpgsql function which will "vacuum full" all existing
databases. Below is an example how to get list of databases.What should I write instead of "raise notice" ?
raise notice ''datname = %'',list.datname;
Something like:
EXECUTE ''VACUUM FULL '' || list.datname;
vacuumdb --all
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83