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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Aug 2, 2013 at 10:18 AM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

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.

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?

If by "modifying the registry," Mr. Brearley means following the procedure
described here on the PostgreSQl
Wiki<http://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows&gt;,
then that actually will change the data directory. That procedure actually
modifies the Windows service command line (stored in the registry), and it
changes the -D argument. (Yes, that appears to have been written against
8.3RC2, but I checked, and it is still applicable to 9.2 if names are
changed appropriately.)

Instead of changing the service, it might be more effective to just stop
the service and manually start PostgreSQL from the command line until the
data can be retrieved. That would allow for somewhat more consistent usage
with other operating systems, making it simpler to help. If he can get
something to connect to that data directory, that would allow him to at
least dump the data so it could be imported into a new, empty cluster.

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

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're cluster specific, he'll need to know credentials for his original cluster anyway to get the data, unless he can do some kind of password reset.

They are cluster specific, as the roles are stored in the database. If you switch between different data directories, that means you're switching the available roles as well. And their details, such as passwords. You're also switching between configurations, 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 postgres server/service with limited credentials and not to log into the database. On Windows, apparently postgres is run under 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 look like there's a relation between OS users and database users, but that's not actually the case (although there's an authentication option 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Aug 3, 2013 at 7:16 AM, Alban Hertroys <haramrae@gmail.com> wrote:

They are cluster specific, as the roles are stored in the database. If you
switch between different data directories, that means you're switching the
available roles as well. And their details, such as passwords. You're also
switching between configurations, 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 postgres server/service
with limited credentials and not to log into the database. On Windows,
apparently postgres is run under 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 look like there's a relation between OS users and
database users, but that's not actually the case (although there's an
authentication option in pg_hba.conf to require such a relation).

Sounds about right. I can definitely confirm that it's run as NETWORK
SERVICE and that psql, by default, tries to connect as the current username.

Anyhow, I'm betting you will need to reset the password in your existing
data directory, Mr. Brearly. First of all, make sure you have an entire
back up of the data directory you want to get data from. (Hopefully, you
have this already from before you were making all these changes.) Then
start PostgreSQL with the data directory with your data. (If this is what
you've configured the service to do within the registry, that's fine.)
Next, follow the instructions in the FAQ:
http://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password._What_can_I_do_to_recover_it.3F.
(Also see this DBA StackExchange question for some Windows specific advice
on reloading the config:
http://dba.stackexchange.com/questions/19643/how-do-i-reset-the-postgres-password-for-postgresql-on-windows.)
See the documentation<http://www.postgresql.org/docs/9.2/static/auth-pg-hba-conf.html&gt;on
configuring the pg_hba.conf file; this file is inside your data
directory somewhere. I suspect the best way to do this is to add a line of
the form "local *database user auth-method*" with *auth-method* as
trust, of course. (Could someone confirm?) Also, do your password reset
from the command line using psql; this eliminates some complexities and
uncertainty regarding PgAdmin in case it doesn't work.

If this works as expected, you'll be able to access your data. Good luck.

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) undesirable consequences,
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 locking a file.

I realise Thomas at least will frown most deeply at these ‘operations’, though they often work as a low-tech solution in many 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks Blade-of-Light (mysterious person)

As you’ll see from my later post, I managed to fix things, largely by understanding that there was a Postgres service I needed to stop before I could make the last change I needed. However, there is a lot of useful stuff in your post, which I will try for another time, should I manage to get stuck again!

Also clarifies some other issues.

Cheers,

Stephen

From: BladeOfLight16 [mailto:bladeoflight16@gmail.com]
Sent: 05 August 2013 03:10
To: Alban Hertroys
Cc: Adrian Klaver; Stephen Brearley; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! (nearly fixed)

On Sat, Aug 3, 2013 at 7:16 AM, Alban Hertroys <haramrae@gmail.com> wrote:

They are cluster specific, as the roles are stored in the database. If you switch between different data directories, that means you're switching the available roles as well. And their details, such as passwords. You're also switching between configurations, 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 postgres server/service with limited credentials and not to log into the database. On Windows, apparently postgres is run under 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 look like there's a relation between OS users and database users, but that's not actually the case (although there's an authentication option in pg_hba.conf to require such a relation).

Sounds about right. I can definitely confirm that it's run as NETWORK SERVICE and that psql, by default, tries to connect as the current username.

Anyhow, I'm betting you will need to reset the password in your existing data directory, Mr. Brearly. First of all, make sure you have an entire back up of the data directory you want to get data from. (Hopefully, you have this already from before you were making all these changes.) Then start PostgreSQL with the data directory with your data. (If this is what you've configured the service to do within the registry, that's fine.) Next, follow the instructions in the FAQ: http://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password._What_can_I_do_to_recover_it.3F. (Also see this DBA StackExchange question for some Windows specific advice on reloading the config: http://dba.stackexchange.com/questions/19643/how-do-i-reset-the-postgres-password-for-postgresql-on-windows.) See the documentation <http://www.postgresql.org/docs/9.2/static/auth-pg-hba-conf.html&gt; on configuring the pg_hba.conf file; this file is inside your data directory somewhere. I suspect the best way to do this is to add a line of the form "local database user auth-method" with auth-method as trust, of course. (Could someone confirm?) Also, do your password reset from the command line using psql; this eliminates some complexities and uncertainty regarding PgAdmin in case it doesn't work.

If this works as expected, you'll be able to access your data. Good luck.

On Mon, Aug 5, 2013 at 2:21 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:

Stephen Brearley, 02.08.2013 11:40:
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.

<snip>

Guess the Wiki<http://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows&gt;needs
to be updated, then.