Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
Today we were able to look at the first computer's files.
About 20 - 30 of them were marked system, hidden, read only.
We cleared the attribute bits with the ATTRIB command and the database now
appears to be normal. We were able to do a backup (that failed before) and were
able to paste in 50K blocks of text with no problems.
We verified that the data directory is owned by the proper postgres user
("limited account") and that only that user has access to the directory or its
files. (This is unlike the second computer, where we had permitted other users
to have access.)
So it appears to be a pristine postgres installation, except that somehow some
of the files were changed.
We have no clue how that happened. At this time the AV software is not
installed. We will watch carefully to see if files get altered again in this
way. Although the second computer has a newer OS and is more powerful, the
client prefers to leave the database on the original computer because it has
better physical security.
Now we just wait to see what happens next, if anything. Thanks for your help.
John
On Mon, 14 Jun 2010 10:59:18 +0200, Magnus Hagander wrote:
>On Mon, Jun 14, 2010 at 10:57, Scott Marlowe wrote:
>> On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander wrote:
>>> On Mon, Jun 14, 2010 at 05:17, John T. Dow wrote:
>>>> Apparently the problem boils down to this question: how did some of the
>>>> files get set to be system and read only?
>>>
>>> Yes. That would be very interesting to know. PostgreSQL never
>>> (intentionally) sets these flags, so they must've come from something
>>> else.
>>
>> Being a non-privaledged account, does the postgres user even have the
>> power to do that?
>
>Yes, IIRC any user that has write permissions on a file can set the
>attributes, including readonly and system.
>
>
>--
> Magnus Hagander
> Me: 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
--
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] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
I was talking to a friend (Joe Newcomer) who said that Unix doesn't have mandatory file locks and he guessed that the empty, system, read only files I saw at my client's site were unix-like lock files. To test that, on my home development computer I typed this command in the base\16384 diretory: attrib +r 2611 That is, I made 2611 read only. Sure enough, pgadmin can't display the columns for any of the tables. I get "permission denied" for 2611. And sure enough, the Java application runs fine and indeed is able to export the table definition, complete with columns. So this is exactly the behavior observed at my client's site. Apparently the problem boils down to this question: how did some of the files get set to be system and read only? Anybody ever seen this? Perhaps it's not even a postgres question. We will investigate further Monday when people are in the office. Any thoughts from anybody would be appreciated. Reminder: the problem with 2611 was observed on the second computer, which runs XP Pro 2002 SP3. The problems pasting 50K of text was first observed on the first computer, running 2000 Server if I remember right. It does not therefore seem to be related to AV software (the original suggestion) or the OS. John On Sun, 13 Jun 2010 14:10:27 -0700, Adrian Klaver wrote: >On Sunday 13 June 2010 1:41:01 pm John T. Dow wrote: >> I have information >> >> We had noticed two relations, their numbers being 16384/16642 and >> 16384/16792. >> >> Here is what pg_class has for them. >> >> >> "relname";"relnamespace";"reltype";"relowner";"relam";"relfilenode";"reltab >>lespace";"relpages";"reltuples";"reltoastrelid";"reltoastidxid";"relhasindex >>";"relisshared";"relistemp";"relkind";"relnatts";"relchecks";"relhasoids";"r >>elhaspkey";"relhasrules";"relhastriggers";"relhassubclass";"relfrozenxid";"r >>elacl";"reloptions" >> >> "pg_toast_16638";99;16643;16510;0;16642;0;0;0;0;16644;t;f;f;"t";3;0;f;t;f;f >>;f;1581;"";"" >> >> "pg_toast_16788";99;16793;16510;0;16792;0;0;0;0;16794;t;f;f;"t";3;0;f;t;f;f >>;f;2202;"";"" >> >> We also looked at the permissions and whether the files actually exist. >> >> Findings: The files are both marked "system file" and have size 0 K. When >> logging on as an administrator and opening the files (eg with notepad, just >> to see if there is nothing at all) they appear to be empty. > >Whose permissions do they have? > >> >> However, while we were working on the problem, pgadmin3 started reporting >> "permission denied" for 2611. At the same time, pgadmin was unable to see >> the columns of the tables. Attempting to do so is what caused the error for >> 2611. >> >> 2611 also appeared to be a system file with 0 bytes. > >What does Postgres think it is? Another TOAST table? > >> >> Meantime, pgadmin was able to create a table and see the columns on the >> standard postgres database. > >Now I am confused. What are you calling the standard Postgres database? > >> >> Also, the Java application was able to see the columns and list them out as >> well. > >Of which database? > >> >> I have noticed that postgres is very unhappy if the proper "postgres" user >> doesn't have access to the files. But I have also noticed that other users >> seem to be able to have access without causing problems. I realize this >> compromises security, but in a development environment it is very >> convenient, eg when doing a system backup. > >Sort of the purpose of permissions :) > >> >> Is it possible that some type of user might be causing files to be created >> as or changed to system files, marked read only, and apparently empty? > >It would seem so. The question is whether this a historical artifact from >corruption in the past or is ongoing? > >> >> I am not certain which users have access to the files at the client's site, >> but I know it's more than just the postgres user. >> >> All of these findings were on the second computer running XP. We ran out of >> time today before we investigated the original server to see if it also had >> system files marked read only with no apparent contents. >> >> John > > > > >-- >Adrian Klaver >adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
>can you please give more information about the (windows)-user "postgres" ? >is it a local user on that machine? How was that user created? It's the user created by the one-click installer. I believe it owns the postgres data directory and is used to start the server. Other than that, the intention is for this user to have no other file privileges. The default is "postgres" but it could be anything. > >Are there any group-policies or similar, or "security-applications" present, >which can change the rights of this user postgres? (Or, can change the >access-properties of files on the system?) I don't know. It is not my computer, it is my client's computer. We will investigate if anything like that is going on. He was only available until 4PM today and we just discovered what was happening shortly before that point. The people that do their security should be available Monday and we can ask them this type of question. Any idea of what to look for? > >Your sentenceabout "postgres being unhappy when not having access to the >files" makes me curious how you did learn that --- was somebody / something >taking file access away from Postgres? Could that somebody / something still >be active? That somebody was me, experimenting over the years. But I have not been messing around with this particular application. However, I'm not sure what the client did, as they copied the data files between the two computers at a time when I wasn't available. (They zipped, then unzipped after logging in as the proper user.) As a developer for multiple clients, I need easy access to my development copies of my clients' postgres data files. Therefore I have experimented with allowing my own userid to have access to the "data" directory and the subdirectories and files. I believe postgres doesn't care if you allow extra users, as long as "postgres" still has the proper access. John > >Harald > > >-- >GHUM Harald Massa >persuadere et programmare >Harald Armin Massa >Spielberger StraAYe 49 >70435 Stuttgart >0173/9409607 >no fx, no carrier pigeon >- >Using PostgreSQL is mostly about sleeping well at night. > -- 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] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
I have information We had noticed two relations, their numbers being 16384/16642 and 16384/16792. Here is what pg_class has for them. "relname";"relnamespace";"reltype";"relowner";"relam";"relfilenode";"reltablespace";"relpages";"reltuples";"reltoastrelid";"reltoastidxid";"relhasindex";"relisshared";"relistemp";"relkind";"relnatts";"relchecks";"relhasoids";"relhaspkey";"relhasrules";"relhastriggers";"relhassubclass";"relfrozenxid";"relacl";"reloptions" "pg_toast_16638";99;16643;16510;0;16642;0;0;0;0;16644;t;f;f;"t";3;0;f;t;f;f;f;1581;"";"" "pg_toast_16788";99;16793;16510;0;16792;0;0;0;0;16794;t;f;f;"t";3;0;f;t;f;f;f;2202;"";"" We also looked at the permissions and whether the files actually exist. Findings: The files are both marked "system file" and have size 0 K. When logging on as an administrator and opening the files (eg with notepad, just to see if there is nothing at all) they appear to be empty. However, while we were working on the problem, pgadmin3 started reporting "permission denied" for 2611. At the same time, pgadmin was unable to see the columns of the tables. Attempting to do so is what caused the error for 2611. 2611 also appeared to be a system file with 0 bytes. Meantime, pgadmin was able to create a table and see the columns on the standard postgres database. Also, the Java application was able to see the columns and list them out as well. I have noticed that postgres is very unhappy if the proper "postgres" user doesn't have access to the files. But I have also noticed that other users seem to be able to have access without causing problems. I realize this compromises security, but in a development environment it is very convenient, eg when doing a system backup. Is it possible that some type of user might be causing files to be created as or changed to system files, marked read only, and apparently empty? I am not certain which users have access to the files at the client's site, but I know it's more than just the postgres user. All of these findings were on the second computer running XP. We ran out of time today before we investigated the original server to see if it also had system files marked read only with no apparent contents. John On Sun, 13 Jun 2010 11:51:45 +0200, Magnus Hagander wrote: >On Sun, Jun 13, 2010 at 5:11 AM, Craig Ringer > wrote: >> On 13/06/10 02:34, Adrian Klaver wrote: >> Question: Is it possible that there's corruption in the database which is being incorrectly reported as "Permission denied"? >> >> It's certainly not impossible. It'd really help if Pg would print more >> details from Windows' error reporting - GetLastError() etc - in cases >> like this. In fact, some searching reveals complaints about just that as >> far back as mid-2008 related to the exact error you're encountering. > >It does if you enable debug logging. DEBUG5 is required from what I >can tell (see src/port/win32error.c, function _dosmaperr(), which is >called from pgwin32_open()). > >In a lot of cases it maps straight over, but in the cases where we >have to map to an errno value and use that, there can be more than >one. In the case of access denied, it can be: >ERROR_ACCESS_DENIED >ERROR_CURRENT_DIRECTORY >ERROR_LOCK_VIOLATION >ERROR_SHARING_VIOLATION (but this is taken care of already in pgwin32_open) >ERROR_NETWORK_ACCESS_DENIED >ERROR_CANNOT_MAKE >ERROR_FAIL_I24 >ERROR_DRIVE_LOCKED >ERROR_SEEK_ON_DEVICE >ERROR_NOT_LOCKED >ERROR_LOCK_FAILED > >Most of these can't (shouldn't be possible at least) appear when we're >opening a file for reading. But it'd be interesting to know what they >were. > >So it'd be interesting to see the output of this at DEBUG5 (there >should be a line saying "mapped win32 error code to " showing >up - there will be *tons* of other logging output of course) > > >-- > Magnus Hagander > Me: 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 -- 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] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
I will provide some answers tomorrow, but I don't have access now. It's a law enforcement agency in another state and the officer I work with will be in Sunday. The original computer's postgres server is stopped, and port forwarding wasn't changed yet so I don't have access to the new computer where the server is running. I have no access to the files themselves unless he's there and let's me in. Thanks. Keep tuned. John On Sat, 12 Jun 2010 15:59:06 -0700, Adrian Klaver wrote: >On Saturday 12 June 2010 12:59:18 pm John T. Dow wrote: > >I am CC'ing the list so more eyes can follow this. > >> > >> >Some more questions. >> >What is the relation that is having the permissions issue? >> >Are the permissions on that file different from the others in the base >> > directory tree? >> >What is the 'certain text'? >> >What are the 'certain fields' and do they have any functions running on >> > them? >> > >> > >> > >> >-- >> >Adrian Klaver >> >> The actual message is: could not open relation base/16384/16642: Permission >> denied. >> >> I presume that the actual file names are not important as they change from >> time to time? > > > >They may or may not depending on what type of relation they are and the type >of >operation done to them. I should have been more specific. What is 16642? >SELECT relname,relkind from pg_class where relfilenode=16642 should provide an >answer. > >> >> No, we have not diddled with the permissions. All files inherit their >> permissions from "data". > >The question is really not whether you changed the permissions, but whether >they >have been changed? Hate to be anal about this, but have you actually looked at >the permissions for that file or are you assuming inheritance of permissions? > >> >> The text I've been using for test purposes is a plain ascii version of the >> US Constitution. It's about 50K, nothing special about it. Just a plain >> text file I had on hand. > >But large enough to invoke TOASTing the value. See here for more info: >http://www.postgresql.org/docs/8.4/interactive/storage-toast.html > >> >> The fields where we noticed it at a couple jTextArea fields in a Java >> application. They are defined as varchar. There is nothing special about >> these fields. No functions, etc. They are fields to hold plain English >> text. Data is normally entered by someone typing it in. (One example is a >> police officer typing in his report of an arrest he's made. After he types >> it in, it's printed and signed and becomes a legal document.) There is a >> similar field in each of three tables: two of the three produced this error >> when we pasted in the text, but the third one had no problem. We also >> pasted the text into some other fields and got the problem. However, except >> for these fields and this text, we have load many fields with data from a >> legacy system and have typed in information into many fields and have had >> no problems. That is to say, the problem is rare, although it has happened >> every time we've pasted that text into those two fields. > >Might be worth showing us the complete schema for those tables. Just to be >clear, which describes the problem: >A)100% reproducible by putting the above text into either of the two fields >OR >B) Rare but when it happens it involves the above text and these two fields. > >> >> John > > > >-- >Adrian Klaver >adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
I posted this two days ago and nobody has commented. I'm reposting the message because I really need advice. Background info: My client got the "permission denied" error on his original server and we removed the AV software without solving the problem. Since the computer is running Windows 2000 Server, SP4, it was suggested that a newer OS might help. We moved the data and the problem persists even on the new computer. What follows is my status report about the new computer as well as the process of copying the data. Note that backing up the database on the original server also gets the "permission denied" error. Posting from 6/10 follows. Here's the current status. We installed postgres on an XP machine, 2002 SP3. (Same as my computer, which never has a problem.) We tried to do a backup of the database on the old computer, to copy the data to the new computer. The backup failed with the same problem mentioned in the subject line. So we zipped up the data directory and unzipped it on the XP computer. We then attempted to paste in the "large" block of text (200 lines of plain ascii, 49000 bytes) and got the same problem as before. Note that the load on the server and on postgres is very low, and that the problem can be recreated with 100% certainty when we paste certain text into certain fields. This computer is running "Symantec Endpoint Protection", with the proactive threat feature turned off. Question: Is it possible that there's corruption in the database which is being incorrectly reported as "Permission denied"? Perhaps the original problem on the other computer created the corruption? Or the corruption came from another source and on both computers creates the incorrect message? We could of course recreate pretty much the same database. We're in development mode now: it was loaded with data from the legacy system extracted a few months ago and since then there has been additional data entered and changed as people have played with and tested the application. Is this a random event? A bug? Advice please on what to do next. John On Tue, 08 Jun 2010 08:37:02 -0400, John T. Dow wrote: >On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote: > >>On 8/06/2010 9:11 AM, John T. Dow wrote: >>> OP here >>> >>> We removed AVG from the computer and rebooted. >>> >>> Same problem. >> >>OK, good to know. Thanks very much for testing that, and my apologies >>for recommending something that didn't work out. Of course, it would >>have been hard to progress without eliminating that possible factor. >> >>> Could it be 2000 Server? SP4? I've seen reports of other problems that went >>> away depending on the version of Windows. >> >>Well, certainly I'd expect that Pg on Windows 2000 server gets about >>zero regular testing. Why would you deploy a server OS that's already 10 >>years out of date, went EOL five years ago, and lost even the option of >>paid extended support this year? >> >>http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274 >> > > > > > >Good point. > >It's not my server, it's my client's server, and I don't know the history of >it. > >They have mentioned another computer which runs XP I believe. It's dedicated >to a single task and could double as the database server, although I don't >think it has any RAID. I will suggest that we try installing Postgres on that >computer and see if the problem goes away. If so, they might choose to make >that their solution (perhaps adding another hard drive and a RAID controller). >The application, daily backups, and WAL files could all live on the original >server. > >If they go that route, we'd never know for certain what the original problem >was. > >I'll post back after anything is done. > >JOhn > > > > > > > > > >>It'd be interesting to investigate this issue ... but win2k server isn't >>exactly easy to come by. Anyone on the list got a win2k server (or >>license) around they can do some experimenting on? All I have here is >>NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop >>suspects. >> >>-- >>Craig Ringer >> >> >>-- >>Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>To make changes to your subscription: >>http://www.postgresql.org/mailpref/pgsql-general > > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
Here's the current status. We installed postgres on an XP machine, 2002 SP3. (Same as my computer, which never has a problem.) We tried to do a backup of the database on the old computer, to copy the data to the new computer. The backup failed with the same problem mentioned in the subject line. So we zipped up the data directory and unzipped it on the XP computer. We then attempted to paste in the "large" block of text (200 lines of plain ascii, 49000 bytes) and got the same problem as before. Note that the load on the server and on postgres is very low, and that the problem can be recreated with 100% certainty when we paste certain text into certain fields. This computer is running "Symantec Endpoint Protection", with the proactive threat feature turned off. Question: Is it possible that there's corruption in the database which is being incorrectly reported as "Permission denied"? Perhaps the original problem on the other computer created the corruption? Or the corruption came from another source and on both computers creates the incorrect message? We could of course recreate pretty much the same database. We're in development mode now: it was loaded with data from the legacy system extracted a few months ago and since then there has been additional data entered and changed as people have played with and tested the application. Is this a random event? A bug? Advice please on what to do next. John On Tue, 08 Jun 2010 08:37:02 -0400, John T. Dow wrote: >On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote: > >>On 8/06/2010 9:11 AM, John T. Dow wrote: >>> OP here >>> >>> We removed AVG from the computer and rebooted. >>> >>> Same problem. >> >>OK, good to know. Thanks very much for testing that, and my apologies >>for recommending something that didn't work out. Of course, it would >>have been hard to progress without eliminating that possible factor. >> >>> Could it be 2000 Server? SP4? I've seen reports of other problems that went >>> away depending on the version of Windows. >> >>Well, certainly I'd expect that Pg on Windows 2000 server gets about >>zero regular testing. Why would you deploy a server OS that's already 10 >>years out of date, went EOL five years ago, and lost even the option of >>paid extended support this year? >> >>http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274 >> > > > > > >Good point. > >It's not my server, it's my client's server, and I don't know the history of >it. > >They have mentioned another computer which runs XP I believe. It's dedicated >to a single task and could double as the database server, although I don't >think it has any RAID. I will suggest that we try installing Postgres on that >computer and see if the problem goes away. If so, they might choose to make >that their solution (perhaps adding another hard drive and a RAID controller). >The application, daily backups, and WAL files could all live on the original >server. > >If they go that route, we'd never know for certain what the original problem >was. > >I'll post back after anything is done. > >JOhn > > > > > > > > > >>It'd be interesting to investigate this issue ... but win2k server isn't >>exactly easy to come by. Anyone on the list got a win2k server (or >>license) around they can do some experimenting on? All I have here is >>NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop >>suspects. >> >>-- >>Craig Ringer >> >> >>-- >>Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>To make changes to your subscription: >>http://www.postgresql.org/mailpref/pgsql-general > > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote: >On 8/06/2010 9:11 AM, John T. Dow wrote: >> OP here >> >> We removed AVG from the computer and rebooted. >> >> Same problem. > >OK, good to know. Thanks very much for testing that, and my apologies >for recommending something that didn't work out. Of course, it would >have been hard to progress without eliminating that possible factor. > >> Could it be 2000 Server? SP4? I've seen reports of other problems that went >> away depending on the version of Windows. > >Well, certainly I'd expect that Pg on Windows 2000 server gets about >zero regular testing. Why would you deploy a server OS that's already 10 >years out of date, went EOL five years ago, and lost even the option of >paid extended support this year? > >http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274 > Good point. It's not my server, it's my client's server, and I don't know the history of it. They have mentioned another computer which runs XP I believe. It's dedicated to a single task and could double as the database server, although I don't think it has any RAID. I will suggest that we try installing Postgres on that computer and see if the problem goes away. If so, they might choose to make that their solution (perhaps adding another hard drive and a RAID controller). The application, daily backups, and WAL files could all live on the original server. If they go that route, we'd never know for certain what the original problem was. I'll post back after anything is done. JOhn >It'd be interesting to investigate this issue ... but win2k server isn't >exactly easy to come by. Anyone on the list got a win2k server (or >license) around they can do some experimenting on? All I have here is >NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop >suspects. > >-- >Craig Ringer > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general -- 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] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
OP here We removed AVG from the computer and rebooted. Same problem. We are quite certain that AVG is no longer installed. It doesn't show up where it used to, and a search of the registry for "AVG" finds a couple leftovers but doesn't seem to indicate that it's still installed. The computer is running Windows 2000 Server, SP4. Build 5.00.2195. It's a Pentium (R) 4 CPU 2.80 GHz, AT/AT Compatible, 1 GB memory. That's not a particularly powerful computer, but so far we're only testing the application. Someone asked about load, someone also mentioned that mixing file serving and database serving is not good in general. Since we're only testing right now and in fact don't expect much load when in production, those probably aren't issues. Anyway, it now looks like the problem is not caused by AV software. Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of Windows. Thanks. John On Mon, 07 Jun 2010 16:35:33 +0200, Thomas Kellerer wrote: >Magnus Hagander, 07.06.2010 16:15: >> On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer wrote: >>> Magnus Hagander, 07.06.2010 15:52: > > Some AV software probably behaves fine. Probably. >>> >>> In case anyone is interested: >>> >>> I have two development computers that run Postgres on Windows XP. >>> One with Avira the other with Sophos. >>> >>> Neither has or had any problems installing or running Postgres >> >> What kind of load do the systems have? Particularly, how many >> parallell connections? That seems to push things over the edge more >> often than high transaction single-user ones. >> > >Ah, that might make the difference: >I have no real load on those computers (as I said, developer machine) > >So it's more a single-user type of load > >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 -- 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 on Windows server could not open relation base/xxx/xxx Permission denied
One of my clients is getting this problem occasionally. Actually, we can cause it to happen quite reliably by pasting certain text into a couple of fields, but the vast majority of text entered into the vast majority of fields causes no problem. I've read enough to suggest that AV software might be the culprit. It has been said that it is not sufficient to exclude the database directory nor even to disable to AV protection, it has to be removed. The problem is, their database server is also a file server. As a file server it must have AV protection. The server is running Windows Server 2003 I believe. It has RAID etc. My client's antivirus software is AVG (paid, not free). Question: Is AV software still regarded as the likely culprit? Question: If so, is any particular brand less likely to cause problems, more likely? Question: Any other suggestions? I'd had to tell my client to purchase more hardware because the database software I've recommended has a problem. I have a number of other clients using Postgres and nobody else has had any problem. Switching AV software wouldn't be such an issue. Thanks. John -- 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] Optimistic locking with multiple rows
On Sun, 10 Jan 2010 16:01:57 +0800, Craig Ringer wrote:
>On 6/01/2010 10:53 PM, John T. Dow wrote:
>> I posted this several days ago to pgsql-jdbc but have had no response. I am
>> posting it here (with minor changes in the wording).
>>
>> I have developed some code that works, I'm just not sure I have the "best"
>> solution.
>>
>> I have applications in which the user can create a read-only resultset with
>> multiple rows. For example, customers who are 90 days in arrears might be
>> brought up for review.
>>
>> The user might scroll through the rows reviewing the data, and then he might
>> decide to update one of them. A second query is used to update that one row.
>> At the time of the update, the current contents of that row is reread FOR
>> UPDATE and compared against the original row. If they differ, someone else
>> has altered the row after the resultset was created.
>>
>> The user is informed that another user has changed the row in question; he
>> can then decide to accept the changes he has made or leave in place the
>> changes made by the other user. In either case, that row in the original
>> resultset has to be made to match the current contents in the table, because
>> the user might scroll back and forth and revisit it.
>>
>> I am using refreshRow() to make that row current, but the problem is that
>> refreshRow() can be extremely slow.
>>
>> I create the read-only, multiple row resultset (ie "viewResultSet") like
>> this:
>>
>> createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
>> ResultSet.CONCUR_READ_ONLY);
>> viewResultSet = jdbcStmt.executeQuery("SELECT ... FROM ... WHERE select
>> multiple rows");
>>
>> Scroll through the resultset to view rows as desired.
>>
>> When positioned at a row, can update that row. See below.
>
>Can you instead create an explicit, named updatable cursor? Then FETCH
>from it, and UPDATE ... WHERE CURRENT OF it? That way you won't have to
>do all this kludging with refreshing rowsets.
>
>--
>Craig Ringer
I have not used cursors, so you've inspired me to rtfm about them. So far I
don't see how to implement optimistic locking with cursors.
The postgres documentation says this: "Without FOR UPDATE, a subsequent WHERE
CURRENT OF command will have no effect if the row was changed since the cursor
was created."
I read that to mean that the cursor has to be declared FOR UPDATE, which means
that the rows are locked and I don't have optimistic locking.
John
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Optimistic locking with multiple rows
I posted this several days ago to pgsql-jdbc but have had no response. I am
posting it here (with minor changes in the wording).
I have developed some code that works, I'm just not sure I have the "best"
solution.
I have applications in which the user can create a read-only resultset with
multiple rows. For example, customers who are 90 days in arrears might be
brought up for review.
The user might scroll through the rows reviewing the data, and then he might
decide to update one of them. A second query is used to update that one row. At
the time of the update, the current contents of that row is reread FOR UPDATE
and compared against the original row. If they differ, someone else has altered
the row after the resultset was created.
The user is informed that another user has changed the row in question; he can
then decide to accept the changes he has made or leave in place the changes
made by the other user. In either case, that row in the original resultset has
to be made to match the current contents in the table, because the user might
scroll back and forth and revisit it.
I am using refreshRow() to make that row current, but the problem is that
refreshRow() can be extremely slow.
I create the read-only, multiple row resultset (ie "viewResultSet") like this:
createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
viewResultSet = jdbcStmt.executeQuery("SELECT ... FROM ... WHERE select
multiple rows");
Scroll through the resultset to view rows as desired.
When positioned at a row, can update that row. See below.
This is the logic I use for updating a single row.
jdbcConn.setAutoCommit(false);
createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE)
updateResultSet = jdbcStmt.executeQuery("SELECT ... FROM ... WHERE
select same row FOR UPDATE");
Compare viewResultSet to updateResultSet to see if changes have been
made by another user.
Ask user what he wants to do. The following code updates the database
row and the
resultset row with the user's changes.
resultSet.updateString(colname,colvalue); // one or more column
updates go here
currentDatabaseSRS.resultSet.updateRow();
jdbcConn.commit();
jdbcConn.setAutoCommit(true);
viewResultSet.refreshRow(); // Refresh the original resultset. This can
be very slow.
This all seems to work well. The biggest question I have is the very last
statement, which refreshes the multi-row read-only resultset. Sometimes this
operation is very slow.
Specific question: why is refreshRow() slow, can I make it faster or should I
perhaps execute the original query again.
General question: any problems evident with this approach?
John
--
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] WAL file questions - how to relocate on Windows, how to replay after total loss, etc
I understand that WAL files can only be used with the database files in use at the time the WAL was written, therefore they are of no use to a database reconstructed from a pg_dump file. Let me see if I have this right. A - To protect against temporary server failure (such as a loss of power), just restart the server. The WAL files will ensure that the database is consistent and current as of the last transaction. B - To protect against permanent server failure (such as physical destruction of the server's hard drives), do a pg_dump backup regularly. The only data loss is data inserted or updated since the last pg_dump. Use pg_dumpall with the -g option to get the global information, use pg_dump with the custom output file format to get the data. C - To protect against permanent server failure with minimal loss of data, use the PITR strategy. D - To transfer a database to another server, use B, because the files are much smaller than an archive of the data directory. E - To upgrade the server software, you must use B. F - To allow selectively restoring data to a previous point in time (such as a table that was dropped by mistake), use PITR. Summary: Permanent loss of the server's hard drives is extremely unlikely, especially with raid, so option B is adequate for most applications. It's easier than PITR to set up and use, it's fast, and the backup files are small. It also serves several other purposes. PITR has disadvantages and costs that probably means it isn't worth the effort except for those that really need to guarantee every possible transaction or need the flexibility of going back in time. However, it would really be nice if the WAL files could be used to make the restored data more current, even if not everything can be restored. Are we certain that useful information can't be gleaned from them to apply changes made since the last pg_dump? John -- 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] WAL file questions - how to relocate on Windows, how to replay after total loss, etc
Douglas You can't blame me for being confused. Here's from section 23.3 of the 8.2 manual. "At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the clusters data directory. The log describes every change made to the databases data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by replaying the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files." That says that the database can be restored using the WAL files, and then it says that their existence makes another strategy for backing updatabases possoble. To me, that means that WAL files are useful even if not doing a physical backup of the actual database files. John On Thu, 28 Aug 2008 11:21:24 -0400, Douglas McNaught wrote: >On Thu, Aug 28, 2008 at 10:57 AM, John T. Dow <[EMAIL PROTECTED]> wrote: >> BACKGROUND INFO BEGINS >> >> Recently I had some questions about doing backups and received very helpful >> replies. I have now put together a BAT file to do a routine backup, using >> pg_dumpall with the -g option to get the roles, and pg_dump with the custom >> format to get all the data. >> >> I am now testing this process to make sure it is possible to recover data up >> to the last minute in the event of a catastrophic server failure. > >You have a fundamental misunderstanding of how this works. You can't >apply saved WAL files to a database restored from a dump. To use >PITR, you need to do a physical backup of the actual database files >(after calling the pg_start_backup() function), then when that is >done, call pg_stop_backup(). You then archive WAL files as the >database runs normally. > >To recover, you do a physical restore of the database files, then run >recovery using the archived WAL files. > >Read the documentation on PITR again--it's reasonably complete. > >-Doug > >-- >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] WAL file questions - how to relocate on Windows, how to replay after total loss, etc
BACKGROUND INFO BEGINS Recently I had some questions about doing backups and received very helpful replies. I have now put together a BAT file to do a routine backup, using pg_dumpall with the -g option to get the roles, and pg_dump with the custom format to get all the data. I am now testing this process to make sure it is possible to recover data up to the last minute in the event of a catastrophic server failure. I should mention that I'm running PostgreSQL 8.2 on a Windows computer (Win2K for testing) and that I am developing and testing this procedure because I do software development for clients using PG at my recommendation. They are small companies who don't use Unix. In fact, the PG server is likely to be one of the office workstations. To test, I ran my backup procedure, added a row to a table, then pressed reset on the workstation/server to simulate the catastrophe. When the computer booted, the server started automatically, so I shut it down. I then renamed the data folder to simulate complete loss of the computer, and I created a new cluster and database and did a restore of the roles and then the data. (My data had an invalid UTF8 character so, thanks to help given here, I extracted that table to plain text, ran iconv on it, and loaded the problem table.) So now I have everything back except that row I added after doing the backup. BACKGROUND INFO ENDS, QUESTIONS BEGIN How to replay WAL files: Now that I have a freshly created and reloaded database, how do I replay the WAL files from the pretend crashed server, now in a renamed folder? I understand I create a recover.conf file and restart the server. But I don't understand the process. I am to put a copy command in the file to copy wal files from the renamed folder, but how does the server know what files to copy? Is something else needed in the recover.conf file? By the way, when I restarted the server, the file was renamed to recover.done but the missing row didn't appear. On Windows, is it possible to relocate the wal files to another hard drive? Does pg_dump do a checkpoint? How do I make sure the wal files are not discarded before another backup is done? How to release wal files after the backup is done? The first two questions I could find no answer to. The others I admit I haven't researched very long as I was more interested in the first question. Thanks John -- 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] Dump/restore with bad data and large objects
Sorry, I missed that. Thanks again. Now to put this all into effect. John On Mon, 25 Aug 2008 14:25:12 -0700, Joshua Drake wrote: >On Mon, 25 Aug 2008 17:05:53 -0400 >"John T. Dow" <[EMAIL PROTECTED]> wrote: > >> Joshua >> >> Thank you very much for answering these various questions. >> >> I guess the compressed format is the best overall solution, except >> for roles. I find myself having a table with other information about >> users (application specific user type, etc) so perhaps the thing to >> do is record enough information there to reconstruct the roles should >> that become necessary. >> >> Can pg_dump dump roles to plain text? How does pg_dumpall do it, > >pg_dumpall -g will dump just roles via plain text. > >Joshua D. Drake >-- >The PostgreSQL Company since 1997: http://www.commandprompt.com/ >PostgreSQL Community Conference: http://www.postgresqlconference.org/ >United States PostgreSQL Association: http://www.postgresql.us/ >Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > > -- 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] Dump/restore with bad data and large objects
Joshua Thank you very much for answering these various questions. I guess the compressed format is the best overall solution, except for roles. I find myself having a table with other information about users (application specific user type, etc) so perhaps the thing to do is record enough information there to reconstruct the roles should that become necessary. Can pg_dump dump roles to plain text? How does pg_dumpall do it, doesn't it do everything via pg_dump? John On Mon, 25 Aug 2008 10:47:11 -0700, Joshua Drake wrote: >On Mon, 25 Aug 2008 13:37:13 -0400 >"John T. Dow" <[EMAIL PROTECTED]> wrote: > >> Joshua >> >> The TOC feature sounds good, as does converting a single table to >> plain text. >> >> But I can't find documentation for the TOC feature under pg_dump or >> pg_restore. I'm looking in postgresql-8.2.1-US.pdf. > >The commands you are looking for are: > >pg_restore -l to get the toc >pg_restore -L to use the toc > >If you open the resulting file from something like pg_restore -l > >foo.toc it is just a plain text list of objects to restore. > >I don't know how well it is documented but I am sure we would accept a >patch. > >> >> Neither could I see anything about converting a single table to a >> plain text dump. > >pg_restore allows you to do so. Something like: > >pg_restore foo.sqlc --file=foo.sql > >> >> Also, I stumbled across the statement that you can't restore large >> objects for a single table. Is that true? > >Large objects are stored in a central table called pg_largeobject, so >yes that would be accuarate. > > >> >> Another thing I couldn't find was how to dump roles using -Fc. >> > >You can't; that is a known and irritating limitation. > >Sincerely, > >Joshua D. Drake > > > >-- >The PostgreSQL Company since 1997: http://www.commandprompt.com/ >PostgreSQL Community Conference: http://www.postgresqlconference.org/ >United States PostgreSQL Association: http://www.postgresql.us/ >Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dump/restore with bad data and large objects
Joshua The TOC feature sounds good, as does converting a single table to plain text. But I can't find documentation for the TOC feature under pg_dump or pg_restore. I'm looking in postgresql-8.2.1-US.pdf. Neither could I see anything about converting a single table to a plain text dump. Also, I stumbled across the statement that you can't restore large objects for a single table. Is that true? Another thing I couldn't find was how to dump roles using -Fc. John On Mon, 25 Aug 2008 10:04:13 -0700, Joshua Drake wrote: >On Mon, 25 Aug 2008 10:21:54 -0400 >"John T. Dow" <[EMAIL PROTECTED]> wrote: > >> By "bad data", I mean a character that's not UTF8, such as hex 98. >> >> As far as I can tell, pg_dump is the tool to use. But it has >> serious drawbacks. >> >> If you dump in the custom format, the data is compressed (nice) and >> includes large objects (very nice). But, from my tests and the >> postings of others, if there is invalid data in a table, although >> PostgreSQL won't complain and pg_dump won't complain, pg_restore will >> strenuously object, rejecting all rows for that particular table (not >> nice at all). > >You can use the TOC feature of -Fc to remove restoring of that single >table. You can then convert that single table to a plain text dump and >clean the data. Then restore it separately. > >If you have foregin keys and indexes on the bad data table, don't >restore the keys until *after* you have done the above. > >Sincerely, > >Joshua D. Drake > >-- >The PostgreSQL Company since 1997: http://www.commandprompt.com/ >PostgreSQL Community Conference: http://www.postgresqlconference.org/ >United States PostgreSQL Association: http://www.postgresql.us/ >Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dump/restore with bad data and large objects
Tom My mistake in not realizing that 8.1 and later can dump large objects in the plain text format. I guess when searching for answers to a problem, the posted information doesn't always specify the version. So, sorry about that. But the plain text format still has serious problems in that the generated file is large for byte arrays and large objects, there is no ability to selectively restore a table, and bad data still isn't detected until you try to restore. Or did I miss something else? John PS: Yes, I know you can pipe the output from pg_dumpall into an archiver, but it's my understanding that the binary data is output in an inefficient format so even if zipped, the resulting file would be significantly larger than the custom format. On Mon, 25 Aug 2008 12:14:41 -0400, Tom Lane wrote: >"John T. Dow" <[EMAIL PROTECTED]> writes: >> If you dump in plain text format, you can at least inspect the dumped >> data and fix it manually or with iconv. But the plain text >> format doesn't support large objects (again, not nice). > >It does in 8.1 and later ... > >> Also, neither of these methods gets information such as the roles, > >Use pg_dumpall. > > 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] Dump/restore with bad data and large objects
By "bad data", I mean a character that's not UTF8, such as hex 98. As far as I can tell, pg_dump is the tool to use. But it has serious drawbacks. If you dump in the custom format, the data is compressed (nice) and includes large objects (very nice). But, from my tests and the postings of others, if there is invalid data in a table, although PostgreSQL won't complain and pg_dump won't complain, pg_restore will strenuously object, rejecting all rows for that particular table (not nice at all). If you dump in plain text format, you can at least inspect the dumped data and fix it manually or with iconv. But the plain text format doesn't support large objects (again, not nice). While byte arrays are supported, they result in very large dump files. Also, neither of these methods gets information such as the roles, so that has to be captured some other way if the database has to be rebuilt from scratch. Is my understanding incomplete or wrong? Is there no good solution? Why isn't there a dumpall that writes in compressed format and allows recovery from bad data? John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
