Thread: ERROR: Could not access status of transaction ####

On 10/19/07, Ralph Smith <smithrn@u.washington.edu> wrote:
> I'm using 7.4 in preparation for an overdue upgrade.
>
> Yesterday I posted 2 questions that were ignored, so I'll try a third now.
>
> When doing a query I  get same error as below, which are the results of
> vacuumdb.
>
> vacuumdb: vacuuming of database "airburst" failed: ERROR:  could not access
> status of transaction 7564911
> DETAIL:  could not open file
> "/var/lib/postgresql/7.4/main/pg_clog/0007": No such file
> or directory
>
> I'm only querying from 1 table.
> I could really use some help.
>
> ----
>
> Regarding yesterday's Qs, how  best to un-import from pg_dumpall's results?
> I'd dump-all'd into a text file and imported it via a psql -f filename.
> It had encoding errors AND did NOT make the database I needed.  Everything
> went into db postgres.

yeah, you should be able to just drop and recreate postgres.  As the
postgres superuser, something like:

dropdb postgres
createdb -T template1 postgres
OR if it fails not finding the postgres db, then
psql template1 -U postgres
create database postgres with template template1

As for the encoding, make a new db with the same encoding as your last
db, and try again.  If that doesn't work, look up iconv in the
archives / google.  Lets you convert from encoding to another.

The vacuumdb error is much more worrisome and makes me wonder about
your hardware / OS reliability / possible driver error.

Thank you Scott!

I'm away from my desk and will dive back into it.
Fortunately I have two machines, each w/ 7.4 & 8.2 on them.  New
installs on Ubuntu 7.4.

As to why I had 'no role or database' errors yesterday, am I right
that it was either:
A)  I accidentally did a pg_dump when I thought I'd done a
pg_dumpall, or
B)  Using the text file output of pg_dumpall behaves differently on
import than the -Fc format?
I'd imported it w/ psql, since it was a text file.

Thanks!
Ralph Smith
smithrn@u.washington.edu
=====================


On Oct 19, 2007, at 1:35 PM, Scott Marlowe wrote:

> On 10/19/07, Ralph Smith <smithrn@u.washington.edu> wrote:
>> I'm using 7.4 in preparation for an overdue upgrade.
>>
>> Yesterday I posted 2 questions that were ignored, so I'll try a
>> third now.
>>
>> When doing a query I  get same error as below, which are the
>> results of
>> vacuumdb.
>>
>> vacuumdb: vacuuming of database "airburst" failed: ERROR:  could
>> not access
>> status of transaction 7564911
>> DETAIL:  could not open file
>> "/var/lib/postgresql/7.4/main/pg_clog/0007": No such file
>> or directory
>>
>> I'm only querying from 1 table.
>> I could really use some help.
>>
>> ----
>>
>> Regarding yesterday's Qs, how  best to un-import from pg_dumpall's
>> results?
>> I'd dump-all'd into a text file and imported it via a psql -f
>> filename.
>> It had encoding errors AND did NOT make the database I needed.
>> Everything
>> went into db postgres.
>
> yeah, you should be able to just drop and recreate postgres.  As the
> postgres superuser, something like:
>
> dropdb postgres
> createdb -T template1 postgres
> OR if it fails not finding the postgres db, then
> psql template1 -U postgres
> create database postgres with template template1
>
> As for the encoding, make a new db with the same encoding as your last
> db, and try again.  If that doesn't work, look up iconv in the
> archives / google.  Lets you convert from encoding to another.
>
> The vacuumdb error is much more worrisome and makes me wonder about
> your hardware / OS reliability / possible driver error.

On 10/19/07, Ralph Smith <smithrn@u.washington.edu> wrote:
> Thank you Scott!
>
> I'm away from my desk and will dive back into it.
> Fortunately I have two machines, each w/ 7.4 & 8.2 on them.  New
> installs on Ubuntu 7.4.
>
> As to why I had 'no role or database' errors yesterday, am I right
> that it was either:
> A)  I accidentally did a pg_dump when I thought I'd done a
> pg_dumpall, or
> B)  Using the text file output of pg_dumpall behaves differently on
> import than the -Fc format?
> I'd imported it w/ psql, since it was a text file.

Most likely the first.  Without a pg_dumpall you don't get the
accounts.  Note that you can do just a pg_dumpalll -g to get the
"global" data, which includes the accounts.

> On 10/19/07, Ralph Smith <smithrn@u.washington.edu> wrote:
>> Thank you Scott!
>>
>> I'm away from my desk and will dive back into it.
>> Fortunately I have two machines, each w/ 7.4 & 8.2 on them.  New
>> installs on Ubuntu 7.4.
>>
>> As to why I had 'no role or database' errors yesterday, am I right
>> that it was either:
>> A)  I accidentally did a pg_dump when I thought I'd done a
>> pg_dumpall, or
>> B)  Using the text file output of pg_dumpall behaves differently on
>> import than the -Fc format?
>> I'd imported it w/ psql, since it was a text file.
==========================================
> On Oct 19, 2007, at 2:06 PM, Scott Marlowe wrote:

> Most likely the first.  Without a pg_dumpall you don't get the
> accounts.  Note that you can do just a pg_dumpalll -g to get the
> "global" data, which includes the accounts.
----------------------------------------------------------
I'm making progess, but SLOOOOOWLY...

I now have a new db postgres, and now my targed db airburst

I have both 7.4 & 8.2 on this box.
When I:
    postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ psql -U
airburst airburst -p 5433

I get:
    psql: FATAL:  IDENT authentication failed for user "airburst"

REFERRING TO /etc/postgresql/7.4/main
My pg_hba.conf has:
    # IPv4-style local connections:
    host    all         all         127.0.0.1
255.255.255.255   trust            # RNS added

My postgresql.conf has:
    syslog = 2 # 0                  # range 0-2; 0=stdout; 1=both;
2=syslog
    client_min_messages = debug1
    log_min_messages = debug1
    log_min_error_statement = error

YET I'm getting NO logging in either /var/log/syslog nor in /var/log/
postgresql/postgresql-7.4-main.log

WHY?
Why no logging
and Why unable to connect?

User postgres connects fine.

One more thanks!

Ralph Smith
smithrn@u.washington.edu
=====================

I have both 7.4 & 8.2 on this box.
Everything below is WRT v7.4

User postgres connects fine.

When I:
    postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ psql -U
airburst airburst -p 5433

I get:
    psql: FATAL:  IDENT authentication failed for user "airburst"

REFERRING TO /etc/postgresql/7.4/main
My pg_hba.conf has:
    # IPv4-style local connections:
    host    all         all         127.0.0.1
255.255.255.255   trust            # RNS added

My postgresql.conf has:
    syslog = 2 # 0                  # range 0-2; 0=stdout; 1=both;
2=syslog
    client_min_messages = debug1
    log_min_messages = debug1
    log_min_error_statement = error

YET I'm getting NO logging in either /var/log/syslog nor in /var/log/
postgresql/postgresql-7.4-main.log

WHY?
Why no logging
and Why unable to connect?


Ralph Smith
smithrn@u.washington.edu
=====================

On 10/19/07, Ralph Smith <smithrn@u.washington.edu> wrote:
> > On 10/19/07, Ralph Smith <smithrn@u.washington.edu> wrote:
> >> Thank you Scott!
> >>
> >> I'm away from my desk and will dive back into it.
> >> Fortunately I have two machines, each w/ 7.4 & 8.2 on them.  New
> >> installs on Ubuntu 7.4.
> >>
> >> As to why I had 'no role or database' errors yesterday, am I right
> >> that it was either:
> >> A)  I accidentally did a pg_dump when I thought I'd done a
> >> pg_dumpall, or
> >> B)  Using the text file output of pg_dumpall behaves differently on
> >> import than the -Fc format?
> >> I'd imported it w/ psql, since it was a text file.
> ==========================================
> > On Oct 19, 2007, at 2:06 PM, Scott Marlowe wrote:
>
> > Most likely the first.  Without a pg_dumpall you don't get the
> > accounts.  Note that you can do just a pg_dumpalll -g to get the
> > "global" data, which includes the accounts.
> ----------------------------------------------------------
> I'm making progess, but SLOOOOOWLY...
>
> I now have a new db postgres, and now my targed db airburst
>
> I have both 7.4 & 8.2 on this box.
> When I:
>     postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ psql -U
> airburst airburst -p 5433
>
> I get:
>     psql: FATAL:  IDENT authentication failed for user "airburst"
>
> REFERRING TO /etc/postgresql/7.4/main
> My pg_hba.conf has:
>     # IPv4-style local connections:
>     host    all         all         127.0.0.1
> 255.255.255.255   trust            # RNS added
>
> My postgresql.conf has:
>     syslog = 2 # 0                  # range 0-2; 0=stdout; 1=both;
> 2=syslog
>     client_min_messages = debug1
>     log_min_messages = debug1
>     log_min_error_statement = error
>
> YET I'm getting NO logging in either /var/log/syslog nor in /var/log/
> postgresql/postgresql-7.4-main.log
>
> WHY?
> Why no logging
> and Why unable to connect?

I don't know why you're not getting any logging, on my ubuntu 7.4
laptop, it just works.  However, on the logging in, you want to edit
your pg_hba.conf file.  There's a page on it in the docs, but it's
pretty much self-documented with lots of comments inside it.  Then
reload or restart pgsql to make the changes take effect.

Ralph Smith <smithrn@u.washington.edu> writes:
> When I:
>     postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ psql -U
> airburst airburst -p 5433

> I get:
>     psql: FATAL:  IDENT authentication failed for user "airburst"

This is not surprising, seeing that you're evidently logged in as
postgres not airburst.  psql's -U option is basically guaranteed not
to work under IDENT authentication: you have to be logged in as the
same username, so -U is useless.  If that's not what you want,
you need to change the pg_hba.conf file --- see
http://www.postgresql.org/docs/7.4/static/client-authentication.html
On a single-user box it wouldn't be unreasonable to use TRUST auth
(at least for local connections); otherwise you probably want to
think about setting up passwords.

> My postgresql.conf has:
>     syslog = 2 # 0                  # range 0-2; 0=stdout; 1=both;
> 2=syslog
>     client_min_messages = debug1
>     log_min_messages = debug1
>     log_min_error_statement = error

> YET I'm getting NO logging in either /var/log/syslog nor in /var/log/
> postgresql/postgresql-7.4-main.log

I think Postgres is probably faithfully sending messages to the syslog
daemon, and the syslog daemon is throwing 'em away because it's not
configured to log 'em.  Check your local documentation for syslogd,
but you probably need something like

local0.*        /var/log/postgresql

added to its configuration file.

            regards, tom lane

On Oct 19, 2007, at 8:25 PM, Tom Lane wrote:

> Ralph Smith <smithrn@u.washington.edu> writes:
>> When I:
>>     postgres@smithrn-ltb1:/usr/lib/postgresql/7.4/bin$ psql -U
>> airburst airburst -p 5433
>
>> I get:
>>     psql: FATAL:  IDENT authentication failed for user "airburst"
>
> This is not surprising, seeing that you're evidently logged in as
> postgres not airburst.  psql's -U option is basically guaranteed not
> to work under IDENT authentication: you have to be logged in as the
> same username, so -U is useless.  If that's not what you want,
> you need to change the pg_hba.conf file --- see
> http://www.postgresql.org/docs/7.4/static/client-authentication.html
> On a single-user box it wouldn't be unreasonable to use TRUST auth
> (at least for local connections); otherwise you probably want to
> think about setting up passwords.
>
>> My postgresql.conf has:
>>     syslog = 2 # 0                  # range 0-2; 0=stdout; 1=both;
>> 2=syslog
>>     client_min_messages = debug1
>>     log_min_messages = debug1
>>     log_min_error_statement = error
>
>> YET I'm getting NO logging in either /var/log/syslog nor in /var/log/
>> postgresql/postgresql-7.4-main.log
>
> I think Postgres is probably faithfully sending messages to the syslog
> daemon, and the syslog daemon is throwing 'em away because it's not
> configured to log 'em.  Check your local documentation for syslogd,
> but you probably need something like
>
> local0.*        /var/log/postgresql
>
> added to its configuration file.
>
>             regards, tom lane
==================================================
Thanks Tom.

After much distraction and snooping, I see that I do need to add an
entry to /etc/syslog.conf

I'm leaning towards

postgres.*    /var/log/postgresql

I'll try that first.  Does anyone know if there is a way that I can
direct two different running versions of postgresql to different logs?

Thanks again!
Ralph Smith
smithrn@u.washington.edu
==================================================