PostgreSQL server won't start, corrupt?
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.confWell, 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.confWell, 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: CAFLXUujxL9GBWAVym38+ZMCZmMckP10Z=iGcWE+wV61eQns6XQ@mail.gmail.com
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).
On 9 October 2014 13:56, John R Pierce <pierce@hogranch.com> wrote:
Show quoted text
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--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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...)
On 9 October 2014 14:25, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Show quoted text
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.confNote 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
On 9 October 2014 14:41, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Show quoted text
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
/messages/by-id/44D5F0D7-6179-4D72-8AE9-595D33416048@nasby.net
and further back:
/messages/by-id/20091020143811.379B.52131E4D@oss.ntt.co.jp
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general