470,590 Members | 2,521 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,590 developers. It's quick & easy.

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;
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
4 7001
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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
> 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=#

--
Richard Huxton
Archonet Ltd


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4
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: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by lnd | last post: by
6 posts views Thread by Alex | last post: by
reply views Thread by Jim Seymour | last post: by
reply views Thread by Rajesh Kumar Mallah | last post: by
15 posts views Thread by Ed L. | last post: by
6 posts views Thread by spied | last post: by
9 posts views Thread by Aleksey Serba | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.