Re: [GENERAL] check constraint question
On Tue, 08 Apr 2014 15:53:48 -0600 CS_DBA cs_...@consistentstate.com wrote: Not sure yet (new client)... for now they simply want to force the template column to be a valid cust_id, if it is not null... It seems to be a different version of the textbook exercice involving EMPLOYEE_ID and MANAGER_ID. -- 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] What does ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table fruits mean?
After being bitten by the bug mentioned in the release notes of 9.3.4, I have realized that a very small part of my data is corrupt, after doing a failover. The bug showed it self as duplicate rows, with the same primary key. Now I can easily afford to delete some data from my database if that’s what it takes to fix my problem, so I just deleted those duplicates whole hug. After doing this, I decided to write a script that drops/recreates all constraints (Pkeys and Fkeys), in order to validate that no more of my data is corrupt. This worked well, until I bumped into the following error, while creating one of the foreign keys: ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table fruits CONTEXT: SQL statement ALTER TABLE pm.fruits ADD CONSTRAINT fruits_pkey PRIMARY KEY (id) What is it, and how do I solve it? -- 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.conf question... CPU spikes
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? Thanks for your help.
[GENERAL] Help
Hi, My name is MOHAMMED BOUZIANE ILYES, i am an postgraduate student at the National high school of computing (ESI) algeirs- Algeria. my master thesis is database replication based on snapshot isolation I encounter somme difficulties to implement a middleware baesd replication with Libpq and c language, for that, I need somme hints to achieve this work, more precisely, how to capture the SQL statement text with libpq at the middleware layer. best regards. -- Mohammed-Bouziane ilyes ingénieur d'état en informatique ORACLE DABASE 10g ADMINISTRATOR CERTIFIED PROFESSIONAL (OCP) Étudiant en deuxième année école doctorale (ESI) option IRM Tel :0664614266 -- 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 Thu, Apr 3, 2014 at 11:03 PM, 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. $ uname -a Linux munn.ca.seevibes.com 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux From the research I've done online, this is likely your issue. Kernel 3.2.0 has some issues that directly and severely impact I/O wait times for PostgreSQL. The suggested fixes (that seem to have worked for most people reporting in) are to revert the OS to Ubuntu Server 10.04 or to install one of the HWE (HardWare Enablement) kernels into the 12.04 system (this would be one of the kernels from a later release of Ubuntu provided in the 12.04 repositories). -- 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
[GENERAL] openssl heartbleed
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. Regards, Gabriel -- 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 Wed, Apr 09, 2014 at 11:54:43AM -0400, Gabriel E. Sánchez Martínez wrote: self-signed. In light of the heartbleed bug, should we create a new server certificate and replace all client certificates? My guess is yes. This depends mostly on what version of openssl you were actually using. If it were me, I'd say yes. A -- Andrew Sullivan a...@crankycanuck.ca -- 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] import .sql file into PostgreSQL database
On 09/04/2014 03:42, Gaurav Jindal wrote: Inline image 1 On Tue, Apr 8, 2014 at 5:21 PM, gaur...@gmail.com mailto:gaur...@gmail.com wrote: - Your OS Windows 7 - What version of PostgreSQL you have 9.3.4 - What is in the SQL file you're trying to execute. Tables and their description - The EXACT error message that you're getting. Permission denied when I run \i dump.sql in psql Please keep your replies on-list. I think that message is coming from the OS; messages from Postgres are usual more specific, for example, Permission denied for relation. It looks as if you haven't the OS permissions to read the SQL file. 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] openssl heartbleed
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. The answer is, of course, it depends. Here's my take: If your connections are coming from the Internet or other untrusted sources *and* you are or were running a vulnerable version of OpenSSL then yes, you should change your keys, certificates and any other credentials that might have been found at some point in RAM including passwords/keys used to access the vulnerable server *or* which the vulnerable server stores and uses to access other systems. Of course this means that if you have PostgreSQL backing a vulnerable public webserver then you are at risk. 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. 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] openssl heartbleed
On Wed, Apr 9, 2014 at 10:54 AM, Gabriel E. Sánchez Martínez gabrielesanc...@gmail.com 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. I highly recommend you, update your server, revoke the certificates and regenerate them. Regards, Regards, Gabriel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Cristian Salamea @ovnicraft
[GENERAL] Refresh Postgres SSL certs?
Hello,
In light of the Heartbleed OpenSSL bug[0,1], I'm wondering if I need
to regenerate the SSL certs on my postgres installations[2] (at least
the ones listening on more than localhost)? On Ubuntu it looks like
there are symlinks at /var/lib/postgresql/9.1/main/server.{crt,key}
pointing to /etc/ssl/private/ssl-cert-snakeoil.{pem,key}. Is there any
documentation on how to regenerate these? Are they self-signed? Can I
replace them with my own self-signed certs, like I'd do with Apache or
Nginx?
Thanks!
Paul
[0] https://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2014-0160
[1] http://heartbleed.com/
[2] http://www.postgresql.org/docs/9.1/static/ssl-tcp.html
--
_
Pulchritudo splendor veritatis.
--
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] Refresh Postgres SSL certs?
On Wed, Apr 09, 2014 at 12:28:14PM -0700, Paul Jungwirth wrote:
Hello,
In light of the Heartbleed OpenSSL bug[0,1], I'm wondering if I need
to regenerate the SSL certs on my postgres installations[2] (at least
the ones listening on more than localhost)? On Ubuntu it looks like
there are symlinks at /var/lib/postgresql/9.1/main/server.{crt,key}
pointing to /etc/ssl/private/ssl-cert-snakeoil.{pem,key}. Is there any
documentation on how to regenerate these? Are they self-signed? Can I
replace them with my own self-signed certs, like I'd do with Apache or
Nginx?
Have you read the Debian README?
/usr/share/doc/postgresql-*/README.Debian.gz
It talks about how the certificates are made. It uses the ssl-cert
package to make them, there's more docs there.
Yes, you can make your own self-signed certs and use them.
Have a nice day,
--
Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/
He who writes carelessly confesses thereby at the very outset that he does
not attach much importance to his own thoughts.
-- Arthur Schopenhauer
signature.asc
Description: Digital signature
Re: [GENERAL] Refresh Postgres SSL certs?
Have you read the Debian README? /usr/share/doc/postgresql-*/README.Debian.gz Thank you for pointing me to that file. From /etc/share/doc/ssl-cert/README it sounds like the old snakeoil cert is already self-signed, so that's promising. So I take it that psql and the postgres client library won't object to a self-signed cert. Do they do any kind of certificate pinning or other caching of the old cert? Or can I just replace the cert, restart the postgres server, and be done? Thanks, Paul -- _ Pulchritudo splendor veritatis. -- 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 10:02:07AM -0500, Christofer C. Bell wrote: On Thu, Apr 3, 2014 at 11:03 PM, 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. $ uname -a Linux munn.ca.seevibes.com 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux From the research I've done online, this is likely your issue. Kernel 3.2.0 has some issues that directly and severely impact I/O wait times for PostgreSQL. The suggested fixes (that seem to have worked for most people reporting in) are to revert the OS to Ubuntu Server 10.04 or to install one of the HWE (HardWare Enablement) kernels into the 12.04 system (this would be one of the kernels from a later release of Ubuntu provided in the 12.04 repositories). 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. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Refresh Postgres SSL certs?
On Wed, Apr 09, 2014 at 12:59:53PM -0700, Paul Jungwirth wrote: Have you read the Debian README? /usr/share/doc/postgresql-*/README.Debian.gz Thank you for pointing me to that file. From /etc/share/doc/ssl-cert/README it sounds like the old snakeoil cert is already self-signed, so that's promising. So I take it that psql and the postgres client library won't object to a self-signed cert. Do they do any kind of certificate pinning or other caching of the old cert? Or can I just replace the cert, restart the postgres server, and be done? No pinning, no caching. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Linux vs FreeBSD
On Wed, Apr 9, 2014 at 9:02 AM, Christofer C. Bell christofer.c.b...@gmail.com wrote: On Thu, Apr 3, 2014 at 11:03 PM, 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. $ uname -a Linux munn.ca.seevibes.com 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux From the research I've done online, this is likely your issue. Kernel 3.2.0 has some issues that directly and severely impact I/O wait times for PostgreSQL. The suggested fixes (that seem to have worked for most people reporting in) are to revert the OS to Ubuntu Server 10.04 or to install one of the HWE (HardWare Enablement) kernels into the 12.04 system (this would be one of the kernels from a later release of Ubuntu provided in the 12.04 repositories). 12.04 supports 3.8.0 directly. There's a site on putting 3.10.17 or so on it as well I found by googling for it. You need 3.10+ if you wanna play with bcache which is how I found it. But you don't need to jump through any hoops to get 3.8.0 on 12.04.4 LTS -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How do I find out where this warning is coming from?
I've get several processes running that use the same database. My database log file is filled with these: 2014-04-09 14:16:45 EDT WARNING: invalid value for parameter search_path: public, operationsplanning, cooling_stands 2014-04-09 14:16:45 EDT DETAIL: schema cooling_stands does not exist I would like to have more information in the file about this, such as the source process or the statement that is throwing this warning. For now, all I have to go on is that the error shows up once a minute. I didn't see anything in postgresql.conf to control the format of this message, but there could well be something I didn't understand. Thanks very much! RobR
Re: [GENERAL] Linux vs FreeBSD
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 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] How do I find out where this warning is coming from?
Rob Richardson rdrichard...@rad-con.com writes: I've get several processes running that use the same database. My database log file is filled with these: 2014-04-09 14:16:45 EDT WARNING: invalid value for parameter search_path: public, operationsplanning, cooling_stands 2014-04-09 14:16:45 EDT DETAIL: schema cooling_stands does not exist I would like to have more information in the file about this, such as the source process or the statement that is throwing this warning. Since there's not a statement there already, I'd imagine this is coming from connection-time processing of values installed by ALTER USER SET or ALTER DATABASE SET. A look into the relevant system catalogs should find the bogus setting. Or you could turn on log_connections to get some info about what's making the connection that throws the warning. (I'd suggest adding %p to your log_line_prefix so you can definitively connect this message with the connection log one ...) 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
[GENERAL] encrypting data stored in PostgreSQL
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? 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] How do I find out where this warning is coming from?
On 04/09/2014 10:34 PM, Rob Richardson wrote: I’ve get several processes running that use the same database. My database log file is filled with these: 2014-04-09 14:16:45 EDT WARNING: invalid value for parameter search_path: public, operationsplanning, cooling_stands 2014-04-09 14:16:45 EDT DETAIL: schema cooling_stands does not exist I would like to have more information in the file about this, such as the source process or the statement that is throwing this warning. For now, all I have to go on is that the error shows up once a minute. I didn’t see anything in postgresql.conf to control the format of this message, but there could well be something I didn’t understand. You missed log_line_prefix. http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-LINE-PREFIX -- Vik
Re: [GENERAL] Linux vs FreeBSD
On Apr 9, 2014, at 1:33 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Apr 9, 2014 at 9:02 AM, Christofer C. Bell christofer.c.b...@gmail.com wrote: On Thu, Apr 3, 2014 at 11:03 PM, 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. $ uname -a Linux munn.ca.seevibes.com 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux From the research I've done online, this is likely your issue. Kernel 3.2.0 has some issues that directly and severely impact I/O wait times for PostgreSQL. The suggested fixes (that seem to have worked for most people reporting in) are to revert the OS to Ubuntu Server 10.04 or to install one of the HWE (HardWare Enablement) kernels into the 12.04 system (this would be one of the kernels from a later release of Ubuntu provided in the 12.04 repositories). 12.04 supports 3.8.0 directly. There's a site on putting 3.10.17 or so on it as well I found by googling for it. You need 3.10+ if you wanna play with bcache which is how I found it. But you don't need to jump through any hoops to get 3.8.0 on 12.04.4 LTS Or wait checks watch 8 days for14.04 LTS with kernel 3.14. 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] encrypting data stored in PostgreSQL
On 4/9/2014 1:40 PM, CS_DBA 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? encrypt the data in the client application before sending it to the database server, decrypt it in the client when you need it back. -- 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] encrypting data stored in PostgreSQL
On 04/09/2014 02:52 PM, John R Pierce wrote: On 4/9/2014 1:40 PM, CS_DBA 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? encrypt the data in the client application before sending it to the database server, decrypt it in the client when you need it back. How does that affect backend sql reporting?\
Re: [GENERAL] encrypting data stored in PostgreSQL
On 04/09/2014 02:52 PM, John R Pierce wrote: On 4/9/2014 1:40 PM, CS_DBA 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? I looked at this a while ago because I have clients who might require this in the future. ISTM you should be able to have your PG data directory stored on an encrypted filesystem. I believe this will decrease performance, but I have no idea by how much. Does anyone else have experience with such a setup, or knowledge of how bad the performance hit might be? Or other factors to take into consideration? Thanks. Ken -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ http://agency-software.org/* *https://agency-software.org/demo/client https://agency-software.org/demo/client* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing listagency-general-requ...@lists.sourceforge.net?body=subscribe to learn more about AGENCY or follow the discussion.
Re: [GENERAL] encrypting data stored in PostgreSQL
On Wed, Apr 09, 2014 at 02:16:34PM -0700, Ken Tanzer wrote: Any thoughts on how to pull this off for PostgreSQL stored data? I looked at this a while ago because I have clients who might require this in the future. ISTM you should be able to have your PG data directory stored on an encrypted filesystem. I believe this will decrease performance, but I have no idea by how much. FWIW, I have several databases running on encrypted filesystems. The performance difference is negligable *if* you have hardware acceleration for your encryption, which most modern processors have. Essentially, the processor can encrypt/decrypt data so much faster than the cost of reading/writing to disk, you don't notice the difference. There's surely a difference, but if this means you meet your requirements it's an excellent solution. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] encrypting data stored in PostgreSQL
On 4/9/2014 2:16 PM, Ken Tanzer wrote: I looked at this a while ago because I have clients who might require this in the future. ISTM you should be able to have your PG data directory stored on an encrypted filesystem. I believe this will decrease performance, but I have no idea by how much. Does anyone else have experience with such a setup, or knowledge of how bad the performance hit might be? Or other factors to take into consideration? Thanks. whats the threat model this encryption is supposed to solve ? a encrypted file system has to be mounted and readable as long as the file system is operational, this implies that any data in it can be read by anyone with access to that system. now, if you just need a checkbox saying its encrypted, then whatever, it hardly matters. -- 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] encrypting data stored in PostgreSQL
On 4/9/2014 2:07 PM, Rob Sargent wrote: encrypt the data in the client application before sending it to the database server, decrypt it in the client when you need it back. How does that affect backend sql reporting?\ does this backend sql reporting system need access to the contents of this encrypted data (presumably credit card numbers or some such ?) if so, then it too would need to be able to decrypt the data and would have to possess the decryption key(s). -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] Linux vs FreeBSD
On Wed, Apr 9, 2014 at 2:56 PM, Steve Atkins st...@blighty.com wrote: On Apr 9, 2014, at 1:33 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Apr 9, 2014 at 9:02 AM, Christofer C. Bell christofer.c.b...@gmail.com wrote: On Thu, Apr 3, 2014 at 11:03 PM, 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. $ uname -a Linux munn.ca.seevibes.com 3.2.0-58-generic #88-Ubuntu SMP Tue Dec 3 17:37:58 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux From the research I've done online, this is likely your issue. Kernel 3.2.0 has some issues that directly and severely impact I/O wait times for PostgreSQL. The suggested fixes (that seem to have worked for most people reporting in) are to revert the OS to Ubuntu Server 10.04 or to install one of the HWE (HardWare Enablement) kernels into the 12.04 system (this would be one of the kernels from a later release of Ubuntu provided in the 12.04 repositories). 12.04 supports 3.8.0 directly. There's a site on putting 3.10.17 or so on it as well I found by googling for it. You need 3.10+ if you wanna play with bcache which is how I found it. But you don't need to jump through any hoops to get 3.8.0 on 12.04.4 LTS Or wait checks watch 8 days for14.04 LTS with kernel 3.14. I'm not deploying any new distro version that soon. :) I know folks just putting 12.04 into prod to replace etch and lenny. :) -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] encrypting data stored in PostgreSQL
On Wed, Apr 9, 2014 at 2:32 PM, John R Pierce pie...@hogranch.com wrote: On 4/9/2014 2:16 PM, Ken Tanzer wrote: I looked at this a while ago because I have clients who might require this in the future. ISTM you should be able to have your PG data directory stored on an encrypted filesystem. I believe this will decrease performance, but I have no idea by how much. Does anyone else have experience with such a setup, or knowledge of how bad the performance hit might be? Or other factors to take into consideration? Thanks. whats the threat model this encryption is supposed to solve ? a encrypted file system has to be mounted and readable as long as the file system is operational, this implies that any data in it can be read by anyone with access to that system. now, if you just need a checkbox saying its encrypted, then whatever, it hardly matters. -- john r pierce 37N 122W somewhere on the middle of the left coast Well the needing to check a box on a checklist was the starting point for me looking into this. I think the scenario would be what if someone stole your hard disks? (Or stole Rackspace's hard disk, in my case.) I didn't dig too deep, but it seemed that there was/is a basic tradeoff--either the encryption key is accessible from the server and thus the filesystem can be conveniently and automatically mounted,but providing little extra security, or 2) the encryption key is user supplied at boot time, providing a good deal extra security but way less convenience. Cheers, Ken -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ http://agency-software.org/* *https://agency-software.org/demo/client https://agency-software.org/demo/client* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing listagency-general-requ...@lists.sourceforge.net?body=subscribe to learn more about AGENCY or follow the discussion.
Re: [GENERAL] Linux vs FreeBSD
On 04/09/14 14:46, Scott Marlowe wrote: I'm not deploying any new distro version that soon. :) I know folks just putting 12.04 into prod to replace etch and lenny. :) You can easily get the 3.11.0 kernel on 12.04.4 LTS by installing the linux-generic-lts-saucy package. IIRC, the fix for the iowait issue was committed into 3.9.x but the first Ubuntu kernel that appeared with the fix was the one for 'saucy'. -- 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 Wed, Apr 9, 2014 at 01:52:51PM -0700, John R Pierce wrote: On 4/9/2014 1:40 PM, CS_DBA 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? encrypt the data in the client application before sending it to the database server, decrypt it in the client when you need it back. I have a presentation that covers some of this: Securing PostgreSQL From External Attack http://momjian.us/main/presentations/features.html#securing -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] After paying PG Associate Cert. Exam what's the next step?
Well, at the end i received my code and a link to access to the certification exam portal and the dummy exam, after half a day or something like that. Regards. *** Oscar Calderon Analista de Sistemas Soluciones Aplicativas S.A. de C.V. www.solucionesaplicativas.com Cel. (503) 7741 7850 Tel. (503) 2522-2834 2014-03-12 19:09 GMT-06:00 Adrian Klaver adrian.kla...@aklaver.com: On 03/12/2014 04:13 PM, Oscar Calderon wrote: Hi there, have a nice day. Well, as the subject says, today i paid for the right to take the PostgreSQL Associate Certification Exam once in enterprisedb.com http://enterprisedb.com website, i received the confirmation email and everything's fine. But after that, i don't know what's next, the email that i received is just regarding to the purchase but it doesn't indicate what are the next steps or something like that. In the portal, i navigated in the menus but i cannot find anything new like some link to take the exam or to take the mock exam that is provided after you pay or some kind of additional resources that you receive after paying (if there's any), so i'm not sure if i have to wait or something like that. Well this really something you need to take up with EnterpriseDB. To that end I offer the link below which seems to be a good place to start: http://www.enterprisedb.com/products-services-training/ postgresql-certification/certification-faq Regards. *** Oscar Calderon Analista de Sistemas Soluciones Aplicativas S.A. de C.V. www.solucionesaplicativas.com http://www.solucionesaplicativas.com Cel. (503) 7741 7850 Tel. (503) 2522-2834 -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] encrypting data stored in PostgreSQL
On 9.4.2014 23:28, Martijn van Oosterhout wrote: On Wed, Apr 09, 2014 at 02:16:34PM -0700, Ken Tanzer wrote: Any thoughts on how to pull this off for PostgreSQL stored data? I looked at this a while ago because I have clients who might require this in the future. ISTM you should be able to have your PG data directory stored on an encrypted filesystem. I believe this will decrease performance, but I have no idea by how much. FWIW, I have several databases running on encrypted filesystems. The performance difference is negligable *if* you have hardware acceleration for your encryption, which most modern processors have. Essentially, the processor can encrypt/decrypt data so much faster than the cost of reading/writing to disk, you don't notice the difference. There's surely a difference, but if this means you meet your requirements it's an excellent solution. We're running a number of rather busy PostgreSQL boxes with encryption at filesystem (or more precisely dm-crypt/LUKS with LVM, IIRC). Support for encryption acceleration (AES-NI [1]) is an absolute must. The other thing that is essential for good performance is reasonably recent kernel. 2.6.x kernels have a single-threaded kcryptd, which means you can't get more than ~150 MB/s AES-256 (per partition). With other algorithms it's not much better (say, 170MB/s with AES-128, IIRC). Somewhere in 3.x (or maybe very late 2.6.x) kcryptd was improved to use multiple threads - that's a significant improvement, both for throughput and latencies. Clearly, it's going to eat (part of) your CPUs, but that's expected. The encryption still has impact on latencies, but with the multi-threaded kcryptd it's pretty-much negligible. regards Tomas [1] http://en.wikipedia.org/wiki/AES_instruction_set -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Capture the SQL statement text with libpq [was: Re: Help]
On Wed, 9 Apr 2014 14:41:46 +0100 MOHAMMED-BOUZIANE Ilyes i_mohammed_bouzi...@esi.dz wrote: my master thesis is database replication based on snapshot isolation I encounter somme difficulties to implement a middleware baesd replication with Libpq and c language, for that, I need somme hints to achieve this work, more precisely, how to capture the SQL statement text with libpq at the middleware layer. Hi, Mohammed I think you could take a look at the code in SQLRelay [1], a middleware which knows how to intercept SQL queries, and uses libpq for talking to PostgreSQL. [1] http://sourceforge.net/projects/sqlrelay/ -- 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
Re: [GENERAL] import .sql file into PostgreSQL database
On Thu, Apr 10, 2014 at 1:02 AM, Raymond O'Donnell r...@iol.ie wrote: I think that message is coming from the OS; messages from Postgres are usual more specific, for example, Permission denied for relation. It looks as if you haven't the OS permissions to read the SQL file. On Windows, you need either to play with icacls to set some read permissions or you can change it directly using the properties menu findable by right-clicking on the culprit file. -- 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] is there a way to firmly cap postgres worker memory consumption?
On Tue, Apr 8, 2014 at 6:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, here's the problem: ExprContext: 812638208 total in 108 blocks; 183520 free (171 chunks); 812454688 used So something involved in expression evaluation is eating memory. Looking at the query itself, I'd have to bet on this: ARRAY_TO_STRING(ARRAY_AGG(MM.ID::CHARACTER VARYING), ',') My guess is that this aggregation is being done across a lot more rows than you were expecting, and the resultant array/string therefore eats lots of memory. You might try replacing that with COUNT(*), or even better SUM(LENGTH(MM.ID::CHARACTER VARYING)), just to get some definitive evidence about what the query is asking to compute. The devs have moved on and want to stick with their new query, so I'll just chalk this up to a bad query and let it go. But I'm glad to have learned a few new tricks, thanks. Meanwhile, it seems like ulimit -v would provide the safety valve you asked for originally. Thank you Amador and Tom for the ulimit solution, that's exactly what I needed.
[GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction
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
Re: [GENERAL] Linux vs FreeBSD
Not a great help with which Linux to run, nor Postgres focused, but may be of interest, very relevant to the subject line.. Given the likely respective numbers of each OS actually out there, I'd suggests BSD is very over-represented in the high uptime list which is suggestive. http://uptime.netcraft.com/perf/reports/performance/Hosters?orderby=epercent Cheers, Brent Wood Brent Wood Principal Technician - GIS and Spatial Data Management Programme Leader - Environmental Information Delivery +64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nzhttp://www.niwa.co.nz [NIWA]http://www.niwa.co.nz From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] on behalf of Fran?ois Beausoleil [franc...@teksol.info] Sent: Thursday, April 10, 2014 8:36 AM To: Bruce Momjian Cc: Christofer C. Bell; pgsql-general@postgresql.org Subject: Re: [GENERAL] Linux vs FreeBSD 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 inline: image843a29.JPG
Re: [GENERAL] postgresql.conf question... CPU spikes
On 04/09/2014 09:43 AM, Bala Venkat 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? Thanks for your help. My guess would be you are running queries that dont use indexes, so its table scanning, and the tables all fit in memory. You should run explain analyze on some of your queries and make sure you have good indexes. You could also log slow queries, which might give some hints. Do you think , I have to reduce the effective_cache and work_mem for this? I would doubt it. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Linux vs FreeBSD
On Wednesday, April 09, 2014 09:02:02 PM Brent Wood wrote: Given the likely respective numbers of each OS actually out there, I'd suggests BSD is very over-represented in the high uptime list which is suggestive. Suggestive of ... sysadmins who don't do kernel updates? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
