Обсуждение: Postgres 9.2.4 for Windows (Vista) Dell Vostro 400, re-installation failure PLEASE CAN SOMEONE HELP!! (nearly fixed)
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.
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.
>Any ideas please?
Feeling much more at home in the community –and cheered by so many of you coming to help!
Much thanks
Stephen
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>
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, 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'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.
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 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) 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
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 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:
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>