Обсуждение: No databases visible on pg 8.3 server
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
Hello,
From what you have written I assume you are able to properly start postgresql service. First thing I would try would be trying to connect to the db with psql and run some queries mostly SELECTS to check if table data is ok. If successful then run a pg_dump on the db and restore it on a fresh instance.
The problem itself might be caused by three factors taken all together the unfinished transaction causing the xlog read error, your run of reset xlog and the shortage of filesystem space.
Hope this helps
11-11-2012 01:49, "Pat Heuvel" <pheuvel@tpg.com.au> napisał(a):
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On 11/11/2012 08:27 AM, Pat Heuvel wrote:
If you haven't already, before you do ANYTHING ELSE, take a file-system level copy of the PostgreSQL database while the server is stopped. See http://wiki.postgresql.org/wiki/Corruption .
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
--
Craig Ringer
Gday Lukasz, You're right - the service does run. However, no databases are visible. I would love to be able to do a pg_dump/restore! Can you tell me what might make postgresql think there are no databases when the file pg_database and the file 1262 both show databases? This is running on a Windows server (has been for four years or more). There have been no changes to the environment in the last eight months, except for running out of disk space. By which I mean the -D path is correct. Regards, Pat (Sorry, this response didn't make it to the list) On 11/11/2012 6:50 PM, Lukasz Brodziak wrote: > Hello, > > From what you have written I assume you are able to properly start > postgresql service. First thing I would try would be trying to connect > to the db with psql and run some queries mostly SELECTS to check if > table data is ok. If successful then run a pg_dump on the db and restore > it on a fresh instance. > The problem itself might be caused by three factors taken all together > the unfinished transaction causing the xlog read error, your run of > reset xlog and the shortage of filesystem space. > Hope this helps > > 11-11-2012 01:49, "Pat Heuvel" <pheuvel@tpg.com.au > <mailto:pheuvel@tpg.com.au>> napisał(a): > > 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 > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org > <mailto:pgsql-admin@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/__mailpref/pgsql-admin > <http://www.postgresql.org/mailpref/pgsql-admin> >
Thanks Craig, I've already done that. On 11/11/2012 9:15 PM, Craig Ringer wrote: > On 11/11/2012 08:27 AM, Pat Heuvel wrote: >> >> 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 > If you haven't already, before you do ANYTHING ELSE, take a file-system > level copy of the PostgreSQL database while the server is stopped. See > http://wiki.postgresql.org/wiki/Corruption . > > -- > Craig Ringer