LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)
-   -   postgresql-server wont run for obscure reasons... (https://www.linuxquestions.org/questions/linux-software-2/postgresql-server-wont-run-for-obscure-reasons-301284/)

supersucker 03-13-2005 08:54 PM

postgresql-server wont run for obscure reasons...
 
hi everybody,

i have a problem which really drives me mad:

i am developing an application using the postgresql 7.4 database on fedora core 3, and i access the database via webmin or via psql which worked fine until yesterday.

somehow i messed up the server (and i really have no idea how....), when i now try to access my database via webmin i get:

DBI connect failed : FATAL: missing or erroneous pg_hba.conf-Datei

if i try to access the database via psql in the console i get the same error-message....

so i reduced my pg_hba.conf to only one single entry:

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all trust

which is the default configuration allowing everybody to access every database on localhost,
i still get the same error, so i thought maybe the entry in the postgresql.conf under /etc/webmin/postgresql/ pointing to the pg_hba.conf is wrong, but it isnt!
here is my postgresql.conf under the webmin directory mentioned above:

sameunix=1
date_subs=0
perpage=25
stop_cmd=if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb stop; else /etc/rc.d/init.d/postgresql stop; fi
psql=/usr/bin/psql
pid_file=/var/run/postmaster.pid
hba_conf=/var/lib/pgsql/data/pg_hba.conf
setup_cmd=if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql start; fi
user=postgres
nodbi=0
start_cmd=if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql start; fi
repository=/home/db_repository
dump_cmd=/usr/bin/pg_dump
access=*: *
rstr_cmd=/usr/bin/pg_restore
login=postgres
basedb=template1
add_mode=1
blob_mode=0
plib=
port=5432
host=

i also erased the complete postgresql-packages and reinstalled everything successfully via yum, but that didnt solve the problem.
everything seems fine, i am out of ideas what to do now...........
has anybody an idea what could have gone wrong? this is the simplest configuration and it should work..........
if not, is there a way to completely reset my postgresql-system, so that i can access it from the start by typing "createdb" , "adduser" and so on? erasing the packages and reinstalling does not seem to overwrite some (or all?) config-files , if nobody knows a solution to this, maybe somebody knows a way how to manage a completely reset of the server with new default-config-files?

many thanks in advance!!

CroMagnon 03-14-2005 01:42 AM

Are there any errors regarding pg_hba.conf in your postgres log file?

supersucker 03-14-2005 07:19 AM

ah yeah,

thats the next problem, i dont know / find that logfile ..............(sorry, i am really a newbie to fedora / postgresql)

i have googled / searched the documentation for a helpfull entry saying : " the server logfile is located under....."
but found nothing like that, ok, so i looked up the file "webmin.log" under /etc/webmin/ to see wether webmin reported an error
when trying to start the postgresql-server, but nothing.....
ok, so i looked up the files in pg_xlog and in pg_clog under /var/lib/pgsql/data but when i open them in vi or nedit or whatever
its binary data......
also, i looked at the postgresql.conf under /var/lib/pgsql/data whether there would be an entry where i could specify something like
"save logfile to" , but again nothing..........

the postgresql-docs didnt help me as well, could somebody please tell me where to find that logfile? or do i have to turn logging on explicitly
because the stderr goes to /dev/null or something like that? if so, where? i didnt found anything in the postgresql.conf..........

thx for helping so far!

CroMagnon 03-14-2005 07:42 AM

Have a look under /var/log... it might be in that directory, or a postgres subdirectory.

supersucker 03-14-2005 08:45 AM

hmm,

i did that, no log file for postgresql under /var/log/, no log file under every directory i found via "find / -name postgresql"
or "find / -name pgsql"
i found some homepages saying "in case of an error, the server creates a log file called server.log" , but that file doesnt exsist on
my machine..............
i just replaced all config-files ( postgresql.conf , pg_hba.conf) by the sample default config files and still get the same error........

below my pg_hba.conf

# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the PostgreSQL Administrator's Guide, chapter "Client
# Authentication" for a complete description. A short synopsis
# follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of seven forms:
#
# local DATABASE USER METHOD [OPTION]
# host DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION]
# hostssl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION]
# hostnossl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION]
# host DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION]
# hostssl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION]
# hostnossl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION]
#
# (The uppercase quantities should be replaced by actual values.)
# The first field is the connection type: "local" is a Unix-domain socket,
# "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an
# SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.
# DATABASE can be "all", "sameuser", "samegroup", a database name (or
# a comma-separated list thereof), or a file name prefixed with "@".
# USER can be "all", an actual user name or a group name prefixed with
# "+" or a list containing either. IP-ADDRESS and IP-MASK specify the
# set of hosts the record matches. CIDR-MASK is an integer between 0
# and 32 (IPv6) or 128(IPv6) inclusive, that specifies the number of
# significant bits in the mask, so an IPv4 CIDR-MASK of 8 is equivalent
# to an IP-MASK of 255.0.0.0, and an IPv6 CIDR-MASK of 64 is equivalent
# to an IP-MASK of ffff:ffff:ffff:ffff::. METHOD can be "trust", "reject",
# "md5", "crypt", "password", "krb4", "krb5", "ident", or "pam". Note
# that "password" uses clear-text passwords; "md5" is preferred for
# encrypted passwords. OPTION is the ident map or the name of the PAM
# service.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".

# Put your actual configuration here
# ----------------------------------
#
# CAUTION: The default configuration allows any local user to connect
# using any PostgreSQL user name, including the superuser, over either
# Unix-domain sockets or TCP/IP. If you are on a multiple-user
# machine, the default configuration is probably too liberal for you.
# Change it to use something other than "trust" authentication.
#
# If you want to allow non-local connections, you need to add more
# "host" records. Also, remember TCP/IP connections are only enabled
# if you enable "tcpip_socket" in postgresql.conf.

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

# IPv4-style local connections:
#host all all 127.0.0.1 255.255.255.255 trust
# IPv6-style local connections:
#host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust

# Using sockets credentials for improved security. Not available everywhere,
# but works on Linux, *BSD (and probably some others)

local all all ident sameuser


and the postgresql.conf:

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".


#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

#tcpip_socket = false
#max_connections = 100
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = '' # what interface to listen on; defaults to any
#rendezvous_name = '' # defaults to the computer name

# - Security & Authentication -

#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

#shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
#sort_mem = 1024 # min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB

# - Free Space Map -

#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
#preload_libraries = ''


#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

#fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or open_datasync
#wal_buffers = 8 # min 4, 8KB each

# - Checkpoints -

#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000


#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------

# - Planner Method Enabling -

#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true

# - Planner Cost Constants -

#effective_cache_size = 1000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)

# - Genetic Query Optimizer -

#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_selection_bias = 2.0 # range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs


#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Syslog -

#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -

#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error

#log_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
# panic

#log_error_verbosity = default # terse, default, or verbose messages

#log_min_error_statement = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)

#log_min_duration_statement = -1 # Log all statements whose
# execution time exceeds the value, in
# milliseconds. Zero prints all queries.
# Minus-one disables.

#silent_mode = false # DO NOT USE without Syslog!

# - What to Log -

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
#log_duration = false
#log_pid = false
#log_statement = false
#log_timestamp = false
#log_hostname = false
#log_source_port = false


#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Statistics Monitoring -

#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false

# - Query/Index Statistics Collector -

#stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true


#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds

# - Locale and Formatting -

#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding

# These settings are initialized by initdb -- they may be changed
#lc_messages = 'C' # locale for system error message strings
#lc_monetary = 'C' # locale for monetary formatting
#lc_numeric = 'C' # locale for number formatting
#lc_time = 'C' # locale for time formatting

# - Other Defaults -

#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10


#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------

#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each


#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------

# - Previous Postgres Versions -

#add_missing_from = true
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true

# - Other Platforms & Clients -

#transform_null_equals = false



any more ideas??

thx for the help so far.........

CroMagnon 03-14-2005 03:49 PM

OK. try taking a look in /var/log/messages or /var/log/syslog instead.

Or just search all the logs:
grep -ri postg /var/log/*

PriceComparison 03-29-2005 02:54 PM

pg_hba.conf error
 
The file pg_hba.conf is in the data directory.
It is possible you have initialized a different data directory and
therefore you are changing the wrong pg_hba.conf file.

Where is your data directory?

Or do a locate:

locate pg_hba.conf

to see all the locations where this exists.

If this is not the issue you may want to try loading a refresh/default pg_hba.conf.
If you do not have it let me know I can email to you.

Andrew (AT) PriceComparison.com
http://www.PriceComparison.com
Online Shopping Starts Here!

rnturn 03-29-2005 07:00 PM

Quote:

ah yeah,

thats the next problem, i dont know / find that logfile

Take a look at the script that's starting PostgreSQL. In your case, that appears to be:

/etc/rc.d/init.d/postgresql

You can start postmaster to either use syslogd to handle the logging (at least you used to; v8 makes no mention of it any more) or you can merely redirect postmaster's output to a log file within the script. I've seen some folks keeping that log under /tmp because that's where the sample script they were using put it. Check that startup script to see where the poastmaster output is being sent.

The PostgreSQL account that you use to run postmaster ought to have some envirnoment variables set that tell you where to find the correct pg+AF8-hba.conf file (i.e. +ACQ-PGDATA).

Also, try passing "-d 5" as an option to the postmaster command in the startup script. That turns on the maximum amount of debugging messages and might make it easier to see if it'll be more specific about what it's griping about in your config file.

Good luck...


All times are GMT -5. The time now is 09:26 PM.