[GENERAL] An alternatives to rules and triggers
Is there some way that one can determine whether a table has changed i.e. an insert, delete, update, without having to resort to setting a flag in another table using a triger or rule. I was wondering whether one of the system relations keep track of whether a table has been modifed. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Connections refused during backups
After an upgrade from 8.0.8 to 8.1.8, I am getting a number of our scripts failing with 'connection refused' at around 4:00am, which seems to be when the nightly database backup occurs. I am using "/usr/bin/pg_dump -Fc -b -c -C" to do the deed. Has there been a change in behavior, i.e. is the whole databases being exclusively locked or something that would prevent a connection. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Connections refused during backups
Tom Lane wrote: "Connection refused" is a kernel-level rejection, and at least in theory should have nothing to do with what Postgres is doing. Did you do a kernel update at the same time? regards, tom lane My apologies, I shouldn't have used the generic socket error 'Connection refused'. What I am getting are errors like "Could not connect to database" from libnss-pgsql ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Connections refused during backups
Tom Lane wrote: Glen Eustace <[EMAIL PROTECTED]> writes: My apologies, I shouldn't have used the generic socket error 'Connection refused'. What I am getting are errors like "Could not connect to database" from libnss-pgsql Please be *exact* rather than handwaving. What is the full text of the client-side message? What if anything shows up in the postmaster log? regards, tom lane This is the exact message from libnss-pgsql, there is currently no timestamp in the log ( which I will fix ), so I cannot place any messages into the correct timeframe. I'll wait until tomorrow morning and see if anything is obvious. The only thing I am aware of is that nss-pgsql uses a 10s connection_timeout, which may be too short when the pgsql server is busy. ---(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: [GENERAL] Connections refused during backups
Tom Lane wrote: Glen Eustace <[EMAIL PROTECTED]> writes: My apologies, I shouldn't have used the generic socket error 'Connection refused'. What I am getting are errors like "Could not connect to database" from libnss-pgsql This is the exact message from libnss-pgsql, While you are at it, you should complain to the libnss-pgsql authors about the inadequacy of the error message. It should at least be repeating the libpq error message ... regards, tom lane Nothing of interest appears in the postmaster logs so I am broadening my search for general network incidents/activity as opposed to postgresql specific. I have also downloaded the source for libnss-pgsql and will add some extra code to see if I can get any more useful info from it. Thanks for the comments/help to date :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Inconsistent values for 'now'
Hi,
I am trying to setup rules on a view that will maintain an audit trail
of modifications in the real table. Things seem to be going ok but when
I EXPLAIN my queries, the literal 'now' is being given two different
values, one 2 days earlier. I am running 7.4.7
The view is defined to be;
CREATE VIEW domain_registry AS
SELECT *
FROM domain_registry_history
WHERE tstamp > 'now';
The current record always having a tstamp of 'infinity'
admin=# explain update domain_registry set status='N' where
domain='xx.co.nz';
QUERY
PLAN
-
Nested Loop (cost=0.00..21.58 rows=4 width=304)
-> Index Scan using domain_registry_history_pkey on
domain_registry_history (cost=0.00..7.18 rows=2 width=304)
Index Cond: (('xx.co.nz'::text = ("domain")::text) AND
(tstamp > '2005-04-01 14:39:49.529816'::timestamp without time zone))
-> Index Scan using domain_registry_history_pkey on
domain_registry_history (cost=0.00..7.18 rows=2 width=146)
Index Cond: ((("domain")::text = 'xx.co.nz'::text) AND
(tstamp > '2005-03-29 15:26:50.361408'::timestamp without time zone))
Index Scan using domain_registry_history_pkey on
domain_registry_history (cost=0.00..7.18 rows=2 width=293)
Index Cond: ((("domain")::text = 'xx.co.nz'::text) AND (tstamp >
'2005-03-29 15:26:50.361408'::timestamp without time zone))
(8 rows)
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz
"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Inconsistent values for 'now'
> Offhand I'd expect the 'now' to be reduced to a timestamp constant > at the time the view is created. Hmmm, my assumption had been that the 'now' constant would be evaluated everytime the underlying SELECT was build by the planner. > although personally I'd not feel very comfortable with the idea of a > view whose contents change over time even when you weren't actually > doing anything to the database. Maybe you should rethink this idea > altogether... This technique was one that was recommended to me by an experienced DBA. Apparently, he and others use it a lot. Using the function now() rather than the literal 'now' works as expected. Thanks. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Mirroring databases in v8
I thought I had seen something that indicated that v8 was going to provide a means to mirror databases without needing all the triggers etc. I downloaded beta 3 but couldn't see anything that would do this. My goal is to mirror a database from one server to another, hopefully from the logs so that there is nothing added to the database. The mirrored db will be used by the remote server in a read-only fashion and will become read-write if the master is no longer available. The transition from RO to RW will involve a reboot of the server as there are lots of other things that need to be started with different properties as well. Is this functionality available in v8 ? If not, is it likely to be at any stage ? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Mirroring databases in v8
On Wed, 2004-10-06 at 20:03, Shridhar Daithankar wrote: > Well, you could start off with PITR capabilities of 8.0. But you may need to > do quite a bit of customization and experimentation. > > Check http://developer.postgresql.org/docs/postgres/backup-online.html > > And we would be very eager to know your experience..:-) Having read this, it would seem I might be jumping the gun a bit. The method given could be used to copy a running database to another system but I'm not sure how it would work if the mirrored database is required to be online at the same time. I suppose what I was looking for was a utility that I could use to pick up the WAL segments from one server and then immediately 'run' them against the database on the remote db (online), if not in real time certainly not too far behind. It would appear that such a utility doesn't exist yet. I am rather committed with other activities at the moment and my knowledge of the pg internals isn't upto speed but I may have a look at how this might work in the not too distant future. If anyone else has already headed down this road, I'd be happy to talk with them. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Mirroring databases in v8
On Thu, 2004-10-07 at 03:06, Tom Lane wrote: > Not sure if you are familiar with the Slony project, but that is > probably your best bet for data propagation to a live slave at present Hi Tom, Thanks for the comment. I was aware of Slony but didn't want the mirroring to require addition of triggers etc. if it could be avoided. I had come across a system in Ingres, and I know Ingres isn't PostgreSQL but it was still a cool idea. The logging system in Ingres produces journal files which are then available for roll-forward recovery, but there is a tool called dbaudit ( I think ) which allows one to determine what the changes where. From this info, ordinary SQL can be generated that one can then use to do other things. In this case the data was used to generate a who changed what when report that was feed back into the database into an audit table. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Functions not dropped.
I was a little surprised today when I tried to move a production database on to my test server. The test server already had a copy of the database so I did psql -Uadmin template1 drop database admin; create database admin; When I tried to reload the production database dump, I got a series of errors relating to functions. I repeated the exercise but this time did a \df prior to trying to load the database. In the public schema for the newly created admin database where all the functions from the old one. It seems that the drop database doesn't drop all of the functions. Is it supposed to ? I am using 7.4.2 on FC2. If it isn't is there a way to clean them all out before the load ? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Functions not dropped.
On Mon, 2004-11-01 at 21:14, Sim Zacks wrote: > I would check the template1 database to see if you had the functions > defined there as well. > Any functions/structures/data... in template1 will be moved to the new > database. Then when you try to restore the old one it will conflict. Thanks you. That is exactly what had happened. I recall on a previous exercise loading the database but forgetting to created the admin db first and it loading the whole lot into template1. I remember cleaning out all the tables etc, I must have forgotten the functions. Time to re-init the installation. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Problems setting up slony-1
I am trying to setup slony-1 and need some off list assistance from someone who has got things going ( or at least knows more than me :-). I am running 7.4.6 on both master and slave but when I run the slonik script, I get the error. :5: Initializing the cluster :7: PGRES_FATAL_ERROR create schema "_GZ"; - ERROR: permission denied for database admin :9: Could not initialize the cluster! I am using a 'slony' user which has createdb rights but the database admin was created by a different user. Any assistance, as always, much appreciated. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 27 5 424 015, Web: www.godzone.net.nz "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] zenoss monitor
Has anyone written or know of a zenoss plugin that can be used in the same way as the mySQLMonitor ? I can use the nagios one but was hoping to get some of the same sort of performance metrics as the mysql one provides. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Glen and Rosanne Eustace GodZone Internet Services, a division of AGRE Enterprises Ltd. P.O. Box 8020, Palmerston North, New Zealand 4446. Ph: +64 6 357 8168, Fax +64 6 357 8165, Mob: +64 21 424 015 http://www.godzone.net.nz "A Ministry specialising in providing low-cost Internet Services to NZ Christian Churches, Ministries and Organisations." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Running postgresql as a VMware ESx client
Hi all, I was wondering whether anyone has had any experience running postgresql in a vm under ESx. VMware provides significant HA/DR oppurtunities and we would like to use it if we can. The DBase would be on a EMC SAN hosted LUN and the ESx servers would be dual Quad CPU HP DL-380/G5s. At this stage we would use iSCSI for SAN connectivity as our testing with MS-SQL has not indicated that FC is needed. We are getting a bit of push back from the external support agency who seem more than a little bit nervous about the environment. I would appreciate any comments. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Glen and Rosanne Eustace GodZone Internet Services, a division of AGRE Enterprises Ltd. P.O. Box 8020, Palmerston North, New Zealand 4446. Ph: +64 6 357 8168, Fax +64 6 357 8165, Mob: +64 21 424 015 http://www.godzone.net.nz "A Ministry specialising in providing low-cost Internet Services to NZ Christian Churches, Ministries and Organisations." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running postgresql as a VMware ESx client
Generally speaking, virtualization allows you to take a bunch of low powered servers and make them live in one big box saving money on electricity and management. Generally speaking, database sers are big powerful boxes with lots of hard disks and gigs upon gigs of ram to handle terabytes of data. Those two things seem at odds to me. If one is handling databases with Terabytes of data and 1000s of connections, I would agree. We will be looking at 100s of Megabytes max and possible several hundred connections. A much smaller workload. When I saw you post that the ESx servers would be dual quad core machines I immediately thought how nice that would be to run pgsql on, without vmware between it and the hardware. To pilot this project we were going to run just the single VM on the server, if there are performance issues we can always rebuild and put the OS directly on the hardware rather than the ESx hypervisor. What, exactly, as you looking to gain by running pgsql under vmware on such hardware? Mobility, in the HA/DR sense. Being able to vmotion the server while it is running to rectify hardware issues or perform upgrades. To bring the server up at a DR site automagically using SRM. And yes, we have still to investigate the whole crash consistency stuff with SRM. This database is heavily biased toward reads rather than writes. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Glen and Rosanne Eustace GodZone Internet Services, a division of AGRE Enterprises Ltd. P.O. Box 8020, Palmerston North, New Zealand 4446. Ph: +64 6 357 8168, Fax +64 6 357 8165, Mob: +64 21 424 015 http://www.godzone.net.nz "A Ministry specialising in providing low-cost Internet Services to NZ Christian Churches, Ministries and Organisations." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Accessing a database via AJAX scripts
I am not sure where my issue lies - apache, perl or postgresql, but as they say, one has to start somewhere. My goal is to have a perl cgi script (that is accessed using AJAX) perform some operations in a database using DBI. Some of the actions are likely to take a while so my intent was to have a table that the backend process periodically writes status messages into. The front end web page then uses another AJAX script to watch this table. I am coming unstuck, the monitoring script works but the backend processing doesn't. My first attempts would not show anything at the front end as the initial response from the backend was not getting through the system until the backend completely finished (even having set no buffering on STDOUT). After googling abit, it would appear that the way around this is to fork the back-end to allow apache to complete the initial response and then carry on the processing in the forked child process. I have opted to use Proc::Daemon to do this. Now I am getting a Pg error "could not receive data from server: Bad file descriptor". I have attempted re-opening STDOUT and STDERR in the forked process but that didn't make any difference. I am obviously missing something here. If anyone has done something similar and has it working I'd appreciate any help I can get. Glen -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 4446 Ph: +64 6 357 8168, Fax: +64 6 357 8165, Mob: +64 27 542 4015 "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accessing a database via AJAX scripts
Hi Tom,
On 08/09/2010 03:33 AM, Tom Lane wrote:
Your note is awfully short of concrete details, but I'm guessing the
basic reason why this wasn't working for you was you were doing all the
operations as a single transaction. The results of that transaction
wouldn't be visible to another one until it commits; so in particular
whatever changes it made in the status table wouldn't be visible.
You'd need to break the processing into something like
begin;
insert into status values ('working');
commit;
begin;
... do the useful stuff here ...
commit;
begin;
insert into status values ('done');
commit;
Yes, it was a bit vague but I didn't want to go into too much detail as
I am still not sure I am even dealing with a postgresql issue.
Unfortunately your comments don't shed any light on the error I am getting;
"could not receive data from server: Bad file descriptor".
This suggests to me that I might have lost the connection in my forked
perl process to the back-end. Can you confirm or deny ? This might
give me some idea of where to keep looking.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 4446
Ph: +64 6 357 8168, Fax: +64 6 357 8165, Mob: +64 27 542 4015
"A Ministry specialising in providing low-cost professional Internet
Services to NZ Christian Churches, Ministries and Organisations"
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accessing a database via AJAX scripts
Not on that much evidence; but trying to pass a libpq connection across a fork is usually risky. The trouble is that both parent and child processes now hold copies of the open socket connection, but only one of them can safely work with it. It could be that you've closed the libpq connection in one process and that killed its usability in the other. Thanks Tom, that is pretty much the conclusion I came to. I think I need to close the db connection prior to the fork and then re-open in the new child. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Glen and Rosanne Eustace GodZone Internet Services, a division of AGRE Enterprises Ltd. P.O. Box 8020, Palmerston North, New Zealand 4446. Ph: +64 6 357 8168, Fax +64 6 357 8165, Mob: +64 27 542 4015 http://www.godzone.net.nz "A Ministry specialising in providing low-cost Internet Services to NZ Christian Churches, Ministries and Organisations." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accessing a database via AJAX scripts - solved
The solution to my problems involved both of the issues Tom identified. 1. Proc::Daemon::Init() call closed the libpq connection in the parent. Solution: deliberately close connection before call and open in the child after the call. 2. The status updates were being written inside a transaction Solution: open a second connection in the child process and use for the status updates to allow the monitor script to select them. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 4446 Ph: +64 6 357 8168, Fax: +64 6 357 8165, Mob: +64 27 542 4015 "A Ministry specialising in providing low-cost professional Internet Services to NZ Christian Churches, Ministries and Organisations" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
