PostgreSQL server won't start, corrupt?

[Rob Flemming]

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

I am afraid something was corrupted, especially based on the info. here: <
https://wiki.pscs.co.uk/how_to:postgresql_server_won_t_start_due_to_damaged_log_files
What can I do to restart?

Thank you,
Rob

[Raymond O'Donnell]

Post by Rob Flemming
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!
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.

[John R Pierce]

Post by Raymond O'Donnell

Post by Rob Flemming
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

[Raymond O'Donnell]

Post by John R Pierce

Post by Raymond O'Donnell

Post by Rob Flemming
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.

[John R Pierce]

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

[Rob Flemming]

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

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

Post by John R Pierce

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
--
http://www.postgresql.org/mailpref/pgsql-general

[Adrian Klaver]

Post by Rob Flemming
I think that pg_hba.conf is a bit of a red herring, the first line of
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.

Post by Rob Flemming
(I changed the file to Unix format anyway, just to rule that out).

[Rob Flemming]

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

Post by Rob Flemming

Post by Rob Flemming
I think that pg_hba.conf is a bit of a red herring, the first line of
PDT LOG: invalid connection type ""

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.

Post by Rob Flemming
(I changed the file to Unix format anyway, just to rule that out).

--
Adrian Klaver

[Adrian Klaver]

Post by Rob Flemming
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++.

[Rob Flemming]

Indeed!

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.

Thanks again,
Rob

Post by Rob Flemming
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

[John R Pierce]

Post by Rob Flemming
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

[Adrian Klaver]

Post by John R Pierce

Post by Rob Flemming
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-***@nasby.net

and further back:

http://www.postgresql.org/message-id/***@oss.ntt.co.jp

[John R Pierce]

Post by Rob Flemming
I added a line to the "pg_hba.conf" file and tried to restart the
server, but now it will not run!
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

[Ray Stell]

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?

[Merlin Moncure]

Post by Rob Flemming
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