Re: [GENERAL] Server stops responding in every week
Andy, How to automatically re-start postgres service in every night ? Or is it better to switch to 32bit server? Neither. You need to fix the problem. How to configure postgres so that freeze issue can addressed ? E.q. if query runs more that 60 seconds, postgres dumps its status and long query reason . How to implement this ? No idea can log_statement=all help. Andrus. -- 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] Read problem from Bytea column
Francisco Figueiredo Jr
Sent from my Android phone.
Em 21/01/2011 13:50, orgilhp orgi...@gmail.com escreveu:
Hello,
Hello!
I have postgresql 8.3.9 database. Now I am working on reading file
from bytea data and save to filesystem.
I am using Npgsql.dll on c#.net.
Which version of Npgsql are you using?
Your code is ok and should be working. More info can be get from Working
with binary data and bytea datatype section of npgsql user manual.
Did you try another npgsql version? If it works in another version, please,
fill a bug report so we can fix it.
Thanks in advance!
First I have using OpenERP to upload a file to postgres database in
bytea column. The original file size is 104,960 bytes and .doc
extension.
Then I read the bytea data and save it to file system, its size
becomes 141,790 bytes. This file cannot be opened however its
extension is .doc.
What is going on?
My c# code is below:
NpgsqlCommand cmd = new NpgsqlCommand(SELECT bytea_col
FROM TableTest LIMIT 1, Conn);
using (FileStream stream = new FileStream(c:\test.doc,
FileMode.Create))
{
byte[] rawbytes = (byte[])cmd.ExecuteScalar();
stream.Write(rawbytes, 0, rawbytes.Length);
stream.Close();
}
--
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] Using copy for WAL archiving on Windows
Christian, Should PostgreSQL maybe provide its own file-copy utility for Windows that meets the requirements for safe WAL archiving? Microsoft does provide an enterprise-ready webscale copy program ... it is called robocopy and part of the Windows Server Resource Kit Tools. Would you mind trying that utility for copying WAL-files? http://www.microsoft.com/downloadS/en/details.aspx?familyid=9d467a69-57ff-4ae7-96ee-b18c4790cffddisplaylang=en (Microsoft URLs are like a leaf in the wind of change, so better google for robocopy site:microsoft.com) best wishes, Harald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH i.Gr. GF: Harald Armin Massa
Re: [GENERAL] Server stops responding in every week
On 01/22/2011 03:00 AM, Andrus Moor wrote: Andy, How to automatically re-start postgres service in every night ? Or is it better to switch to 32bit server? Neither. You need to fix the problem. How to configure postgres so that freeze issue can addressed ? E.q. if query runs more that 60 seconds, postgres dumps its status and long query reason . How to implement this ? No idea can log_statement=all help. Andrus. Before we can fix the problem, we need to know what the problem is. Wait until the server is really slow and having problems. Then answer these questions: Does ram using increase over the week? Do you see Idle in transaction (probably in task manager)? Show us: select * from pg_stat_activity; and select * from pg_locks; A little practice up front might help. When the system is broke, you can interrogate it to see what its doing. Hopefully we'll see something locked, and a bunch of things waiting. -Andy -- 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] Using copy for WAL archiving on Windows
* Harald Armin Massa wrote:
Should PostgreSQL maybe provide its own file-copy utility for
Windows that meets the requirements for safe WAL archiving?
Microsoft does provide an enterprise-ready webscale copy program ... it
is called robocopy and part of the Windows Server Resource Kit Tools.
Would you mind trying that utility for copying WAL-files?
I'm not sure I trust _that_:
### archive_wal.bat ###
robocopy pg_xlog c:\wal %1 /r:0 /w:0 /xc
if errorlevel 4 exit 1
if errorlevel 1 exit 0
if errorlevel 0 exit 1
### end ###
archive_command = archive_wal.bat %f
robocopy's exit codes make good reading, if you are into horror stories.
Thanks for the tip; I had previously discounted robocopy because I
thought it could not copy individual files, only whole directories (with
exclusion patterns, but not inclusion patterns). It turns out I will not
use it for entirely different reasons.
The perfect archiving utility for Windows is something like this:
### archive_wal.c ###
#define UNICODE 1
#define _UNICODE 1
#include windows.h
int wmain(int argc, WCHAR *argv[])
{
return ((argc == 3
CopyFile(argv[1], argv[2], TRUE) != 0) ? 0 : 1);
}
### end ###
archive_command = archive_wal %p c:\wal\%f
--
Christian
--
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] SHMMAX and SHMALL question
DM wrote: RAM = 16GB, what value should i set for shmall? Given that PostgreSQL rarely sees increasing improvement as shared_buffers goes over 50% of RAM, I just use that figure for the shmall and then compute shmmax based on the page size to match it. I use the attached script to do all the hard work, haven't found a Linux system yet it didn't do the right thing on. It sounds like you might have the math on the relation between the two backwards, look at the output and code of this once and that should sort things out for you. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books #!/bin/bash # Output lines suitable for sysctl configuration based # on total amount of RAM on the system. The output # will allow up to 50% of physical memory to be allocated # into shared memory. # On Linux, you can use it as follows (as root): # # ./shmsetup /etc/sysctl.conf # sysctl -p # Early FreeBSD versions do not support the sysconf interface # used here. The exact version where this works hasn't # been confirmed yet. page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` if [ -z $page_size ]; then echo Error: cannot determine page size exit 1 fi if [ -z $phys_pages ]; then echo Error: cannot determine number of memory pages exit 2 fi shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo \# Maximum shared segment size in bytes echo kernel.shmmax = $shmmax echo \# Maximum number of shared memory segments in pages echo kernel.shmall = $shmall -- 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] Server stops responding in every week
Andy, Then answer these questions: Does ram using increase over the week? Server was re-started yesterday and there was little use after restart. server is idle. Task Manager shows now PF Usage 1.22 GB Physical Memory (K) Total 4191872 Avail 348 (last 4 vary) System cache 35676xx (last 2 vary) How to check for memory increase over week ? Do you see Idle in transaction (probably in task manager)? Task Manager shows only image name. Where I should see this ? Show us: select * from pg_stat_activity; I ran it from pgadmin. It shows now : 11874;postgres;212;10;postgres;;127.0.0.1;1183;2011-01-22 21:24:51.343+02;;2011-01-22 21:24:51.5+02;f;IDLE 45923;mydb;3080;10;postgres;;127.0.0.1;1184;2011-01-22 21:24:55.25+02;;2011-01-22 21:24:55.281+02;f;IDLE 45923;mydb;4732;10;postgres;;127.0.0.1;1185;2011-01-22 21:24:57.156+02;2011-01-22 21:25:24.109+02;2011-01-22 21:25:24.109+02;f; I will try to run it on next crash if users will not restart server before. and select * from pg_locks; I ran it from pgadmin. It shows now : relation;45923;109854/241;4732;AccessShareLock;t virtualxid;4/241;4/241;4732;ExclusiveLock;t I will try to run it on next crash if users will not restart server before. A little practice up front might help. When the system is broke, you can interrogate it to see what its doing. Hopefully we'll see something locked, and a bunch of things waiting. Users can restart server without contacting me. For this reason I asked for automated dump. Andrus. -- 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] Server stops responding in every week
I know you're running windows, but if you can get bash working on it, here's a simple bash script I wrote that when it detects too many people connected creates a table called pg_stat_bk_20110120140634 (i.e. date and time) so I can then look over what was in pg_stat_activity when things were acting up. #!/bin/bash threshold=50; dt=`date +%Y%m%d%H%M%S`; active=`/usr/bin/psql www -Atc select count(*) from pg_stat_activity where current_query not ilike '%idle%';` if [[ active -gt threshold ]]; then echo there are $active backends; echo creating backup for pg_stat as pg_stat_bk_$dt psql www -c select * into monitoring.pg_stat_bk_$dt from pg_stat_activity where current_query not ilike '%idle%'; fi note that you should probably change what triggers it to things like if there are any queries waiting or idle in transaction, etc. check.sh Description: Bourne shell script -- 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] SHMMAX and SHMALL question
Thank you so much for the script. ~deepak On Jan 22, 2011, at 10:18 AM, Greg Smith g...@2ndquadrant.com wrote: DM wrote: RAM = 16GB, what value should i set for shmall? Given that PostgreSQL rarely sees increasing improvement as shared_buffers goes over 50% of RAM, I just use that figure for the shmall and then compute shmmax based on the page size to match it. I use the attached script to do all the hard work, haven't found a Linux system yet it didn't do the right thing on. It sounds like you might have the math on the relation between the two backwards, look at the output and code of this once and that should sort things out for you. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books #!/bin/bash # Output lines suitable for sysctl configuration based # on total amount of RAM on the system. The output # will allow up to 50% of physical memory to be allocated # into shared memory. # On Linux, you can use it as follows (as root): # # ./shmsetup /etc/sysctl.conf # sysctl -p # Early FreeBSD versions do not support the sysconf interface # used here. The exact version where this works hasn't # been confirmed yet. page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` if [ -z $page_size ]; then echo Error: cannot determine page size exit 1 fi if [ -z $phys_pages ]; then echo Error: cannot determine number of memory pages exit 2 fi shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo \# Maximum shared segment size in bytes echo kernel.shmmax = $shmmax echo \# Maximum number of shared memory segments in pages echo kernel.shmall = $shmall -- 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] Subquery for column names of tablefunc crosstab queries
On 01/21/2011 09:34 AM, Julia Jacobson wrote:
Two further problems are the fact that the names of columns are not
allowed to begin with a number and every entry in the table definition
list must not only contain the name of the column but of course also a
data type (always the same - int).
Is it possible for a newbie to solve my problem by a user-defined
function in PL/pgSQL or is it rather complicated?
It's a bit tricky to get correct. The following is a bit ugly, and just
barely tested, but works at least for your presented case:
---
CREATE OR REPLACE FUNCTION generate_crosstab_sql(relname text,
grpattr text,
grpattrtyp text,
catattr text,
valattr text,
valattrtyp text,
whereclause text)
RETURNS text AS $$
DECLARE
crosstabsql text;
coldef text;
catdef text;
rec record;
BEGIN
coldef := '(' || grpattr || ' ' || grpattrtyp;
catdef := 'VALUES';
FOR rec IN EXECUTE
'SELECT DISTINCT ' || catattr ||
' AS c FROM ' || relname || ' WHERE ' ||
whereclause || ' ORDER BY 1'
LOOP
coldef := coldef || ',' || rec.c || ' ' || valattrtyp;
IF catdef = 'VALUES' THEN
catdef := catdef || '($v$' || rec.c || '$v$)';
ELSE
catdef := catdef || ',($v$' || rec.c || '$v$)';
END IF;
END LOOP;
coldef := coldef || ')';
IF catdef != 'VALUES' THEN
crosstabsql :=
$ct$SELECT * FROM crosstab ('SELECT $ct$ ||
grpattr || $ct$,$ct$ ||
catattr || $ct$,$ct$ || valattr ||
$ct$ FROM $ct$ || relname ||
$ct$ WHERE $ct$ || whereclause ||
$ct$ ORDER BY 1,2','$ct$ ||
catdef || $ct$') AS $ct$ || coldef;
END IF;
RETURN crosstabsql;
END;
$$ LANGUAGE plpgsql;
---
Then this call:
---
SELECT generate_crosstab_sql('mytable',
'rowid',
'text',
'rowdt::date',
'temperature',
'int',
'1 = 1');
---
Produces this SQL:
---
SELECT * FROM crosstab ('SELECT rowid,rowdt::date,temperature FROM
mytable WHERE 1 = 1 ORDER BY
1,2','VALUES($v$2003-03-01$v$),($v$2003-03-02$v$),($v$2003-03-03$v$)')
AS (rowid
text,2003-03-01 int,2003-03-02 int,2003-03-03 int)
---
Which produces this result:
---
SELECT * FROM crosstab ('SELECT rowid,rowdt::date,temperature FROM
mytable WHERE 1 = 1 ORDER BY
1,2','VALUES($v$2003-03-01$v$),($v$2003-03-02$v$),($v$2003-03-03$v$)')
AS (rowid
contrib_regression(# text,2003-03-01 int,2003-03-02 int,2003-03-03
int);
rowid | 2003-03-01 | 2003-03-02 | 2003-03-03
---+++
test1 | 42 ||
test2 || 53 |
test3 ||| 49
(3 rows)
---
You might need some adjustments to get this to do exactly what you want.
Also please test it for correctness ;-)
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, 24x7 Support
signature.asc
Description: OpenPGP digital signature
