Re: Windows 10 Pro issue - Mailing list pgsql-general

On 02/14/2018 09:28 AM, Dale Seaburg wrote:

CCing list so more eyes can see this


>> Is the pg_hba.conf file actually there?
> 
> Yes, the pg_hba.conf is in the "proper path" - C:\Program Files 
> (x86)\PostgreSQL\8.4\data
> Footnote: at the user level or system level I do not see any environment 
> variables (EV) pointing to the above path.
> Not sure whether an EV is even needed.
> 
>>
>> If it is there have the permissions changed on it or the directories 
>> above?
>>
> 
> As far as I can tell permissions are OK to the above directory, and 
> .conf files.

You might to take a look at this post if you have not already:

https://www.postgresql.org/message-id/14113c03-cbd9-584a-9ec1-6412d5606404%40tpg.com.au

> 
> More importantly, how do I go about building a backup of the data, so I 
> can do an upgrade to a much later release, like 9.6 or so without having 
> the postgres service running?  Hate to sound so ignorant, but I've not 
> had to "travel down this road" before.
> What would be the best approach to upgrading, without the '.\postgres' 
> service running?

Important, before you do any of this for real I would plan on creating a 
test area or doing this on another similar machine to get the process down.

Migrating from 8.4 to 9.6 is a major version to major version upgrade. 
It also represents a sizable number of changes. Not sure how much you 
know about what is going on in the database. Still it would not hurt to 
go here:

https://www.postgresql.org/docs/10/static/release.html

A versioning number note, prior to latest version 10 Postgres used a 
three number system X.Y.z where X and Y represented a major upgrade and 
z represented bug fix/security fix releases. With 10 the system changed 
to X.y where X is the major version and y is bug fix/security fix. I 
mention that because when going through the release notes you really 
only need to concentrate on the notes for X.Y(<10) and X(>=10). So for 
the next version after 8.4, which is 9.0:

https://www.postgresql.org/docs/10/static/release-9-0.html

You want to concentrate on:

E.136.2. Migration to Version 9.0
https://www.postgresql.org/docs/10/static/release-9-0.html#id-1.11.6.140.4

E.136.3. Changes
https://www.postgresql.org/docs/10/static/release-9-0.html#id-1.11.6.140.5

You will not actually be migrating to 9.0 but the above will tell what 
changed relative to 8.4. Repeat for the other major releases to see what 
changed relative to the prior release. What is important to remember is 
that the changes are cumulative so the current latest version 10.2 will 
have the preceding changes. What you are looking for is any changes that 
may impact your code in the server or in client programs using the server.


> 
> BTW, I have done backups before using pgAdmin, but, not the psql tool.

Now this is where someone that is more familiar with running Postgres on 
Windows will need to chime in. The best practices is to install the new 
version in parallel with the old and use the pg_dump from the new 
version to dump the database from the old version, as pg_dump is 
backwards compatible not forward compatible. I just have not done that 
on Windows so I am not going to be of much help. To help out with 
answering this it would be helpful know where you got the Postgres 
program from and how you installed it. It would be also good to know 
what amount of data you are dealing with.


> 
> Dale Seaburg


-- 
Adrian Klaver
adrian.klaver@aklaver.com