[GENERAL] many schemas or many databases
Hi, is there any noticeable difference between a cluster with many databases and a database with many schemas? I've got a quite huge database on Oracle with about 400 logically disjoint schemas. I could import that into PostgreSQL as many different databases, or as one database with many schemas. From the application point of view it could be easier to have different databases, as for now the applications log in into different schemas, so this behavior wouldn't change. Do you see any drawbacks of any of the solutions? regards Szymon
[GENERAL] (not equal to) in where clause can use index
Is it possible to add some operator class in Postgresql 9 so that (not equal to) in where clause can use index? If yes how? Any idea please.
Re: [GENERAL] (not equal to) in where clause can use index
One way to get around the problem is often to do a left or right join, use = instead of , and catch the rows with null in id field, something like: SELECT a.* from table a LEFT JOIN tableb b on a.testfld=b.testfld WHERE b.id is null; This one will use indexes on testflda and testfldb an HTH /Nicklas On Tue, 2011-02-08 at 15:09 +0600, AI Rumman wrote: Is it possible to add some operator class in Postgresql 9 so that (not equal to) in where clause can use index? If yes how? Any idea please. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Mon, Feb 7, 2011 at 20:38, Thom Brown t...@linux.com wrote: Yes, of course, int8 functions are separate. I attach an updated patch, although I still think there's a better way of doing this. Thanks. Please add the patch to the *current* commitfest because it's a bugfix. https://commitfest.postgresql.org/action/commitfest_view?id=9 I've not tested the patch yet, but if we could drop the following line in the patch, the code could be much cleaner. /* ensure first value in series should exist */ I'm not sure how this should be handled. Should there just be a check for either kind of infinity and return an error if that's the case? I Maybe so. It also works if we had infinity on timestamp overflow, but I've not tested yet. Anyway, we need similar fix for timestamp versions. -- Itagaki Takahiro -- 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] many schemas or many databases
hi, i would prefer many schemas. advantages: - one backup/restore for all (or selective) - one connection pool - simple access to all schemas regards thomas Am 08.02.2011 09:30, schrieb Szymon Guz: Hi, is there any noticeable difference between a cluster with many databases and a database with many schemas? I've got a quite huge database on Oracle with about 400 logically disjoint schemas. I could import that into PostgreSQL as many different databases, or as one database with many schemas. From the application point of view it could be easier to have different databases, as for now the applications log in into different schemas, so this behavior wouldn't change. Do you see any drawbacks of any of the solutions? regards Szymon -- 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] many schemas or many databases
Hi 2011/2/8 Thomas Markus t.mar...@proventis.net: hi, i would prefer many schemas. advantages: - one backup/restore for all (or selective) - one connection pool - simple access to all schemas +1 and one disadvantage - impossible separation on independent hw, when it is necessary or when you has more hw Depends on application and size of data - if you has lot of logs, or some like OLAP data, then is very practical use more than one database. Regards Pavel Stehule regards thomas Am 08.02.2011 09:30, schrieb Szymon Guz: Hi, is there any noticeable difference between a cluster with many databases and a database with many schemas? I've got a quite huge database on Oracle with about 400 logically disjoint schemas. I could import that into PostgreSQL as many different databases, or as one database with many schemas. From the application point of view it could be easier to have different databases, as for now the applications log in into different schemas, so this behavior wouldn't change. Do you see any drawbacks of any of the solutions? regards Szymon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] about PostgreSQL 9.0.3 RPMs
Hi, I searched PostgreSQL 9.0.3 RPMs for RHEL5, but couldn't find them. I need to them in my business. They are not yet on the following page. http://yum.pgrpms.org/9.0/redhat/rhel-5Server-x86_64/ When are they available? regards, -- OTSUKA Kenji NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On 8 February 2011 09:22, Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Mon, Feb 7, 2011 at 20:38, Thom Brown t...@linux.com wrote: Yes, of course, int8 functions are separate. I attach an updated patch, although I still think there's a better way of doing this. Thanks. Please add the patch to the *current* commitfest because it's a bugfix. https://commitfest.postgresql.org/action/commitfest_view?id=9 I've not tested the patch yet, but if we could drop the following line in the patch, the code could be much cleaner. /* ensure first value in series should exist */ I'm not sure how this should be handled. Should there just be a check for either kind of infinity and return an error if that's the case? I Maybe so. It also works if we had infinity on timestamp overflow, but I've not tested yet. Anyway, we need similar fix for timestamp versions. Well, in its current state, I expect it to get rejected, but I guess at least it gets a better chance of being looked at. I've added it to the commitfest now. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] fulltext search and hunspell
Jens,
have you tried german compound dictionary from
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
Oleg
On Tue, 8 Feb 2011, Jens Sauer wrote:
Hey,
thanks for your answer.
First I checked the links in the tsearch_data directory
de_de.affix, and de_de.dict are symlinks to the corresponding files in
/var/cache/postgresql/dicts/
Then I recreated them by using pg_updatedicts.
This is an extract of the de_de.affix file:
# this is the affix file of the de_DE Hunspell dictionary
# derived from the igerman98 dictionary
#
# Version: 20091006 (build 20100127)
#
# Copyright (C) 1998-2009 Bjoern Jacke bjo...@j3e.de
#
# License: GPLv2, GPLv3 or OASIS distribution license agreement
# There should be a copy of both of this licenses included
# with every distribution of this dictionary. Modified
# versions using the GPL may only include the GPL
SET ISO8859-1
TRY esijanrtolcdugmphbyfvkwqxz??ESIJANRTOLCDUGMPHBYFVKWQXZ-.
PFX U Y 1
PFX U 0 un .
PFX V Y 1
PFX V 0 ver .
SFX F Y 35
[...]
I cannot find compoundwords controlled z there, so I manually added it.
[...]
# versions using the GPL may only include the GPL
compoundwords controlled z
SET ISO8859-1
TRY esijanrtolcdugmphbyfvkwqxz??ESIJANRTOLCDUGMPHBYFVKWQXZ-.
[...]
Then I restarted PostgreSQL.
Now I get an error:
SELECT * FROM ts_debug('Schokoladenfabrik');
FEHLER: falsches Affixdateiformat f?r Flag
CONTEXT: Zeile 18 in Konfigurationsdatei
?/usr/share/postgresql/8.4/tsearch_data/de_de.affix?: ?PFX U Y 1
?
SQL-Funktion ?ts_debug? Anweisung 1
SQL-Funktion ?ts_debug? Anweisung 1
Which means:
ERROR: wrong Affixfileformat for flag
CONTEXT: Line 18 in Configuration ...
If I add
COMPOUNDFLAG Z
ONLYINCOMPOUND L
instead of compoundwords controlled z
I didn't get an error:
SELECT * FROM ts_debug('Schokoladenfabrik');
alias | description | token |
dictionaries | dictionary | lexemes
---+-+---+---+-+---
asciiword | Word, all ASCII | Schokoladenfabrik |
{german_hunspell,german_stem} | german_stem | {schokoladenfabr}
(1 row)
But it seems that the hunspell dictionary is not working for compound words.
Maybe pg_updatedicts has a bug and generates affix files in the wrong format?
Jens
2011/2/7 Oleg Bartunov o...@sai.msu.su:
Jens,
could you check affix file for
compoundwords controlled z
also, can you provide link to dictionary files, so we can check if they
supported, since we have only rudiment support of hunspell.
btw,it'd be nice to have output from ts_debug() to make sure dictionaries
actually used.
Oleg
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Displaying text appears as hex data
Hello list, I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: $ TERM=vt100 /pfx/bin/psql opensips opensips psql (9.0.2) Type help for help. opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. How can I accomplish this in the easiest way? Is there something in the PostgreSQL source tree that I should change, for example hacking in: src/backend/utils/mb/conversion_procs Thanks alot, Michael -- 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] Displaying text appears as hex data
On 8 February 2011 10:39, Michael postgre...@encambio.com wrote: Hello list, I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: $ TERM=vt100 /pfx/bin/psql opensips opensips psql (9.0.2) Type help for help. opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. ASCII text? You mean you wish to translate the binary into ASCII? You can use this: SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; The reason why it doesn't automatically do this is because since it's binary data, it's up to you to define what its content format is. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] Displaying text appears as hex data
On Tue, Feb 08, 2011 at 11:39:04AM +0100, Michael wrote: I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB I take it you mean BYTEA. and PostgreSQL is displaying it in hex format like so: $ TERM=vt100 /pfx/bin/psql opensips opensips psql (9.0.2) Type help for help. opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. You might attempt to apply decode(column, 'hex') to the relevant column. Better be sure the result really is printable text. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Maintenance commands on standby servers
On Tue, Feb 8, 2011 at 4:04 AM, Sylvain Rabot sylv...@abstraction.fr wrote: Is it possible to run maintenance commands like ANALYZE, VACUUM, CLUSTER on a standby server ? No. Since the effect of the maintenance command on the primary server is also replicated, you don't need to do that on the standby. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] Additional Grants To SuperUser?
2011/2/7 Carlos Mennens carlos.menn...@gmail.com On Fri, Feb 4, 2011 at 5:08 PM, Dmitriy Igrishin dmit...@gmail.com wrote: These all (SUPERUSER, CREATEDB, SUPERUSER) are role attributes. By performing ALTER ROLE postgres NOSUPERUSER it is possible to turn role with a superuser status into a role that just can create databases and manage roles (admin, but without superuser privileges). So is it very bad to alter ANY of the default role attributes granted to the 'postgres' user? I don't know if removing role attributes from him will have negative consequences to features / functional tasks of the PostgreSQL server / client application(s). Nothing special in 'postgres' user from the POV of DBMS. It is just a user with superuser attribute created when you perform initdb(1). But please note, some OS distributives uses 'postgres' for non-interactive access to all databases for automatic maintenance (custom daily cronjobs, replication, and similar tasks) -- please see you pg_hba.conf file where entry for 'postgres' user usually resides. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] about PostgreSQL 9.0.3 RPMs
On Tue, 2011-02-08 at 19:09 +0900, OTSUKA Kenji wrote: I searched PostgreSQL 9.0.3 RPMs for RHEL5, but couldn't find them. I need to them in my business. They are not yet on the following page. http://yum.pgrpms.org/9.0/redhat/rhel-5Server-x86_64/ In the next 2 days. There is an issue in the current RPM buildfarm that resulted in delay. Regards, -- Devrim GÜNDÜZ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Displaying text appears as hex data
Hello Karsten, On Tues., Feb 08, 2011, Karsten Hilbert wrote: On 8 February 2011 10:39, Michael wrote: I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB I take it you mean BYTEA. That's probably correct, yes. and PostgreSQL is displaying it in hex format like so: $ TERM=vt100 /pfx/bin/psql opensips opensips psql (9.0.2) Type help for help. opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. You might attempt to apply decode(column, 'hex') to the relevant column. I had tried that before, and here's the result: opensips= SELECT id, time_stamp, callid, traced_user, decode(msg, 'hex'), method FROM sip_trace; ERROR: invalid hexadecimal digit: \ I don't understand this. Isn't it PostgreSQL that stores the BYTEA values and then displays them in hex, indicating this by prepending the '\x' backslash ex? Or if the '\x' is actually stored, then why and who is doing that? Any idea? Thanks, Michael -- 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] Displaying text appears as hex data
Hello Thom, I sent this accidentally to you directly, here's a copy for the list as well. On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 10:39, Michael wrote: opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. ASCII text? You mean you wish to translate the binary into ASCII? You can use this: SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; The reason why it doesn't automatically do this is because since it's binary data, it's up to you to define what its content format is. That's understandable and PostgreSQL is doing the right thing, but... opensips= SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; ERROR: function convert_from(text, unknown) does not exist LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg... ^ The arrow in the last line indicates that 'convert_from' is not correctly parsed. Is it because I've not build something in: src/backend/utils/mb/conversion_procs I think I built a standard PostgreSQL installation, or? Regards, Michael -- 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] Displaying text appears as hex data
On 8 February 2011 12:45, Michael postgre...@encambio.com wrote: Hello Thom, I sent this accidentally to you directly, here's a copy for the list as well. On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 10:39, Michael wrote: opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. ASCII text? You mean you wish to translate the binary into ASCII? You can use this: SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; The reason why it doesn't automatically do this is because since it's binary data, it's up to you to define what its content format is. That's understandable and PostgreSQL is doing the right thing, but... opensips= SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; ERROR: function convert_from(text, unknown) does not exist LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg... ^ The arrow in the last line indicates that 'convert_from' is not correctly parsed. My understanding was that your msg column was of type bytea. Is this not the case? Or is it a different column which needs converting? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] Displaying text appears as hex data
Hello Thom,
On Tues., Feb 08, 2011, Thom Brown wrote:
On 8 February 2011 12:45, Michael postgre...@encambio.com wrote:
On Tues., Feb 08, 2011, Thom Brown wrote:
On 8 February 2011 10:39, Michael wrote:
opensips= select * from sip_trace;
id | time_stamp | callid | traced_user | msg | method | ...
1234 | 2011-02-03 | ... | | \x494e56495445207369703a...
Others have said that when they use MySQL, the exact SQL command
as above results in ASCII text rather than hexadecimal, and this
is my goal as well.
opensips= SELECT id, time_stamp, callid, traced_user,
convert_from(msg, 'SQL_ASCII'), method FROM sip_trace;
ERROR: function convert_from(text, unknown) does not exist
LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg...
^
The arrow in the last line indicates that 'convert_from' is not
correctly parsed.
My understanding was that your msg column was of type bytea. Is this
not the case? Or is it a different column which needs converting?
The main developer has confirmed that the msg column is stored
as a BLOB, and I'm nearly sure that bytea is used in this case.
Yes, the correct column that is appearing in hex is called 'msg.'
Is the function 'convert_from' that you mentioned compiled into
the PostgreSQL server binary, or is it part of the template1 when
first created, or something else? It seems I'm missing it, right?
$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i bytea {} \; -print
...nothing
$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i hex {} \; -print
...nothing
$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i binary {} \; -print
static unsigned short BinarySearchRange
./euc_tw_and_big5/big5.c
...nope, not there either.
Regards,
Michael
--
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] Displaying text appears as hex data
On Tue, Feb 08, 2011 at 02:19:49PM +0100, Michael wrote: The arrow in the last line indicates that 'convert_from' is not correctly parsed. My understanding was that your msg column was of type bytea. Is this not the case? Or is it a different column which needs converting? The main developer has confirmed that the msg column is stored as a BLOB, That is not really possible since PostgreSQL does not have a BLOB data type as such. It would either be a BYTEA column or a Large Object (which would live elsewhere and not inside the table you work on). and I'm nearly sure that bytea is used in this case. Very likely. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Displaying text appears as hex data
On 8 February 2011 13:19, Michael postgre...@encambio.com wrote: Hello Thom, On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 12:45, Michael postgre...@encambio.com wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 10:39, Michael wrote: opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. opensips= SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; ERROR: function convert_from(text, unknown) does not exist LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg... ^ The arrow in the last line indicates that 'convert_from' is not correctly parsed. My understanding was that your msg column was of type bytea. Is this not the case? Or is it a different column which needs converting? The main developer has confirmed that the msg column is stored as a BLOB, and I'm nearly sure that bytea is used in this case. Yes, the correct column that is appearing in hex is called 'msg.' Well the function seems to think it's a text field. Is the function 'convert_from' that you mentioned compiled into the PostgreSQL server binary, or is it part of the template1 when first created, or something else? It seems I'm missing it, right? No, you're probably not missing it. It's complaining that a function with the given signature (text, unknown), doesn't exist. It will work if it matches (bytea, name). The error message being returned is saying that the msg field is actually a text field. Try: SELECT pg_typeof(msg) FROM sip_trace LIMIT 1; Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] Displaying text appears as hex data
On 8 February 2011 13:43, Thom Brown t...@linux.com wrote: On 8 February 2011 13:19, Michael postgre...@encambio.com wrote: Hello Thom, On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 12:45, Michael postgre...@encambio.com wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 10:39, Michael wrote: opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. opensips= SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; ERROR: function convert_from(text, unknown) does not exist LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg... ^ The arrow in the last line indicates that 'convert_from' is not correctly parsed. My understanding was that your msg column was of type bytea. Is this not the case? Or is it a different column which needs converting? The main developer has confirmed that the msg column is stored as a BLOB, and I'm nearly sure that bytea is used in this case. Yes, the correct column that is appearing in hex is called 'msg.' Well the function seems to think it's a text field. Is the function 'convert_from' that you mentioned compiled into the PostgreSQL server binary, or is it part of the template1 when first created, or something else? It seems I'm missing it, right? No, you're probably not missing it. It's complaining that a function with the given signature (text, unknown), doesn't exist. It will work if it matches (bytea, name). The error message being returned is saying that the msg field is actually a text field. Try: SELECT pg_typeof(msg) FROM sip_trace LIMIT 1; Or if it really is text format: SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; But then that's less efficient than storing it as bytea and slower to query. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] Displaying text appears as hex data
Hello Karsten, On Tues., Feb 08, 2011, Karsten Hilbert wrote: On Tue, Feb 08, 2011 at 02:19:49PM +0100, Michael wrote: The arrow in the last line indicates that 'convert_from' is not correctly parsed. My understanding was that your msg column was of type bytea. Is this not the case? Or is it a different column which needs converting? The main developer has confirmed that the msg column is stored as a BLOB, That is not really possible since PostgreSQL does not have a BLOB data type as such. It would either be a BYTEA column or a Large Object (which would live elsewhere and not inside the table you work on). and I'm nearly sure that bytea is used in this case. Very likely. Okay, after looking at the sources I can confirm that BLOB maps indeed to the 'bytea' PostgreSQL data type. Regards, Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] When will old wal segments get removed?
hi we had a problem with archiving. so pg_xlog accumulated more wal segments. archive_command was modified to version that doesn't fail, and it is working ok. but old segments are still in pg_xlog. they are way past anything that could be even remotely needed: # g_controldata . pg_control version number:843 Catalog version number: 200904091 Database system identifier: 5429353595627434591 Database cluster state: in production pg_control last modified: February 8, 2011 2:01:46 PM GMT Latest checkpoint location: 76E/A607B0B8 Prior checkpoint location:76E/92057108 Latest checkpoint's REDO location:76E/93004FE8 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/47225854 Latest checkpoint's NextOID: 66603928 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint:February 8, 2011 1:57:16 PM GMT Minimum recovery ending location: 0/0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value and pg_xlog/ has: $ ls -l pg_xlog/ | head total 2620721 -rw--- 1 postgres postgres 249 Sep 8 20:14 000103DB003E.61A8.backup -rw--- 1 postgres postgres 16777216 Feb 8 11:39 0001076D00F7 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00F8 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00F9 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00FA -rw--- 1 postgres postgres 16777216 Feb 8 11:41 0001076D00FB -rw--- 1 postgres postgres 16777216 Feb 8 11:42 0001076D00FC -rw--- 1 postgres postgres 16777216 Feb 8 11:43 0001076D00FD -rw--- 1 postgres postgres 16777216 Feb 8 11:44 0001076D00FE they are archived: $ ls -l pg_xlog/archive_status/ | head total 179 -rw--- 1 postgres postgres 0 Sep 8 20:14 000103DB003E.61A8.backup.done -rw--- 1 postgres postgres 0 Feb 8 11:39 0001076D00F7.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00F8.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00F9.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00FA.ready -rw--- 1 postgres postgres 0 Feb 8 11:41 0001076D00FB.ready -rw--- 1 postgres postgres 0 Feb 8 11:42 0001076D00FC.ready -rw--- 1 postgres postgres 0 Feb 8 11:43 0001076D00FD.ready -rw--- 1 postgres postgres 0 Feb 8 11:44 0001076D00FE.ready . checkpoint_segments is 30: $ psql -c 'show checkpoint_segments' checkpoint_segments - 30 (1 row) what can I do to make them go away? tried select pg_switch_xlog(), but it was just allocating new wal segments. version of pg is 8.4.2. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] Displaying text appears as hex data
Hello Thom, On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 13:43, Thom Brown wrote: On 8 February 2011 13:19, Michael wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 12:45, Michael wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 10:39, Michael wrote: opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. opensips= SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; ERROR: function convert_from(text, unknown) does not exist LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg... ^ The arrow in the last line indicates that 'convert_from' is not correctly parsed. My understanding was that your msg column was of type bytea. Is this not the case? Or is it a different column which needs converting? The main developer has confirmed that the msg column is stored as a BLOB, and I'm nearly sure that bytea is used in this case. Yes, the correct column that is appearing in hex is called 'msg.' Well the function seems to think it's a text field. Okay, maybe I missed something and thought it was a bytea. Is the function 'convert_from' that you mentioned compiled into the PostgreSQL server binary, or is it part of the template1 when first created, or something else? It seems I'm missing it, right? No, you're probably not missing it. It's complaining that a function with the given signature (text, unknown), doesn't exist. It will work if it matches (bytea, name). The error message being returned is saying that the msg field is actually a text field. Try: SELECT pg_typeof(msg) FROM sip_trace LIMIT 1; opensips= SELECT pg_typeof(msg) FROM sip_trace LIMIT 1; pg_typeof --- text (1 row) ...so you are right. Its not a bytea after all. Or if it really is text format: SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; That worked very well, thanks. Now that I can read the text, I see that it is very poorly formatted. I'll write about that problem in another email. But then that's less efficient than storing it as bytea and slower to query. I'll pass that on to the OpenSIPS database developer, thanks. Regards, Michael -- 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] When will old wal segments get removed?
On 8 February 2011 14:07, hubert depesz lubaczewski dep...@depesz.com wrote: hi we had a problem with archiving. so pg_xlog accumulated more wal segments. archive_command was modified to version that doesn't fail, and it is working ok. but old segments are still in pg_xlog. they are way past anything that could be even remotely needed: # g_controldata . pg_control version number: 843 Catalog version number: 200904091 Database system identifier: 5429353595627434591 Database cluster state: in production pg_control last modified: February 8, 2011 2:01:46 PM GMT Latest checkpoint location: 76E/A607B0B8 Prior checkpoint location: 76E/92057108 Latest checkpoint's REDO location: 76E/93004FE8 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/47225854 Latest checkpoint's NextOID: 66603928 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint: February 8, 2011 1:57:16 PM GMT Minimum recovery ending location: 0/0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value and pg_xlog/ has: $ ls -l pg_xlog/ | head total 2620721 -rw--- 1 postgres postgres 249 Sep 8 20:14 000103DB003E.61A8.backup -rw--- 1 postgres postgres 16777216 Feb 8 11:39 0001076D00F7 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00F8 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00F9 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00FA -rw--- 1 postgres postgres 16777216 Feb 8 11:41 0001076D00FB -rw--- 1 postgres postgres 16777216 Feb 8 11:42 0001076D00FC -rw--- 1 postgres postgres 16777216 Feb 8 11:43 0001076D00FD -rw--- 1 postgres postgres 16777216 Feb 8 11:44 0001076D00FE they are archived: $ ls -l pg_xlog/archive_status/ | head total 179 -rw--- 1 postgres postgres 0 Sep 8 20:14 000103DB003E.61A8.backup.done -rw--- 1 postgres postgres 0 Feb 8 11:39 0001076D00F7.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00F8.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00F9.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00FA.ready -rw--- 1 postgres postgres 0 Feb 8 11:41 0001076D00FB.ready -rw--- 1 postgres postgres 0 Feb 8 11:42 0001076D00FC.ready -rw--- 1 postgres postgres 0 Feb 8 11:43 0001076D00FD.ready -rw--- 1 postgres postgres 0 Feb 8 11:44 0001076D00FE.ready . checkpoint_segments is 30: $ psql -c 'show checkpoint_segments' checkpoint_segments - 30 (1 row) what can I do to make them go away? tried select pg_switch_xlog(), but it was just allocating new wal segments. version of pg is 8.4.2. Well normally those would get deleted automatically after archiving, but since you're suggesting the previous archive_command didn't return a zero exit status, that process wouldn't have happened. If you're sure those are archived, can't you just go ahead and delete them manually? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] When will old wal segments get removed?
On Tue, Feb 08, 2011 at 02:21:08PM +, Thom Brown wrote: $ ls -l pg_xlog/ | head total 2620721 -rw--- 1 postgres postgres 249 Sep 8 20:14 000103DB003E.61A8.backup -rw--- 1 postgres postgres 16777216 Feb 8 11:39 0001076D00F7 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00F8 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00F9 -rw--- 1 postgres postgres 16777216 Feb 8 11:40 0001076D00FA -rw--- 1 postgres postgres 16777216 Feb 8 11:41 0001076D00FB -rw--- 1 postgres postgres 16777216 Feb 8 11:42 0001076D00FC -rw--- 1 postgres postgres 16777216 Feb 8 11:43 0001076D00FD -rw--- 1 postgres postgres 16777216 Feb 8 11:44 0001076D00FE they are archived: $ ls -l pg_xlog/archive_status/ | head total 179 -rw--- 1 postgres postgres 0 Sep 8 20:14 000103DB003E.61A8.backup.done -rw--- 1 postgres postgres 0 Feb 8 11:39 0001076D00F7.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00F8.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00F9.ready -rw--- 1 postgres postgres 0 Feb 8 11:40 0001076D00FA.ready -rw--- 1 postgres postgres 0 Feb 8 11:41 0001076D00FB.ready -rw--- 1 postgres postgres 0 Feb 8 11:42 0001076D00FC.ready -rw--- 1 postgres postgres 0 Feb 8 11:43 0001076D00FD.ready -rw--- 1 postgres postgres 0 Feb 8 11:44 0001076D00FE.ready Well normally those would get deleted automatically after archiving, but since you're suggesting the previous archive_command didn't return a zero exit status, that process wouldn't have happened. If you're sure those are archived, can't you just go ahead and delete them manually? right now archiving works, and was called for all older wal segments - so we can see it in archive_status/. I probably could remove them by hand, but I never feel ok to do stuff like this manually, and what's more - i'd prefer to understand why these are not getting removed. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] Displaying text appears as hex data
Hello Thom, On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 13:43, Thom Brown wrote: On 8 February 2011 13:19, Michael wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 12:45, Michael wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 10:39, Michael wrote: opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; opensips= SELECT id, time_stamp, callid, traced_user, \ convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; id | time_stamp | callid | traced_user | convert_from | method ---+-+---+-+-+ 30772 | 2011-02-08 15:04:34 | ab19ccbd8120-z92f7ean9o9c | | INVITE sip:n...@name.host.tld;user=phone SIP/2.0\r +| INVITE | | | | Via: SIP/2.0/TLS 192.168.100.123:1234;branch=asd84bK-rr8scd0jteop;rport\r +| | | | | From: Username Person One sip:proxyu...@name.host.tld;tag=thefromtag12\r +| There's a ton of blanks after '\r' the carriage returns. Is there any way to construct a SELECT statement using filters or something that will neatly format the msg field? Is there some stream editor similar function? SELECT delwhitespace(s/convert_from(msg::bytea, 'SQL_ASCII'::name)) Sorry to be pesky, since you already answered the original question. Thanks again for doing that. Regards, Michael -- 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] Displaying text appears as hex data
On 8 February 2011 14:30, Michael postgre...@encambio.com wrote: Hello Thom, On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 13:43, Thom Brown wrote: On 8 February 2011 13:19, Michael wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 12:45, Michael wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 10:39, Michael wrote: opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; opensips= SELECT id, time_stamp, callid, traced_user, \ convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; id | time_stamp | callid | traced_user | convert_from | method ---+-+---+-+-+ 30772 | 2011-02-08 15:04:34 | ab19ccbd8120-z92f7ean9o9c | | INVITE sip:n...@name.host.tld;user=phone SIP/2.0\r +| INVITE | | | | Via: SIP/2.0/TLS 192.168.100.123:1234;branch=asd84bK-rr8scd0jteop;rport\r +| | | | | From: Username Person One sip:proxyu...@name.host.tld;tag=thefromtag12\r +| There's a ton of blanks after '\r' the carriage returns. Is there any way to construct a SELECT statement using filters or something that will neatly format the msg field? Is there some stream editor similar function? SELECT delwhitespace(s/convert_from(msg::bytea, 'SQL_ASCII'::name)) Sorry to be pesky, since you already answered the original question. Thanks again for doing that. Well, you could always try: SELECT translate(convert_from(msg::bytea, 'SQL_ASCII'::name), E'\r\n', ''); -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql - recovery.conf
Hi, In file recovery.conf I can define recovery_target_time or recovery_target_xid. I have question where I cand found this information? For@ll -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] about PostgreSQL 9.0.3 RPMs
Hi, I searched PostgreSQL 9.0.3 RPMs for RHEL5, but couldn't find them. I need to them in my business. They are not yet on the following page. http://yum.pgrpms.org/9.0/redhat/rhel-5Server-x86_64/ When is they available? regards, -- OTSUKA Kenji NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about database configuration
Hello We have a database running on Windows Server 2008 standard 32bit using Postgres 8.3 If I run a specific query on this database it takes about 4 seconds If I do explain analyze on the query it takes about 17 seconds and I get this result in the beginning Unique (cost=43820.39..43822.51 rows=47 width=81) (actual time=15810.309..15920.974 rows=2548 loops=1) - Sort (cost=43820.39..43820.51 rows=47 width=81) (actual time=15810.295..15857.082 rows=17930 loops=1) Sort Key: com.commseqno, ((subplan)) Sort Method: external sort Disk: 2928kB - Nested Loop Left Join (cost=404.24..43819.08 rows=47 width=81) (actual time=42.088..15422.206 rows=17930 loops=1) - Hash Left Join (cost=404.24..21797.89 rows=7 width=81) (actual time=41.298..784.648 rows=2627 loops=1) Hash Cond: (com.commseqno = com2.parentseqno) So ok I guess I have to add some more work_mem to be able to quicksort using RAM to speed this query up a bit? Now I made a backup of this database and restored it on a Windows Server 2008 standard 64 bit running Postgres 9.0 64bit On this server the postgres.conf file is pretty much the same as 8.3 except that I have increased the work_mem to 8MB my settings are shared_buffers = 512MB work_mem = 8MB maintenance_work_mem = 16MB wal_buffers = 16MB effective_cache_size = 1500MB Now if I run the same query on this database it takes 8 seconds (instead of 4 seconds on version 8.3) And if I do explain analyze on the query it takes about 100 seconds and the output looks completely different HashAggregate (cost=8427.62..8713.00 rows=1 width=81) (actual time=112332.799..112338.177 rows=2548 loops=1) - Nested Loop Left Join (cost=513.32..8427.58 rows=1 width=81) (actual time=24.545..112233.491 rows=17929 loops=1) Join Filter: (com.commseqno = com2.parentseqno) - Nested Loop Left Join (cost=513.32..7994.81 rows=1 width=77) (actual time=16.160..485.733 rows=17908 loops=1) - Nested Loop (cost=513.32..7994.09 rows=1 width=77) (actual time=16.144..370.642 rows=2626 loops=1) What could it be that I have not set up correctly for this to work as on the 8.3 version? If it mattars I have run all queries using pgAdmin 1.12.1 Thanks /Niklas
[GENERAL] Size of varchar in an array
I'm using the following type definition: create type typedef.BASIC_PEDIGREE as ( DATE_ADDED TIMESTAMP, DESCRIPTION VARCHAR(128) [10] )\c I understand that PostgreSQL doesn't enforce the array length [10]. However, I'd like to write an application that can retrieve this length AND the size of the varchar that makes up the array. I can't find these values anyplace in the various system catalogs or views. Does PostgreSQL keep this information someplace and if so where? If there's no way to retrieve (say) the max size of the varchar, does this mean that PostgreSQL does not enforce the max size of this field as well as not enforcing the array bounds?
Re: [GENERAL] Displaying text appears as hex data
Hello Thom, On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 14:30, Michael postgre...@encambio.com wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 13:43, Thom Brown wrote: On 8 February 2011 13:19, Michael wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 12:45, Michael wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 10:39, Michael wrote: opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... Others have said that when they use MySQL, the exact SQL command as above results in ASCII text rather than hexadecimal, and this is my goal as well. SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; opensips= SELECT id, time_stamp, callid, traced_user, \ convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; id | time_stamp | callid | traced_user | convert_from | method ---+-+---+-+-+ 30772 | 2011-02-08 15:04:34 | ab19ccbd8120-z92f7ean9o9c | | INVITE sip:n...@name.host.tld;user=phone SIP/2.0\r +| INVITE | | | | Via: SIP/2.0/TLS 192.168.100.123:1234;branch=asd84bK-rr8scd0jteop;rport\r +| | | | | From: Username Person One sip:proxyu...@name.host.tld;tag=thefromtag12\r +| There's a ton of blanks after '\r' the carriage returns. Is there any way to construct a SELECT statement using filters or something that will neatly format the msg field? Is there some stream editor similar function? SELECT delwhitespace(s/convert_from(msg::bytea, 'SQL_ASCII'::name)) Sorry to be pesky, since you already answered the original question. Thanks again for doing that. Well, you could always try: SELECT translate(convert_from(msg::bytea, 'SQL_ASCII'::name), E'\r\n', ''); Thanks alot that really helps, I'll figure the rest out from here. Regards, Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperl.dll on windows with postgresql 9.0.3
I am upgrading a Windows install from 8.2.x to 9.0.3. ActivePerl 5.8 was already installed, but when I run the createlang command to install into my database, I get... could not load library “C:/Program Files/PostgreSQL/8.3/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 I see the plperl.dll in that very location in the error. I looked at the functions of pg_catalog and the plperl functions are not there like plpgsql call handler, etc. I thought this was due to the older version of ActivePerl, so I uninstall Postgres and ActivePerl, restarted, downloaded and installed latest ActivePerl 5.12, restarted and re-installed Postgres, and restarted yet again to still get the error. I did a search and find perhaps 5.12 does not work (at least during beta)? http://postgresql.1045698.n5.nabble.com/BUG-5601-cannot-create-language-plperl-td2264970.html I am installing on Windows 2003 server. Unfortunately I never installed Postgres on Windows prior to the pginstaller, so I'm not sure what else needs to be done for PostgreSQL to find the module. Since I see the module dll in the lib folder, I assume a separate package is not needed like I am accustomed to under BSD, is that right and it should find without further config or installs? Thanks. -- Robert rob...@webtent.org -- 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] plperl.dll on windows with postgresql 9.0.3
On 2/8/2011 11:58 AM, Robert Fitzpatrick wrote: could not load library “C:/Program Files/PostgreSQL/8.3/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 Sorry the correct error I am getting is... could not load library “C:/Program Files/PostgreSQL/9.0/lib/plperl.dll”: The specified module could not be found. SQL state: 58P01 The previous was copied from a post found on the net when searching for solutions. Thanks for any help! -- Robert rob...@webtent.org -- 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] Maintenance commands on standby servers
On Tue, 2011-02-08 at 21:05 +0900, Fujii Masao wrote: On Tue, Feb 8, 2011 at 4:04 AM, Sylvain Rabot sylv...@abstraction.fr wrote: Is it possible to run maintenance commands like ANALYZE, VACUUM, CLUSTER on a standby server ? No. Since the effect of the maintenance command on the primary server is also replicated, you don't need to do that on the standby. Regards, Thank you for your explanation. -- Sylvain Rabot sylv...@abstraction.fr signature.asc Description: This is a digitally signed message part
[GENERAL] Backup/Restore Needed for Upgrade from 9.0beta4?
From section 15.4 of the manual: If you are upgrading from PostgreSQL 9.0.x, the new version can use your current data files so you should skip the backup and restore steps Is 9.0beta4 considered a 9.0.x version, or do I need to backup/restore when upgrading from that version? Thanks much- Lee -- 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] Displaying text appears as hex data
Michael postgre...@encambio.com wrote: Hello list, I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: $ TERM=vt100 /pfx/bin/psql opensips opensips psql (9.0.2) Type help for help. opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... set: bytea_output = 'escape' Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Displaying text appears as hex data
On 8 February 2011 18:45, Andreas Kretschmer akretsch...@spamfence.net wrote: Michael postgre...@encambio.com wrote: Hello list, I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: $ TERM=vt100 /pfx/bin/psql opensips opensips psql (9.0.2) Type help for help. opensips= select * from sip_trace; id | time_stamp | callid | traced_user | msg | method | ... 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... set: bytea_output = 'escape' That won't help as the msg column is actually text... for some reason. And they want to see the converted ASCII text based on the hex-represented binary data. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Permission denied error - best way to fix?
Here's the error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for relation pantryitems pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE Does the user need to be a superuser, or is there some way to GRANT this permission (if possible, I don't want this user to be able to modify the schema).. Mike -- 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] Displaying text appears as hex data
Thom Brown t...@linux.com wrote: On 8 February 2011 18:45, Andreas Kretschmer akretsch...@spamfence.net wrote: Michael postgre...@encambio.com wrote: Hello list, I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: set: bytea_output = 'escape' That won't help as the msg column is actually text... for some reason. Are you sure? I know that problem from DRUPAL with 9.0. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Displaying text appears as hex data
On 8 February 2011 19:28, Andreas Kretschmer akretsch...@spamfence.net wrote: Thom Brown t...@linux.com wrote: On 8 February 2011 18:45, Andreas Kretschmer akretsch...@spamfence.net wrote: Michael postgre...@encambio.com wrote: Hello list, I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: set: bytea_output = 'escape' That won't help as the msg column is actually text... for some reason. Are you sure? I know that problem from DRUPAL with 9.0. I asked Michael to confirm the column type of msg and it turned out to be text. No mention of the lo contrib module, so I guess that may possibly come into play, but I wouldn't know about that. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] many schemas or many databases
Il 08/02/2011 10.42, Pavel Stehule ha scritto: Hi 2011/2/8 Thomas Markus t.mar...@proventis.net: hi, i would prefer many schemas. advantages: - one backup/restore for all (or selective) But this also means if one crashes all crash. And lack of flexibility in deployments. It heavily depends in what grade of independence you want among the applications. -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it == -- 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] CRUD functions, similar to SQL stored procedurs, for postgresql tables?
I see nothing tricky in your CRUDdy procedures. I would think porting the sql-generator would be pretty straight forward. Except for the names and a few other changes, the story's the same one.: read the system catalogues and generate your procs-cum-functions. Your jdbc interactions should turn out largely unchanged if the names of the routines are directly transferable. Has this approach failed? Or have I mis-understood? The SQLgenerator software was written as a Windows program for MS SQLserver only and my company purchased the software. I was trying to find if there is some similar utility for Postgresql that would read the table structures, write the CRUD / DML functions, and then add the functions to the database. That way all I would have to do is customize the functions after they were written. The data I am porting is from very old software, pre 1995 DOS format in xbase tables and flat files. We are writing small scripts in whatever works to push it into postgresql. There is no Java layer. I have found an example of what a postgresql function would look like that does an insert. Until today I had not been able to find insert function examples. This site has a create / update function: http://stackoverflow.com/questions/2102613/postgresql-insert-that-depends-on-data-in-another-table-best-practice This site has a script that says it will CRUD generate functions but I just found it today and haven't tried it yet, it is tested for postgresql versions 8.1.9 and 7.4.18 and seems to be what I was looking for. http://myleshenderson.com/index.php/2007/11/16/postgresql-insert-function-generator The introduction to this script reads: PostgreSQL Insert Function Generator. I like the plpgsql procedural language for PostgreSQL more than it is appropriate to like a procedural language. I find the reward:effort ratio to be quite high. Since one can do so many cool things with it, why waste time writing boring insert functions for tables? Here’s my plpgsql function which generates the create function statements for tables in a database. I don't see a reference to this site in the maillist archives and wonder if anyone has used it or has found something similar posted for 9.1? Thanks, MargaretThis e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain company proprietary, privileged or confidential information. If you are not the intended recipient(s), please contact the sender by reply e-mail, advise them of the error and destroy this message and its attachments as well as any copies. The review, use or distribution of this message or its content by anyone other than the intended recipient or senior management of the company is strictly prohibited. -- 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] Permission denied error - best way to fix?
2011/2/8, Mike Christensen m...@kitchenpc.com: Here's the error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for relation pantryitems pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE Does the user need to be a superuser, or is there some way to GRANT this permission (if possible, I don't want this user to be able to modify the schema).. this lock need only SELECT privilege. pasman -- 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] Permission denied error - best way to fix?
On 02/08/2011 10:57 AM, Mike Christensen wrote: Here's the error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for relation pantryitems pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE Does the user need to be a superuser, or is there some way to GRANT this permission (if possible, I don't want this user to be able to modify the schema).. Mike pg_dump is an admin function so it will need to run as a role that can touch all requested objects and take action. For this particular case you could GRANT SELECT on that table to the restricted user. The trouble being is that this turns into a game of Whack-a-Mole fairly quickly. This is one of those situations where taken a long view on your permissions scheme will pay dividends. -- 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
Re: [GENERAL] Displaying text appears as hex data
Thom Brown t...@linux.com wrote: On 8 February 2011 19:28, Andreas Kretschmer akretsch...@spamfence.net wrote: Thom Brown t...@linux.com wrote: On 8 February 2011 18:45, Andreas Kretschmer akretsch...@spamfence.net wrote: Michael postgre...@encambio.com wrote: Hello list, I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: set: bytea_output = 'escape' That won't help as the msg column is actually text... for some reason. Are you sure? I know that problem from DRUPAL with 9.0. I asked Michael to confirm the column type of msg and it turned out to be text. No mention of the lo contrib module, so I guess that may possibly come into play, but I wouldn't know about that. Okay, you are right, i haven't read the other sub-thread, sorry. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] plperl.dll on windows with postgresql 9.0.3
I don't use pl/perl, but I've seen a very similar could not load library error with pl/python on Windows, even though the dll named was present. Windows gives very bad error messages when it fails to dynamically link. You could use something like dependency walker, which is probably a pain. I think that plperl.dll simply wants to link to a major version of perl that you don't have. It's a matter of finding out which and installing it. -- Regards, Peter Geoghegan -- 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] many schemas or many databases
Szymon Guz, 08.02.2011 09:30: Hi, is there any noticeable difference between a cluster with many databases and a database with many schemas? I've got a quite huge database on Oracle with about 400 logically disjoint schemas. I could import that into PostgreSQL as many different databases, or as one database with many schemas. From the application point of view it could be easier to have different databases, as for now the applications log in into different schemas, so this behavior wouldn't change. Do you see any drawbacks of any of the solutions? I think the question is: do you have queries that retrieve data from different schemas in Oracle? If so then the only way to go in PostgreSQL is to use multiple schemas. If you don't need cross-schema/database queries then I don't think there is none of the solution is particular better than the other. Both have advantages and disadvantages (as described by the other posters) Regards Thomas -- 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] Displaying text appears as hex data
On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 19:28, Andreas Kretschmer akretsch...@spamfence.net wrote: Thom Brown t...@linux.com wrote: On 8 February 2011 18:45, Andreas Kretschmer akretsch...@spamfence.net wrote: Michael postgre...@encambio.com wrote: I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: set: bytea_output = 'escape' That won't help as the msg column is actually text... for some reason. Are you sure? I know that problem from DRUPAL with 9.0. I asked Michael to confirm the column type of msg and it turned out to be text. No mention of the lo contrib module, so I guess that may possibly come into play, but I wouldn't know about that. By the way if the data is indeed properly stored as text in the database, why isn't it printed as text when using a plain SELECT? The text data is printed as binary hex instead, what?! ...but the conversion works of course. Regards, Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] No Password Access
Today on a new PostgreSQL 9.0.3 server I created a new user:
CREATE ROLE carlos LOGIN CREATEDB CREATEROLE;
CREATE ROLE
I then set a password and comment on the user:
ALTER ROLE carlos WITH PASSWORD 'letmein';
ALTER ROLE
COMMENT ON ROLE carlos IS 'Database Administrator';
COMMENT
So I now try to connect to the database from my desk workstation:
carlos@laptop:~$ psql -h db1 -d postgres
psql (8.4.7, server 9.0.3)
WARNING: psql version 8.4, server version 9.0.
Some psql features might not work.
Type help for help.
postgres= SELECT current_user;
current_user
--
carlos
(1 row)
postgres= \du
List of roles
Role name | Attributes | Member of
---+-+---
carlos| Create role | {}
: Create DB
maggie| Create DB | {}
postgres | Superuser | {}
: Create role
: Create DB
Why am I not prompted for a password when I connect from my laptop to
the server? I didn't grant attributes of 'INHERIT' to carlos so
shouldn't I be prompted for my password? Am I missing something here?
--
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] Displaying text appears as hex data
On 8 February 2011 22:27, Michael postgre...@encambio.com wrote: On Tues., Feb 08, 2011, Thom Brown wrote: On 8 February 2011 19:28, Andreas Kretschmer akretsch...@spamfence.net wrote: Thom Brown t...@linux.com wrote: On 8 February 2011 18:45, Andreas Kretschmer akretsch...@spamfence.net wrote: Michael postgre...@encambio.com wrote: I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) as BLOB and PostgreSQL is displaying it in hex format like so: set: bytea_output = 'escape' That won't help as the msg column is actually text... for some reason. Are you sure? I know that problem from DRUPAL with 9.0. I asked Michael to confirm the column type of msg and it turned out to be text. No mention of the lo contrib module, so I guess that may possibly come into play, but I wouldn't know about that. By the way if the data is indeed properly stored as text in the database, why isn't it printed as text when using a plain SELECT? The text data is printed as binary hex instead, what?! ...but the conversion works of course. That's why I'm a bit miffed, because you've stored a textual representation of the binary data in hex. In other words, when it says '\x494e56495445207369703a', it's stored as that bit of text. It's not outputting a hexadecimal representation of the data, the hexadecimal representation is the actual data. So you could have '\x494ePostgreSQL rules', which obviously wouldn't be valid for casting to a bytea, but it's perfectly valid text. The question is, how is that data getting in there? There's probably binary data with a ::text conversion going on during insert. What you appear to want is the original textual representation going in as a text field since you're converting all the time during the SELECT, but it's being put into binary then cast to text before getting to your table. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] Permission denied error - best way to fix?
Here's the error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for relation pantryitems pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE Does the user need to be a superuser, or is there some way to GRANT this permission (if possible, I don't want this user to be able to modify the schema).. Mike pg_dump is an admin function so it will need to run as a role that can touch all requested objects and take action. For this particular case you could GRANT SELECT on that table to the restricted user. The trouble being is that this turns into a game of Whack-a-Mole fairly quickly. This is one of those situations where taken a long view on your permissions scheme will pay dividends. Hmm interesting, I wonder if this error is happening because pg_dump is being run from another program (RubyMine) and the process doesn't have whatever access it needs. -- 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] Permission denied error - best way to fix?
On 02/08/11 2:44 PM, Mike Christensen wrote: Here's the error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for relation pantryitems pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE Does the user need to be a superuser, or is there some way to GRANT this permission (if possible, I don't want this user to be able to modify the schema).. Mike pg_dump is an admin function so it will need to run as a role that can touch all requested objects and take action. For this particular case you could GRANT SELECT on that table to the restricted user. The trouble being is that this turns into a game of Whack-a-Mole fairly quickly. This is one of those situations where taken a long view on your permissions scheme will pay dividends. Hmm interesting, I wonder if this error is happening because pg_dump is being run from another program (RubyMine) and the process doesn't have whatever access it needs. its definately a postgres permissions error.I'd verify the pg user credentials being passed to pg_dump. if no user or anything is being specified, then its likely inheriting the unix username that the parent process runs as. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
Hello all,
I have set up PostgreSQL Streaming Replication and all seems to work fine when
updating records as the records are instantaneously updated on the slave,
however, I was wondering perhaps if someone can give me some verification that
what I am doing is alright or some more insight into what I am doing. Perhaps
this will also help others in the future.
First on the master, I have the following in /var/lib/pgsql/data/standby.sh:
#!/bin/sh
LOG_FILE=/tmp/postgres_wal_archiving.log
log() { echo `date --rfc-3339=ns` $1 $LOG_FILE; }
log_error() { echo `date --rfc-3339=ns` $1 $LOG_FILE; exit 1; }
wal_path=$1
wal_file=$2
backup_server=slave01
remote_archive=/var/lib/pgsql/walfiles/$wal_file
log Transfering file to backup server, filename: $wal_file
rsync $wal_path $backup_server:$remote_archive
if [ $? -eq 0 ]; then
log Transfer to slave server completed
else
log_error Sending $wal_file failed.
fi
On the slave, I create the directory /var/lib/pgsql/walfiles (remote_archive)
for the script to copy the walfiles over to.
Then, within the master's postgresql.conf I have:
wal_level = hot_standby
archive_mode = on
archive_command = '/var/lib/pgsql/data/standby.sh %p %f /dev/null'# The
same script as above
archive_timeout = 30
max_wal_senders = 5
wal_keep_segments = 32
#hot_standby = off
I start up the master server and verify that files are indeed being SCPed over
to /var/lib/pgsql/walfiles (also processes shows: 'archiver process last was
00010003001E').
After starting up on the master, I rsync over the data/ directory to the slave:
/path/to/psql -c SELECT pg_start_backup('label', true)
rsync -avz --delete /var/lib/pgsql/data/ slave01:/var/lib/pgsql/data --exclude
postmaster.pid
/path/to/psql -c SELECT pg_stop_backup()
And I add recovery.conf over on the the slave's data/ directory:
standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=postgres'
trigger_file = '/tmp/trigger'
restore_command='cp /var/lib/pgsql/walfiles/%f %p'
And in the slave's postgresql.conf, I remove the comment on :
hot_standby = on
Upon starting the slave, everything works fine and updates to records occur on
the slave immediately (what is the actual timing for this)?
My confusion is: does streaming replication require WAL archiving as I have
illustrated above or is it a just in case scenario? Also, the restore_command
on the slave - is this correct, assuming that the master is dropping off files
via SCP to /var/lib/pgsql/walfiles ?
Thank you very much
Ogden Nefix
--
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] No Password Access
On Dienstag, 8. Februar 2011, Carlos Mennens wrote: Why am I not prompted for a password when I connect from my laptop to the server? check your pg_hba.conf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recovery with WAL
Where can I find information about recovery_target_time or recovery_target_xid. I have two servers db1 and db2, WAL files are copied from db1 to db2. Database will colapse at 17:10 and i wan't to recove base from 17:05, so where can I find info about recovery_time. Albert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.0.X FOR UPDATE|SHARE on Sub-Query Causes cannot extract system attribute from virtual tuple if Sub-Query Returns Records (BUG)
I actually posted a more detail posting on this issue but I have a lot of extra information that jumbles things up. More simply if you run any query of the form: SELECT subquerycolumn FROM ( SELECT subquerycolumn FROM table WHERE [condition] FOR UPDATE -- WHERE is optional but obviously useful; FOR SHARE also causes this behavior ) intermediate The error cannot extract system attribute from virtual tuple is thrown IIF the sub-query returns one or more records. My prior posting on this topic made it seem as if some environments worked because the query I was testing had a sub-query that was not returning any records. I have tested on both Linux and Windows (both 64-bit) versions of 9.0.3 and get this behavior. Anyway, the above query form works in both 8.2 and 8.4 but fails in 9.0.3 (at least, through probably all 9.0.X releases) I'll ask in the other thread (once it appears) for suggestions as to possible workarounds (and maybe better performing) methods to accomplish my goal but figured the simple query form shown above should readily re-produce the exception on any 9.0.X system. If there is a BUG (or when one is opened) responding to this message with a BUG# would be welcomed. Thanks, David J
[GENERAL] pg_dump: schema with OID 58698 does not exist
howdy all, I'm getting the above error in one of my dev DBs. I've read in the archives that to stop the error from happening I can just delete entries in pg_type and pg_class, however there seemed to be some community interest in doing some debugging. (mentioned in this thread: http://archives.postgresql.org/pgsql-bugs/2010-01/msg00090.php ) This is PG 9.0.2 on RedHat 64 bit. Is there anything you'd like me to check before i fix the problem? (since it's dev, i plan to just wack the database and re-create it.. so i don't need to worry about if that particular object was used or whatever... ) Thanks Dave -- 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_restore validation?
Is there a way we can validate a postgers backup? (short of restoring it somewhere) 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: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On 02/07/2011 06:38 AM, Thom Brown wrote:
On 7 February 2011 09:04, Itagaki Takahiroitagaki.takah...@gmail.com wrote:
On Fri, Feb 4, 2011 at 21:32, Thom Brownt...@linux.com wrote:
The issue is that generate_series will not return if the series hits
either the upper or lower boundary during increment, or goes beyond
it. The attached patch fixes this behaviour, but should probably be
done a better way. The first 3 examples above will not return.
There are same bug in int8 and timestamp[tz] versions.
We also need fix for them.
=# SELECT x FROM generate_series(9223372036854775807::int8,
9223372036854775807::int8) AS a(x);
Yes, of course, int8 functions are separate. I attach an updated
patch, although I still think there's a better way of doing this.
=# SELECT x FROM generate_series('infinity'::timestamp, 'infinity', '1
sec') AS a(x);
=# SELECT x FROM generate_series('infinity'::timestamptz, 'infinity',
'1 sec') AS a(x);
I'm not sure how this should be handled. Should there just be a check
for either kind of infinity and return an error if that's the case? I
didn't find anything wrong with using timestamp boundaries:
postgres=# SELECT x FROM generate_series('1 Jan 4713 BC
00:00:00'::timestamp, '1 Jan 4713 BC 00:00:05'::timestamp, '1 sec') AS
a(x);
x
4713-01-01 00:00:00 BC
4713-01-01 00:00:01 BC
4713-01-01 00:00:02 BC
4713-01-01 00:00:03 BC
4713-01-01 00:00:04 BC
4713-01-01 00:00:05 BC
(6 rows)
Although whether this demonstrates a true timestamp boundary, I'm not sure.
postgres=# SELECT x FROM generate_series(1, 9,-1) AS a(x);
postgres=# SELECT x FROM generate_series(1, 9,3) AS a(x);
They work as expected in 9.1dev.
Those 2 were to demonstrate that the changes don't affect existing
functionality. My previous patch proposal (v2) caused these to return
unexpected output.
Isn't this all really a bug fix that should be backpatched, rather than
a commitfest item?
cheers
andrew
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Wed, Feb 9, 2011 at 10:17, Andrew Dunstan and...@dunslane.net wrote: Isn't this all really a bug fix that should be backpatched, rather than a commitfest item? Sure, but we don't have any bug trackers... -- Itagaki Takahiro -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On 02/08/2011 08:19 PM, Itagaki Takahiro wrote: On Wed, Feb 9, 2011 at 10:17, Andrew Dunstanand...@dunslane.net wrote: Isn't this all really a bug fix that should be backpatched, rather than a commitfest item? Sure, but we don't have any bug trackers... Quite right, but the commitfest manager isn't meant to be a substitute for one. Bug fixes aren't subject to the same restrictions of feature changes. cheers andrew -- 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] Permission denied error - best way to fix?
On Tuesday, February 08, 2011 2:44:51 pm Mike Christensen wrote: Here's the error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: permission denied for relation pantryitems pg_dump: The command was: LOCK TABLE public.pantryitems IN ACCESS SHARE MODE Does the user need to be a superuser, or is there some way to GRANT this permission (if possible, I don't want this user to be able to modify the schema).. Mike pg_dump is an admin function so it will need to run as a role that can touch all requested objects and take action. For this particular case you could GRANT SELECT on that table to the restricted user. The trouble being is that this turns into a game of Whack-a-Mole fairly quickly. This is one of those situations where taken a long view on your permissions scheme will pay dividends. Hmm interesting, I wonder if this error is happening because pg_dump is being run from another program (RubyMine) and the process doesn't have whatever access it needs. Two possibilities, it is being run as the RubyMine user or as the user that is specified in the connection information RubyMine is using to pull and push data to the database. -- 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
Re: [GENERAL] Issues with generate_series using integer boundaries
On Mon, Jan 31, 2011 at 7:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: Actually, those lower bound errors aren't related to generate_series, but I'd still like to know why -2147483648::int4 is out of range. :: binds tighter than - (and everything else too). Write (-2147483648)::int4 instead. That's surprising enough that it might be worth generating a warning if the typecasting operator is used on a mathmatical expression--a - b::int4--rather than a single value (eg. (a - b)::int4 or f()::int4). I don't know the grammar to know if that fits. -- Glenn Maynard
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Tue, Feb 8, 2011 at 8:30 PM, Andrew Dunstan and...@dunslane.net wrote: Quite right, but the commitfest manager isn't meant to be a substitute for one. Bug fixes aren't subject to the same restrictions of feature changes. Another option would be to add this here: http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] PostgreSQL 9.0 Streaming Replication Configuration
pg_controldata command is helpful.
Archiving wal not required, but you can roll it either way.
On Tue, Feb 08, 2011 at 04:46:51PM -0600, Ogden wrote:
Hello all,
I have set up PostgreSQL Streaming Replication and all seems to work fine
when updating records as the records are instantaneously updated on the
slave, however, I was wondering perhaps if someone can give me some
verification that what I am doing is alright or some more insight into what I
am doing. Perhaps this will also help others in the future.
First on the master, I have the following in /var/lib/pgsql/data/standby.sh:
#!/bin/sh
LOG_FILE=/tmp/postgres_wal_archiving.log
log() { echo `date --rfc-3339=ns` $1 $LOG_FILE; }
log_error() { echo `date --rfc-3339=ns` $1 $LOG_FILE; exit 1; }
wal_path=$1
wal_file=$2
backup_server=slave01
remote_archive=/var/lib/pgsql/walfiles/$wal_file
log Transfering file to backup server, filename: $wal_file
rsync $wal_path $backup_server:$remote_archive
if [ $? -eq 0 ]; then
log Transfer to slave server completed
else
log_error Sending $wal_file failed.
fi
On the slave, I create the directory /var/lib/pgsql/walfiles (remote_archive)
for the script to copy the walfiles over to.
Then, within the master's postgresql.conf I have:
wal_level = hot_standby
archive_mode = on
archive_command = '/var/lib/pgsql/data/standby.sh %p %f /dev/null'# The
same script as above
archive_timeout = 30
max_wal_senders = 5
wal_keep_segments = 32
#hot_standby = off
I start up the master server and verify that files are indeed being SCPed
over to /var/lib/pgsql/walfiles (also processes shows: 'archiver process
last was 00010003001E').
After starting up on the master, I rsync over the data/ directory to the
slave:
/path/to/psql -c SELECT pg_start_backup('label', true)
rsync -avz --delete /var/lib/pgsql/data/ slave01:/var/lib/pgsql/data
--exclude postmaster.pid
/path/to/psql -c SELECT pg_stop_backup()
And I add recovery.conf over on the the slave's data/ directory:
standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=postgres'
trigger_file = '/tmp/trigger'
restore_command='cp /var/lib/pgsql/walfiles/%f %p'
And in the slave's postgresql.conf, I remove the comment on :
hot_standby = on
Upon starting the slave, everything works fine and updates to records occur
on the slave immediately (what is the actual timing for this)?
My confusion is: does streaming replication require WAL archiving as I have
illustrated above or is it a just in case scenario? Also, the
restore_command on the slave - is this correct, assuming that the master is
dropping off files via SCP to /var/lib/pgsql/walfiles ?
Thank you very much
Ogden Nefix
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] PostgreSQL 9.0 Streaming Replication Configuration
On Feb 8, 2011, at 8:47 PM, Ray Stell wrote: pg_controldata command is helpful. Archiving wal not required, but you can roll it either way. That is my confusion - Archiving wal does not conflict in any way with streaming replication? What if streaming replication lags behind (especially with a lot of connections). Thank you Ogden -- 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_dump: schema with OID 58698 does not exist
David Kerr d...@mr-paradox.net writes: I'm getting the above error in one of my dev DBs. Would you poke around in the system catalogs and find where the dangling reference is located? Have you got any idea of how to reproduce this failure from a standing start? 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] PostgreSQL 9.0 Streaming Replication Configuration
If the standby server cannot pull the WAL file from the master using streaming replication, then it will attempt to pull it from the archive. If the WAL segment isn't archived (for example because you aren't using archiving), then your streaming replication is unrecoverable and you have to take a fresh backup from the master and transfer it over to the standby machine to start replication again. So the value of having archiving setup is that in case a standby falls way behind, then the standby can recover without having to copy your database over to the standby machine again. Another setting you can tweak is wal_keep_segments on the master machine, which is the minimum numbers of WAL segments it will keep without deleting. So just with some simple math: (wal_keep_segments * 16MB / your_wal_write_rate) you can determine a ballpark of how long your standby machines can fall behind while still being able to recover without archiving. -Dan On Tue, Feb 8, 2011 at 6:51 PM, Ogden li...@darkstatic.com wrote: On Feb 8, 2011, at 8:47 PM, Ray Stell wrote: pg_controldata command is helpful. Archiving wal not required, but you can roll it either way. That is my confusion - Archiving wal does not conflict in any way with streaming replication? What if streaming replication lags behind (especially with a lot of connections). Thank you Ogden -- 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] PostgreSQL 9.0 Streaming Replication Configuration
On Tue, Feb 08, 2011 at 08:51:42PM -0600, Ogden wrote: On Feb 8, 2011, at 8:47 PM, Ray Stell wrote: pg_controldata command is helpful. Archiving wal not required, but you can roll it either way. That is my confusion - Archiving wal does not conflict in any way with streaming replication? What if streaming replication lags behind (especially with a lot of connections). I don't know about the any way deal. The admin cookbook says: There are two main ways to set up streaming replication: with or without an additional archive. Set up without an external archive is presented here, as it is both the most simple and efficient way. There is one downside that suggests the simple approach may not be appropriate for larger databases, explained later in the recipe. It looks like that has to do with the initial backup for building the standby taking to long. -- 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] about PostgreSQL 9.0.3 RPMs
On Tue, 08 Feb 2011 14:23:54 +0200, Devrim GUNDUZ wrote: In the next 2 days. There is an issue in the current RPM buildfarm that resulted in delay. Thank you for your reply. -- OTSUKA Kenji NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] postgis 1.5.2 installation configure: WARNING: could not locate CUnit required for liblwgeom unit tests
On 2011-02-07, at 11:27 AM, Edoardo Panfili wrote: On 07/02/11 18.55, Paul Ramsey wrote: Well, maybe you could in-place upgrade if you left your PostGIS version at the original and only upgraded the PostgreSQL part, but you aren't doing that, you're also upgrading your PostGIS version. pg_dump the database create a new database on the new server, install postgis in it pg_restore the database I am at the beginning with postigis. This is also my way to copy the data from one machine to another. I have the same server in both the machines (postgresql 8.4 + postgis 1.5), no errors during this process. ignore the many errors this sentence scares me a bit (for my future)... what kind of errors? error messages would be a better term. In the procedure above you are installing a database dump, which will naturally include the PostGIS function and type definitions into a database in which you have *already* installed the PostGIS function and type definitions. Naturally you get a lot of noise as the functions in the dump try to install and hit the existing types and functions. But it's sound and fury signifying nothing. P. Edoardo On 2011-02-07, at 9:49 AM, akp geek wrote: Please pardon my ignorance. The reason I am worried about it is, when I tried to use pg_upgrade to migrate from 8.3 to 9.0.2. I am getting an error Failed to load library: $libdir/liblwgeom ERROR: could not access file $libdir/liblwgeom: No such file or directory I am using pg_upgrade for upgrading Regards On Mon, Feb 7, 2011 at 12:39 PM, Paul Ramsey pram...@cleverelephant.ca mailto:pram...@cleverelephant.ca wrote: It's not a dynlib, it's statically linked at build time, so have no fear. Stop thinking so much :) P On 2011-02-07, at 9:38 AM, akp geek wrote: installation was successful. But it did not install the liblwgeom.so Regards On Mon, Feb 7, 2011 at 12:29 PM, Paul Ramsey pram...@cleverelephant.ca mailto:pram...@cleverelephant.ca wrote: It's just a warning, continue happily onwards. Just means a few unit tests won't be run. P. On 2011-02-07, at 9:27 AM, akp geek wrote: Hi All - I am trying to install postgis 1.5.2 on solaris10. When I run the configure I get the following. *configure: WARNING: could not locate CUnit required for liblwgeom unit tests* is there some setting I need to do to make it work? $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... ^C10.112.161.124$ $./configure --prefix=/opt/postgres/gis --with-geosconfig=/opt/postgres/gis/bin/geos-config --with-projdir=/opt/postgres/gis checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/5bin/sed checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep checking for egrep... /usr/sfw/bin/ggrep -E checking for fgrep... /usr/sfw/bin/ggrep -F checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking for BSD- or MS-compatible name lister (nm)... /usr/ccs/bin/nm -p checking the name lister (/usr/ccs/bin/nm -p) interface... BSD nm checking whether ln -s works... yes checking the maximum length of command line arguments... 786240 checking whether the shell understands some XSI constructs... yes checking whether the shell understands +=... no checking for /usr/ccs/bin/ld option to reload object files... -r checking for objdump... no checking how to recognize dependent libraries... pass_all checking for ar... ar checking for strip... strip checking for ranlib... ranlib checking command to parse /usr/ccs/bin/nm -p output from gcc object... ok checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes
Re: [GENERAL] PostgreSQL 9.0 Streaming Replication Configuration
Thank you for letting me know about pg_controldata. I have been playing around
with this tool.
I notice on my master server I have:
Latest checkpoint location: 1E3/F220
Prior checkpoint location:1E3/F120
Latest checkpoint's REDO location:1E3/F220
And on the slave server (where it is archiving to), I have:
Latest checkpoint location: 1E3/EF20
Prior checkpoint location:1E3/EF20
Latest checkpoint's REDO location:1E3/EF20
These are the main differences - should these match or is this a sign of being
too out of sync? How can I best use this tool?
Thank you
Ogden
On Feb 8, 2011, at 8:47 PM, Ray Stell wrote:
pg_controldata command is helpful.
Archiving wal not required, but you can roll it either way.
On Tue, Feb 08, 2011 at 04:46:51PM -0600, Ogden wrote:
Hello all,
I have set up PostgreSQL Streaming Replication and all seems to work fine
when updating records as the records are instantaneously updated on the
slave, however, I was wondering perhaps if someone can give me some
verification that what I am doing is alright or some more insight into what
I am doing. Perhaps this will also help others in the future.
First on the master, I have the following in /var/lib/pgsql/data/standby.sh:
#!/bin/sh
LOG_FILE=/tmp/postgres_wal_archiving.log
log() { echo `date --rfc-3339=ns` $1 $LOG_FILE; }
log_error() { echo `date --rfc-3339=ns` $1 $LOG_FILE; exit 1; }
wal_path=$1
wal_file=$2
backup_server=slave01
remote_archive=/var/lib/pgsql/walfiles/$wal_file
log Transfering file to backup server, filename: $wal_file
rsync $wal_path $backup_server:$remote_archive
if [ $? -eq 0 ]; then
log Transfer to slave server completed
else
log_error Sending $wal_file failed.
fi
On the slave, I create the directory /var/lib/pgsql/walfiles
(remote_archive) for the script to copy the walfiles over to.
Then, within the master's postgresql.conf I have:
wal_level = hot_standby
archive_mode = on
archive_command = '/var/lib/pgsql/data/standby.sh %p %f /dev/null'#
The same script as above
archive_timeout = 30
max_wal_senders = 5
wal_keep_segments = 32
#hot_standby = off
I start up the master server and verify that files are indeed being SCPed
over to /var/lib/pgsql/walfiles (also processes shows: 'archiver process
last was 00010003001E').
After starting up on the master, I rsync over the data/ directory to the
slave:
/path/to/psql -c SELECT pg_start_backup('label', true)
rsync -avz --delete /var/lib/pgsql/data/ slave01:/var/lib/pgsql/data
--exclude postmaster.pid
/path/to/psql -c SELECT pg_stop_backup()
And I add recovery.conf over on the the slave's data/ directory:
standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=postgres'
trigger_file = '/tmp/trigger'
restore_command='cp /var/lib/pgsql/walfiles/%f %p'
And in the slave's postgresql.conf, I remove the comment on :
hot_standby = on
Upon starting the slave, everything works fine and updates to records occur
on the slave immediately (what is the actual timing for this)?
My confusion is: does streaming replication require WAL archiving as I have
illustrated above or is it a just in case scenario? Also, the
restore_command on the slave - is this correct, assuming that the master is
dropping off files via SCP to /var/lib/pgsql/walfiles ?
Thank you very much
Ogden Nefix
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
