Re: [GENERAL] Linux vs FreeBSD
On 4 April 2014 11:03, François Beausoleil franc...@teksol.info wrote: Hi all! Does PG perform that much better on FreeBSD? I have some performance issues on a Ubuntu 12.04 which I'd like to resolve. iowait varies a lot, between 5 and 50%. Does FreeBSD better schedule I/O, which could alleviate some of the issues, or not at all? I have no experience administering FreeBSD, but I'm willing to learn if I'll get some performance enhancements out of the switch. Ubuntu 14.04 LTS is being released in a few days and you might have more success with its newer kernel. And try different schedulers if you haven't already - IIRC switching to deadline resolved one of our load problems. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ -- 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] openssl heartbleed
Steve Crawford wrote: On 04/09/2014 08:54 AM, Gabriel E. Sánchez Martínez wrote: Hi all, Our server is running Ubuntu Server 13.10 (we will soon upgrade to 14.04) and PostgreSQL 9.1. We use certificates for all client authentication on remote connections. The server certificate is self-signed. In light of the heartbleed bug, should we create a new server certificate and replace all client certificates? My guess is yes. [...] If you aren't and weren't running a vulnerable version or if the vulnerable systems were entirely within a trusted network space with no direct external access then you are probably at low to no risk and need to evaluate the cost of updates against the low level of risk. If you are in a totally trusted environment, why would you use SSL? Yours, Laurenz Albe -- 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] openssl heartbleed
On 4/10/2014 1:01 AM, Albe Laurenz wrote: If you are in a totally trusted environment, why would you use SSL? Belt, and suspenders. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Order By and Comparisson
On Mon, 07 Apr 2014 11:04:23 +0100 howardn...@selestial.com howardn...@selestial.com wrote: Hi, just as I thought I had postgres mastered :) the ordering of strings is causing me some confusion. Can someone explain how the database orders strings in the ORDER BY command. My example: My database is encoding is UTF-8, and default language is english, If I have a text column in a table with the following rows: 'a' 'A' '~' Then in UTF-8, I would expect the order to give me 'A' 'a' '~' But instead I get: '~' 'a' 'A' Is there anywhere in the documentation I can get a more detailed explanation of this? Expect for the unexpected. SQL alphabetical sorting can get pretty complicated, as stated in this note from Oracle 10g Release 2 docs[1]: In the ASCII standard, all uppercase letters appear before any lowercase letters. In the EBCDIC standard, the opposite is true: all lowercase letters appear before any uppercase letters. [1] docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm -- Alberto Cabello Sánchez albe...@unex.es -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Stored procedures and schema renames
I would like to rename schemas without breaking the stored procedures in them. Currently, this does not work if the stored procedure definition contains a schema self-reference because that does not get renamed. I tried SET search_path FROM CURRENT, but that seems to expand the search path on function definition time, which does not address this issue either. If backend code changes are required, what would be a reasonable way to approach this? Would adding a CURRENT_SCHEMA pseudo-schema which can be used in stored procedures work? -- Florian Weimer / Red Hat Product Security Team -- 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 upgrading a (tuple?) lock in a rollback'd sub-transaction
And it also tells you how to stop it --bibtex iirc Sent from my iPhone On Apr 9, 2014, at 8:41 PM, Amit Langote amitlangot...@gmail.com wrote: Hi, Currently there is a warning against the following in manual: BEGIN; SELECT * FROM mytable WHERE key = 1 FOR UPDATE; SAVEPOINT s; UPDATE mytable SET ... WHERE key = 1; ROLLBACK TO s; here: http://www.postgresql.org/docs/9.2/static/sql-select.html IIUC, it says if the lock-upgrading sub-transaction is rollback'd, as an undesirable effect, any lock held by the parent transaction is effectively lost. A few tests suggest that the lock is still effective for a concurrent transaction started before the lock-upgrading operation (UPDATE) in the later savepoint. The lock is forgotten, though, if a concurrent transaction acquired the lock after the UPDATE on the tuple in the later savepoint. As soon as the UPDATE is rollback'd, the concurrent transaction, blind to any lock the parent transaction had on the tuple, gets the lock. -- 1] -- session-1 $ BEGIN; $ SELECT * FROM mytable WHERE Key = 1 FOR UPDATE 2] -- session-1 $ SAVEPOINT s; $ UPDATE mytable SET ... WHERE key = 1; 3] -- session-2 $ SELECT * FROM mytable WHERE Key = 1 FOR UPDATE 4] -- session-1 $ ROLLBACK TO s; 5] -- session-2 -- gets the lock and free to modify the tuple (inconistently, off course) -- Although, if [3] were before [2], this wouldn't happen I know it is still a warned-against usage; but, is it useful to clarify this nuance of the behavior? -- Amit -- 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] Stored procedures and schema renames
Code for db functions should be a repository. Easy edit and rerun Sent from my iPhone On Apr 10, 2014, at 6:19 AM, Florian Weimer fwei...@redhat.com wrote: I would like to rename schemas without breaking the stored procedures in them. Currently, this does not work if the stored procedure definition contains a schema self-reference because that does not get renamed. I tried SET search_path FROM CURRENT, but that seems to expand the search path on function definition time, which does not address this issue either. If backend code changes are required, what would be a reasonable way to approach this? Would adding a CURRENT_SCHEMA pseudo-schema which can be used in stored procedures work? -- Florian Weimer / Red Hat Product Security Team -- 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] encrypting data stored in PostgreSQL
On Apr 9, 2014, at 3:40 PM, CS_DBA cs_...@consistentstate.com wrote: Hi All; We have a client with this requirement: At rest data must be encrypted with a unique client key Any thoughts on how to pull this off for PostgreSQL stored data? Does there happen to be a Postgresql proxy, such as a modified pgbouncer, that implements column-wise and/or row-wise encryption and decryption using keys specific to the authenticated user? It seems like a reasonable way to implement an encryption layer that would provide protection against a number of threats without requiring modification to the higher layers of the application or to the Postgresql server. Guy -- 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] openssl heartbleed
John R Pierce wrote: On 4/10/2014 1:01 AM, Albe Laurenz wrote: If you are in a totally trusted environment, why would you use SSL? Belt, and suspenders. I guess what I wanted to say was: If you are concerned enough to use SSL, you should be concerned enough to change your certificates. To continue the suspenders parable, if you are worried enough to wear suspenders you should replace them if they have been cut in two. Or take them off - wearing broken suspenders is sillier than wearing none (SSL costs resources). Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] design question: how to geocode multiple dynamic city, country?
i accept multiple city, country from users on-the-fly, and want to dynamically map them. i could create a table where i insert their multiple entries, and then geocode that table for display. but i also want to avoid giving write permission to the web user. i could create a schema and restrict write to that schema. or something better? -- 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] Linux vs FreeBSD
On Wed, Apr 9, 2014 at 3:36 PM, François Beausoleil franc...@teksol.info wrote: Le 2014-04-09 à 16:20, Bruce Momjian a écrit : On Wed, Apr 9, 2014 at 10:02:07AM -0500, Christofer C. Bell wrote: This highlights a more fundamental problem of the difference between a workstation-based on OS like Ubuntu and a server-based one like Debian or FreeBSD. I know Ubuntu has a server version, but fundamentally Ubuntu's selection of kernels and feature churn make it less than ideal for server deployments. I am sure someone can post that they use Ubuntu just fine for server deployments, but I continue to feel that Ubuntu is chosen by administrators because it an OS they are familiar with on workstations, rather than it being the best choice for servers. I'm not a full-time sysadmin. I chose Ubuntu because I have familiarity with it, and because installing Puppet on it installed the certificates and everything I needed to get going. I tried Debian, but I had to fight and find the correct procedures to install the Puppet certificates and all. Ubuntu saved me some time back then. Cheers! François I'm not wanting to get after anyone here, but I want it on the record that I am not the source of the above quote discouraging the use of Ubuntu in a server role. That would be Bruce Momjian. While Bruce is entitled to his opinion, it's not one I agree with and I don't want a Google search years from now to tie my name to that viewpoint. Thanks! -- Chris If you wish to make an apple pie from scratch, you must first invent the Universe. -- Carl Sagan -- 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] Linux vs FreeBSD
On 04/10/14 17:25, Christofer C. Bell wrote: I'm not wanting to get after anyone here, but I want it on the record that I am not the source of the above quote discouraging the use of Ubuntu in a server role. That would be Bruce Momjian. While Bruce is entitled to his opinion, it's not one I agree with and I don't want a Google search years from now to tie my name to that viewpoint. Who (in their right mind) would ever think of anything but BSD in a server role? shaking head Jan -- Jan Wieck Senior Software Engineer http://slony.info -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] HOT standby on windows not working
Hi All; We're setting up a HOT standby on Windows 2000 server and PostgreSQL 9.2 We do this: 1) stop both servers 2) copy the master data directory to the slave 3) edit the master postgresql.conf file as follows: Modify the following listen_address = ‘*’ wal_level = hot_standby max_wal_senders = 3 4) edit the master pg_hba.conf file and add this line: host replication all 192.168.91.136/32 trust where 192.168.91.136 is the IP of the slave 5) edit the postgresql.conf file on the slave as follows: hot_standby = on 6) create a recovery.conf with the following contents: standby_mode = ‘on’ primary_conninfo = ‘host=192.168.91.165’ where 192.168.91.165 is the IP of the master 7) start the standby 8) start the master The standby simply comes online, almost like it is ignoring the recovery.conf file. Here's a tail of the log from the slave when we start it: 2014-04-10 15:45:24 MDT [3756]: [1-1] LOG: database system was interrupted; last known up at 2014-04-10 16:36:17 MDT 2014-04-10 15:45:24 MDT [3756]: [2-1] LOG: database system was not properly shut down; automatic recovery in progress 2014-04-10 15:45:24 MDT [3756]: [3-1] LOG: record with zero length at 0/880 2014-04-10 15:45:24 MDT [3756]: [4-1] LOG: redo is not required 2014-04-10 15:45:24 MDT [3592]: [1-1] LOG: database system is ready to accept connections 2014-04-10 15:45:24 MDT [3572]: [1-1] LOG: autovacuum launcher started Any thoughts why this is not working? I've also tried this approach: 1) Master postgresql.conf file Modify the following settings: listen_address = ‘*’ wal_level = hot_standby max_wal_senders = 3 2) Modify Master pg_hba.conf file: hostssl replication al 192.168.91.136/32 trust 3) RESTART MASTER DATABASE 4) Slave postgresql.conf file hot_standby = on 5) Create a recovery.conf file on the slave as follows: standby_mode = ‘on’ primary_conninfo = ‘host=192.168.91.165’ 6) Execute a ‘pg_start_backup’ on the master. SELECT pg_start_backup (‘date’, true); 7) Copy the data files to the standby. a) Copied the master data directory to the slave desktop b) removed postgresql.conf and pg_hba.conf from the data directory (the copy on the slave desktop) c) removed the pg_xlog dir from the data directory (the copy on the slave desktop) 8) Execute a ‘pg_stop_backup’ on the master. SELECT pg_stop_backup (); 9) Copy the Write Ahead Log (WAL) files (the data/pg_xlog directory) from the master to the standby 10) start the standby database We get the same behavior (i.e. the slave comes fully online, not just into recovery / standby mode) no matter which approach we use... Thoughts? Thanks in advance... -- 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] openssl heartbleed
On 04/10/2014 01:01 AM, Albe Laurenz wrote: Steve Crawford wrote: If you aren't and weren't running a vulnerable version or if the vulnerable systems were entirely within a trusted network space with no direct external access then you are probably at low to no risk and need to evaluate the cost of updates against the low level of risk. If you are in a totally trusted environment, why would you use SSL? I didn't say *totally* trusted - that doesn't exist. We use secure connections inside our firewall all the time and sometimes authentication convenience is as much a driving factor as security. I didn't suggest someone *avoid* updating keys/certificates - just to evaluate cost vs. risk as one must always do. But I'd submit that anyone seriously concerned about this attack being launched from within their internal network has a whole bunch of higher-priority security problems. -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] HOT standby on windows not working
On Fri, Apr 11, 2014 at 8:15 AM, CS_DBA cs_...@consistentstate.com wrote: Hi All; We're setting up a HOT standby on Windows 2000 server and PostgreSQL 9.2 We do this: I've also tried this approach: 1) Master postgresql.conf file Modify the following settings: listen_address = '*' wal_level = hot_standby max_wal_senders = 3 2) Modify Master pg_hba.conf file: hostssl replication al 192.168.91.136/32 trust 3) RESTART MASTER DATABASE Use the pg_basebackup utility to take the backup directory and change the the conf files. 4) Slave postgresql.conf file hot_standby = on 5) Create a recovery.conf file on the slave as follows: standby_mode = 'on' primary_conninfo = 'host=192.168.91.165' 6) start the standby database Try with the above approach. Regards, Hari Babu Fujitsu Australia -- 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 upgrading a (tuple?) lock in a rollback'd sub-transaction
On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent robjsarg...@gmail.com wrote: And it also tells you how to stop it --bibtex iirc Yeah, it's a caution against a potentially harmful usage anyway. Users should not use it at all. I was just wondering if the description of the behavior, that is, potential disappearance of certain locks is complete enough. -- Amit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [DOCS] [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction
Amit Langote wrote: On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent robjsarg...@gmail.com wrote: And it also tells you how to stop it --bibtex iirc Yeah, it's a caution against a potentially harmful usage anyway. Users should not use it at all. I was just wondering if the description of the behavior, that is, potential disappearance of certain locks is complete enough. You do realize that this is no longer the case in 9.3, right? I don't see a point in changing old releases' documentation. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [DOCS] [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction
On Fri, Apr 11, 2014 at 11:52 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Amit Langote wrote: On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent robjsarg...@gmail.com wrote: And it also tells you how to stop it --bibtex iirc Yeah, it's a caution against a potentially harmful usage anyway. Users should not use it at all. I was just wondering if the description of the behavior, that is, potential disappearance of certain locks is complete enough. You do realize that this is no longer the case in 9.3, right? I don't see a point in changing old releases' documentation. I see, okay. And yes, I'm aware that this's no longer an issue in 9.3. Thanks, Amit -- 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.conf question... CPU spikes
On Thu, Apr 10, 2014 at 12:43 AM, Bala Venkat akpg...@gmail.com wrote: Hi all - We are running postgres 9.0 ( 32 bit ) + postgis 1.5.2 on Solaris Sparc M5000 with 64GB . Recently we are getting CPU utilitzation to 99% . In the config file shared_buffers=2GB. work_mem = 128MB effective_cache_size=48GB maintaince_work_mem= 500MB max_connections = 300 When the CPU spikes happens, when I look at the pg_stat_activity log, the queries where current_query not like '%IDLE%' are between 100-110. Do you think , I have to reduce the effective_cache and work_mem for this? What does the load average say ? What about memory usage and disk IO ? Best way to look at CPU spikes issue is through top or equivalent utility which helps us know the PIDs for top resource consuming processes and the processes / sessions info using the same PIDs can be pulled in from pg_stat_activity. Another best way - which i felt is the best tool is - pg_top. pg_top is an excellent tool which help us identify the top resource consuming queries responsible for high CPU consumption or high DISK IO. Once you identify resource consuming processes or queries, things can be taken from there. Regards, Venkata Balaji N Fujitsu Australia
