Обсуждение: Is my db dead ?
My server crashed yesterday, the hd was saved though and I was able to get a copy of the data directory of my pg cluster. On the server now, when I start pg, I can see only like 1% of my data, there are many dbs/ tables and rows missing. But the data dir seems ok in size though. So I copied the whole data dir to a new serv, installed the same pg version but now I can't get it started at all. There are some weird things in the pg_controldata : pg_control last modified: Tue 03 Sep 6869 17:57:36 CET Time of latest checkpoint: Thu 11 Jan 1116953 03:46:31 CET LC_COLLATE: LC_CTYPE: So now I can't start the server because of the blank locale : Starting PostgreSQL 8.1 database server: main* Error: The server must be started under the locale : which does not exist any more. Soo... Is there a chance for me to see again my data ? What can I do to fix this ? Thanks for any suggestions
dawmette@gmail.com escribió: > My server crashed yesterday, the hd was saved though and I was able to > get a copy of the data directory of my pg cluster. On the server now, > when I start pg, I can see only like 1% of my data, there are many dbs/ > tables and rows missing. But the data dir seems ok in size though. So > I copied the whole data dir to a new serv, installed the same pg > version but now I can't get it started at all. There are some weird > things in the pg_controldata : > > pg_control last modified: Tue 03 Sep 6869 17:57:36 CET > Time of latest checkpoint: Thu 11 Jan 1116953 03:46:31 CET > LC_COLLATE: > LC_CTYPE: What's the full pg_controldata output? Can you send the pg_control file as a binary attachment? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Ok in fact, the times weird thing were because I copied the data dir on a 64 bits arch and it went from a 32 bit arch. On the 32 bit one :
pg_control version number: 812
Catalog version number: 200510211
Database system identifier: 4989617165674917617
Database cluster state: in production
pg_control last modified: Mon 04 Feb 2008 20:43:33 CET
Current log file ID: 0
Next log file segment: 34
Latest checkpoint location: 0/2109832C
Prior checkpoint location: 0/210982E8
Latest checkpoint's REDO location: 0/2109832C
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 1315262
Latest checkpoint's NextOID: 34543
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Time of latest checkpoint: Mon 04 Feb 2008 20:05:40 CET
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Date/time type storage: 64-bit integers
Maximum length of locale name: 128
LC_COLLATE: fr_FR.utf8
LC_CTYPE: fr_FR.utf8
Everything seems ok but I still can't see 99% of my data. No error msg, nothing special, just a ton of relations does not exist and empty or almost empty tables.
pg_control version number: 812
Catalog version number: 200510211
Database system identifier: 4989617165674917617
Database cluster state: in production
pg_control last modified: Mon 04 Feb 2008 20:43:33 CET
Current log file ID: 0
Next log file segment: 34
Latest checkpoint location: 0/2109832C
Prior checkpoint location: 0/210982E8
Latest checkpoint's REDO location: 0/2109832C
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 1315262
Latest checkpoint's NextOID: 34543
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Time of latest checkpoint: Mon 04 Feb 2008 20:05:40 CET
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Date/time type storage: 64-bit integers
Maximum length of locale name: 128
LC_COLLATE: fr_FR.utf8
LC_CTYPE: fr_FR.utf8
Everything seems ok but I still can't see 99% of my data. No error msg, nothing special, just a ton of relations does not exist and empty or almost empty tables.
On Feb 5, 2008 4:19 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
dawmette@gmail.com escribió:> My server crashed yesterday, the hd was saved though and I was able toWhat's the full pg_controldata output? Can you send the pg_control file
> get a copy of the data directory of my pg cluster. On the server now,
> when I start pg, I can see only like 1% of my data, there are many dbs/
> tables and rows missing. But the data dir seems ok in size though. So
> I copied the whole data dir to a new serv, installed the same pg
> version but now I can't get it started at all. There are some weird
> things in the pg_controldata :
>
> pg_control last modified: Tue 03 Sep 6869 17:57:36 CET
> Time of latest checkpoint: Thu 11 Jan 1116953 03:46:31 CET
> LC_COLLATE:
> LC_CTYPE:
as a binary attachment?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Вложения
Ok I progressed a bit, now with psql I can see my main db and some tables, but they don't contain many rows.
Noticable messages in the logfile :
2008-02-05 18:01:18 CET LOG: transaction ID wrap limit is 1073748480, limited by database "wowdbu"
2008-02-05 18:01:18 CET LOG: autovacuum: processing database "postgres"
2008-02-05 18:01:18 CET LOG: incomplete startup packet
2008-02-05 18:02:08 CET LOG: incomplete startup packet
I tried a pg_dumpall and it fails quickly with this error :
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not access status of transaction 3270404
DETAIL: could not open file "pg_clog/0003": No such file or directory
pg_dump: The command was: SELECT tableoid, oid, proname, prolang, pronargs, proargtypes, prorettype, proacl, pronamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = proowner) as rolname FROM pg_proc WHERE NOT proisagg AND pronamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
pg_dumpall: pg_dump failed on database "postgres", exiting
I read about things like zeroing missing pg_clog files... Can it work ? I thought clog files were about transactions, but I don't care about last transactions, even data from 1 month ago would be enough for me.
So what can I do from there ?
Noticable messages in the logfile :
2008-02-05 18:01:18 CET LOG: transaction ID wrap limit is 1073748480, limited by database "wowdbu"
2008-02-05 18:01:18 CET LOG: autovacuum: processing database "postgres"
2008-02-05 18:01:18 CET LOG: incomplete startup packet
2008-02-05 18:02:08 CET LOG: incomplete startup packet
I tried a pg_dumpall and it fails quickly with this error :
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not access status of transaction 3270404
DETAIL: could not open file "pg_clog/0003": No such file or directory
pg_dump: The command was: SELECT tableoid, oid, proname, prolang, pronargs, proargtypes, prorettype, proacl, pronamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = proowner) as rolname FROM pg_proc WHERE NOT proisagg AND pronamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
pg_dumpall: pg_dump failed on database "postgres", exiting
I read about things like zeroing missing pg_clog files... Can it work ? I thought clog files were about transactions, but I don't care about last transactions, even data from 1 month ago would be enough for me.
So what can I do from there ?
On Feb 5, 2008 4:19 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
dawmette@gmail.com escribió:> My server crashed yesterday, the hd was saved though and I was able toWhat's the full pg_controldata output? Can you send the pg_control file
> get a copy of the data directory of my pg cluster. On the server now,
> when I start pg, I can see only like 1% of my data, there are many dbs/
> tables and rows missing. But the data dir seems ok in size though. So
> I copied the whole data dir to a new serv, installed the same pg
> version but now I can't get it started at all. There are some weird
> things in the pg_controldata :
>
> pg_control last modified: Tue 03 Sep 6869 17:57:36 CET
> Time of latest checkpoint: Thu 11 Jan 1116953 03:46:31 CET
> LC_COLLATE:
> LC_CTYPE:
as a binary attachment?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Eric Renard escribió: > pg_dump: Error message from server: ERROR: could not access status of > transaction 3270404 > DETAIL: could not open file "pg_clog/0003": No such file or directory > pg_dump: The command was: SELECT tableoid, oid, proname, prolang, pronargs, > proargtypes, prorettype, proacl, pronamespace, (SELECT rolname FROM > pg_catalog.pg_roles WHERE oid = proowner) as rolname FROM pg_proc WHERE NOT > proisagg AND pronamespace != (select oid from pg_namespace where nspname = > 'pg_catalog') > pg_dumpall: pg_dump failed on database "postgres", exiting > > I read about things like zeroing missing pg_clog files... Can it work ? I > thought clog files were about transactions, but I don't care about last > transactions, even data from 1 month ago would be enough for me. > So what can I do from there ? Before you start randomly zeroing stuff, do you have any files in pg_clog? What does that directory look like? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I have only 2 files in this dir :
total 336
-rw------- 1 postgres postgres 262144 2006-11-15 18:34 0000
-rw------- 1 postgres postgres 73728 2008-02-05 14:09 0001
I thought clog files were only files related to transactions in progress ? There's no way to tell psql to ignore all current transactions and act like if they were rolled back or something ?
total 336
-rw------- 1 postgres postgres 262144 2006-11-15 18:34 0000
-rw------- 1 postgres postgres 73728 2008-02-05 14:09 0001
I thought clog files were only files related to transactions in progress ? There's no way to tell psql to ignore all current transactions and act like if they were rolled back or something ?
On Feb 5, 2008 8:47 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Eric Renard escribió:Before you start randomly zeroing stuff, do you have any files in
> pg_dump: Error message from server: ERROR: could not access status of
> transaction 3270404
> DETAIL: could not open file "pg_clog/0003": No such file or directory
> pg_dump: The command was: SELECT tableoid, oid, proname, prolang, pronargs,
> proargtypes, prorettype, proacl, pronamespace, (SELECT rolname FROM
> pg_catalog.pg_roles WHERE oid = proowner) as rolname FROM pg_proc WHERE NOT
> proisagg AND pronamespace != (select oid from pg_namespace where nspname =
> 'pg_catalog')
> pg_dumpall: pg_dump failed on database "postgres", exiting
>
> I read about things like zeroing missing pg_clog files... Can it work ? I
> thought clog files were about transactions, but I don't care about last
> transactions, even data from 1 month ago would be enough for me.
> So what can I do from there ?
pg_clog? What does that directory look like?
--Alvaro Herrera http://www.CommandPrompt.com/The PostgreSQL Company - Command Prompt, Inc.