Re: [GENERAL] 8.4.0 installer for Windows from EnterpriseDB does not seem to include pgagent
On Thu, Jul 23, 2009 at 08:45, Steffen Kuhn wrote: > Hallo Knut, > > this is right see following link for details about enterpriseDB > installers and provided features > http://www.enterprisedb.com/products/postgres_plus/overview.do#ui-tabs-6 > 8 That page refers to the Postgres Plus product. The community product packaged by enterprisedb is something different. I thought we had pgagent as a stackbuilder application, but it seems I was mistaken. Dave? -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] A question on PSQL 8.3 setup
Hi, folks? I'm hoping this is the right mailing list for this sort of question. If not, I apologize you any inconvenience in advance, though. I'm trying to set up postgreql 8.3 in a box running Ubuntu 8.04 and PSQL 8.3 Thisbox is sort of misconfigured because it has 15GB for / and 3 TB for /home. Due to the relatively large size of database, about 470 GB, I need to configure PSQL to store all DB-related data into /home/pgsql/data instead of somewhere in / such as /var/lib/ postgresql/8.3/... or /usr/local/pgsql/data. Unfortunately, however, I'm not allowed to re-partition but to use the existing ones. If I'm not mistaken, I can use initdb and tablespace in order to expand database clusters and tables to a new location such as /home/pgsql/data. However, whenever I try to dump a DB in, it seems to use / and consumes the whole space assigned for /, no matter what I do with initdb and tablespace. I might be wrong with using initdb and tablespaces, but it's very weird to me, though. It'd be greatly appreciated if someone helps me to redirect all DB stuff into /home/pgsql/data directory instead of some directories in /. Here are the procedure that I did: > sudo mkdir /home/pgsql > sudo mkdir /home/pgsql/data > sudo chown postgres /home/pgsql/data > sudo mkdir /home/pgsql/ts > sudo chown postgres /home/pgsql/ts > sudo su - postgres > initdb -D /home/pgsql/data > pg_ctl -D /home/pgsql/data -l logfile start > createdb DBNAME > createuser USER > psql -s DBNAME db=> create tablespace dbts location '/home/pgsql/ts'; db=> set default_tablespace=dbts; >ctrl+D > exit > psql db < 470GB_DB_DUMPED_FILE SET SET SET SET SET SET SET SET CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE setval --- 200607607 (1 row) ALTER TABLE ERROR: could not extend relation 1663/16384/21734: No space left on device HINT: Check free disk space. CONTEXT: COPY packets, line 22968771: "53 115989288 2008-09-20 23:13:00.640317-06 4 5 1500 14995 2 16384 64 6 26411 64.106.46.186 222.134..." Thanks a lot, Jong
Re: [GENERAL] 8.4.0 installer for Windows from EnterpriseDB does not seem to include pgagent
On Thu, Jul 23, 2009 at 8:06 AM, Magnus Hagander wrote: > On Thu, Jul 23, 2009 at 08:45, Steffen Kuhn wrote: >> Hallo Knut, >> >> this is right see following link for details about enterpriseDB >> installers and provided features >> http://www.enterprisedb.com/products/postgres_plus/overview.do#ui-tabs-6 >> 8 > > That page refers to the Postgres Plus product. The community product > packaged by enterprisedb is something different. I thought we had > pgagent as a stackbuilder application, but it seems I was mistaken. > Dave? pgAgent binaries can be found at http://www.postgresql.org/ftp/pgadmin3/release/pgagent/ There's no StackBuilder component for it. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] synchronous_commit=off doesn't always return immediately
Hi, I set synchronous_commit to off and expected trivial inserts (single row, 6-8 columns) to always return quickly. However, I see that during checkpoints they sometimes take over a second. I tried setting the full_page_write to off, but this didn't seem to have an effect. The version I'm using is 8.3.3 The configuration is: bgwriter_delay = 20ms # 10-1ms between rounds bgwriter_lru_maxpages = 1000# 0-1000 max buffers written/round bgwriter_lru_multiplier = 5.0 # 0-10.0 multipler on buffers scanned/round synchronous_commit = off# immediate fsync at commit full_page_writes = off # recover from partial page writes checkpoint_segments = 32# in logfile segments, min 1, 16MB each checkpoint_timeout = 30min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 checkpoint_warning = 30s# 0 is off Thanks, Tomer -- View this message in context: http://www.nabble.com/synchronous_commit%3Doff-doesn%27t-always-return-immediately-tp24621119p24621119.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
[GENERAL] PostgreSQL 8.4.0-1 windwos crashes when run without admin privs
trying to run postgresql-8.4.0-1-windows.exe as a user without admin-privs leads to: "PostgreSQL has detected a problem and has to be ended" (on german) Problem-Signator: AppName: postgresql-8.4.0-1 -windows.exe ModVer: 1.0.0.0 Offset: 0004df8b the same also happens when only trying to read the --help without admin privs. Operating System: Windows XP. could this be connected to a screwed up Internet Explorer Installation? Does the installer use IE to do something? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad?
Re: [GENERAL] table.column in query results?
Andrew Klaassen wrote: > Is it possible to get table.column in query results rather > than just column? > > I.e. I'd like: > > SELECT * FROM foo, bar; > foo.id | foo.name | bar.id | bar.text > ---+--++- > ... > > ...rather than: > > SELECT * FROM foo, bar; > id | name | id | text > ---+--++- > ... You'd have to use aliases, like SELECT id AS "foo.id" ... Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] enabling join_collapse_limit for a single query only
groovefillet wrote: > Is it possible to set the runtime parameter 'join_collapse_limit' for > a single query only without setting/unsetting it before/after? Yes: START TRANSACTION; SET LOCAL join_collapse_limit = 42; SELECT . COMMIT; Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with pg_restore?
On 2009-07-14, Jim Michaels wrote:
>
> --0-1060148048-1247615236=:84835
> Content-Type: text/plain; charset=us-ascii
>
> I am having problems with pg_restore. pg_restore
> --file=c:\pg-jmichae3-7-13-2009.sql --verbose --host=localhost --port=5432
> --username=postgres
>
> this just hangs.
> I am restoring from 8.3.7 to 8.4 - what did I do wrong?
> could somebody rewrite pg_dumpall and pg_dump so that it makes editable dumps?
> most programmer's text editors can't handle more than 2000 characters per
> line.
any text editor with a line length limit shorter than 1 megabyte is a
liability. that said if you dump as inserts there's probably a way to
use sed to split the long strings and still have a loadable dump.
> and I want to be able to edit my dumps.
I find that jed is powerful, fast, and reasonably easy to use. and had
no problem with 2.3MB lines.
gnome-text-editor ("gedit") handles lines of tens of thousands of
characters OK but seems to have problems displaying million character
lines,
I expect ther big guns "vim" and "emacs" also have no problems with
long lines.
--
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] Server Backup: pg_dump vs pg_dumpall
On 2009-07-20, APseudoUtopia wrote: > --001636c5b16936e279046f2a9776 > Content-Type: text/plain; charset=UTF-8 > Content-Transfer-Encoding: 7bit > > Hey, > > I'm writing a backup script. Right now, I only have one database on my > postgresql server. I'm deciding if I should use pg_dump or pg_dumpall when > backing up the server. As far as I can tell, pg_dumpall cannot compress the > dumps automatically and it only dumps data in the standard SQL text file > format. This means that I would not be able to use pg_restore to selectively > restore the database, correct? > > What is *NOT* included in a pg_dump that IS in pg_dumpall (Other than all > the databases)? Things like user-defined functions and datatypes? Roles? > Views? roles mostly AIUI > I was leaning towards pg_dumpall, but then I realized that it only dumps in > the standard SQL text file format, and it cannot be compressed > automatically. you can pipe its product through your favourite stream compressor (lzma, bzip2, gzip etc) -- 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] table.column in query results?
On 2009-07-22, Andrew Klaassen wrote: > Hi, > > Is it possible to get table.column in query results rather than just column? it is possible to get the OID of the table from libpq. I don't think psql provides display of the table name as a formatting option, and most wrapper around libpq seem to ignore this feature. if you can manipulate the query you can alias the column in the query and hope that none of the tables or columns has a dot in its name already! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] comparing NEW and OLD (any good this way?)
Hi, My colleage Geard Troost and I found a handy way of comparing OLD and NEW in a trigger function. Normally this does not work (if anyone can tell me why, that'd be great), but once you cast them to text, it does. Is there anything to say against this, or can i go ahead and recommend this to everyone who wants to check if anything changed before doing what their update triggers do? Cheers, WBL Here's the code: drop table test; create table test (id integer primary key, value integer); insert into test values (1,1); insert into test values (2,1); insert into test values (3,1); insert into test values (4,1); insert into test values (5,1); insert into test values (6,1); create or replace function bla() returns trigger as $$ begin IF (NEW::TEXT = OLD::TEXT) THEN raise notice 'changed'; END IF; return NEW; end $$ language plpgsql; CREATE TRIGGER test_bla BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE public.bla(); update test set value =NULL where id= 1; update test set value =NULL where id= 1; -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: unexpected data beyond EOF in block of relation "RelationName"
Hi, I'm getting the following error from my Postgres database while inserting: ERROR: unexpected data beyond EOF in block of relation "RelationName". My configuration is: openSUSE 11.1 Linux linux-wsr1 2.6.27.23-0.1-default #1 SMP 2009-05-26 17:02:05 -0400 x86_64 x86_64 x86_64 GNU/Linux Postgres 8.3.7 Can anybody advise how to solve this problem? Thanks! Marcin -- 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] comparing NEW and OLD (any good this way?)
Hello http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body regards Pavel Stehule 2009/7/23 Willy-Bas Loos : > Hi, > > My colleage Geard Troost and I found a handy way of comparing OLD and > NEW in a trigger function. > Normally this does not work (if anyone can tell me why, that'd be > great), but once you cast them to text, it does. > > Is there anything to say against this, or can i go ahead and recommend > this to everyone who wants to check if anything changed before doing > what their update triggers do? > > Cheers, > > WBL > > Here's the code: > > drop table test; > create table test (id integer primary key, value integer); > insert into test values (1,1); > insert into test values (2,1); > insert into test values (3,1); > insert into test values (4,1); > insert into test values (5,1); > insert into test values (6,1); > > create or replace function bla() returns trigger as $$ > begin > IF (NEW::TEXT = OLD::TEXT) THEN raise notice 'changed'; > END IF; > return NEW; > end > $$ > language plpgsql; > > CREATE TRIGGER test_bla BEFORE UPDATE > ON test FOR EACH ROW > EXECUTE PROCEDURE public.bla(); > > update test set value =NULL where id= 1; > update test set value =NULL where id= 1; > > -- > "Patriotism is the conviction that your country is superior to all > others because you were born in it." -- George Bernard Shaw > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ECPG Deallocate PREPARE statement - bug ?
Hi guys, I have a program that I need compile using PostgreSQL 8.4.0 (or later) and it must be able to run on an 8.3.5 based system as well as 8.4.0. I'm using embedded SQL for C and I have the following sequence of statements: snprintf( stmt, 3000, "SELECT count(*) FROM %s WHERE %s", *table, *where ); EXEC SQL AT :_thisDbConn PREPARE cntstmt FROM :stmt; EXEC SQL AT :_thisDbConn EXECUTE cntstmt INTO :recCount :fnull; . . EXEC SQL DEALLOCATE PREPARE cntstmt; This seems to be ok running on the 8.4.0 system, but when running it on the 8.3.5, it complains that it is an 'Invalid statement name cntstmt' for the deallocation. I then tried to add the 'AT :_thisDbConn' to the DEALLOCATE statement, but ecpg complained that there was no "at" allowed for deallocate. However, looking at the output (the .c file) I noticed that it had generated an apparently correct ECPG_deallocate() call. Manually compiling this and linking the program turned out to be able to run on both the 8.3.5 and the 8.4.0 system without problems. Is this a bug in ecpg or am I doing something wrong ? Please advise, Leif -- 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] comparing NEW and OLD (any good this way?)
Pavel Stehule, 23.07.2009 13:45: Hello http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body regards Pavel Stehule That collection of tips is really nice. Why isn't there a link from the Postgres Wiki to your page? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] comparing NEW and OLD (any good this way?)
Pavel Stehule schrieb: Hello http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body regards Pavel Stehule Pavel, this trick-list is awesome ;-) Thanks for the tip! Cheers Andy P.S.: a link to that would be nice ;-) -- 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] A question on PSQL 8.3 setup
Jong Chun Park wrote: > > sudo mkdir /home/pgsql > > sudo mkdir /home/pgsql/data > > sudo chown postgres /home/pgsql/data > > sudo mkdir /home/pgsql/ts > > sudo chown postgres /home/pgsql/ts > > sudo su - postgres > > initdb -D /home/pgsql/data > > pg_ctl -D /home/pgsql/data -l logfile start Can you issue as a superuser in psql: SHOW data_directory; and check that the result is what you expect? Also, are you using the ubuntu postgresql-8.3 package or a self-compiled version? Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] comparing NEW and OLD (any good this way?)
2009/7/23 Andreas Wenk : > Pavel Stehule schrieb: >> >> Hello >> >> >> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body >> >> regards >> Pavel Stehule >> > > Pavel, this trick-list is awesome ;-) Thanks for the tip! > > Cheers > > Andy > > P.S.: a link to that would be nice ;-) > look on http://wiki.postgresql.org/wiki/Category:Snippets There are link on tricks page. Pavel -- 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] 8.4.0 installer for Windows from EnterpriseDB does not seem to include pgagent
Dave, Are there any plans to add the plug-ins that were available in the 8.3 install to the stackbuilder component? Best Regards Michael Gould "Dave Page" wrote: > On Thu, Jul 23, 2009 at 8:06 AM, Magnus Hagander wrote: >> On Thu, Jul 23, 2009 at 08:45, Steffen Kuhn wrote: >>> Hallo Knut, >>> >>> this is right see following link for details about enterpriseDB >>> installers and provided features >>> http://www.enterprisedb.com/products/postgres_plus/overview.do#ui-tabs-6 >>> 8 >> >> That page refers to the Postgres Plus product. The community product >> packaged by enterprisedb is something different. I thought we had >> pgagent as a stackbuilder application, but it seems I was mistaken. >> Dave? > > pgAgent binaries can be found at > http://www.postgresql.org/ftp/pgadmin3/release/pgagent/ > > There's no StackBuilder component for it. > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] comparing NEW and OLD (any good this way?)
Pavel Stehule, 23.07.2009 14:50: look on http://wiki.postgresql.org/wiki/Category:Snippets That page is not accessible from the Wiki's main page (at least I can't find an easy way to navigate there) I think there should be a prominent link right at the start page that links to that page and your excellent collection. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with pg_restore?
On Thu, Jul 23, 2009 at 10:47:40AM +, Jasen Betts wrote:
> I find that jed is powerful, fast, and reasonably easy to use. and had
> no problem with 2.3MB lines.
>
> gnome-text-editor ("gedit") handles lines of tens of thousands of
> characters OK but seems to have problems displaying million character
> lines,
>
> I expect ther big guns "vim" and "emacs" also have no problems with
> long lines.
GNU Emacs is fine; just tried with a line consisting of a million copies
of "helloworld " and it was a bit slow with some operations but wasn't
as bad as I was expecting.
--
Sam http://samason.me.uk/
--
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] comparing NEW and OLD (any good this way?)
On Thu, Jul 23, 2009 at 7:45 AM, Pavel Stehule wrote: > Hello > > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body note: in PostgreSQL 8.4, you can compare record variables directly with standard boolean operators. 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] A question on PSQL 8.3 setup
On Thu, Jul 23, 2009 at 01:08:26AM -0600, Jong Chun Park wrote: > I need to configure PSQL to store all DB-related data into > /home/pgsql/data instead of somewhere in / such as /var/lib/ > postgresql/8.3/... or /usr/local/pgsql/data. Assuming you're using the standard builds in Ubuntu, I'd probably do something like: 1) make your directory if you haven't got one already: mkdir /home/postgres chown postgres:postgres /home/postgres 2) shut down postgres /etc/init.d/postgres-8.3 stop 3) copy the existing database cluster over to its new location cp -a /var/lib/postgresql/8.3 /home/postgres 4) move the old cluster out of the way for safe keeping and put a symlink back in place mv /var/lib/postgresql/8.3 /var/lib/postgresql/8.3-old ln -s /home/postgres/8.3 /var/lib/postgresql/8.3 5) start PG again /etc/init.d/postgres-8.3 start if that all works then you've got the option of dumping the old data that was stashed away above and update the config file to point straight to the new location. -- Sam http://samason.me.uk/ -- 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] problem with pg_restore?
On Tue, Jul 14, 2009 at 5:47 PM, Jim Michaels wrote: > I am having problems with pg_restore. pg_restore > --file=c:\pg-jmichae3-7-13-2009.sql --verbose --host=localhost --port=5432 > --username=postgres > > this just hangs. > I am restoring from 8.3.7 to 8.4 - what did I do wrong? > > could somebody rewrite pg_dumpall and pg_dump so that it makes editable > dumps? > most programmer's text editors can't handle more than 2000 characters per > line. > and I want to be able to edit my dumps. When I need to make changes to large dumps I use tools like sed, awk and diff, not a text editor. -- 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] comparing NEW and OLD (any good this way?)
On Thu, Jul 23, 2009 at 01:45:36PM +0200, Pavel Stehule wrote: > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks Just had a quick flick through your list and one of the early ones stuck out: http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types is scary; even worse is that it was changed to be like this in 8.2 because the standard says it should behave this way. What on earth were they thinking when they defined the standard this way? -- Sam http://samason.me.uk/ -- 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] problem with pg_restore?
On Thu, Jul 23, 2009 at 07:40:18AM -0600, Scott Marlowe wrote:
> On Tue, Jul 14, 2009 at 5:47 PM, Jim Michaels wrote:
> > could somebody rewrite pg_dumpall and pg_dump so that it makes editable
> > dumps?
> > most programmer's text editors can't handle more than 2000
> > characters per line. and I want to be able to edit my dumps.
>
> When I need to make changes to large dumps I use tools like sed, awk
> and diff, not a text editor.
Indeed, but I still like to be able to use a text editor to verify that
my code is doing the right thing. Obviously for large files (i.e. a GB
and over) it's not going to work, but I'd still expect tools to work
("less -n" seems to be my tool of choice at the moment).
--
Sam http://samason.me.uk/
--
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] Copying only incremental records to another DB..
On Tue, Jun 30, 2009 at 11:21 PM, Scott Ribe wrote: > Shut down the postmasters and rsync. (Assuming same architecture & build > options...) > You mean rsync the "data" folder, or the entire PG folder? Architecture may be the same (same processor) but the setup is a touch different: SCSI hard disks instead of SATA, and RAID10 instead of RAID1. Plus, the version of PG is different: this is 8.2.9, that is 8.4. Will this be a challenge? -- 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] Copying only incremental records to another DB..
> You mean rsync the "data" folder, or the entire PG folder? I meant the data folder. > Will this be a challenge? Yes, if you're using different major PG releases, then the data files are not binary compatible. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- 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] comparing NEW and OLD (any good this way?)
2009/7/23 Merlin Moncure : > On Thu, Jul 23, 2009 at 7:45 AM, Pavel Stehule wrote: >> Hello >> >> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body > > note: in PostgreSQL 8.4, you can compare record variables directly > with standard boolean operators. > actualised Thank You Pavel > 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] synchronous_commit=off doesn't always return immediately
tomrevam writes: > I set synchronous_commit to off and expected trivial inserts (single row, > 6-8 columns) to always return quickly. However, I see that during > checkpoints they sometimes take over a second. What sort of disk hardware have you got? It sounds overstressed. It might help to increase wal_buffers. 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] ERROR: unexpected data beyond EOF in block of relation "RelationName"
Marcin Gon writes: > I'm getting the following error from my Postgres database while inserting: > ERROR: unexpected data beyond EOF in block of relation "RelationName". You omitted the HINT that says this has only been known to occur in connection with buggy kernels. I see you're running a fairly new kernel, but it might still pay to inquire whether it's subject to the wrong-lseek-result race condition that was around ahile back. As far as getting out of the immediate problem is concerned, I think restarting the postmaster ought to fix it. 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] Search Path vs Synonyms
Given the situation where a user connecting to the database needs access to two separate schemas: the primary schema which contains the data they will be updating and a second schema which contains read-only reference data, used by many users, that will be using in joins on queries. I don't want to have to use fully qualified names (I am migrating code from Oracle which uses synonyms), so what is the best way (in both performance and reliability) to refer to the tables; Search Path or Synonyms? Thanks. CONFIDENTIALITY - This email and any files transmitted with it, are confidential, may be legally privileged and are intended solely for the use of the individual or entity to whom they are addressed. If this has come to you in error, you must not copy, distribute, disclose or use any of the information it contains. Please notify the sender immediately and delete them from your system. SECURITY - Please be aware that communication by email, by its very nature, is not 100% secure and by communicating with Perform Group by email you consent to us monitoring and reading any such correspondence. VIRUSES - Although this email message has been scanned for the presence of computer viruses, the sender accepts no liability for any damage sustained as a result of a computer virus and it is the recipients responsibility to ensure that email is virus free. AUTHORITY - Any views or opinions expressed in this email are solely those of the sender and do not necessarily represent those of Perform Group. COPYRIGHT - Copyright of this email and any attachments belongs to Perform Group, Companies House Registration number 6324278.
[GENERAL] split string by special characters
I wonder if you could please help me out to extract a character string
to an array or better yet, a table.
I'd like to split strings of text up into words and delimiters (but
not delete the delimiters). The delimiters are defined as comma,
space, dot, singe/double quotation mark, question mark etc.¹ in a
separate table (delimiters) depending on what rules apply for the
input.
regexp_split_to_array/table seem quite suitable but I have
difficulties to form the right expression with it, apart from that it
remove the delimiters as well.
Example:
This is just a text that contain special characters such as , (comma),
"(", ")" (left and right parenthesis) as well as "?" question mark.
How do I split it up with PostgreSQL?
Expected result:
{This, " ", is, " ", just, " ", a, ..., PostgreSQL, "?" }
__
¹) Also later on tags such as and at other times something
else depending on the circumstances.
//Jan-Erik
--
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] enabling join_collapse_limit for a single query only
Hi, and thanks for the reply. We're issuing queries from within our application where transactions span a whole unit of work, so wrapping a single query in a transaction just to set the param isn't really feasible except on a separate connection, which i am loathe to do. I was really hoping to be able to set join_collapse_limit=1 *just* on the single query, as a kind of query hint, eg: /* !hint:join_collapse_limit=1 */ SELECT ... I take it this is this not possible in postgres? cheers, Matt h On 23/07/2009, at 09:50, Albe Laurenz wrote: groovefillet wrote: Is it possible to set the runtime parameter 'join_collapse_limit' for a single query only without setting/unsetting it before/after? Yes: START TRANSACTION; SET LOCAL join_collapse_limit = 42; SELECT . COMMIT; Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with pg_restore?
On Thu, Jul 23, 2009 at 3:14 PM, Sam Mason wrote:
> On Thu, Jul 23, 2009 at 07:40:18AM -0600, Scott Marlowe wrote:
>> On Tue, Jul 14, 2009 at 5:47 PM, Jim Michaels wrote:
>> > could somebody rewrite pg_dumpall and pg_dump so that it makes editable
>> > dumps?
>> > most programmer's text editors can't handle more than 2000
>> > characters per line. and I want to be able to edit my dumps.
You need to get yourself a better editor.
>> When I need to make changes to large dumps I use tools like sed, awk
>> and diff, not a text editor.
>
> Indeed, but I still like to be able to use a text editor to verify that
> my code is doing the right thing. Obviously for large files (i.e. a GB
> and over) it's not going to work, but I'd still expect tools to work
> ("less -n" seems to be my tool of choice at the moment).
Actually traditionally tools like sed, awk, etc had fixed-size line
buffers. If your system has a BSD-derived set of tools you may or may
not run into problems depending on whether they've been reimplemented
since. One of the main distinguishing features of the GNU tools was
specifically that they had a policy of choosing implementations that
removed arbitrary limits even if it meant less efficient
implementations.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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] comparing NEW and OLD (any good this way?)
On Thu, Jul 23, 2009 at 01:40:45PM +0200, Willy-Bas Loos wrote: > Is there anything to say against this, or can i go ahead and recommend > this to everyone who wants to check if anything changed before doing > what their update triggers do? Perhaps 8.4's suppress_redundant_updates_trigger() could be helpful in this case: http://www.postgresql.org/docs/8.4/interactive/functions-trigger.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] table.column in query results?
--- On Thu, 7/23/09, Albe Laurenz wrote: > Andrew Klaassen wrote: > > Is it possible to get table.column in query results > rather > > than just column? > > > > I.e. I'd like: > > > > SELECT * FROM foo, bar; > > foo.id | foo.name | bar.id | bar.text > > ---+--++- > > ... > > > > ...rather than: > > > > SELECT * FROM foo, bar; > > id | name | id | text > > ---+--++- > > ... > > You'd have to use aliases, like > > SELECT id AS "foo.id" ... Ah... so no way to do it with a wildcard in the query? That's unfortunate. Thanks. Andrew __ The new Internet Explorer® 8 - Faster, safer, easier. Optimized for Yahoo! Get it Now for Free! at http://downloads.yahoo.com/ca/internetexplorer/ -- 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] synchronous_commit=off doesn't always return immediately
On Thu, 23 Jul 2009, tomrevam wrote: bgwriter_delay = 20ms # 10-1ms between rounds bgwriter_lru_maxpages = 1000# 0-1000 max buffers written/round bgwriter_lru_multiplier = 5.0 # 0-10.0 multipler on buffers scanned/round These settings may be contributing to the problem. You should never run the background writer that frequently--it just wastes resources and writes more than it should. I'd suggest turning it off altogether (bgwriter_lru_maxpages = 0) and seeing if things improve any, just to rule that out as a potential source of issues. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] ERROR: could not access status of transaction 2495690984
On Tue, 21 Jul 2009 15:32:07 -0400 Merlin Moncure wrote: > 2009/7/21 Bjørn T Johansen : > > I have had som problem with my filesystem and now I get these error > > messsages when trying to access my database..: > > > > ERROR: could not access status of transaction 2495690984 > > Could not open file "pg_clog/094C": No such file or directory. > > got backups? > > One way to suppress this error, but not fix the underlying corruption, > is to generate clog files that the database thinks should be there. > Make a 256k file filled with 0x55 and put it where the database is > expecting. > > merlin > Yes, I have backups... Was just kind of hoping for another solution :) BTJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 3des key lengths and key management
I am working for a small company that is going through a PCI DSS audit. The auditor wants to know how long the key lengths are for the fields that we have encrypted with pgcrypto 3des. I am by no means an expert in cryptology, so I am struggling with what to tell him?I've done a day or so of googling and the best I can tell is the 3des uses 3x56bit keys and encrypts the date 3 times with each of the keys. He did not seem to like that answer. He seems to believe that 3des can use 2048 bit keys and that is the minimal acceptable standard of PCI DSS? What I know is that we simply added the contrib pgcrypto stuff into the database and started using 3des and it seemed to work. So my questions are: 1) What are the default 3des key lengths when you load postgresql enterprise db on a redhat ES x86_64 box? 2) If possible how can you change the keys? and replace them with keys with lengths to 2048 bit or above? 3) If 2 is not possible then what other encryption type can we use that will meet his 2048 bit key length requirement? 4) Is is possible to compile C or Java code that will allow me to be the only one whom knows the pass-key but allow other users to encrypt/ decrypt data? Thanks in advance, --bb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Disable databse listing for non-superuser (\l) ?
All: Any suggestions on how-to, or comments on a potential NFR, to disable non-superuser's from viewing the database list via \l? Possibly a postgresql.conf toggle or restrictions on the internal views that constitute say, 'pg_catalog.pg_database'. Something equivalent, in principal, to FreeBSD sysctl: % security.bsd.see_other_uids=0 Just a thought... ~BAS -- 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] ERROR: could not access status of transaction 2495690984
On Tue, 21 Jul 2009, Merlin Moncure wrote:
One way to suppress this error, but not fix the underlying corruption,
is to generate clog files that the database thinks should be there.
Make a 256k file filled with 0x55 and put it where the database is
expecting.
Here's a list of past messages on this topic I found interesting (I just
ran into this recently):
http://archives.postgresql.org/pgsql-general/2006-12/msg01546.php
http://archives.postgresql.org/pgsql-hackers/2005-01/msg00678.php
http://archives.postgresql.org/pgsql-general/2009-03/msg01106.php
http://archives.postgresql.org/pgsql-general/2004-10/msg00542.php
http://groups.google.com/group/comp.databases.postgresql.hackers/browse_thread/thread/4988752a6939f45a/ab22db2773d387d9?q=%22could+not+access+status+of+transaction%22+group%3Acomp.databases.postgresql.*&pli=1
http://www.nabble.com/Could-not-open-file-%22pg_clog-%22-td23499731.html
http://archives.postgresql.org/pgsql-admin/2008-09/msg00276.php
And here's how I generated dummy clog files using bash that worked to get
the database back up again. After replacing all of the ones the database
wanted, and running a database-wide VACUUM to make sure everybody was
happy, I was able to dump all the recent data I don't have anywhere else
out of the system.
---
Generate a 256K file of all "55" characters, which indicates all
transactions it might be looking for are treated as already commited:
for i in {1..262144}; do printf '\125'; done > committed
ls -l committed
od -xv committed | head
od -xv committed | tail
$ ls -l committed
-rw-r--r-- 1 gsmith gsmith 262144 2009-06-25 11:01 committed
$ od -xv committed | head
000
020
040
060
100
120
140
160
200
220
$ od -xv committed | tail
0777560
0777600
0777620
0777640
0777660
000
020
040
060
100
chown postgres.postgres committed
chmod 600 committed
mv -i committed $PGDATA/pg_clog/0646
--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
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] commercial adaptation of postgres
On Mon, 20 Jul 2009, Christophe wrote: On Jul 20, 2009, at 6:56 PM, Dennis Gearon wrote: I once talked to a company that made a custome version of Postgres. It split tables up on columns and also by rows, had some other custome features. It was enormously faster from what I gathered. I could of sworn it began with the letter 'T', but maybe not. I don't see anything like that on the commercial page of the posgres site. Truviso? http://truviso.com/ We don't do any column-oriented stuff at Truviso. From the description Dennis gave, I'm guess he's thinking of the Petabyte database at Yahoo: http://it.toolbox.com/blogs/database-soup/2-petabyte-postgresql-24848 -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] 3des key lengths and key management
On Thu, Jul 23, 2009 at 6:11 PM, bulk wrote: > 1) What are the default 3des key lengths when you load postgresql > enterprise db on a redhat ES x86_64 box? Traditionally 3des can use either 112-bit or 56-bit keys. I think the openssl interface actually lets you set the third key separately now but I'm not sure that adds any security. > 2) If possible how can you change the keys? and replace them with keys > with lengths to 2048 bit or above? > 3) If 2 is not possible then what other encryption type can we use that > will meet his 2048 bit key length requirement? He's completely confused. Symmetric key encryption like DES/3DES/AES use key sizes much smaller than this. 3DES is still considered reasonably secure with 112 bit keys, though the real state of the art today is AES which uses key sizes of 128, 192, or 256. He or she is thinking of asymmetric encryption like RSA. For those algorithms key sizes are often in the range 1024, 2048, or even higher -- 2048 isn't actually considered very high any more. But those keys are generally used differently. They're used to sign or protect hashes or keys which are then generated or used with symmetric encryption algorithms. They're far too slow to use for the actual encryption itself. > 4) Is is possible to compile C or Java code that will allow me to be the > only one whom knows the pass-key but allow other users to encrypt/decrypt > data? Well you need the pass-key to do the encryption or decryption. With asymmetric algorithms you can separate those two functions so the group which can encrypt is separate from the group which can decrypt. You can create a function to do the encryption and another function to do decryption, make it SECURITY DEFINER, and grant access to only certain users. But then anyone who has a dump of your database will have the key which kind of defeats the purpose of using encryption. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] uuid contrib don't compile in OpenSolaris
Hi all, I have some issues to compile uuid contrib of 8.4 version. Touching something i see that the gmake don't find uuid.h. (pfexec gmake -d) Touching more, i add uuid.h into the uuid directory and i had a error message: missing separator. So i google a little and i find something: http://www.cygwin.com/faq/faq.programming.html#faq.programming.make-spaces I read the source and it explain that some issues were fixed into Debian, but other platforms didn't tested yet. If this is really, we are using 8.4 final, and it don't sounds consistent at all. BTW, somebody patched this ? If not, i will continue my workaround on this and try to propose something to fix it. -- Emanuel Calvo Franco www.emanuelcalvofranco.com.ar ArPug.com.ar / aosug.com.ar -- 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] 3des key lengths and key management
On Jul 23, 2009, at 10:11 AM, bulk wrote: I am working for a small company that is going through a PCI DSS audit. securitymetrics.com? (They seem to be the low bidder, with everything that implies. They asked me to open up my firewall to them, pointing at a fake server, just so they'd have something to audit, after failing our audit "because we only allowed access to the application from inside our firewall.".) The auditor wants to know how long the key lengths are for the fields that we have encrypted with pgcrypto 3des. I am by no means an expert in cryptology, so I am struggling with what to tell him?I've done a day or so of googling and the best I can tell is the 3des uses 3x56bit keys and encrypts the date 3 times with each of the keys. If he really said that 3des can use 2048 bit keys, tell him he's an idiot. He did not seem to like that answer. He seems to believe that 3des can use 2048 bit keys and that is the minimal acceptable standard of PCI DSS? What I know is that we simply added the contrib pgcrypto stuff into the database and started using 3des and it seemed to work. So my questions are: 1) What are the default 3des key lengths when you load postgresql enterprise db on a redhat ES x86_64 box? Dunno. 3des is usually a 112 bit key, though, IIRC. 168 at most. It's rather an old cipher by this point, but still secure enough for most things. 2) If possible how can you change the keys? and replace them with keys with lengths to 2048 bit or above? 3) If 2 is not possible then what other encryption type can we use that will meet his 2048 bit key length requirement? Long key lengths, thousands of bits, are something you tend to talk about when you're looking at an asymmetric cipher. RSA, DSA stuff like that. Symmetric ciphers (aka secret-key), like 3des, tend to use much shorter key lengths. A symmetric cipher uses the same key to encrypt and decrypt a message. An asymmetric cipher (aka public-key) uses one key to encrypt the message and needs a different key to decrypt it. 4) Is is possible to compile C or Java code that will allow me to be the only one whom knows the pass-key but allow other users to encrypt/decrypt data? Yes, that's asymmetric cryptography, using something like DSA. For a web application capturing credit cards, say, doing that you might use something like pgp to handle all the encryption and decryption. You'd use PGP in your webapp using a public key to encode the credit card numbers you were given before storing them in the database. Any time you need to access that you'd pull it out of the database, and use PGP with the associated private key to recover the credit card number, probably on a separate secure system. That's reengineering your entire system architecture and business process, though, rather than just dropping in a new encryption algorithm. As I understand the PCI DSS requirements (which is only a little) you don't need to use asymmetric crypto to comply with them, and that as long as you have decent key management and access control, 3des would be fine. https://www.pcisecuritystandards.org/ has a bunch of the docs if you're interested. 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] 3des key lengths and key management
On Jul 23, 2009, at 12:11 PM, Steve Atkins wrote: 4) Is is possible to compile C or Java code that will allow me to be the only one whom knows the pass-key but allow other users to encrypt/decrypt data? Yes, that's asymmetric cryptography, using something like DSA. Oops. Missed the "decrypt" bit there. It's sort of possible, but only by relying on other access controls to allow other users to run your code, but not access it in other ways. Within the database a security definer function would let you do that. But anyone with superuser access to the machine, or to the database, or who had access to backups or database dumps would be able to get the key. You can build systems of increasing complexity to get marginally more protection, but it's really not a path you want to be going down just for PCI compliance. 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] 3des key lengths and key management
On Jul 23, 2009, at 12:11 PM, Steve Atkins wrote: They asked me to open up my firewall to them, pointing at a fake server, just so they'd have something to audit, after failing our audit "because we only allowed access to the application from inside our firewall." I'm glad it wasn't just me. We failed our audit because our firewall correctly detected their probes as an intrusion, and shut them down. It would be as if your home failed a security audit because no one could get through the external fence, so "we couldn't verify that the door was locked." -- 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] A question on PSQL 8.3 setup
Yes, it's working now. I appreciate your help. The problem was to not stoppsql process even before doing anything. Thanks a lot, Jong On Thu, Jul 23, 2009 at 7:38 AM, Sam Mason wrote: > On Thu, Jul 23, 2009 at 01:08:26AM -0600, Jong Chun Park wrote: > > I need to configure PSQL to store all DB-related data into > > /home/pgsql/data instead of somewhere in / such as /var/lib/ > > postgresql/8.3/... or /usr/local/pgsql/data. > > Assuming you're using the standard builds in Ubuntu, I'd probably do > something like: > > 1) make your directory if you haven't got one already: > mkdir /home/postgres > chown postgres:postgres /home/postgres > > 2) shut down postgres > /etc/init.d/postgres-8.3 stop > > 3) copy the existing database cluster over to its new location > cp -a /var/lib/postgresql/8.3 /home/postgres > > 4) move the old cluster out of the way for safe keeping and put a > symlink back in place > mv /var/lib/postgresql/8.3 /var/lib/postgresql/8.3-old > ln -s /home/postgres/8.3 /var/lib/postgresql/8.3 > > 5) start PG again > /etc/init.d/postgres-8.3 start > > if that all works then you've got the option of dumping the old data > that was stashed away above and update the config file to point straight > to the new location. > > -- > Sam http://samason.me.uk/ > > -- > 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] commercial adaptation of postgres
Also try Netezza, one data warehouse appliance originally based on Postgres. Although this is not the only such Postgres derivative. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> Greg Smith 07/24/09 9:10 AM >>> On Mon, 20 Jul 2009, Christophe wrote: > On Jul 20, 2009, at 6:56 PM, Dennis Gearon wrote: >> >> I once talked to a company that made a custome version of Postgres. It >> split tables up on columns and also by rows, had some other custome >> features. It was enormously faster from what I gathered. >> >> I could of sworn it began with the letter 'T', but maybe not. I don't see >> anything like that on the commercial page of the posgres site. > > Truviso? http://truviso.com/ We don't do any column-oriented stuff at Truviso. >From the description Dennis gave, I'm guess he's thinking of the Petabyte database at Yahoo: http://it.toolbox.com/blogs/database-soup/2-petabyte-postgresql-24848 -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] user/grant - best practices handling permission in production system
Hi All, I have some questions for you about the best way to handle permission on a database in a production system. The final goal is to have a web application connected to the db using a single user that must run select/delete/insert/update (and maybe truncate) In my opinion that user should NOT own the db and the db itself should NOT be created using that user. Of course that user should NOT be able to create database or other users. The steps could be: 1. using postgres user (or another user with grant for creating database) create the database 2. using the user used in step 1, create the schema and populate tables with initial data 3. using the user used in the previous step, create a new user (the one the webapp will use) 4. give to the new user the grant on all database objects for select/delete/insert/update At this point the webapp should work correctly. The main missing point for me is how to perform step 4 in a simple way since it seems there is not a way to give the right grants to all db objects in one shot. What do you think about that ? What are the common practices for a production system ? thanks a lot ste -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: user/grant - best practices handling permission in production system
On Thu, Jul 23, 2009 at 10:09 PM, Stefano Nichele wrote: > 2. using the user used in step 1, create the schema and populate tables with > At this point the webapp should work correctly. > The main missing point for me is how to perform step 4 in a simple way since > it seems there is not a way to give the right grants to all db objects in > one shot. Well there isn't a way to do step 2 in one shot either. You'll have to issue a CREATE statement for each object, it's no extra work to issue a GRANT for each object with the specific rights the application should have at that time. Think of it as an important part of the process of creating a new object. Note that it's probably not necessary to grant all rights to every table. Most applications have some tables that are read-only or insert-only from the point of view of the application. Your system will be more secure if the application does not have unnecessary privileges. So thinking about what rights to grant to the application for each object when it's created is not a bad thing. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] citext contrib module (building indexes)
I've got several columns in my database that need to have case insensitive searches done so I've loaded the citext control module and have changed the data types to citext. When I create a index on a column that is defined as a citext, how is that going to be treated. Will Postgres use the index to search or will it always do a table scan? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
[GENERAL] A question about pg_standby.
I am using pg_standby to set up a warm standby. Everything seems to be working OK so far but I do have one question. I wanted to check to make sure that the replication was happening so I created the trigger file which put the database to "live"mode. I ran a query on the standby to make sure the new data was there. I re-created the recovery.conf file and restarted the postgres server. The server went back into the standby mode but now it's confused and wants to keep all archive files. The log file says Keep archive history: and later So it looks like it's applying the log files but not deleting the old ones. What's the best way to "pause" the log shipping and restart it later? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] synchronous_commit=off doesn't always return immediately
Tom Lane-2 wrote: > > > What sort of disk hardware have you got? It sounds overstressed. > > It might help to increase wal_buffers. > > The disk is a SATA drive and it will be replaced in future projects with a better drive. However, I still don't understand why there is any halt in the client when synchronous_commit is off. Thanks, Tomer -- View this message in context: http://www.nabble.com/synchronous_commit%3Doff-doesn%27t-always-return-immediately-tp24621119p24630130.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
[GENERAL] Converting SQL to pg
I must be tired. Any suggestions on converting the following to postgresql? UPDATE IGNORE yose5_user_newflags AS flags, yose5_messages AS msg SET flags.forum_id = msg.forum_id WHERE flags.message_id = msg.message_id AND flags.message_id IN (15580, 15581, 15706, 15712, 15713, 15719, 15888) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] service stopping and pg_standby on windows
Hello. I use postgres on windows and try setup warm standby. For restore_command i use pg_standby program. When i try to stop postgres service, that runs in recovery mod, service not stopping correctly, because pg_standby still works. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Search Path vs Synonyms
Matthew Seaborn wrote: > Given the situation where a user connecting to the database > needs access to two separate schemas: the primary schema > which contains the data they will be updating and a second > schema which contains read-only reference data, used by many > users, that will be using in joins on queries. > > I don't want to have to use fully qualified names (I am > migrating code from Oracle which uses synonyms), so what is > the best way (in both performance and reliability) to refer > to the tables; Search Path or Synonyms? There are no synonyms in PostgreSQL: synonyms are Oracle's way of search_path (though more selective). You excluded the best solution, namely to qualify the objects. If your user should be able to access *all* tables in both schemas unqualified and there are no name collisions between objects in the schemas, I would recommend search_path. Otherwise, use views. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
