Обсуждение: PostgreSQL server won't start, corrupt?

On 09/10/2014 21:30, Rob Flemming wrote:
> Hi I've had a a PostgreSQL 9.3 server running on Windows 7 for
> several months.
>
> While trying to diagnose a remote computer's connection problem
> (through ArcGIS, grrr), i was clicking through the postgres (data)
> directory  and opened a couple of files using Notepad but promptly
> closed them without making any changes.  e.g.
> "C:\postgres\pg_clog\0000" and "C:\postgres\pg_stat\global.stat".
>
> I added a line to the "pg_hba.conf" file and tried to restart the
> server, but now it will not run!
>
> The messages in the log file are: 2014-10-09 12:59:40 PDT LOG:
> invalid connection type "" 2014-10-09 12:59:40 PDT CONTEXT:  line 1
> of configuration file "C:/postgres/pg_hba.conf" 2014-10-09 12:59:40
> PDT FATAL:  could not load pg_hba.conf

Well, the error messages seem to indicate that there's a problem with
pg_hba.conf, so I'd start by reversing whatever you did there and seeing
if that fixes it.

I'd also use something other than Notepad.... it has a hard time with
non-Windows line endings. Notepad++ is a very good free text editor, for
example.

Hope this helps,

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 10/9/2014 1:30 PM, Rob Flemming wrote:
> I added a line to the "pg_hba.conf" file and tried to restart the
> server, but now it will not run!
>
> The messages in the log file are:
>     2014-10-09 12:59:40 PDT LOG:  invalid connection type ""
>     2014-10-09 12:59:40 PDT CONTEXT:  line 1 of configuration file
> "C:/postgres/pg_hba.conf"
>     2014-10-09 12:59:40 PDT FATAL:  could not load pg_hba.conf

it appears your problem is pg_hba.conf, NOT the logfiles. something you
put on line 1 of that file is causing it to choke.





--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 10/9/2014 1:41 PM, Raymond O'Donnell wrote:
>> >The messages in the log file are: 2014-10-09 12:59:40 PDT LOG:
>> >invalid connection type "" 2014-10-09 12:59:40 PDT CONTEXT:  line 1
>> >of configuration file "C:/postgres/pg_hba.conf" 2014-10-09 12:59:40
>> >PDT FATAL:  could not load pg_hba.conf
> Well, the error messages seem to indicate that there's a problem with
> pg_hba.conf, so I'd start by reversing whatever you did there and seeing
> if that fixes it.

OH!     Notepad probably saved it as Windows Unicode, which is UTF16,
and put a utf16 byte marker in the first bytes of the file.

Try using notepad++ (open source freeware editor for windows) and make
sure you save it as UTF8 or plain ascii, NOT as UTF16 aka unicode.


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Oct 9, 2014, at 4:30 PM, Rob Flemming <robf.42@gmail.com> wrote:
>     2014-10-09 12:59:40 PDT CONTEXT:  line 1 of configuration file "C:/postgres/pg_hba.conf"
>     2014-10-09 12:59:40 PDT FATAL:  could not load pg_hba.conf

what’s on line 1 of pg_hba.conf?

On 09/10/2014 21:44, John R Pierce wrote:
> On 10/9/2014 1:41 PM, Raymond O'Donnell wrote:
>>> >The messages in the log file are: 2014-10-09 12:59:40 PDT LOG:
>>> >invalid connection type "" 2014-10-09 12:59:40 PDT CONTEXT:  line 1
>>> >of configuration file "C:/postgres/pg_hba.conf" 2014-10-09 12:59:40
>>> >PDT FATAL:  could not load pg_hba.conf
>> Well, the error messages seem to indicate that there's a problem with
>> pg_hba.conf, so I'd start by reversing whatever you did there and seeing
>> if that fixes it.
>
> OH!     Notepad probably saved it as Windows Unicode, which is UTF16,
> and put a utf16 byte marker in the first bytes of the file.

I can see the lightbulb flashing on from this side of the Atlantic! :-)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 10/9/2014 1:52 PM, Rob Flemming wrote:
> I have just installed Notepad++.
> The file was UTF8, with no special characters...

ok, whats the first line ?

hmm.   the file may also have to be in "unix" format, not "dos", as DOS
format puts <CR> in front of each new-line.   thats under Edit -> EOL
Conversion in notepad++






--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 10/09/2014 02:16 PM, Rob Flemming wrote:
> I think that pg_hba.conf is a bit of a red herring, the first line of
> the log file is probably more important (?):
> PDT LOG:  invalid connection type ""

No pg_hba.conf is where the issue is. From your original post:

2014-10-09 12:59:40 PDT LOG:  invalid connection type ""
2014-10-09 12:59:40 PDT CONTEXT:  line 1 of configuration file
"C:/postgres/pg_hba.conf"
2014-10-09 12:59:40 PDT FATAL:  could not load pg_hba.conf

Note the CONTEXT line that places the error in pg_hba.conf. Also the
next line that shows that pg_hba.conf is not loaded. Somehow you have
entered something on the first line that Postgres thinks is a connection
specification.

>
> (I changed the file to Unix format anyway, just to rule that out).
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/09/2014 02:37 PM, Rob Flemming wrote:
> You were right!  (Of course, you knew that.)
>
> I typed a new pg_hba.conf from scratch without all the comments, i.e.
> only the connection strings, it worked.
>
> Thank you 'Community'!
>
> (Doh!  My kids think I'm so smart because I work on computers, some day
> they'll discover the truth. Now to spend the afternoon backing up that
> data...)
>

The moral to this story is never let Notepad near anything you care
about. If you have to use one of the MS apps to edit a text file use
Wordpad, otherwise stick with Notepad++.

>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/9/2014 2:50 PM, Rob Flemming wrote:
>
> I'd also note that I was unable to fix the file with Notepad++ because
> I was saving it with
> "Encoding > Encode in UTF-8"
> rather than
> ">Encode in UTF-8 without BOM"
> The "BOM" was the bit causing the trouble at the start of the file.

sigh.   postgres should probably be tolerant of said silliness.  of
course, a BOM in a UTF8 file is nonsense.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 10/09/2014 03:00 PM, John R Pierce wrote:
> On 10/9/2014 2:50 PM, Rob Flemming wrote:
>>
>> I'd also note that I was unable to fix the file with Notepad++ because
>> I was saving it with
>> "Encoding > Encode in UTF-8"
>> rather than
>> ">Encode in UTF-8 without BOM"
>> The "BOM" was the bit causing the trouble at the start of the file.
>
> sigh.   postgres should probably be tolerant of said silliness.  of
> course, a BOM in a UTF8 file is nonsense.
>

For the reasons why this not so, see:

http://www.postgresql.org/message-id/44D5F0D7-6179-4D72-8AE9-595D33416048@nasby.net

and further back:

http://www.postgresql.org/message-id/20091020143811.379B.52131E4D@oss.ntt.co.jp

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Oct 9, 2014 at 3:30 PM, Rob Flemming <robf.42@gmail.com> wrote:
> Hi I've had a a PostgreSQL 9.3 server running on Windows 7 for several
> months.
>
> While trying to diagnose a remote computer's connection problem (through
> ArcGIS, grrr), i was clicking through the postgres (data) directory  and
> opened a couple of files using Notepad but promptly closed them without
> making any changes.  e.g. "C:\postgres\pg_clog\0000" and
> "C:\postgres\pg_stat\global.stat".

Any change to clog files would indeed corrupt the database.  It sounds
to me like you have a mundane pg_hba.conf problem -- no big deal!  Fix
it and start taking regular backups.

merlin