Thread: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! (nearly fixed)

On 08/02/2013 02:40 AM, Stephen Brearley wrote:
> Hi Folks (hope I haven’t left anyone out)
>
> SUCCESS (ALMOST)!!
>
>  >>I’ve managed to re-install Postgres **without any install errors** and
>
>  >>I’ve managed to login **without any connection errors**
>
> But..I’m not 100% sure how I did it and..
>
>  >>I can’t access my table data
>
> I think I got Postgres to install properly because of one or more of the
> following, after performing a(nother) uninstall:
>
> 1)I set the registry Postgres data reference to point to the default
> location on the C: drive
>
> 2)I made sure that I didn’t have anything in the \data folder on the D:
> drive, by putting my \data_old well away from my intended data location,
> to avoid any ‘interference’ or confusion by the Postgres during installation
>
> 3)I had a problem performing step 2, because I’d just started a
> reinstall when I thought (2) was a good idea, so I aborted the
> installation, which resulted in a file that just would not move, and so
> forced me to run chkdsk. This found the offending orphaned file, but may
> have fixed something else??
>
> 4)I chose a database password during the install that did not include
> any special characters
>
> Having been able to successfully connect to Postgres using both Navicat
> and pgAdmin, I then moved my \data_old back to the \9.2 folder on the D:
> drive, and modified the registry reference to point to this
> location..however I can’t see my old tables.
>
> I can change my registry reference to point to D:\..\9.2\data or
> D:\..\9.2\data_old without any (apparently) undesirable consequences,
> but I just can’t see my tables in the system, which makes me think I
> need to do more than this. I am unable to rename \data to move all my
> old files wholesale into that folder, as Windows won’t let me, but if I
> move \base which appears to have all the database in it (and is about
> double the size in my old dataset), I get the same result, I can’t see
> my tables.

You can't do that and have it work. \base is just part of the puzzle,
you need the complete \data directory for Postgres to work.

>
> I realise Thomas at least will frown most deeply at these ‘operations’,
> though they often work as a low-tech solution in many programs. Clearly
> the best result would be able to pg_dump from my old data and pg_restore
> it into my new set up, but it seems I am not able to properly see my old
> data in the first place.

The problem(as stated before) is you have two distinct installations of
the Postgres data directory, one on D:\ and one on C:\. In Postgres
parlance the \data directory in each of those locations represents a
database cluster. The procedure you described above for the successful
install set up Postgres to run off the C:\ cluster. All the stuff you
are doing on D:\ will be of no use until you point Postgres at the
cluster on D:\(I don't think changing the registry entry counts).
Unfortunately my lack of knowledge regarding Postgres on Windows means I
will be of no help there. Someone else will need to fill in the procedure.

I have to ask again, how important is the old data and how much is there?

Would it be easier to just reenter it in the new cluster?

>
>  >Any ideas please?
>
> Feeling much more at home in the community –and cheered by so many of
> you coming to help!
>
> Much thanks
>
>    Stephen
>


--
Adrian Klaver
adrian.klaver@gmail.com

>----- Original Message -----
>From: Stephen Brearley

<Snip>

>Having been able to successfully connect to Postgres using both Navicat and
>pgAdmin, I then moved my \data_old back to the \9.2 folder on the D: drive,
>and modified the registry reference to point to this location..however I
>can't see my old tables.

>I can change my registry reference to point to D:\..\9.2\data or
>D:\..\9.2\data_old without any (apparently) undesirable consequences, but I
>just can't see my tables in the system, which makes me think I need to do
>more than this. I am unable to rename \data to move all my old files
>wholesale into that folder, as Windows won't let me, but if I move \base
>which appears to have all the database in it (and is about double the size
>in my old dataset), I get the same result, I can't see my tables.

>I realise Thomas at least will frown most deeply at these 'operations',
>though they often work as a low-tech solution in many programs. Clearly the
>best result would be able to pg_dump from my old data and pg_restore it
>into my new set up, but it seems I am not able to properly see my old data
>in the first place.

You could try:

    1. Changing the registry entry to point to the default data location on
the C: drive
    2. Using pg_ctl to point postgresql to D:\..\9.2\data or
D:\..\9.2\data_old

George

<Snip>

On Aug 3, 2013, at 24:04, BladeOfLight16 <bladeoflight16@gmail.com> wrote:

> My big concern as I've been reading this thread is whether users are cluster specific or installation specific. If
they'recluster specific, he'll need to know credentials for his original cluster anyway to get the data, unless he can
dosome kind of password reset. 

They are cluster specific, as the roles are stored in the database. If you switch between different data directories,
thatmeans you're switching the available roles as well. And their details, such as passwords. You're also switching
betweenconfigurations, such as pg_hba.conf. 

Where it gets confusing a bit here is that there's usually also an OS postgres user, but that user is used to run the
postgresserver/service with limited credentials and not to log into the database. On Windows, apparently postgres is
rununder a standard network service account instead, which serves the same purpose. 

Now, if you connect to the database without specifying a role-name to connect as, the standard tools (psql, pg_dump,
etc.Don't know about pgAdmin) take your user account name and try to use that for the database login role, making it
looklike there's a relation between OS users and database users, but that's not actually the case (although there's an
authenticationoption in pg_hba.conf to require such a relation). 

At least, this is how I think it works. If I'm wrong someone will no doubt correct me ;)

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Stephen Brearley, 02.08.2013 11:40:
> I can change my registry reference to point to D:\..\9.2\data or D:\..\9.2\data_old without any (apparently)
undesirableconsequences,  
> but I just can’t see my tables in the system, which makes me think I need to do more than this.

Why don't you use the official way to re-configure the Windows service and use pg_ctl unregister and pg_ctl register
to make it use the correct data directory. There is no need to manually change the registry.

> I am unable to rename \data to move all my old files wholesale into that folder, as Windows won’t let me,
> but if I move \base which appears to have all the database in it (and is about double the size in my old dataset),
> I get the same result, I can’t see my tables.

I you can't move the directory it means it is still be used/locked by another process. I always use ProcessExplorer (or
ProcessExplorer,both from www.sysinternals.com - now Microsoft) to be indispensable to figure out which process is
lockinga file. 

> I realise Thomas at least will frown most deeply at these ‘operations’, though they often work as a low-tech solution
inmany programs.  

I do change the registry manually myself that's not the point. But I only do it if there is no tool support for it.
Changing the windows service is much easier done through pg_ctl and ensures that nothing is forgotten.

Thomas