[GENERAL] Converting a timestamp to a time
I'm in the middle of moving a production database from 7.1 to 8.1 and have hit a slight problem. On the old system I've got a query including datastamp AS datastamp, date(datastamp ) as datadate, time(datastamp ) as datatime, status, -- etc. This is actually generated on the client to possibly include timezone correction. Obviously this has worked fine for a number of years on 7.1, but 8.1 is objecting to the time() cast. What is the correct (or even any :-) way of converting a timestamp into a time (without timezone etc.)? Any suggestions would be much appreciated. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] explosion of tiny tables representing multiple
On Fri, Nov 03, 2006 at 08:25:25PM +, Benjamin Weaver wrote: Dear Martijn, Wow, didn't know about arrays. Did lots of sql, but, as I think about it, that was 7 years ago, and we didn't know about arrays then Are their performance problems with arrays? We will not likely be working with more than 50,000 - 100,000 records. If by records you mean rows in the database, then 50,000 rows is a baby database, nothing to worry about there. Performence of arrays scale about linear with the number of elements in the array. So if most of your arrays have only 2 or 3 elements, the performence should be good. If you make a single array with 50,000 element, it's going to suck very badly. Note, recent versions of postgres have better support for arrays, including for indexing thereof. Especially the new GIN index type may be useful for you. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] ERROR: tuple concurrently updated
Tom Lane wrote: Russell Smith [EMAIL PROTECTED] writes: I got this error the other day, I was under the impression that vacuum could get a concurrently updated tuple. I could be wrong. It is possible for somebody to quickly explain this situation? Message follows; vacuumdb: vacuuming of table school.person in database sqlfilter failed: ERROR: tuple concurrently updated Was this a VACUUM ANALYZE, and if so might there have been another ANALYZE running concurrently on that table? If so, this is just a reflection of concurrent attempts to update the same pg_statistic row. It's harmless since the ANALYZE that didn't fail presumably stored pretty nearly the same results. There is some interlocking to prevent the error in CVS HEAD, though. Thanks Tom, that makes a lot on sense. There is an analyze in that. Plus 8.1 autovac probably got its hand in at the same time. I didn't expect it to give the error about the vacuumed table if pg_statistic was the table having the concurrent update. I will remember that for the future. Thanks again for the fast and concise response. Russell Smith regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dividing integers not producing decimal fractions
On Fri, Nov 03, 2006 at 02:03:59PM -0800, [EMAIL PROTECTED] wrote: You're right (I dug around in the documentation and edjoocated myself). However: snip sales=# select 1/2::float; ?column? -- 0.5 (1 row) Note that in this case the float cast only applies to the last number. That's why you get this: sales=# select (1/2)*4::float; ?column? -- 0 (1 row) The integer divide happens first. It is best to apply the cast to the first element of the expression, as expressions are parsed left-to-right, so: select (1::float/2)::4; Works better. However, mostly it's better to explicitly make all your constants non-integer if that's what you mean. This statement: select (1.0/2.0)*4.0; Gives the same result, but doesn't need any casts. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Simple stored procedure examples?
On Sat, Nov 04, 2006 at 08:35:28AM -0800, novnov wrote: So pgSQL is case sensitive and that include keywords like UPDATE and SET. No it's not. Only identifiers in double quotes () are case-sensetive. So, in your example below, because the function was created with double quotes, you now have to use double quotes and the same case every time you want to use it. If you create a function/table/column without double quotes, you never need quotes and it is case-insensetive. In your case it's possible that pgAdmin is adding the quotes for you, maybe? There what worked, for the record: -- Function: proc_UpdateItemName() -- DROP FUNCTION proc_UpdateItemName(); CREATE OR REPLACE FUNCTION proc_UpdateItemName() RETURNS void AS $BODY$UPDATE Item SET ItemName = 'fox';$BODY$ LANGUAGE 'sql' VOLATILE; ALTER FUNCTION proc_UpdateItemName() OWNER TO postgres; Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Converting a timestamp to a time
Mark Morgan Lloyd [EMAIL PROTECTED] schrieb: What is the correct (or even any :-) way of converting a timestamp into a time (without timezone etc.)? You can CAST it: test=# select now(); now --- 2006-11-05 11:16:05.205235+01 (1 row) test=# select now()::time; now 11:16:18.22527 (1 row) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] help with a query
Pedro Doria Meunier [EMAIL PROTECTED] schrieb: Hi all! This is most certainly a lame question but perhaps someone is gracious enough to lend me a hand ;-) I have the following setup in a table: The first record which is to be found (ok easy enough :D) with a timestamp meets a certain condition (ignition off) The following record is for the event of ignition on again with a timestamp. So the question here is: how can I compute the time difference between these two records in a single query? Better yet: finding all the records that meet the first condition (ignition off) and the immediately following records as to compute the time difference. ;-) Okay, let me try. First, i created a similar table: test=# select * from test; id | nr | ts | event +++--- 1 | 1 | 2006-11-05 11:20:34.308945 | 0 2 | 2 | 2006-11-05 11:20:41.245691 | 0 3 | 2 | 2006-11-05 11:20:43.630381 | 1 4 | 1 | 2006-11-05 11:20:49.762882 | 1 5 | 3 | 2006-11-05 11:20:55.427288 | 0 (5 rows) As we can see, i have a column nr to identify paired rows. The event-column is similar to your ignition (off-on - 0-1). The rows with id 1 and 4, and 2 and 3 paired. There are only 0-events and paired 1-events or only a 0-event, and only one pair for every nr. Now i want to know the elapsed time for every nr (1 and 2) between the 0 and 1 - event: test=# select a.id, a.nr, a.ts as event_off, a.event, b.id, b.ts as event_on, b.ts-a.ts as elapsed from test a, test b where (a.nr=b.nr and a.tsb.ts); id | nr | event_off | event | id | event_on | elapsed +++---+++- 1 | 1 | 2006-11-05 11:20:34.308945 | 0 | 4 | 2006-11-05 11:20:49.762882 | 00:00:15.453937 2 | 2 | 2006-11-05 11:20:41.245691 | 0 | 3 | 2006-11-05 11:20:43.630381 | 00:00:02.38469 (2 rows) Hope that helps, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] max_fsm_pages
I just did a vacuum analyze and I got a message I've never seen before: conwatchlive=# vacuum analyze; NOTICE: number of page slots needed (27056) exceeds max_fsm_pages (2) HINT: Consider increasing the configuration parameter max_fsm_pages to a value over 27056. VACUUM conwatchlive=# What does this mean? I assume it has nothing to do with the Flying Spaghetti Monster. More generally, I am a novice at administering a PostgreSQL database, is there a list of tips somewhere that I can read to improve performance? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] max_fsm_pages
Naz Gassiep [EMAIL PROTECTED] schrieb: NOTICE: number of page slots needed (27056) exceeds max_fsm_pages (2) HINT: Consider increasing the configuration parameter max_fsm_pages to a value over 27056. VACUUM conwatchlive=# What does this mean? I assume it has nothing to do with the Flying Read again ;-) You should increase 'max_fsm_pages' ;-) More generally, I am a novice at administering a PostgreSQL database, is there a list of tips somewhere that I can read to improve performance? Yes, for instance, read this: http://www.powerpostgresql.com/Downloads/annotated_conf_80.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Converting a timestamp to a time
Andreas Kretschmer schrieb: Mark Morgan Lloyd [EMAIL PROTECTED] wrote: What is the correct (or even any :-) way of converting a timestamp into a time (without timezone etc.)? You can CAST it: test=# select now(); now --- 2006-11-05 11:16:05.205235+01 (1 row) test=# select now()::time; now 11:16:18.22527 (1 row) Thanks Andreas, I've ended up using CAST( ... AS TIME). I think the :: notation might be fragile in this instance because of the machine-generated SQL which gets a bit hairy in places. I've also had to replace INTERVAL() with CAST( ... INTERVAL) wherever it occurs and replace the result of a function with TIMESTAMP WITH TIME ZONE... hopefully that won't mess anything up, the server is aggresively GMT since we have to deal with several timezones simultaneously and it's the only way I could work it. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Converting a timestamp to a time
Mark Morgan Lloyd [EMAIL PROTECTED] schrieb: test=# select now()::time; now 11:16:18.22527 (1 row) Thanks Andreas, I've ended up using CAST( ... AS TIME). I think the :: notation might be fragile in this instance because of the machine-generated SQL which Thats okay, because my version (the ::cast) is a PostgreSQL-feature, but the cast(... as ...) is more SQL-conform. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] varchar
Hi,I would like to allow web site user to fill a field and for that i would need a large varchar()...maybe something around 100.000 characters.i guess that VARCHAR can not hold so many character and that i should turn to bytea. Am I right or is there some other possibility ?i'm asking that because i've seen that bytea has some issues to store non ASCII characters like from UNICODE andSlovak language...so how can i store central europe characters (UNICODE) into BYTEA field ? thx,Alain
Re: [GENERAL] Converting a timestamp to a time
Andreas Kretschmer schrieb: Mark Morgan Lloyd [EMAIL PROTECTED] wrote: test=# select now()::time; now 11:16:18.22527 (1 row) Thanks Andreas, I've ended up using CAST( ... AS TIME). I think the :: notation might be fragile in this instance because of the machine-generated SQL which Thats okay, because my version (the ::cast) is a PostgreSQL-feature, but the cast(... as ...) is more SQL-conform. Thanks for that, feedback on best practice is always useful. I must admit that the only server I've used before pg was the SOLID Server (using Bonzai Tree technology), but they changed their licensing terms which made it impractical. I looked briefly at MySQL which in those days didn't support transactions, apart from that we decided that we didn't fancy waving the incredibly-tacky name in front of our customers :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] varchar
On Nov 5, 2006, at 15:32 , Alain Roger wrote: I would like to allow web site user to fill a field and for that i would need a large varchar()...maybe something around 100.000 characters. i guess that VARCHAR can not hold so many character and that i should turn to bytea. Am I right or is there some other possibility ? Not at all -- PostgreSQL can fit roughly 1 gigabyte of data in a single varchar column. Avoid bytea for anything except purely binary data. Keep in mind that PostgreSQL is not able to index values longer than ~ 8 kilobytes. Note that the text data type, which is unlimited in length by definition, is usually preferred over varchar. Read more here: http://www.postgresql.org/docs/8.1/interactive/datatype- character.html Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Simple stored procedure examples?
Hmm well that's interesting. I had posted to the pgAdmin list too re this issue, thinking that the quotes issues was something with that interface to pgsql. There I was told PostgreSQL does require you to use double quotes in some circumstances (for example, if you use upper case letters). pgAdmin automatically quotes identifiers if you use a name that requires them. Isn't that somewhat of a contradiction of what you wrote? Note, the only reason the function uses double quotes is because the table def does, or at least that's how I interpret. I would really prefer it if simple names like Item and ItemName not be double quoted. You're saying that postgres itself would only require double quotes if the table was originally decribed that way (and it is, being created by pgAdmin). Seems like an odd mismatch between pgsql and pgAdmin, why would pgAdmin take the seemingly uneccessary step of double quoting names like Item? Any suggestions for a db admin tool that does not introduce this error? Martijn van Oosterhout wrote: On Sat, Nov 04, 2006 at 08:35:28AM -0800, novnov wrote: So pgSQL is case sensitive and that include keywords like UPDATE and SET. No it's not. Only identifiers in double quotes () are case-sensetive. So, in your example below, because the function was created with double quotes, you now have to use double quotes and the same case every time you want to use it. If you create a function/table/column without double quotes, you never need quotes and it is case-insensetive. In your case it's possible that pgAdmin is adding the quotes for you, maybe? There what worked, for the record: -- Function: proc_UpdateItemName() -- DROP FUNCTION proc_UpdateItemName(); CREATE OR REPLACE FUNCTION proc_UpdateItemName() RETURNS void AS $BODY$UPDATE Item SET ItemName = 'fox';$BODY$ LANGUAGE 'sql' VOLATILE; ALTER FUNCTION proc_UpdateItemName() OWNER TO postgres; Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -- View this message in context: http://www.nabble.com/Simple-stored-procedure-examples--tf2572616.html#a7186929 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Simple stored procedure examples?
On Sun, Nov 05, 2006 at 08:51:52AM -0800, novnov wrote: I would really prefer it if simple names like Item and ItemName not be double quoted. You're saying that postgres itself would only require double quotes if the table was originally decribed that way (and it is, being created by pgAdmin). Seems like an odd mismatch between pgsql and pgAdmin, why would pgAdmin take the seemingly uneccessary step of double quoting names like Item? Evidently, pgAdmin sees you using uppercase letters and decides to quote them. It could also not quote them, then it would be case insensetive. If you create the table with lowercase, does pgadmin allow you to refer to them with mixed case? I don't use pgAdmin, so I can't really say much about this. Any suggestions for a db admin tool that does not introduce this error? I don't use pgAdmin, the only tool I use is psql, and it doesn't automatically quote anything, ever. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] basic SQL request
Hi,i've tried to run a basic SQL request as followed :select *from articles, articletypes, departmentwhere articles.articletype_id = articletype.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now()and i got the following error message :ERROR: missing FROM-clause entry for table articletypei'm confused now, if i use LEFTJOIN it's the same, so where is the trouble ? thx,AL.
Re: [GENERAL] basic SQL request
You wrote articletype instead of articletypes in the first WHERE clause: is this the problem? Regards Marco -- Marco Bizzarri http://iliveinpisa.blogspot.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] basic SQL request
On Sunday 05 November 2006 11:42 am, Alain Roger wrote: Hi, i've tried to run a basic SQL request as followed : select * from articles, articletypes, department ^ s where articles.articletype_id = articletype.articletype_id AND ^ no s articles.department_id = department.department_id AND articles.validity_period_end now() and i got the following error message : ERROR: missing FROM-clause entry for table articletype i'm confused now, if i use LEFTJOIN it's the same, so where is the trouble ? thx, AL. The table name in the FROM clause has an s on the end. The name in the WHERE clause does not. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] basic SQL request
Check this out:= articletype.articletype_id ANDand in your from clause you have something like this:from articles, articletypes, departmentSo you need to fix articletypes or if it is articletype Regards,---Shoaib MirEnterpriseDB (www.enterprisedb.com)On 11/6/06, Alain Roger [EMAIL PROTECTED] wrote:Hi,i've tried to run a basic SQL request as followed : select *from articles, articletypes, departmentwhere articles.articletype_id = articletype.articletype_id AND articles.department_id = department.department_id AND articles.validity_period_end now()and i got the following error message :ERROR: missing FROM-clause entry for table articletypei'm confused now, if i use LEFTJOIN it's the same, so where is the trouble ? thx,AL.
Re: [GENERAL] Simple stored procedure examples?
novnov wrote: I would really prefer it if simple names like Item and ItemName not be double quoted. You're saying that postgres itself would only require double quotes if the table was originally decribed that way (and it is, being created by pgAdmin). Seems like an odd mismatch between pgsql and pgAdmin, why would pgAdmin take the seemingly uneccessary step of double quoting names like Item? From the manual - http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html 4.1.1. Identifiers and Key Words Tokens such as SELECT, UPDATE, or VALUES in the example above are examples of key words, that is, words that have a fixed meaning in the SQL language. The tokens MY_TABLE and A are examples of identifiers. They identify names of tables, columns, or other database objects, depending on the command they are used in. Therefore they are sometimes simply called names. snip Identifier and key word names are case insensitive. Therefore UPDATE MY_TABLE SET A = 5; can equivalently be written as uPDaTE my_TabLE SeT a = 5; A convention often used is to write key words in upper case and names in lower case, e.g., UPDATE my_table SET a = 5; There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes (). snip Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and foo are considered the same by PostgreSQL, but Foo and FOO are different from these three and each other. Being a GUI tool that generates the underlying SQL statements you will find that pgAdmin is generating SQL to conform to the above. But when you enter an SQL statement yourself you will need to use quotes to maintain any uppercase characters in the names. This may catch you in pgAdmin as when creating a function you are actually entering an SQL statement that is used as part of the Function definition. You can test this by opening the new table dialog and entering the name as test then look at the SQL tab and you will have CREATE TABLE test ( ) WITHOUT OIDS; The change the name to Test and in the SQL tab you will have CREATE TABLE Test ( ) WITHOUT OIDS; -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Geometry
Bob Pawley wrote: Can anyone point me to an overview of PostgreSQL geometry ? Bob Pawley Try www.postgis.org A third party add-on to Postgres implementing OGC SFS compliant functionality. This is more complete useful that the built in Postgres spatial data support. Brent Wood ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] opening a channel between two postgreSQL-servers?
Michael Fuhr wrote: On Sat, Nov 04, 2006 at 10:12:00PM +0100, stig erikson wrote: a handy thing in mysql is FEDERATED tables that allows one to open a channel from one MySQL-server to another MySQL-server. it helps a lot when writing stored procedures that transfer data to other servers. you can do the transfer without any extarnal temporary files or external applications that read from one server and insert into another server. Does PG have anything similar? Not in the stock installation but you can establish a connection between one PostgreSQL server and another with contrib/dblink, or with just about any other data source using David Fetter's dbi-link. http://pgfoundry.org/projects/dbi-link/ PostgreSQL has several server-side languages such as PL/Perl, PL/Python, PL/Tcl, PL/Ruby, PL/php, PL/Java, PL/R, etc. In general a server-side function written in one of those languages can do anything a standalone application could do, such as connecting to another database, even a different DBMS (you could connect from PostgreSQL to MySQL, Oracle, SQL Server, etc.). thank you Michel and Ben. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] schema diagram with child tables
Hi all,I am drawing the schema diagram for my postgres database. I wanted to add child tables to the master table in my diagram but I am not sure how to do it. I couldnt find any sample diagram for this in the web. If somebody can help me out with this that will be great.Thanks a lot for your helpRoopa We have the perfect Group for you. Check out the handy changes to Yahoo! Groups.
Re: [GENERAL] help with a query
If you do not want to amend your table with extra information, this is how you do it: Suppose you have a table create table events ( time timestamp, object int refers objects(id), -- The thing that had its ignition turned on or off at this time ignition boolean, comment varchar ); You can then do select distinct on (e1.time, e1.object, e1.comment) e1.time as start, e2.time as end, e2.time - e1.time as duration, e1.object, e1.comment from events as e1, events as e2 where e1.object = e2.object and e1.time e2.time order by e1.time, e1.object, e1.comment, e2.time asc; The trick here is to sort by e2.time in ascending order, and to not include e2.time (or any of e2:s fields) in the distinct condition. This causes all rows where the values from e1 are the same to be considered duplicates, and only the first one of them included in the result - which is the one with the lowest e2.time value, since we did sort on that key in ascending order. Note that this trick works since order by is processed before the distinct clause. This is specifically noted in the PostgreSQL manual. Note that there is a slightly more intuitive way of doing this using a subquery with a min() aggregate function, but this is considerably less efficient, especially with larger tables (use explain and you'll understund why). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Need replacement booth member for USENIX Lisa
Hello, Due to some customer requirements, I may not be able to make USENIX LISA. This is a 2 day exhibition on December 6/7. Josh Berkus, Robert Bernier, Robert Treat and in theory someone from eDB will be there. Can someone take my place? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Dump all databases to corresponding files
Anybody know of a script that dumps all databases into corresponding dump files, e.g. $ ./dump template0 - template0.sql template1 - template1.sql db1 - db1.sql db2 - db2.sql ... Also, would this approach add up to equal the output of pg_dumpall, or does pg_dumpall dump additional things (if so, please describe how they'd also be dumped)? Thanks, csn Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates (http://voice.yahoo.com) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Dump all databases to corresponding files
On 11/6/06, CSN [EMAIL PROTECTED] wrote: Anybody know of a script that dumps all databases into corresponding dump files, e.g. $ ./dump template0 - template0.sql template1 - template1.sql db1 - db1.sql db2 - db2.sql ... Also, would this approach add up to equal the output of pg_dumpall, or does pg_dumpall dump additional things (if so, please describe how they'd also be dumped)? there are many ways to do this, but this would be a fun exercise for pl/sh. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dump all databases to corresponding files
CSN [EMAIL PROTECTED] writes: Also, would this approach add up to equal the output of pg_dumpall, or does pg_dumpall dump additional things (if so, please describe how they'd also be dumped)? You'd be missing roles (user/group definitions) and tablespace definitions. pg_dump doesn't emit either. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Header meaning for pg_dump
Dear All, I had open the backup file of PostgreSQL created by pg_dump command. I found that pg_dump make a comment line as header for each module it backup. I try to understand the meaning of value contained on header for my sample header TOC entry 1427 (class 1259 OID 1216127)-- Dependencies: 7-- Name: t_option; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- Could you please advise me for What does they mean? and What the value used for? Thank You. NETsolutions Asia Limited +66 (2) 237 7247
[GENERAL] upgrade to 8.0.9
Hi, I am currently using 8.0.0 and I want to upgrade to 8.0.9 Please tell if i can just install the rpms for 8.0.9 Will I not have to rebuild my application with new libpq.so? or does the libpq.so still remain the same. Thanks, regards Surabhi
Re: [GENERAL] upgrade to 8.0.9
surabhi.ahuja wrote: Hi, I am currently using 8.0.0 and I want to upgrade to 8.0.9 Please tell if i can just install the rpms for 8.0.9 Upgrading from 8.0.x to 8.0.9 will use your current datafiles without problems. Upgrading to 8.1.5 will need a dump/restore. Will I not have to rebuild my application with new libpq.so? or does the libpq.so still remain the same. The 8.0.0 client should connect to 8.0.9 server without problems but .so libs are dynamicly linked so your app will use the one currently installed on the machine running your app and you shouldn't need to re-link with the newer version. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] upgrade to 8.0.9
Hi, On Mon, 2006-11-06 at 10:58 +0530, surabhi.ahuja wrote: I am currently using 8.0.0 and I want to upgrade to 8.0.9 Please tell if i can just install the rpms for 8.0.9 http://www.postgresql.org/ftp/binary/v8.0.9/linux/rpms/ Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[GENERAL] .
. Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions ! Profitez des connaissances, des opinions et des expériences des internautes sur Yahoo! Questions/Réponses.
