Re: [GENERAL] Which version of postgresql supports replication on RHEL6?
[ Please don't do top posting]
20. cd /usr/local/pgsql/
21 . tar -czf data.tar.gzdata/
After 21. step, seems you forgot to execute pg_stop_backup() command.
With this, I would recommend you to follow the documentation given below:
http://wiki.postgresql.org/wiki/Streaming_Replication
Thanks Regards,
Vibhor
On Apr 22, 2011, at 11:08 AM, Tiruvenkatasamy Baskaran wrote:
Hi Vibhor Kumar,
Here is the configuration changes.
Changes made on Master DB
-
1. tar –xzvf postgresql-9.0.4.tar
2. cd postgresql-9.0.4
3. ./configure
4. gmake
5. su
6. gmake install
7. adduser postgres
8. mkdir -p /usr/local/pgsql/data
9. chown -R postgres /usr/local/pgsql
10. su - postgres
11. /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
“Modify the following attributes and their values in postgresql.conf
as follows”
--
12. vi /usr/local/postgres/data/postgresql.conf
listen_addresses = '*'
max_connections = 500
shared_buffers = 32MB
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /usr/local/pgsql/data/pg_xlogarch/%f'
max_wal_senders = 1( this setting for 1 slave can connect with
Master)
wal_keep_segments = 32
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
13. mkdir /usr/local/pgsql/data/pg_xlogarch/
14. su – postgres
15. cd /usr/local/pgsql/
“Modify the following attributes and their values in
pg_hba.conf as follows”
---
16. vi data/pg_hba.conf
hostreplication all
10.128.16.52/32 (slave IPaddress) trust
17. /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
18. cd /usr/local/pgsql/bin/
19. /psql –c SELECT pg_start_backup('label') PICOEMSDB
20. cd /usr/local/pgsql/
21 . tar -czf data.tar.gzdata/
Changes made on Slave DB
-
• tar –xzvf postgresql-9.0.4.tar
• cd postgresql-9.0.4
• ./configure
• gmake
• su
• gmake install
• adduser postgres
• mkdir -p /usr/local/pgsql/data
• chown -R postgres /usr/local/pgsql
• cd /usr/local/pgsql
• Copy the tar file which we have created in master machine to the
respected dir in the slave m/c step 21
• tar xzf data.tar.gz
• chown –R postgres:postgres /usr/local/pgsql/data/pg_xlogarch
• cd data
• rm –rf postmaster.pid
“Modify the following attributes and their values in Postgresql.conf as
follows”
--
• vi /usr/local/pgsql/data/Postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 500 # (change requires restart)
shared_buffers = 32MB # min 128kB
hot_standby = on
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error
message
lc_monetary = 'en_US.UTF-8' # locale for monetary
formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
• create recovery.conf file in the following directory
/usr/local/pgsql/data/
Add the below lines in the recovery.conf file
standby_mode = 'on'
primary_conninfo = 'host=master ip port=5432 user=postgres'
restore_command = 'cp /usr/local/pgsql/data/pg_xlogarch/%f %p'
• /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
Regards,
Tiru
-Original Message-
From: Vibhor Kumar [mailto:vibhor.ku...@enterprisedb.com]
Sent: Thursday, April 21, 2011 6:53 PM
To: Tiruvenkatasamy Baskaran
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Which version of postgresql supports replication on
RHEL6?
On Apr 21, 2011, at 6:35 PM, Tiruvenkatasamy Baskaran wrote:
Got the following messages in log file on the master DB.
LOG: database system was shut down at 2011-04-01 14:27:37 IST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: replication connection authorized: user=postgres host=10.128.16.52
port=52324
cp: cannot stat
Re: [GENERAL] Which version of postgresql supports replication on RHEL6?
On 21/04/2011 14:33, Vibhor Kumar wrote: On Apr 21, 2011, at 4:23 PM, Tiruvenkatasamy Baskaran wrote: Which version of postgresql supports replication on RHEL6? RHEL version : 2.6.32-71.el6.x86_64 Why are you re-posting your question, if it has been answered? Only guessing, but maybe the OP isn't subscribed to this list and his post has only just been moderated? Not that I'm saying that cross-posting is OK either... :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Help - corruption issue?
On Fri, Apr 22, 2011 at 12:06 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Fri, Apr 22, 2011 at 12:51 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 21.4.2011 07:16, Phoenix Kiula napsal(a): Tomas, I did a crash log with the strace for PID of the index command as you suggested. Here's the output: http://www.heypasteit.com/clip/WNR Also including below, but because this will wrap etc, you can look at the link above. Thanks for any ideas or pointers! Process 15900 attached - interrupt to quit Nope, that's the psql process - you need to attach to the backend process that's created to handle the connection. Whenever you create a connection (from a psql), a new backend process is forked to handle that single connection - this is the process you need to strace. You can either see that in 'ps ax' (the PID is usually +1 with respect to the psql process), or you can do this SELECT pg_backend_pid(); as that will give you PID of the backend for the current connection. Thanks. Did that. The crash.log is a large-ish file, about 24KB. Here's the last 10 lines though. Does this help? ~ tail -10 /root/crash.log read(58, `\1\0\0\230\337\0\343\1\0\0\0P\0T\r\0 \3 \374\236\2\2T\215\312\1\354\235\32\2..., 8192) = 8192 write(97, 213.156.60\0\0 \0\0\0\37\0\364P\3\0\34@\22\0\0\000210, 8192) = 8192 read(58, `\1\0\0\274\362\0\343\1\0\0\0T\0\210\r\0 \3 0\217\352\1\240\236\272\0024\235\322\2..., 8192) = 8192 read(58, [\1\0\0\354)c*\1\0\0\0T\0\214\r\0 \3 \254\236\242\2\340\220\342\2\\\235\232\2..., 8192) = 8192 read(58, \\\1\0\0\200\245\207\32\1\0\0\0\\\0\340\r\0 \3 \237\272\1\304\235\262\2\340\215\322\1..., 8192) = 8192 read(58, \350\0\0\0\274\311x\323\1\0\0\0\\\\r\0 \3 \200\236\372\2(\235\252\2\34\234\22\2..., 8192) = 8192 read(58, ;\1\0\0|#\265\30\1\0\0\0`\0h\r\0 \3 \324\236R\2\314\235\n\2h\215\362\1..., 8192) = 8192 read(58, c\1\0\\24%u\1\0\0\0\230\0\210\r\0 \3 \240\226\32\16\260\235\252\1p\222Z\10..., 8192) = 8192 --- SIGSEGV (Segmentation fault) @ 0 (0) --- Process 17161 detached The full crash.log file is here if needed: https://www.yousendit.com/download/ VnBxcmxjNDJlM1JjR0E9PQ Btw, this happens when I try to create an index on one of the columns in my table. Just before this, I had created another index on modify_date (a timestamp column) and it went fine. Does that mean anything? Thanks Probably a dumb and ignorant question, but should I be reseting the xlog? http://postgresql.1045698.n5.nabble.com/SIGSEGV-when-trying-to-start-in-single-user-mode-td1924418.html Nope, that's a different problem I guess - you don't have problems with starting up a database (when the logs are replayed), so this would not help (and it might cause other issues). Anyway I haven't found anything useful in the strace output - it seems it works fine, reads about 500MB (each of the 'read' calls corresponds to 8kB of data) of data and then suddenly ends. A bit strange is the last line is not complete ... Anyway, this is where my current knowledge of how processes in PostgreSQL ends. If I was sitting at the terminal, I'd probably continue by try and error to find out more details about the segfault, but that's not very applicable over e-mail. So let's hope some of the pg gurus who read this list will enlighten us with a bit more knowledge. regards Tomas -- 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] Help - corruption issue?
On Fri, Apr 22, 2011 at 7:07 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 12:06 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Fri, Apr 22, 2011 at 12:51 AM, Tomas Vondra t...@fuzzy.cz wrote: Dne 21.4.2011 07:16, Phoenix Kiula napsal(a): Tomas, I did a crash log with the strace for PID of the index command as you suggested. Here's the output: http://www.heypasteit.com/clip/WNR Also including below, but because this will wrap etc, you can look at the link above. Thanks for any ideas or pointers! Process 15900 attached - interrupt to quit Nope, that's the psql process - you need to attach to the backend process that's created to handle the connection. Whenever you create a connection (from a psql), a new backend process is forked to handle that single connection - this is the process you need to strace. You can either see that in 'ps ax' (the PID is usually +1 with respect to the psql process), or you can do this SELECT pg_backend_pid(); as that will give you PID of the backend for the current connection. Thanks. Did that. The crash.log is a large-ish file, about 24KB. Here's the last 10 lines though. Does this help? ~ tail -10 /root/crash.log read(58, `\1\0\0\230\337\0\343\1\0\0\0P\0T\r\0 \3 \374\236\2\2T\215\312\1\354\235\32\2..., 8192) = 8192 write(97, 213.156.60\0\0 \0\0\0\37\0\364P\3\0\34@\22\0\0\000210, 8192) = 8192 read(58, `\1\0\0\274\362\0\343\1\0\0\0T\0\210\r\0 \3 0\217\352\1\240\236\272\0024\235\322\2..., 8192) = 8192 read(58, [\1\0\0\354)c*\1\0\0\0T\0\214\r\0 \3 \254\236\242\2\340\220\342\2\\\235\232\2..., 8192) = 8192 read(58, \\\1\0\0\200\245\207\32\1\0\0\0\\\0\340\r\0 \3 \237\272\1\304\235\262\2\340\215\322\1..., 8192) = 8192 read(58, \350\0\0\0\274\311x\323\1\0\0\0\\\\r\0 \3 \200\236\372\2(\235\252\2\34\234\22\2..., 8192) = 8192 read(58, ;\1\0\0|#\265\30\1\0\0\0`\0h\r\0 \3 \324\236R\2\314\235\n\2h\215\362\1..., 8192) = 8192 read(58, c\1\0\\24%u\1\0\0\0\230\0\210\r\0 \3 \240\226\32\16\260\235\252\1p\222Z\10..., 8192) = 8192 --- SIGSEGV (Segmentation fault) @ 0 (0) --- Process 17161 detached The full crash.log file is here if needed: https://www.yousendit.com/download/ VnBxcmxjNDJlM1JjR0E9PQ Btw, this happens when I try to create an index on one of the columns in my table. Just before this, I had created another index on modify_date (a timestamp column) and it went fine. Does that mean anything? Thanks Probably a dumb and ignorant question, but should I be reseting the xlog? http://postgresql.1045698.n5.nabble.com/SIGSEGV-when-trying-to-start-in-single-user-mode-td1924418.html Nope, that's a different problem I guess - you don't have problems with starting up a database (when the logs are replayed), so this would not help (and it might cause other issues). Anyway I haven't found anything useful in the strace output - it seems it works fine, reads about 500MB (each of the 'read' calls corresponds to 8kB of data) of data and then suddenly ends. A bit strange is the last line is not complete ... Anyway, this is where my current knowledge of how processes in PostgreSQL ends. If I was sitting at the terminal, I'd probably continue by try and error to find out more details about the segfault, but that's not very applicable over e-mail. So let's hope some of the pg gurus who read this list will enlighten us with a bit more knowledge. regards Tomas In the pg_dumpall backup process, I get this error. Does this help? pg_dump: SQL command failed pg_dump: Error message from server: ERROR: invalid memory alloc request size 4294967293 pg_dump: The command was: COPY public.links (id, link_id, alias, aliasentered, url, user_known, user_id, url_encrypted, title, private, private_key, status, create_date, modify_date, disable_in_statistics, user_running_id, url_host_long) TO stdout; pg_dumpall: pg_dump failed on database snipurl, exiting Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help - corruption issue?
On Fri, Apr 22, 2011 at 7:07 PM, t...@fuzzy.cz wrote: In the pg_dumpall backup process, I get this error. Does this help? Well, not really - it's just another incarnation of the problem we've already seen. PostgreSQL reads the data, and at some point it finds out it needs to allocate 4294967293B of memory. Which is strange, because it's actually a negative number (-3 AFAIK). It's probably caused by data corruption (incorrect length for a field). There are ways to find out more about the cause, e.g. here: http://archives.postgresql.org/pgsql-hackers/2005-10/msg01198.php but you need to have a pg compiled with debug support. I guess the packaged version does not support that, but maybe you can get the sources and compile them on your own. If it really is a data corruption, you might try to locate the corrupted blocks like this: -- get number of blocks SELECT relpages FROM pg_class WHERE relname = 'table_name'; -- get items for each block (read the problematic column) FOR block IN 1..relpages LOOP SELECT AVG(length(colname)) FROM table_name WHERE ctid = '(block,0)'::ctid AND ctid '(block+1,0)'::ctid; and once it fails remember the block ID (and restart - there might be more). regards Tomas -- 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] Help - corruption issue?
On Fri, Apr 22, 2011 at 8:20 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 7:07 PM, t...@fuzzy.cz wrote: In the pg_dumpall backup process, I get this error. Does this help? Well, not really - it's just another incarnation of the problem we've already seen. PostgreSQL reads the data, and at some point it finds out it needs to allocate 4294967293B of memory. Which is strange, because it's actually a negative number (-3 AFAIK). It's probably caused by data corruption (incorrect length for a field). There are ways to find out more about the cause, e.g. here: http://archives.postgresql.org/pgsql-hackers/2005-10/msg01198.php but you need to have a pg compiled with debug support. I guess the packaged version does not support that, but maybe you can get the sources and compile them on your own. If it really is a data corruption, you might try to locate the corrupted blocks like this: -- get number of blocks SELECT relpages FROM pg_class WHERE relname = 'table_name'; -- get items for each block (read the problematic column) FOR block IN 1..relpages LOOP SELECT AVG(length(colname)) FROM table_name WHERE ctid = '(block,0)'::ctid AND ctid '(block+1,0)'::ctid; Thanks for this. Very useful. What is this -- a function? How should I execute this query? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help - corruption issue?
On Fri, Apr 22, 2011 at 8:20 PM, t...@fuzzy.cz wrote: On Fri, Apr 22, 2011 at 7:07 PM, t...@fuzzy.cz wrote: In the pg_dumpall backup process, I get this error. Does this help? Well, not really - it's just another incarnation of the problem we've already seen. PostgreSQL reads the data, and at some point it finds out it needs to allocate 4294967293B of memory. Which is strange, because it's actually a negative number (-3 AFAIK). It's probably caused by data corruption (incorrect length for a field). There are ways to find out more about the cause, e.g. here: http://archives.postgresql.org/pgsql-hackers/2005-10/msg01198.php but you need to have a pg compiled with debug support. I guess the packaged version does not support that, but maybe you can get the sources and compile them on your own. If it really is a data corruption, you might try to locate the corrupted blocks like this: -- get number of blocks SELECT relpages FROM pg_class WHERE relname = 'table_name'; -- get items for each block (read the problematic column) FOR block IN 1..relpages LOOP SELECT AVG(length(colname)) FROM table_name WHERE ctid = '(block,0)'::ctid AND ctid '(block+1,0)'::ctid; Thanks for this. Very useful. What is this -- a function? How should I execute this query? It's a pseudocode - you need to implement that in whatever language you like. You could do that in PL/pgSQL but don't forget it's probably going to crash when you hit the problematic block so I'd probably implement that in outside the DB (with a logic to continue the loop once the connection dies). And 'ctid' is a pseudocolumn that means '(block#, row#)' i.e. it's something like a physical location of the row. regards Tomas -- 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] Different views of remote server
Yes it shows only one server on the remote computer. I can send the table as an sql dump if the list will accept an attachment. The dumped table contains the geom information that I can't see on the remote connection. I restored that same dumped table into a different local database. Somewhere in the restore process those geoms somehow got lost. Taken all together it does seem as if there is something wrong with the geom information. Even to my uneducated eye they look strange compared to the others in the table. Four of the problem fields represent arrow heads. The fifth is a point. Bob -Original Message- From: Adrian Klaver Sent: Thursday, April 21, 2011 7:22 PM To: pgsql-general@postgresql.org Cc: Bob Pawley ; Scott Marlowe Subject: Re: [GENERAL] Different views of remote server On Thursday, April 21, 2011 9:24:57 am Bob Pawley wrote: Hi Scott According to NSAuditor(www.nsauditor.com) there is only one server with port 5432. When I enter information into the remote database it shows up on the same database that has this problem. How do I determine my 'connection credentials'? In pgAdmin they will be in the server properties. Look to see if you are connecting to a port other than 5432. Bob -- 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] converting databases form SQL_ASCII to UTF8
We are moving our databases to new hardware soon, so we felt it would be a good time to get the encoding correct. Our databases are currently SQL_ASCII and we plan to move them to UTF8. So, as previously noted, there are certain characters that won't load into a UTF8 database from a dump of the SQL_ASCII database. Here's our problem. We planned on moving databases a few at a time. Problem is, there is a process that pushes data from one database to another. If this process attempts to push data from a SQL_ASCII database to a new UTF8 database and it has one of these characters mentioned above, the process fails. So, now the question is, is this effort even worth our effort? What is the harm in leaving our databases SQL_ASCII encoded? Thanks for any insights. -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- 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] converting databases form SQL_ASCII to UTF8
On Fri, Apr 22, 2011 at 11:00 AM, Geoffrey Myers li...@serioustechnology.com wrote: Here's our problem. We planned on moving databases a few at a time. Problem is, there is a process that pushes data from one database to another. If this process attempts to push data from a SQL_ASCII database to a new UTF8 database and it has one of these characters mentioned above, the process fails. The database's enforcement of the encoding should be the last layer that does so. Your applications should be enforcing strict utf-8 encoding from start to finish. Once this is done, and the old data already in the DB is properly encoded as utf-8, then there should be no problems switching on the utf-8 encoding in postgres to get that final layer of verification.
Re: [GENERAL] converting databases form SQL_ASCII to UTF8
On Friday, April 22, 2011 8:00:08 am Geoffrey Myers wrote: What is the harm in leaving our databases SQL_ASCII encoded? SQL_ASCII is basically no encoding. The world is slowly but surely moving to Unicode, sooner or later you are going to hit the unknown encoding/Unicode wall. Probably better to get it over with now. Thanks for any insights. -- 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] converting databases form SQL_ASCII to UTF8
On Fri, Apr 22, 2011 at 11:16 AM, Geoffrey Myers g...@serioustechnology.com wrote: Totally agree. Still, the question remains, why not leave it as SQL_ASCII? you have no guarantees that the data stored within is utf-8. that is all. if you can make such guarantees from within your application, then you have some confidence, but never 100%. but it depends on what access you permit to the DB. it is kind of like removing FKs and enforcing them from the application layer. you can do it, but you just don't have a 100% guarantee that the DB is consistent.
Re: [GENERAL] converting databases form SQL_ASCII to UTF8
Vick Khera wrote: On Fri, Apr 22, 2011 at 11:00 AM, Geoffrey Myers li...@serioustechnology.com mailto:li...@serioustechnology.com wrote: Here's our problem. We planned on moving databases a few at a time. Problem is, there is a process that pushes data from one database to another. If this process attempts to push data from a SQL_ASCII database to a new UTF8 database and it has one of these characters mentioned above, the process fails. The database's enforcement of the encoding should be the last layer that does so. Your applications should be enforcing strict utf-8 encoding from start to finish. Once this is done, and the old data already in the DB is properly encoded as utf-8, then there should be no problems switching on the utf-8 encoding in postgres to get that final layer of verification. Totally agree. Still, the question remains, why not leave it as SQL_ASCII? -- Geoffrey Myers Myers Consulting Inc. 770.592.1651 -- 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] converting databases form SQL_ASCII to UTF8
On 04/22/2011 09:16 AM, Geoffrey Myers wrote: Vick Khera wrote: On Fri, Apr 22, 2011 at 11:00 AM, Geoffrey Myers li...@serioustechnology.com mailto:li...@serioustechnology.com wrote: Here's our problem. We planned on moving databases a few at a time. Problem is, there is a process that pushes data from one database to another. If this process attempts to push data from a SQL_ASCII database to a new UTF8 database and it has one of these characters mentioned above, the process fails. The database's enforcement of the encoding should be the last layer that does so. Your applications should be enforcing strict utf-8 encoding from start to finish. Once this is done, and the old data already in the DB is properly encoded as utf-8, then there should be no problems switching on the utf-8 encoding in postgres to get that final layer of verification. Totally agree. Still, the question remains, why not leave it as SQL_ASCII? Maybe because you'll have to consistently remember that you're doing non-standard stuff? -- 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] converting databases form SQL_ASCII to UTF8
On Fri, Apr 22, 2011 at 9:16 AM, Geoffrey Myers g...@serioustechnology.comwrote: Vick Khera wrote: The database's enforcement of the encoding should be the last layer that does so. Your applications should be enforcing strict utf-8 encoding from start to finish. Once this is done, and the old data already in the DB is properly encoded as utf-8, then there should be no problems switching on the utf-8 encoding in postgres to get that final layer of verification. Totally agree. Still, the question remains, why not leave it as SQL_ASCII? Well, if your data is supposed to be UTF-8 encoded, then any of those characters with invalid encoding in UTF-8 could reasonably be viewed as data errors. Leaving the database in SQL-ASCII allows those errors to continue accumulating, which will make a switch in the future even harder. If the lack of being able to check encoding errors at the database level doesn't bother you, and you're fine with risking bigger pain later in order to avoid pain now, then I see no compelling reason to move away from SQL_ASCII. -Eric
Re: [GENERAL] Needs Suggestion
Sorry, but I'm not able to understand about how to use pgsnap for measuring query performance. I have installed pgsnap. when I run pgsnap it shows some error: *Connecting to test database... Adding some HTML files... Getting Misc informations... Getting General informations... sh: pg_controldata: not found Getting Global Informations... pg_buffercache unavailable! Getting Database Informations... pg_buffercache unavailable! pgstattuple unavailable! pgstattuple on indexes unavailable! Getting Current Activities Informations... Getting Statistical Informations... Getting Tools Informations... pgPool unavailable!* However,it generates few reports on the mentioned directory. But I am not able to understand how to use it to measure the performance for each query that I run. -- Thank You, Subham Roy, CSE IIT Bombay.
Re: [GENERAL] converting databases form SQL_ASCII to UTF8
On 04/22/2011 08:00 AM, Geoffrey Myers wrote: We are moving our databases to new hardware soon, so we felt it would be a good time to get the encoding correct. Our databases are currently SQL_ASCII and we plan to move them to UTF8. We are in the same boat, fortunately only on one older server we are upgrading and fortunately for internal apps So, as previously noted, there are certain characters that won't load into a UTF8 database from a dump of the SQL_ASCII database. Here's our problem. We planned on moving databases a few at a time. Problem is, there is a process that pushes data from one database to another. If this process attempts to push data from a SQL_ASCII database to a new UTF8 database and it has one of these characters mentioned above, the process fails. So, now the question is, is this effort even worth our effort? What is the harm in leaving our databases SQL_ASCII encoded? SQL_ASCII is a synonym for no encoding. You put in a stream of bytes and that's what you get out. That's OK if the byte-stream has exactly the same meaning to every application and user. If that's not the case then you have bytes in your database but you don't know what those bytes are supposed to represent. In a way, it's like having a generic integer column but depending on the user or the application, that column might represent a unix epoch timestamp, an age in years, a salary, a weight in grams, furlongs per fortnight, etc. And there is no indicator to say which it is. Not good. We are in the final stages of cleaning up our last bit of non-utf8 data and the above some what silly example is actually not far from the truth. Due to data that arrived from web-inputs, spreadsheet imports, command-line, internal-apps, etc. we have been faced with cleaning tables where one row has only basic ASCII data, another has UTF8, while others have various different Microsoft encodings. With data like that it's pretty much impossible to guarantee that even a simple web-report will display fully correctly. The longer you wait, the worse it gets. Even though it is only a tiny percentage of our data, cleaning it is still a pain. Is it worth it? Dunno - you have to calculate the cost/benefit. For us it was a no-brainer to bite-the-bullet and do it. Cheers, Steve -- 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] Different views of remote server
Hi A little more research. I accessed the problem table as a remote connection using PGAdmin. I selected the cell that shows as being null and copied and pasted the contents into Word. The geom IS there. Using this method the geom is also present, but not visible, in the table I am accessing as a local connection. This is the remote database which I dumped and then restored as a local. These fields are not only not visible but they return null when I do a simple select query. I could really do with some suggestions. Bob -Original Message- From: Adrian Klaver Sent: Thursday, April 21, 2011 7:22 PM To: pgsql-general@postgresql.org Cc: Bob Pawley ; Scott Marlowe Subject: Re: [GENERAL] Different views of remote server On Thursday, April 21, 2011 9:24:57 am Bob Pawley wrote: Hi Scott According to NSAuditor(www.nsauditor.com) there is only one server with port 5432. When I enter information into the remote database it shows up on the same database that has this problem. How do I determine my 'connection credentials'? In pgAdmin they will be in the server properties. Look to see if you are connecting to a port other than 5432. Bob -- 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 -- 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] Different views of remote server
On Friday, April 22, 2011 12:52:28 pm Bob Pawley wrote: Hi A little more research. I accessed the problem table as a remote connection using PGAdmin. I selected the cell that shows as being null and copied and pasted the contents into Word. The geom IS there. Using this method the geom is also present, but not visible, in the table I am accessing as a local connection. This is the remote database which I dumped and then restored as a local. These fields are not only not visible but they return null when I do a simple select query. I could really do with some suggestions. Still waiting for a description of the table and the exact data type you are using. Also are you sure it is really returning null and that you are not looking at empty space at the beginning of the record? Bob -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DB Encoding enforcement
Hey folks, Having not had to worry about character encoding in the past we blithely used the SQL_ASCII encoding and had the application do the input filtering. We have reached the point where we would like to have the DB enforce the character encoding for us. We have chosen to go with LATIN9 encoding with C collation and type attributes. We ended up with a PG 8.4.7 DB on Ubuntu Server 10.04 LTS that looks like this: psql -l List of databases Name| Owner | Encoding | Collation | Ctype | Access privileges ---+--+--+---+---+--- enc_test | postgres | LATIN9 | C | C | postgres | postgres | LATIN9 | C | C | template0 | postgres | LATIN9 | C | C | =c/postgres : postgres=CTc/postgres template1 | postgres | LATIN9 | C | C | =c/postgres : postgres=CTc/postgres (4 rows) I then created a simple table in enc_test: create table xyz ( str text ); and tried inserting a text string with invalid characters for the encoding which was (surprising to me) successful: insert into xyz values ( E'abc \342\200\223 cef' ); INSERT 0 1 (this is a sample from our DB where a unicode sequence was able to be inserted). I know that \200 and \223 are not valid characters in the LATIN9 character set. My question is: Are we expecting too much or did we do something wrong? I would have expected the insert to fail. We wish to stick with single-byte characters (hence the LATIN9 encoding) as we have legacy code that will fail with multi-byte characters (which unfortunately precludes UTF-8 :-( ). TIA. Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Number of Physical I/Os
Can we measure the number of Physical I/Os or Disk I/Os for a particular query in Postgres? In Oracle we can do this with the help of a TraceFile TKPROF. -- Thank You, Subham Roy, CSE IIT Bombay.
[GENERAL] Disk space usage discrepancy
We're trying to figure out how to account for our disk space
consumption in a database.
$ sudo du -shx /var/lib/postgresql/8.4/main/
1.9G/var/lib/postgresql/8.4/main/
But when we query Postgresql to find out how much disk space is
actually being used by the various databases, we get a total of under
600MB (the exact query we use for determining this is below, derived
from the example query in the PG documentation):
$ pg-dbspace
psql: FATAL: database template0 is not currently accepting connections
1272446976 rp
971186176 sfbox
513794048 yang
30326784 ch
16400384 reviewboard
14958592 pod
6733824 cbkup
5767168 redmine_default
2138112 ibkup
2138112 foo
2113536 template1
2113536 postgres
There are two databases with tablespaces on different volumes than
what /var/lib/postgresql/ is on - their PG-reported consumption is
~2.1GB, and they take up about ~1.5x more on disk:
$ df -h | fgrep /mnt
/dev/sdf 2.0G 1.4G 502M 74% /mnt/box
/dev/sdg 5.0G 2.1G 2.7G 44% /mnt/rp
We're also curious about the 1.5x overhead, but we're mainly not sure
why the rest of the database takes up 3x more than reported, even
discounting pg_xlog (which is for the entire cluster):
$ sudo -i 'du -shx /var/lib/postgresql/8.4/main/*'
1.8G/var/lib/postgresql/8.4/main/base
816K/var/lib/postgresql/8.4/main/global
144K/var/lib/postgresql/8.4/main/pg_clog
28K /var/lib/postgresql/8.4/main/pg_multixact
192K/var/lib/postgresql/8.4/main/pg_stat_tmp
80K /var/lib/postgresql/8.4/main/pg_subtrans
4.0K/var/lib/postgresql/8.4/main/pg_tblspc
4.0K/var/lib/postgresql/8.4/main/pg_twophase
4.0K/var/lib/postgresql/8.4/main/PG_VERSION
129M/var/lib/postgresql/8.4/main/pg_xlog
4.0K/var/lib/postgresql/8.4/main/postmaster.opts
4.0K/var/lib/postgresql/8.4/main/postmaster.pid
0 /var/lib/postgresql/8.4/main/server.crt
0 /var/lib/postgresql/8.4/main/server.key
Any hints? Thanks in advance.
The queries were using:
$ type pg-dbspace
pg-dbspace is a function
pg-dbspace ()
{
for db in $(psql -Atc 'select datname from pg_database');
do
printf '%12d %s\n' $(PGDATABASE=$db pg-space total) $db;
done | sort -rn
}
$ type pg-space
pg-space is a function
pg-space ()
{
local schema=${schema:-${1:-}} flags=;
case ${schema:-} in
total)
local query='select sum(bytes) from schemas' flags=-At
;;
'*')
local query='select * from tables'
;;
'')
local query='select * from schemas'
;;
*)
local query=select * from tables where _schema = '$schema'
;;
esac;
psql $flags -c
with
total as (
select sum(pg_relation_size(oid)) from pg_class where relkind = 'r'
),
basic as (
select
n.nspname as _schema,
relname as _table,
pg_relation_size(r.oid) as bytes,
(100*pg_relation_size(r.oid)/(select * from
total))::numeric(4,1) as pct
from pg_class r inner join pg_namespace n on (n.oid = relnamespace)
where relkind = 'r'
),
tables as (
select
_schema,
_table,
bytes,
lpad(pg_size_pretty(bytes), 9) as size,
pct
from basic
order by bytes desc
),
schemas as (
select
_schema,
sum(bytes) as bytes,
lpad(pg_size_pretty(sum(bytes)::int), 9) as size,
sum(pct) as pct
from basic
group by _schema
order by bytes desc
)
$query;
}
--
Yang Zhang
http://yz.mit.edu/
--
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] Different views of remote server
If it's empty space at the beginning it goes on for a long time. Can I send the table as an sql dump as an attachment with the list?? Bob -Original Message- From: Adrian Klaver Sent: Friday, April 22, 2011 1:29 PM To: Bob Pawley Cc: pgsql-general@postgresql.org ; Scott Marlowe Subject: Re: [GENERAL] Different views of remote server On Friday, April 22, 2011 12:52:28 pm Bob Pawley wrote: Hi A little more research. I accessed the problem table as a remote connection using PGAdmin. I selected the cell that shows as being null and copied and pasted the contents into Word. The geom IS there. Using this method the geom is also present, but not visible, in the table I am accessing as a local connection. This is the remote database which I dumped and then restored as a local. These fields are not only not visible but they return null when I do a simple select query. I could really do with some suggestions. Still waiting for a description of the table and the exact data type you are using. Also are you sure it is really returning null and that you are not looking at empty space at the beginning of the record? Bob -- 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 -- 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] Different views of remote server
On Friday, April 22, 2011 2:06:52 pm Bob Pawley wrote: If it's empty space at the beginning it goes on for a long time. Can I send the table as an sql dump as an attachment with the list?? If you want you can send off list to me. Bob -- 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] Number of Physical I/Os
On 4/22/2011 4:03 PM, SUBHAM ROY wrote: Can we measure the number of Physical I/Os or Disk I/Os for a particular query in Postgres? In Oracle we can do this with the help of a TraceFile TKPROF. -- Thank You, Subham Roy, CSE IIT Bombay. Nope. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Should I free this memory?
Hello,
My C function:
PG_FUNCTION_INFO_V1(my_function);
Datum my_function(PG_FUNCTION_ARGS)
{
MemoryContext old_context;
int * p = NULL;
float f = 0.0;
old_context = MemoryContextSwitchTo(fcinfo-flinfo-fn_mcxt);
p = palloc(100);
MemoryContextSwitchTo(old_context);
// do some other stuff
PG_RETURN_FLOAT8(f); // I didn't call pfree(p)
}
Should I free the memory allocated for p? I'm getting memory leaks
when I don't free the memory, and they disappear when I call pfree(p);
I think the response is yes, I should free the memory (looking at
the results), but I'm not sure why. So, maybe my real doubt is: which
memory context are fcinfo-flinfo-fn_mcxt and old_context? In which
context should I work and why?
Many thanks in advance, and best regards
--
Jorge Arévalo
Internet Mobilty Division, DEIMOS
jorge.arev...@deimos-space.com
http://es.linkedin.com/in/jorgearevalo80
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com
http://geohash.org/ezjqgrgzz0g
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What is this doing? SELECT (a,b,c) FROM mytable ...
What syntax or operator did I (accidentally) invoke by putting parentheses around my column list? SELECT (a, b, c) FROM mytable... It gets me a single result column with comma-separated values in parentheses (see 2nd SELECT below). I can't find an explanation in the PostgreSQL manual. It doesn't seem to be an array, a subquery, row constructor, etc. What sort of thing is it? test= CREATE TABLE mytable (a INTEGER, b INTEGER, c INTEGER); test= INSERT INTO mytable VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9); test= SELECT a, b, c FROM mytable ORDER BY 1; a | b | c ---+---+--- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 (3 rows) test= SELECT (a, b, c) FROM mytable ORDER BY 1; row - (1,2,3) (4,5,6) (7,8,9) (3 rows) -- 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] What is this doing? SELECT (a,b,c) FROM mytable ...
ljb ljb9...@pobox.com writes: What syntax or operator did I (accidentally) invoke by putting parentheses around my column list? SELECT (a, b, c) FROM mytable... It gets me a single result column with comma-separated values in parentheses (see 2nd SELECT below). I can't find an explanation in the PostgreSQL manual. It doesn't seem to be an array, a subquery, row constructor, etc. What sort of thing is it? Yeah, it's a ROW() constructor. According to the SQL spec you're allowed to omit the ROW keyword. Not one of their better ideas IMO, especially in a syntax that generally prefers wordiness. It is documented, very briefly, under 4.2.13 Row Constructors: The key word ROW is optional when there is more than one expression in the list. 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] Should I free this memory?
=?ISO-8859-1?Q?Jorge_Ar=E9valo?= jorge.arev...@deimos-space.com writes: old_context = MemoryContextSwitchTo(fcinfo-flinfo-fn_mcxt); p = palloc(100); MemoryContextSwitchTo(old_context); Why are you doing that? Should I free the memory allocated for p? I'm getting memory leaks when I don't free the memory, and they disappear when I call pfree(p); If you allocate that space again on every call, yes you'll get leaks. The fn_mcxt context typically has query lifespan, and could be even longer lived than that. While you could fix it with a pfree at the end of the function, you'll still have a leak if you lose control partway through due to some function throwing an elog(ERROR). By and large, if you intend to allocate the space again on every call anyway, you should just palloc it in your calling memory context, which has got tuple-cycle lifespan and so doesn't pose much risk of bloat. The only reason to allocate something in fn_mcxt is if you're trying to cache data across successive function calls. 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
