Database Reference
In-Depth Information
How it works...
PostgreSQL uses internal transaction identifiers that are four bytes long, so we only have
2^31 transaction ids (about two billion). PostgreSQL wraps around and starts again from the
beginning when that wraps around, allocating new identifiers in a circular manner. The reason
we do this is that moving to an eight-byte identifier has various other negative effects and
costs that we would rather not pay, so we keep the four-byte transaction identifier, which also
has costs.
PostgreSQL is designed to continue using ids even after the system wraps. Properly
maintained, everything will keep working forever and you'll never notice what happens
on the inside. To allow that to happen we need to run regular VACUUMs.
There's more...
If you received the aforementioned ERROR, and the database is no longer accepting
commands you're probably wondering what the phrase use a standalone backend to
vacuum that database means.
A "standalone backend" means running the database server from just a single executable
process. This is the equivalent of *nix run-level 1, also known as single user mode. We restrict
access to the database to just a single user.
The command to do this is the following, noting that the --single must be the very first
command on the command line:
$ postgres --single -D /full/path/to/datadir postgres
which then returns the following command line prompt:
PostgreSQL stand-alone backend 9.0
backend>
and you can then run the VACUUM from there, as follows:
PostgreSQL stand-alone backend 9.0
backend> VACUUM;
backend>
when you're finished, type <CTRL>-D (or whatever you have set EOF to be for your terminal
window) once or twice if you also used the -j option.
You should also check for old prepared transactions as described in Removing old prepared
transactions .
 
Search WWH ::




Custom Search