Re: [GENERAL] Getting cozy with weekly PITR

2008-09-22 Thread Bohdan Linda
pg_start_backup will flush old transactions, thus you have full DB backup. Unless you want them archived, no need to keep them Regards, Bohdan On Mon, Sep 22, 2008 at 09:41:47AM +0200, Joey K. wrote: During week 2, after the base backup, can we remove week 1's base and WAL files? -- Sent

[GENERAL] Weird pg_ctl behaviour via ssh

2008-07-31 Thread Bohdan Linda
Hello, I am fiddling around with pgpool-II and online recovery. Recovery depends on remote starting of a cluster. This means I need to ssh into a box, start clustern (with PITR recovery) and terminate that ssh connection. If I use the following script: ssh -T remote export

Re: [GENERAL] Weird pg_ctl behaviour via ssh

2008-07-31 Thread Bohdan Linda
On Thu, Jul 31, 2008 at 11:24:35AM +0200, Bohdan Linda wrote: /opt/postgres-8.3.3/bin/psql -h remote -p postgres psql: FATAL: the database system is starting up I am attaching additional info. The /dev/null is understable, but what I am worried is that if I query status of a server via

Re: [GENERAL] archive_timeout, checkpoint_timeout

2008-07-31 Thread Bohdan Linda
Hello, If you just want to ship segments to a standby server on a timely basis, the setting to tune should be archive_timeout, no? just curious, how would the stand-by DB process the segments? Regards, Bohdan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] ER diagram software

2008-07-22 Thread Bohdan Linda
I have done some research recently and found on acceptable: - DBdesigner4, which is depreceated and replaced by MySQL workbench. Is OSS, but no linux version yet. Also has clunky pgsql support - Aqua data studio (www.aquafold.com). It's java app which I am using for some time already.

Re: [GENERAL] nested view with outer joins - best practices

2008-06-10 Thread Bohdan Linda
On Mon, Jun 09, 2008 at 04:41:16PM +0200, Tom Lane wrote: 8.0 is incapable of reordering outer joins, which is likely the cause of your problem. Thank you, will try. Bohdan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] nested view with outer joins - best practices

2008-06-09 Thread Bohdan Linda
Hello, I have noted one very strange thing which I would like to discuss with you. I have noted that outer joins on nested views takes heavily longer than the inner ones. Example: REATE VIEW ports_view AS SELECT ports.pid, nodes.nname FROM ports JOIN nodes ON nodes.nid =

Re: [GENERAL] Vacuuming on heavily changed databases

2008-05-20 Thread Bohdan Linda
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

[GENERAL] Vacuuming on heavily changed databases

2008-05-19 Thread Bohdan Linda
Hello, I would like to ask an opinion on vacuuming general. Imagine situation that you have single table with 5 fields (one varchar). This table has during the day - cca 620 000 inserts - 0 updates - cca 620 000 deletes The table is vacuumed daily, but somehow after several months I got to size

Re: [GENERAL] Vacuuming on heavily changed databases

2008-05-19 Thread Bohdan Linda
On Mon, May 19, 2008 at 04:59:42PM +0200, Harald Armin Massa wrote: do not vacuum DAILY. set up autovacuum to run AT LEAST every minute. autovacuum will flag the deleted rows as to be reusable by next insert. Make sure to use 8.3.latest, it's much more easy to setup autovacuum then before.

[GENERAL] Password safe web application with postgre

2008-05-15 Thread Bohdan Linda
Hello, I have the following problem. A multiuser app has authentization and authorization done based on pgsql. The frontend is web based so it is stateless; it is connecting to database on every get/post. There is also a requirement that the user is transparently logged in for some period of

Re: [GENERAL] Password safe web application with postgre

2008-05-15 Thread Bohdan Linda
Hello, thank you everyone for the answers. I went through and I forgot add one thing. The web-app is frontend, thus basically PL/PGSQL launcher and all changes are audited, so common login is unwelcome. On Thu, May 15, 2008 at 05:40:49PM +0200, Steve Manes wrote: I keep the user's login

[GENERAL] PAM + Password authentication

2008-04-24 Thread Bohdan Linda
Hello, Can the PGSQL database be configured that it performs authentication against PAM and if fails the it tries against internal mechanizm? I would like to migrate to PAM, but I do not want to promote some users to system wide. Till now I am able to do one or the other way. Thank you, Bohdan

[GENERAL] auditing question

2007-07-11 Thread Bohdan . Linda
Hello, I am restricted to 8.0.7 version of postgresql and I am facing two problems when trying to build generic auditing function. I went through documentation http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html example: Example 35-3. A PL/pgSQL Trigger Procedure For Auditing

[GENERAL] Re:[GENERAL] auditing question - PARTIALY SOLVED

2007-07-11 Thread Bohdan . Linda
Hello, with help of Bricklen, I have found that the problem is the scope of call EXECUTE. When this is called it seems to be launched outside of the trigger's scope and OLD/NEW is not defined. (Manual suggests it) The remaining question is how to compare OLD.* and NEW.* in generic way for

Re: [GENERAL] High-availability

2007-06-04 Thread Bohdan Linda
On Mon, Jun 04, 2007 at 04:21:32PM +0200, Chander Ganesan wrote: I think you'll typically find that you can get one or the other - synchronous replication, or load balancing...but not both. On the other Hi, I am in very similar position, but I am more failover oriented. I am considering

[GENERAL] Little Offtopic: Database frontends

2006-01-30 Thread Bohdan Linda
Hello, I am sorry for this little offtopic, but recently I was looking for some matured DB frontend. I am licensed user of Aquafold Datastudio and before I will start to spend another money for next licenses, I would like to ask you if there are some similar frontends out there, but with three

[GENERAL] SHA1 authentication

2005-10-24 Thread Bohdan Linda
Hello all, I would like to use password authentication for pgsql users for remote backup purposes. I don't like the fact storing cleartext password on a system. From documentation, i have learnt that passwords can be encrypted by md5 and crypt methods. But we know, that md5 is rather weak

Re: [GENERAL] SHA1 authentication

2005-10-24 Thread Bohdan Linda
Thank you for the explanation. Cheers, Bohdan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the

Re: [GENERAL] Securing Postgres

2005-10-06 Thread Bohdan Linda
On Thu, Oct 06, 2005 at 11:57:32AM +0200, Martijn van Oosterhout wrote: This is the bit that's been bugging me this whole thread. Who owns the data? I've had to help people out with programs where they could type data in but couldn't get the reports they wanted out. Furtunatly, Access's access

[GENERAL] pgclient hostbased authentication

2005-09-21 Thread Bohdan Linda
Hello, may I ask, how(or which) ip is checked against pg_hba.conf IP entry in NAT environment? Could it be, that psql client packs IP address of the client into athentication data? Regards, Bohdan ---(end of broadcast)--- TIP 5: don't forget

Re: [GENERAL] pgclient hostbased authentication

2005-09-21 Thread Bohdan Linda
No. Why? Describe your problem. I have got response like bellow, when connecting to server in completely different network than 172.x.x.x -- org.postgresql.util.PSQLException: Connection rejected: FATAL: no pg_hba.conf entry for host 172.x.x.x, user XxXxXx, database yYyYyY, SSL off

[GENERAL] Access management for DB project.

2005-09-08 Thread Bohdan Linda
Hi, I started thinking of some security access management. Basically imagine this scenario according users: 1) Writer does only inserts to black hole. 2) Reader does only reports on inserted data, cannot modify or add anything 3) Maintainer can run a task on the data, but cannot read or

[GENERAL] Partial commit within the trasaction

2005-09-08 Thread Bohdan Linda
Hello, I have read that 7.x version had a model all or nothing in transactions. Thus I have upgraded to version 8 and would like to do the following: plpgsq code does time intensive data manipulation + database vacuuming. This data manipulation is splitted logically into several steps. After

[EMAIL PROTECTED]: Re: [GENERAL] Access management for DB project.]

2005-09-08 Thread Bohdan Linda
Tanks guys, That was exactly what I was looking for. B. ---BeginMessage--- On 8/9/05 11:08 am, Bohdan Linda [EMAIL PROTECTED] wrote: Hi, I started thinking of some security access management. Basically imagine this scenario according users: 1) Writer does only inserts to black hole

Re: [GENERAL] Partial commit within the trasaction

2005-09-08 Thread Bohdan Linda
On Thu, Sep 08, 2005 at 02:53:47PM +0200, Michael Fuhr wrote: One way would be to use contrib/dblink to open another connection to the database so the status messages could be inserted in a separate transaction. This could do the trick for logging, even writting of a package that would do all

Re: [GENERAL] Partial commit within the trasaction

2005-09-08 Thread Bohdan Linda
On Thu, Sep 08, 2005 at 04:35:51PM +0200, Michael Fuhr wrote: On Thu, Sep 08, 2005 at 03:39:50PM +0200, Bohdan Linda wrote: commit it now. You have to do some extra bookkeeping and you can't commit several prepared transactions atomically (as far as I know), but that's one way you could make

[GENERAL] REVOKE question

2005-09-02 Thread Bohdan Linda
Hello, I have encountered on (for me) wierd thing. When dropping an user, the database will not forget his permissions. After his recreation he has the original permissions. I use an approach of dropping all users when recreating the database environment and user recreation to avoid any

Re: [GENERAL] detection of VACUUM in progress

2005-08-31 Thread Bohdan Linda
On Tue, Aug 30, 2005 at 06:07:24PM +0200, Michael Fuhr wrote: tables, and a VACUUM might start or complete immediately after you issue the query but before you read the results). This method is therefore unreliable. I intend to do the VACUUM FULL during quiet hours, thus the chance of fitting

Re: [GENERAL] detection of VACUUM in progress

2005-08-31 Thread Bohdan Linda
From the postgresql manual http://www.postgresql.org/docs/8.0/interactive/maintenance.html : The standard form of VACUUM is best used with the goal of maintaining a fairly level steady-state usage of disk space. If you need to return disk space to the operating system you can use VACUUM

[GENERAL] detection of VACUUM in progress

2005-08-30 Thread Bohdan Linda
Hello, Is there any way how to detect running command VACUUM by reading pg_* tables? The idea is to detectect when table is not accessible due maintainance. The approach of explicitely setting a flag into status table is not very convenient, while I want to cover also non-systematic

Re: [GENERAL] Postgresql replication

2005-08-25 Thread Bohdan Linda
I would have a slight offtopic question, this is issue only of pgsql or there are some other db solutions which have good performance when doing this kind of replication across the world. Regards, Bohdan On Thu, Aug 25, 2005 at 09:01:49AM +0200, William Yu wrote: It provides pseudo relief if