Re: Is my database now too big?
От | Darren Reed |
---|---|
Тема | Re: Is my database now too big? |
Дата | |
Msg-id | 4712E839.7090403@fastmail.net обсуждение исходный текст |
Ответ на | Re: Is my database now too big? (Darren Reed <darrenr+postgres@fastmail.net>) |
Список | pgsql-admin |
Darren Reed wrote: > Scott Marlowe wrote: >> On 10/7/07, Darren Reed <darrenr+postgres@fastmail.net> wrote: >> > Scott Marlowe wrote: >> > > On 10/7/07, Darren Reed <darrenr@fastmail.net> wrote: >> > > > Scott Marlowe wrote: >> >> > A few days ago I did: >> > pg_dumpall > foo >> > What I was doing yesterday was: >> > rm -rf /data/db/* >> > initdb -D /data/db >> > start >> > psql < foo >> > run for some period >> > stop >> > reboot >> > start >> > ...tables have gone but disk space is still in use. >> > I dont know if it was during the period of running that the >> > database got corrupted (interrupted insert/update/query?) >> > or what happened. >> >> Are you sure postgresql was starting up in the /data/db directory >> after reboot and not somewhere else like /var/lib/pgsql/data??? >> >> IF you're definitely hitting the right directory, then Is the database >> shutting down cleanly on reboot? It might be that it's getting killed >> during a write and you've got some kind of problem with fsync on your >> machine so the db is getting corrupted >> >> > > Can you be more specific on what exact query causes the problem >> to show up? >> > > >> > >> > It turned out that _any_ query on that table caused the problem to >> show up. >> > >> > I couldn't even do "DROP TABLE ifl;" without postgres growing until it >> > ran out of memory. >> >> definitely sounds like some kind of issue other just the size of the >> table, like some kind of corruption. >> >> ... >> And I don't see anything else in your postgresql.conf that looks >> suspicious. I'm leaning towards possible pilot error in shutting down >> or starting up the db. >> > > Ok, I've had another reoccurance of this problem. > > The sequence of events was something like this: > CREATE TABLESPACE foo LOCATION "/data/index/ext"; > <wait> > <machine hangs> > <reboot> > Of course postgresql didn't shut down cleanly because it was > naughtly earlier and ate all my RAM, causing the box to hang. > Now I'm back to the prior problem: entire tables are missing > when postgresql starts back up again. Obviously there is some > sort of corruption (caused by postgresql) and it isn't able to > recover properly. I suppose the obvious question here is how do I restore the missing tables? The data is all there, is there some sort of transaction log that can be unwound to restore visibility of the missing tables? Where do I start for trying to do some forensics? Or am I SOL? Later on... So having upgraded to 8.1.10, I thought I'd try out a few things... # /usr/pkg/bin/pg_dumpall -- -- PostgreSQL database cluster dump -- \connect postgres -- -- Roles -- CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN; CREATE ROLE root; ALTER ROLE root WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN; -- -- Database creation -- REVOKE ALL ON DATABASE template1 FROM PUBLIC; REVOKE ALL ON DATABASE template1 FROM postgres; GRANT ALL ON DATABASE template1 TO postgres; \connect postgres pg_dump: SQL command failed pg_dump: Error message from server: ERROR: out of memory DETAIL: Failed on request of size 20. pg_dump: The command was: LOCK TABLE public.foo IN ACCESS SHARE MODE pg_dumpall: pg_dump failed on database "postgres", exiting # SELECT * FROM foo LIMIT 1; ERROR: out of memory DETAIL: Failed on request of size 20. I don't know if this is of any significance: # \l List of databases Name | Owner | Encoding -----------+----------+----------- postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (3 rows) # /usr/pkg/bin/psql -U postgres template1 psql: FATAL: out of memory DETAIL: Failed on request of size 20. What puzzles me is why the transaction log hasn't resulted in postgresql being able to restore itself to a known clean state. Darren
В списке pgsql-admin по дате отправления: