Re: No databases visible on pg 8.3 server - Mailing list pgsql-admin

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>
>