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@xxxxxxxxxx
<mailto:pheuvel@xxxxxxxxxx>> 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@xxxxxxxxxxxxxx
<mailto:pgsql-admin@xxxxxxxxxxxxxx>)
To make changes to your subscription:
http://www.postgresql.org/__mailpref/pgsql-admin
<http://www.postgresql.org/mailpref/pgsql-admin>
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
