Re: No databases visible on pg 8.3 server

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(at)tpg(dot)com(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin>
>