From: Pat Heuvel <pheuvel@tpg.com.au>
Subject: Re: No databases visible on pg 8.3 server
Date: Tue, 20 Nov 2012 22:49:09 +1100
Organization: Hub.Org Networking Services
Message-ID: <50AB6E35.1020506@tpg.com.au>
References: <k7mrf0$2nir$1@news.hub.org>
	<CAGWYGjW4U7pGdJmvx23J6fi0=Y6mmk8ZxodG_ThXFum6QgpH1A@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
User-Agent: Mozilla/5.0 (Windows NT 6.0;
	rv:16.0) Gecko/20121026 Thunderbird/16.0.2
In-Reply-To: 
 <CAGWYGjW4U7pGdJmvx23J6fi0=Y6mmk8ZxodG_ThXFum6QgpH1A@mail.gmail.com>
To: pgsql-admin@postgresql.org
Content-Transfer-Encoding: quoted-printable

Gday Lukasz,

You're right - the service does run. However, no databases are visible.=20
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=20
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).=20
There have been no changes to the environment in the last eight months,=20
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 restor=
e
> 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=C5=82(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 unlin=
k
>     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 pani=
c
>     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, execut=
e
>     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 whic=
h
>     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. O=
r
>     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 vis=
ible.
>
>     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>
>