[GENERAL] Question about linux filesystem and psql
Is it possible to keep data safe with linux and psql after crash? The short description of the scenario is: -> writing data with psql (using transactions), store process completed -> user hits the reset button or kernel crashes (whatever left the filesystem unsynced) -> filesystem is xfs or reiserfs -> reboot causes some blocks filled with zero (journalling feature discussed on lkml) of last accessed files from some applications My questions are: Is there any solution that psql keeps the database intact (already written data)? Is there an option for psql or filesystem (like reiserfs data=ordered) which should be used (maybe backup database) Thanks Marcus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] mirroring data on different drives?
Someone please please help me :-( Steve Steve wrote: Hi, I've been running postgres on my server for over a year now and the tables have become huge. I have 3 tables that have data over 10GB each and these tables are read very very frequently. In fact, heavy searches on these tables are expected every 2 to 3 minutes. This unfortunately gives a very poor response time to the end user and so I'm looking at other alternatives now. Currently, the postgresql installation is on a single disk and so all the tables have their data read from a single disk. Searching on different tables by multiple users at the same time results in very slow searches, as it's mainly dependant on the spindle speed. I recently gained access to another server which has 3 SCSI disks. I know there is a way to mirror the tables across the three different disks but I'm not sure if it's as easy as symlinking the files (WAL files only?) across. Can anyone please tell me what to do here and how to harness the power of the three SCSI drives that I have. Which files in the data directory need to be moved? Is this safe? Can backups etc be easily done? Any information will be greatly appreciated. Thank you, Steve And now a word from our sponsor -- For a quality usenet news server, try DNEWS, easy to install, fast, efficient and reliable. For home servers or carrier class installations with millions of users it will allow you to grow! See http://netwinsite.com/sponsor/sponsor_dnews.htm ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] pg storage system
Where I can find some documents about PostgreSQL storage system ? I think that ERL-M87-06.pdf is quite old and need some details. Perheps there is some comparison between postgres and postgresql strorage system. Thanks for any information. Kris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Table access method not behaving like Oracle (index vs sequential scan). Examples and stats provided.
I expected Postgresql to use an indexed access method, but in certain cases it is using a sequential scan. Details are below: Table: P1_NRN_ROAD ( sobjidint8 primary key, vint8 not null, ordint2 not null) * The table contains 1.1 million rows. * Column 'v' exhibits very high selectivity: "select count(*) from (select distinct v from p1_nrn_road) A" returns 1,051,276. * The table contains one index: P1_NRN_ROAD_V (v, sobjid) (The index includes the column sobjid because the query projects this col, and its inclusion in the index allows it to be serviced without accessing the underlying table) * The table was vacuumed and analyzed after the index was created (I even set the col statistics to 1000) Now, for the queries: QUERY 1: select sobjid from p1_nrn_road where v = 2226497481090365991 The plan is "Index scan using p1_nrn_road_v on p1_nrn_road (cost=0.00..6.52 rows=2 width=8)" The plan was close: only one row is returned. Everything is fine here. QUERY 2: select sobjid from p1_nrn_road where v = 1 The plan is "Seq Scan on p1_nrn_road (cost=0.00..22158.54 rows=2 width=8)" The plan is wrong: "select min(v) from p1_nrn_road" returns 2226497481090365991, which indicates that the query will return 0 rows. I can't understand why a sequential scan is selected for query 2 when the plan suggests only two rows (high selectivity) are expected. Oracle doesn't behave like this, and I expect Postgresql to behave similarly (are my expectations too high?) Thank you in advance for any assistance you can provide. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] please please please PLEASE help!
Hi, I've asked this question a couple of times before on this forum but no one seems to be nice enough to point me to the right direction or help me out with any information, if possible. Please help me out with this because this is a very serious issue for me and I need to learn more about this. And here it is again: I've been running postgres on my server for over a year now and the tables have become huge. I have 3 tables that have data over 10GB each and these tables are read very very frequently. In fact, heavy searches on these tables are expected every 2 to 3 minutes. This unfortunately gives a very poor response time to the end user and so I'm looking at other alternatives now. Currently, the postgresql installation is on a single disk and so all the tables have their data read from a single disk. Searching on different tables by multiple users at the same time results in very slow searches, as it's mainly dependant on the spindle speed. I recently gained access to another server which has 3 SCSI disks. I know there is a way to mirror the tables across the three different disks but I'm not sure if it's as easy as symlinking the files (WAL files only?) across. Can anyone please tell me what to do here and how to harness the power of the three SCSI drives that I have. Which files in the data directory need to be moved? Is this safe? Can backups etc be easily done? Any information will be greatly appreciated. Thank you, Steve ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] installation problem...
Hi: I'm installing postgresql 7.4.3 on Solaris and during ./configure I get the following error: checking types of arguments for accept()... configure: error: could not determine argument types Any ideas? Jon Wynett Senior Java Developer Research Systems, Inc. 303-413-3985 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Sql injection attacks
In article <[EMAIL PROTECTED]>, "B. van Ouwerkerk" <[EMAIL PROTECTED]> writes: > I've been reading this discussion and I asked myself whether you guys > remove/replace unwanted chars from strings you get from the web or > not.. The problem is not limited to strings you get from the web. Those strings can come from _any_ source you don't control fully. And you don't remove unwanted chars - a search for "O'Neill" is prefectly reasonable and not more dangerous than a search for "Anderson" as long as you escape the quotation mark properly. > If you do remove them AFAIK it doesn't only prevent SQL injection but also XSS. You can prevent XSS in the same manner: carefully escape everything that looks dangerous. You just use different escaping rules because you have other dangerous characters (especially '<'). ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] installation problem...
There should be a config.log file in the directory where you ran configure: output in that file around the error is the first place to look. - DAP -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jonathan Wynett Sent: Friday, July 23, 2004 5:37 PM To: '[EMAIL PROTECTED]' Subject: [GENERAL] installation problem... Hi: I'm installing postgresql 7.4.3 on Solaris and during ./configure I get the following error: checking types of arguments for accept()... configure: error: could not determine argument types Any ideas? Jon Wynett Senior Java Developer Research Systems, Inc. 303-413-3985 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Backup questions
I have 2 questions. 1. What is the best way to back up a production box running Postgres nightly? 2. I thought the best way was to create a script that would use pg_dump to create a dump every night and then use restore if I need to. I did this but I am having a problem. Apparently there were a few Primary key Big Serial fields that had the name changed on them after they were created. The backup works fine but during the restore it fails because it thinks a relation is missing. Before restoring the database with the changed fields works fine, I just cannot get it to restore. Is there something in the background that Postgres is hanging on to after I renamed the fields that is preventing me from restoring? How can I work around this? Thank you Jason Tesser Software Development NMI 715-324-6900 x5273 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question about linux filesystem and psql
Marcus Wegner <[EMAIL PROTECTED]> writes: > Is it possible to keep data safe with linux and psql after crash? > > The short description of the scenario is: > -> writing data with psql (using transactions), store process completed > -> user hits the reset button or kernel crashes (whatever left the filesystem > unsynced) > -> filesystem is xfs or reiserfs > -> reboot causes some blocks filled with zero (journalling feature discussed > on lkml) of last accessed files from some applications The quesiton here is: do these filesystems lie about fsync()? Or do they just corrupt files that were written but not synced before the crash? I think it's the latter--lying about fsync() is a pretty major bug, and I don't recall that being claimed on LKML. Given this, PG should be in good shape--it fsyncs() the WAL file before reporting transaction success, so it should be able to recover committed transactions. > My questions are: > Is there any solution that psql keeps the database intact (already written > data)? > Is there an option for psql or filesystem (like reiserfs data=ordered) which > should be used (maybe backup database) You should always back up your data. :) You might also consider ext3 with data=writeback. As long as the WAL files are in sync and the filesystem metadata is journaled, PG can recover from crashes just fine (barring actual hardware failure). -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Backup questions
I have 2 questions. 1. What is the best way to back up a production box running Postgres nightly? 2. I thought the best way was to create a script that would use pg_dump to create a dump every night and then use restore if I need to. I did this but I am having a problem. Apparently there were a few Primary key Big Serial fields that had the name changed on them after they were created. The backup works fine but during the restore it fails because it thinks a relation is missing. Before restoring the database with the changed fields works fine, I just cannot get it to restore. Is there something in the background that Postgres is hanging on to after I renamed the fields that is preventing me from restoring? How can I work around this? Thank you Jason Tesser Software Development NMI 715-324-6900 x5273 ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] mirroring data on different drives?
Steve <[EMAIL PROTECTED]> wrote: > Someone please please help me :-( > > Steve > > Steve wrote: > > Hi, > > > > I've been running postgres on my server for over a year now and the > > tables have become huge. I have 3 tables that have data over 10GB each > > and these tables are read very very frequently. In fact, heavy searches > > on these tables are expected every 2 to 3 minutes. This unfortunately > > gives a very poor response time to the end user and so I'm looking at > > other alternatives now. > > > > Currently, the postgresql installation is on a single disk and so all > > the tables have their data read from a single disk. Searching on > > different tables by multiple users at the same time results in very slow > > searches, as it's mainly dependant on the spindle speed. I recently > > gained access to another server which has 3 SCSI disks. I know there is > > a way to mirror the tables across the three different disks but I'm not > > sure if it's as easy as symlinking the files (WAL files only?) across. > > Can anyone please tell me what to do here and how to harness the power > > of the three SCSI drives that I have. Which files in the data directory > > need to be moved? Is this safe? Can backups etc be easily done? Any > > information will be greatly appreciated. Thank you, The answer to your question is OS/hardware dependent. The best alternative is to have hardware-based RAID with battery backing. This provides excellent speed with good safety. The next option is to use some sort of software RAID. How to do this depends on the OS you are using, and will be covered in the docs for that OS. I don't think a lot of symlinking is a very good way to distribute your data. Many folks have had success by keeping the data and the WAL on seperate disks, but that leaves you with a third disk that isn't helping any. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] tablespace spec
David Parker wrote: > Is there a spec/posting somewhere that more or less reflects how > tablespaces are supposed to work in 7.5? I've found a lot of info on it > by searching the archives, but I haven't found the posting that > describes the basic functionality. Have you looked at the development docs for 7.5: http://developers.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] mirroring data on different drives?
Steve <[EMAIL PROTECTED]> wrote: > > Someone please please help me :-( [snip] Relax, Steve. The people helping-out on PostgreSQL mailing lists are in 24 timezones, have real jobs (for which they actually get paid), are all volunteers (for which they don't get paid), may be going to school, take vacations, sleep, etc. It may take some time, maybe hours, or even days, depending on a variety of factors, for somebody to address your question(s). And that's assuming somebody knows, has the time, and will take an interest. I'm no expert in pgsql performance, but I suspect you'd be better-off with UW-SCSI drives in a RAID array, than doing things like trying to put different bits on different drives. There are reams of other questions, such as: Do you "vacuum analyze" regularly? Do you have sufficient sort memory? It may be that your question(s) would be more appropriately directed to the performance mailing list, rather than general. You might also help yourself by spending some time searching/browsing the performance, admin and general mailing lists archives for past discussions of issues similar to yours. Jim ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Table access method not behaving like Oracle (index vs sequential scan). Examples and stats provided.
"Kevin Macdonald" <[EMAIL PROTECTED]> writes: > QUERY 2: select sobjid from p1_nrn_road where v = 1 > > The plan is "Seq Scan on p1_nrn_road (cost=0.00..22158.54 rows=2 width=8)" Incidentally, you should send the whole plan. In general you should send all the information you have, not just the parts you think are relevant. The next line would have clearly shown the problem to someone who knew what to look for. try where v = '1' or where v = 1::bigint The problem is that in 7.4 and previous indexes can't be used for cross-type comparisons and integer constants are assumed to be integer not bigint type. If you leave it in quotes then postgres doesn't pick a type until it looks at what you're comparing it with. Or if you cast it then you force it to be a bigint=bigint comparison. 7.5 will avoid this problem. -- greg ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] please please please PLEASE help!
On Friday 23 Jul 2004 11:41 am, Steve wrote: > Currently, the postgresql installation is on a single disk and so all > the tables have their data read from a single disk. Searching on > different tables by multiple users at the same time results in very slow > searches, as it's mainly dependant on the spindle speed. I recently > gained access to another server which has 3 SCSI disks. I know there is > a way to mirror the tables across the three different disks but I'm not > sure if it's as easy as symlinking the files (WAL files only?) across. > Can anyone please tell me what to do here and how to harness the power > of the three SCSI drives that I have. Which files in the data directory > need to be moved? Is this safe? Can backups etc be easily done? Any > information will be greatly appreciated. Thank you, Postgresql 7.5 which is expected to enter in beta soon, has tablespace support which can do these sort of things without resorting to any filesystem changes. You can give it a try. Or you can do a software RAID on these drives to speed up reads. That should achieve similar effect. HTH Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] tablespace spec
http://developer.postgresql.org Bruce Momjian wrote: David Parker wrote: Is there a spec/posting somewhere that more or less reflects how tablespaces are supposed to work in 7.5? I've found a lot of info on it by searching the archives, but I haven't found the posting that describes the basic functionality. Have you looked at the development docs for 7.5: http://developers.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Wal logs
This question may seem trivial but how do I rebuild a database with the wal logs? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL]
"Chris Gamache" <[EMAIL PROTECTED]> wrote: > When I reload and attempt to access the database I find this > in the logs: > > ERROR: cache lookup failed for function 75769893 You should be OK as long as you have the dump file. If you haven't modifed any of the tsearch2 pg_ts_* tables, try and load tsearch2.sql first before loading the dump file. You will get a lot of errors when you load the dump file. It will try to create object that tsearch2.sql already created. Things should work if you haven't modified any of the tsearch2 tables. Below is some sample code. Change the names of the databases and the path to the *.sql files. createdb ts_db_b; psql ts_db_b < tsearch2.sql; psql ts_db_b < ts_db_a.sql; So what if you did modify the pg_ts_* tables? Load the dump file into a new database. Read about the regprocedure patch at: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html Install the file regprocedure_7.4.patch.gz, then load the following into your database. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_update.sql George Essig ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] mirroring data on different drives?
Steve wrote: Someone please please help me :-( Hi Steve, How much leverage over the design of the application do you have? Depending on the data that's being accessed, it may be worth looking into materialised views for some of the data. Very application dependent on course: http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html Hope that helps. Regards and best wishes, Justin Clift Steve Steve wrote: Hi, I've been running postgres on my server for over a year now and the tables have become huge. I have 3 tables that have data over 10GB each and these tables are read very very frequently. In fact, heavy searches on these tables are expected every 2 to 3 minutes. This unfortunately gives a very poor response time to the end user and so I'm looking at other alternatives now. Currently, the postgresql installation is on a single disk and so all the tables have their data read from a single disk. Searching on different tables by multiple users at the same time results in very slow searches, as it's mainly dependant on the spindle speed. I recently gained access to another server which has 3 SCSI disks. I know there is a way to mirror the tables across the three different disks but I'm not sure if it's as easy as symlinking the files (WAL files only?) across. Can anyone please tell me what to do here and how to harness the power of the three SCSI drives that I have. Which files in the data directory need to be moved? Is this safe? Can backups etc be easily done? Any information will be greatly appreciated. Thank you, Steve ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Wal logs
Jason Tesser wrote: > This question may seem trivial but how do I rebuild a database with the > wal logs? You can't, but with 7.5 you can archive those logs and use them and a recent backup for point-in-time recovery. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Rendezvous Name?
Hi, I am running postgresql 7.3.4 with rendezvous enabled on MacOSX 10.3.4. I tried changing the rendezvous_name parameter in the postgresq.conf file to 'RendezvousAtMacJerry' and SIGHUP'ed the server. However the rendezvous name did not change, it is still the rendezvous name for my computer. Is the rendezvous name hard coded? Jerry Sigh, someone else was logged in to the DB via psql after they logged out the name changed. Jerry ---(end of broadcast)--- TIP 3: 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
Re: [GENERAL] Wal logs
how -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Thu 7/29/2004 7:56 PM To: Jason Tesser Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Wal logs Jason Tesser wrote: > This question may seem trivial but how do I rebuild a database with the > wal logs? You can't, but with 7.5 you can archive those logs and use them and a recent backup for point-in-time recovery. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Wal logs
Point-in-time recovery coming in 7.5. You want instructions? --- Jason Tesser wrote: > how > > > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > Sent: Thu 7/29/2004 7:56 PM > To: Jason Tesser > Cc: [EMAIL PROTECTED] > Subject: Re: [GENERAL] Wal logs > Jason Tesser wrote: > > This question may seem trivial but how do I rebuild a database with the > > wal logs? > > You can't, but with 7.5 you can archive those logs and use them and a > recent backup for point-in-time recovery. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
