Re: [ADMIN] [GENERAL] Streaming Replication limitations
On Wed, Apr 13, 2011 at 11:23:24PM +0530, raghu ram wrote: > Hi, > > Is there any limitations to configure streaming replication between > different operating systems i.e solaris 64 bit to RHEL 64 bit. I personally wouldn't be willing to use anything except identical binaries for the back end, and those two platforms are binary incompatible. The manual actually warns about this. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] [GENERAL] FW: backup using pg_dump postgreSQL 8.3.8
On Wed, Mar 09, 2011 at 10:31:56AM -0500, Tom Lane wrote: > Andrew Sullivan writes: > > On Wed, Mar 09, 2011 at 03:58:20PM +0200, Sandy Test wrote: > >> Unfortunately, even with the pg_hba.conf fix of adding host postgres ... > >> trust, > If it is asking for a password, and password authentication is what's > supposed to be used, then pg_hba is not where the problem is. Yes, but I think the OP was saying that the setting was moved to trust. My point was just that adding a trust entry isn't enough if there's an earlier password entry. I agree that .pgpass is probably the desired answer anyway. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] [GENERAL] FW: backup using pg_dump postgreSQL 8.3.8
On Wed, Mar 09, 2011 at 03:58:20PM +0200, Sandy Test wrote: > > We want to be able to run a nightly backup using the pg_dump command. > > Unfortunately, even with the pg_hba.conf fix of adding host postgres ... > trust, > > It still asks for a password. pg_hba sometimes surprises people because of its matching rules. If there's another entry that matches before the explicit postgres entry, that's the rule you're going to get: The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied. (http://www.postgresql.org/docs/9.0/interactive/auth-pg-hba-conf.html). Are you sure that's not your problem. (It always is for me, and I always make this mistake at least once per installation, even after many years.) A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PID file
On Mon, Sep 29, 2008 at 12:43:54PM -0500, Ing. Jorge S Alanís Garza wrote: > shutting down cleanly. Is there a way to recover the non-working postgres > instance? Is this a very corruption-prone environment? It's sure corruption-prone if you delete the pidfile. If your iSCSI system keeps dropping out on you, then you need to fix that. Otherwise, things are going to break in a way you'll be unhappy with later. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] turning of pg_xlog
On Mon, Sep 29, 2008 at 01:00:41PM +0200, Jonny wrote: > Is it possible to turn off the comlete (Wal) pg_xlog? No. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Recommend dba maintenance tasks on a regular bases
On Fri, Sep 12, 2008 at 11:49:46AM -0400, Barbara Stephenson wrote: > I have recommend the below to my group but not sure if reindexing should be > involved since autovacuum is on? No, there's no reason to reindex regularly if everything is working as expected. > > How can I be sure auto vacumming is working fine? Check the pg_statitistic_all_tables entries in last_autovacuum and last_autoanalyze. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] replica of database
On Wed, Sep 10, 2008 at 09:52:41PM -0700, Joshua D. Drake wrote: > Version 7.3 is end of life and I don't know of *any* of the replication > technologies that will work with it. The 1.0.x Slony releases work with 7.3.x, x>2. I don't recommend sticking with 7.3, however. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Bug introduced in 8.0
On Tue, Aug 26, 2008 at 08:46:33PM -0400, Lew wrote: > upgrades to PG, it is our duty to inform our bosses of the risk of not > upgrading, so they can properly assess risks and manage them accordingly. I agree. It is, by the same token, your duty to yourself to ensure that, if the answer is, "No," you get that answer in writing so that future failures are not possibly pinned on you as having been negligent in installing stability and security releases. By the way, I always refer to these of late as "security and stability" rather than "minor" releases. I do that because it presents them to the target audience in a way that is understandable. Those releases are, for the project, a maintenance burden and not a way to introduce features. It's wise to keep that in mind when presenting such releases to managers responsible for the decision. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Restoring Backup With OIDs As Primary Key
On Tue, Aug 26, 2008 at 01:30:23PM +1000, Ben C wrote: > > Is there a way to increment the OID by changing a value in the system tables? I don't think so, but I just tried creating a table in 8.3 with a column named "oid", and it worked. Maybe you can load in your data that way, so that you don't need to change your application. (I'd test it a little more carefully for side effects than I just did, mind.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Bug introduced in 8.0
On Tue, Aug 26, 2008 at 08:21:53AM -0300, Sergio Gabriel Rodriguez wrote: > "Fix bug introduced in 8.0 that could allow ReadBuffer to return an > already-used page as new, potentially causing loss of > recently-committed data (Tom)" > http://www.postgresql.org/docs/8.0/static/release-8-0-6.html > > I know an upgrade is a best solution but it's impossible now for me (I > do not have authorization), my answer is, what exactly means this bug? > is serious? Yes, it's serious. It means that you can lose data that has been committed. Get authorization. You can install the latest version of the 8.0.x software directly in place, with no dump or restore. It's a drop-in replacement. The minor releases are security and stability releases. If your management thinks that they are optional to install, then get that in writing, because they are instructing you to run known-dangerous software. They are being negligent. Patching your software for known defects is not "upgrading", it's "doing your job." A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Pg/CyberCluster test results
On Fri, Aug 22, 2008 at 06:33:50AM -0700, CG wrote: > cluster, and I'm finidng that it is REALLY easy for the two > back-ends to get out of sync with each other. When I investigated that product, I came to the conclusion that it's in the family of replication by query-dispatch. Everything in that family has this problem, and it's a fundamental limitation of the approach. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Pg/CyberCluster test results
On Fri, Aug 22, 2008 at 03:52:39PM +0200, RW wrote: > It seems that we have to wait for PGCluster-II which isn't a > "shared nothing" solution. Instead all files are on a shared > medium like SAN or iSCSI and all instances uses this medium > (similar to Oracle). That's not shared-nothing, it's shared-storage. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Very Urgent : Db processes are increasing
On Thu, Jul 17, 2008 at 02:46:58PM +0530, Suresh Gupta VG wrote: > Hi List, > > I am using pgsql 7.4.2 on solaris-9. Go and upgrade that right away. It has several very nasty bugs in it. 7.4.21 is the latest. > pg_ctl start -D /db1/postgresql/data -o "-i" > > If I exclude "-I" option, my application couldn't connect the DB server > at all. Well, you could set that in the configuration file, but obviously, you're using the network somehow. > Here are my queries. > 1) Why DB processes are increasing drastically, it reaches up to 104 > connection( configured to 100 max connections) through there is no > application trying to connect/access the DB. Something is connecting. You can change the logging settings to find out who is connecting and from where. Alternatively, try netstat to look at who is connecting to port 5432. > 2) Is "-I" option is important to get the access from other server to DB > server ?? Well, it's spelled "-i", but yes. You have to listen on a TCP/IP socket, or else nobody can connect from another server. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Backup and failover process
On Tue, Jul 15, 2008 at 11:08:27AM -0500, Campbell, Lance wrote: > 1) On the primary server, all WAL files will be written to a backup > directory. Once a night I will delete all of the WAL files on the primary > server from the backup directory. I will create a full file SQL dump of the > database and put it into the same backup folder that the WAL files are put > in. The backup directory will be rsynced to the failover server. This will > cause the failover server to delete all of the WAL files it has copies of > each night. > 2)On the primary server, I will then check periodically with cron > during the day to see if there is a new WAL file. If there is a new WAL file > I will then copy it to the fail over server. > 3) At the end of the day I will repeat step #1. I think your outline sounds rather fragile. Moreover, I don't understand why you're planning to delete WAL files from the target server. It seems to me you'd be better off using pg_standby along with some helper applications. (Command Prompt has a tool we use for this, and I believe it's been released, but I'll have to check.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] full-text search question
On Wed, Jun 18, 2008 at 02:49:48PM +0200, Sabbiolina wrote: > www.google.com is only treated as a unique word? Why not producing multiple > tokens like www.google.com, www, ., google, ., com? (obviously www and . can > be nulled or stopworded). You wouldn't want to get the token ".". It's not a token, but a label boundary. So in your analogy of treating the labels in a FQDN as "words", the "." needs to be treated the way spaces are between words. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] table creation/modification datestamp
On Mon, May 26, 2008 at 11:25:56PM +0200, Andreas 'ads' Scherbaum wrote: > > But that covers only data changes. There's no way to track DDL changes. Ah, yes, you want DDL changes. No, the only way I know of doing that is by tracking the logs. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] table creation/modification datestamp
On Mon, May 26, 2008 at 11:18:37AM -0700, Kevin Neufeld wrote: > As a DBA, it would be extremely useful to know when tables have been > created or modified. PostgreSQL doesn't keep track of this, does it? Nope. There's a project in pgfoundry, IIRC, to do it with triggers. You could also use Slony (or any of its work-similar friends) to do something similar. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] character varying exceeded
On Tue, May 20, 2008 at 08:19:56AM -0700, Marc Fromm wrote: > breached the 1000 limit. When the user submitted the form the data was > not entered into the database and no error or message was displayed. I think your application is broken. There was so an error message generated. It sounds like your application didn't catch it or display it. If you're using straight psql, then the problem is that client_min_messages is set too high, and clients aren't seeing errors. That's surely a mistake. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Clustering 2 EDBs On windows
On Fri, May 09, 2008 at 06:52:47PM +0530, Sunitha S wrote: > We have setup edb-edb replication by configuring the master on one > machine while slave on another machine(both running on Windows OS and > postgres plus Advanced 8.3) for achieving the remote master/slave setup > as per the following document > > http://www.enterprisedb.com/documentation/edb-to-edb-replication.html If you're using enterprisedb products, I suggest you contact them for support. This is a PostgreSQL list. I know they build their products using PostgreSQL, but I haven't any idea how those products differ from the community-shipped PostgreSQL, and I don't feel comfortable speculating. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] 8.1.8 autovacuum missing databases
On Fri, May 02, 2008 at 04:18:17PM -0400, Tom Lane wrote: > Uh, no, what that function returns is the per-table > autovac_vacuum_timestamp. A grep through the source code shows that > the per-database last_autovac_time is exposed nowhere. Oh, sorry, I completely misunderstood you. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] 8.1.8 autovacuum missing databases
On Fri, May 02, 2008 at 12:45:07PM -0400, Tom Lane wrote: > to have exposed last_autovac_time anywhere in the pgstat views. > Obviously we cannot back-patch that, but I think it should be fixed > going forward. That's already fixed in 8.2 and forward (see pg_stat_get_last_autovacuum_time() and the pg_stat%tables views). This feature is actually something I think makes upgrading from 8.1 to 8.2 worth the effort. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Vacuumdb error - corruption
On Fri, Apr 25, 2008 at 06:03:12PM -0400, Bhella Paramjeet-PFCW67 wrote: > No database is not sitting on NFS storage. We are using emc storage and > the file system is fibre attached to storage. What's the filesystem? Are you sure you don't have any bad memory in the box? I'm suspicious of the hardware first. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Retore Postgres DB without dump
On Fri, Apr 11, 2008 at 04:14:05PM +0200, Baudrion Philippe wrote: > - So, I have build and installed a 7.4 version of Postgres > - Copy the files in an empty database > - Connection was ok, but "\dt" told me "no relations found". > > What can I try next ? > I also have the original "pg_clog" and "pg_xlog" directories, and I have > also tried to replace them. What can I do with the WAL segments ? If you didn't put everything back in the same place, you won't get a data area. Just start up the 7.4 back end against the entire data tree of the old 7.4 installation. a -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Handling large volumes of data
On Tue, Apr 08, 2008 at 12:13:36PM +0200, Johann Spies wrote: > I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950 Thow away your RAID 5. It's a loser for this. Raid 1+0 is what you need. > server. I am using an XFS-filesystem. I am not certain about the On another note, I've had abysmal experiences with xfs on linux. Like, "Oops, everything's in lost+found now!" abysmal. (Unfortunately, actually, in my experience Linux has only bad and worse choices for database filesystems. Maybe they'll get it right with ext4.) A -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_dump/pg_restore
On Tue, Apr 08, 2008 at 04:52:23PM -0300, Sergio Gabriel Rodriguez wrote: > needs of others which can't be found because the script will generate > them later. How can I make an orderer sql dump??? Use the custom format, use pg_restore to get the catalogue, and then edit that catalogue to re-order the restore.. A -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Hiding PG/SQL functions
On Tue, Apr 08, 2008 at 12:25:03PM +0200, [EMAIL PROTECTED] wrote: > > My questions are: > 1. How can you hide PG/SQL functions from other users, so that they can't > see the functions, but they can still execute them? See the archives on this. There are some tricks suggested, but the short answer is, "You can't." > 2. What do you think about this idea: Can you embed PG/SQL function in a C > code, compile it to a shared library and then let the users use it? How do > you do that? This would protect my functions (I think). I would rather > not give up PG/SQL language as the procedures are almost ready. So > embedding PG/SQL in C is feasible, while writing all procedures from the > scratch rather not. I don't know what it would mean to embed them in C code: you'd have to write enough C that you might as well just port the functions completely. > 3. Where can I find more details about database security in this respect > (ie. protection of my code). Even some links to the manual would be > helpful. Well, to begin with, you can do a quick search for "Security by obscurity," where you will likely discover that this is no kind of security at all (not even, really, protection of your code: it'd be fairly trivial even if you could obscure your functions to find out what they're doing, by looking at the logs of the database back end). Unless you run everything for the users and control where the code is installed and how it is distributed, some people will almost certainly attempt to do something with your code that you don't want them doing (as just about every software vendor has re-learned for themselves). Best, A -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Tuning
On Mon, Apr 07, 2008 at 12:45:38PM -0400, Chris Browne wrote: > versions of PostgreSQL. e.g. - with v7.2, there were patterns of > updates that would leave portions of indexes not usable, but the issue > was rectified in ~7.4, and people have not been observing problems > relating to this former scenario. There remain use patterns that will leave the indexes in pretty bad shape. This is an inherent limiation with btrees, though -- if you just unbalanced the tree with a large number of deletes, there's nothing you can do except REINDEX. A -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Postgres port bindings changed after box restart
On Wed, Mar 05, 2008 at 10:07:14AM +1100, Daniel Punton wrote: > Our redhat server was restarted and postgres is showing different databases > on different ports? > Could someone explain how this might happen Different configuration files? > and where this information comes from (what conf for port bindings) postgresql.conf. Other than that, you'll have to use find, I'd say. > as is there a way of guarenteeing this will not happen on restarts. Sure. When you start up, make sure each start-up script points to the correct data area. A -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-admin
Re: [ADMIN] Meaning of logs of postgresql
On Thu, Feb 28, 2008 at 06:15:29PM +0530, Suresh Gupta VG wrote: > WARNING: there is no transaction in progress This means you issued COMMIT when there was no BEGIN that started the transaction. I bet you're in autocommit mode and don't know it. > LOG: unexpected EOF on client connection This means the client disappeared. Are you disconnecting without closing your connections? A ---(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 mailing list cleanly
Re: [ADMIN] how do I get table DDL from psql (not from pg_dump)
On Wed, Feb 27, 2008 at 05:21:08AM -0600, Sofer, Yuval wrote: > I need to extract table DDL (create script) from database connection > (using sql, by retrieving system table info or by activating some pg > function) pg_dump -s does this. See the manual for more details on the switches. A ---(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 mailing list cleanly
Re: [ADMIN] pg_ctl reload query
On Thu, Feb 21, 2008 at 04:01:00PM +1030, Shilpa Sudhakar wrote: > Hi All, > > Wanted to know what does pg_ctl do in the background when we run *pg_ctl > -D datadir reload * It sends a SIGHUP to the back end. > command. Generally, it just reloads the files and everything works fine. > But strangely, last time when we ran the reload command and tried to > access *psql dbname, * > It gives a message *"database shutting down" * Are you sure you didn't issue "restart" instead? It's an easy enough mistake to make. A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Database Loading
On Tue, Feb 19, 2008 at 01:47:01PM -0500, Carol Walter wrote: > I have to load a very large database. Is there a command that can > load data in bulk faster than the copy command does? No. A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] why use -Ft or -Fc instead of the other on pg_dump?
On Tue, Feb 19, 2008 at 12:59:55PM -0500, Douglas J Hunley wrote: > I seemed to have walked into a "spirited" debate at work today, so I'm > turning > to the authorities to get it resolved. Why would one choose -Ft over -Fc (or > vice-versa) when doing pg_dump? Is there truly any difference of note > (outside the formats themselves)? My rule is "never use -Ft". I've tripped over various bugs over the years. Indeed, I just had another example of one (from 7.4, mind) the other day. A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] pg_dump on windows
On Fri, Feb 08, 2008 at 06:30:33PM +, [EMAIL PROTECTED] wrote: > > LOG unexpected EOF on client connection > LOG unexpected EOF on client connection > LOG unexpected EOF on client connection This means the back end thinks the client disappeared. What is the probability you have a firewall in between your client and the server? Some of them "helpfully" drop the connection after some period of "inactivity". Since you're just sitting there waiting for the command to complete, you look like you have no activity. > In locks tab > I see about 30 locks listed all for the same large table > They are all for the same start time of 11:35am > Mode: accessShareLock > Granted: yes. they all say yes for granted. If they're all granted, you're not waiting for one. A ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Search string without tsearch
On Mon, Jan 28, 2008 at 02:38:15PM -0300, A.Burbello wrote: > e.g ... name like 'JOHN%SMITH'; > > I know there is contrib Tsearch, but I can't for > political reasons. > Is there any way to do with good performance??? No. Also, rejecting the provided feature in the system for political reasons is a poor technical choice. I sympathise, but whoever the politician is needs to get a clue, or you'll never be able to make the system operate as it should. A ---(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 mailing list cleanly
Re: [ADMIN] Need some info.
On Thu, Jan 10, 2008 at 01:07:09PM +0530, Suresh Gupta VG wrote: > We are upgrading the pgsql from 7.4.2 to 8.2.5 on Solaris 9 Spark > machine, I had downloaded the sources also. I had gone through the > instalation documents too. I could not find the procedure to rollback > the new version, if the new version does not support our application. This is a difficulty, yes. > 1) Can we do rollback if required after complete installation?? Generally, major releases are binary incompatible. If you find your upgrade fails, you can of course continue to use your old installation (so don't delete your old data directory!). But if you've processed transactions in the new system, they'll be lost to the old installation, and then you're hosed. What you _can_ do, however, is install Slony. You might need to upgrade your 7.4 version to do this -- the version you have is very old, and there are some bugs that might affect Slony (I don't remember). Not to worry -- that shouldn't be a big deal (it's not a dump and restore). But it will require some planning and such. What you do, in this case, is install Slony on 7.4, add the 8.2.x replica, and let it catch up. When it's time to do your upgrade, you do a switchover (not a failover) and cause the 8.2.x to replicate to your 7.4.x installation. Now transactions that happen in 8.2 will also appear in 7.4, and if something blows up, you can switch back. This feature is an explicit design goal of Slony, and Afilias has used it in production, so I can be confident it works. But you need to do a lot of planning and testing. It isn't something to do in a couple days' time. > 3) If we do the installation in the other directory which doesn't > contain old version. Does it solve our problem Sort of; see above. A ---(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 mailing list cleanly
Re: [ADMIN] user login: problems in linux
On Tue, Jan 08, 2008 at 06:53:57PM +0100, Cédric Villemain wrote: > Andrew, it seems "root" is here a postgresql user (which have rights to > connect to "somedb") Yeah, I see that now. It's still a foolish idea. A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] user login: problems in linux
On Mon, Jan 07, 2008 at 08:26:08PM -0800, dr_pompeii wrote: > i only can loggin in postgres with superuser and not like a simple user > > see that "root" is a user for postgresql You're not allowed to log in as root. Period. Use a different user. There is no possible way you should be doing this as root anyway. A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Vacuum taking an age
On Fri, Jan 04, 2008 at 06:55:13AM +0200, Brian Modra wrote: > I started a vacuum on the table yesterday, and its still running. I > guess thats because the table is live. I am pretty sure that if I take > it offline, then the vacuum will complete relatively quickly. Am I > right? (I don't want to take it offline unless I really need to.) No. The vacuum on that table is going to take a long time, period. See upthread about not brushing teeth for 3 months. Anyway, I can't believe a plain vacuum is going to help you here. I'd perform CLUSTER or else dump the database and reload it. You're looking at a service outage, I think. A ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] reconfiguring diskspace while upgrading to 8.2.5
On Wed, Jan 02, 2008 at 10:04:53AM -0500, Mark Steben wrote: > The choices we see are: [. . .] I think this is likely to depend almost entirely on benchmarking your case, and testing the different possibilities. That said, except in extremely well-defined cases, it's often not a bad idea to make one large RAID anyway, because you sometimes find that your assumptions about your access patterns were wrong, and you've optimised for the wrong thing. In that case, the single large RAID offers greater flexibility, and if your controller is smart enough, the advantages are small enough that you don't gain enough for the loss in flexibility. I also want to point out that you need to test your workload _on 8.2_, and not make assumptions about disk use based on any experience you have gained from your production systems. Build a benchmark that looks like your production traffic, by all means, but don't extrapolate from 7.x to later versions for I/O information. There have been several huge strides in recent releases in reducing unecessary I/O, and 7.4 is susceptible to "checkpoint storms" that make even the most exotic storage hardware fail to perform well under some circumstances. Are your estimates of size, &c., based on a fresh load of the data? There are some lost-space issues in 7.4 that are solved in later releases, so you might find that some of your estimates of size are a little high. This matters for I/O, and might also affect your decision. > Also are there tools out there that monitor disk I/O and disk speed? Your OS should provide several. The basic ones are iostat and vmstat, but AIX has its own totally strange variants, and Solaris has some marvellous I/O tools. A ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] high-availability on MS Windows cluster -- need insights
On Thu, Dec 27, 2007 at 11:52:28AM -0800, George, Harry G wrote: > ed (text from MS CompSci course on DBMS design). Then emailed an > analysis and the tradeoffs as best I understood them to the user and > requested additional info and requirements (no response so far -- > holidays). AlsoOnly then did I begin posting requests for help, in > preparation for getting those answers back from the user. Ok, well, let us know what you learn. I'm happy to share what I know (it ain't much, as anyone who knows me will tell you), but without a more specific use-case, it's hard to say anything. > Sorry about the signature line. It is required here. However, I'll try > posting from home in the future. I don't care, myself. It's just one of those things that get some people's back up, and they won't respond to messages with them attached. A ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] high-availability on MS Windows cluster -- need insights
On Thu, Dec 27, 2007 at 07:45:36AM -0800, George, Harry G wrote: > A user asked for help re high-availability on MS Windows. While I'm Well, the first thing to do is define what you mean by "high availability". There's a good discussion of this in the manual. The manual does not, however, go over all the options you might have; so once you've determined which problem(s) you're trying to solve, we might be able to help you :) > Notice: This communication may contain sensitive information. If you [&c.] Just to warn you, there are some people around here who are very sensitive tot hese sorts of automatic corporate-policy footers. If you can disable it for sending to the list, that'd be good. (If you can get your legal department to understand why it's bad to append such things in a public posting, even better.) A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] What's the XID?
On Wed, Dec 26, 2007 at 05:48:14PM -0800, ivan.hou wrote: > > XID is transcation ID? Yes. > so, "select * from mydb" is a transcation? Yes. > if i executed "select * from mydb" twice, the XID wil be increased by 2 Yep. Whereas if you did BEGIN; SELECT 1; SELECT 1; COMMIT; the xid would be increased by 1. A ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Dangerous hint in the PostgreSQL manual
On Wed, Dec 12, 2007 at 06:01:52PM +0100, Listaccount wrote: > BTW : How can one find out the application doing unused allocations? > What value of "ps" output to watch for? As far as I know, the only way to learn that is to use a debugger. If the OS knew this, it'd be able to shoot the misbehaving process instead of whatever it guesses on. A ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Dangerous hint in the PostgreSQL manual
Dear docs mavens: Please see below for a possible adjustment to the docs. Is it agreeable? If so, I'll see about putting together a patch. On Wed, Dec 12, 2007 at 05:19:24PM +0100, Listaccount wrote: > >What I _would_ support in the docs is the following addition in 17.4.3, > >where this is discussed: > > > >. . .it will lower the chances significantly and will therefore > >lead to more robust system behavior. It may also cause fork() to fail > >when the machine appears to have available memory. This is done by > >selecting. . . > > > >Or something like that. This would warn potential users that they really > >do > >need to read their kernel docs. > > On this one we can agree. Maybe we should mention the root-cause. > > "It may also cause fork() to fail when the machine appears to have > available memory because of other applications doing careless memory > allocation" > > Would be nice to save others from learning about this the hard way. > > Regards > > Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Dangerous hint in the PostgreSQL manual
On Tue, Dec 11, 2007 at 03:08:36PM +0100, Listaccount wrote: > I would have not been surprised if the OOM-Killer would go around in > case of short memory but i was surprised to see fork failed with a > system having 1GB Memory available. You don't understand: the system _did not_ have 1G of memory available. It was all committed to applications that had asked for it. Just because they asked for it even though they were never going to use it doesn't mean that it isn't gone. It's used, as far as the kernel is concerned. The overcommit trick some OSes have implemented is a filthy hack to get around poor memory allocation discipline in applications. The point of the PostgreSQL documentation is to tell you how best to run Postgres, safely and reliably. The only safe and reliable way to run on Linux is not to use overcommit. Turning it off ensures that the system can't run out of memory in this way. What I _would_ support in the docs is the following addition in 17.4.3, where this is discussed: . . .it will lower the chances significantly and will therefore lead to more robust system behavior. It may also cause fork() to fail when the machine appears to have available memory. This is done by selecting. . . Or something like that. This would warn potential users that they really do need to read their kernel docs. A ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Dangerous hint in the PostgreSQL manual
On Tue, Dec 11, 2007 at 09:23:38AM +0100, Listaccount wrote: > I don't want to start the discussion what is the rigth thing todo, Then you shouldn't ask here. The manual was changed to say what it does after considerable community discussion. In my view, the Linux kernel's behaviour is completely unacceptable, and exactly the sort of amateur design foolishness that people are complaining about when they say Linux is a toy. > What i would like to see in the documentation is the easy hint to > check if you get i trouble with this setting so one can prepare. >From the point of view of Postgres, "getting in trouble" means "postmaster shot in head by surprise." If you feel otherwise, then you have to learn how to tune your operating system correctly. The PostgreSQL manual is not a place for general wisdom about how to tune various kernels. I think the advice is correctly worded as it is. > A simple "see if your "CommitLimit - Commited_AS" from /proc/meminfo > come close to 0 after some uptime and if so don't use it. That's not good enough, because the case where you really get into trouble might be an unusual case. It's in fact exactly the condition where your machine is facing surprising loads where memory overcommit will bite you. So following your advice will still lead people to be surprised when their postmaster goes away because they were Slashdotted or something. > only like to see a hint how to check *before* you get in trouble. "Am I using Linux with overcommit?" would be one such check. The only reliable one. (Also, "Am I using AIX?" just in case anyone thinks this is some sort of anti-Linux bias I have. Malloc lying ranks with system sins right up there with fsync returning before the bits are on the platter.) A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Dangerous hint in the PostgreSQL manual
On Mon, Dec 10, 2007 at 04:26:12PM +0100, Listaccount wrote: > Hello > > I have been trapped by the advice from the manual to use "sysctl -w > vm.overcommit_memory=2" when using Linux (see 16.4.3. Linux Memory > Overcommit). This value should only be used when PostgreSQL is the I think you need to read the documentation more carefully, because it clearly suggests you (1) look at the kernel source and (2) consult a kernel expert as part of your evaluation. In any case, > /proc/meminfo on a longer running system. If "Committed_AS" reaches or > come close to "CommitLimit" one should not set overcommit_memory=2 (see > http://www.redhat.com/archives/rhl-devel-list/2005-February/msg00738.html). my own reading of that message leads me to the opposite conclusion as yours. You should _for sure_ set overcommit_memory=2 in that case. And this is why: > this setting the machine in question may get trouble with "fork > failed" even if the standard system tools report a lot of free memory > causing confusion to the admins. You _want_ the fork to fail when the kernel can't (over)commit the memory, because otherwise the stupid genius kernel will come along and maybe blip your postmaster on the head, causing it to die by surprise. Don't like that? Use more memory. Or get an operating system that doesn't do stupid things like promise more memory than it has. Except, of course, those are getting rarer and rarer all the time. Please note that memory overcommit is sort of like a high-risk mortgage: the chances that the OS will recover enough memory in any given round start out as high. Eventually, however, the [technical|financial] economy is such that only high-risk commitments are available, and at that point, _someone_ isn't going to pay back enough [memory|money] to the thing demanding it. At that point, it's anyone's guess what will happen next. A ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Enhancement request
On Fri, Nov 30, 2007 at 02:00:05PM -0800, Joshua D. Drake wrote: > Yes autovacuum is great for general low use scenarios. Throw it at a > database doing hundreds of thousands (or even millions) of transactions > an hour that has relations that in the multiple hundred gig range and > autovacuum is useless for a good portion of that database. This isn't a good argument for adding new knobs, though. It's an argument for using the limited resources to make autovacuum better. Compare with the never-ending arguments for index hints. A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] clear statistics in pg_stat
On Thu, Nov 29, 2007 at 03:32:59PM -0600, Campbell, Lance wrote: > How can I clear pg_stat views? I thought there was a command I could According to the manual, there's this: pg_stat_reset() I suggest you read the section of the manual about stats collection. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [ADMIN] backup of postgres scheduled with cron
On Thu, Nov 22, 2007 at 09:14:13AM -0500, Martin Gainty wrote: > > Good Morning AndrewI noticed the Vixie cron responds to SIGHUP signalsDo > you have any suggestions or tutorials on how Postgres would feed these > event signals to Vixie cron? Why would Postgres have to tell crond to restart? A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] backup of postgres scheduled with cron
On Thu, Nov 22, 2007 at 02:59:33PM +0100, Marco Bizzarri wrote: > Andrew, can you confirm the previous statement? I'm checking on a Debian > Linux, > at it seems to be a Vixie Cron, and that feature is described in the man > page... If the feature's in your man page, then it works on your system :) I just wanted to warn you that this isn't an original feature of cron, so you have to check your system always to be sure you have it. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] backup of postgres scheduled with cron
On Thu, Nov 22, 2007 at 02:28:08PM +0100, Marco Bizzarri wrote: > > why don't you add a "MAILTO=" at the start of your > crontab file, so that you can receive a report of the problem? Note: check that your cron accepts such an addition. Many systems now use Vixie's cron, which does accept that, but some don't. It's a nice feature, and good for this purpose. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] functions pg_get...
On Thu, Nov 15, 2007 at 04:33:34PM +0100, "König, Monika" wrote: > I'm looking for a postgres-funtion that shows me the definition of a table. In psql, this is retrieved by \d [tablename]. By running psql -E, you'll be able to see how it generates that. (No, I'm not going to tell you, because I think it's good practice :) A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] trigger ddl actions in a table
On Thu, Nov 15, 2007 at 04:29:51PM +0100, "König, Monika" wrote: > I can see all ddl in my log files, but > - there are even the errors in and > - i can't use it for a select There are logging options that should allow you to collect the DDL and then parse it. You could then load it into a table. But to answer your question, > Is there an possibility for having such an system-action table?? you cannot today have triggers on system tables, and AFAIK nobody has contributed a DDL-audit capability yet. IMO the safest (i.e. least subject to attack) way to do this would be to pull it out of the WAL on another machine. Given that we already have PITR, the data must be in there (maybe not with a wallclock time, but with relative time). It seems this is something that could be added, if someone were to do the work. I am not aware that anyone is doing it at the moment, but I bet there are developers who will work for sponsorship :) A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] How to monitor resources on Linux.
On Wed, Aug 29, 2007 at 08:05:00AM -0400, John R Allgood wrote: >Yeap that was us on the pcmiler past issues. This app provides > mileage lookup for our application. Our customers require us to use that > package for mileage lookup. I don't know anything about pcmiler, but does it have to run on the same box as the back end? Maybe you could put it somewhere else, and then you could make your databases work well. Finding a nice 32-bit Intel box for it oughta be pretty trivial (=="cheap") these days. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] How to monitor resources on Linux.
On Tue, Aug 28, 2007 at 03:24:43PM -0500, Scott Marlowe wrote: > Also, by 7.4 autovacuum existed, even if it isn't perfect yet. It's > still better than weekly analyze. I wouldn't use it -- it had serious issues. But this is another point: 7.4 has a big whack of performance issues compared to later releases. So if upgrading is at all an option, it's worth considering. (This is all unrelated to memory use, though.) A -- Andrew Sullivan | [EMAIL PROTECTED] However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton ---(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 mailing list cleanly
Re: [ADMIN] How to monitor resources on Linux.
On Tue, Aug 28, 2007 at 04:14:09PM -0400, John R Allgood wrote: > We were running vacuum and vacuum full daily without the vaccum analyze > on weekends. After about 2 weeks the master database would slow down. That doesn't surprise me. If you have enough writes, the regular vacuum isn't running often enough. The goal is to vacuum "just enough". The vacuum delay stuff in more recent releases is valuable here. > How often do you run VACUUM or are you using the autovacumm daemon. We have a complicated set of scripts that vacuum some tables very often, some other tables less often, yet other tables rarely, and some tables only once a week. Autovacuum is currently in final testing, though, I believe (though it's not my department any more, so liberal salting of my words is needed). -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(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 mailing list cleanly
Re: [ADMIN] How to monitor resources on Linux.
On Tue, Aug 28, 2007 at 03:40:03PM -0400, John R Allgood wrote: > lot of activity as compared to the other databases. We run VACUUM at > midday VACUUM FULL at night, VACUUM ANALYZE on weekends. If you are running VACUUM often enough, then you should _never_ need VACUUM FULL. And weekly VACUUM ANALYSE is probably too infrequent. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(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 mailing list cleanly
Re: [ADMIN]
On Thu, Aug 09, 2007 at 01:05:16PM -0400, Mark Steben wrote: > > ERROR: could not send data to client: Broken pipe > When we run this manually on the local machine we do not get this error. It > is a Postgres function that is called. Is there a firewall in between that is timing out the TCP connection because it's "idle"? A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] varchar vs text columns
On Mon, Jul 23, 2007 at 11:33:54AM -0700, Jessica Richard wrote: > What is the difference between varchar and text for a string column? > What is the benefit of using each one? > Is text better than varchar performance wise? >From the manual: Tip: There are no performance differences between these three types, apart from the increased storage size when using the blank-padded type. While character(n) has performance advantages in some other database systems, it has no such advantages in PostgreSQL. In most situations text or character varying should be used instead. Note that varchar(n) will have a performance effect, because on insert or update you have to check to make sure the input doesn't exceed the specified length. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] cache problem (v2)
On Tue, Jul 17, 2007 at 08:45:13PM +0200, De Leeuw Guy wrote: > I have : > table test ( int code, int qte); Right, I remember that part. > t1 Is that BEGIN;? If not, this is _not_ one transaction. It's two. > INSERT test values(1, 150) >call my trigger that SELECT WHERE code=3 (does not exist) and INSERT You're not actually _calling_ the trigger, right? It just happens automatically? Also, I don't have the slightest clue how this code=3 (does not exist) works. AFAICT code=1. So code=3 is never true, no? > Now I have a flat file : > 1,150 > 2,450 > > COPY path_to_this_flat_file > > code=3,qte=450 > Why ? Because it's all one transaction, and because there's more than one state your variables could resolve to, only one of them actually applies is my _guess_. It's hard for me to say with any more detail without the code and the schema. Anyway, you have two transactions in your first example, it appears. The COPY statement is only one. > Another error also is "duplicate key" This is a different problem. Where is it coming from? Anyway, you have some sort of collision there, as the result I assume of your modifications of the data. Does it only happen with the COPY case? If so, that's another clue that the trigger function is not doing what you think it is. > I read the documentation from postgres not all. But sufficiently to > start a test of a trigger. But apparently not a successful one ;-) I'm just saying, it seems to me that you have a deep misunderstanding of the way transaction scope works. I think you need to have another look at that. I also think you need to look a little harder at how COPY works as compared to INSERT. > I never say that it's a bug, I say that with COPY the trigger does not > work like with INSERT. I think it does, and I think your problem is coming from your misunderstanding of how the trigger is working. But I still don't have a clear handle on what you're trying to do. One way to try this is to put your two INSERT statements into one transaction. If things are really as you seem to have described them (two insert statements, two lines in the COPY file), then if you do BEGIN;INSERT..;INSERT..; COMMIT you should get the same problem you do with COPY. > and to speed up the major type of query called by our users I build a > sum of different items. I can see why you'd do that (although it seems like a pre-optimisation to me -- you might find that PostgreSQL is fast enough to do this without the precompiled number). > This is the job of my trigger : build the sum code to speed up the > standard query of our users. Right. Like I said, I think you have a problem, likely in your function code, that is causing only one criterion to evaluate to TRUE when you think two of them should. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] cache problem (v2)
On Tue, Jul 17, 2007 at 05:49:15PM +0200, De Leeuw Guy wrote: > > Your trigger function runs inside the transaction of the calling > > statement, unless you have explicitly started a transaction. > > > Not possible from a trigger Yes, sorry, I phrased that wrong. Let me put it differently: your trigger runs only inside the transaction of the calling statement, unless that statement itself is inside a longer explicitly-called transaction. For example: t1 t2 BEGIN UPDATE table_with_trigger SELECT somethingSELECT ...FROM trigger_effect INSERT something else COMMIT In this case, t2 does _not_ see the effects of the trigger in t1, because those effects are not visible until the COMMIT. But t1 t2 UPDATE table_with_trigger SELECT somethingSELECT...FROM trigger_effect INSERT something else in this case, t2 _does_ see the effects, because the trigger's effects are COMMITted implicitly after the UPDATE statement. > Yes true, It's my first step with postgres but also with a relationnal > database. > But I'm sure that with COPY the data are not correctly updated and > if I edit the file loaded by the COPY command and adapt it with each > line with a command INSERT all work fine. If I read that right, you admit that you are inexperienced with the concepts and the software, and you are unable to show us all the relevant code or send us a precise description of what you are doing; but, you are convinced nevertheless that the problem is a bug or deficiency in PostgreSQL that nobody else seems to be having, and not a problem with your approach? I suggest you think again. A -- Andrew Sullivan | [EMAIL PROTECTED] However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton ---(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 mailing list cleanly
Re: [ADMIN] cache problem (v2)
On Tue, Jul 17, 2007 at 07:59:32AM +0200, De Leeuw Guy wrote: > the COPY start a transaction Yes. _Everything_ is in a transaction in PostgreSQL. If you don't explicitly do BEGIN. . .COMMIT, then the system does it implicitly for each statement. > if yes each time my trigger select a data > and update then the value returned by the read are the value before the > transaction. It's true ? if yes my question are : also in the same > transaction (like my trigger) ? Your trigger function runs inside the transaction of the calling statement, unless you have explicitly started a transaction. I'm starting to get the impression you have a misunderstanding of how PostgreSQL works. I think you maybe need to spend some more time with the manual. If you find the English version puzzling, you can also work in French (at http://docs.postgresqlfr.org/) or German (I see a translation at http://www.postgresql.org/docs/books/pghandbuch.html.de; I dunno if there's a more recent one). Sorry, AFAIK there isn't one in Dutch. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] cache problem (v2)
On Mon, Jul 16, 2007 at 10:22:33PM +0200, De Leeuw Guy wrote: > > > Yes, but maybe the post are too big, can I send it to your address ? > It's written in C Well, sending it to me will do you zero good, as my C skills are awful. Assuming it's a reasonable size, I think the list will take it. > > There is nothing in the cache that isn't "in the database", as it > > were, but there are visibility rules that might be affecting you. > > > visibility rules ? You can see things that happened in your own transaction, but nobody else can. If you're working READ COMMITTED, you can also see work that other transactions commit while you were working. Could any of that be affecting you? Unless your own function is doing something with some cache, I am positive there's no cache issue here as you describe it. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] cache problem (v2)
On Mon, Jul 16, 2007 at 07:47:09PM +0200, De Leeuw Guy wrote:
> >
> > So if I am reading you correctly, IF code IN (1,2) THEN {do
> > something}? What do you do then? I don't get this part.
> >
> I record a row with code = 3 and the value = the sum of each values in
> code 1 & 2
This is also opaque to me now, because that doesn't seem to be what
your example said. Perhaps you should post your function body?
> > I don't see how this is possible either. You just deleted from test;
> > how can you hae anything left in there?
> >
> I skip the delete action when the code is a sum code
Well, that's not what your example said either. You need to show us
actually what you are doing, rather than describing it in terms that
leave out significant parts.
> Yes I test this situation on my trigger.
> I sure about 99% that my problem come from the cache.
There is nothing in the cache that isn't "in the database", as it
were, but there are visibility rules that might be affecting you.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
---(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 mailing list cleanly
Re: [ADMIN] cache problem (v2)
I tried to answer the previous version of this mail, but I don't
understand it. Let me see if my questions trigger something for you,
though.
On Mon, Jul 16, 2007 at 12:32:07PM +0200, De Leeuw Guy wrote:
>
> a table :
> CREATE TABLE test (
> codeint8 NOT NULL,
> qte float8,
> CONSTRAINT test_pkey PRIMARY KEY (code)
> )
> CREATE TRIGGER trig_update_sum BEFORE INSERT OR UPDATE OR DELETE ON
> table_test
> FOR EACH ROW EXECUTE PROCEDURE gd_trigfunc_before_buildsum();
> EOF
>
> code maybe 1,2,3
> 3 = sum of 1 & 2
>
> the trigger make the sum :
> if event = INSERT code = 3 ==> skip
> if event = INSERT code = 1 or 2
>if select code 3 exist ? yes = update qte code 3 + qte code x
> no = insert qte to code 3
So if I am reading you correctly, IF code IN (1,2) THEN {do
something}? What do you do then? I don't get this part.
> INSERT INTO test VALUES (1, 50);
> INSERT INTO test VALUES (3, 60);
> ok code 3 = 110
>
> psql : delete from test;
> code 1 & 2 deleted
> code 3 = 50
I don't see how this is possible either. You just deleted from test;
how can you hae anything left in there?
> maybe this is because these commands start a transaction with a cache
> and each time that I request a select for a sum code that are on the
> cache the value returned are not the real new value.
No, but a trigger that executes SQL can cause the trigger to fire
again. Are you sure that's not happening?
A
--
Andrew Sullivan | [EMAIL PROTECTED]
Everything that happens in the world happens at some place.
--Jane Jacobs
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] 8.2.4 patches?
On Tue, Jul 10, 2007 at 04:32:32PM -0400, Ray Stell wrote: > Are there critical security or performance patches that should be > applied to 824 and how could I have determined this without this post? If there were a real critical bug, there'd be a new release (which would mean 8.2.5). So the short answer is, "No." But sometimes there are patches that are critical for you_ but not _for everyone. You can compare the difference between the REL_[v_num]_STABLE and REL_[v_num]_[minor] of your release to see if there's something. You can do that by looking at the CVS interface at <http://developer.postgresql.org/cvsweb.cgi/pgsql/>, among other ways. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Help me find a suitable replication solution
On Tue, Jul 03, 2007 at 08:15:35AM +0800, Yuan HOng wrote: > sites shall update these tables, and the link is over a WAN, it seems > the most appropriate replication solution would be a multi-master, > asynchronous solution. Yes, that would be best. > be to use a separate table for order on web server and replicate it > back to inhouse server. But then I have two tables for order, which > will make the applicate much complicated. Yep. > Finding no suitable soluiton for my case, I am wondering whether I > have made a mistake in my database design. Havent' others experienced > similiar situation as I do? Or do I have some misunderstanding of the > capabilities of the existing solutions? Am I overlooking something? There simply isn't anything that implements this now. It's possible to bodge it up using Slony, as you point out, but that makes things more complicated. If you need this now, though, that's your only option AFAIK. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] replication between linxu postgresql and Windows postgresql
On Mon, Jun 25, 2007 at 02:42:04PM -0400, Chris Browne wrote: > No it can't. It will not work with versions of PostgreSQL earlier > than 7.3.3, because it requires namespace functionality that did not > stabilize until that point. But if you're running on <7.3.3, you really, really need to upgrade anyway. And you could upgrade to the latest 7.3 and get the Slony functionality. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Database User
On Wed, Jun 20, 2007 at 11:59:11AM -0400, Carol Walter wrote: > I hope this is a simple request, but I'm a relative newby and I don't > know my way around the system tables yet. My user wants to know who > has privs to a specific database. How can I give her this info. You could use the access privilege functions documented in http://www.postgresql.org/docs/8.1/interactive/functions-info.html. Alternatively, the datacl column in the pg_database table might help you. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On Mon, Jun 18, 2007 at 02:38:32PM -0400, Andrew Sullivan wrote: > I've picked -advocacy. Actually, I _had_ picked advocacy, but had an itchy trigger finger. Apologies, all. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On Mon, Jun 18, 2007 at 02:16:56PM -0400, Jonah H. Harris wrote: > pgsql-advocacy... your thoughts? I've picked -advocacy. > > I think the Oracle discussion is over, David T. just needs URL references > IMHO. I don't think we can speak about Oracle; if we were licenced, we'd be violating it, and since we're not, we can't possibly know about it, right ;-) But there are some materials about why to use Postgres on the website: http://www.postgresql.org/about/advantages A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
All, On Mon, Jun 18, 2007 at 07:50:22PM +0200, Andreas Kostyrka wrote: [something] It would appear that this was the flame-fest that was predicted. Particularly as this has been copied to five lists. If you all want to have an argument about what Oracle should or should not do, could you at least limit it to one list? A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] AIX - best practices?
On Sat, Jun 16, 2007 at 12:00:36PM +0200, Dawid Kuroczko wrote: > Anyway, what are your feelings with regard to using PostgreSQL > under AIX? "Don't". This is just my personal opinion, and I don't speak for my employer (especially in this case!), but my feelings are that AIX is an awful, bletcherous filthy mess that attempts to make UNIX look like a bad imitation of an AS400. Just for example, some packages come as RPMs and some as smit-installable packages, but neither packaging system knows anything about the other one, so you have the potential for all sorts of stupid errors that packaging systems were designed to _avoid_, without any of the flexibility of just compiling it yourself from source. This foolishness includes packages like, oh, gcc. Making shared libraries reminds you of the bad old a.out days on Linux; and it is distressing the IBM hasn't managed to update AIX to pre-1995 technology. That's ok, though, because some things (it seems) can't be made into shared libraries at all. So far as I know, for instance, we were never able to get PL/R working on AIX, because we just couldn't get the darn thing to compile. I seem to recall that building Perl in a way that was adequately flexible ended up taking weeks of troubleshooting and work on the part of people much smarter than I. Seneca Cunningham posted either on -general or -hackers (I forget which, but check the archives) a set of fantastically detailed contortions necessary to get some set of Postgres things working the way we wanted. AIX is designed to foil the simplest possible desires in an effort to solve some problem I haven't learned of yet. And the above assumes that everything works the way you would expect -- you know, in the way the manual says it does. I have no idea what parts of the system are actually tested by IBM's QA department before new AIX releases ship, but I can say with some certainty that parts of both libc and fsck -- fsck!! -- got overlooked in the past. At least when the Linux fanboys push on you the kernel patch of the millisecond, you know that you can look at the code or ask someone else about their experiences with it. In the case of IBM, what you get to ask is, well, IBM Support. Nice people. Excellent phone manners. Professional and worthy emails suitable for forwarding to management without even looking for scatalogical remarks. But prompt and effusive with technical detail? Not so much. The only slightly saving grace on AIX is a utility called topas, which does a nice curses-based display of various performance pieces. It makes most of the tools available on Linux seem primitive; but then, since most of the tools on Linux _are_ primitive, that shouldn't be surprising. And it's sort of awful that what you get for your license fee and inability to look at the code is one nice tool that works better than some free stuff, but doesn't work anywhere near as nicely as the RICHPse toolkit that was available starting with (IIRC) Solaris 2.5. Also, the _usual_ tools that you might be used to don't exist on AIX, so you have to learn topas, or fly blind. The large print giveth, and the small print taketh away. I loathe using AIX. I would run the other way before installing it on purpose. And given that IBM also supports both Red Hat and SuSE on POWER5 (and that for at least one non-Postgres workload we tried, Linux was actually faster on the hardware), I'd use Linux instead. > How do you feel it compares to other "big" Unixen as far as > PostgreSQL goes? As they say in alt.sysadmin.recovery, all operating systems suck. They don't usually say, however, that AIX, when pronounced as a word, is the only one that actually describes what it gives you. All best, A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] the right time to vacuum database?
On Fri, Jun 08, 2007 at 08:20:36AM -0700, Charles.Hou wrote: > because if the size of database have been increased 36MB in one day , > then it will be 1G MB increased after 1 month. so i worry about the > size. other strange thing, if i block all postgresql client and run > vacuumdb, there will have about 100MB free space. Your conclusion of linear growth doesn't actually follow from your premises. In any case, it sounds to me like (1) you're not vacuuming often enough and (2) you have transactions open from the clients which are preventing vacuuming from being effective. Autovacuum will solve (1) now that you've enabled it, but you need to fix your existing bloat (which probably means either VACUUM FULL or CLUSTER and REINDEX). (2) is a bigger problem, which you need to address at the client. Also, I bet your free space map is too small. Increase it. I have the feeling, on the basis of this thread, that you need to spend some more time with the manual. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] the right time to vacuum database?
On Fri, Jun 08, 2007 at 06:29:55AM -0700, Charles.Hou wrote: > > i have traced the size of the table. About 1000 new rows will be > inserted into the table in one day. each row has 300 bytes. > 1000*300/1024=293K, but the size of this table had been increased 3MB. > 3MB-293K=2.7MB...Why?where is the 2.7MB? How do you know what the size of the table is? You had at least two tables you were working on before. I think there must be something you're not communicating completely. (And why are you worried about less than 3 Meg anyway? Regular vacuum will leave some empty space around for new data, which means you don't have to go down to the filesystem to make the file bigger before you write it it. This is a Good Thing.) Please go back and run VACUUM VERBOSE on the table you killed the vacuum on before. A -- Andrew Sullivan | [EMAIL PROTECTED] However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] reclaiming disk space after major updates
On Thu, Jun 07, 2007 at 03:26:56PM -0600, Dan Harris wrote: > > They don't always have to be in a single transaction, that's a good idea to > break it up and vacuum in between, I'll consider that. Thanks If you can do it this way, it helps _a lot_. I've had to do this sort of thing, and breaking into groups of a couple thousand or so really made the difference. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] reclaiming disk space after major updates
On Wed, Jun 06, 2007 at 04:04:44PM -0600, Dan Harris wrote: > of these operations or a full dump/reload. I do run VACUUM regularly, it's > just that sometimes we need to go back and update a huge percentage of rows > in a single batch due to changing customer requirements, leaving us with > significant table bloat. Do you need to update those rows in one transaction (i.e. is the requirement that they all get updated such that the change only becomes visible at once)? If not, you can do this in batches and vacuum in between. Batch updates are the prime sucky area in Postgres. Another trick, if the table is otherwise mostly static, is to do the updating in a copy of the table, and then use the transactional DDL features of postgres to change the table names. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] the right time to vacuum database?
On Thu, Jun 07, 2007 at 12:07:03AM -0700, Charles.Hou wrote: > i use the VACUUM VERBOSE a table , but it had been hold 10 minutes . i > must use the linux command " kill " to exit the VACUUM. > after i kill this PID, the PostgreSql have been restart > automatically. Maybe it was doing work. Why did you kill it? If there is a problem on that table, I wouldn't be surprised that it'd take 10 minutes. You just caused all the work it started to do to be undone, and to cause more bloat. But. . . > DETAIL: 0 dead row versions cannot be removed yet. . . . this suggests you don't have bloat on that table at least. So it sounds to me like your disk use is going up because you have a lot of data. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(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 mailing list cleanly
Re: [ADMIN] the right time to vacuum database?
On Tue, Jun 05, 2007 at 05:28:10PM -0700, Charles.Hou wrote: > Client connected: about 100 pc, 1 pc with 1 connection at least, the > max is 4 connections So up to 400 connections? Are they all running transactions? For any length of time? > 194 tables on the database, and some of the tables always executed > the update/insert command . So _some_ tables are getting updated? (Inserts are not really relevant to vacuuming here, but they'll surely be relevant to size) > i also set the autovacuum to enable. today, the database size is > 497MB. 497-440MB=57MB, 57 MB have been increased in 18 hours. > what's wrong with my PostgreSql Server? Maybe nothing. How much data do you think you're putting in there? What does VACUUM VERBOSE say, as I asked about -- are you getting a lot of dead space? A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] the right time to vacuum database?
On Tue, Jun 05, 2007 at 03:33:09AM -0700, Charles.Hou wrote: > how can i know that it's the time to vacuumdb? i set the crontab to > vacuumdb 3 times in one day. because my database size increase from > 440MB to 460MB in 8 hours. You haven't told us enough. What's the churn on the database, to begin with? That's only 20M. If you replace approximately 20M in 8 hours, then it might be that the FSM is working perfectly, and this is the overhead that is reasonable to keep around to allow your database to work perfectly. Also, have you looked into autovacuum? It might take some of the guesswork out of this. Finally, I expect that most of your churn is in particular tables. Those are the ones you want to vacuum frequently. The rest of them aren't that interesting. The way to learn about this is to use VACUUM VERBOSE, which will give you information about how many pages it is recovering. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Deletes hurt
On Thu, May 31, 2007 at 09:23:01AM -0700, Joshua D. Drake wrote: > Right. Where we just mark the row as dead and have to vacuum. We just > delay the pain ;). They've delayed the pain too. This is actually an illustration of what Tom Lane said recently -- that the strategy of paying the cost of maintenance outside the main transaction path is intrinsically superior, because you don't have to pay it while your user is sitting there waiting for you. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] High Avaliable in the PostgreSQL.
On Thu, May 31, 2007 at 05:30:20PM +1200, adey wrote: > HP was providing CA (Continuous Access) software that was claimed to provide > WAN SAN replication by repeating IO in exactly the sequence it was generated > on the master, to the slave. The CA stuff, or anything else built on FCIP, is pretty cool, but I'd worry a little about latency. Certainly your costs are going to be high on transit, but if the data and uptime are worth it, you could do it. The big issue here is the same as in any other failover-to-other-node case -- you have to be _super_ sure that the failing master writer is dead and disconnected, and across a wide area, this is going to be very tough to do. I think you'd have to spend a fair amount of time doing risk analysis of conflicting commits happening in both sites (if the WAN link goes down because, say, some genius decides to run a backhoe through the fiber going out of your "primary" city, you could find your secondary site decides to promote itself. But the primary site might still have transactions in flight.) > system. I'm not sure this would be sufficient for 99.9% uptime though, as > there would be some startup requirements on the slave. Right, and the requirement was actually upped to 99.99%, which is approximately 1 hour of allowable downtime a year. That is a very high bar. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] High Avaliable in the PostgreSQL.
On Wed, May 30, 2007 at 06:12:02PM -0400, Adam Tauno Williams wrote: > > Sure it can be done. Get two SANs that support replication, redundant > high-speed WAN links, high end servers, large UPSs, and generators. Most SANs that I've seen aren't in "geographically separate" locations in the way most think of this. It's usually metronet -- it's not even a different city. That's a poor disaster prevention strategy, although it might be worth it as step one. (If you can take the latency, of course, you can make this go further, but true both-coast solutions, for instance, will make the latency such that users will certainly be able to see it.) A -- Andrew Sullivan | [EMAIL PROTECTED] However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] High Avaliable in the PostgreSQL.
On Wed, May 30, 2007 at 04:42:08PM -0300, Fernando Ike de Oliveira wrote: > was 99,7% but considering the current necessities, change percentual > to 99,99%. I think in solution probability pgpool-2 or Heartbeat + > GFS. The PostgreSQL servers will be in different physical places. I would be very interested to see a set-up that can actually guarantee 99.99% uptime across a wide area link. What write speeds do you need in the database? What is your tolerance for loss of committed data? A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(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 mailing list cleanly
Re: [ADMIN] Desaster Recovery from failover using SLONY-I
On Wed, May 30, 2007 at 12:57:55PM +0200, Peter Hausmann wrote: > Hi, > > After a node is dropped due to failover, it takes days to recover the > database, because it is build up from scratch. This is from Slony? You know that that's a built-in limitation, and that the preferred method is switchover, right? Failover should be the last result. (Also, you'll get better results if you discuss this on the slony list.) > We would prefer another approach: > Make an Online Backup of the Provider database. > The provider database continues writing new data and the Slony-Logs. > The Backup is restored to the failed system. > The slony cluster is dropped from the restored system > and the system is defined as Slony-subscriber. > How can we setup this system to recover from the status of the backup time? Slony knows nothing about the WAL-archiving backup method. When you subscribe a datbaase, it automatically drops and recreates the target from scratch. There has been some discussion of how to improve that, but AFAIK nobody has written the support for it yet. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Can I restrict backups?
On Wed, May 23, 2007 at 10:44:36AM +0200, Andreas wrote: > The problem - not only - of my customer is that he primarily has to > trust strange people enough to grant them access to the very base of his > business and this base could be carried away on an USB stick or piped > out via email or whatever. > Legal repercussions against the individual when the worst case happened > are no actual solution, I fear. The only actually useful advice one can give in that case is, "Don't do that." If the business is worth having, it's surely worth protecting by hiring people you can trust. But you could restrict the subset of the data they can see using VIEWs. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Manual trigger removal [WAS] Flushing Postgres Function Cache
On Thu, May 17, 2007 at 02:02:59PM +0100, Raf wrote: > I'm curious as to whether the problem may have resulted from slony > corruptting internal pg_ or postgres not correctly respecting the > integrity of drop-trigger type transactions and their respective > dependencies. Did you do any DDL with Slony installed, without using EXECUTE SCRIPT? A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Measuring statement runtime?
On Wed, Feb 07, 2007 at 09:00:32AM -0800, Andrew Edson wrote: > What is the command I should be looking for to measure the > runtime on my statements? In psql, use \timing. In your postgresql.conf file, you can set log_min_duration_statement=0. You can also use EXPLAIN ANALYSE to see the full execution plan, plus information about how long each step _actually_ takes. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] database encryption
On Wed, Feb 07, 2007 at 11:23:03AM +0100, Olivier Boissard wrote: > I saw on the web that there is a contrib (pgcypto) but I can't estimate > its efficiency Well, it works for some people. What are you trying to accomplish with "database encryption"? A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(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 mailing list cleanly
Re: [ADMIN] how to add a new data partition
On Tue, Feb 06, 2007 at 01:46:19PM -0800, Karthikeyan Sundaram wrote: > We are using postgres 8.0.1 on our production and 8.2 on our development. > The linuix partition is getting full where the database resides. In few Which partition is getting full? > Adding a new tablespace doesn't solve the issue as there are many tables > in the old tablespace. Is there a way to extend the tablespace to the new > partition like in Oracle. Not like in Oracle; Postgres doesn't manage the disk the way Oracle does. My suggestion is to use the underlying operating system tools to do this. I know you _can_, but I haven't done it recently on Linux. A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Postgres-R
It's basically pre-production-ready software to date. But see the excellent presentation materials at http://conference.postgresql.org/Program (last presentation in the left-hand column). I hope to have audio for that session available soon. A On Thu, Nov 02, 2006 at 07:16:24PM +0200, Anton P. Linevich wrote: > > Hello. > > Just interesting, anyone from this list ever try Postgres-R for > cluster/replication? > > Sorry for OT. > > -- > Anton P. Linevich > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(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 mailing list cleanly
Re: [ADMIN] New to PostgreSQL
On Thu, Nov 02, 2006 at 11:13:24AM +0300, Achilleas Mantzios wrote: > > I think, apart from postgresql you gotta get familiar with the > linux/*bsd/unix > concepts as well, as sometimes performance has to do with OS tuning too. Let me echo that, because it's extremely important: when I was in charge of hiring people to work on our databases, I was very concerned not to get the sort of people who felt that the system was "the sysadmin's problem". You _cannot_ treat the operating system as not your problem when addressing performance questions in PostgreSQL. This is a very unfamiliar situation to most Oracle and some DB2 administrators, in my experience. The good ones, of course, have no problem catching on; the bad ones never deserved the name DBA anyway ;-) A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] 100% failover + replication solution
On Mon, Oct 30, 2006 at 06:41:54PM +0530, Moiz Kothari wrote: > I agree that PGCluster might be a better option, i dont want to go with > Slony because of primary key constraints. I don't know what the "primary key constraints" issue you have is, but Slony would be inappropriate for a "100% failover" system anyway: you can't know you haven't trapped data on the origin. This is in fact true for the WAL shipping you suggested, also. The only way to achieve 100% reliable failover today, with guaranteed no data loss, is to use a system that commits all the data on two machines at the same time in the same transaction. I haven't seen any argument so far that there is any such system "out of the box", although with two phase commit support available, it would seem that some systems could be extended in that direction. The other answer for all of this is to do it with hardware, but that's a shared-disk system, so if your disk blows up, you have a problem. Or, if you're using the operating system of people who don't know how fsck works. I don't know anyone who has that problem; certainly not any vendors whose name starts with 'I' and ends with 'M'. > 1) It might slow down the process a bit. as confirmation happens after > transaction gets comitted to all the nodes. Anyone who tells you that you can have completely reliable data replication with no performance hit is trying to sell you a bridge in Brooklyn. If you want reliable data replication that guarantees you can have automatic failover, you are going to pay for it somehow; the question is which compromise you want to make. That seems to be something you'll need to decide. > 2) Its difficult to convince, as it is an external project and if support > for the same stops or future versions of postgres does not work, it might be > a problem. If you have this problem, probably free software isn't for you. PostgreSQL is a modular system, and people use different components together in deployed systems. This happens to be true of commercial offerings too (if not, you could buy the cheapest version of, say, Oracle and get RAC in the bargain), but they _sell_ it to you as though it were one big package. To the extent your managers don't understand this, you're always going to have a problem using free software. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Vacuum Problems (locking)
On Wed, Oct 25, 2006 at 03:54:17PM -0400, Mike Goldner wrote: > Finally, and most important is the blocking. The vacuum duration > reported in the log converts to about 170 minutes. I can track > backwards in the log and the only messages prior to the 6:52am > completion of the vacuum end at 3:57am (almost exactly 170 minutes > prior). > > So, all indications point to postgres blocking all access during the > entire vacuum. The vacuum command you posted doesn't block. I can think of two other explanations: 1. You just didn't have any activity then. Don't throw away this possibility without evidence: I can't count the number of blind alleys I've been down because someone insisted that "never happens". 2. You're completely pegged on I/O. Vacuum will make this worse, and maybe therefore no transactions get through. You can fiddle with the vacuum settings to get them to back off a little and let some other transactions through. That said, your essential problem is that one table. Vacuum it more often -- from the look of the churn on it, I'd just put a job on it that runs all the time and sleeps for a few seconds in between -- and you shouldn't have this problem. But you'll need to VACUUM FULL or dump and reload first. A -- Andrew Sullivan | [EMAIL PROTECTED] Windows is a platform without soap, where rats run around in open sewers. --Daniel Eran ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] postgres in HA constellation
On Tue, Oct 10, 2006 at 10:11:08AM -0500, Jim C. Nasby wrote: > couldn't setup HA on OpenBSD. The key is just to make sure that you > never bring up two servers on the same data directory. I think this highlights exactly what I'm trying to emphasise: in actual, shared-nothing systems like this, there's no possible guarantee of "never". There are possible guarantees of "very rarely". The problem is, you're already trying to address a teeny portion of the likely events on your machines. So you have to assume that more than one thing might break at the same time, and have a recovery plan for it. I submit that a recovery plan of "restore from pg_dump" is usually not going to be enough if it was worth the cost and hassle of setting up shared disk failover. YMMV, of course. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] postgres in HA constellation
On Thu, Oct 05, 2006 at 08:43:21PM -0500, Jim Nasby wrote: > Isn't it entirely possible that if the master gets trashed it would > start sending garbage to the Slony slave as well? Well, maybe, but unlikely. What happens in a shared-disc failover is that the second machine re-mounts the same partition as the old machine had open. The risk is the case where your to-be-removed machine hasn't actually stopped writing on the partition yet, but your failover software thinks it's dead, and can fail over. Two processes have the same Postgres data and WAL files mounted at the same time, and blammo. As nearly as I can tell, it takes approximately zero time for this arrangement to make such a mess that you're not committing any transactions. Slony will only get the data on COMMIT, so the risk is very small. > I think PITR would be a much better option to protect against this, > since you could probably recover up to the exact point of failover. That oughta work too, except that your remounted WAL gets corrupted under the imagined scenario, and then you copy the next updates to the WAL. So you have to save all the incremental copies of the WAL you make, so that you don't have a garbage file to read. As I said, I don't think that it's a bad idea to use this sort of trick. I just think it's a poor single line of defence, because when it fails, it fails hard. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] postgres in HA constellation
On Thu, Oct 05, 2006 at 04:24:17AM -, Sebastian Reitenbach wrote: > > I just have one data center, no remote far away replication is needed. If it is at all feasible with your budget, I'd think _very strongly_ about replicating using Slony inside your data centre _too_. The shared storage answer is nice, but it is _really really really_ easy to shoot yourself in the foot with a rocket propelled grenade with that arrangement. Very careful administration might prevent it, but there is a reason that none of the corporate people will guarantee two machines will never accidentally mount the same file system at once: in a shared-disc-only system, it's impossible to be 100% certain that the other machine really is dead and not coming back. Very tricky scripts could of course lower the risk. If you're really going to have all that data, it's going to be a major pain to restore in the event of such corruption. In addition, your recovery will only be to the last dump. That's why I suggest replicating, either with Slony or something else, as a belt that will nicely complement the suspenders of your shared-disc failover. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Postgres Logging doesnt work
On Mon, Oct 02, 2006 at 11:04:11AM -0400, Pallav Kalva wrote: > Hi , > > My production database stopped writting to the postgres log files > all of a sudden, does anybody know why ? How big is the file? > log_rotation_age | 10080 > log_rotation_size | 0 You've set this up to rotate once every 10,080 minutes, no matter what. If you've run into a file size limit, then you'll be out of luck until the next file is opened, which should be on the same day of the week the postmaster was last started. Just a guess. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
