1

Temat: Postgres nie wstaje! POMOCY !

Mam aplikację, która kopiuje dane z Oracle do Postgre 8.3 używając polecenia COPY. Podczas wykonywania importu, zostały zmienione uprawnienia jednego z katalogów w katalogu base (base/22904). Automatycznie po zmianie uprawnień postgres się wywalił (windows pozwolił na zmianę uprawnień). Teraz usługa nie wstaje a w logach znalazłem:

%t LOG:  database system was interrupted while in recovery at 2009-06-17 15:21:09 CEST
%t HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
%t LOG:  database system was not properly shut down; automatic recovery in progress
%t LOG:  redo starts at 5F/6804BD60
%t FATAL:  could not create relation 1663/22904/623510509: File exists
%t CONTEXT:  xlog redo insert: rel 1663/22904/623510509; tid 853987/20
%t DZIENNIK:  startup process (PID 3336) exited with exit code 1
%t DZIENNIK:  aborting startup due to startup process failure

W jaki sposób odzyskać, wystartować bazę? Nie mogę stracić danych (w tabelach jest około 1 mld rekordów), a nie mogę zacząć importować od początku. Nie mam żadnego backupu bazy (wiem idiotyczne ale terminy gonią i codziennie kilkdziesiąt mln rekordów musi zostać zaimportowane). Błagam pomocy!

Ostatnio edytowany przez smdvq (2009-06-17 17:26:58)

2

Odp: Postgres nie wstaje! POMOCY !

Czy te wszystkie logi  dostajesz po uruchomieniu systemu? Czy część jest pozostałością po imporcie?

3

Odp: Postgres nie wstaje! POMOCY !

Te logi dostaje gdy próbuje wystartowac usługę postgresa z windowsa. Usługa nie wstaje.

4

Odp: Postgres nie wstaje! POMOCY !

Wyglada jakby postgres zatrzymal sie w trakcie kopiowania danych. Masz jakieś pliki w katalogu pg_xlog?

5

Odp: Postgres nie wstaje! POMOCY !

Tak mam pliki w katalogu xlog.

Udalo mi sie wystartowac baze. Po prostu usunalem bladne pliki (np 623510509) z systemu plikow i pozwolilem aby redo mi je przywrocilo. Usluga bazy startuje jednak po sekundzie zatrzymuje sie. W logach znalazlem:

%t LOG:  database system was interrupted; last known up at 2009-06-18 10:55:41 CEST
%t LOG:  database system was not properly shut down; automatic recovery in progress
%t LOG:  record with zero length at 5F/73E3A440
%t LOG:  redo is not required
%t DZIENNIK:  database system is ready to accept connections
%t LOG:  autovacuum launcher started
%t PANIC:  could not open critical system index 2662


This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

%t DZIENNIK:  server process (PID 2992) exited with exit code 3
%t DZIENNIK:  terminating any other active server processes
%t DZIENNIK:  all server processes terminated; reinitializing
%t KATASTROFALNY:  pre-existing shared memory block is still in use
%t PODPOWIEDŹ:Check if there are any old server processes still running, and terminate them.

6

Odp: Postgres nie wstaje! POMOCY !

7

Odp: Postgres nie wstaje! POMOCY !

Oczywiście zostawiłem sobie tamte pliki. Spróbuje je teraz wgrać z powrotem i odpalic pg_resetxlog. Napisze czy co uzyskałem. Wydaje mi się ze moze być problem z uprawnieniami do systemu plików.

Zauważyłem, że postgres jesli jest odpalany jako service to jest odpalany z poziomu jakiegoś dziwnego użytkownika, którego moj system nie rozpoznaje. Wszystkie katalogi w katalogu base zostały utworzone przez tego usera o nazwie S-1-5-5-0-3664610 (postgresa cały czas odpalałem jako usługę), teraz gdy próbuje go odpalić przez pg_ctl to mam persmission denied. Ktoś może naświetlić ten problem ?

8

Odp: Postgres nie wstaje! POMOCY !

Postgres i windows to zawsze była dla mnie zagadka smile

9

Odp: Postgres nie wstaje! POMOCY !

Wgrałem zepsute pliki i o dziwo bez wywoływania pg_resetxlog postgres wstał. Co prawda nadal są błędy jednak nie wywalił się od razu na początku.  W logach teraz znajduje:

%t LOG:  database system was interrupted; last known up at 2009-06-18 15:26:18 CEST
%t LOG:  database system was not properly shut down; automatic recovery in progress
%t LOG:  record with zero length at 5F/73E3ACF8
%t LOG:  redo is not required
%t DZIENNIK:  database system is ready to accept connections
%t LOG:  autovacuum launcher started
%t ERROR:  index "V_POLA_ODNIESIENIA_pkey" contains unexpected zero page at block 245897
%t HINT:  Please REINDEX it.
%t STATEMENT:    SELECT max("ID") FROM "public"."V_POLA_ODNIESIENIA"
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t FATAL:  could not open file "base/17549/PG_VERSION": Permission denied
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t FATAL:  could not open file "base/21426/PG_VERSION": Permission denied
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t FATAL:  could not open file "base/22237/PG_VERSION": Permission denied
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 0
%t DETAIL:  Could not open file "pg_subtrans/1E5E": Permission denied.
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 0
%t DETAIL:  Could not open file "pg_subtrans/1E5E": Permission denied.
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t ERROR:  could not access status of transaction 471677341
%t DETAIL:  Could not open file "pg_clog/01C1": Permission denied.
%t STATEMENT:  DELETE FROM qrtz_FIRED_TRIGGERS WHERE ENTRY_ID = $1
%t PANIC:  could not open file "pg_xlog/000000010000005F0000007A" (log file 95, segment 122): Permission denied


This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

%t DZIENNIK:  server process (PID 3356) exited with exit code 3
%t DZIENNIK:  terminating any other active server processes
%t WARNING:  terminating connection because of crash of another server process
%t DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
%t HINT:  In a moment you should be able to reconnect to the database and repeat your command.
%t WARNING:  terminating connection because of crash of another server process
%t DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
%t HINT:  In a moment you should be able to reconnect to the database and repeat your command.
%t WARNING:  terminating connection because of crash of another server process
%t DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
%t HINT:  In a moment you should be able to reconnect to the database and repeat your command.
%t WARNING:  terminating connection because of crash of another server process
%t DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
%t HINT:  In a moment you should be able to reconnect to the database and repeat your command.
%t WARNING:  terminating connection because of crash of another server process
%t DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
%t HINT:  In a moment you should be able to reconnect to the database and repeat your command.
%t WARNING:  terminating connection because of crash of another server process
%t DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
%t HINT:  In a moment you should be able to reconnect to the database and repeat your command.
%t DZIENNIK:  all server processes terminated; reinitializing
%t KATASTROFALNY:  pre-existing shared memory block is still in use
%t PODPOWIEDŹ:Check if there are any old server processes still running, and terminate them.

Jak widać nadal są jakieś jazdy z uprawnieniami. Jutro spróbuje odpalić backup na tych tabelach może pójdzie, wtedy jak przywróce na nowej instalce wydaje mi się, że powinno być ok. Mam nadzieje. Musze jeszcze poczytać co jest w katalogu pg_clog. Dam znać jak poszło z backupem.

Ostatnio edytowany przez smdvq (2009-06-18 21:41:11)

10

Odp: Postgres nie wstaje! POMOCY !

Wreszcie się udało. Musiałem zmienić właściciela wszystich plików w katalogu data i postgres wstaje bez żadnego problemu. Mam również dostęp do wszystkich danych. Z jedna tabela są problemy ale prosty: reindex table powinien załatwić sprawę. Zaraz zaczynam backupy.

Tak jak pisałem wcześniej problemem było to, że postgres jako usługa był startowany z poziomu dziwnego usera i wszystko było ok dopóki nie nadałem praw do katalogu bazy danych użytkownikowi postgres. Zastanawiam się tylko czy to był problem tylko u mnie czy ogólnie jest to problem postgresa startującego jako usługa w systemach windows.

Dzięki za pomoc i zainteresowanie.
Pozdrawiam
Patryk S.

11

Odp: Postgres nie wstaje! POMOCY !

Myślę że jako opensourcowy program ciężko mu dogadać się z komercyjnym windowsem smile.