Обсуждение: how to vacuum from standalone backend

Help!

This is postgresql 8.1.3 also the database debug can easily be recreated if I have to drop it
but I can't seem to do that either.

 vacuumdb debug
vacuumdb: could not connect to database debug: FATAL:  database is not accepting commands to avoid wraparound data loss in database "debug"
HINT:  Stop the postmaster and use a standalone backend to vacuum database "debug".

I am getting the above message. I am not quite sure how to proceed.

I did the following:
postgres -D /usr/local/pgsql/data debug

WARNING:  database "debug" must be vacuumed within 1000000 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 1000000 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".

PostgreSQL stand-alone backend 8.1.3
backend> vacuum full debug;

backend> vacuum full debug;
WARNING:  database "debug" must be vacuumed within 999999 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
ERROR:  relation "debug" does not exist
backend> vacuum full;
WARNING:  database "debug" must be vacuumed within 999998 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999997 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999996 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999995 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999994 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999993 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999992 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999991 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999990 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999989 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999988 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999987 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999986 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999985 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999984 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999983 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
ERROR:  could not access status of transaction 449971277
DETAIL:  could not open file "pg_clog/01AD": No such file or directory


Now what?

Thanks in advance.

On 12/14/2010 01:04 PM, Steve Clark wrote:

Help!

This is postgresql 8.1.3 also the database debug can easily be recreated if I have to drop it
but I can't seem to do that either.

 vacuumdb debug
vacuumdb: could not connect to database debug: FATAL:  database is not accepting commands to avoid wraparound data loss in database "debug"
HINT:  Stop the postmaster and use a standalone backend to vacuum database "debug".

I am getting the above message. I am not quite sure how to proceed.

I did the following:
postgres -D /usr/local/pgsql/data debug

WARNING:  database "debug" must be vacuumed within 1000000 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 1000000 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".

PostgreSQL stand-alone backend 8.1.3
backend> vacuum full debug;

backend> vacuum full debug;
WARNING:  database "debug" must be vacuumed within 999999 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
ERROR:  relation "debug" does not exist
backend> vacuum full;
WARNING:  database "debug" must be vacuumed within 999998 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999997 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999996 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999995 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999994 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999993 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999992 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999991 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999990 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999989 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999988 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999987 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999986 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999985 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999984 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
WARNING:  database "debug" must be vacuumed within 999983 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "debug".
ERROR:  could not access status of transaction 449971277
DETAIL:  could not open file "pg_clog/01AD": No such file or directory


Now what?

Thanks in advance.

Please disregard  - figured out how to drop the database.
drop database debug - not dropdb debug - duh!

--
Stephen Clark
NetWolves
Sr. Software Engineer III
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.clark@netwolves.com
http://www.netwolves.com

On Tue, Dec 14, 2010 at 11:04 AM, Steve Clark <sclark@netwolves.com> wrote:
> Help!
>
> This is postgresql 8.1.3 also the database debug can easily be recreated if

Do you have a reason for running a version of postgresql that is
missing over 2 years of security and bug fixes?  The 8.1 branch is up
to 8.1.22 now, I'd really advise updating to it when you can.   If you
can upgrading to 8.4 or 9.0 would be useful, as they're much faster,
and support for 8.1 is gonna go away fairly soon.

>  vacuumdb debug
> vacuumdb: could not connect to database debug: FATAL:  database is not
> accepting commands to avoid wraparound data loss in database "debug"
> HINT:  Stop the postmaster and use a standalone backend to vacuum database
> "debug".
>
> I am getting the above message. I am not quite sure how to proceed.

First, turn autovac back on, or setup a cron job to run at night and
run vacuum for you to prevent this from happening again.

> I did the following:
> postgres -D /usr/local/pgsql/data debug
>
> backend> vacuum full;
> WARNING:  database "debug" must be vacuumed within 999998 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999997 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999996 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999995 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999994 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999993 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999992 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999991 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999990 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999989 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999988 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999987 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999986 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999985 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999984 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> WARNING:  database "debug" must be vacuumed within 999983 transactions
> HINT:  To avoid a database shutdown, execute a full-database VACUUM in
> "debug".
> ERROR:  could not access status of transaction 449971277
> DETAIL:  could not open file "pg_clog/01AD": No such file or directory

So maybe you've run into one of the many bugs in 8.1.3?

> Now what?

Update your pg version first.  (and I know you've dropped and
recreated the db and all that.  but still, you need an update).

When I try to connect to a database, I get the following error :

psql teleflowdb8

psql: FATAL:  database is not accepting commands to avoid wraparound data
loss in database "teleflowdb8"
HINT:  Stop the postmaster and use a standalone backend to vacuum database
"teleflowdb8".

Then I try to go to standalone mode:

$./postgresql-8.2 stop
$su postgres
$cd /usr/lib/postgresql/8.2/bin

$./postgres --single -D /var/lib/postgresql/8.2/main/ teleflowdb8

I got the following error :
--single cannot access the server configuration file
"/var/lib/postgresql/8.2/main/postgresql.conf": No such file or directory

When to check the postgresql.conf file is located at
/etc/postgresql/8.2/main

$ls

environment
log
pgdata
pg_hba.conf
pg_ident.conf
postgresql.conf
start.conf


So I did :

$./postgres --single -D /etc/postgresql/8.2/main teleflowdb8

But error as follows.
2012-02-24 19:04:28 SGT FATAL:  "/etc/postgresql/8.2/main" is not a valid
data directory
2012-02-24 19:04:28 SGT DETAIL:  File "/etc/postgresql/8.2/main/PG_VERSION"
is missing.

I do not have the /var/lib/pgsql/data directory.

Can anyone help on this issue. TQ















--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-vacuum-from-standalone-backend-tp3305010p5512163.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Fri, Feb 24, 2012 at 2:30 AM, seha <blackroose89@yahoo.com> wrote:
>
> When I try to connect to a database, I get the following error :
>
> psql teleflowdb8
>
> psql: FATAL:  database is not accepting commands to avoid wraparound data
> loss in database "teleflowdb8"
> HINT:  Stop the postmaster and use a standalone backend to vacuum database
> "teleflowdb8".
>
> Then I try to go to standalone mode:
>
> $./postgresql-8.2 stop
> $su postgres
> $cd /usr/lib/postgresql/8.2/bin
>
> $./postgres --single -D /var/lib/postgresql/8.2/main/ teleflowdb8
>
> I got the following error :
> --single cannot access the server configuration file
> "/var/lib/postgresql/8.2/main/postgresql.conf": No such file or directory
>
> When to check the postgresql.conf file is located at
> /etc/postgresql/8.2/main
>
> $ls
>
> environment
> log
> pgdata
> pg_hba.conf
> pg_ident.conf
> postgresql.conf
> start.conf
>
>
> So I did :
>
> $./postgres --single -D /etc/postgresql/8.2/main teleflowdb8

That's the wrong direction.  Try just copying
/etc/postgres/8.2/main/postgresql.conf to
/var/lib/postgresql/8.2/main/.  Worked for me.

On Friday, February 24, 2012 12:31:34 pm Scott Marlowe wrote:
> On Fri, Feb 24, 2012 at 2:30 AM, seha <blackroose89@yahoo.com> wrote:
> > When I try to connect to a database, I get the following error :

>
> That's the wrong direction.  Try just copying
> /etc/postgres/8.2/main/postgresql.conf to
> /var/lib/postgresql/8.2/main/.  Worked for me.

For the record:
http://www.postgresql.org/docs/9.0/interactive/runtime-config-file-locations.html
"
In a default installation, none of the above parameters are set explicitly.
Instead, the data directory is specified by the -D command-line option or the
PGDATA environment variable, and the configuration files are all found within the
data directory.

If you wish to keep the configuration files elsewhere than the data directory, the
postgres -D command-line option or PGDATA environment variable must point to the
directory containing the configuration files, and the data_directory parameter
must be set in postgresql.conf (or on the command line) to show where the data
directory is actually located. Notice that data_directory overrides -D and
PGDATA for the location of the data directory, but not for the location of the
configuration files.

If you wish, you can specify the configuration file names and locations
individually using the parameters config_file, hba_file and/or ident_file. config_file
can only be specified on the postgres command line, but the others can be set
within the main configuration file. If all three parameters plus data_directory
are explicitly set, then it is not necessary to specify -D or PGDATA.

When setting any of these parameters, a relative path will be interpreted with
respect to the directory in which postgres is started.
"


--
Adrian Klaver
adrian.klaver@gmail.com