[ADMIN]
Hello, I am interested in the file system level backup: http://www.postgresql.org/idocs/index.php?backup-file.html Is there any documentation/ suggestions on restoring file system backups. Thanks. Joyce Yuen _ ¦b http://explorer.msn.com.hk/intl.asp §K¶O¤U¸ü MSN Explorer ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] database testing
At the moment I try to write a concept for testing databases (postgresql and sybase). the databases are used to learn. Wiches kind of test parameters (performance, stresstests, balancing) would you use. mfg diego ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] unsubscribe me
---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] unsubscribe me
---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Fwd: [ADMIN] unsubscribe me
__ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN]
> I am interested in the file system level backup: > > http://www.postgresql.org/idocs/index.php?backup-file.html > > Is there any documentation/ suggestions on restoring file system > backups. It would be identical to Oracle's cold backup strategy (about which I'm sure you'll find volumes on the web). It is actually rather simple in concept: 1. tar up your entire Postgres directory (assuming your databases live within the Postgres directory) after you've shut down all your databases. 2. Wait until database crashes and is irrecoverable. 3. De-tar your Postgres backup into the directory again. 4. Start up your database. 5. Be delighted about how well it worked out, but because you're paranoid, do as many queries as you can on the data to see if it looks sane. Of course, it is suggested you replace step #2 with: "Every now and then when you're paranoid do steps 3-5 somewhere." I suggest you read any backup and recovery book. I particularly recommend Unix Backup and Recovery here: http://www.backupcentral.com/thebook.html -- that book, although dealing not with database backups, will cover 90% of the concepts you need to understand Postgres "cold" filesystem-level backups. As a DBA, though, I'd finally give you this advice: use the online backup facility. It shouldn't be any more difficult, and it will let you leave the database up and running whilst you backup. -- Tim Ellis DBA, Gamet ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] PG_XLOG How long to retain
Being fairly new to postgresql, I have a couple of questions on pg_xlog We are on 7.2.1 1. How does one properly administer the pg_xlogs? -- How long to retain them? Is it ok to delete them after a period of time? If so, what period of time? 2. I understand that the numbering scheme does not wrap around -- is there a way to set the numbering scheme back to 1? we are still in testing mode, and our xlogs are up to: -rw--- 1 pgsql pgsql 16777216 Jun 12 17:03 00090097 -rw--- 1 pgsql pgsql 16777216 Jun 12 16:57 00090098 -rw--- 1 pgsql pgsql 16777216 Jun 12 16:56 00090099 -rw--- 1 pgsql pgsql 16777216 Jun 12 16:56 0009009A -rw--- 1 pgsql pgsql 16777216 Jun 12 16:58 0009009B -rw--- 1 pgsql pgsql 16777216 Jun 12 16:57 0009009C -rw--- 1 pgsql pgsql 16777216 Jun 12 16:57 0009009D 3. There is normally only 7 of these logs on our system -- so I am assuming that "something" is deleteing them -- Is there an option to "not" delete them, until I want to delete them. If there is more documentation somewhere on these questions, please point me there. Thanks Pam Wampler ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] PG_XLOG How long to retain
Pam Wampler wrote: > Being fairly new to postgresql, I have a couple of questions on pg_xlog > We are on 7.2.1 > > 1. How does one properly administer the pg_xlogs? -- How long to retain > them? > Is it ok to delete them after a period of time? If so, what period of > time? It is auto-cleaned up. > 2. I understand that the numbering scheme does not wrap around -- is there > a way > to set the numbering scheme back to 1? we are still in testing mode, > and our > xlogs are up to: > -rw--- 1 pgsql pgsql 16777216 Jun 12 17:03 00090097 > -rw--- 1 pgsql pgsql 16777216 Jun 12 16:57 00090098 > -rw--- 1 pgsql pgsql 16777216 Jun 12 16:56 00090099 > -rw--- 1 pgsql pgsql 16777216 Jun 12 16:56 0009009A > -rw--- 1 pgsql pgsql 16777216 Jun 12 16:58 0009009B > -rw--- 1 pgsql pgsql 16777216 Jun 12 16:57 0009009C > -rw--- 1 pgsql pgsql 16777216 Jun 12 16:57 0009009D Don't worry about it. An initdb will reset it, as I remember. > 3. There is normally only 7 of these logs on our system -- so I am assuming > that > "something" is deleteing them -- Is there an option to "not" delete > them, until > I want to delete them. Why? > If there is more documentation somewhere on these questions, please point me > there. You have read the official documentation on the WAL system? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Erro sum + date ?
What err ? Order By (Sum(Qtde)/(1+current_date-Max(Data))) Desc Regards, Robson
[ADMIN] Err sum number with Date ?
What err ? Order By (Sum(Qtde)/(1+current_date-Max(Data))) Desc Regards, Robson
[ADMIN] Indexes on separate disk ?
I've looked around a lot and don't think this is possible with Postgres, but figured I would ask in case I missed something. Is it possible to configure things so that an index resides on a separate disk ? It doesn't look like it, since they both have to reside in the same database, and the entire contents of the database would reside underneath a single directory, and therefore on a single disk. Thanks Charlie ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Triggers... Questions... Yes.
I just used Dezign for Databases and created a simple table in what it claimed was "ANSI Level 2" compliance with a one-up "autonum" column. It spewed forth the following code: -- file generated by "dezign for databases" -- create-date:6/13/2002 -- create-time:10:29:56 am -- project-name :not yet specified -- project-author :not yet specified create table transaction(autonum integer); create trigger transaction_autonum_inc before insert on transaction referencing new as n for each row set (n.autonum) = (select (max(autonum),0) + 1 from transaction); Postgres (as you might suspect) didn't like it. Looking at the Postgres "create trigger" syntax, there is nothing about "referencing X as Y" being a possible phrase. Is the problem in: 1. Dezign for Databases, this really isn't ANSI Level 2, 2. Postgres, it doesn't (yet) support this level of functionality, but should, or 3. Me, I'm mixing up ANSI Level 2 and ANSI SQL '92, Postgres doesn't now and has no plans to support ANSI Level 2 SQL ??? Thanks, Tim Ellis DBA, Gamet ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Err sum number with Date ?
Robson- In this, and your last question, the problem is that you are using functions from Microsoft Access. You'll keep running into this until you learn about the equivalent functions in PostgreSQL. The PostgreSQL functions are explained here: http://www.postgresql.org/idocs/index.php?functions.html You may also want to learn how to create your own functions in case there is no equivalent: http://www.postgresql.org/idocs/index.php?sql-createfunction.html -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Robson Martins Sent: Thursday, June 13, 2002 2:30 PM To: [EMAIL PROTECTED] Subject: [ADMIN] Err sum number with Date ? What err ? Order By (Sum(Qtde)/(1+current_date-Max(Data))) Desc Regards, Robson ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Triggers... Questions... Yes.
On Thu, 13 Jun 2002, Tim Ellis wrote: > I just used Dezign for Databases and created a simple table in what it > claimed was "ANSI Level 2" compliance with a one-up "autonum" column. > > It spewed forth the following code: > > -- file generated by "dezign for databases" > -- create-date:6/13/2002 > -- create-time:10:29:56 am > -- project-name :not yet specified > -- project-author :not yet specified > > create table transaction(autonum integer); > > create trigger transaction_autonum_inc > before insert on transaction > referencing new as n > for each row > set (n.autonum) = (select (max(autonum),0) + 1 from transaction); > > Postgres (as you might suspect) didn't like it. Looking at the Postgres > "create trigger" syntax, there is nothing about "referencing X as Y" being > a possible phrase. > > Is the problem in: > > 1. Dezign for Databases, this really isn't ANSI Level 2, > 2. Postgres, it doesn't (yet) support this level of functionality, but >should, or > 3. Me, I'm mixing up ANSI Level 2 and ANSI SQL '92, Postgres doesn't now >and has no plans to support ANSI Level 2 SQL IIRC, SQL92 doesn't have triggers. I believe the above is SQL 99 syntax and it looks like triggers aren't Core SQL99 either. PostgreSQL may eventually take the full SQL99 syntax but it's likely to be a while unless someone wants it enough to work on it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Triggers... Questions... Yes.
Tim Ellis <[EMAIL PROTECTED]> writes: > I just used Dezign for Databases and created a simple table in what it > claimed was "ANSI Level 2" compliance with a one-up "autonum" column. There is no such animal as "ANSI Level 2" SQL. > create trigger transaction_autonum_inc > before insert on transaction > referencing new as n > for each row > set (n.autonum) = (select (max(autonum),0) + 1 from transaction); Looking at SQL99 (that's ISO/IEC 9075-2:1999, Part 2: SQL/Foundation if we have to get pedantic about it), there is something about a "referencing" clause, which we do not have. However, the "set" command shown here does not appear to be legal according to SQL99, so I'm not planning to get excited about not having "referencing". There are a number of missing features in our trigger stuff, and that one seems right at the bottom of the list as far as adding useful functionality goes. BTW, what are they expecting "(max(autonum),0)" to do, other than draw a syntax error? Perhaps there was supposed to be a COALESCE there? Even if it worked or were standards-compliant, this approach to implementing an autonumbering column would be brain dead in the extreme --- you do *not* want to do a max() aggregate for every insert. Use a sequence object instead... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Indexes on separate disk ?
On Thu, Jun 13, 2002 at 12:49:39PM -0700, Charlie Toohey wrote: > I've looked around a lot and don't think this is possible with Postgres, but > figured I would ask in case I missed something. Is it possible to configure > things so that an index resides on a separate disk ? It doesn't look like it, > since they both have to reside in the same database, and the entire contents > of the database would reside underneath a single directory, and therefore on > a single disk. Maybe you can just move it to a different filesystem and use a symlink? It should be transparent to postgresql unless it ever replaces the file. -- Ragnar Kjørstad Big Storage ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Triggers... Questions... Yes.
> > I just used Dezign for Databases and created a simple table in what it > > claimed was "ANSI Level 2" compliance with a one-up "autonum" column. > > There is no such animal as "ANSI Level 2" SQL. That explains a lot of things. About this DDL problem and Dezign for Databases both. > > set (n.autonum) = (select (max(autonum),0) + 1 from transaction); > > BTW, what are they expecting "(max(autonum),0)" to do, other than draw a > syntax error? Perhaps there was supposed to be a COALESCE there? Yeh. Good question. I would assume their generator MEANT (max(autonum,0)+1) supposing that a "ANSI Level 2"-compliant server's max function might return the second value if the first is NULL. But no matter how you slice it, that looks syntactically wrong. > Even if it worked or were standards-compliant, this approach to > implementing an autonumbering column would be brain dead in the extreme > --- you do *not* want to do a max() aggregate for every insert. Use > a sequence object instead... Perhaps. Is the sequence object part of standard SQL? I know Postgres and Oracle have it (among others) but I also know of at least one major recent-version RDBMS that fails to have a sequence object. On the topic of max(x), there are certain RDBMSs that implement a max(x) in such a way that the optimiser will, supposing x has an index on it, use the index to determine max(x) rather than a table scan. I noticed during porting an from Sybase to Postgres this particular problem when my select max(x) from tableY queries were running very slowly. -- Tim Ellis DBA, Gamet ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Triggers... Questions... Yes.
Tim Ellis <[EMAIL PROTECTED]> writes: > Yeh. Good question. I would assume their generator MEANT > (max(autonum,0)+1) supposing that a "ANSI Level 2"-compliant server's > max function might return the second value if the first is NULL. But no > matter how you slice it, that looks syntactically wrong. I suspected they meant COALESCE(max(autonum),0) + 1, which would be correct since max() on no values is defined to produce NULL. I'm not aware of any variant of max() that takes two arguments. > Perhaps. Is the sequence object part of standard SQL? I know Postgres and > Oracle have it (among others) but I also know of at least one major > recent-version RDBMS that fails to have a sequence object. It is not standard. On the other hand, expecting people to have implemented every last frammish in SQL99 is a bit premature also... SQL92 didn't have triggers at all. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Indexes on separate disk ?
=?iso-8859-1?Q?Ragnar_Kj=F8rstad?= <[EMAIL PROTECTED]> writes: > On Thu, Jun 13, 2002 at 12:49:39PM -0700, Charlie Toohey wrote: >> I've looked around a lot and don't think this is possible with Postgres, but >> figured I would ask in case I missed something. Is it possible to configure >> things so that an index resides on a separate disk ? > Maybe you can just move it to a different filesystem and use a symlink? That's pretty much the standard hack: shut down the postmaster, move the file, create a symlink. However this is a fairly labor-intensive kluge. Especially so if the file exceeds 1Gb, because then you will need to deal with symlinking multiple segments --- and perhaps re-symlinking them, if the size dips below a Gb boundary and then grows again. Still, for sub-Gb-sized indexes it's certainly doable. We hope to have a cleaner tablespace-based approach in a release or two. BTW, I'd certainly recommend getting the WAL files (pg_xlog directory) moved to their own drive long before you worry about separating indexes from data. That's a lot simpler (you only need a symlink for the directory). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
