Re: Can't connect (2 dbs) or login (2 others)

On 9/22/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Morris Goldstein" <morris(dot)x(dot)goldstein(at)gmail(dot)com> writes:
> > Two of the nodes have logs that look like this:
>
> > 2007-09-22 07:06:05 [3060] LOG: could not open file
> > "/var/lib/pgsql/data/pg_xlog/0000000000000000" (log file 0, segment
> > 0): No such file or directory
> > 2007-09-22 07:06:05 [3060] LOG: invalid primary checkpoint record
> > 2007-09-22 07:06:05 [3060] LOG: could not open file
> > "/var/lib/pgsql/data/pg_xlog/0000000000000000" (log file 0, segment
> > 0): No such file or directory
> > 2007-09-22 07:06:05 [3060] LOG: invalid secondary checkpoint record
> > 2007-09-22 07:06:05 [3060] PANIC: could not locate a valid checkpoint record
>
> This is not good. Is it really complaining about log file 0? I can
> hardly credit that a database you care about wouldn't have used more log
> space than that.

There is much more log space. The pg_xlog files are 0000000900000C2-
0000000A0000006C.

>
> You might be able to recover, at least to the extent of getting some
> data out of these DBs, by using pg_resetxlog. Read its man page
> *carefully* beforehand.
> http://www.postgresql.org/docs/7.4/static/app-pgresetxlog.html
>
> > The other two look like this:
>
> > FATAL: IDENT authentication failed for user "xxx"
>
> This looks like user error, ie, trying to log in as a different Postgres
> user than your Unix username is. You can hardly have been accustomed to
> doing that if you were normally using IDENT authentication before.
> If you want to switch to some other authentication method, read the docs
> http://www.postgresql.org/docs/7.4/static/client-authentication.html
> and edit pg_hba.conf.

No, it isn't user error. I tried logging in myself with our usual ids and they
were rejected. Here is the end of strace output on an attempt to connect
using psql.

open("/lib/libnss_files.so.2", O_RDONLY) = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\240\31"..., 512) = 512
fstat64(3, {st_mode=S_IFREG|0755, st_size=46740, ...}) = 0
mmap2(NULL, 41616, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3,
0) = 0xd26000
mmap2(0xd2f000, 8192, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x8) = 0xd2f000
close(3) = 0
mprotect(0xd2f000, 4096, PROT_READ) = 0
munmap(0xb7f2f000, 17508) = 0
open("/etc/passwd", O_RDONLY) = 3
fcntl64(3, F_GETFD) = 0
fcntl64(3, F_SETFD, FD_CLOEXEC) = 0
fstat64(3, {st_mode=S_IFREG|0644, st_size=1435, ...}) = 0
mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0xb7f33000
read(3, "root:x:0:0:root:/root:/bin/bash\n"..., 4096) = 1435
close(3) = 0
munmap(0xb7f33000, 4096) = 0
stat64("/root/.pgpass", 0xbf9b3288) = -1 ENOENT (No such file or directory)
socket(PF_FILE, SOCK_STREAM, 0) = 3
fcntl64(3, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
connect(3, {sa_family=AF_FILE, path="/tmp/.s.PGSQL.5432"}, 110) = -1
ENOENT (No such file or directory)
close(3) = 0
write(2, "psql: could not connect to serve"..., 163psql: could not
connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
) = 163
exit_group(2) = ?
Process 11653 detached

>
> BTW, 7.4.8 is pretty old; you really ought to be on something more
> current. The latest release in that branch is 7.4.18 --- you are
> missing two years worth of bug fixes.

The fun and games started during an upgrade to newer software, which
includes 7.4.18.

Morris