Обсуждение: postgres on SuSE 9.1

Dear my friends...

Usually I use MySQL. Now I have to migrate my database
from MySQL to Postgres.

I have created a database successfully with "creatdb"
and a user account successfully.

But I can not access the postgres with pgaccess.

I found this error message :
"
Error trying to connect to database 'custadm' on host
localhost

PostgreSQL error message: Connection to database
failed
could not create socket: ��H����pH����lH����
"
Error in startup script: window ".pgaw:OpenDB" was
deleted before its visibility changed
    while executing
"tkwait visibility .pgaw:OpenDB"
    (procedure "::Connections::openConn" line 40)
    invoked from within
"::Connections::openConn $i 1"
    (procedure "main" line 63)
    invoked from within
"main $argc $argv"
    (file "/usr/bin/pgaccess" line 810)
patrixlinux@patrix:~>
"

Here what I have done
"
patrixlinux@patrix:~> su
Password:
patrix:/localhome/patrixlinux # su postgres
postgres@patrix:/localhome/patrixlinux> cd
postgres@patrix:~> psql custadm
Welcome to psql 7.4.2, the PostgreSQL interactive
terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

custadm=# create user someone with password 'pass_me'
createdb;
CREATE USER
custadm=#
"

Anybody would be so nice to tell me why I can not
access my postgres with pgaccess?

Thank you very much in advance.




__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

Prabu Subroto <prabu_subroto@yahoo.com> wrote:

> Dear my friends...
>
> Usually I use MySQL. Now I have to migrate my database
> from MySQL to Postgres.
>
> I have created a database successfully with "creatdb"
> and a user account successfully.
>
> But I can not access the postgres with pgaccess.
>
> I found this error message :
> "
> Error trying to connect to database 'custadm' on host
> localhost
>
> PostgreSQL error message: Connection to database
> failed
> could not create socket: __H____pH____lH____
> "
> Error in startup script: window ".pgaw:OpenDB" was
> deleted before its visibility changed
>     while executing
> "tkwait visibility .pgaw:OpenDB"
>     (procedure "::Connections::openConn" line 40)
>     invoked from within
> "::Connections::openConn $i 1"
>     (procedure "main" line 63)
>     invoked from within
> "main $argc $argv"
>     (file "/usr/bin/pgaccess" line 810)
> patrixlinux@patrix:~>
> "
>
> Here what I have done
> "
> patrixlinux@patrix:~> su
> Password:
> patrix:/localhome/patrixlinux # su postgres
> postgres@patrix:/localhome/patrixlinux> cd
> postgres@patrix:~> psql custadm
> Welcome to psql 7.4.2, the PostgreSQL interactive
> terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> custadm=# create user someone with password 'pass_me'
> createdb;
> CREATE USER
> custadm=#
> "
>
> Anybody would be so nice to tell me why I can not
> access my postgres with pgaccess?
>
> Thank you very much in advance.

I would guess that Posgres is not listening on an IPv4 socket.  I think pgaccess
always connects via an IPv4 socket.  (may be wrong here ...)

Anyway, check your postgresql.conf file to see if the option to listen on a
tcp/ip port is turned on.  If not, that's almost definately your problem.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Dear Bill

I tried to modify my postgresql.conf file. But
lookslike it is not correct yet.

Please tell me where the mistake.

Here I attached my postgresql.conf.

TIA.

patrix:/var/lib/pgsql/data # ls
.  ..  PG_VERSION  base  global  pg_clog  pg_hba.conf
pg_ident.conf  pg_xlog  postgresql.conf
postmaster.opts
patrix:/var/lib/pgsql/data # vi postgresql.conf
patrix:/var/lib/pgsql/data # cat 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 = 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 = ''
unix_socket_group = ''
unix_socket_permissions = 0777  # octal
virtual_host = '192.168.23.237'         # 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 = true
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 = true
#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 = 'en_US.UTF-8'             # locale for
system error message strings
lc_monetary = 'en_US.UTF-8'             # locale for
monetary formatting
lc_numeric = 'en_US.UTF-8'              # locale for
number formatting
lc_time = 'en_US.UTF-8'                 # 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
patrix:/var/lib/pgsql/data #
--- Bill Moran <wmoran@potentialtech.com> wrote:
> Prabu Subroto <prabu_subroto@yahoo.com> wrote:
>
> > Dear my friends...
> >
> > Usually I use MySQL. Now I have to migrate my
> database
> > from MySQL to Postgres.
> >
> > I have created a database successfully with
> "creatdb"
> > and a user account successfully.
> >
> > But I can not access the postgres with pgaccess.
> >
> > I found this error message :
> > "
> > Error trying to connect to database 'custadm' on
> host
> > localhost
> >
> > PostgreSQL error message: Connection to database
> > failed
> > could not create socket: __H____pH____lH____
> > "
> > Error in startup script: window ".pgaw:OpenDB" was
> > deleted before its visibility changed
> >     while executing
> > "tkwait visibility .pgaw:OpenDB"
> >     (procedure "::Connections::openConn" line 40)
> >     invoked from within
> > "::Connections::openConn $i 1"
> >     (procedure "main" line 63)
> >     invoked from within
> > "main $argc $argv"
> >     (file "/usr/bin/pgaccess" line 810)
> > patrixlinux@patrix:~>
> > "
> >
> > Here what I have done
> > "
> > patrixlinux@patrix:~> su
> > Password:
> > patrix:/localhome/patrixlinux # su postgres
> > postgres@patrix:/localhome/patrixlinux> cd
> > postgres@patrix:~> psql custadm
> > Welcome to psql 7.4.2, the PostgreSQL interactive
> > terminal.
> >
> > Type:  \copyright for distribution terms
> >        \h for help with SQL commands
> >        \? for help on internal slash commands
> >        \g or terminate with semicolon to execute
> query
> >        \q to quit
> >
> > custadm=# create user someone with password
> 'pass_me'
> > createdb;
> > CREATE USER
> > custadm=#
> > "
> >
> > Anybody would be so nice to tell me why I can not
> > access my postgres with pgaccess?
> >
> > Thank you very much in advance.
>
> I would guess that Posgres is not listening on an
> IPv4 socket.  I think pgaccess
> always connects via an IPv4 socket.  (may be wrong
> here ...)
>
> Anyway, check your postgresql.conf file to see if
> the option to listen on a
> tcp/ip port is turned on.  If not, that's almost
> definately your problem.
>
> --
> Bill Moran
> Potential Technologies
> http://www.potentialtech.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
settings





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

Dear my friend, Scott...

It's solved... Thank you very...very much, my
friend...

I still have one question. I also have one station at
home with SuSE 8.2. Why this postgres from SuSE 8.2
does not have "pg_hba.conf"?
and how can I use pgaccess to access into the postgres
server?
And its "postgresql.conf" is also different as the one
from SuSE 9.2.

Thank you very much in advance.
--- Scott Marlowe <smarlowe@qwest.net> wrote:
> On Mon, 2004-06-07 at 10:38, Prabu Subroto wrote:
> > Dear my friends...
> >
> > Usually I use MySQL. Now I have to migrate my
> database
> > from MySQL to Postgres.
> >
> > I have created a database successfully with
> "creatdb"
> > and a user account successfully.
> >
> > But I can not access the postgres with pgaccess.
> >
> > I found this error message :
> > "
> > Error trying to connect to database 'custadm' on
> host
> > localhost
> >
> > PostgreSQL error message: Connection to database
> > failed
> > could not create socket: HpHlH
>
> You need to do two things, likely:
>
> edit $PGDATA/postgresql.conf and turn on
> tcpip_socket = true, then edit
> $PGDATA/pg_hba.conf and add your machine IP with an
> appropriate
> authentication method for your needs there.  The
> line will look
> something like this:
>
> local  all    all             ident   sameuser
>
> or
>
> host  all   all   127.0.0.1    255.0.0.0   trust
>
> or something similar.  Then restart postgresql and
> you're golden.
>
>
>





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

On Wed, 2004-06-09 at 09:39, Prabu Subroto wrote:
> Dear my friend, Scott...
>
> It's solved... Thank you very...very much, my
> friend...
>
> I still have one question. I also have one station at
> home with SuSE 8.2. Why this postgres from SuSE 8.2
> does not have "pg_hba.conf"?
> and how can I use pgaccess to access into the postgres
> server?
> And its "postgresql.conf" is also different as the one
> from SuSE 9.2.

My guess is that it's just located somewhere strange.  As root, enter
"locate pg_hba.conf" and see if that finds it.

Dear Scott...

No, it does not work.

Here is my try under below:
susefujitsu:~ # whoami
root
susefujitsu:~ # locate pg_hba.conf
bash: locate: command not found
susefujitsu:~ #


Please tell me furthermore.
--- Scott Marlowe <smarlowe@qwest.net> wrote:
> On Wed, 2004-06-09 at 09:39, Prabu Subroto wrote:
> > Dear my friend, Scott...
> >
> > It's solved... Thank you very...very much, my
> > friend...
> >
> > I still have one question. I also have one station
> at
> > home with SuSE 8.2. Why this postgres from SuSE
> 8.2
> > does not have "pg_hba.conf"?
> > and how can I use pgaccess to access into the
> postgres
> > server?
> > And its "postgresql.conf" is also different as the
> one
> > from SuSE 9.2.
>
> My guess is that it's just located somewhere
> strange.  As root, enter
> "locate pg_hba.conf" and see if that finds it.
>




__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/

On Wed, 2004-06-09 at 13:09, Prabu Subroto wrote:
> Dear Scott...
>
> No, it does not work.
>
> Here is my try under below:
> susefujitsu:~ # whoami
> root
> susefujitsu:~ # locate pg_hba.conf
> bash: locate: command not found
> susefujitsu:~ #
>

Dang, then I don't have a clue what to do.  Maybe a google search for
pg_hba.conf and suse?

On Wed, Jun 09, 2004 at 01:48:11PM -0600, Scott Marlowe wrote:
> On Wed, 2004-06-09 at 13:09, Prabu Subroto wrote:

> > Here is my try under below:
> > susefujitsu:~ # whoami
> > root
> > susefujitsu:~ # locate pg_hba.conf
> > bash: locate: command not found
> > susefujitsu:~ #
>
> Dang, then I don't have a clue what to do.  Maybe a google search for
> pg_hba.conf and suse?

What about

find / -name pg_hba.conf

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo lógico y coherente. Pero el universo real se halla siempre
un paso más allá de la lógica" (Irulan)

It seems that the command locate is not installed by default under SuSE (I am
using 9.0).

However, that command is in the package "findutils-locate" that comes with the
distribution.  You can install it with Yast.

Bernard

On Wednesday 09 June 2004 16:09, Alvaro Herrera wrote:
> On Wed, Jun 09, 2004 at 01:48:11PM -0600, Scott Marlowe wrote:
> > On Wed, 2004-06-09 at 13:09, Prabu Subroto wrote:
> > > Here is my try under below:
> > > susefujitsu:~ # whoami
> > > root
> > > susefujitsu:~ # locate pg_hba.conf
> > > bash: locate: command not found
> > > susefujitsu:~ #
> >
> > Dang, then I don't have a clue what to do.  Maybe a google search for
> > pg_hba.conf and suse?
>
> What about
>
> find / -name pg_hba.conf

After a bit more of search, pg_hba.conf seems to be in /var/lib/pgsql/data
under SuSE 9.0.

The following was the steps I used to find the location:
1- Look in /etc/rc.d/postgresql for some clue.  It points to /etc/sysconfig/
postgresql
2- Look in /etc/sysconfig/postgresql and another clue is "~postgres/data"
3- Hum! the ~postgres means the home of the user postgres.  Look into /etc/
passwd to find the home of the user postgres and it is /var/lib/pgsql
4- Effectively under /var/lib/pgsql there is a directory called data which
contains the file pg_hba.conf.

I am using 9.0 right now but this was true for previous version of SuSE as
well.  Therefore, I would assume that that it will be in the smae place in
9.1

Bernard


On Wednesday 09 June 2004 17:11, Bernard Clement wrote:
> It seems that the command locate is not installed by default under SuSE (I
> am using 9.0).
>
> However, that command is in the package "findutils-locate" that comes with
> the distribution.  You can install it with Yast.
>
> Bernard
>
> On Wednesday 09 June 2004 16:09, Alvaro Herrera wrote:
> > On Wed, Jun 09, 2004 at 01:48:11PM -0600, Scott Marlowe wrote:
> > > On Wed, 2004-06-09 at 13:09, Prabu Subroto wrote:
> > > > Here is my try under below:
> > > > susefujitsu:~ # whoami
> > > > root
> > > > susefujitsu:~ # locate pg_hba.conf
> > > > bash: locate: command not found
> > > > susefujitsu:~ #
> > >
> > > Dang, then I don't have a clue what to do.  Maybe a google search for
> > > pg_hba.conf and suse?
> >
> > What about
> >
> > find / -name pg_hba.conf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Prabu,

You need to allow Postgres to accept TCP/IP connections.
There are two ways to do that in SuSE :

1. run yast2, go to "System", click on "/etc/sysconfig Editor",
    go to Applications/PostgreSQL/POSTGRES_OPTIONS
    and add  -i parameter, click on Finish ... SuSE will do all the rest.
2. edit file /var/lib/pgsql/data/postgresql.conf and set
    tcp_socket to true. You need to restart postgres after that:
     /etc/init.d/postgresql restart

Regards,
Mikhail


Prabu Subroto wrote:

>Dear my friends...
>
>Usually I use MySQL. Now I have to migrate my database
>from MySQL to Postgres.
>
>I have created a database successfully with "creatdb"
>and a user account successfully.
>
>But I can not access the postgres with pgaccess.
>
>I found this error message :
>"
>Error trying to connect to database 'custadm' on host
>localhost
>
>PostgreSQL error message: Connection to database
>failed
>could not create socket: ©Hÿ¿pHÿ¿lHÿ¿
>"
>Error in startup script: window ".pgaw:OpenDB" was
>deleted before its visibility changed
>    while executing
>"tkwait visibility .pgaw:OpenDB"
>    (procedure "::Connections::openConn" line 40)
>    invoked from within
>"::Connections::openConn $i 1"
>    (procedure "main" line 63)
>    invoked from within
>"main $argc $argv"
>    (file "/usr/bin/pgaccess" line 810)
>patrixlinux@patrix:~>
>"
>
>Here what I have done
>"
>patrixlinux@patrix:~> su
>Password:
>patrix:/localhome/patrixlinux # su postgres
>postgres@patrix:/localhome/patrixlinux> cd
>postgres@patrix:~> psql custadm
>Welcome to psql 7.4.2, the PostgreSQL interactive
>terminal.
>
>Type:  \copyright for distribution terms
>       \h for help with SQL commands
>       \? for help on internal slash commands
>       \g or terminate with semicolon to execute query
>       \q to quit
>
>custadm=# create user someone with password 'pass_me'
>createdb;
>CREATE USER
>custadm=#
>"
>
>Anybody would be so nice to tell me why I can not
>access my postgres with pgaccess?
>
>Thank you very much in advance.
>
>
>
>
>__________________________________
>Do you Yahoo!?
>Friends.  Fun.  Try the all-new Yahoo! Messenger.
>http://messenger.yahoo.com/
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
>