Обсуждение: recovering data from pgsql 6.1 installation
Hi! The problem: We've had the pgsql 6.1 installation made by previous admin. Due instability, I am to upgrade it to the current one. Having no previous experience with pgsql, I've forgot to dump db :( Now, neither pgsql 6.3, 6.2 or even 6.1 can't read the db. $ psql mailing Connection to database 'mailing' failed. PQexec() -- Request was sent to backend, but backend closed the channel before responding. This probably means the backend terminated abnormally before or while processing the request. How I can find more verbose error message? What are the possible ways to recover the data? Pointers to TFM would be very helpfull. -- SY, Andrey V Khavryutchenko http://www.kbi.kiev.ua/~akhavr Shick's Law: There is no problem a good miracle can't solve.
Andrey V Khavryutchenko wrote: >Hi! > >The problem: > >We've had the pgsql 6.1 installation made by previous admin. Due >instability, I am to upgrade it to the current one. Having no previous >experience with pgsql, I've forgot to dump db :( > >Now, neither pgsql 6.3, 6.2 or even 6.1 can't read the db. > >$ psql mailing >Connection to database 'mailing' failed. >PQexec() -- Request was sent to backend, but backend closed the channel >before responding. This probably means the backend terminated abnormally >before or while processing the request. > >How I can find more verbose error message? What are the possible ways to >recover the data? Pointers to TFM would be very helpfull. Look at the man page for postgres. I suggest you turn on debugging in the backend: nohup postmaster -o -e -d 2 >> /var/log/postgres.log 2>&1 & Look at the end of /var/log/postgres.log immediately after a failure to get an indication of what happened. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "Who is like unto thee, O LORD, among the gods? who is like thee, glorious in holiness, fearful in praises, doing wonders?" Exodus 15:11
Hi, Oliver! >>>>> "OE" == Oliver Elphick writes: OE> Look at the man page for postgres. Something in particular? OE> I suggest you turn on debugging in the backend: nohup postmaster -o -e OE> -d 2 >> /var/log/postgres.log 2>&1 & OE> Look at the end of /var/log/postgres.log immediately after a failure to OE> get an indication of what happened. $ cat /var/log/postgres.d/messages [...] /home/postgres/bin/postmaster: BackendStartup: pid 20300 user akhavr db mailing socket 5 /home/postgres/bin/postmaster: ServerLoop: done with 5 /home/postgres/bin/postmaster child[20300]: execv(/home/postgres/bin/postgres, -p, -d2, -P5, -e, mailing, ) FindBackend: found "/home/postgres/bin/postgres" using argv[0] /home/postgres/bin/postmaster: reaping dead processes... /home/postgres/bin/postmaster: CleanupProc: pid 20300 exited with status 139 /home/postgres/bin/postmaster: CleanupProc: reinitializing shared memory and semaphores binding ShmemCreate(key=9428cec, size=758552) $ sudo -u postgres /home/postgres/bin/postgres \ -D /home/postgres/data -e -d 2 mailing FindBackend: found "/home/postgres/bin/postgres" using argv[0] ---debug info--- Quiet = f Noversion = f stable = f timings = f dates = European bufsize = 64 query echo = f multiplexed backend? = f DatabaseName = [mailing] ---------------- InitPostgres().. binding ShmemCreate(key=0, size=758552) Segmentation fault (core dumped) I guess I should rebuild postgres with debug info and report the backtrace? -- SY, Andrey V Khavryutchenko http://www.kbi.kiev.ua/~akhavr Shick's Law: There is no problem a good miracle can't solve.
Andrey V Khavryutchenko wrote: >OE> Look at the man page for postgres. > >Something in particular? I can't remember what your original question was! postgres is the backend program, which is started by postmaster for each connection. >OE> I suggest you turn on debugging in the backend: nohup postmaster -o -e >OE> -d 2 >> /var/log/postgres.log 2>&1 & >$ sudo -u postgres /home/postgres/bin/postgres \ > -D /home/postgres/data -e -d 2 mailing ... >Segmentation fault (core dumped) > >I guess I should rebuild postgres with debug info and report the backtrace? Remember you can also use gdb (or ddd, which is nicer) to attach to a running process. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "Who is like unto thee, O LORD, among the gods? who is like thee, glorious in holiness, fearful in praises, doing wonders?" Exodus 15:11
Hi! I'm trying to recover the database, created by pgsql, installed by previous admin. Freshly installed postgresql 6.1 can't read it. Investigation showed up that the backend dumps core. The backtrace follows: $ gdb postgres core GDB is free software and you are welcome to distribute copies of it under certain conditions; type "show copying" to see the conditions. There is absolutely no warranty for GDB; type "show warranty" for details. GDB 4.16 (i586-ksi-linux), Copyright 1996 Free Software Foundation, Inc... (no debugging symbols found)... Core was generated by `/home/akhavr/postgres -D /home/postgres/data -e -d 2 mail ingad'. Program terminated with signal 11, Segmentation fault. Reading symbols from /usr/lib/libcrypt.so.1...(no debugging symbols found)... done. Reading symbols from /lib/libdl.so.1...(no debugging symbols found)...done. Reading symbols from /lib/libm.so.5...(no debugging symbols found)...done. Reading symbols from /usr/lib/libreadline.so.2... (no debugging symbols found)...done. Reading symbols from /lib/libtermcap.so.2...(no debugging symbols found)... done. Reading symbols from /lib/libc.so.5...(no debugging symbols found)...done. Reading symbols from /lib/ld-linux.so.1...(no debugging symbols found)...done. #0 0x80e89b0 in comphash () (gdb) bt #0 0x80e89b0 in comphash () #1 0x80e8a5f in CatalogCacheComputeHashIndex () #2 0x80e8fc2 in SearchSysCache () #3 0x80eb1ec in SearchSysCacheTuple () #4 0x80ee325 in SetUserId () #5 0x80ee698 in InitUserid () #6 0x80ee8ba in InitPostgres () #7 0x80ccb36 in PostgresMain () #8 0x80964d7 in main () #9 0x80621de in _start () (gdb) -- SY, Andrey V Khavryutchenko http://www.kbi.kiev.ua/~akhavr Shick's Law: There is no problem a good miracle can't solve.