Обсуждение: ERROR: Could not access status of transaction ####
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.
Please help.
Ralph Smith
=====================
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 ==================================================