From: Aldor <an@mediaroot.de>
To: Ilya A. Kovalenko <shadow@oganer.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: VACUUM FULL vs dump & restore
Date: Sat, 24 Sep 2005 12:28:45 +0100
Message-ID: <4335386D.4070105@mediaroot.de> (raw)
In-Reply-To: <1981474048079.20050924125612@oganer.net>
References: <1981474048079.20050924125612@oganer.net>
Another way how to do it with having access to the data in the same time
is to create a new table, named a little bit differently and do an:
insert into [table]2
select *
from [table];
Then switch to the second table.
Then you have to do on the first table the TRUNCATE and DROP.
For getting out which table is the actual one you can create a table
which holds the originate table name and the actual table name. When
using plpgsql you can check the table name before building the queries
and then build them with EXECUTE.
Be aware that you cannot do:
SELECT col1, col2
FROM gettablename('[table]');
Also be aware to switch back when you do the process again, so you dump
the data from the [table]2 to [table].
For my experience this way was faster then dump-truncate-restore on the
table.
Regards,
Aldor
Ilya A. Kovalenko wrote:
> Greetings,
>
> What advantages I lose, when using dump-truncate-restore (table
> or whole DB) instead of performing VACUUM FULL ?
> In both cases I have no access to data, but first is much faster
> (by subjective estimate).
>
> Thank you,
>
> Ilya A. Kovalenko (mailto:shadow@oganer.net)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: an@mediaroot.de, shadow@oganer.net
Subject: Re: VACUUM FULL vs dump & restore
In-Reply-To: <4335386D.4070105@mediaroot.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox