Pat Heuvel
2012-11-11 00:27:34 UTC
Gday,
My customer has a large database running under 8.3. I would love to
upgrade, but that's a story for another thread :)
User called me and told me the database was out of space. I managed to
recover a little space by moving the text logs (pg_log) to another drive.
I then had a process read through the large objects table and unlink
those not referenced in the appropriate "user" (as opposed to system) table.
While this process was running I had asked the users not to add any new
items to the database, as I wanted to recover some space first. One user
did not get the message and proceeded to add a new item, which promptly
blew the remaining space and caused postgres to panic and shutdown.
When I tried to restart the database, it failed. Checking the log showed:
2012-11-05 23:03:06 EST LOG: database system was interrupted; last
known up at 2012-11-05 11:48:07 EST
2012-11-05 23:03:06 EST LOG: database system was not properly shut
down; automatic recovery in progress
2012-11-05 23:03:06 EST LOG: redo starts at 285/29010680
2012-11-05 23:03:06 EST LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2012-11-05 23:03:06 EST FATAL: the database system is starting up
2012-11-05 23:03:07 EST LOG: could not open file
"pg_xlog/00000001000002850000002E" (log file 645, segment 46): No such
file or directory
2012-11-05 23:03:07 EST LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2012-11-05 23:03:07 EST FATAL: the database system is starting up
2012-11-05 23:03:08 EST LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2012-11-05 23:03:08 EST FATAL: the database system is starting up
2012-11-05 23:03:08 EST LOG: startup process (PID 872) was terminated
by exception 0xC000000D
2012-11-05 23:03:08 EST HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
2012-11-05 23:03:08 EST LOG: aborting startup due to startup process
failure
After some googling I ran a pg_resetxlog -f with the following results
in the log:
2012-11-06 00:05:22 EST LOG: database system was shut down at
2012-11-06 00:04:57 EST
2012-11-06 00:05:22 EST WARNING: database "template1" must be vacuumed
within 3229614458 transactions
2012-11-06 00:05:22 EST HINT: To avoid a database shutdown, execute a
full-database VACUUM in "template1".
2012-11-06 00:05:22 EST LOG: database system is ready to accept connections
2012-11-06 00:05:22 EST LOG: autovacuum launcher started
2012-11-06 00:05:22 EST LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
I shut down the database, installed another drive, and copied the entire
filesystem to the new drive.
When I next connected to the database, no databases were visible at all,
not even the templates.
After much googling I found references to pg_database/1262. A hex editor
showed the names I was hoping to see: both templates, postgres and my
customer's database. Similarly, the file "pg_database" matched what I
was seeing in 1262.
I had an old hard drive which I replaced in April of this year which had
the same database structure on it. When I replaced it I copied the file
structure to the new drive and restarted the database server against
that. So I copied the 1262 file from that to the current drive - with no
effect. Still couldn't see any databases. Or roles, for that matter...
So please, can someone give me a clue where I should look next? If I
have to, I will start from the old drive again, but I don't really want
to lose the data between April and now if I can avoid it.
By the way, the filesystem /appears/ to have the right amount of
occupied data for the database, despite said database not being visible.
Regards and TIA,
Pat Heuvel
My customer has a large database running under 8.3. I would love to
upgrade, but that's a story for another thread :)
User called me and told me the database was out of space. I managed to
recover a little space by moving the text logs (pg_log) to another drive.
I then had a process read through the large objects table and unlink
those not referenced in the appropriate "user" (as opposed to system) table.
While this process was running I had asked the users not to add any new
items to the database, as I wanted to recover some space first. One user
did not get the message and proceeded to add a new item, which promptly
blew the remaining space and caused postgres to panic and shutdown.
When I tried to restart the database, it failed. Checking the log showed:
2012-11-05 23:03:06 EST LOG: database system was interrupted; last
known up at 2012-11-05 11:48:07 EST
2012-11-05 23:03:06 EST LOG: database system was not properly shut
down; automatic recovery in progress
2012-11-05 23:03:06 EST LOG: redo starts at 285/29010680
2012-11-05 23:03:06 EST LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2012-11-05 23:03:06 EST FATAL: the database system is starting up
2012-11-05 23:03:07 EST LOG: could not open file
"pg_xlog/00000001000002850000002E" (log file 645, segment 46): No such
file or directory
2012-11-05 23:03:07 EST LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2012-11-05 23:03:07 EST FATAL: the database system is starting up
2012-11-05 23:03:08 EST LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
2012-11-05 23:03:08 EST FATAL: the database system is starting up
2012-11-05 23:03:08 EST LOG: startup process (PID 872) was terminated
by exception 0xC000000D
2012-11-05 23:03:08 EST HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.
2012-11-05 23:03:08 EST LOG: aborting startup due to startup process
failure
After some googling I ran a pg_resetxlog -f with the following results
in the log:
2012-11-06 00:05:22 EST LOG: database system was shut down at
2012-11-06 00:04:57 EST
2012-11-06 00:05:22 EST WARNING: database "template1" must be vacuumed
within 3229614458 transactions
2012-11-06 00:05:22 EST HINT: To avoid a database shutdown, execute a
full-database VACUUM in "template1".
2012-11-06 00:05:22 EST LOG: database system is ready to accept connections
2012-11-06 00:05:22 EST LOG: autovacuum launcher started
2012-11-06 00:05:22 EST LOG: loaded library
"$libdir/plugins/plugin_debugger.dll"
I shut down the database, installed another drive, and copied the entire
filesystem to the new drive.
When I next connected to the database, no databases were visible at all,
not even the templates.
After much googling I found references to pg_database/1262. A hex editor
showed the names I was hoping to see: both templates, postgres and my
customer's database. Similarly, the file "pg_database" matched what I
was seeing in 1262.
I had an old hard drive which I replaced in April of this year which had
the same database structure on it. When I replaced it I copied the file
structure to the new drive and restarted the database server against
that. So I copied the 1262 file from that to the current drive - with no
effect. Still couldn't see any databases. Or roles, for that matter...
So please, can someone give me a clue where I should look next? If I
have to, I will start from the old drive again, but I don't really want
to lose the data between April and now if I can avoid it.
By the way, the filesystem /appears/ to have the right amount of
occupied data for the database, despite said database not being visible.
Regards and TIA,
Pat Heuvel