[GENERAL] Bitrock XML Source
Greetings, I am trying to get the source XML file for the PostgreSQL installer. This is the BitRock InstallBuilder XML file. Can anyone direct me to the proper place to obtain this installer file? Thank you, Greg
Re: [GENERAL] Postgres process is crashing continously in 9.1.1
We can understand the difference in shared buffer size as the Windows PC has
2GB of RAM and the board has 256MB of RAM.
So please let us know if this shared buffer parameter has any relation to the
problem we are facing.
Thanks and Regards
Jayashankar
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jayashankar K B
Sent: Tuesday, May 22, 2012 11:27 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Postgres process is crashing continously in 9.1.1
Hi,
We are using Postgres 9.1.1 on a board with Coldfire controller.
The postgres processes are crashing and restarting upon executing a particular
instruction and it keeps repeating. Even when we tried with Postgres 9.1.3,
same problem happens.
It works fine until the FINANCIALTRANSACTIONID reaches 1000.
But the same setup is working fine on a windows PC. We have tried to compare
the configuration differences between windows PC and the board and found that
only difference is the Shared Buffers which is 32 on the PC and 24 on the board.
I am pasting the server log from the board here.
The line highlighted in yellow is the instruction which is causing the crash.
Please let us know why this crash is happening and how we can fix it.
LOG: redo starts at 0/D9B75B4
LOG: record with zero length at 0/D9BBE5C
LOG: redo done at 0/D9BBE22
LOG: last completed transaction was at log time 2012-05-22 02:22:26.641488+00
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
ERROR: duplicate key value violates unique constraint
financialtransaction_pkey
DETAIL: Key (financialtransactionid)=(1004) already exists.
STATEMENT: Insert into FINANCIALTRANSACTION
(ATTENDANT,ENGINEHOUR,RECEIPTPRINTED,FINANCIALTRANSACTIONID) values
('0','0.0','0','1004')
LOG: server process (PID 4016) was terminated by signal 11: Segmentation fault
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.
FATAL: poll() failed in statistics collector: Unknown error 516
LOG: statistics collector process (PID 3962) exited with exit code 1
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2012-05-22 02:22:29 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: consistent recovery state reached at 0/D9BBEAA
LOG: redo starts at 0/D9BBEAA
LOG: record with zero length at 0/D9C07FA
LOG: redo done at 0/D9C07C0
LOG: last completed transaction was at log time 2012-05-22 02:23:05.372245+00
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
ERROR: duplicate key value violates unique constraint
financialtransaction_pkey
DETAIL: Key (financialtransactionid)=(1004) already exists.
STATEMENT: Insert into FINANCIALTRANSACTION
(ATTENDANT,ENGINEHOUR,RECEIPTPRINTED,FINANCIALTRANSACTIONID) values
('0','0.0','0','1004')
LOG: server process (PID 4098) was terminated by signal 11: Segmentation fault
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.
FATAL: poll() failed in statistics collector: Unknown error 516
LOG: statistics collector process (PID 4035) exited with exit code 1
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2012-05-22 02:23:08 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: consistent recovery state reached at 0/D9C0848
LOG: redo starts at 0/D9C0848
LOG: record with zero length at 0/D9C5218
LOG: redo done at 0/D9C51DE
LOG: last completed transaction was at log time 2012-05-22 02:23:49.659502+00
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
Thanks and Regards
Jayashankar
Larsen Toubro Limited
www.larsentoubro.com
This Email may contain confidential or privileged information for the intended
recipient (s). If you are not the intended recipient, please do not use or
disseminate the information, notify the sender and delete it from your system.
Earth Day. Every Day.
Larsen Toubro Limited
www.larsentoubro.com
This Email may contain confidential or privileged information for the intended
recipient (s). If you are not the intended recipient, please do not use or
disseminate the information,
Re: [GENERAL] Bitrock XML Source
On 05/21/12 11:03 PM, Greg Simpson wrote: I am trying to get the source XML file for the PostgreSQL installer. This is the BitRock InstallBuilder XML file. Can anyone direct me to the proper place to obtain this installer file? the Windows installer? thats produced by EnterpriseDB, and I don't think they've open sourced it. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Postgres process is crashing continously in 9.1.1
On 05/21/12 11:05 PM, Jayashankar K B wrote: board with Coldfire controller. what is this board? Coldfire is the embedded 68k-like Freescale processor? what operating system is this under? what sort of storage does this embedded system use for the database? telling us FINANCIALWHATEVERID 1000 doesn't really do us much good since we have no idea what your database looks like, or what your code is doing. the log seems to indicate there was a constraint violation just before the exception hit. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Postgres process is crashing continously in 9.1.1
Yes the board has the embedded 68k architecture based Freescale Coldfire processor. The board has a custom built Linux based on the kernel 2.6.38 The database is stored on an SD card of 4GB capacity. This is the table we have. CREATE TABLE financialtransaction ( FINANCIALTRANSACTIONID BIGINT NOT NULL PRIMARY KEY, TIME_STAMP TIMESTAMP, ATTENDANTSMALLINT, RECEIPTPRINTEDBOOLEAN DEFAULT FALSE, ODOMETER VARCHAR(20), ENGINEHOUR NUMERIC(9,2), CONSTRAINT financialtransaction_pkey PRIMARY KEY (FINANCIALTRANSACTIONID ) ) WITH ( OIDS=FALSE ); ALTER TABLE financialtransaction OWNER TO postgres; On writing into this table, a stored procedure is triggered which inserts into another table. But crash is happening while writing into this financialtransaction table once this table has more than 1000 records. Please let me know if you need any other information. Thanks and Regards Jayashankar -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: 22 May 2012 PM 12:00 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres process is crashing continously in 9.1.1 On 05/21/12 11:05 PM, Jayashankar K B wrote: board with Coldfire controller. what is this board? Coldfire is the embedded 68k-like Freescale processor? what operating system is this under? what sort of storage does this embedded system use for the database? telling us FINANCIALWHATEVERID 1000 doesn't really do us much good since we have no idea what your database looks like, or what your code is doing. the log seems to indicate there was a constraint violation just before the exception hit. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Larsen Toubro Limited www.larsentoubro.com This Email may contain confidential or privileged information for the intended recipient (s). If you are not the intended recipient, please do not use or disseminate the information, notify the sender and delete it from your system. Earth Day. Every Day. -- 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] Postgres process is crashing continously in 9.1.1
On 05/22/2012 01:57 PM, Jayashankar K B wrote: Please let us know why this crash is happening and how we can fix it. LOG: server process (PID 4016) was terminated by signal 11: Segmentation fault If you can't reproduce this crash on a more developer-friendly machine than your embedded system, what you will need to do is trap this crash and get a backtrace that shows where and how the Pg backend(s) died. Your embedded devs should hopefully have no problem with this. You can enable core dumps and have Pg coredump if you have the storage. This works even if you can't predict exactly when the crash will happen or which backend will crash. It requires enough disk space to write out a core file. If you're using a vaguely modern Linux kernel you can set a core dump path on an NFS volume or other network file store to write cores to, so you don't need local storage. See man 5 core http://linux.die.net/man/5/core and the kernel.core_pattern sysctl. Note that you can even pipe core dumps to a program (like, say, scp or netcat) so they don't have to be written even to a network mounted file system. Alternately, you can attach gdb to a backend you know will crash and trap the crash that way. See: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD You will need PostgreSQL to have been compiled with debugging enabled and will need the debug symbols for your libraries. On many embedded platforms those are not included; the binaries are typically stripped. If you're working with stripped binaries you'll get one of the useless backtraces shown in the wiki article above. If your binaries are stripped you can still create a useful backtrace so long as you have access to unstripped copies of those binaries in your development environment, outside the running embedded machine, or you have debuginfo files. You need a core file, either one you let Linux save on crash, or one you created by trapping a crash with gdb and saving it with the gcore /path/to/core/file/postgres.core command. Once you have the core file and have it copied to your development environment, you can debug it with gdb from there using versions of your libraries with full debug symbols or detached debuginfo. Note that the libraries and PostgreSQL binaries must be EXACTLY IDENTICAL to the ones running on the real host except for not being stripped. You can't use binaries that're just the same version of the libraries, they have to be the _same_, built with the same version of the same compiler with the same options as the ones you were actually running. Usually they're the exact same binaries, just copies made before you stripped them for copying onto the embedded device. Of course, you'll be running gdb inside your cross-compile environment to debug. Again, your embedded developers should know how to do all this. If your embedded platform doesn't have debuginfo files or unstripped versions of your libraries, yell at whoever built it and get them to fix it. If you don't have unstripped binaries, you can still build a debug version of PostgreSQL and examine that, you'll just have lots of ??? entries for non-PostgreSQL parts of the call path. The stack trace might be useless, but might not be too. -- Craig Ringer -- 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] Bitrock XML Source
On Tue, May 22, 2012 at 2:24 AM, John R Pierce pie...@hogranch.com wrote: On 05/21/12 11:03 PM, Greg Simpson wrote: I am trying to get the source XML file for the PostgreSQL installer. This is the BitRock InstallBuilder XML file. Can anyone direct me to the proper place to obtain this installer file? the Windows installer? thats produced by EnterpriseDB, and I don't think they've open sourced it. It used to be opensource, but EnterpriseDB have since closed-sourced it. Or at least some kind of hybrid. It sometimes gets updates at http://git.postgresql.org/gitweb/?p=edb-installers.git;a=summary, but I don't believe it always does - right now the last thing that happened in the repo was 5 months ago. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] Postgres process is crashing continously in 9.1.1
On Tue, May 22, 2012 at 4:51 PM, Jayashankar K B jayashankar...@lnties.com wrote: On writing into this table, a stored procedure is triggered which inserts into another table. But crash is happening while writing into this financialtransaction table once this table has more than 1000 records. What language is the stored procedure written in? Is it possible that it's that procedure that segfaults? Postgres experts, do stored procedure segfaults bring down the backend process like that? ChrisA -- 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] Postgres process is crashing continously in 9.1.1
But here, the crash is happening right at the insert statement. That is insert itself is failing. Unless the insert is successful, stored procedure is not triggered. Thanks and regards Jayashankar -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Angelico Sent: Tuesday, May 22, 2012 3:10 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres process is crashing continously in 9.1.1 On Tue, May 22, 2012 at 4:51 PM, Jayashankar K B jayashankar...@lnties.com wrote: On writing into this table, a stored procedure is triggered which inserts into another table. But crash is happening while writing into this financialtransaction table once this table has more than 1000 records. What language is the stored procedure written in? Is it possible that it's that procedure that segfaults? Postgres experts, do stored procedure segfaults bring down the backend process like that? ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Larsen Toubro Limited www.larsentoubro.com This Email may contain confidential or privileged information for the intended recipient (s). If you are not the intended recipient, please do not use or disseminate the information, notify the sender and delete it from your system. Earth Day. Every Day. -- 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] Postgres process is crashing continously in 9.1.1
On Tue, May 22, 2012 at 8:23 PM, Jayashankar K B jayashankar...@lnties.com wrote: But here, the crash is happening right at the insert statement. That is insert itself is failing. Unless the insert is successful, stored procedure is not triggered. Hmm. I wonder is it possible that going past ID 999 and into a four-digit number is causing stack damage that crashes the server a few iterations later... many things are possible. I'd look at the code of the procedure and see if there's any possible memory/stack issues. ChrisA -- 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] how to for loop with distinct values?
2012/5/22 Merlin Moncure mmonc...@gmail.com On Mon, May 21, 2012 at 3:39 PM, J.V. jvsr...@gmail.com wrote: I am banging my head over this. I want to select distinct values from a varchar column and iterate through the values. I want to select the distinct values from this column and loop through them (using as a variable) in a raise notice statement and also in an update statement. I have not been able to do this trying dozens of different approaches. I could not find an example even after searching google. So for example, suppose table: mytable has a column value that is defined as a varchar: for tmp_var in select distinct(value) from mytable where value2='literal' loop raise notice 'I want to print a message here - the tmp_var is [' || tmp_var || ']'; == error on this line update table set somecolumn = ''' || tmp_var || ''' end loop; I want to use each distinct value in a raise notice line and an update statement. tmp_var has to be in ' ' ticks or will not work. it is failing on the first FOR statement stating: invalid input syntax for integer: some_distinct_value. How do I select varchar distinct values and iterate using variables in a raise notice statement and inside another update statement? this seems simple to do , but have not found a way. Well it looks like you have a couple of problems here. First, when you 'raise notice', generally you do it like this: raise notice 'value of var is %', var; And not do string concatenation. As for the update statement, you should be quoting tmp_var. At most you should be casting (tmp_var::int) and then be diagnosing why you have non integer data in a value you are trying to put into a integer column (which is the point of the insert). So, you are very close -- it all comes down to how you are handling the NOTICE i think. A quick review of the examples here: http://www.postgresql.org/docs/9.2/static/plpgsql-errors-and-messages.html might be helpful. merlin Nice color and font ;-) -- // Dmitriy.
Re: [GENERAL] Postgres process is crashing continously in 9.1.1
On Tue, May 22, 2012 at 5:41 AM, Chris Angelico ros...@gmail.com wrote: On Tue, May 22, 2012 at 8:23 PM, Jayashankar K B jayashankar...@lnties.com wrote: But here, the crash is happening right at the insert statement. That is insert itself is failing. Unless the insert is successful, stored procedure is not triggered. Hmm. I wonder is it possible that going past ID 999 and into a four-digit number is causing stack damage that crashes the server a few iterations later... many things are possible. I'd look at the code of the procedure and see if there's any possible memory/stack issues. Hm, on linux you check stack size with ulimit -s? If stack is set too low, a lower setting of max_stack_depth should prevent the crash. It's pretty hard to hit that unless you have extraordinarily complex and/or recursive functions though. Any chance of seeing the function source? merlin -- 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] how to for loop with distinct values?
On Tue, May 22, 2012 at 6:03 AM, Dmitriy Igrishin dmit...@gmail.com wrote: Nice color and font ;-) yup -- html formatted emails that I find displeasing get an automatic response in 'ms comic sans' :-D. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL]
stop sending mail
Re: [GENERAL]
2012/5/22 Gugu Nomcebo Mthimkhulu hlubelihle.m...@yahoo.com stop sending mail You must access the link [1] to unsubscribe from this mailing list. [1] http://www.postgresql.org/mailpref/pgsql-general -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [GENERAL]
On 05/22/2012 07:13 AM, Gugu Nomcebo Mthimkhulu wrote: stop sending mail To unsubscribe go here: http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?func=lists-long-fullextra=pgsql-general -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why jasperserver has been changed from MySQL to PostGreSQL
Why jasperserver has been changed from MySQL to PostGreSQL In previous versions of jasperserver, MySQL was the supporting database but in the jasperserver 4.5 version it changed to PostGreSQL. My question is that why jasperserver changed its mind to PostGreSQL. Which of the features of PostGreSQL are powerful than MySQL? Could you please give me a clue? Thanks and Regards Farhad Koohbor – BI/DW developer
Re: [GENERAL] why jasperserver has been changed from MySQL to PostGreSQL
On May 22, 2012, at 7:31 AM, farhad koohbor wrote: My question is that why jasperserver changed its mind to PostGreSQL. Which of the features of PostGreSQL are powerful than MySQL? Could you please give me a clue? Postgres is more SQL-compliant and tends to work better at larger scale than MySQL. It also is more extendable. For more details, see http://www.postgresql.org/about/, and to answer your real question definitively, instead of just speculating, ask the jasperserver people themselves. :)
Re: [GENERAL] Why are pg_restore taking that long ?
Possibly. You should be able to tell what's taking so much time by keeping an eye on your server during the restore. You could set log_statement = 'all' (or just 'ddl' if there is significant other activity in other databases) during the restore, and keep an eye on pg_stat_activity if you need to. OK, nothing unusual I think. It's the foreign keys that takes that long to generate. I didn't realize that they are in the system tables. Poul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] haproxy / pgpool / rhcs
Hello I'm implementing HA/failover for my PG nodes. I'm using PG9.0 and async replication and linux. Typical problem - if node 1 fails I want the mirror to become active and take over for the master. The solution should be able to initiate the failover of the standby and start re-directing traffic it. I've spent a lot of time looking at PgPool so I'm well aware that it's perfectly capable of this. However, it feels a little bit like overkill since i don't want it's pooler and I'm not load balancing. The other option would be RHCS, which I know will work, but to implement it with pg replication is a little hacky. So, I'm considering HAProxy, does anyone have experience with that for managing HA, good or bad? Thanks. -- 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] Reasons for postgres processes beeing killed by SIGNAL 9?
On Saturday, May 19, 2012 04:42:16 PM Clemens Eisserer wrote: Hi again, We are still constantly getting postgresql processes killed by signal 9 from time to time, without any idea why or how. Syslog seems completly clean. In case a postgresql process would exceed some restricted resources like file descriptors, would the kernel choose to terminate it using SIGKILL? Are there any other common examples / occurences where processes are terminated this way automatically? Check dmesg or the kernel log. I'd guess it's the OOM-killer. Assuming this is on Linux, that is. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1
No one has any ideas or suggestions, or even questions? If someone needs more information, I'd be happy to provide it. This problem is absolutely killing me. On Mon, May 21, 2012 at 2:05 PM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I made no changes to postgresql.conf following the upgrade. I used pg_upgrade on the master to upgrade it, followed by blowing away $PGDATA on all the standbys and rsyncing them fresh from the master. All of the servers have 128GB RAM, and at least 16 CPU cores. Everything appeared to be working fine until last night when the load on the master suddenly took off, and hovered at around 30.00 ever since. Prior to the load spike, the load was hovering around 2.00 (which is actually lower than it was averaging prior to the upgrade when it was often around 4.00). When I got in this morning, I found an autovacuum process that had been running since just before the load spiked, and the pg_dump cronjob that started shortly after the load spike (and normally completes in about 20 minutes for all the databases) was still running, and hadn't finished the first of the 6 databases. I ended up killing the pg_dump process altogether in the hope that it might unblock whatever was causing the high load. Unfortunately that didn't help, and the load continued to run high. I proceeded to check dmesg, /var/log/messages and the postgresql server log (all on the master), but I didn't spot anything out of the ordinary, definitely nothing that pointed to a potential explanation for all of the high load. I inspected what the autovacuum process was doing, and determined that it was chewing away on the largest table (nearly 98 million rows) in the largest database. It was making very slow progress, at least I believe that was the case, as when I attached strace to the process, the seek addresses were changing in a random fashion. Here are the current autovacuum settings: autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold | 50 autovacuum_freeze_max_age | 2 autovacuum_max_workers | 4 autovacuum_naptime | 1min autovacuum_vacuum_cost_delay | 20ms autovacuum_vacuum_cost_limit | -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 Did something significant change in 9.1 that would impact autovacuum behavior? I'm at a complete loss on how to debug this, since I'm using the exact same settings now as prior to the upgrade. thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_basebackup blocking all queries
Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I'm finding that I cannot runpg_basebackup at all, or it blocks all SQL queries from running until pg_basebackup has completed (and the load on the box just takes off to over 75.00). By blocks I mean that any query that is submitted just hangs and does not return at all until pg_basebackup has stopped. I'm assuming that this isn't expected behavior, so I'm rather confused on what is going on. The command that I'm issuing is: pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres Can someone provide some guidance on how to debug this? thanks! -- 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] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1
I'd say to do some monitoring of your machine when this is happening. vmstat, iostat, iotop, htop, and so on. Are you running out of memory, a context switch / interrupt storm? IO bound? And so on. On Tue, May 22, 2012 at 12:20 PM, Lonni J Friedman netll...@gmail.com wrote: No one has any ideas or suggestions, or even questions? If someone needs more information, I'd be happy to provide it. This problem is absolutely killing me. On Mon, May 21, 2012 at 2:05 PM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I made no changes to postgresql.conf following the upgrade. I used pg_upgrade on the master to upgrade it, followed by blowing away $PGDATA on all the standbys and rsyncing them fresh from the master. All of the servers have 128GB RAM, and at least 16 CPU cores. Everything appeared to be working fine until last night when the load on the master suddenly took off, and hovered at around 30.00 ever since. Prior to the load spike, the load was hovering around 2.00 (which is actually lower than it was averaging prior to the upgrade when it was often around 4.00). When I got in this morning, I found an autovacuum process that had been running since just before the load spiked, and the pg_dump cronjob that started shortly after the load spike (and normally completes in about 20 minutes for all the databases) was still running, and hadn't finished the first of the 6 databases. I ended up killing the pg_dump process altogether in the hope that it might unblock whatever was causing the high load. Unfortunately that didn't help, and the load continued to run high. I proceeded to check dmesg, /var/log/messages and the postgresql server log (all on the master), but I didn't spot anything out of the ordinary, definitely nothing that pointed to a potential explanation for all of the high load. I inspected what the autovacuum process was doing, and determined that it was chewing away on the largest table (nearly 98 million rows) in the largest database. It was making very slow progress, at least I believe that was the case, as when I attached strace to the process, the seek addresses were changing in a random fashion. Here are the current autovacuum settings: autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold | 50 autovacuum_freeze_max_age | 2 autovacuum_max_workers | 4 autovacuum_naptime | 1min autovacuum_vacuum_cost_delay | 20ms autovacuum_vacuum_cost_limit | -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 Did something significant change in 9.1 that would impact autovacuum behavior? I'm at a complete loss on how to debug this, since I'm using the exact same settings now as prior to the upgrade. thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- To understand recursion, one must first understand recursion. -- 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] pg_basebackup blocking all queries
Do the queries here help? http://wiki.postgresql.org/wiki/Lock_Monitoring On Tue, May 22, 2012 at 12:42 PM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I'm finding that I cannot runpg_basebackup at all, or it blocks all SQL queries from running until pg_basebackup has completed (and the load on the box just takes off to over 75.00). By blocks I mean that any query that is submitted just hangs and does not return at all until pg_basebackup has stopped. I'm assuming that this isn't expected behavior, so I'm rather confused on what is going on. The command that I'm issuing is: pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres Can someone provide some guidance on how to debug this? thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- To understand recursion, one must first understand recursion. -- 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] pg_basebackup blocking all queries
Thanks for your reply. Unfortunately, those queries don't shed any light no the problem. The first two return 0 rows, and the third just returns 12 rows all associated with the query itself, rather than anything else. Any other suggestions? On Tue, May 22, 2012 at 2:56 PM, Scott Marlowe scott.marl...@gmail.com wrote: Do the queries here help? http://wiki.postgresql.org/wiki/Lock_Monitoring On Tue, May 22, 2012 at 12:42 PM, Lonni J Friedman netll...@gmail.com wrote: Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I'm finding that I cannot runpg_basebackup at all, or it blocks all SQL queries from running until pg_basebackup has completed (and the load on the box just takes off to over 75.00). By blocks I mean that any query that is submitted just hangs and does not return at all until pg_basebackup has stopped. I'm assuming that this isn't expected behavior, so I'm rather confused on what is going on. The command that I'm issuing is: pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres Can someone provide some guidance on how to debug this? thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] main log encoding problem
I'm using postgresql 9.1.3 from debian squeeze-backports with zh_CN.UTF-8 locale, i find my main log (which is /var/log/postgresql/postgresql-9.1-main.log) contains ??? which indicate some sort of charset encoding problem. But error messages related to pgsql is fine, only other system messages have this problem, for example: 2012-05-19 16:06:12 CST ??: ?? 2012-05-19 16:06:10 CST 2012-05-19 16:06:12 CST ??: ??? 2012-05-19 16:06:12 CST ??: ???autovacuum 2012-05-19 16:06:12 CST ??: ??? 2012-05-19 16:07:16 CST 错误: 角色postgres 已经存在(in english: Error: role postgres already exists) 2012-05-19 16:07:16 CST 语句: CREATE ROLE postgres; 2012-05-19 16:07:16 CST 错误: 语言 plpgsql 已经存在 (in english: Error: language plpgsql already exists) 2012-05-19 16:07:16 CST 语句: CREATE PROCEDURAL LANGUAGE plpgsql; 2012-05-19 16:08:23 CST : ?? huangyi ??? 2012-05-19 16:08:52 CST : ?? huangyi ??? 2012-05-19 16:09:01 CST ??: ???(zlfund)(huangyi) ??? 2012-05-19 16:09:01 CST : Peer authentication failed for user zlfund 2012-05-19 16:09:34 CST ??: ???(zlfund)(huangyi) ??? 2012-05-19 16:09:34 CST : Peer authentication failed for user zlfund I guess it has something to do with packaging problem rather than postgresql itself, but it would be great if you can give me some clue where the problem might be. My best regards. Yi Huang.
Re: [GENERAL] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 - 9.1
On Mon, May 21, 2012 at 2:05 PM, Lonni J Friedmannetll...@gmail.com wrote: Greetings, When I got in this morning, I found an autovacuum process that had been running since just before the load spiked, Autovacuum might need to set the freeze bit very first time it runs. I recall hearing advice about running a 'vacuum freeze' after you insert a huge amount of data. And I recall pg_upgrade doesn't write stats, so did you analyze your database? Or, maybe its not vacuum... maybe some of your sql statements are planning differently and running really bad. Can you check some? Can you log slow queries? Have you checked the status of your raid? Maybe you lost a drive and its in recovery and you have very slow IO? -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
