Re: [GENERAL] after postgresql install my pc freezes/slows critically
zax zax wrote: I installed postgresql on my pc ... put it short: my whole pc slowed dramatically ... my pc became useless I tried uninstalling postgresql, freezing and slowing remained i tried system restore, freezing and slowing remained It's nothing to do with PostgreSQL then. It can't be. Installing PostgreSQL must trigger something else that's causing the problem. You don't say what's happening though - do you see a lot of disk activity, CPU activity or what? i figured there might be an issue with my firewall + antivirus software - Norton Internet Security 2004 - A sensible guess - Norton can interfere with all sorts of stuff. So after the format c: new windows, and BEFORE installing NIS i installed postgresql on a new absolutely virgin windows setup same freezing going slowly, freezing stuff:( The wipe + reinstall approach is understandable with Windows, but makes it difficult to find the real problem. Since you had problems straight away, that suggests one of three things: 1. Some subtle hardware fault (although that would cause crashes). 2. A bad version of Windows (could you be missing a correct driver?) 3. A virus that's surviving a re-format (unlikely). so I had to format c: again and put up windows again with a plan that I never would try to install postgresql again on this configuration as it would ruin my pc and I would need to install for 3 days again. I wouldn't plan to buy a complete new hardware just because of this as this machine is not that old and should be perfect my config is Intel P4 2,8G Asus P4P800 SE (i848P) 1 G PC3200 DDR 3 harddisk over 700 G space ATI Radeon HD2400 Win XP Sp2 with all updates NIS2k4 That's fine, although if you're running a lot of memory-hungry applications you could be causing the machine to run out of memory. With your clean install though, that's plenty of machine - lots of systems out there are smaller than that. So - we need to see exactly what's happening. You might find some of the sysinternals tools useful: http://technet.microsoft.com/en-us/sysinternals/default.aspx?PHPSESSID=9692e6... ProcessExplorer and FileMon can be handy to show what's going on. First though, turn to the built-in task-manager (ctrl+alt+del and select from the menu). That should give you a first guess. Make a note of what's happening both before and after you install PG and when it's running / stopped. I'm a classic user I can install anything but haven't got a clue about real programming or informatic skills, I don't understand postgresql either:) I can't figure out what part of it messes up with what part or either if it's software or hardware related, I never ever had any compatibility issues on this config in the 3-4 years i've been using this one You won't need any programming or PostgreSQL skills. You will need Windows administration skills, and to be honest you might find more expert help in a Windows forum rather than a PG forum. Lots of friendly people here, but they're database experts rather than Windows experts. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Download HTML documentation?
Hi, I'm trying to download the HTML version of the manual (8.3.1). It does not seem to be part of the Windows installer any more. Instead the a chm version is included. At http://www.postgresql.org/docs/manuals/ it says the HTML can be downloaded in the FTP area, but I cannot find any download that sounds like it contained the pre-built HTML manual. At least no directory or file with doc or manual in its name. What am I missing? I think it would be a nice idea to add a direkt link on the main documentation page for the HTML download (next to the PDF links) Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Download HTML documentation?
Thomas Kellerer wrote: Hi, I'm trying to download the HTML version of the manual (8.3.1). It does not seem to be part of the Windows installer any more. Instead the a chm version is included. At http://www.postgresql.org/docs/manuals/ it says the HTML can be downloaded in the FTP area, but I cannot find any download that sounds like it contained the pre-built HTML manual. At least no directory or file with doc or manual in its name. What am I missing? Did you look in the dev directory of the ftp? http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fdev%2Fdoc%2Fpostgres.tar.gz klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] Download HTML documentation?
Klint Gore, 20.05.2008 09:39: Thomas Kellerer wrote: Hi, I'm trying to download the HTML version of the manual (8.3.1). It does not seem to be part of the Windows installer any more. Instead the a chm version is included. At http://www.postgresql.org/docs/manuals/ it says the HTML can be downloaded in the FTP area, but I cannot find any download that sounds like it contained the pre-built HTML manual. At least no directory or file with doc or manual in its name. What am I missing? Did you look in the dev directory of the ftp? http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fdev%2Fdoc%2Fpostgres.tar.gz klint. Yes and no. I did see the dev directory, but assumed that the the manual for the stable release would not be stored in a directory called dev. The manual in there says 8.4 so I guess it is really bleeding edge and might contain information not applicable to 8.3. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Vacuuming on heavily changed databases
On Mon, May 19, 2008 at 08:38:09PM +0200, Scott Marlowe wrote: OK. Assuming that the 50G is mostly dead space, there are a few possibilities that could be biting you here, but the most likely one is that your Free Space Map settings aren't high enough to include all the rows that have been deleted since the last vacuum was run. If you can't take down the server to change those settings, then running vacuum more often will help. The autovacuum daemon is your friend. Even with the default non aggresive settings it comes with, it would have caught this long before now. I can bring down the DB for short time, but I am stuct with 8.0. Found that autovacuum is part of contrib, thus will try Thank you all for the opinion Regards, Bohdan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgre Deployment and Installation options
I am trying to distribute PostgreSQL with my applications but and need to simplify the installation procedure as much as possible for obvious reasons. Installing it to Windows environments only. Currently I prompt the end user to just start the postgre-8.3.msi install from an icon. This however requires them to enter a password of MY choosing (at minimum) and also to uncheck the Launch Stack Builder check box (which I don't want them to install). I know these steps are simple but am dealing with sub-simple people here and I want to avoid having to provide un-necessry support. Question is: Can the Installation/Setup procedure be configured so they don't have to do these steps, or even so that they don't see any dialog windows at all? Are there command line options or utilities to install it in a Windows environment that will serve this purpose? TIA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] i am looking for postgresql hosting server
i am planning to use postgresql server as a database backend of my project. currently, i have mysql5 database hosted on my VPS. my VPS only have 512MB ram and i already install many server inside. Apache2, Dovecot, postfix to name a few. I am looking for postgresql dedicated hosting. I just want to use the database but my application is on my own server (only the database in different server). Currently, most postgresql hosting bundled with website (apache2 etc) which is i don't need. Anyone know this type of service exists?
Re: [GENERAL] Postgre Deployment and Installation options
J Ottery wrote: I am trying to distribute PostgreSQL with my applications but and need to simplify the installation procedure as much as possible for obvious reasons. Installing it to Windows environments only. Currently I prompt the end user to just start the postgre-8.3.msi install from an icon. This however requires them to enter a password of MY choosing (at minimum) and also to uncheck the Launch Stack Builder check box (which I don't want them to install). I know these steps are simple but am dealing with sub-simple people here and I want to avoid having to provide un-necessry support. Question is: Can the Installation/Setup procedure be configured so they don't have to do these steps, or even so that they don't see any dialog windows at all? Are there command line options or utilities to install it in a Windows environment that will serve this purpose? http://pginstaller.projects.postgresql.org/silent.html -- Postgresql php tutorials http://www.designmagick.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] i am looking for postgresql hosting server
paragasu wrote: i am planning to use postgresql server as a database backend of my project. currently, i have mysql5 database hosted on my VPS. my VPS only have 512MB ram and i already install many server inside. Apache2, Dovecot, postfix to name a few. I am looking for postgresql dedicated hosting. I just want to use the database but my application is on my own server (only the database in different server). Currently, most postgresql hosting bundled with website (apache2 etc) which is i don't need. Anyone know this type of service exists? If you get another vps account, just ssh in and either disable or remove apache and then configure postgres as you need. -- Postgresql php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] escaping and quoting
Hello. My dbmail server using postgresql produces a lot of warnings about '\\' in strings. The particular string it is complaining about is escaped by EscapeBytea, and included in single quotes (not E''). Is it all right to set standard_compliant_strings to on? Greetings, Maarten Deprez -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] getting number of affected rows in dblink_exec
Hello,
In my db I have created a view that read data from a remote table
using dblink, I can modify the remote table using a couple of rules on
the view. My problem arise when I try to get the number of affected
rows since dblink_exec returns such information in a result set
instead of putting it in the return value.
Here is the relevant (simplified) sql code:
CREATE VIEW test_view AS
SELECT test.id, test.description
FROM dblink('dbname=xxx port=5432 host=xxx user=xxx
password=xxx'::text, 'select id, description'::text) test_view(id
integer, description character varying(100));
CREATE RULE test_view_upd AS
ON UPDATE TO test_view DO INSTEAD
SELECT dblink_exec('dbname=xxx port=5432 host=xxx user=xxx password=xxx',
'update test set description = '''::text || NEW.description || '''
where id = ' || old.id) AS dblink_exec;
there is a way to create this rule in a way compatible with the
standard SQL behaviour ?
Thanks in advance,
Stefano
--
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] i am looking for postgresql hosting server
At 4:36a -0400 on Tue, 20 May 2008, paragasu wrote: I am looking for postgresql dedicated hosting. Have you looked at the Postgres site? http://www.postgresql.org/support/professional_hosting If you're to be your own sysadmin/dba, you might also consider something like slicehost. I've heard a lot of hype for them recently. Kevin -- 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] Postgre Deployment and Installation options
J Ottery wrote: I am trying to distribute PostgreSQL with my applications but and need to simplify the installation procedure as much as possible for obvious reasons. Installing it to Windows environments only. Currently I prompt the end user to just start the postgre-8.3.msi install from an icon. This however requires them to enter a password of MY choosing (at minimum) and also to uncheck the Launch Stack Builder check box (which I don't want them to install). I know these steps are simple but am dealing with sub-simple people here and I want to avoid having to provide un-necessry support. Question is: Can the Installation/Setup procedure be configured so they don't have to do these steps, or even so that they don't see any dialog windows at all? Are there command line options or utilities to install it in a Windows environment that will serve this purpose? Did you read http://pginstaller.projects.postgresql.org/silent.html ? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] escaping and quoting
Maarten Deprez wrote: My dbmail server using postgresql produces a lot of warnings about '\\' in strings. The particular string it is complaining about is escaped by EscapeBytea, and included in single quotes (not E''). Is it all right to set standard_compliant_strings to on? Depends. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] best er modeling tool for postgreSQL
Hi, I'm evaluating different er modeling tools .. I have to support the same logical db on postgresql and sqlserver .. All tools I tried so far ignore the schema information .. all tables end up living in the default schema. What can you suggest ? thank you PLS: reply directly as well Best regards Enrico Sabbadin [EMAIL PROTECTED] Can you fly that thing ? Not Yet (The Matrix)
Re: [GENERAL] Schema migration tools?
Christophe, I agree with Dr. DeSoi that it may not always be possible to automate schema transformations. However, if you need to compare the metadata between two databases, the free, open-source SchemaCrawler for SQL Server tool will do this for you. You can take human-readable snapshots of the schema and data, for later comparison. Comparisons are done using a standard diff tool such as WinMerge. SchemaCrawler outputs details of your schema (tables, views, procedures, and more) in a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs) in the same plain- text formats. SchemaCrawler is available at SourceForge: http://schemacrawler.sourceforge.net/ Sualeh Fatehi -- 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] rounding problems
On May 14, 3:27 pm, [EMAIL PROTECTED] (Sam Mason) wrote: On Wed, May 14, 2008 at 02:08:47PM -0400, Justin wrote: Sam Mason wrote: What doesfoxprouse for storing numbers? or is it just that you never pushed it hard enough for the abstractions to show through. I know i pushed it. Foxpro for the most has only 4 basic data types Numeric (similar to Posgresql numeric), Boolean, Date, Text aka (string) Thefoxprotables supported far more data types but when every it was dumped to variable it acted like one of the 4. I really meant how much did you check the results, or did you accept that they were correct? Foxprodid not suffer floating point math errors. I normally used 8 to 10 points precision. Foxprowas limited to 15 points of precision period. No more and no less, once you hit that was it. 15 places seems very similar to what a 64bit IEEE floating point number will give you, i.e. a double in C/C++. My problem is we calculate resistance of parts in aFoxproapp that we want to move because we want to bring all the custom apps into one framework and single database. Take this calculation (0.05/3* 1.0025) which is used to calculate parts resistance and Tolerance. (its Ohms Law) The value returned from C++ = .016708 which is wrong it should be .0167418. We just shrank the tolerance on the part we make Why are you so sure about theFoxProresult? I've just checked a few calculators and get results consistent with your C++ version. Justin C: 0.016708 JFoxPro: 0.0167418 My C: 0.01670833 bc[1]: 0.01670832 PG[2]: 0.016708336675 Google[3]: 0.016708 (actually gives 1.6708e-6) Both bc and Postgres use their own code (i.e. not the CPU's FPU) to do the math, and as they all agree I'm thinkingFoxProis incorrect! Next I tried doing it accurately (in Haskell if it makes any difference) and get an answer of 401/24000 out, which would agree with everything butFoxPro. If I calculate the ratio back out forFoxProI get 401/239520242 which is a little way out. The Documentation from MS says 15 points of precision but the result say otherwise. The docs for what?FoxProor their C compiler? If you meanFoxPro, I think this is another case of MS screwing up. I'm glad You and others are taking the time to explain to me the odd results before i get into redoing that application. Welcome to the PG community, lots of people to get interested in lots of things! Why oh Why did MS killFoxpro. :'( I understood it, knew its quirks and it worked very well with Postgresql Are you sure you want to stay with it if its answers are wrong? Sam * This is fun, at 0400 AM. I enjoy reading Experts having serious fun! VFP 6.0, using my defaults ? (0.05/3* 1.00250) displays 0l.0167083000 SET DECIMALS TO 15 ? ((0.05/3)* 1.0025) displays 0.0167083 and a frivolous example: SET DECIMALS TO 18 ? ((0.05/3.)* 1.0025) displays 0.0167083000 Anybody tried to reckon this math the way we used to do it with a Slide-Rule ??? (In VFP of course) glene77is -- 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] i am looking for postgresql hosting server
On Tue, May 20, 2008 at 7:11 PM, Kevin Hunter [EMAIL PROTECTED] wrote: At 4:36a -0400 on Tue, 20 May 2008, paragasu wrote: I am looking for postgresql dedicated hosting. Have you looked at the Postgres site? http://www.postgresql.org/support/professional_hosting If you're to be your own sysadmin/dba, you might also consider something like slicehost. I've heard a lot of hype for them recently. Kevin i did see the list. i will look into slicehost.. thanks
[GENERAL] Server Configuration
Hi, I am currently trying to tweak Postgresql 8.1, to improve the overall performance of the database. I have read over the following page/artical http://www.powerpostgresql.com/PerfList/, however at the moment (not for much longer hopefully) my hands aree tied when it comes to altering the kernel parameters, and thus allocating more shared buffers. I have read over the rest of the artical and adjusted some of the other values mentioned. The values I have altered are: work_mem = 33554# min 64, size in KB maintenance_work_mem = 33554# min 1024, size in KB max_fsm_pages = 10 # min max_fsm_relations*16, 6 bytes each vacuum_cost_delay = 50 # 0-1000 milliseconds wal_buffers = 64 # min 4, 8KB each commit_delay = 0# range 0-10, in microseconds commit_siblings = 50 # range 1-1000 effective_cache_size = 3000 # typically 8KB each autovacuum = on# enable autovacuum subprocess? autovacuum_naptime = 30 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 400# min # of tuple updates before vacuum autovacuum_analyze_threshold = 100# min # of tuple updates before analyze autovacuum_vacuum_scale_factor = 0.4# fraction of rel size before vacuum autovacuum_analyze_scale_factor = 0.2# fraction of rel size before analyze Is it ok to have these settings with increasing the amount of shared buffers ? Thanks for any advice, Andy
Re: [GENERAL] ranked subqueries vs distinct question
On Wed, May 14, 2008 at 10:36:31AM -0500, David McNett wrote: If the view is limited as you describe, don't use is. Ah, of course, that was the best advice amongst all :-) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] rounding problems
glene77is wrote: On May 14, 3:27 pm, [EMAIL PROTECTED] (Sam Mason) wrote: On Wed, May 14, 2008 at 02:08:47PM -0400, Justin wrote: Sam Mason wrote: What doesfoxprouse for storing numbers? or is it just that you never pushed it hard enough for the abstractions to show through. I know i pushed it. Foxpro for the most has only 4 basic data types Numeric (similar to Posgresql numeric), Boolean, Date, Text aka (string) Thefoxprotables supported far more data types but when every it was dumped to variable it acted like one of the 4. I really meant how much did you check the results, or did you accept that they were correct? Foxprodid not suffer floating point math errors. I normally used 8 to 10 points precision. Foxprowas limited to 15 points of precision period. No more and no less, once you hit that was it. 15 places seems very similar to what a 64bit IEEE floating point number will give you, i.e. a double in C/C++. My problem is we calculate resistance of parts in aFoxproapp that we want to move because we want to bring all the custom apps into one framework and single database. Take this calculation (0.05/3* 1.0025) which is used to calculate parts resistance and Tolerance. (its Ohms Law) The value returned from C++ = .016708 which is wrong it should be .0167418. We just shrank the tolerance on the part we make Why are you so sure about theFoxProresult? I've just checked a few calculators and get results consistent with your C++ version. Justin C: 0.016708 JFoxPro: 0.0167418 My C: 0.01670833 bc[1]: 0.01670832 PG[2]: 0.016708336675 Google[3]: 0.016708 (actually gives 1.6708e-6) Both bc and Postgres use their own code (i.e. not the CPU's FPU) to do the math, and as they all agree I'm thinkingFoxProis incorrect! Next I tried doing it accurately (in Haskell if it makes any difference) and get an answer of 401/24000 out, which would agree with everything butFoxPro. If I calculate the ratio back out forFoxProI get 401/239520242 which is a little way out. The Documentation from MS says 15 points of precision but the result say otherwise. The docs for what?FoxProor their C compiler? If you meanFoxPro, I think this is another case of MS screwing up. I'm glad You and others are taking the time to explain to me the odd results before i get into redoing that application. Welcome to the PG community, lots of people to get interested in lots of things! Why oh Why did MS killFoxpro. :'( I understood it, knew its quirks and it worked very well with Postgresql Are you sure you want to stay with it if its answers are wrong? Sam * This is fun, at 0400 AM. I enjoy reading Experts having serious fun! VFP 6.0, using my defaults ? (0.05/3* 1.00250) displays 0l.0167083000 SET DECIMALS TO 15 ? ((0.05/3)* 1.0025) displays 0.0167083 and a frivolous example: SET DECIMALS TO 18 ? ((0.05/3.)* 1.0025) displays 0.0167083000 Foxpro always stops at 15 decimals points, Even though some of the documentation says 20 and 22 points of precision depending on the version. I have versions 5 to 9 Anybody tried to reckon this math the way we used to do it with a Slide-Rule ??? (In VFP of course) A slide what??. I have never touched one or seen a slide rule in real life, just pretty pictures :-) glene77is
[Fwd: Re: [GENERAL] i am looking for postgresql hosting server]
Perhaps meant for the list. Original Message Subject: Re: [GENERAL] i am looking for postgresql hosting server Date: Tue, 20 May 2008 09:03:37 -0500 From: Vitaliyi [EMAIL PROTECTED] To: [EMAIL PROTECTED] References: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] On Tue, May 20, 2008 at 8:58 AM, paragasu [EMAIL PROTECTED] wrote: On Tue, May 20, 2008 at 7:11 PM, Kevin Hunter [EMAIL PROTECTED] wrote: At 4:36a -0400 on Tue, 20 May 2008, paragasu wrote: I am looking for postgresql dedicated hosting. Have you looked at the Postgres site? http://www.postgresql.org/support/professional_hosting If you're to be your own sysadmin/dba, you might also consider something like slicehost. I've heard a lot of hype for them recently. Kevin http://0x2a-dc.com/ this is my dc -- 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] escaping and quoting
I wrote: My dbmail server using postgresql produces a lot of warnings about '\\' in strings. The particular string it is complaining about is escaped by EscapeBytea, and included in single quotes (not E''). Is it all right to set standard_compliant_strings to on? Laurenz Albe wrote: Depends. Okay, what do you need to know? It can be any string, a part of an email message. Should strings escaped by EscapeBytea be included in '', or E'' in the SQL command, to avoid the warning? Greetings, Maarten -- 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] DB page cache/query performance
From: Greg Smith [mailto:[EMAIL PROTECTED] Sent: Monday, May 19, 2008 9:03 PM So, yes, in 8.3 it's possible that you can have sequential scans of large tables or the VACUUM data pass through the buffer cache, but not remain in it afterwards. I didn't think George would ever run into this in the specific example he asked about because of (1). This behavior only kicks in if you're scanning a table large relative to the total shared buffer cache and that didn't seem like an issue in his case. Correct -- the tables in this example were tiny, shared buffers are large, and, in any case, I am still on 8.1... George -- 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] psql: FATAL: Ident authentication failed for user postgres
Hi Luigi, It is Linux. I am trying to login as postgres but as a user not in the database. This is because I will be creating a web front end, and various users, not registered in the database, will need to access the database. Thus, as postgres I can log in fine: bash-3.1$ psql kermit -U postgres Welcome to psql 8.1.11, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit kermit=# But as myself, I can't. /home/djoo[8:25am]$ %psql kermit -U postgres psql: FATAL: Ident authentication failed for user postgres This is pretty much the same setup I had with another company, so I am confused why I can't access. Is there another file that I need to alter besides the pg_hba.conf file? Thanks for your help, Dan From: Luigi Castro Cardeles [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 20, 2008 5:53 AM To: Dan Joo Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed for user postgres hi, what's your machine configuration? Linux, Mac Os? if you are using mac, maybe you have a problem with identd. what user are you trying to logon? comand: psql -U user-name -d database-name []'s -- Luigi Castro Cardeles
Re: [GENERAL] Postgresql MVCC, Read Committed Isolation Level and taking snapshot
On Sun, May 18, 2008 at 02:04:56PM +0200, Ivan Sergio Borgonovo wrote: What is the effect of having nested functions all declared SERIALIZABLE? You can't do that. You have to declare SERIALIZABLE right at the beginning of the transaction, or it won't work (and, IIRC, it throws an error). That means _before_ you call the first function (since calling the function is then the first statement in the transaction, before serializable). A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] psql: FATAL: Ident authentication failed for user postgres
On Tue, May 20, 2008 at 08:24:30AM -0700, Dan Joo wrote: /home/djoo[8:25am]$ %psql kermit -U postgres psql: FATAL: Ident authentication failed for user postgres confused why I can't access. Is there another file that I need to alter besides the pg_hba.conf file? That depends on the content of your pg_hba.conf file. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] psql: FATAL: Ident authentication failed for userpostgres
Hi Karsten, The only non-commented lines are: [EMAIL PROTECTED] data]# cat pg_hba.conf | grep -v '^#' local all all trust hostall all 127.0.0.1/32 trust hostall all ::1/128 trust Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Karsten Hilbert Sent: Tuesday, May 20, 2008 8:51 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed for userpostgres On Tue, May 20, 2008 at 08:24:30AM -0700, Dan Joo wrote: /home/djoo[8:25am]$ %psql kermit -U postgres psql: FATAL: Ident authentication failed for user postgres confused why I can't access. Is there another file that I need to alter besides the pg_hba.conf file? That depends on the content of your pg_hba.conf file. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] psql: FATAL: Ident authentication failed for userpostgres
On Tue, 2008-05-20 at 08:50 -0700, Dan Joo wrote: Hi Karsten, The only non-commented lines are: [EMAIL PROTECTED] data]# cat pg_hba.conf | grep -v '^#' local all all trust hostall all 127.0.0.1/32 trust hostall all ::1/128 trust Dan If that is your production pg_hba.conf you have either failed to reload the database or you aren't actually looking at the right pg_hba.conf. Sincerely, Joshua D. Drake -- 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] psql: FATAL: Ident authentication failed for userpostgres
On Tue, May 20, 2008 at 08:50:16AM -0700, Dan Joo wrote: The only non-commented lines are: [EMAIL PROTECTED] data]# cat pg_hba.conf | grep -v '^#' local all all trust hostall all 127.0.0.1/32 trust hostall all ::1/128 trust Well, neither of which allows IDENT based authentication. This doesn't explain, however, why it's attempting IDENT in the first place. It really *should* just work due to the first line. Is this the pg_hba.conf that's actually being used ? Perhaps there's another one lying in another cluster's directory ? What happens if you rename it so PG cannot find it anymore - does PG complain about the missing file ? [mailto:[EMAIL PROTECTED] On Behalf Of Karsten Hilbert Interesting. Are my mails being screened ? confused why I can't access. Is there another file that I need to alter besides the pg_hba.conf file? That depends on the content of your pg_hba.conf file. Assuming the above really IS the file in use the answer is No. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to modify a view
Hello, The DB I am using has views. I need to modify the view adding one field to the QUERY that build the query. When I edit the VIEW and add the field, I get the error that tells me "It's not possible to change columns number in a view" So, do I need to remove the complete VIEW and create a new one ? Or there is other way to modify the view. ? Thanks in advance, --
Re: [GENERAL] psql: FATAL: Ident authentication failed for userpostgres
On Tue, May 20, 2008 at 09:17:03AM -0700, Joshua D. Drake wrote: The only non-commented lines are: [EMAIL PROTECTED] data]# cat pg_hba.conf | grep -v '^#' local all all trust hostall all 127.0.0.1/32 trust hostall all ::1/128 trust Dan If that is your production pg_hba.conf you have either failed to reload the database Make the database engine reload its config file, that is, I assume. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] psql: FATAL: Ident authentication failed foruserpostgres
Great suggestion! I renamed it to pg_hba.conf_tmp, and I can still log into PG. There is another linux box in the network and changed the title of the pg_hba.conf file as well, and still allows me to log in to PG. Is there a command that I can use to find out which file PG is looking into for the settings in pg_hba.conf? Could it be that the config file is not even titled pg_hba.conf? Just so that you know, I restarted postgres after changing the file names in both machines. [EMAIL PROTECTED] djoo]# /etc/init.d/postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Karsten Hilbert Sent: Tuesday, May 20, 2008 9:27 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed foruserpostgres On Tue, May 20, 2008 at 08:50:16AM -0700, Dan Joo wrote: The only non-commented lines are: [EMAIL PROTECTED] data]# cat pg_hba.conf | grep -v '^#' local all all trust hostall all 127.0.0.1/32 trust hostall all ::1/128 trust Well, neither of which allows IDENT based authentication. This doesn't explain, however, why it's attempting IDENT in the first place. It really *should* just work due to the first line. Is this the pg_hba.conf that's actually being used ? Perhaps there's another one lying in another cluster's directory ? What happens if you rename it so PG cannot find it anymore - does PG complain about the missing file ? [mailto:[EMAIL PROTECTED] On Behalf Of Karsten Hilbert Interesting. Are my mails being screened ? confused why I can't access. Is there another file that I need to alter besides the pg_hba.conf file? That depends on the content of your pg_hba.conf file. Assuming the above really IS the file in use the answer is No. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] how to modify a view
J. Manuel Velasco - UBILIBET wrote: Hello, The DB I am using has views. I need to modify the view adding one field to the QUERY that build the query. When I edit the VIEW and add the field, I get the error that tells me It's not possible to change columns number in a view So, do I need to remove the complete VIEW and create a new one ? Or there is other way to modify the view. ?¿ Thanks in advance, -- Yes - The only way to do it is DROP VIEW... then CREATE VIEW... - the catch is any other views that depend on the view you wish to drop. manual quote You can only replace a view with a new query that generates the identical set of columns (i.e., same column names and data types). /manual quote -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] psql: FATAL: Ident authentication failed foruserpostgres
On Tue, May 20, 2008 at 09:47:55AM -0700, Dan Joo wrote: Is there a command that I can use to find out which file PG is looking into for the settings in pg_hba.conf? Could it be that the config file is not even titled pg_hba.conf? http://www.postgresql.org/docs/8.3/static/runtime-config-file-locations.html Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server Configuration
On Tue, May 20, 2008 at 8:14 AM, Andy Dale [EMAIL PROTECTED] wrote: Hi, I am currently trying to tweak Postgresql 8.1, to improve the overall performance of the database. I have read over the following page/artical http://www.powerpostgresql.com/PerfList/, however at the moment (not for much longer hopefully) my hands aree tied when it comes to altering the kernel parameters, and thus allocating more shared buffers. I have read over the rest of the artical and adjusted some of the other values mentioned. The values I have altered are: work_mem = 33554# min 64, size in KB Depending on how many connections you're handling and how much memory you have, this might be a little large, but it's not terrible. Make sure you aren't running your machine low on spare memory, as this can cause the machine to start swapping and make it run slower. maintenance_work_mem = 33554# min 1024, size in KB max_fsm_pages = 10 # min max_fsm_relations*16, 6 bytes each vacuum_cost_delay = 50 # 0-1000 milliseconds Maybe a little high. most people find that 10 is just fine to keep vacuum from slamming your I/O bandwidth. wal_buffers = 64 # min 4, 8KB each commit_delay = 0# range 0-10, in microseconds commit_siblings = 50 # range 1-1000 Setting a short commit delay may allow for more siblings to get committed together. effective_cache_size = 3000 # typically 8KB each autovacuum = on# enable autovacuum subprocess? autovacuum_naptime = 30 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 400# min # of tuple updates before vacuum autovacuum_analyze_threshold = 100# min # of tuple updates before analyze autovacuum_vacuum_scale_factor = 0.4# fraction of rel size before vacuum autovacuum_analyze_scale_factor = 0.2# fraction of rel size before analyze Is it ok to have these settings with increasing the amount of shared buffers ? Sure. What's your max connections set to? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] intermittent problems with ident authentication
I have some scripts that connect to my 8.2 postgres server using ident authentication, and maybe ~10% of the time, authentication fails like so: 2008-05-20 00:22:54 UTC LOG: invalidly formatted response from Ident server: 49205 , 5432 : ERROR :dba xinetd's logs show normal auth activity at these times. This is on CentOS 5.1. Has anybody else seen anything like this? Or even better, figured out what was going on? -- 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] Postgresql MVCC, Read Committed Isolation Level and taking snapshot
On Fri, 16 May 2008 09:55:56 -0400 Andrew Sullivan [EMAIL PROTECTED] wrote: On Fri, May 16, 2008 at 09:06:11AM +0200, Ivan Sergio Borgonovo wrote: Is BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE what I'm looking for? Yes. OK... What if I want to avoid the rollback problem and avoid to deal with the ERROR: could not serialize access due to concurrent update I don't have to update/insert rows in the same tables I'm examining. I just would like to have a coherent snapshot of some tables. It should be something like: 1) check is some conditions are met with a bunch of selects and computation on returned rows 2) if everything is OK copy a slice of the snapshot in *other* tables. It would be something like select into _a, _b c, d from t1 join t2... select into _c, _d c, d from t3 join t4... if(...) ... if(...) ... if(everything is fine) insert into t10 (a,b,h,i,l,m) select _a, _b, h, i, l, m from t1 join t2... I'm not interested into the fact that _a, _b, _c, _d, t1 or t2 may be different when other transaction commit... I'm just interested in saving in t10 a coherent state. Since I'm going to save all the collected data into another table other than the one that may be updated by another transaction am I going to incur in the above ERROR: could not serialize...? Can I reach my target of getting a coherent snapshot with a set of for share? Is there a set of examples that could highlight the differences of for share/update and serializable with their pitfalls? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] psql: FATAL: Ident authentication failedforuserpostgres
Thanks Karsten! Yes, there was another config file in another directory! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Karsten Hilbert Sent: Tuesday, May 20, 2008 10:36 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] psql: FATAL: Ident authentication failedforuserpostgres On Tue, May 20, 2008 at 09:47:55AM -0700, Dan Joo wrote: Is there a command that I can use to find out which file PG is looking into for the settings in pg_hba.conf? Could it be that the config file is not even titled pg_hba.conf? http://www.postgresql.org/docs/8.3/static/runtime-config-file-locations. html Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Corrupted database's files (linux RAID5 + PostgreSQL 8.3.0)
Hi, Today one of the disk was marked as as failed and now some files are corrupted. I've decided to copy the pgsqldata directory and try to fix PG_VERSION (see below for information - what PostgreSQL don't like) files ... and see if the database will come up. During copying files and etc. I'll be open for any other idea how to deal with the problem ;) PostgreSQL's log offer me to run initdb (HINT message from LOG file) - what will happen if then I try to copy the rest ot the structure into the newly created database cluster ? linux (Slackware 12.0.0), software RAID5 (partition based) + PostgreSQL 8.3.0: Here's what happen (from dmesg): --- # uname -a Linux xeonito 2.6.21.5 #3 SMP Tue Oct 2 16:20:48 EEST 2007 i686 Intel(R) Xeon(R) CPU E5335 @ 2.00GHz GenuineIntel GNU/Linux --- # dmesg sd 0:0:3:0: SCSI error: return code = 0x0802 sdd: Current: sense key=0x4 ASC=0x44 ASCQ=0x0 Info fld=0x0 end_request: I/O error, dev sdd, sector 159620863 sd 0:0:3:0: SCSI error: return code = 0x0802 sdd: Current: sense key=0x4 ASC=0x44 ASCQ=0x0 Info fld=0x0 end_request: I/O error, dev sdd, sector 159617119 raid5: Disk failure on sdd1, disabling device. Operation continuing on 4 devices .. RAID5 conf printout: --- rd:5 wd:4 disk 0, o:1, dev:sdb1 disk 1, o:1, dev:sdc1 disk 2, o:0, dev:sdd1 disk 3, o:1, dev:sde1 disk 4, o:1, dev:sdf1 RAID5 conf printout: --- rd:5 wd:4 disk 0, o:1, dev:sdb1 disk 1, o:1, dev:sdc1 disk 3, o:1, dev:sde1 disk 4, o:1, dev:sdf1 --- # cat /proc/mdstat Personalities : [linear] [raid0] [raid1] [raid10] [raid6] [raid5] [raid4] [multipath] [faulty] md1 : active raid5 sdb1[0] sdf1[4] sde1[3] sdd1[5](F) sdc1[1] 585924608 blocks level 5, 8192k chunk, algorithm 2 [5/4] [UU_UU] md0 : active raid5 sdb2[0] sdf2[4] sde2[3] sdd2[5](F) sdc2[1] 390053888 blocks level 5, 1024k chunk, algorithm 2 [5/4] [UU_UU] unused devices: none --- And here's what the partitions look like: # fdisk -l /dev/sdb Disk /dev/sdb: 249.8 GB, 249865175040 bytes 255 heads, 63 sectors/track, 30377 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Device Boot Start End Blocks Id System /dev/sdb1 1 18237 146488671 83 Linux /dev/sdb2 18238 3037797514550 83 Linux --- Kernel parameters: echo 42 /proc/sys/kernel/shmmax echo 42 /proc/sys/kernel/shmall sysctl -w vm.overcommit_memory=2 echo 8192 /sys/block/md0/md/stripe_cache_size echo 8192 /sys/block/md1/md/stripe_cache_size --- Both md0 and md1 are used from PostgreSQL - initially it was not design to use the whole disk sdb-sdf, but due to size requirement I join also the other unused space to be used by PostgreSQL. And here's the Postgre's log (FATAL message is coming when I try to connect to the database, of course this is the case for the most interesting database ... some other small databases are working fine): LOG: received smart shutdown request LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down LOG: could not create IPv6 socket: Address family not supported by protocol LOG: database system was shut down at 2008-05-20 17:54:17 EEST LOG: autovacuum launcher started LOG: database system is ready to accept connections FATAL: base/16399 is not a valid data directory DETAIL: File base/16399/PG_VERSION does not contain valid data. HINT: You might need to initdb. Of course base/16399/PG_VERSION contains something strange not the version information: # cat base/16399/PG_VERSION X --- -- 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] how to modify a view
On Wed, 2008-05-21 at 02:43 +0930, Shane Ambler wrote: Yes - The only way to do it is DROP VIEW... then CREATE VIEW... - the catch is any other views that depend on the view you wish to drop. Tip: It's sometimes useful to rename an existing view or table to deprecate it. (I use names like _someview_v_20080520.) When you rename a table or view, the definition of any views which depend on them will be updated appropriately. This is particularly handy when you have several layers of views and don't want to break current behavior. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
[GENERAL] Short-circuiting FK check for a newly-added field
I need to add a field to a fairly large table. In the same alter statement I'd like to add a FK constraint on that new field. Is there any way to avoid the check of the table that the database is doing right now? The check is pointless because the newly added field is nothing but NULLs. This is version 8.1.mumble. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] best er modeling tool for postgreSQL
I've played about with DIA and the transform tool I can't remember the name of right no that takes DIA input and creates DDL commands. not bad. But I keep coming back to a big white board... :) On Tue, May 20, 2008 at 7:13 AM, Enrico SABBADIN [EMAIL PROTECTED] wrote: Hi, I'm evaluating different er modeling tools .. I have to support the same logical db on postgresql and sqlserver .. All tools I tried so far ignore the schema information .. all tables end up living in the default schema. What can you suggest ? thank you PLS: reply directly as well Best regards Enrico Sabbadin [EMAIL PROTECTED] Can you fly that thing ? Not Yet (The Matrix) -- 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] Postgresql MVCC, Read Committed Isolation Level and taking snapshot
On Tue, May 20, 2008 at 08:56:41PM +0200, Ivan Sergio Borgonovo wrote: I just would like to have a coherent snapshot of some tables. If you have a multi-statement transaction, then if you are in READ COMMITTED you can see changes, and if you are in SERIALIZABLE you can't. You can't of course see changes in the same statement as it were -- that's not meaningful (because either they committed before you saw the row or after). So, 1) check is some conditions are met with a bunch of selects and computation on returned rows 2) if everything is OK copy a slice of the snapshot in *other* tables. This is more than one statement. So you will be able to see changes in between those statements. If you don't care about that, then your approach will work. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] pgxs question - linking c-functions to external libraries
A typo on my part in my posting. My Makefile has: SHLIB_LINK = -lgsl -lgslcblas Regards John Quoting Martijn van Oosterhout [EMAIL PROTECTED]: On Mon, May 19, 2008 at 06:23:03PM +0100, [EMAIL PROTECTED] wrote: Thanks for the reply. My GSL libraries, 'libgsl' and 'libgslcblas', are in the I saw that and then in your previous message: PG_CPPFLAGS = -lgsl -lgslcblas PG_LIBS = -lgsl -gslcblas SHLIB_LINK = -lgsl -gslcblas Shouldn't those -gslcblas be -lgslcblas? Have a ncie day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. -- 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] pg_standby stuck on a wal file size 16MB
as Greg pointed out: my use of rsync --remove-sent-files option had
contributed to a short sized wal log file on standby.
changing master's postgres crontab to the following helped to resolve
the issue:
# ship logs to standby:
*/2 * * * * rsync -aq /wal_archive_local/ 10.10.10.12::wal_archive/
# remove files older then remove_check file mtime
*/5 * * * * find /wal_archive_local/ ! -newer
/wal_archive_local/remove_check -exec rm -f {} \; touch
/wal_archive_local/remove_check
Thank you!
V.
Greg Smith wrote:
On Sat, 17 May 2008, Ioannis Tambouras wrote:
The archive command tests if the wal segment exists and is a file,
but it does not check if the file is still being written.
That's because it doesn't have to; the archive command doesn't get
called until the writing is done.
I don't have sources of pg_standby near me, but I remember in the
C code checks for complete segment sizes.
That's on the receiving side, to make sure it's not trying to process
files that haven't finished copying to the standby yet. You don't
have to do any of that in the archive_command.
Anyway, back to the original question:
archive_command = 'test ! -f /usr/local/wal_archive_local/%f cp %p
/usr/local/wal_archive_local/%f'
archive files are then moved on master to standby every other minute:
rsync -aq --remove-sent-files /usr/local/wal_archive_local/
slave::wal_archive/
I don't see any mechanism here to keep rsync from copying over partial
files to the standby before they've finished copying to the
wal_archive_local directory. That's my guess for where the small
files are coming from, rsync before the cp is done. If you're going
to buffer in a transfer directory, you need some sort of test or
locking to make sure the file is complete with exactly 16MB before it
gets rsync'd over. I suspect no amount of poking at the standby will
root out the issue because it's happening on the primary.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
--
Vladimir (Vlad) Kosilov
Senior Systems Administrator
Contigo Systems Inc.
604.683.3106 (phone)
604.648.9886 (fax)
[EMAIL PROTECTED]
www.contigo.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] Postgresql MVCC, Read Committed Isolation Level and taking snapshot
On Tue, 2008-05-20 at 17:04 -0400, Andrew Sullivan wrote: On Tue, May 20, 2008 at 08:56:41PM +0200, Ivan Sergio Borgonovo wrote: I just would like to have a coherent snapshot of some tables. If you have a multi-statement transaction, then if you are in READ COMMITTED you can see changes, and if you are in SERIALIZABLE you can't. Unless the changes were made by yourself in the top-level transaction or an un-aborted subtransaction, in which case they are visible. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] pg_standby stuck on a wal file size 16MB
On Wed, May 21, 2008 at 4:14 AM, Vlad Kosilov [EMAIL PROTECTED] wrote:
as Greg pointed out: my use of rsync --remove-sent-files option had
contributed to a short sized wal log file on standby.
changing master's postgres crontab to the following helped to resolve the
issue:
# ship logs to standby:
*/2 * * * * rsync -aq /wal_archive_local/ 10.10.10.12::wal_archive/
# remove files older then remove_check file mtime
*/5 * * * * find /wal_archive_local/ ! -newer
/wal_archive_local/remove_check -exec rm -f {} \; touch
/wal_archive_local/remove_check
Hmmm, nice trick.
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
