[GENERAL] "show all" command crashes server
Hi folks First time poster here so please extend grace if I don't initially provide what is needed to help. I am running postgresql 8.3.7 on debian lenny (postgresql-8.3_8.3.7-0lenny1_i386.deb). I have three of these servers and generally they run well. On this one server if I use the command "show all" in psql, phpPgAdmin or pgAdmin3 the postgresql server spits the dummy as follows: postg...@theconsole:~$ psql Welcome to psql 8.3.7, 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 postgres=# show all; 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. In the syslog is: Sep 10 23:55:14 theconsole postgres[31118]: [3-2] 0: LOCATION: reaper, postmaster.c:2156 Sep 10 23:55:15 theconsole postgres[31124]: [4-1] [local] [unknown] [unknown] 0: LOG: 08P01: incomplete startup packet Sep 10 23:55:15 theconsole postgres[31124]: [4-2] [local] [unknown] [unknown] 0: LOCATION: ProcessStartupPacket, postmaster.c:1396 Sep 10 23:55:36 theconsole postgres[31118]: [4-1] 0: LOG: 0: server process (PID 31145) was terminated by signal 11: Segmentation fault Sep 10 23:55:36 theconsole postgres[31118]: [4-2] 0: LOCATION: LogChildExit, postmaster.c:2529 Sep 10 23:55:36 theconsole postgres[31118]: [5-1] 0: LOG: 0: terminating any other active server processes Sep 10 23:55:36 theconsole postgres[31118]: [5-2] 0: LOCATION: HandleChildCrash, postmaster.c:2374 Sep 10 23:55:36 theconsole postgres[31118]: [6-1] 0: LOG: 0: all server processes terminated; reinitializing Sep 10 23:55:36 theconsole postgres[31118]: [6-2] 0: LOCATION: PostmasterStateMachine, postmaster.c:2690 Sep 10 23:55:36 theconsole postgres[31146]: [7-1] 0: LOG: 0: database system was interrupted; last known up at 2009-09-10 23:55:14 EST Sep 10 23:55:36 theconsole postgres[31146]: [7-2] 0: LOCATION: StartupXLOG, xlog.c:4836 Sep 10 23:55:36 theconsole postgres[31147]: [7-1] [local] postgres postgres 0: FATAL: 57P03: the database system is in recovery mode Sep 10 23:55:36 theconsole postgres[31147]: [7-2] [local] postgres postgres 0: LOCATION: ProcessStartupPacket, postmaster.c:1648 Sep 10 23:55:36 theconsole postgres[31146]: [8-1] 0: LOG: 0: database system was not properly shut down; automatic recovery in progress Sep 10 23:55:36 theconsole postgres[31146]: [8-2] 0: LOCATION: StartupXLOG, xlog.c:5003 Sep 10 23:55:36 theconsole postgres[31146]: [9-1] 0: LOG: 0: record with zero length at 2A/E734761C Sep 10 23:55:36 theconsole postgres[31146]: [9-2] 0: LOCATION: ReadRecord, xlog.c:3126 Sep 10 23:55:36 theconsole postgres[31146]: [10-1] 0: LOG: 0: redo is not required Sep 10 23:55:36 theconsole postgres[31146]: [10-2] 0: LOCATION: StartupXLOG, xlog.c:5146 Sep 10 23:55:36 theconsole postgres[31150]: [7-1] 0: LOG: 0: autovacuum launcher started Sep 10 23:55:36 theconsole postgres[31150]: [7-2] 0: LOCATION: AutoVacLauncherMain, autovacuum.c:520 Sep 10 23:55:36 theconsole postgres[31118]: [7-1] 0: LOG: 0: database system is ready to accept connections this is 100% repeatable. The database seems to work fine unless this command is run then it is instant death. any help would be appreciated regards Grant
Re: [GENERAL] "show all" command crashes server
On 11/09/2009, at 1:09 AM, Richard Huxton wrote: On this one server if I use the command "show all" in psql, phpPgAdmin or pgAdmin3 the postgresql server spits the dummy as follows: postgres=# show all; server closed the connection unexpectedly Hmm - some modules can provide their own config variables. Do you have the same modules installed in all three servers? How can I determine what modules are installed ? I do know that pgmemcache is installed on this server - but it was there before the problems started and it works ok. Can you "show" individual variables? I did a show all on one of the other servers, created a script to use each of the resulting outputs in a single show statement and ran on the problem server. It ran without a fault. I then took the postgresql.conf file from the problem server, grabbed all the config lines and submitted them one at a time (again with a script) and it also worked fine. regards Grant Maxwell -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "show all" command crashes server
On 11/09/2009, at 8:17 AM, Tom Lane wrote: Grant Maxwell writes: On 11/09/2009, at 1:09 AM, Richard Huxton wrote: Hmm - some modules can provide their own config variables. Do you have the same modules installed in all three servers? How can I determine what modules are installed ? The contents of the local_preload_libraries and shared_preload_libraries parameters would probably be enough ... regards, tom lane On the problem server: shared_preload_libraries = 'pgmemcache' #local_preload_libraries = '' on the others both are emply. For good measure I removed pgmemcache but the problem persists. I have now put it back. regards Grant -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "show all" command crashes server
On 11/09/2009, at 8:36 AM, Tom Lane wrote: Grant Maxwell writes: On the problem server: shared_preload_libraries = 'pgmemcache' #local_preload_libraries = '' on the others both are emply. Sounds like a smoking gun to me. For good measure I removed pgmemcache but the problem persists. Did you restart the postmaster afterwards? shared_preload_libraries is only considered at postmaster start. yep - full restart. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] primary keys
Hi Folks I'm looking for a bit of advice regarding alpha primary keys. I have a table (designed by someone else) that has a numeric primary key and also a unique non-null email address field. The use of the primary key is causing me some headaches in that in multiple database server environment each server allocates a unique number from a range (and that works fine) but when the table is replicated (master-master- master) the exception handling is a bit tricky because each database server may have records that are duplicate at the email address field - with a different primary key number. I don't know why it was done this way but it seems to me that the email addresses are unique, non null and could be used as the primary key. This would make the replication much faster and simpler. Does anyone out there think the change (number to email address as primary key) would be a bad idea ? your thoughts would be appreciated. regards Grant -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] primary keys
On 13/09/2009, at 2:46 AM, Tom Lane wrote: Grant Maxwell writes: I don't know why it was done this way but it seems to me that the email addresses are unique, non null and could be used as the primary key. This would make the replication much faster and simpler. Does anyone out there think the change (number to email address as primary key) would be a bad idea ? One thing that's often considered a useful attribute of a primary key is that it be immutable. In your application, do users ever change their email addresses? If so, what should happen --- is it okay to treat that as effectively a new entry? This would be ok. The table keeps a list of email addresses and some stats on them users may have multiple addresses but would never alter a specific record. Also, if you have any other tables referencing this one via foreign keys, you'd have to have them storing the email address instead of the serial number; it'll be bulkier and address updates will be that much more expensive. so text PKs will be less efficient than numeric ? You can find lots and lots and lots of discussion of this topic if you search the archives for talk about natural versus surrogate keys. Good pointer = thanks Tom regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "show all" command crashes server *** FIXED ***
First of all thanks to those who provided input. This problem is now fixed and I thought I would post this solution so that others might benefit in the future. For the sake of completeness: The error was that if "show all" was run on this postgresql (version 8.3) server, postgres would crash and then recover. Otherwise the server "seemed" healthy The postgres log showed: Sep 10 23:55:36 theconsole postgres[31118]: [4-1] 0: LOG: 0: server process (PID 31145) was terminated by signal 11: Segmentation fault Sep 10 23:55:36 theconsole postgres[31118]: [4-2] 0: LOCATION: LogChildExit, postmaster.c:2529 Sep 10 23:55:36 theconsole postgres[31118]: [5-1] 0: LOG: 0: terminating any other active server processes Sep 10 23:55:36 theconsole postgres[31118]: [5-2] 0: LOCATION: HandleChildCrash, postmaster.c:2374 Sep 10 23:55:36 theconsole postgres[31118]: [6-1] 0: LOG: 0: all server processes terminated; reinitializing Sep 10 23:55:36 theconsole postgres[31118]: [6-2] 0: LOCATION: PostmasterStateMachine, postmaster.c:2690 Sep 10 23:55:36 theconsole postgres[31146]: [7-1] 0: LOG: 0: database system was interrupted; last known up at 2009-09-10 23:55:14 EST Sep 10 23:55:36 theconsole postgres[31146]: [7-2] 0: LOCATION: StartupXLOG, xlog.c:4836 Sep 10 23:55:36 theconsole postgres[31147]: [7-1] [local] postgres postgres 0: FATAL: 57P03: the database system is in recovery mode Sep 10 23:55:36 theconsole postgres[31147]: [7-2] [local] postgres postgres 0: LOCATION: ProcessStartupPacket, postmaster.c:1648 Sep 10 23:55:36 theconsole postgres[31146]: [8-1] 0: LOG: 0: database system was not properly shut down; automatic recovery in progress Sep 10 23:55:36 theconsole postgres[31146]: [8-2] 0: LOCATION: StartupXLOG, xlog.c:5003 Sep 10 23:55:36 theconsole postgres[31146]: [9-1] 0: LOG: 0: record with zero length at 2A/E734761C Sep 10 23:55:36 theconsole postgres[31146]: [9-2] 0: LOCATION: ReadRecord, xlog.c:3126 Sep 10 23:55:36 theconsole postgres[31146]: [10-1] 0: LOG: 0: redo is not required Sep 10 23:55:36 theconsole postgres[31146]: [10-2] 0: LOCATION: StartupXLOG, xlog.c:5146 Sep 10 23:55:36 theconsole postgres[31150]: [7-1] 0: LOG: 0: autovacuum launcher started Sep 10 23:55:36 theconsole postgres[31150]: [7-2] 0: LOCATION: AutoVacLauncherMain, autovacuum.c:520 Sep 10 23:55:36 theconsole postgres[31118]: [7-1] 0: LOG: 0: database system is ready to accept connections SOLUTION: Increase the memory on the server. WHY We had recently ( a month before) had installed splunk on the server. It was running ok The combination of splunk and other tasks running had pushed the memory too close. What we did not notice was that swap had been almost completely consumed - nasty RESULT We shut it all down, increased the memory (double) and voila - problem gone. It goes to show that when hunting problems we should not ignore the basic environmental elements. It also goes to show that our monitoring system was not looking at this relatively new server. (this confession is not an invitation for a spanking) again thanks for the help Grant On 11/09/2009, at 9:09 AM, Grant Maxwell wrote: On 11/09/2009, at 8:36 AM, Tom Lane wrote: Grant Maxwell writes: On the problem server: shared_preload_libraries = 'pgmemcache' #local_preload_libraries = '' on the others both are emply. Sounds like a smoking gun to me. For good measure I removed pgmemcache but the problem persists. Did you restart the postmaster afterwards? shared_preload_libraries is only considered at postmaster start. yep - full restart. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for way to replicate master db to multiple mobile databases
On 25/09/2009, at 12:50 PM, Selena Deckelmann wrote: On Thu, Sep 24, 2009 at 8:09 AM, Cédric Villemain wrote: Le jeudi 24 septembre 2009, Selena Deckelmann a écrit : Hi! On Wed, Sep 23, 2009 at 10:11 AM, Bryan Montgomery > wrote: Hi, I'm looking for a way to replicate am master database to multiple (100+) databases that are taken in to the field. Currently for each laptop we dump and load the tables. However,there is only a small percentage of data that changes on a frequent basis. I've been looking around and come across pyerplica, londiste and bucardo - the documentation on most of these is fairly sparse. It seems that Bucardo may be the best bet - at least initially. Bucardo is a good choice for this usage model because it was originally designed to work over a lossy network connections. yes, but isn't bucardo designed to 2 nodes only ? No, definitely not! You can replicate to any number of systems. And you can group them in whatever groups you'd like. Multi-master (as Joshua said) only works between two nodes, but master->slave can be from a master, to any number of slaves. I use bucardo extensively across multiple sites and with complex replication requirements. It does a great job. regards Grant -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with array query
Hi Folks
According to the 8.3 docs I should be able to write:
select * from tblretrain where 'ms-ap-t2-02c9' NOT IN (owners);
where owners is an array per the following definition
CREATE TABLE tblretrain
(
pkretrainid integer NOT NULL,
mailid integer NOT NULL,
train_to smallint NOT NULL,
owners character varying(1024)[],
bayes_trained boolean DEFAULT false,
contents text NOT NULL,
CONSTRAINT tblretrain_pk PRIMARY KEY (pkretrainid)
)
The problem is that it generates an error:
ERROR: array value must start with "{" or dimension information
** Error **
ERROR: array value must start with "{" or dimension information
SQL state: 22P02
It seems as though postgres is not recognising owners as an array.
Any suggestions please ?
regards
Grant
Re: [GENERAL] problem with array query
Hi Tom The bit I was reading is http://www.postgresql.org/docs/8.3/interactive/arrays.html#AEN6019 __ EXTRACT However, this quickly becomes tedious for large arrays, and is not helpful if the size of the array is uncertain. An alternative method is described in Section 9.20. The above query could be replaced by: SELECT * FROM sal_emp WHERE 1 = ANY (pay_by_quarter); In addition, you could find rows where the array had all values equal to 1 with: SELECT * FROM sal_emp WHERE 1 = ALL (pay_by_quarter); END EXTRACT __ (section 9.20 is the bit that suggests the syntax I was trying) <> ALL is not working. I thought it would fail if the LS does not match every array member of the RS. What I'm trying to do is find every record where "my name" is not in the array. So I tried <> ANY and also <> ALL and both returned an empty row set. regards Grant On 28/09/2009, at 11:42 AM, Tom Lane wrote: Grant Maxwell writes: According to the 8.3 docs I should be able to write: select * from tblretrain where 'ms-ap-t2-02c9' NOT IN (owners); where owners is an array per the following definition owners character varying(1024)[], No, what you can write is "<> ALL", not NOT IN. It seems as though postgres is not recognising owners as an array. It's trying to parse the literal as an array so that it can do a plain equality comparison against the owners column. You probably read the part of the docs where it says that x NOT IN (SELECT ...) is equivalent to x <> ALL (SELECT ...). Which is true, but it has nothing to do with the non-sub-SELECT syntax. Without a sub-SELECT, we have two cases: x NOT IN (y,z,...) expects x,y,z to all be the same type. x <> ALL (y) expects y to be an array of x's type. Got it? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
