Thread: Urgent Help Required

On 10/08/2013 03:55 AM, shailesh singh wrote:
> I had got this message while running vacuum full from backend . Now My
> database is not starting , Help pls.
>
> 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

>
> Now what?

First some information.

1) What version of Postgres are you using?

2) Does database debug in fact exist or not?
    In other words does it show up with \l in psql?

Also it not necessary to use FULL with the VACUUM.

>
> Thanks in advance.
>
> Shailesh Singh


--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver-3 wrote
> On 10/08/2013 03:55 AM, shailesh singh wrote:
>> I had got this message while running vacuum full from backend . Now My
>> database is not starting , Help pls.
>>
>> 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
>
>>
>> Now what?
>
> First some information.
>
> 1) What version of Postgres are you using?
>
> 2) Does database debug in fact exist or not?
>     In other words does it show up with \l in psql?
>
> Also it not necessary to use FULL with the VACUUM.
>
>>
>> Thanks in advance.
>>
>> Shailesh Singh

From the documentation:

http://www.postgresql.org/docs/9.2/interactive/sql-vacuum.html

"With no parameter, VACUUM processes every table in the current database
that the current user has permission to vacuum. With a parameter, VACUUM
processes only that table."

Since you must be connected to a database to issue VACUUM to specify which
database would be redundant.  Your specification of "debug" in the above
command was not taken to be a database but rather a relation/table.

The basic steps are:

1) connect to the "debug" database.
2) issue the command  "VACUUM" with no parameters

The reason for the error is that transaction id wraparound is on the verge
of occurring.  After having solved the immediate problem by manually
vacuuming you should try and describe to us why it is the auto-vacuum
service has failed to vacuum the debug database in time to prevent the
warning.

The immediate solution will work on any version but the cause analysis will
require knowing the PostgreSQL versions, its configuration, and basic usage
characteristics. Regardless, though, always provide version information when
asking for help.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Urgent-Help-Required-tp5773675p5773692.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 10/08/2013 08:03 AM, shailesh singh wrote:
> Dear all,
>   First of all i wish to share actual error meassge,
>
> Below are the queries i had executed on the terminal on my server
>
>
> -bash-3.2$ touch fix.sql
> -bash-3.2$ echo "VACUUM FULL;" > fix.sql
> -bash-3.2$ postgres -D /var/lib/pgsql/data patnadbold < fix.sql
> WARNING: database "patnadbold" must be vacuumed within 1000000 transactions
> HINT: To avoid a database shutdown, execute a full-database VACUUM in
> "patnadbold".
> WARNING: database "patnadbold" must be vacuumed within 1000000 transactions
> HINT: To avoid a database shutdown, execute a full-database VACUUM in
> "patnadbold".
>
> PostgreSQL stand-alone backend 8.1.11

>
> After this i am able to stop /start my db server but i am not able to
> connect to my databases (it tells to run vacuum full first on patnadbold
> databases)
>
>
> 1)I am using postgres 8.4 version.

This seems to be at odds with "PostgreSQL stand-alone backend 8.1.11".
Are you sure you are working on the correct database cluster?

> 2) I had two databases on this server i) patnadbold  ii) patnaonlinedb
>
> For me patnadbold is of no use if at this moment i lost this database
> that also fine to me.
> I wanted to connect patnaonlinedb any how and wanted to perform backup
> of this , Solution please.
>



--
Adrian Klaver
adrian.klaver@gmail.com

On Tue, Oct 08, 2013 at 08:06:50AM -0700, bricklen wrote:
> On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh <shaileshjuly@gmail.com>wrote:
>
> > HINT: To avoid a database shutdown, execute a full-database VACUUM in
> > "patnadbold".
> > ERROR: could not access status of transaction 33011
> > DETAIL: could not open file "pg_clog/0000": No such file or directory
> > exit
> >
> >
> > After this i am able to stop /start my db server but i am not able to
> > connect to my databases (it tells to run vacuum full first on patnadbold
> > databases)
> >
>
> The message does *not* say to run "VACUUM FULL", it says to run a
> "full-database VACUUM". Different things.
> Connect to "patnadbold" and issue "VACUUM;" (without double-quotes) as the
> others have suggested.

In case it isn't clear to the original poster, VACUUM FULL will take a
lot longer than a simple VACUUM and probably not really help much.

Just plain VACUUM.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

> postgres -D /var/lib/pgsql/data patnadbold < fix.sql

What the heck is the point of feeding "VACUUM FULL;" into the standard input
of the postgres command?  "postgres" simply starts the server, it does not
execute arbitrary SQL.  Once the database is started you want to use "psql"
- either interactively or in a similar manner to the above - to connect to
the <patnadbold> database and run "VACUUM".

Note that:  "su postgres" -> "psql" will likely connect you to the
"postgres" database as that is the normal default.  You will need to tell
psql which database to connect to.


> PostgreSQL stand-alone backend 8.1.11

8.1 is unsupported at this point; plus you say 8.4 below which this
contradicts.  Your configuration is very unclear.


> ERROR: could not access status of transaction 33011
> DETAIL: could not open file "pg_clog/0000": No such file or directory
> exit

This is bothersome as well...


> After this i am able to stop /start my db server but i am not able to
> connect to my databases (it tells to run vacuum full first on patnadbold
> databases)
>
>
> 1)I am using postgres 8.4 version.
> 2) I had two databases on this server i) patnadbold  ii) patnaonlinedb
>
> For me patnadbold is of no use if at this moment i lost this database that
> also fine to me.
> I wanted to connect patnaonlinedb any how and wanted to perform backup of
> this , Solution please.

The only thing not mentioned is starting postgres is single-user mode; this
may be necessary though I am not sure and others can be of more help.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Urgent-Help-Required-tp5773675p5773716.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

*Don't* VACUUM FULL.  Just VACUUM.  It's not the same thing.

...Robert

On 10/08/2013 09:03 AM, David Johnston wrote:
>
>> postgres -D /var/lib/pgsql/data patnadbold < fix.sql
>
> What the heck is the point of feeding "VACUUM FULL;" into the standard input
> of the postgres command?  "postgres" simply starts the server, it does not
> execute arbitrary SQL.  Once the database is started you want to use "psql"
> - either interactively or in a similar manner to the above - to connect to
> the <patnadbold> database and run "VACUUM".

I was under that impression also. So I went back in the archives, and in
8.1 this was possible.

http://www.postgresql.org/docs/8.1/interactive/app-postgres.html


>
>
>> PostgreSQL stand-alone backend 8.1.11
>
> 8.1 is unsupported at this point; plus you say 8.4 below which this
> contradicts.  Your configuration is very unclear.

This part is still unclear to me. I would like some more information on
where the 8.4 cluster comes into play, if at all. My hunch is the OP is
working with two different Postgres instances un-intently .



> David J.



--
Adrian Klaver
adrian.klaver@gmail.com

On 10/8/13 5:55 AM, shailesh singh wrote:
 > 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

Unless I'm mistaken, that missing CLOG file is a bad sign...?
--
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

On 10/8/2013 8:35 AM, Chris Travers wrote:
> First, while vacuum is usually preferred to vacuum full, in this case,
> I usually find that vacuum full clears up enough cruft to be worth it
> (not always, but especially if you are also having performance issues).


IIRC, vacuum full was pretty broken in 8.1, which the output the
original postered showed indicated they were running.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Chris Travers <chris.travers@gmail.com> wrote:
> John R Pierce <pierce@hogranch.com> wrote:

> I often find in those cases it is a choice between vacuum full
> and dumpall/initdb/reload/analyze.....

Way back in the 8.1 days I often found CLUSTER to be my best option
-- as long as I had room enough for a second copy (without the
bloat) while it was running.  If I didn't have that much room the
dump/initdb/restore option was almost always faster than VACUUM
FULL.  Personally, I would follow the restore with VACUUM FREEZE
ANALYZE rather than just ANALYZE, so that the entire database
didn't come due for wraparond prevention vacuums at an inopportune
time.

The main thing here seem to be to upgrade to a supported version
(preferably 9.2 or 9.3).

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company