Thread: Users and multiple server environment

Users and multiple server environment

From
Michael Long
Date:
Hi all,

I now have 2 versions of postgres running on one box (7.3.2 and 8.0b3)
each listening on a different port. When creating a database, user or
schema in 8.0 that exists in 7.3.2 I get an error saying it already
exists. Can I have only one of each of these per server or am I missing
something? If possible I would like to be able to create a test database
on my production server that contains the same schemas and users as the
production database.

tia,
Mike

Re: Users and multiple server environment

From
Tom Lane
Date:
Michael Long <mlong@datalong.com> writes:
> I now have 2 versions of postgres running on one box (7.3.2 and 8.0b3)
> each listening on a different port. When creating a database, user or
> schema in 8.0 that exists in 7.3.2 I get an error saying it already
> exists. Can I have only one of each of these per server or am I missing
> something?

You're missing something.  Check which port you are actually talking to.

I do this all the time --- my development machine standardly has half a
dozen postmasters running on it, one for each major release 7.0-current.
They do not impinge on each other at all.  It helps to build each
release from source so that you can set a different install prefix and
default port number for each one.

            regards, tom lane

Re: Users and multiple server environment

From
Michael Long
Date:
>You're missing something.  Check which port you are actually talking to.
>
>I do this all the time --- my development machine standardly has half a
>dozen postmasters running on it, one for each major release 7.0-current.
>They do not impinge on each other at all.  It helps to build each
>release from source so that you can set a different install prefix and
>default port number for each one.
>
>
>
I had just discovered that I need to specify the port when creating a
database. I had thought that I had read in the documentation that if I
built the server from source and specified the port to use that the
client would be bound to that by default. This does not appear to be the
case. Maybe I misread it. Thanks for the quick response.


Re: Users and multiple server environment

From
Tom Lane
Date:
Michael Long <mlong@datalong.com> writes:
> I had just discovered that I need to specify the port when creating a
> database. I had thought that I had read in the documentation that if I
> built the server from source and specified the port to use that the
> client would be bound to that by default.

The client *that you built along with the server* will bind to that port
by default.  It's not going to somehow magically propagate to other
clients built with other default port numbers.

The default port number on the client side is actually built into libpq,
not psql.  So one possible explanation if you think you've covered the
obvious bases is that you're invoking the correct version-specific psql
programs, but they are all binding to the same libpq.so.  Check
LD_LIBRARY_PATH, ldconfig configuration, etc.

            regards, tom lane

pg_dump w/ 8.0b2 and win2k

From
Micahael Long
Date:
I am trying to dump a schema that was created on the windows version of
postgres. I am getting an error that I don't understand.. I can dump the
entire database with the superuser but dumping one schema doesn't seem
to work.

pg_dump -v -U proporg > temp1.sql

pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined conversions
pg_dump: reading user-defined tables
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  permission denied for
relation pg_ts_dict
pg_dump: The command was: LOCK TABLE public.pg_ts_dict IN ACCESS SHARE MODE
pg_dump: *** aborted because of error

tia,
Mike

Re: Users and multiple server environment

From
Michael Long
Date:
Michael Long wrote:

>
>> You're missing something.  Check which port you are actually talking to.
>>
>> I do this all the time --- my development machine standardly has half a
>> dozen postmasters running on it, one for each major release 7.0-current.
>> They do not impinge on each other at all.  It helps to build each
>> release from source so that you can set a different install prefix and
>> default port number for each one.
>>
Further investigation shows that I was not using the newly built psql
binary. When attempting to use the newly built binary I get an error.
Did I miss something during the configure/make dance. Here is what I get.

 ./psql -U proporg
./psql: relocation error: ./psql: undefined symbol: PQsetErrorVerbosity


Re: Users and multiple server environment

From
Tom Lane
Date:
Michael Long <mlong@datalong.com> writes:
> Further investigation shows that I was not using the newly built psql
> binary. When attempting to use the newly built binary I get an error.
> Did I miss something during the configure/make dance. Here is what I get.

>  ./psql -U proporg
> ./psql: relocation error: ./psql: undefined symbol: PQsetErrorVerbosity

This looks like a 7.4 psql trying to use a pre-7.4 libpq.so.  You need
to take a close look at where you've installed the different libpq
generations and how this matches up to the ldconfig search path.

Personally I like to use rpath-style link specifications to ensure that
a particular psql generation will hook to the corresponding libpq
generation, regardless of any system-wide ldconfig settings.  There are
other ways to do it, but the default Linux mechanism doesn't work very
well for this :-(

            regards, tom lane

Re: Users and multiple server environment

From
Michael Long
Date:
>This looks like a 7.4 psql trying to use a pre-7.4 libpq.so.  You need
>to take a close look at where you've installed the different libpq
>generations and how this matches up to the ldconfig search path.
>
>Personally I like to use rpath-style link specifications to ensure that
>a particular psql generation will hook to the corresponding libpq
>generation, regardless of any system-wide ldconfig settings.  There are
>other ways to do it, but the default Linux mechanism doesn't work very
>well for this :-(
>
>
>
This is where I am getting into areas I don't really understand. I
believe I have 7.3.2 installed along with 8.0b3.

/usr/bin/psql -V
psql (PostgreSQL) 7.3.2

/usr/local/pgsql_8.0b3/bin/psql -V
psql (PostgreSQL) 8.0.0beta3

The when configuring 8.0b3 I thought that I had specified to use the
rpath style link, although now that I look at the string again, maybe not :)

./configure --prefix=/usr/local/pgsql_8.0b3 --disable-rpath
--with-pgport=5480 --with-perl --with-python --with-tcl
--enable-thread-safety --enable-debug --enable-cassert --enable-depend
--with-libraries=/lib:/usr/local/lib/python2.3/lib-dynload

How do I go about sorting this out? If I rerun the istall without the
--disable-rpath switch will this help?



Re: Users and multiple server environment

From
Tom Lane
Date:
Michael Long <mlong@datalong.com> writes:
> The when configuring 8.0b3 I thought that I had specified to use the
> rpath style link, although now that I look at the string again, maybe not :)

> ./configure --prefix=/usr/local/pgsql_8.0b3 --disable-rpath

Looks like not ...

> How do I go about sorting this out? If I rerun the istall without the
> --disable-rpath switch will this help?

I would recommend "make distclean", reconfigure, rebuild whenever you
change configure arguments.  That's the only way to be really sure the
changes propagate to everywhere they need to.

            regards, tom lane

Re: Users and multiple server environment

From
Michael Long
Date:
>I would recommend "make distclean", reconfigure, rebuild whenever you
>change configure arguments.  That's the only way to be really sure the
>changes propagate to everywhere they need to.
>
>
>
Tom, I followed your recommendation and rebuilt postgres after doing
"make distclean". I still get the following

./psql template1
./psql: relocation error: ./psql: undefined symbol: PQsetErrorVerbosity

Is psql linked to the proper libraries?

--This is version 7.3.2
ldd /usr/bin/psql
        libpq.so.3 => /usr/lib/libpq.so.3 (0x40015000)
        libpam.so.0 => /lib/libpam.so.0 (0x4003a000)
        libssl.so.0.9.6 => /usr/lib/libssl.so.0.9.6 (0x40042000)
        libcrypto.so.0.9.6 => /usr/lib/libcrypto.so.0.9.6 (0x40072000)
        libz.so.1 => /lib/libz.so.1 (0x40149000)
        libreadline.so.4 => /lib/libreadline.so.4 (0x40158000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x40185000)
        libresolv.so.2 => /lib/libresolv.so.2 (0x401b6000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x401c9000)
        libdl.so.2 => /lib/libdl.so.2 (0x401de000)
        libm.so.6 => /lib/libm.so.6 (0x401e1000)
        libc.so.6 => /lib/libc.so.6 (0x40203000)
        libncurses.so.5 => /lib/libncurses.so.5 (0x40339000)
        /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)

ldd /usr/local/pgsql_8.0b3/bin/psql
        libpq.so.3 => /usr/lib/libpq.so.3 (0x40025000)
        libz.so.1 => /lib/libz.so.1 (0x40039000)
        libreadline.so.4 => /lib/libreadline.so.4 (0x40049000)
        libcrypt.so.1 => /lib/libcrypt.so.1 (0x40076000)
        libresolv.so.2 => /lib/libresolv.so.2 (0x400a7000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x400b9000)
        libdl.so.2 => /lib/libdl.so.2 (0x400ce000)
        libm.so.6 => /lib/libm.so.6 (0x400d1000)
        libc.so.6 => /lib/libc.so.6 (0x400f4000)
        libssl.so.0.9.6 => /usr/lib/libssl.so.0.9.6 (0x4022a000)
        libcrypto.so.0.9.6 => /usr/lib/libcrypto.so.0.9.6 (0x4025a000)
        libncurses.so.5 => /lib/libncurses.so.5 (0x40331000)
        /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)


Re: Users and multiple server environment

From
dan@kwasar.biz
Date:
Quoting Michael Long <mlong@datalong.com>:

>
> ./psql template1
> ./psql: relocation error: ./psql: undefined symbol: PQsetErrorVerbosity
>
> Is psql linked to the proper libraries?
>
> --This is version 7.3.2

Below looks okay.

> ldd /usr/bin/psql
>         libpq.so.3 => /usr/lib/libpq.so.3 (0x40015000)
>         libpam.so.0 => /lib/libpam.so.0 (0x4003a000)
>         libssl.so.0.9.6 => /usr/lib/libssl.so.0.9.6 (0x40042000)
>         libcrypto.so.0.9.6 => /usr/lib/libcrypto.so.0.9.6 (0x40072000)
>         libz.so.1 => /lib/libz.so.1 (0x40149000)
>         libreadline.so.4 => /lib/libreadline.so.4 (0x40158000)
>         libcrypt.so.1 => /lib/libcrypt.so.1 (0x40185000)
>         libresolv.so.2 => /lib/libresolv.so.2 (0x401b6000)
>         libnsl.so.1 => /lib/libnsl.so.1 (0x401c9000)
>         libdl.so.2 => /lib/libdl.so.2 (0x401de000)
>         libm.so.6 => /lib/libm.so.6 (0x401e1000)
>         libc.so.6 => /lib/libc.so.6 (0x40203000)
>         libncurses.so.5 => /lib/libncurses.so.5 (0x40339000)
>         /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)
>

I've never installed 8.0b3, but I would guess that libpq is also found in
/usr/local. Check for a second copy there and modify LD_LIBRARY_PATH and/or
/etc/ld.so.conf (followed by ldconfig) on Linux to look there first.

> ldd /usr/local/pgsql_8.0b3/bin/psql
>         libpq.so.3 => /usr/lib/libpq.so.3 (0x40025000)
>         libz.so.1 => /lib/libz.so.1 (0x40039000)
>         libreadline.so.4 => /lib/libreadline.so.4 (0x40049000)
>         libcrypt.so.1 => /lib/libcrypt.so.1 (0x40076000)
>         libresolv.so.2 => /lib/libresolv.so.2 (0x400a7000)
>         libnsl.so.1 => /lib/libnsl.so.1 (0x400b9000)
>         libdl.so.2 => /lib/libdl.so.2 (0x400ce000)
>         libm.so.6 => /lib/libm.so.6 (0x400d1000)
>         libc.so.6 => /lib/libc.so.6 (0x400f4000)
>         libssl.so.0.9.6 => /usr/lib/libssl.so.0.9.6 (0x4022a000)
>         libcrypto.so.0.9.6 => /usr/lib/libcrypto.so.0.9.6 (0x4025a000)
>         libncurses.so.5 => /lib/libncurses.so.5 (0x40331000)
>         /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x40000000)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>




----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Re: Users and multiple server environment

From
Tom Lane
Date:
Michael Long <mlong@datalong.com> writes:
> ./psql template1
> ./psql: relocation error: ./psql: undefined symbol: PQsetErrorVerbosity

> Is psql linked to the proper libraries?

> ldd /usr/local/pgsql_8.0b3/bin/psql
>         libpq.so.3 => /usr/lib/libpq.so.3 (0x40025000)

Obviously not :-(.  This should be pointing to /usr/local/pgsql_8.0b3/lib/
so it seems you didn't do the rpath enabling correctly; or maybe your
system is configured in a way that overrides rpath (do you have
LD_LIBRARY_PATH set in your environment?).

I don't have any more advice than that to give; you may need to find a
Linux shared-library guru to help you fix it.

            regards, tom lane

Re: Users and multiple server environment

From
Michael Long
Date:
>Obviously not :-(.  This should be pointing to /usr/local/pgsql_8.0b3/lib/
>so it seems you didn't do the rpath enabling correctly; or maybe your
>system is configured in a way that overrides rpath (do you have
>LD_LIBRARY_PATH set in your environment?).
>
>
>
Yay!! When I set LD_LIBRARY_PATH then things work correctly. Your
patience and help is very much appreciated. If you are ever in the
Boston area I will buy you a beer.

>I don't have any more advice than that to give; you may need to find a
>Linux shared-library guru to help you fix it.
>
>
One final question, I used the following to configure the build. Did I
do something to cause the library to not link properly or do I need to
find a Linux shared-library guru?

./configure --prefix=/usr/local/pgsql_8.0b3 --with-pgport=5480
--with-perl --with-python --with-tcl --enable-thread-safety
--enable-debug --enable-cassert --enable-depend

Re: Users and multiple server environment

From
"Cao Van Khanh"
Date:
Try :
./psql -d template1




-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, October 04, 2004 5:25 AM
To: Michael Long
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Users and multiple server environment

Michael Long <mlong@datalong.com> writes:
> ./psql template1
> ./psql: relocation error: ./psql: undefined symbol:
PQsetErrorVerbosity

> Is psql linked to the proper libraries?

> ldd /usr/local/pgsql_8.0b3/bin/psql
>         libpq.so.3 => /usr/lib/libpq.so.3 (0x40025000)

Obviously not :-(.  This should be pointing to
/usr/local/pgsql_8.0b3/lib/
so it seems you didn't do the rpath enabling correctly; or maybe your
system is configured in a way that overrides rpath (do you have
LD_LIBRARY_PATH set in your environment?).

I don't have any more advice than that to give; you may need to find a
Linux shared-library guru to help you fix it.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match



Altering column type causes unstable server and data loss.

From
Michael Long
Date:
Hi,

If this is not the correct place report this type thing please let me
know and I will post it to the correct location. I was able to
successfully alter a column of type date to varchar(20). However now
when I perform selects on the table I will either get the results as
expected, have the psql connection to the server broken, or have the
server itself hang. The column in question is called "startdate". I have
copied the text from the psql sessions in question below along with
comments preceded with <<. I have also included the relevant entries in
the log file.

The other interesting behavior is that prior to altering the table I
could connect to the server from my Win2k box. After this point I get
the message "FATAL: missing or erroneous pg_hba.conf file." This file
not only exists but has not be modified by me during this time frame.
This is a test server so losing some data is acceptable, but it would be
nice to know what happened and how to resolve it.

Sorry for the long posting but I wanted to be as thorough as possible
when documenting this.

Environment:
    Server OS:  SuSE 9.1
    Database:    Postgres: 8.0b3
    Client OS:  Win2k


<< After altering the table and realizing there was a problem. I
described the table and it looks as expected
 proporg=>\d mortgage;
                                          Table "proporg.mortgage"
   Column    |            Type             |
Modifiers
-------------+-----------------------------+----------------------------------------------------------------
 id          | integer                     | not null default
nextval('proporg.mortgage_id_seq'::text)
 player_id   | integer                     |
 parcel_id   | integer                     | not null
 loannbr     | character varying(20)       |
 lender_id   | integer                     |
 startdate   | character varying(20)       |
 payment     | numeric(16,4)               |
 duedate     | smallint                    |
 years       | smallint                    |
 nbrpayments | smallint                    |
 amount      | numeric(16,4)               |
 rate        | numeric(3,2)                |
 terms       | character varying(255)      |
 pmi         | boolean                     | not null
 escrowins   | boolean                     | not null
 escrowtax   | boolean                     | not null
 createdate  | timestamp without time zone | not null
 modifydate  | timestamp without time zone | not null default
('now'::text)::timestamp(6) without time zone
Indexes:
    "mortgage_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "$3" FOREIGN KEY (lender_id) REFERENCES player(id) ON UPDATE CASCADE
ON DELETE CASCADE
    "$2" FOREIGN KEY (player_id) REFERENCES player(id) ON UPDATE CASCADE
ON DELETE CASCADE
    "$1" FOREIGN KEY (parcel_id) REFERENCES parcel(id) ON UPDATE CASCADE
ON DELETE CASCADE

<< I attempted to alter the column type back to type "date" with no luck

proporg=> alter table mortgage alter column startdate type date;
ERROR:  column "startdate" cannot be cast to type "date"

<< It is possible to retrieve some information from the table

proporg=> select id from mortgage;
 id
----
  2
  8
(2 rows)

<< When attempting to retrieve all records from the table the connection
is broken
<< It is possible to exit psql with "\q" at this
point

proporg=> select * from mortgage;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q

<< After restarting the psql session and attempting to specifically
retrieve data from
<< the id and startdate columns both psql and postmaster hang. The
server becomes
<< very sluggish at this point as if the CPU is racing.  It is necessary
to issue
<< kill -QUIT pid_no to kill the server and then kill pid_no to kill
psql.  The message
<< Terminated does not appear untill after the postmaster is killed.


Welcome to psql 8.0.0beta3, the PostgreSQL interactive terminal.

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

proporg=> select id, startdate from mortgage;
Terminated

<< Log file Entries - The table was altered and then the store proc was
executed. You will notice I left the to_char
<< function in the stor proc by accident after changing the column type
to varchar from date. May this has something
<< to do with it?

ERROR:  function to_char(character varying, "unknown") does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.
CONTEXT:  SQL query " select m.id ,m.parcel_id ,m.player_id ,m.loannbr
,to_char(m.startdate, 'MM/DD/YYYY') as "startdate" ,m.amount from
mortgage m ORDER BY startdate, loannbr"
        PL/pgSQL function "mortgage_list" line 22 at open
LOG:  server process (PID 4440) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:22:30 EDT
LOG:  checkpoint record is at 0/FA094C
LOG:  redo record is at 0/FA094C; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 1866; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  redo starts at 0/FA0988
LOG:  record with zero length at 0/FB2D88
LOG:  redo done at 0/FB2D60
LOG:  database system is ready
LOG:  server process (PID 4557) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:23:57 EDT
LOG:  checkpoint record is at 0/FB2D88
LOG:  redo record is at 0/FB2D88; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/FB2DC4
LOG:  redo is not required
LOG:  database system is ready
ERROR:  function building_list("unknown") does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.
LOG:  server process (PID 4573) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:25:51 EDT
LOG:  checkpoint record is at 0/FB2DC4
LOG:  redo record is at 0/FB2DC4; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/FB2E00
LOG:  redo is not required
LOG:  database system is ready
LOG:  server process (PID 4578) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:28:06 EDT
LOG:  checkpoint record is at 0/FB2E00
LOG:  redo record is at 0/FB2E00; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/FB2E3C
LOG:  redo is not required
LOG:  database system is ready
LOG:  server process (PID 4583) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and
repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-07 23:29:08 EDT
LOG:  checkpoint record is at 0/FB2E3C
LOG:  redo record is at 0/FB2E3C; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  record with zero length at 0/FB2E78
LOG:  redo is not required
LOG:  database system is ready
LOG:  received smart shutdown request
LOG:  shutting down
LOG:  database system is shut down
LOG:  could not bind IPv4 socket: Address already in use
HINT:  Is another postmaster already running on port 5450? If not, wait
a few seconds and retry.
LOG:  database system was shut down at 2004-10-07 23:31:36 EDT
LOG:  checkpoint record is at 0/FB2EB4
LOG:  redo record is at 0/FB2EB4; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 1872; next OID: 41804
LOG:  database system is ready
LOG:  invalid IP mask "trust" in pg_hba.conf file line 71: Name or
service not known
FATAL:  missing or erroneous pg_hba.conf file



Re: Altering column type causes unstable server and data loss.

From
Tom Lane
Date:
Michael Long <mlong@datalong.com> writes:
> If this is not the correct place report this type thing please let me
> know and I will post it to the correct location.

pgsql-bugs is much more appropriate, especially for problem reports
against a beta version.

> I was able to successfully alter a column of type date to
> varchar(20). However now when I perform selects on the table I will
> either get the results as expected, have the psql connection to the
> server broken, or have the server itself hang.

I tried to reproduce this without any success.  Given the inconsistency
of the behavior, I'm wondering about hardware flakiness on your machine.
I could believe that ALTER TABLE has a bug causing it to produce a
corrupt output table, but if the table is corrupt then "select *" should
produce consistently wrong answers or a consistent crash.  In any case,
I made a table matching your table schema, put a few rows in it, did the
ALTER, and was still able to SELECT * from it.  So if there's a bug it
requires additional triggering conditions you haven't mentioned.

> The other interesting behavior is that prior to altering the table I
> could connect to the server from my Win2k box. After this point I get
> the message "FATAL: missing or erroneous pg_hba.conf file." This file
> not only exists but has not be modified by me during this time frame.

So what's in the file now?

            regards, tom lane

Re: Altering column type causes unstable server and data

From
Micahael Long
Date:
>pgsql-bugs is much more appropriate, especially for problem reports
>against a beta version.
>
>
>
I will keep this in mind in the outside chance that I run into another
issue :)

>I tried to reproduce this without any success.  Given the inconsistency
>of the behavior, I'm wondering about hardware flakiness on your machine.
>
>
>
As I get some time I will try to create a scenario to reproduce the
problem. While thinking about the sequence of events that led to this
problem I realized that it will take some controlled testing to isolate
what went wrong. I may be that the alter column type command had nothing
to do with it.

>>the message "FATAL: missing or erroneous pg_hba.conf file." This file
>>not only exists but has not be modified by me during this time frame.
>>
>>
>
>So what's in the file now?
>
>
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

local   all         all                               trust

# IPv4-style local connections:
# host    all         all         127.0.0.1/32          trust
host    all          all         192.168.1.0/24        trust

# IPv6-style local connections:
# host    all         all         ::1/128               trust