postgres Installaton unter Debian, Zugriff über pgadmin, Win

hi,

habe mit
apt- get update und apt- get install postgresql
postgres auf debian installiert.
Nun habe ich mit Windows versucht (pgAdmin) auf diesen Server, 192.168.3.68 zuzugreifen und dann mit dem Client 192.168.3.64 Datenbanken zu erstellen.
Leider kommt immer die Fehlermeldung “Server does not listen”, wieso?

danke,

Sascha

hi,

lauscht dein postgresql server überhaupt auf der externen adresse? prüfen am besten mit “netstat -lap |grep LISTEN”.

wenn ja firewall aufgemacht?

cut b.

hi,

danke für die Antwort, er lauscht nicht, wie bringe ich ihn zum lauschen?

danke!

Ihn starten? Das Ding sollte standardmäßig auf tcp hören. Zur Sicherheit nochmal in der /etc/postgresql/postgresql.conf prüfen:

tcpip_socket = true

Und setze in der pg_hba.conf die Berechtigungen für die entsprechenden IP-Adressen richtig. Default darf nur localhost sich anmelden.

hi,

er lauscht immer noch nicht, ich habe den Wert bei tcp_ip_socket auf true gesetzt, war false, geht noch nicht, hierm mal die Dateien:

# -----------------------------
# 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 = true
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 = '/var/run/postgresql'
#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 = true
#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 = 'de_DE@euro'		# locale for system error message strings
lc_monetary = 'de_DE@euro'		# locale for monetary formatting
lc_numeric = 'de_DE@euro'		# locale for number formatting
lc_time = 'de_DE@euro'			# 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

die pg_hba.conf sieht so aus:

# 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 (IPv4) 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.


# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (autovacuum, daily cronjob, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

local   all         all                                             ident sameuser
# IPv4-style local connections:
host    all         all         127.0.0.1         255.255.255.255   md5
host    all         all         192.168.3.0       255.255.255.0     md5
# IPv6-style local connections:
host    all         all         ::1               ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff        md5

Netstat gibt folgendes aus:
tcp 0 0*:ipp : LISTEN 2673/cupsd

woran liegt es, dass er nicht lauscht?

danke!

schau mal was ganz oben steht:

Comments are introduced with ‘#’ anywhere on a line.

das sollte deine frage wieso er nicht lauscht beantworten… :wink:

cu tb

so, jetzt geht es- danke!

bekomme jetzt die Fehlermeldung, dass das Passwort nicht stimmt, habe aber bei der Installation keines mitangegeben und in der postgresql.conf steht auch keine Zeile dafür- wo muss ich den Benutzer root und weitere konfigurieren?

danke!

hi,

nicht root! postgres ist hier der super user! wie schaust du nicht einfach mal in der handbuch? du findest es unter http://www.postgresql.org. die benutzung und sogar das downloaden ist kostenlos!

alternativ gibt es auch die gedruckte version (wenn auch schon etwas älter) im shop: http://www.pg-forum.de/shop.html?id=251&action=detail&asin=3826613376

cu tb.

hi,

also createuser -P test funktioniert nicht, bekomme die Fehlermeldung, dass root nicht existiert, wo lege ich root an?

danke!

Lies doch bitte das Manual richtig. Postgres-Benutzer können nur durch einen postgres-Benutzer angelegt werden, der auch die Berechtigung dazu hat. Der einzigste Benutzer, der das für Postgres nach der Installation darf, ist…du wirst es erraten: postgres.

Also: als root anmelden, oder su und dann root-Passwort
dann: su postgres (switcht auf den systemuser postgres)
dann: createuser -P
dann: kannste angeben, ob du neue DBs anlegen darfst und neue Nutzer anlegen darfst.

danke!

cu