[GENERAL] Terminating a rogue connection
Assuming a *nix server: if a monitoring program determines that an established connection appears to be trying to so something inappropriate, what's the best way of terminating that session rapidly? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Terminating a rogue connection
On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd wrote: > Assuming a *nix server: if a monitoring program determines that an > established connection appears to be trying to so something inappropriate, > what's the best way of terminating that session rapidly? select pg_terminate_backend(procpid) from pg_stat_activity where . The main difficulty is recognizing which PID to terminate, though. There's a good lot of information available in pg_stat_activity; logins, application names, and connection IP addresses are handy here. But ultimately, it's just pg_terminate_backend. ChrisA -- 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] Terminating a rogue connection
Hi all, in elderly versions, where pg_terminate_backend is missing, you'd issue a kill -15 from the command line. Bèrto On 27 July 2012 09:33, Chris Angelico wrote: > On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd > wrote: >> Assuming a *nix server: if a monitoring program determines that an >> established connection appears to be trying to so something inappropriate, >> what's the best way of terminating that session rapidly? > > select pg_terminate_backend(procpid) from pg_stat_activity where . > > The main difficulty is recognizing which PID to terminate, though. > There's a good lot of information available in pg_stat_activity; > logins, application names, and connection IP addresses are handy here. > But ultimately, it's just pg_terminate_backend. > > ChrisA > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music. -- 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] Terminating a rogue connection
Chris Angelico wrote: On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd wrote: Assuming a *nix server: if a monitoring program determines that an established connection appears to be trying to so something inappropriate, what's the best way of terminating that session rapidly? select pg_terminate_backend(procpid) from pg_stat_activity where . The main difficulty is recognizing which PID to terminate, though. Exactly :-) I'd add that this is a hypothetical situation at present, I'm just trying to plan ahead. There's a good lot of information available in pg_stat_activity; logins, application names, and connection IP addresses are handy here. But ultimately, it's just pg_terminate_backend. ChrisA -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] postgres maintenance db
On Thu, 2012-07-26 at 15:07 -0700, hartrc wrote: > I'm using postgres 9.1.4 on Suse Linux Enterprise Server 11. > After successful installation I by default have one database installed > called postgres. > > I'm starting the process of migrating some database schemas off Oracle and > mysql onto postgres but I want to understand how to best set up the > "databases". > > What is the purpose of the postgres database? It's the default database for tools like createdb, createuser, createlang, dropdb, dropuser, etc. > I try and drop it and get > "maintenance database can't be dropped" error. > This is a pgAdmin message. It doesn't mean you can't drop it with the usual way (DROP DATABASE statement, or the dropdb tool), or with pgAdmin (but you first need to change the maintenance database of your server... BTW, the maintenance database is an expression specific to pgAdmin). > Should I create a separate database that has all my application schemas in > it and let the postgres database be stand-alone, or should I put my > application schemas inside the postgres database? Your choice :) > I didn't really want my database to be called postgres, can it be renamed? > Well, you can drop it or rename it. It will make your life quite hard with the usual tools (createdb and the like). It's way better to add a new database, and keep the postgres database. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] BI tools and postgresql
On 26/07/2012 15:04, Vincent Veyron wrote: The money spent in licences alone would pay for scores of developpers to produce any kind of reporting you will need many times over (the data and its structure is what counts, reporting is easy if you have that) I disagree that licences will cover reporting costs... I come from the other end of the spectrum. We provide reporting systems for telco's banks etc. These are relatively stable, carefully designed reports going to either a small number of high profile clients or to a large number of end users. When we installed a system for a UK telco some years ago it ended up being the biggest outsourced print job in europe. It took up two exchange trunks for data and ran a months reports iin a few days. Initially the reporting hardware was three(ish) mid range sun desktops :-) We dont "do" generic reporting systems - our target audience are established complex and configurable reports that are run periodically and take up lots of manula or system resources. If the Op wants more detail I can pass provide $boss's email address but as I say it is more of a niche reporting product. Jacqui p.s. I am interested in this thread as I have a clinet who has a larg(ish) PG db and creates ad-hoc crosstab style reports. If I can find a tool he could use - he is a salesman and very non technical :-) At the mo I am building reporting tables and using thsi to populate crosstabs in openoffice using datapilot - crude but alot faster than the existing solution which involves manually calcing each cell in the crosstab :-/ openoffice replaces weeks of work with no more that an hours report design/config. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] information_schema.referential_constraints broken?
This query: select * from information_schema.referential_constraints; under PG 8.4.5 (Windows platform) produces this error message: ERROR: operator is not unique: smallint[] <@ smallint[] LINE 1: select $1 <@ $2 and $2 <@ $1 ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. QUERY: select $1 <@ $2 and $2 <@ $1 CONTEXT: SQL function "_pg_keysequal" during inlining ** Error ** ERROR: operator is not unique: smallint[] <@ smallint[] SQL state: 42725 Hint: Could not choose a best candidate operator. You might need to add explicit type casts. Context: SQL function "_pg_keysequal" during inlining I don't have more recent 8.4 releases to try it on. It works fine on PG 9.1.3. Did anyone else experienced this problem? Regards, Igor Neyman
[GENERAL] Switching from OSX to Linux, multi-line queries in \copy don't work anymore
I recently switched from OSX to Linux and \copy in psql no longer accepts multi-line queries. For instance: \copy ( select * from pg_settings ) to '/tmp/settings.csv' with csv header This works fine on OSX. On Linux I get: \copy: parse error at end of line Am I missing something here? Version information: OSX (installed from macports): iDarwin huxley.local 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:32:41 PDT 2011; root:xnu-1504.15.3~1/RELEASE_X86_64 x86_64 PostgreSQL 9.0.7 on x86_64-apple-darwin10.8.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5666) (dot 3), 64-bit Linux (from pitti's ppa): Linux planck 2.6.38-8-server #42-Ubuntu SMP Mon Apr 11 03:49:04 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux PostgreSQL 9.0.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit -Ryan Kelly -- 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] Switching from OSX to Linux, multi-line queries in \copy don't work anymore
On 07/27/2012 09:28 PM, Ryan Kelly wrote: I recently switched from OSX to Linux and \copy in psql no longer accepts multi-line queries. For instance: \copy ( select * from pg_settings ) to '/tmp/settings.csv' with csv header This works fine on OSX. On Linux I get: \copy: parse error at end of line Am I missing something here? A wild guess: I'd say this is a consequence of the fact that psql on OS X uses libedit, on Linux it uses readline. Personally I had no idea that multiline \copy was possible at all. I've always though the way backslash commands are EOL-terminated while everything else is semicolon terminated is a bit of a wart, though. I don't have an answer for you. Using the --no-readline argument makes no difference on my 9.1.4 here. This may just be an area where libedit is smarter than readline - or it might be that I'm totally wrong and the real issue is something else entirely. Thanks for making the effort to produce a good post with all the detailed version info, exact error text, etc. -- Craig Ringer -- 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] Switching from OSX to Linux, multi-line queries in \copy don't work anymore
Craig Ringer wrote: On 07/27/2012 09:28 PM, Ryan Kelly wrote: I recently switched from OSX to Linux and \copy in psql no longer accepts multi-line queries. For instance: \copy ( select * from pg_settings ) to '/tmp/settings.csv' with csv header This works fine on OSX. On Linux I get: \copy: parse error at end of line Am I missing something here? A wild guess: I'd say this is a consequence of the fact that psql on OS X uses libedit, on Linux it uses readline. Personally I had no idea that multiline \copy was possible at all. I've always though the way backslash commands are EOL-terminated while everything else is semicolon terminated is a bit of a wart, though. I don't have an answer for you. Using the --no-readline argument makes no difference on my 9.1.4 here. This may just be an area where libedit is smarter than readline - or it might be that I'm totally wrong and the real issue is something else entirely. Thanks for making the effort to produce a good post with all the detailed version info, exact error text, etc. Also appear to get it here on single-line queries: markMLl=> \copy (select * from pg_settings) to '/tmp/settings.csv' with csv header; \copy: parse error at "select" markMLl=> However my psql and server are rather old which could be an issue (8.1.19 to server 8.4 if I recall correctly). -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Switching from OSX to Linux, multi-line queries in \copy don't work anymore
On Fri, Jul 27, 2012 at 09:49:06PM +0800, Craig Ringer wrote: > On 07/27/2012 09:28 PM, Ryan Kelly wrote: > >I recently switched from OSX to Linux and \copy in psql no longer > >accepts multi-line queries. For instance: > > > >\copy ( > > select > > * > > from > > pg_settings > >) to '/tmp/settings.csv' with csv header > > > >This works fine on OSX. On Linux I get: > >\copy: parse error at end of line > > > >Am I missing something here? > A wild guess: I'd say this is a consequence of the fact that psql on > OS X uses libedit, on Linux it uses readline. My psql on OSX is built with readline: ryan@huxley: otool -L psql /opt/local/lib/postgresql90/bin/psql: /opt/local/lib/postgresql90/libpq.5.dylib (compatibility version 5.0.0, current version 5.3.0) /opt/local/lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0) /opt/local/lib/libreadline.6.2.dylib (compatibility version 6.0.0, current version 6.2.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 125.2.11) Also note that I don't usually type these in directly, rather I use \e to write them in vim. Doesn't make any difference, though. > Personally I had no idea that multiline \copy was possible at all. > I've always though the way backslash commands are EOL-terminated > while everything else is semicolon terminated is a bit of a wart, > though. > > I don't have an answer for you. Using the --no-readline argument > makes no difference on my 9.1.4 here. This may just be an area where > libedit is smarter than readline - or it might be that I'm totally > wrong and the real issue is something else entirely. > > Thanks for making the effort to produce a good post with all the > detailed version info, exact error text, etc. > > -- > Craig Ringer > -Ryan -- 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] Terminating a rogue connection
On Fri, Jul 27, 2012 at 7:09 PM, Mark Morgan Lloyd wrote: > Chris Angelico wrote: >> >> On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd >> wrote: >>> >>> Assuming a *nix server: if a monitoring program determines that an >>> established connection appears to be trying to so something >>> inappropriate, >>> what's the best way of terminating that session rapidly? >> >> >> select pg_terminate_backend(procpid) from pg_stat_activity where . >> >> The main difficulty is recognizing which PID to terminate, though. > > > Exactly :-) > > I'd add that this is a hypothetical situation at present, I'm just trying to > plan ahead. Something I've been developing at work lately combines this with editing pg_hba.conf to ensure that a kicked connection cannot reconnect. Services register themselves with a particular user name, then SET USER to switch to the one actual user who owns tables and stuff, so my overlording monitor can kick off any service based on IP and usename (note the spelling - it's not "username" in the table). Rewrite pg_hba.conf, SIGHUP, then pg_terminate_backend in a searched SELECT as seen above. This may be overkill for what you're doing, though. It's part of our "prevent split-brain problems" technique. ChrisA -- 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] Switching from OSX to Linux, multi-line queries in \copy don't work anymore
On Fri, Jul 27, 2012 at 02:06:01PM +, Mark Morgan Lloyd wrote: > Craig Ringer wrote: > >On 07/27/2012 09:28 PM, Ryan Kelly wrote: > >>I recently switched from OSX to Linux and \copy in psql no longer > >>accepts multi-line queries. For instance: > >> > >>\copy ( > >> select > >> * > >> from > >> pg_settings > >>) to '/tmp/settings.csv' with csv header > >> > >>This works fine on OSX. On Linux I get: > >>\copy: parse error at end of line > >> > >>Am I missing something here? > >A wild guess: I'd say this is a consequence of the fact that psql > >on OS X uses libedit, on Linux it uses readline. > > > >Personally I had no idea that multiline \copy was possible at all. > >I've always though the way backslash commands are EOL-terminated > >while everything else is semicolon terminated is a bit of a wart, > >though. > > > >I don't have an answer for you. Using the --no-readline argument > >makes no difference on my 9.1.4 here. This may just be an area > >where libedit is smarter than readline - or it might be that I'm > >totally wrong and the real issue is something else entirely. > > > >Thanks for making the effort to produce a good post with all the > >detailed version info, exact error text, etc. > > Also appear to get it here on single-line queries: > > markMLl=> \copy (select * from pg_settings) to '/tmp/settings.csv' > with csv header; > \copy: parse error at "select" > markMLl=> > > However my psql and server are rather old which could be an issue > (8.1.19 to server 8.4 if I recall correctly). This works for me, it might be the case that your psql is too old. Perhaps \copy didn't support queries until later? -Ryan -- 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] information_schema.referential_constraints broken?
Answering my own question. Replacing original definition of _pg_keysequal (in information_schema): CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining AS 'select $1 <@ $2 and $2 <@ $1'; with this one: CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1'; makes queries like: select * from information_schema.referential_constraints; against information_schema.referential_constraints work without errors. Sorry, for the noise. Igor Neyman From: Igor Neyman Sent: Friday, July 27, 2012 9:27 AM To: pgsql-general@postgresql.org Subject: information_schema.referential_constraints broken? This query: select * from information_schema.referential_constraints; under PG 8.4.5 (Windows platform) produces this error message: ERROR: operator is not unique: smallint[] <@ smallint[] LINE 1: select $1 <@ $2 and $2 <@ $1 ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. QUERY: select $1 <@ $2 and $2 <@ $1 CONTEXT: SQL function "_pg_keysequal" during inlining ** Error ** ERROR: operator is not unique: smallint[] <@ smallint[] SQL state: 42725 Hint: Could not choose a best candidate operator. You might need to add explicit type casts. Context: SQL function "_pg_keysequal" during inlining I don't have more recent 8.4 releases to try it on. It works fine on PG 9.1.3. Did anyone else experienced this problem? Regards, Igor Neyman
Re: [GENERAL] Terminating a rogue connection
Chris Angelico wrote: On Fri, Jul 27, 2012 at 7:09 PM, Mark Morgan Lloyd wrote: Chris Angelico wrote: On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd wrote: Assuming a *nix server: if a monitoring program determines that an established connection appears to be trying to so something inappropriate, what's the best way of terminating that session rapidly? select pg_terminate_backend(procpid) from pg_stat_activity where . The main difficulty is recognizing which PID to terminate, though. Exactly :-) I'd add that this is a hypothetical situation at present, I'm just trying to plan ahead. Something I've been developing at work lately combines this with editing pg_hba.conf to ensure that a kicked connection cannot reconnect. Services register themselves with a particular user name, then SET USER to switch to the one actual user who owns tables and stuff, so my overlording monitor can kick off any service based on IP and usename (note the spelling - it's not "username" in the table). Rewrite pg_hba.conf, SIGHUP, then pg_terminate_backend in a searched SELECT as seen above. This may be overkill for what you're doing, though. It's part of our "prevent split-brain problems" technique. One problem there is that if somebody is doing something that causes a significant CPU or memory overcommit, it might be some while before SIGHUP etc. works. I'm currently eyeballing the Linux capabilities stuff, it looks as though if a monitor has CAP_NET_ADMIN that it will be able to temporarily add a firewall rule that blocks the rogue client's traffic. I'm hoping to be able to avoid "on the fly" editing of configuration files, there's too much could go wrong. Which I suppose leads into another question... -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Adding users connection via SSL
I'm hoping to be able to avoid "on the fly" editing of configuration files, there's too much could go wrong. Is it possible to create or modify a user connecting via an authenticated/encrypted protocol as an SQL activity, in the same way that CREATE ROLE ... PASSWORD creates one authenticated by password? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] fgets failure in Solaris after patching
Rebuilt the PostgreSQL server software because we were patching up from 9.0.4 to 9.0.8. Deployed software and received the following error when trying to restart server. fgets failure: Error 0 The program postgres is needed by pg_ctl but was not found in the same directory as pg_ctl 9.0.4 works. 9.0.8 does not. The information in this email is confidential and may be legally privileged against disclosure other than to the intended recipient. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. Please immediately delete this message and inform the sender of this error.
Re: [GENERAL] postgres maintenance db
On 07/26/2012 03:07 PM, hartrc wrote: I'm using postgres 9.1.4 on Suse Linux Enterprise Server 11. After successful installation I by default have one database installed called postgres. I'm starting the process of migrating some database schemas off Oracle and mysql onto postgres but I want to understand how to best set up the "databases". What is the purpose of the postgres database? I try and drop it and get "maintenance database can't be dropped" error. Start here: http://www.postgresql.org/docs/9.1/interactive/manage-ag-createdb.html Should I create a separate database that has all my application schemas in it and let the postgres database be stand-alone, or should I put my application schemas inside the postgres database? I didn't really want my database to be called postgres, can it be renamed? Ignore postgres, template0 and template1 "system" databases. Create your user-database(s) with whatever name(s) you wish. As to how to the proper way to migrate, that depends on what you are trying to achieve. Are these databases that you are migrating separate standalone databases being migrated to one machine, do queries need to reference tables on the different databases (i.e. are you merging various databases in the process), etc.? It helps to have an overview. In PostgreSQL a database "cluster" is a collection of separate named databases. A cluster is managed by one master process regardless of the number of databases it contains. A cluster reads a single postgresql.conf file for configuration. User and group information is shared across the entire cluster. That is, there is only one user "steve" in the cluster so "steve" is the same user in any database created in the cluster so while steve may or may not have permission to access certain databases, tables, etc., you cannot have a different user steve in database1 than in database2. And a cluster listens on the assigned address(es) and port(s). One host can have multiple clusters running each with its own configuration, ports, addresses and storage area. One cluster can contain many databases. Each database has one or more schemas (by default all new databases have a schema called "public"). Schema, in this context, is more of a namespace and should not be confused with "schema" in the sense of the layout of your database tables and references. See: http://www.postgresql.org/docs/9.1/static/ddl-schemas.html . Things like foreign keys require tables be within the same database (though the tables can be in different schemas). Queries can join data from different databases, or even different clusters, but that requires use of SQL-MED and/or some contrib modules and can introduce a host of performance, isolation and other issues. Hope this helps. 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] Schema-only dump dumps no constraints, no triggers
Hi, again, I'm sorry about the lack of version information - I concentrated so much on describing the problem correctly, that I forgot to provide basic information. The version at locations is 9.1.4 (though this is irrelevant now), the server is 9.0.4. We found what the problem was. Another problem stems from it, however. Please read on. To add to the information already provided - we have a two-way backup of the template database. One is a WAL replication and the other is londiste (skytools) replication with periodic complete copy. As it turned out, the "stable" script uses not, as we remembered, the actual template database but the londiste-replicated database which was to make next complete copy a few days ago. The copy did not complete, however - the schema-table-column structure transfer completed, but the constraints and triggers did not get through somehow, as there was a lack of hard drive space. Digging on it, we found out that the drive's space was not used up by files in the filesystem, it was filled with deleted files that postgresql server was still clinging on to, probably for a good while. After restarting the server many, many gigabytes were suddenly made available on disk. And this is the new problem - the server has quite a throughput and this is probably what causes the "leakage". How can we force the server to let go of the files? Or maybe it is an actual leak that needs to be studied upon? On a side note, obviously, the Windows dump came out alright because it was from the proper database, not the replicated copy. Best regards, Marek Kielar Dnia 27 lipca 2012 4:46 Adrian Klaver napisał(a): > On 07/26/2012 04:09 PM, Marek Kielar wrote: > > Hi, > > > > we are using "pg_dump -s" (schema-only) to copy the structure of a > > template/prototype database as a set-up for several dozen Fedora boxes. The > > dump used to work alright until very recently. Now, across new machines > > that are to be introduced into the network it consistently refuses to dump > > constraints and triggers - seems pg_dump just skips over them. Otherwise > > the dump seems to be complete, the schema-table-column layout is complete. > > We thought it was the template server problem, but the oddity is that a > > dump made with Windows version of pgAdmin3 comes out complete. > > > > The command we use is: > > /usr/bin/pg_dump -h -p -U -F p -N > > '*somename*' -N 'somename2' -N 'somename3' -N 'somename4' -N 'somename5' -T > > '*somename6*' -s -v -f /some/dir/schemacopy.sql > > > > The dump is made using a script we consider to be stable and therefore it > > hasn't changed since a long while ago. We also weren't able to pin down any > > other change between the systems where it previously worked and the ones > > where it now refuses to - the operating system (Fedora 16) is the same, the > > hardware is the same, the template database server is the same. It doesn't > > matter whether we are running the script on an up-to-date system or an > > outdated-off-liveCD-installation version, so it most probably is not > > update-related. The server (as a system) is sometimes under pretty much > > load so it might be resource-related - be it currently or previously. > > > > Searching through the archives, I have only stumbled upon a post from 2003 > > about a similar issue > > (http://archives.postgresql.org/pgsql-admin/2003-08/msg00239.php) which > > might be connected, however, since the reporter gave up quickly, the issue > > remained unsolved. > > > > How can we dig into this further? What might be happening? > > Postgres version? > Is there more than one version of PG on machine? > The dump made with PgAdmin uses the same parameters? > Any errors in the logs on either the dump or restore side? > > > > > Best regards, > > Marek Kielar > > > > > > > -- 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] information_schema.referential_constraints broken?
Igor Neyman writes: > Answering my own question. > Replacing original definition of _pg_keysequal (in information_schema): > CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean > LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining > AS 'select $1 <@ $2 and $2 <@ $1'; > with this one: > CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean > LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining > AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) > $1'; > makes queries like: > select * from information_schema.referential_constraints; > against information_schema.referential_constraints work without errors. This is known to happen pre-9.0 if you have contrib/intarray installed. 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] REINDEX and COPY is wainting since Jun 21!
Hi, i have postgres 8.3 running in SCO Openserver 5.0.7 today, i had problems to start psql, the error mesage was: 'FATAL Memory out, Detail: Failed on resqueted size ...' , and after i checked the process i noticed the following: the elder REINDEX had been running since Jun-21 by 35 days 12 hrs (35-12:12:08) WTF Can i kill safely these process? there is any risk to corrupt the database??? PPID PID STIME 499 19802 Jun-20 36-12:12:07 postgres: postgres databasefoo 127.0.0.1(4730) VACUUM 499 17203 Jun-20 36-14:42:40 postgres: postgres databasefoo 192.168.1.242(2350) COPY 499 17195 Jun-20 36-14:42:41 postgres: postgres databasefoo 192.168.1.242(2336) idle 13925 13927 Jun-21 35-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 13930 Jun-21 35-12:12:08 postgres: postgres databasefoo 127.0.0.1(4612) REINDEX waiting 7551 7553 Jun-22 34-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 7761 Jun-22 34-12:02:07 postgres: us405 databasefoo 192.168.1.244(3302) DELETE waiting 499 7556 Jun-22 34-12:12:08 postgres: postgres databasefoo 127.0.0.1(1251) REINDEX waiting 1196 1198 Jun-23 33-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 1391 Jun-23 33-12:02:08 postgres: us405 databasefoo 192.168.1.244() DELETE waiting 499 1201 Jun-23 33-12:12:08 postgres: postgres databasefoo 127.0.0.1(3623) REINDEX waiting 24682 24684 Jun-24 32-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 24887 Jun-24 32-12:02:06 postgres: us405 databasefoo 192.168.1.244(2910) DELETE waiting 499 24687 Jun-24 32-12:12:07 postgres: postgres databasefoo 127.0.0.1(2699) REINDEX waiting 18386 18388 Jun-25 31-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 18585 Jun-25 31-12:02:07 postgres: us405 databasefoo 192.168.1.244(4713) DELETE waiting 499 18391 Jun-25 31-12:12:07 postgres: postgres databasefoo 127.0.0.1(3411) REINDEX waiting 12176 12178 Jun-26 30-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 12373 Jun-26 30-12:02:08 postgres: us405 databasefoo 192.168.1.244(2648) DELETE waiting 499 12181 Jun-26 30-12:12:08 postgres: postgres databasefoo 127.0.0.1(3322) REINDEX waiting 5889 5891 Jun-27 29-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 6092 Jun-27 29-12:02:05 postgres: us405 databasefoo 192.168.1.244(4479) DELETE waiting 499 5894 Jun-27 29-12:12:08 postgres: postgres databasefoo 127.0.0.1(1797) REINDEX waiting 29401 29403 Jun-28 28-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 29606 Jun-28 28-12:02:05 postgres: us405 databasefoo 192.168.1.244(2288) DELETE waiting 499 29406 Jun-28 28-12:12:08 postgres: postgres databasefoo 127.0.0.1(4203) REINDEX waiting 23072 23074 Jun-29 27-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 23266 Jun-29 27-12:02:08 postgres: us405 databasefoo 192.168.1.244(4147) DELETE waiting 499 23077 Jun-29 27-12:12:07 postgres: postgres databasefoo 127.0.0.1(4313) REINDEX waiting 16692 16694 Jun-30 26-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 16902 Jun-30 26-12:02:06 postgres: us405 databasefoo 192.168.1.244(1964) DELETE waiting 499 16697 Jun-30 26-12:12:08 postgres: postgres databasefoo 127.0.0.1(4288) REINDEX waiting 10408 10410 Jul-01 25-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 10603 Jul-01 25-12:02:08 postgres: us405 databasefoo 192.168.1.244(3808) DELETE waiting 499 10413 Jul-01 25-12:12:08 postgres: postgres databasefoo 127.0.0.1(3881) REINDEX waiting 4056 4058 Jul-02 24-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 4255 Jul-02 24-12:02:08 postgres: us405 databasefoo 192.168.1.244(1641) DELETE waiting 499 4061 Jul-02 24-12:12:08 postgres: postgres databasefoo 127.0.0.1(4679) REINDEX waiting 27578 27580 Jul-03 23-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 27782 Jul-03 23-12:02:04 postgres: us405 databasefoo 192.168.1.244(3470) DELETE waiting 499 27583 Jul-03 23-12:12:08 postgres: postgres databasefoo 127.0.0.1(1078) REINDEX waiting 22375 22377 Jul-04 22-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 22582 Jul-04 22-12:02:05 postgres: us405 databasefoo 192.168.1.244(1380) DELETE waiting 499 22380 Jul-04 22-12:12:08 postgres: postgres databasefoo 127.0.0.1(2960) REINDEX waiting 16197 16199 Jul-05 21-12:12:08 /usr/local/pgsql/bin/psql -h localhost databasefoo postgres 499 16392 Jul-05 21-12:02:06 postgres: us405 databasefoo 192.168.1.244(3223) DELETE waiting 499 16202 Jul-05 21-12:12:08 postgres: postgres databasefoo 127.0.0.1(1850) REINDEX waiting 9991 9993 Jul-06 20-12:12:08 /usr/local/pgsql/bin/psql -h localho
[GENERAL] can we avoid pg_basebackup on planned switches?
We make heavy use of streaming replication on PG 9.1 and it's been great for us. We do have one issue with it, though, and that's when we switch master nodes - currently, the documentation says that you must run pg_basebackup on your old master to turn it into a slave. That makes sense when the old master had crashed, but it seems that in the case of a planned switch, we could do better. Here's what we tried that seemed to work... are we shooting ourselves in the foot? 1. Cleanly shut down the current master. 2. Pick a slave, turn it into the new master. 3. Copy the new pg_xlog history file over to the old master. 4. On any other slaves (many of our clusters are 3 nodes), we already have "recovery_target_timeline=latest" and wal archiving, so they should already be working as slaves of the new master. 5. Set up recovery.conf on the old master to be like the other slaves. 6. Start up the old master. Have we just avoided running pg_basebackup, or have we just given ourselves data corruption? Because we're using wal archiving, can we simplify and leave out step 3?
Re: [GENERAL] Switching from OSX to Linux, multi-line queries in \copy don't work anymore
Ryan Kelly writes: > I recently switched from OSX to Linux and \copy in psql no longer > accepts multi-line queries. For instance: > \copy ( > select > * > from > pg_settings > ) to '/tmp/settings.csv' with csv header > This works fine on OSX. On Linux I get: > \copy: parse error at end of line FWIW, I get that error on either OS X or Linux, and I'm a bit astonished by your report that there are any versions of psql that allow it. psql doesn't do multi-line backslash commands, in any context. Are you sure you weren't doing a plain SQL "copy" command, without a backslash? 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] fgets failure in Solaris after patching
"Stephan, Richard" writes: > Rebuilt the PostgreSQL server software because we were patching up from 9.0.4 > to 9.0.8. Deployed software and received the following error when trying to > restart server. > fgets failure: Error 0 > The program postgres is needed by pg_ctl but was not found in the same > directory as pg_ctl Is that a verbatim copy of the error message? When I try intentionally provoking this type of failure (by renaming the postgres executable out of the way), 9.0 pg_ctl gives me this: $ pg_ctl start The program "postgres" is needed by pg_ctl but was not found in the same directory as "/home/tgl/version90/bin/pg_ctl". Check your installation. $ The lack of double quotes and the lack of a full path to the pg_ctl program make me wonder if you're running some really old copy of pg_ctl instead of the 9.0 version as intended. Anyway, if you didn't copy-and-paste exactly, what the error indicates is that pg_ctl tried to execute "postgres -V" and didn't get any output. What happens when you try that directly? 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] REINDEX and COPY is wainting since Jun 21!
On Friday, July 27, 2012 11:59:49 AM Ing.Edmundo.Robles.Lopez wrote: > Hi, i have postgres 8.3 running in SCO Openserver 5.0.7 > > today, i had problems to start psql, the error mesage was: 'FATAL > Memory out, Detail: Failed on resqueted size ...' , and after i checked > the process i noticed the following: > > the elder REINDEX had been running since Jun-21 by 35 days 12 hrs > (35-12:12:08) WTF Well it looks like either the COPY or the VACUUM have been running since the 20th and are blocking the other processes. Is that a VACUUM FULL perhaps? > > Can i kill safely these process? > there is any risk to corrupt the database??? I would use pg_cancel_backend() on them. Supposed to be safe, and I've never had corruption issues. -- When the Athenians finally wanted not to give to society but for society to give to them, when the freedom they wished for most was freedom from responsibility, then Athens ceased to be free and was never free again.” -- Edward Gibbon -- 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] BI tools and postgresql
SpagoBI provides extensive and integrated tools for BI/Reporting/Data mining/Realtime BI/ Mobile BI/BAM all under Mozilla Public Licence. The downside is that it's too big for simple reporting/dashboard needs and a quite long learning curve. http://www.spagoworld.org/xwiki/bin/view/SpagoBI/ HTH, --Daoud. On Fri, Jul 27, 2012 at 11:13 AM, Jacqui Caren wrote: > On 26/07/2012 15:04, Vincent Veyron wrote: >> >> The money spent in licences alone would pay for scores of developpers to >> produce any kind of reporting you will need many times over (the data >> and its structure is what counts, reporting is easy if you have that) > > > I disagree that licences will cover reporting costs... > > I come from the other end of the spectrum. We provide reporting systems > for telco's banks etc. These are relatively stable, carefully designed > reports going to either a small number of high profile clients or > to a large number of end users. > > When we installed a system for a UK telco some years ago it ended up > being the biggest outsourced print job in europe. It took up two > exchange trunks for data and ran a months reports iin a few days. > > Initially the reporting hardware was three(ish) mid range sun desktops :-) > > We dont "do" generic reporting systems - our target audience are > established complex and configurable reports that are run > periodically and take up lots of manula or system resources. > > If the Op wants more detail I can pass provide $boss's email address > but as I say it is more of a niche reporting product. > > Jacqui > > p.s. I am interested in this thread as I have a clinet who has a larg(ish) > PG db and creates ad-hoc crosstab style reports. If I can find a tool he > could > use - he is a salesman and very non technical :-) > > At the mo I am building reporting tables and using thsi to populate > crosstabs in openoffice using datapilot - crude but alot faster than > the existing solution which involves manually calcing each cell in > the crosstab :-/ openoffice replaces weeks of work with no more > that an hours report design/config. > > > -- > 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] Switching from OSX to Linux, multi-line queries in \copy don't work anymore
On Fri, Jul 27, 2012 at 12:06 PM, Tom Lane wrote: > Ryan Kelly writes: >> I recently switched from OSX to Linux and \copy in psql no longer >> accepts multi-line queries. For instance: > >> \copy ( >> select >> * >> from >> pg_settings >> ) to '/tmp/settings.csv' with csv header > >> This works fine on OSX. On Linux I get: >> \copy: parse error at end of line > > FWIW, I get that error on either OS X or Linux, and I'm a bit astonished > by your report that there are any versions of psql that allow it. > psql doesn't do multi-line backslash commands, in any context. Are you > sure you weren't doing a plain SQL "copy" command, without a backslash? it can be coerced: postgres=# \copy ( select 0 ) to stdout 0 now -- to do that, I had to use the \e command to do it in vi, then recall the command with readline :-). \copy can work arbitrarily work or fail in all kinds of ways. merlin -- 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] fgets failure in Solaris after patching
Sorry, that was not the verbatim message, (a cut & paste mistake). More information (9.0.8): $ pg_ctl start Killed fgets failure: Error 0 The program "postgres" is needed by pg_ctl but was not found in the same directory as "/opt/postgres/9.0/bin/pg_ctl". Check your installation. $ postgres -V ld.so.1: postgres: fatal: libc.so.1: version `SUNW_1.22.7' not found (required by file /opt/postgres/9.0/bin/postgres) ld.so.1: postgres: fatal: /usr/lib/libc.so.1: wrong ELF class: ELFCLASS32 Killed $ ldd /opt/postgres/9.0/bin/postgres libssl.so.0.9.8 => /opt/postgres/externalDeps/lib/libssl.so.0.9.8 libcrypto.so.0.9.8 => /opt/postgres/externalDeps/lib/libcrypto.so.0.9.8 libnsl.so.1 => /lib/64/libnsl.so.1 libsocket.so.1 =>/lib/64/libsocket.so.1 libm.so.2 => /lib/64/libm.so.2 libldap-2.4.so.2 => /opt/postgres/externalDeps/lib/libldap-2.4.so.2 libc.so.1 => /lib/64/libc.so.1 libc.so.1 (SUNW_1.22.7) => (version not found) libdl.so.1 =>/lib/64/libdl.so.1 libgcc_s.so.1 => /usr/sfw/lib/64/libgcc_s.so.1 libmp.so.2 =>/lib/64/libmp.so.2 libmd.so.1 =>/lib/64/libmd.so.1 libscf.so.1 => /lib/64/libscf.so.1 liblber-2.4.so.2 => /opt/postgres/externalDeps/lib/liblber-2.4.so.2 libresolv.so.2 =>/lib/64/libresolv.so.2 libgen.so.1 => /lib/64/libgen.so.1 libsasl.so.1 => /usr/lib/64/libsasl.so.1 libgss.so.1 => /usr/lib/64/libgss.so.1 libdoor.so.1 => /lib/64/libdoor.so.1 libuutil.so.1 => /lib/64/libuutil.so.1 libcmd.so.1 => /lib/64/libcmd.so.1 /platform/SUNW,Sun-Fire-V890/lib/sparcv9/libc_psr.so.1 /platform/SUNW,Sun-Fire-V890/lib/sparcv9/libmd_psr.so.1 *** 9.0.4 for comparison: $ postgres -V postgres (PostgreSQL) 9.0.4 $ ldd /opt/postgres/9.0/bin/postgres libssl.so.0.9.8 => /opt/postgres/externalDeps/lib/libssl.so.0.9.8 libcrypto.so.0.9.8 => /opt/postgres/externalDeps/lib/libcrypto.so.0.9.8 libnsl.so.1 => /lib/64/libnsl.so.1 librt.so.1 =>/lib/64/librt.so.1 libsocket.so.1 =>/lib/64/libsocket.so.1 libm.so.2 => /lib/64/libm.so.2 libldap-2.4.so.2 => /opt/postgres/externalDeps/lib/libldap-2.4.so.2 libc.so.1 => /lib/64/libc.so.1 libdl.so.1 =>/lib/64/libdl.so.1 libgcc_s.so.1 => /usr/sfw/lib/64/libgcc_s.so.1 libmp.so.2 =>/lib/64/libmp.so.2 libmd.so.1 =>/lib/64/libmd.so.1 libscf.so.1 => /lib/64/libscf.so.1 libaio.so.1 => /lib/64/libaio.so.1 liblber-2.4.so.2 => /opt/postgres/externalDeps/lib/liblber-2.4.so.2 libresolv.so.2 =>/lib/64/libresolv.so.2 libgen.so.1 => /lib/64/libgen.so.1 libsasl.so.1 => /usr/lib/64/libsasl.so.1 libgss.so.1 => /usr/lib/64/libgss.so.1 libdoor.so.1 => /lib/64/libdoor.so.1 libuutil.so.1 => /lib/64/libuutil.so.1 libcmd.so.1 => /lib/64/libcmd.so.1 /platform/SUNW,Sun-Fire-V890/lib/sparcv9/libc_psr.so.1 /platform/SUNW,Sun-Fire-V890/lib/sparcv9/libmd_psr.so.1 Thank you for helping to point out where the actual problem lies. The ldd command is showing that there is a library issue with trying to use the 9.0.8 version. libc.so.1 => /lib/64/libc.so.1 libc.so.1 (SUNW_1.22.7) => (version not found) Richard -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, July 27, 2012 1:21 PM To: Stephan, Richard Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] fgets failure in Solaris after patching "Stephan, Richard" writes: > Rebuilt the PostgreSQL server software because we were patching up from 9.0.4 > to 9.0.8. Deployed software and received the following error when trying to > restart server. > fgets failure: Error 0 > The program postgres is needed by pg_ctl but was not found in the same > directory as pg_ctl Is that a verbatim copy of the error message? When I try intentionally provoking this type of failure (by renaming the postgres executable out of the way), 9.0 pg_ctl gives me this: $ pg_ctl start The program "postgres" is needed by pg_ctl but was not found in the same directory as "/home/tgl/version90/bin/pg_ctl". Check your installation. $ The lack of double quotes and the lack of a full path to the pg_ctl program make me wonder if you're running some really old copy of pg_ctl instead of the 9.0 version as intended. Anyway, if you didn't copy-and-paste exactly, what the error indicates is that pg_ctl tried to execute "postgres -V" and didn't get any output. What happens when you try that directly? regards, tom lane The information in this email is confidential and may be legally privileged against disclosure other than to the intended recipient. It is intended
Re: [GENERAL] REINDEX and COPY is wainting since Jun 21!
thanks a lot for answer. El 27/07/2012 12:26 p.m., Alan Hodgson escribió: On Friday, July 27, 2012 11:59:49 AM Ing.Edmundo.Robles.Lopez wrote: Hi, i have postgres 8.3 running in SCO Openserver 5.0.7 today, i had problems to start psql, the error mesage was: 'FATAL Memory out, Detail: Failed on resqueted size ...' , and after i checked the process i noticed the following: the elder REINDEX had been running since Jun-21 by 35 days 12 hrs (35-12:12:08) WTF Well it looks like either the COPY or the VACUUM have been running since the 20th and are blocking the other processes. Is that a VACUUM FULL perhaps? No, is a vacuum analyze. Can i kill safely these process? there is any risk to corrupt the database??? I would use pg_cancel_backend() on them. Supposed to be safe, and I've never had corruption issues. I will try pg_cancel_backend(). By the way, i reproduced the fail on another database and tested with kill -2 and kill -9 and, the database entered into a database recovery mode. El contenido de este correo electrónico y sus archivos adjuntos son privados y confidenciales y va dirigido exclusivamente a su destinatario. No se autoriza la utilización, retransmisión, diseminación, o cualquier otro uso de esta información por un receptor o entidades distintas al destinatario. Si recibe este correo sin ser el destinatario se le solicita eliminarlo y hacerlo del conocimiento del emisor. La empresa no se hace responsable de transmisiones o comunicaciones no autorizadas o emitidas por personas ajenas a sus colaboradores utilizando éste medio electrónico. The content of this email and its attached files are private and confidential and intended exclusively for the use of the individual or entity to which they are addressed. The retransmission, dissemination, or any other use of this information other than by the intended recipient is prohibited. If you have received this email in error please delete it and notify the sender. The company cannot be held liable for unauthorized electronic transmissions or communications, nor for those emitted by non-company individuals and entities. -- 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] REINDEX and COPY is wainting since Jun 21!
El 27/07/2012 12:26 p.m., Alan Hodgson escribió: On Friday, July 27, 2012 11:59:49 AM Ing.Edmundo.Robles.Lopez wrote: Hi, i have postgres 8.3 running in SCO Openserver 5.0.7 today, i had problems to start psql, the error mesage was: 'FATAL Memory out, Detail: Failed on resqueted size ...' , and after i checked the process i noticed the following: the elder REINDEX had been running since Jun-21 by 35 days 12 hrs (35-12:12:08) WTF Well it looks like either the COPY or the VACUUM have been running since the 20th and are blocking the other processes. Is that a VACUUM FULL perhaps? No, is a vacuum analyze. Can i kill safely these process? there is any risk to corrupt the database??? I would use pg_cancel_backend() on them. Supposed to be safe, and I've never had corruption issues. thanks, i will try with pg_cancel_backend. El contenido de este correo electrónico y sus archivos adjuntos son privados y confidenciales y va dirigido exclusivamente a su destinatario. No se autoriza la utilización, retransmisión, diseminación, o cualquier otro uso de esta información por un receptor o entidades distintas al destinatario. Si recibe este correo sin ser el destinatario se le solicita eliminarlo y hacerlo del conocimiento del emisor. La empresa no se hace responsable de transmisiones o comunicaciones no autorizadas o emitidas por personas ajenas a sus colaboradores utilizando éste medio electrónico. The content of this email and its attached files are private and confidential and intended exclusively for the use of the individual or entity to which they are addressed. The retransmission, dissemination, or any other use of this information other than by the intended recipient is prohibited. If you have received this email in error please delete it and notify the sender. The company cannot be held liable for unauthorized electronic transmissions or communications, nor for those emitted by non-company individuals and entities. -- 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] fgets failure in Solaris after patching
"Stephan, Richard" writes: > More information (9.0.8): > $ pg_ctl start > Killed > fgets failure: Error 0 > The program "postgres" is needed by pg_ctl but was not found in the > same directory as "/opt/postgres/9.0/bin/pg_ctl". > Check your installation. > $ postgres -V > ld.so.1: postgres: fatal: libc.so.1: version `SUNW_1.22.7' not found > (required by file /opt/postgres/9.0/bin/postgres) > ld.so.1: postgres: fatal: /usr/lib/libc.so.1: wrong ELF class: ELFCLASS32 > Killed Ah-hah, so your problem is a library mismatch between where you compiled postgres and where you're trying to run it. This is not the first time we've had to suggest that people run "postgres -V" manually to debug a problem. I see that find_other_exec() intentionally routes the stderr output of that to /dev/null: snprintf(cmd, sizeof(cmd), "\"%s\" -V 2>%s", retpath, DEVNULL); It strikes me that this is just a damfool idea. Generally there should be no stderr output, and if there is some, hiding it from the user is not helpful. Does anyone object to removing that redirection? 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] Postgresql Developer Privileges
Postgresql v9.1.4 SUSE Linux Enterprise Server SP2 In my agency we have application developers who do most of the database design/development themselves (not my choice) in the development environment but do not administer the database. Therefore I want developers to be able to create & drop tables, create & drop indexes, views, triggers, procedures sequences etc. But I don’t want them to be able to create login roles or shutdown the database or drop schemas. Basically they need to be a “power user” but definitely not a superuser. The main issue I’m having in Postgresql is that I can give them permission to create objects in a schema but not drop objects if they are not the owner. I don’t want all developers to use a shared login role or have access to a superuser account. Also I don’t think it makes sense for the developers to really own the object themselves. Is there a way to achieve the following? Basically each developer has their own login role Developers can create and drop objects in schemas in which they have create privileges granted. Developers can drop objects created by other developers Developers do not own the objects themselves Thank You Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-Developer-Privileges-tp5718244.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Postgresql Developer Privileges
On 07/27/12 12:30 PM, hartrc wrote: In my agency we have application developers who do most of the database design/development themselves (not my choice) in the development environment but do not administer the database. Therefore I want developers to be able to create & drop tables, create & drop indexes, views, triggers, procedures sequences etc. But I don’t want them to be able to create login roles or shutdown the database or drop schemas. Basically they need to be a “power user” but definitely not a superuser. The main issue I’m having in Postgresql is that I can give them permission to create objects in a schema but not drop objects if they are not the owner. I don’t want all developers to use a shared login role or have access to a superuser account. Also I don’t think it makes sense for the developers to really own the object themselves. Is there a way to achieve the following? Basically each developer has their own login role Developers can create and drop objects in schemas in which they have create privileges granted. Developers can drop objects created by other developers I think I'd create the database owned by a group role like 'developer', and make each developer a member of this role. Developers do not own the objects themselves thats a little more tricky, as AFAIK only a 'superuser' can change the role that owns an object, by default its owned by the rule that created it, but a user who's a member of a group can SET ROLE group; and then any objects they create belong to that group, or they can ALTER TABLE/VIEW/etc name OWNER TO newowner; for any role they are a member of. -- john r pierceN 37, W 122 santa cruz ca mid-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] Schema-only dump dumps no constraints, no triggers
On 07/27/2012 09:32 AM, Marek Kielar wrote: Hi, again, I'm sorry about the lack of version information - I concentrated so much on describing the problem correctly, that I forgot to provide basic information. The version at locations is 9.1.4 (though this is irrelevant now), the server is 9.0.4. We found what the problem was. Another problem stems from it, however. Please read on. To add to the information already provided - we have a two-way backup of the template database. One is a WAL replication and the other is londiste (skytools) replication with periodic complete copy. As it turned out, the "stable" script uses not, as we remembered, the actual template database but the londiste-replicated database which was to make next complete copy a few days ago. The copy did not complete, however - the schema-table-column structure transfer completed, but the constraints and triggers did not get through somehow, as there was a lack of hard drive space. Digging on it, we found out that the drive's space was not used up by files in the filesystem, it was filled with deleted files that postgresql server was still clinging on to, probably for a good while. After restarting the server many, many gigabytes were suddenly made available on disk. And this is the new problem - the server has quite a throughput and this is probably what causes the "leakage". How can we forc e the server to let go of the files? Or maybe it is an actual leak that needs to be studied upon? On a side note, obviously, the Windows dump came out alright because it was from the proper database, not the replicated copy. What where the deleted files? WAL, Logs, other? What type of WAL replication are you doing? Streaming, log shipping, etc? What are your settings for the WAL replication? In particular wal_keep_segments ? Is the WAL replication actually working? Best regards, Marek Kielar -- 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] Trim not working (PostgreSQL 9.1.2 on Win64)
I've a select with the following expression:
select trim(both ' ' from substring(rslinha2 from 5 for position('(-)'
in rslinha2)-6))
from ...
problem is that the spaces are not being removed from either side. What
would be wrong?
Thanks for your help,
Edson Richter
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Elegant method of accessing N-dimension array in C function
Hi, I'm wondering if there is a more elegant (best practice?) approach to accessing and processing a N-dimension array from the SQL side in a C function. I've looked at deconstruct_array() but would like a second opinion before I use that function. Basically, I have a function that receives a double precision[][] that is processed upon in my C function. Thanks! -bborie -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.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] Trim not working (PostgreSQL 9.1.2 on Win64)
Em 27/07/2012 21:04, Edson Richter escreveu:
I've a select with the following expression:
select trim(both ' ' from substring(rslinha2 from 5 for position('(-)'
in rslinha2)-6))
from ...
problem is that the spaces are not being removed from either side.
What would be wrong?
Thanks for your help,
Edson Richter
Ok, sorry for answering my self. I just created an "alltrim" function
with the following code:
CREATE OR REPLACE FUNCTION alltrim(text)
RETURNS text AS
$BODY$
select regexp_replace(regexp_replace($1, '^\s*', ''), '\s*$', '');
$BODY$
LANGUAGE sql VOLATILE
COST 100;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trim not working (PostgreSQL 9.1.2 on Win64)
I've a select with the following _expression_:
select trim(both ' ' from substring(rslinha2 from 5 for
position('(-)' in rslinha2)-6))
from ...
problem is that the spaces are not being removed from either side.
What would be wrong?
Thanks for your help,
--
Edson Carlos Ericksson Richter
SimKorp Informática Ltda
Fone:
(51) 3366-7964
Celular:
(51)9318-9766/(51)
8585-0796
