Re: [GENERAL] Alternative replication method.
Mike Benoit [EMAIL PROTECTED] writes: Has anyone used PostgreSQL with Drbd (http://www.complang.tuwien.ac.at/reisner/drbd/)? Does it guarantee preservation of write ordering? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL]
Entered in the pg_hba.conf file hostall the.machine's.ip.address 255.255.255.255 and still gives me the error that it doesn't have an entry for the servers ip. Warning: Unable to connect to PostgreSQL server: FATAL: No pg_hba.conf entry for host machine's.ip user postgres, database nm in /var/www/html/crohns/phpBB2/db/postgres7.php on line 79 phpBB : Critical Error Could not connect to the database I would also like to know how to restart postmaster to reinitialize changes if someone could help me out on that. in need of help, Dan snip This is in the pg_hba.conf file www.websiteIamusing.com all themachine'sIP 255.255.255.255 trust I am sure I need to edit something yet. host is a keyword (for tcp/ip connections as opposed to keyword local for local domain socket connections), it is not a hostname. Try: host all the.machine.ip.address 255.255.255.255 Cheers, Steve ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Inheritance Indexes
On Wed, 25 Jun 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: I *think* 7.4 may be smarter about implying these conditions as well. Not really. AFAIR the Append-style plan is the only thing you can get out of the planner for inheritance trees. This works well enough for restriction clauses like id = constant (since those get pushed down to the member tables, much as with UNION ALL), but it just isn't gonna be efficient for join situations. And I can't see any realistic way for the planner to realize that only some pairs of child tables need be joined. I was actually thinking of the table1.col=table2.col and table1.col=42 implying table2.col=42 when I wrote the above because he was also wondering why it wasn't using index scans on the table2 tree. Which now that I have access to my 7.4 box again, it does appear to. ---(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]
host all the.machine's.ip.address 255.255.255.255 and still gives me the error that it doesn't have an entry for the servers ip. Warning: Unable to connect to PostgreSQL server: FATAL: No pg_hba.conf entry for host machine's.ip user postgres, database nm in Well, you of course need to replace machine's.ip with the actual IP. You did do that and just edited it out for posting on the list, right ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] change management
Well, business is good I guess. We're getting ready to deploy a few new installations of our software which is raising some questions. Most of them I have answers to, but one area of development I haven't had to deal with much is managing changes to the database structure. I would really like to know what others consider best practice for distributing changes to database structure and data. Currently we have one production server and one test server. We simply copy and paste our database change queries to get the same result on both servers. I'm also concerned with the differences of sequence values. There are a couple of tables that will need to be replicated from time to time. For now data will get copied from one master server out to several other installations. There may come a point some day when the other installations add data to these tables that may or may not need to get pushed back to the main server. Any suggestions? Matthew Nuzum | Makers of Elite Content Management System www.followers.net | View samples of Elite CMS in action [EMAIL PROTECTED] | http://www.followers.net/portfolio/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Inherits tables and current CVS
How can I know tables is inherited or no? # create table parent ( id int primary key, value text ); # create table child ( cvaltext, primary key (id) ) INHERITS (parent); # \d child Table public.child Column | Type | Modifiers +-+--- id | integer | not null value | text| cval | text| Indexes: child_pkey PRIMARY KEY btree (id) \d doesn show any info about inheritance -- Teodor Sigaev E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_statistic_relid_att_index
Jean-Christophe ARNU (JX) [EMAIL PROTECTED] writes: I've a problem with the pg_statistic_relid_att_index system index. The server version is 7.1.3 (so reindex is not available). You're overdue for an update then. Have you read the lists of bugs fixed since 7.1? regards, tom lane ---(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] Alternative replication method.
According to section 5 in this: http://www.complang.tuwien.ac.at/reisner/drbd/publications/drbd_paper_for_LK7.pdf it looks like it does guarantee write order. snip 5 Write ordering Some file systems require that certain blocks hit the media in a determined order, for example a JFS needs to write a transaction (the commit record must be last) into the journal before it does any updates to the home locations. It does this by postponing the home location updates until it knows that the writes to the journal are on stable storage. (This is done with wait_on_buffer() and/or buffer_uptodate()) From the DRBD's point of view the question is, which blocks might be reordered when writing to the secondary's disk. To ensure exactly the same write order as on the primary, we must use the following scheme: 1. Get a block from the network and put it onto the buffer cache. 2. Write that buffer and wait for IO completion. 3. Continue with 1. ... /snip On Wed, 2003-06-25 at 07:44, Tom Lane wrote: Mike Benoit [EMAIL PROTECTED] writes: Has anyone used PostgreSQL with Drbd (http://www.complang.tuwien.ac.at/reisner/drbd/)? Does it guarantee preservation of write ordering? regards, tom lane -- Best Regards, Mike Benoit NetNation Communications Inc. Systems Engineer Tel: 604-684-6892 or 888-983-6600 --- Disclaimer: Opinions expressed here are my own and not necessarily those of my employer ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] how to fix missing chunk number 0?
Title: how to fix missing chunk number 0? Howdy Migrating PostgreSQL 7.2.1 on RedHat Linux 7.2. I'm trying to dump my tables and have ran into a severe problem. When I try to pg_dump tables, they fail and I get an error: [snip] pg_dump: query to obtain definition of view t_ref_hedis_ce failed: ERROR: missing chunk number 0 for toast value 2085599783 [/snip] The problem is, I can't look at the pg_toast_2085599783 because it doesn't exist (as per the suggestions at groups.google.com). I can not vacuum or otherwise try to drop the view because I get the same error. At the same time, I looked in the ~/pgsql/data/base/19643563/OID and saw that the OID was gone. I *touched* a new one and gave it postgres:postgres ownership and tried to drop it again. I got the same error as above. How can I fix this? I really need to pg_dump some data in order to move it. Thanks! -X
Re: [GENERAL]
On 25/06/2003 15:46 Daniel E. Fisher wrote: Entered in the pg_hba.conf file hostall the.machine's.ip.address 255.255.255.255 and still gives me the error that it doesn't have an entry for the servers ip. If You're using PG 7.3, then you need a user column: host database(or all)user(or all)the.machine's.ip.address 255.255.255.255 Warning: Unable to connect to PostgreSQL server: FATAL: No pg_hba.conf entry for host machine's.ip user postgres, database nm in /var/www/html/crohns/phpBB2/db/postgres7.php on line 79 phpBB : Critical Error Could not connect to the database I would also like to know how to restart postmaster to reinitialize changes if someone could help me out on that. Depends on which *nix flavour/distribution you're using. I use Red Hat Linux and service postgresql restart works for me. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Inherits tables and current CVS
Teodor Sigaev [EMAIL PROTECTED] writes: How can I know tables is inherited or no? You have to look in pg_inherits. AFAIR psql has no \d command that will tell you anything about it. regards, tom lane ---(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] pg_statistic_relid_att_index
Oh, good. I was under the impression that 7.1.3 didn't have reindex (that's was Jean said at first, and I haven't used it in so long, I wasn't sure either.) Jean, don't forget to BACKUP first. On Wed, 25 Jun 2003, Martijn van Oosterhout wrote: On Wed, Jun 25, 2003 at 04:14:10AM -0600, scott.marlowe wrote: Looks like you'll have to dump and restore your database. :-( Nothing so drastic. 7.1 has reindex (7.0 has to 7.1 must have it too). To do it on system indexes you need to run it as single user mode. Start the a postgres process with -P which allows you to reindex system indexes. Probably drop them too, if you want to. On Wed, 25 Jun 2003, Jean-Christophe ARNU wrote: Le Wed, 25 Jun 2003 03:25:46 -0600 (MDT) scott.marlowe [EMAIL PROTECTED] me disait que : Drop and recreate the index is your only solution given the constraints you have. Thanks for your quick answer. :) I agree on this procedure to get rid of the reluctant indices but as pg_statistic_relid_att_index is a system index it seems to put another problem in the bucket : An error occurs when I try to remove this index supervisor=# drop index pg_statistic_relid_att_index; ERROR: index pg_statistic_relid_att_index is a system index If I try to do the same on template1 database, I get the same result :/ Regards ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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] Eliminating start error message: unary operator
Title: RE: [GENERAL] Eliminating start error message: unary operator
Hi Tom,
I got the following log when I tried to output the messages from pg_ctl (enclosed is the text file also). I modified the postscript script to get the log writeeen at bootup. The line of postscript script that I modified was:
su -l postgres -s /bin/sh -c /usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start /dev/null 21 /dev/null
To:
su -l postgres -s /bin/sh -c /bin/sh -x /usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start /tmp/mylog 21 /dev/null
Indeed, I do not see an error anywhere.
++ basename /usr/bin/pg_ctl
+ CMDNAME=pg_ctl
+ help=pg_ctl is a utility to start, stop, restart, reload configuration files,
or report the status of a PostgreSQL server.
Usage:
pg_ctl start [-w] [-D DATADIR] [-s] [-l FILENAME] [-o OPTIONS]
pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl restart [-w] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] [-o OPTIONS]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]
Common options:
-D DATADIR location of the database storage area
-s only print errors, no informational messages
-w wait until operation completes
-W do not wait until operation completes
--help show this help, then exit
--version output version information, then exit
(The default is to wait for shutdown, but not for start or restart.)
If the -D option is omitted, the environment variable PGDATA is used.
Options for start or restart:
-l FILENAME write (or append) server log to FILENAME. The
use of this option is highly recommended.
-o OPTIONS command line options to pass to the postmaster
(PostgreSQL server executable)
-p PATH-TO-POSTMASTER normally not necessary
Options for stop or restart:
-m SHUTDOWN-MODE may be 'smart', 'fast', or 'immediate'
Shutdown modes are:
smart quit after all clients have disconnected
fast quit directly, with proper shutdown
immediate quit without complete shutdown; will lead to recovery on restart
Report bugs to [EMAIL PROTECTED].
+ advice=Try 'pg_ctl --help' for more information.
+ bindir=/usr/bin
+ VERSION=7.3.2
+ umask 077
+ echo '\c'
+ grep -s c
+ ECHO_N=echo -n
+ ECHO_C=
+ echo /usr/bin/pg_ctl
+ grep /
++ echo /usr/bin/pg_ctl
++ sed 's,/[^/]*$,,'
+ self_path=/usr/bin
+ '[' -x /usr/bin/postmaster ']'
+ '[' -x /usr/bin/psql ']'
+ PGPATH=/usr/bin
+ po_path=/usr/bin/postmaster
+ wait=
+ wait_seconds=60
+ logfile=
+ silence_echo=
+ shutdown_mode=smart
+ '[' 7 -gt 0 ']'
+ shift
+ PGDATA=/var/lib/pgsql/data
+ export PGDATA
+ shift
+ '[' 5 -gt 0 ']'
+ shift
+ po_path=/usr/bin/postmaster
+ shift
+ '[' 3 -gt 0 ']'
+ shift
+ POSTOPTS=-p 5432
+ shift
+ '[' 1 -gt 0 ']'
+ op=start
+ shift
+ '[' 0 -gt 0 ']'
+ '[' xstart = x ']'
+ '[' -z /var/lib/pgsql/data ']'
+ '[' -z '' ']'
+ wait=no
+ sig=-TERM
+ '[' start = reload ']'
+ DEFPOSTOPTS=/var/lib/pgsql/data/postmaster.opts.default
+ POSTOPTSFILE=/var/lib/pgsql/data/postmaster.opts
+ PIDFILE=/var/lib/pgsql/data/postmaster.pid
+ '[' start = status ']'
+ '[' start = stop -o start = restart -o start = reload ']'
+ '[' start = start -o start = restart ']'
+ oldpid=
+ '[' -f /var/lib/pgsql/data/postmaster.pid ']'
+ '[' -z '-p 5432' ']'
+ eval set X '-p 5432'
++ set X -p 5432
+ shift
+ '[' -n '' ']'
+ '[' -n '' ']'
+ '[' no = yes ']'
+ echo 'postmaster successfully started'
postmaster successfully started
+ exit 0
+ /usr/bin/postmaster -p 5432
LOG: database system was shut down at 2003-06-25 13:04:50 EDT
LOG: checkpoint record is at 0/12155DD8
LOG: redo record is at 0/12155DD8; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 3002; next oid: 632310
LOG: database system is ready
-Original Message-
From: Carlos
Sent: Tuesday, June 24, 2003 1:56 PM
To: 'Tom Lane'; Carlos
Cc: '[EMAIL PROTECTED]'; Dain
Subject: RE: [GENERAL] Eliminating start error message: unary operator
Hi Tom,
Thank you very much for your help. In order to get the pg_ctl trace at start up, I would appreciate it if you could advise me on how to modify the postscrpt script. The line in question in the script is: su -l postgres -s /bin/sh -c /usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start /dev/null 21 /dev/null
Should it be something like:
su -l postgres -s /bin/sh -c /bin/sh -x /usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start mylog 21 /dev/null
Thanks in advance for your response.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Tom Lane
Sent: Tuesday, June 24, 2003 10:32 AM
To: Carlos
Cc: [EMAIL PROTECTED]; Dain
Subject: Re: [GENERAL] Eliminating start error message: unary operator
Carlos Oliva [EMAIL PROTECTED] writes:
This log has the error but I do not understand why it occurs.
Starting postgresql service: + su -l postgres -s /bin/sh -c
'/usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -o
'\''-p 5432'\'' start /dev/null 21'
-sh: [: ==: unary operator expected
[GENERAL] INSERT WHERE NOT EXISTS
Hi, I am developing application with PHP as the front end, PGSQL as the backend. I am trying to figure out what's the best way to do this. I want to check if an entry already exists in the table. If it does, then I will do UPDATE tablename otherwise, I will do INSER INTO tablename... What's the best way to do that? I can of course check first, and then put the login in PHP code, eg: // check if entry already exists SELECT COUNT(*) FROM tablename WHERE [cond] .. if($count 0) UPDATE else INSERT but this will double the hit to the database server, because for every operation I need to do SELECT COUNT(*) first. The data itself is not a lot, and the condition is not complex, but the hitting frequency is a lot. I vaguely remember in Oracle, there is something like this: INSERT INTO mytable SELECT 'value1', 'value2' FROM dummy_table WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) This query will do INSERT, if there is not an entry already in the TABLE mytable that match the condition mycondition. Otherwise, the INSERT just fails and return 0 (without returning error), so I can check on that and do update instead. This is especially useful in my case because about most of the time the INSERT will succeed, and thus will reduce the hit frequency to the DB server from PHP by probably a factor of 1.5 or so. Is there anything like that with PostgreSQL? I looked the docs and googled but haven't found anything. Anyhelp is greatly appreciated. Thanks. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] INSERT WHERE NOT EXISTS
Just wrap it in a transaction: begin; select * from table where somefield='somevalue'; (in php code) if pg_num_rows1... update table set field=value where somefield=somevalue; else insert into table (field) values (value); commit; On Wed, 25 Jun 2003, Reuben D. Budiardja wrote: Hi, I am developing application with PHP as the front end, PGSQL as the backend. I am trying to figure out what's the best way to do this. I want to check if an entry already exists in the table. If it does, then I will do UPDATE tablename otherwise, I will do INSER INTO tablename... What's the best way to do that? I can of course check first, and then put the login in PHP code, eg: // check if entry already exists SELECT COUNT(*) FROM tablename WHERE [cond] .. if($count 0) UPDATE else INSERT but this will double the hit to the database server, because for every operation I need to do SELECT COUNT(*) first. The data itself is not a lot, and the condition is not complex, but the hitting frequency is a lot. I vaguely remember in Oracle, there is something like this: INSERT INTO mytable SELECT 'value1', 'value2' FROM dummy_table WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) This query will do INSERT, if there is not an entry already in the TABLE mytable that match the condition mycondition. Otherwise, the INSERT just fails and return 0 (without returning error), so I can check on that and do update instead. This is especially useful in my case because about most of the time the INSERT will succeed, and thus will reduce the hit frequency to the DB server from PHP by probably a factor of 1.5 or so. Is there anything like that with PostgreSQL? I looked the docs and googled but haven't found anything. Anyhelp is greatly appreciated. Thanks. RDB ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] INSERT WHERE NOT EXISTS
On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote: Hi, I am developing application with PHP as the front end, PGSQL as the backend. I am trying to figure out what's the best way to do this. I want to check if an entry already exists in the table. If it does, then I will do UPDATE tablename otherwise, I will do INSER INTO tablename... (...) I vaguely remember in Oracle, there is something like this: INSERT INTO mytable SELECT 'value1', 'value2' FROM dummy_table WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) This query will do INSERT, if there is not an entry already in the TABLE mytable that match the condition mycondition. Otherwise, the INSERT just fails and return 0 (without returning error), so I can check on that and do update instead. This kind of query should work; just leave out the FROM dummy_table bit. (in Oracle it would be FROM dual). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] INSERT WHERE NOT EXISTS
On Wednesday 25 June 2003 03:26 pm, Ian Barwick wrote: snip I vaguely remember in Oracle, there is something like this: INSERT INTO mytable SELECT 'value1', 'value2' FROM dummy_table WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) This query will do INSERT, if there is not an entry already in the TABLE mytable that match the condition mycondition. Otherwise, the INSERT just fails and return 0 (without returning error), so I can check on that and do update instead. This kind of query should work; just leave out the FROM dummy_table bit. (in Oracle it would be FROM dual). Hi, this seems to work. Thanks. Don't know why I didn't just try it. And yes, in Oracle it's SELECT .. FROM dual. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(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] INSERT WHERE NOT EXISTS
On Wednesday 25 June 2003 21:37, Mike Mascari wrote: Ian Barwick wrote: On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote: (...) This kind of query should work; just leave out the FROM dummy_table bit. (in Oracle it would be FROM dual). I proposed that same solution 3 years ago. Tom shoots it down: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=3A4D611 6.1A613402%40mascari.comrnum=1prev=/groups%3Fq%3DMike%2BMascari%2BINSERT%2 BNOT%2BEXISTS%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den Reuben must be prepared for unique key violation, I'm afraid. And, despite the optimism in the link, we still don't have savepoints. :-( aha, useful to know. Thanks. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
