Re: [GENERAL] pg_dump with select command
Hi, On 12 September 2011 15:03, Adarsh Sharma adarsh.sha...@orkash.com wrote: Today I need some part ( subset ) of some tables to another database to a remote server. I need to take backup of tables after satisfying a select query. Is there any option to specify query in pg_dump command.I researched in the manual but not able to find that. Please let me know if it is possible as we can can specify in mysqldump command. No, pg_dump can dump full tables only. You can use psql: psql -h host1 ... -c 'copy (select ... from tablename where ...) to stdout' | psql -h host2 ... -c 'copy tablename from stdin' (where '...' are other psql's options like user, db, ...) -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] servoy-postgresql plugin
On Mon, Sep 12, 2011 at 5:25 AM, Rogel Nocedo rog...@theservicemanager.com wrote: Is there a way that I can use the postgresql commands like pg_dump etc in servoy? I am tasked to do a restore/backup database utility programmatically for our users. It’s a little painful to do a select for the table and insert scripts based on the resultset. Probably need to ask on the Servoy list. If it can run a program, it can do this. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] (replication) Detecting if server a slave, or a master in recovery
Hi, What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_location() and pg_last_xlog_replay_location() functions. However it occurs to me that a master server, which has been roughly rebooted and is replaying its WAL files will probably give the same response as a slave, when I call the current_xlog_location(). What is the best method for determining whether you're connected to the master or slave database? Thanks, Toby -- 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] (replication) Detecting if server a slave, or a master in recovery
On 12/09/11 16:54, Toby Corkindale wrote: Hi, What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_location() and pg_last_xlog_replay_location() functions. However it occurs to me that a master server, which has been roughly rebooted and is replaying its WAL files will probably give the same response as a slave, when I call the current_xlog_location(). What is the best method for determining whether you're connected to the master or slave database? It's disappointing that I can't query standby_mode in psql. ie. SHOW standby_mode; Toby -- 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] (replication) Detecting if server a slave, or a master in recovery
On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_location() and pg_last_xlog_replay_location() functions. However it occurs to me that a master server, which has been roughly rebooted and is replaying its WAL files will probably give the same response as a slave, when I call the current_xlog_location(). What is the best method for determining whether you're connected to the master or slave database? SELECT pg_is_in_recovery(); -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] (replication) Detecting if server a slave, or a master in recovery
On 12/09/11 17:13, Simon Riggs wrote: On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_location() and pg_last_xlog_replay_location() functions. However it occurs to me that a master server, which has been roughly rebooted and is replaying its WAL files will probably give the same response as a slave, when I call the current_xlog_location(). What is the best method for determining whether you're connected to the master or slave database? SELECT pg_is_in_recovery(); If I'm on a master database, which had previously crashed and is now in the process of recovery, won't that also return true there? -- 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] (replication) Detecting if server a slave, or a master in recovery
On Mon, Sep 12, 2011 at 8:19 AM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: On 12/09/11 17:13, Simon Riggs wrote: On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale What is the best method for determining whether you're connected to the master or slave database? SELECT pg_is_in_recovery(); If I'm on a master database, which had previously crashed and is now in the process of recovery, won't that also return true there? No, because you can't connect to the database during crash recovery, so the first time you can connect to a has-crashed master it will return false. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] (replication) Detecting if server a slave, or a master in recovery
On 12/09/11 17:27, Simon Riggs wrote: On Mon, Sep 12, 2011 at 8:19 AM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: On 12/09/11 17:13, Simon Riggs wrote: On Mon, Sep 12, 2011 at 7:54 AM, Toby Corkindale What is the best method for determining whether you're connected to the master or slave database? SELECT pg_is_in_recovery(); If I'm on a master database, which had previously crashed and is now in the process of recovery, won't that also return true there? No, because you can't connect to the database during crash recovery, so the first time you can connect to a has-crashed master it will return false. aaah.. Thanks! I didn't realise that. Thanks for your help. -Toby -- 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] writing block 6850 of relation 1663/17231/1259
Thanks Bruce. If I upgrade the pg sql will it fix the problem - quite happy to do if it does. Else need way to recover the database. After some googling I found that 1259 represents PG_CLASS table. This drift me towards the conclusion that this table is in unstable state. I have run pg_resetxlog couple of times without any switches like -o,-x,-l and it has not helped. I'm going to run it again with switches and see how it goes. Thanks, Mudit -Original Message- From: Bruce Momjian [mailto:br...@momjian.us] Sent: 12 September 2011 02:37 To: Mudit Mishra Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] writing block 6850 of relation 1663/17231/1259 Mudit Mishra wrote: Sorry forgot to change title. Any help will be greatly received! -Original Message- From: Mudit Mishra Sent: 11 September 2011 14:39 To: pgsql-general@postgresql.org Subject: RE: [GENERAL] COPY FROM (query) in plpgsql Hi All, Can anyone help me to fix this issue? I keep getting following errors in the log. I do not have a backup of the database and want to recover the database as much as possible from this error. 2011-09-09 11:06:39 PANIC: xlog flush request 2/190490D8 is not satisfied --- flushed only to 2/19004190 2011-09-09 11:06:39 CONTEXT: writing block 6850 of relation 1663/17231/1259 OS: Windows 2003 server pgsql version: Welcome to psql 8.0.3, the PostgreSQL interactive terminal. FYI, that is a very old version of Postgres and you have not even minor-upgraded it in years. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] writing block 6850 of relation 1663/17231/1259
It probably won't fix it, but you'll avoid possible issues in the future. However you should look at possibly upgrading to 8.4 or later, as 8.0 is either out of its support life, or getting close to it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Has Pg 9.1.0 been released today?
http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: Release Date: 2011-09-12 *bounces excitedly* Has the release candidate gone final today? -Toby -- 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] (replication) Detecting if server a slave, or a master in recovery
On Mon, 2011-09-12 at 17:09 +1000, Toby Corkindale wrote: On 12/09/11 16:54, Toby Corkindale wrote: Hi, What is the correct way to tell what mode a replicated Pg server is currently in, via a database connection? I can take a guess if it's the master or a slave by using the pg_current_xlog_location() and pg_last_xlog_replay_location() functions. However it occurs to me that a master server, which has been roughly rebooted and is replaying its WAL files will probably give the same response as a slave, when I call the current_xlog_location(). What is the best method for determining whether you're connected to the master or slave database? It's disappointing that I can't query standby_mode in psql. ie. SHOW standby_mode; I agree. Actually, you can't get the value of any parameter set in recovery.conf. But Fuji Masao seems to work on it: he posted a patch to unite recovery.conf and postgresql.conf (see unite recovery.conf and postgresql.conf thread on pgsql-hackers). And I guess it'll help us querying these parameters' values. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Has Pg 9.1.0 been released today?
On Mon, Sep 12, 2011 at 10:10, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: Release Date: 2011-09-12 *bounces excitedly* Has the release candidate gone final today? Not yet. But we are planning to put it out, and we need to load the website documentation ahead of time. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Has Pg 9.1.0 been released today?
On Mon, Sep 12, 2011 at 10:40, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 12/09/11 20:31, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:10, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: Release Date: 2011-09-12 *bounces excitedly* Has the release candidate gone final today? Not yet. But we are planning to put it out, and we need to load the website documentation ahead of time. Then how come was put on the download page over 24 hours ago? We always put the files up on the ftpsite some time ahead to make sure it hits all the mirrors. It's not officially released (and guaranteed) until you see the announcement. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Has Pg 9.1.0 been released today?
On Mon, Sep 12, 2011 at 10:50, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 12/09/11 20:44, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:40, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 12/09/11 20:31, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:10, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: Release Date: 2011-09-12 *bounces excitedly* Has the release candidate gone final today? Not yet. But we are planning to put it out, and we need to load the website documentation ahead of time. Then how come was put on the download page over 24 hours ago? We always put the files up on the ftpsite some time ahead to make sure it hits all the mirrors. It's not officially released (and guaranteed) until you see the announcement. So there is a probability (presumably very small) that the source may change - if a significant problem is discoved late in the process, but one that can be quickly fixed? Yes. More interesting: how likely is the source to change, and what are the general guidelines associated with such a change? It has happened once or twice in the past, but very seldom. What happens is the version is removed again, a fix is applied, and a re-release is done with a new version number. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pgpool outdated spec
Hi, I'm doing a rebuild to a rpm package postgresql-9.0 with pgpool but your spec is outdated, there is some spec updated or have to make my changes manually? -- * Emanuel Araújo* http://eacshm.wordpress.com/ * * *Linux Certified LPIC-1*
Re: [GENERAL] Pgpool outdated spec
I'm doing a rebuild to a rpm package postgresql-9.0 with pgpool but your spec is outdated, there is some spec updated or have to make my changes manually? Devrim is in charge of updating the spec file. Devrim? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Pgpool outdated spec
On Mon, 2011-09-12 at 08:07 -0300, Emanuel Araújo wrote: I'm doing a rebuild to a rpm package postgresql-9.0 with pgpool but your spec is outdated, there is some spec updated or have to make my changes manually? Please hold on for 2-3 days. PgPool is the first RPM that I will need to update, however I'm waiting for 9.1.0 to be releases first. I will release packages before Thursday. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Has Pg 9.1.0 been released today?
On 12/09/11 20:44, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:40, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 12/09/11 20:31, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:10, Toby Corkindale toby.corkind...@strategicdata.com.auwrote: http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: Release Date: 2011-09-12 *bounces excitedly* Has the release candidate gone final today? Not yet. But we are planning to put it out, and we need to load the website documentation ahead of time. Then how come was put on the download page over 24 hours ago? We always put the files up on the ftpsite some time ahead to make sure it hits all the mirrors. It's not officially released (and guaranteed) until you see the announcement. So there is a probability (presumably very small) that the source may change - if a significant problem is discoved late in the process, but one that can be quickly fixed? More interesting: how likely is the source to change, and what are the general guidelines associated with such a change? Cheers, Gavin -- 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] Has Pg 9.1.0 been released today?
On 12/09/11 20:31, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:10, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: Release Date: 2011-09-12 *bounces excitedly* Has the release candidate gone final today? Not yet. But we are planning to put it out, and we need to load the website documentation ahead of time. Then how come was put on the download page over 24 hours ago? I already have it installed! -- 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] Has Pg 9.1.0 been released today?
On Mon, 2011-09-12 at 20:40 +1200, Gavin Flower wrote: Not yet. But we are planning to put it out, and we need to load the website documentation ahead of time. Then how come was put on the download page over 24 hours ago? I already have it installed! PostgreSQL source code ships with no warranty ;) -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] 8.4.4 locked after power failure
So here's the thing. I got a message from one of the developers, that running 'create temporary sequence xyz;' hangs on the database. That seemed suspicious. I tried running any ddl command, and that hang. No other connections to the database. It turned out that it had a power failure earlier in the morning. That seems ok, but in the past postgresql will always recover fine (at least 8.3.x). This time I had to reindex, and vacuum all user and system catalogues to get system back in order. Any ideas ? Is that something new, is it fixed in any newer releases ? Unfortunately it had to be done rather quick - so I couldn't salvage any data. -- GJ -- 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] Has Pg 9.1.0 been released today?
On Monday, September 12, 2011 1:50:24 am Gavin Flower wrote: So there is a probability (presumably very small) that the source may change - if a significant problem is discoved late in the process, but one that can be quickly fixed? It has been officially released per announcement on pgsql-announce. More interesting: how likely is the source to change, and what are the general guidelines associated with such a change? Cheers, Gavin -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Index Corruption
We moved to PostgreSQL about 2 years ago and have been very happy with it overall. The only major issue that we've had is intermittent index corruption. This manifests itself as either duplicate key value violates unique constraint or could not read block 37422 of relation 1663/18663/19063: read only 0 of 8192 bytes. REINDEXing the table solves the problem. We do sometimes have bugs that cause unique index violations, so the first error is especially annoying. We've encountered the problem with both JDBC and libpq clients. The problem has persisted through upgrades to our database servers (from 32bit CentOS 5.3 with PostgreSQL 8.3.9 to 64bit CentOS 5.6 with PostgreSQL 8.4.8, all with stock kernels). Our database servers are fully virtualized, running under VMware Server on Dell PowerEdge Servers. We use battery backed raid controllers (PERC4/5/6), configured for RAID 10. We also experienced the problem when we had physical database servers. Servers all have 2 CPUs. PostgreSQL is installed from the PGDG RPMs (yum.postgresql.org). Our only non-default postgresql.conf options are the typical tunables: shared_buffers, checkpoint_segments, effective_cache_size, default_statistics_target, etc. WAL settings are defaults (save checkpoint_segments). No clustering, no addons. We've tuned autovacuum to be more aggressive in an attempt to address some bloat issues; this didn't seem to have any impact on the frequency of index corruption. The databases are unloaded nightly. We do a weekly pg_dumpall as an integrity check. We've only seen the problem in our many weekly batch processes. Typically, these processes DELETE a large subset of data from a table and then repopulate with the same values in indexed fields. Depending on the dataset, they run from 30 minutes to 8 hours. These processes are scheduled so that only one program (which is single threaded) is updating any given table at a time. We have 12 database servers and we usually have about 1 incident per week. Sometimes we'll go for weeks without any occurrences, and then we'll have a flurry of them. My primary question: is this normal? There isn't an overwhelming amount of messages in the archives about index corruption, which leads me to think that there's something with our configuration or our processes that is making us more susceptible. Is there something we should be doing to make index corruption less likely? Is there anyway to do an index integrity check so we can be more proactive with REINDEXing? Thanks, dylan -- 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] Has Pg 9.1.0 been released today?
On 13/09/11 01:58, Devrim GÜNDÜZ wrote: On Mon, 2011-09-12 at 20:40 +1200, Gavin Flower wrote: Not yet. But we are planning to put it out, and we need to load the website documentation ahead of time. Then how come was put on the download page over 24 hours ago? I already have it installed! PostgreSQL source code ships with no warranty ;) You mean I don't get my money back if I don't like it??? :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Has Pg 9.1.0 been released today?
On Tue, 2011-09-13 at 02:04 +1200, Gavin Flower wrote: PostgreSQL source code ships with no warranty ;) You mean I don't get my money back if I don't like it??? :-) :) FWIW, 9.1.0 was just officially announced. Enjoy! -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] 8.4.4 locked after power failure
2011/9/12 Grzegorz Jaśkiewicz gryz...@gmail.com: So here's the thing. I got a message from one of the developers, that running 'create temporary sequence xyz;' hangs on the database. That seemed suspicious. I tried running any ddl command, and that hang. No other connections to the database. It turned out that it had a power failure earlier in the morning. That seems ok, but in the past postgresql will always recover fine (at least 8.3.x). This time I had to reindex, and vacuum all user and system catalogues to get system back in order. Any ideas ? Is that something new, is it fixed in any newer releases ? Unfortunately it had to be done rather quick - so I couldn't salvage any data. It seems odd that you could not create a temp sequence but you were able to reindex the entire database. did you confirm you were blocking on a non-granted lock? merlin -- 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] Index Corruption
Dylan Adams dylan.adams.w...@gmail.com writes: [ persistent occurrences of index corruption ] My primary question: is this normal? No. It does sound like you're managing to tickle some bug or other. Can you extract a test case of any kind? We could fix it if we could see it happening, but there's not enough information here for that. regards, tom lane -- 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] Has Pg 9.1.0 been released today?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 You mean I don't get my money back if I don't like it??? :-) Are you kidding? You get *twice* your money back, and you get to keep the product! - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201109121049 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk5uHA0ACgkQvJuQZxSWSsh9uACg7tzTcjoPE7z8BMU4SUw++W+Z BA4AnAqFLDpT7i6W7enD33enDkjPoH9A =lK9t -END PGP SIGNATURE- -- 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] Index Corruption
Hi. Do you have triggers on corrupted tables? 2011/9/12, Dylan Adams dylan.adams.w...@gmail.com: We moved to PostgreSQL about 2 years ago and have been very happy with it overall. The only major issue that we've had is intermittent index corruption. This manifests itself as either duplicate key value violates unique constraint or could not read block 37422 of relation 1663/18663/19063: read only 0 of 8192 bytes. REINDEXing the table solves the problem. We do sometimes have bugs that cause unique index violations, so the first error is especially annoying. We've encountered the problem with both JDBC and libpq clients. The problem has persisted through upgrades to our database servers (from 32bit CentOS 5.3 with PostgreSQL 8.3.9 to 64bit CentOS 5.6 with PostgreSQL 8.4.8, all with stock kernels). Our database servers are fully virtualized, running under VMware Server on Dell PowerEdge Servers. We use battery backed raid controllers (PERC4/5/6), configured for RAID 10. We also experienced the problem when we had physical database servers. Servers all have 2 CPUs. PostgreSQL is installed from the PGDG RPMs (yum.postgresql.org). Our only non-default postgresql.conf options are the typical tunables: shared_buffers, checkpoint_segments, effective_cache_size, default_statistics_target, etc. WAL settings are defaults (save checkpoint_segments). No clustering, no addons. We've tuned autovacuum to be more aggressive in an attempt to address some bloat issues; this didn't seem to have any impact on the frequency of index corruption. The databases are unloaded nightly. We do a weekly pg_dumpall as an integrity check. We've only seen the problem in our many weekly batch processes. Typically, these processes DELETE a large subset of data from a table and then repopulate with the same values in indexed fields. Depending on the dataset, they run from 30 minutes to 8 hours. These processes are scheduled so that only one program (which is single threaded) is updating any given table at a time. We have 12 database servers and we usually have about 1 incident per week. Sometimes we'll go for weeks without any occurrences, and then we'll have a flurry of them. My primary question: is this normal? There isn't an overwhelming amount of messages in the archives about index corruption, which leads me to think that there's something with our configuration or our processes that is making us more susceptible. Is there something we should be doing to make index corruption less likely? Is there anyway to do an index integrity check so we can be more proactive with REINDEXing? Thanks, dylan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with the 9.1 one-click installer Windows7 64bit
Hi, I tried to install 9.1 on a Windows7 64bit machine but the installation hangs during the initdb process. Looking at the taskmanager I could see that the installer script was waiting for icacls.exe to complete setting the approriate permissions on the data directory. As I know that problems during initdb are usually a sign of some permission problems, I pre-created the data directory and manually changed the owner of that directory to be the one of the postgres service account (which is a *local* user, not a domain user) With the second attempt, the installer again hang during initdb. Checking the state using ProcessExplorer I could see that the installer script was waiting for icacls.exe to set permissions for the user currently running the installer. It was running [icacls.exe thomas] instead of [icacls.exe mydomain\thomas] - although I have to admit that I don't know if that would make a difference. So I killed the iacls.exe and the script proceeded, just to hang at the next call to icacls.exe when it tried to set the privileges on the directory for the postgres user despite the fact that that user already was the owner and had full control over it. So I killed icacls.exe again and then the script finally finished without problems. The service was registered and successully started. UAC is turned off on my computer. Btw: is there a way to run the one-click installer *without* the automatically running initdb? I did not have these problems on the same computer with the 9.0 installer - the only difference is that I installed the 32bit version of 9.0, while I know decided to use the 64bit version. Could that make a difference? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Two 9.1 Questions
Hello, Now that 9.1 has been released, I have two questions: 1. Can we upgrade a 9.0 hot standby (replicating from a 9.0 master) to a 9.1 standby while still replicating from the 9.0 master? Are there any version differences that would cause a problem? We would then eventually promote the 9.1 machine to the master... 2. Is it possible to do something like the following: A. Pause Replication on a hot standby (using the pause_at_recovery_target recovery target setting) B. Call pg_dumpall on the slave C. Resume replication (by disabling the pause_at_recovery_target recovery target setting) During the pause, we would likely continue shipping WAL to the slave (unless this is unadvisable). Is this a reasonable approach? Thank you, Andrew -- 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] Two 9.1 Questions
On Mon, Sep 12, 2011 at 4:52 PM, Andrew Hannon ahan...@fiksu.com wrote: Hello, Now that 9.1 has been released, I have two questions: 1. Can we upgrade a 9.0 hot standby (replicating from a 9.0 master) to a 9.1 standby while still replicating from the 9.0 master? Are there any version differences that would cause a problem? We would then eventually promote the 9.1 machine to the master... Not yet. That's a planned feature awaiting funding. 2. Is it possible to do something like the following: A. Pause Replication on a hot standby (using the pause_at_recovery_target recovery target setting) B. Call pg_dumpall on the slave C. Resume replication (by disabling the pause_at_recovery_target recovery target setting) During the pause, we would likely continue shipping WAL to the slave (unless this is unadvisable). Is this a reasonable approach? It will work... please read about the functions for pause/resume. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] 8.4.4 locked after power failure
2011/9/12 Merlin Moncure mmonc...@gmail.com: It seems odd that you could not create a temp sequence but you were able to reindex the entire database. did you confirm you were blocking on a non-granted lock? I could revacuum/reindex all stuff, only if I had to do the system catalogues first. That seemed a bit suspicious. Also, I had to stop all other connections - because any other locked transaction would prevent it from going on. Oh, the last thing - pg_cancel_backend() couldn't kill the locked backend. I had to restart the whole postgresql using -m immediate. Odd stuff indeed, as I was hoping postgresql will clean things like locks on recovery from crash. Never happened to me with 8.3 -- GJ -- 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] Index Corruption
On Mon, Sep 12, 2011 at 9:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: Dylan Adams dylan.adams.w...@gmail.com writes: [ persistent occurrences of index corruption ] My primary question: is this normal? No. It does sound like you're managing to tickle some bug or other. Can you extract a test case of any kind? We could fix it if we could see it happening, but there's not enough information here for that. I haven't been able to come up with a self contained test case. There have been a few instances where a particular series of batch processes which, when run repeatedly on a particular data set, will reproduce the problem consistently. But it's not possible to release the required code and data. dylan -- 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] Index Corruption
2011/9/12 pasman pasmański pasma...@gmail.com: Hi. Do you have triggers on corrupted tables? I haven't checked all the occurences, but at least some of the tables that have had corrupted index have no triggers. dylan -- 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] Index Corruption
On 9/12/2011 1:10 PM, Dylan Adams wrote: On Mon, Sep 12, 2011 at 9:41 AM, Tom Lanet...@sss.pgh.pa.us wrote: Dylan Adamsdylan.adams.w...@gmail.com writes: [ persistent occurrences of index corruption ] My primary question: is this normal? No. It does sound like you're managing to tickle some bug or other. Can you extract a test case of any kind? We could fix it if we could see it happening, but there's not enough information here for that. I haven't been able to come up with a self contained test case. There have been a few instances where a particular series of batch processes which, when run repeatedly on a particular data set, will reproduce the problem consistently. But it's not possible to release the required code and data. dylan How about some specifics about the process? Maybe I can work up a look-a-like. Something like: we have two clients that insert as fast as possible into this temp table: create table; we have 5 clients select/insert/delete from temp into live table that looks like : create table; I'll post my scripts and you can yea/nea them until we get close, maybe find the problem along the way. I would not need data or code, but actual table structure's sure would be swell. -Andy -- 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] Problem with the 9.1 one-click installer Windows7 64bit
On Mon, Sep 12, 2011 at 10:12 AM, Thomas Kellerer spam_ea...@gmx.net wrote: Hi, I tried to install 9.1 on a Windows7 64bit machine but the installation hangs during the initdb process. Looking at the taskmanager I could see that the installer script was waiting for icacls.exe to complete setting the approriate permissions on the data directory. As I know that problems during initdb are usually a sign of some permission problems, I pre-created the data directory and manually changed the owner of that directory to be the one of the postgres service account (which is a *local* user, not a domain user) With the second attempt, the installer again hang during initdb. Checking the state using ProcessExplorer I could see that the installer script was waiting for icacls.exe to set permissions for the user currently running the installer. It was running [icacls.exe thomas] instead of [icacls.exe mydomain\thomas] - although I have to admit that I don't know if that would make a difference. So I killed the iacls.exe and the script proceeded, just to hang at the next call to icacls.exe when it tried to set the privileges on the directory for the postgres user despite the fact that that user already was the owner and had full control over it. So I killed icacls.exe again and then the script finally finished without problems. The service was registered and successully started. UAC is turned off on my computer. Btw: is there a way to run the one-click installer *without* the automatically running initdb? I did not have these problems on the same computer with the 9.0 installer - the only difference is that I installed the 32bit version of 9.0, while I know decided to use the 64bit version. Could that make a difference? hm, why is icacls hanging? does it do that if you run it from the command line? merlin -- 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] Has Pg 9.1.0 been released today?
Congratulations on the release of 9.1.0! Lots of great features, I for one can't wait to try out unlogged tables, that should help a lot in our environment. Now that you have streaming replication both async and sync, are you working on multi-master replication? *excited* Or what's the roadmap? Thanks again and keep up the great work! Aleksey -- 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] Index Corruption
On Mon, Sep 12, 2011 at 1:20 PM, Andy Colson a...@squeakycode.net wrote: On 9/12/2011 1:10 PM, Dylan Adams wrote: On Mon, Sep 12, 2011 at 9:41 AM, Tom Lanet...@sss.pgh.pa.us wrote: Dylan Adamsdylan.adams.w...@gmail.com writes: [ persistent occurrences of index corruption ] My primary question: is this normal? No. It does sound like you're managing to tickle some bug or other. Can you extract a test case of any kind? We could fix it if we could see it happening, but there's not enough information here for that. I haven't been able to come up with a self contained test case. There have been a few instances where a particular series of batch processes which, when run repeatedly on a particular data set, will reproduce the problem consistently. But it's not possible to release the required code and data. dylan How about some specifics about the process? Maybe I can work up a look-a-like. I sincerely appreciate your offer, but I don't think it will help. I've tried to replicate what these are processes are doing as a standalone program and haven't been able to replicate the problem. Maybe there's something about the order or timing of the operations that I'm not capturing. I'll take another look at my test program and see if I can get it to trigger the problem. dylan -- 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] Index Corruption
On Mon, Sep 12, 2011 at 8:03 AM, Dylan Adams dylan.adams.w...@gmail.com wrote: We moved to PostgreSQL about 2 years ago and have been very happy with it overall. The only major issue that we've had is intermittent index corruption. This manifests itself as either duplicate key value violates unique constraint or could not read block 37422 of relation 1663/18663/19063: read only 0 of 8192 bytes. REINDEXing the table solves the problem. We do sometimes have bugs that cause unique index violations, so the first error is especially annoying. We've encountered the problem with both JDBC and libpq clients. The problem has persisted through upgrades to our database servers (from 32bit CentOS 5.3 with PostgreSQL 8.3.9 to 64bit CentOS 5.6 with PostgreSQL 8.4.8, all with stock kernels). Our database servers are fully virtualized, running under VMware Server on Dell PowerEdge Servers. We use battery backed raid controllers (PERC4/5/6), configured for RAID 10. We also experienced the problem when we had physical database servers. Servers all have 2 CPUs. Are you sure you aren't having either server or RAID problems of some kind? Single bit memory errors or bad sectors not getting remapped before corrupting data etc? Have you torture tested your hardware to ensure it's rock solid stable? Dell's insistence on using non buffered memory has cause me untold problems with single bit errors in the past. I don't know if they still use unbuffered memory in their servers or not as I gave up on Dell three or four years ago for servers and support. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unlogged table restart
Hi, great to see unlogged tables. There was discussion in the lists a while ago about various options for what would happen on server restart. I understand after a crash they'll be truncated but what about after a clean restart? Are they guaranteed to retain all committed data? If so I'll definitely use them for frequently written logs, if not I may still do so but will need some extra backup steps. Regards Oliver GTwM oli...@gtwm.co.uk (sent from iPad) -- 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] Unlogged table restart
On Mon, Sep 12, 2011 at 3:56 PM, Oliver Kohll oliver.li...@gtwm.co.uk wrote: Hi, great to see unlogged tables. There was discussion in the lists a while ago about various options for what would happen on server restart. I understand after a crash they'll be truncated but what about after a clean restart? Are they guaranteed to retain all committed data? If so I'll definitely use them for frequently written logs, if not I may still do so but will need some extra backup steps. Per the documentation they are only cleared following an unclean shutdown...(http://www.postgresql.org/docs/9.1/static/sql-createtable.html) merlin -- 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] Index Corruption
On Mon, Sep 12, 2011 at 3:51 PM, Scott Marlowe scott.marl...@gmail.com wrote: Are you sure you aren't having either server or RAID problems of some kind? Single bit memory errors or bad sectors not getting remapped before corrupting data etc? Have you torture tested your hardware to ensure it's rock solid stable? Dell's insistence on using non buffered memory has cause me untold problems with single bit errors in the past. I don't know if they still use unbuffered memory in their servers or not as I gave up on Dell three or four years ago for servers and support. Current servers (R710s) use DDR3 - Synchronous Registered (Buffered) ECC memory. We don't think there's anything wrong with the hardware, as the problem has persisted across various physical servers (the R710s replaced 2950s). We also don't have any other intermittent issues (postgres crashes, application server crashes, etc) that would indicate random bit errors. Thanks, dylan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compatibility 9.1rc and 9.1.0
Hi. I have the cluster created under 9.1rc version. When i do upgrade to 9.1.0, i can only reinstall binaries or i should import data too? -- pasman -- 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] Compatibility 9.1rc and 9.1.0
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I have the cluster created under 9.1rc version. When i do upgrade to 9.1.0, i can only reinstall binaries or i should import data too? Should work to just update the binaries. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign key check only if not null?
Hi, I bet this is a simple solution but I have been racking my brains. I have a column in my table: user_id varchar(100) ; This can be NULL, or it can have a value. If it has a value during INSERT or UPDATE, I want to check that the user exists against my Users table. Otherwise, NULL is ok. (Because the functionality in question is open to both unregistered and registered users). Any idea on how I can implement a FOREIGN KEY constraint? Or do I need a pre-insert and pre-update RULE for this? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.0, 9.1 RPM based parallel execution?
so I have a centos6 machine running the 9.0.latest from the yum.postgresql.org repo... I'd like to bring up 9.1 on it in parallel, on a different port and directory.but I still want 9.0 to be the default install... I was looking at the PG wiki and didn't see this explained. does anyone know of a 'howto' explaining this? or, if Devrim or someone wants to explain it here, I'll endeavor to update the wiki after testing it on my system... -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Foreign key check only if not null?
hi, fks do just that. you can create your fk with just one command: alter table xxx add constraint fk_name foreign key (user_id) references users (id); parent table's id field should also be of the same type and also it should be primary key or at least unique. you can create your pk with (if you don't already have one): alter table users add constraint pk_users primary key (id); also i wouldn't use a varchar(100) as a pk field, i would suggest using some other datatype maybe a bigint, but that always finally depends on the model, like if there is a really good reason for using a varchar(100), well, it's your call. regards, eduardo On Mon, Sep 12, 2011 at 10:48 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: Hi, I bet this is a simple solution but I have been racking my brains. I have a column in my table: user_id varchar(100) ; This can be NULL, or it can have a value. If it has a value during INSERT or UPDATE, I want to check that the user exists against my Users table. Otherwise, NULL is ok. (Because the functionality in question is open to both unregistered and registered users). Any idea on how I can implement a FOREIGN KEY constraint? Or do I need a pre-insert and pre-update RULE for this? Thanks! -- 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] Foreign key check only if not null?
On Mon, Sep 12, 2011 at 6:48 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: I have a column in my table: user_id varchar(100) ; This can be NULL, or it can have a value. If it has a value during INSERT or UPDATE, I want to check that the user exists against my Users table. Otherwise, NULL is ok. (Because the functionality in question is open to both unregistered and registered users). Any idea on how I can implement a FOREIGN KEY constraint? This sounds like an ordinary foreign key constraint. Just be sure that you drop the null constraint on the table's user_id column. So: ALTER TABLE my table ADD CONSTRAINT my table_Users_user_id_fkey FOREIGN KEY (user_id) REFERENCES Users (user_id) ON UPDATE CASCADE ON DELETE SET NULL, ALTER COLUMN user_id DROP NOT NULL; -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date.
On 9/12/2011 9:54 PM, Reid Thompson wrote: Ack -- i flubbed the subject and sample. The sample data should be val val2date 11 2011-01-01 22 2011-01-02 33 2011-01-03 41 2011-01-04 52 2011-01-05 53 2011-01-01 41 2011-01-02 62 2011-01-03 43 2011-01-04 31 2011-01-05 22 2011-01-06 43 2011-01-07 61 2011-01-08 42 2011-01-09 53 2011-01-01 21 2011-01-02 42 2011-01-03 23 2011-01-04 11 2011-01-01 22 2011-01-02 33 2011-01-03 41 2011-01-04 32 2011-01-05 13 2011-01-01 21 2011-01-02 32 2011-01-03 43 2011-01-04 54 2012-01-01 resultset: 13 2011-01-01 22 2011-01-06 31 2011-01-05 42 2011-01-09 52 2011-01-05 61 2011-01-08 where any one of these 3 11 2011-01-01 11 2011-01-01 13 2011-01-01 or any one of these 2 31 2011-01-05 32 2011-01-05 are suitable for val = 1, val = 3 respectively. sigh -- looks like I fat fingered one of my resultset values above. But, I think this gives me what I want: test=# select distinct on (val1) val1, val2, val3 from (SELECT max(val3) OVER (PARTITION BY val1), * FROM sampledata) as sq where val3 = max order by val1; val1 | val2 |val3 --+--+ 1| 3| 2011-01-01 2| 2| 2011-01-06 3| 2| 2011-01-05 4| 2| 2011-01-09 5| 4| 2012-01-01 6| 1| 2011-01-08 (6 rows) val1 | val2 |val3 --+--+ 1| 3| 2011-01-01 1| 1| 2011-01-01 1| 1| 2011-01-01 2| 2| 2011-01-02 2| 1| 2011-01-02 2| 3| 2011-01-04 2| 1| 2011-01-02 2| 2| 2011-01-06 2| 2| 2011-01-02 3| 3| 2011-01-03 3| 3| 2011-01-03 3| 2| 2011-01-05 3| 1| 2011-01-05 3| 2| 2011-01-03 4| 1| 2011-01-04 4| 1| 2011-01-02 4| 3| 2011-01-04 4| 3| 2011-01-07 4| 3| 2011-01-04 4| 2| 2011-01-09 4| 1| 2011-01-04 4| 2| 2011-01-03 5| 4| 2012-01-01 5| 2| 2011-01-05 5| 3| 2011-01-01 5| 3| 2011-01-01 6| 1| 2011-01-08 6| 2| 2011-01-03 (28 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump
Hi! How can I please a directory folder where my backup files will be placed? I am calling pg_dump C:\Program Files (x86)\PostgreSQL\9.0\binpg_dump -i -h localhost -p 5433 -U postgres -f add.sql --column-inserts -t address my_db C:\Program Files (x86)\PostgreSQL\9.0\binpg_dump -i -h localhost -p 5433 -U postgres -f phone.sql --column-inserts -t phone my_db I tried these but did not work. C:\Program Files (x86)\PostgreSQL\9.0\binpg_dump -i -h localhost -p 5433 -U postgres -f C:\dbbackup13092011\add.sql --column-inserts -t address my_db C:\Program Files (x86)\PostgreSQL\9.0\binpg_dump -i -h localhost -p 5433 -U postgres -f C:\dbbackup13092011\phone.sql --column-inserts -t phone my_db C:\dbbackup does not exist yet. Please advise. Thanks and Regards, Rogel
[GENERAL] Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date.
Ack -- i flubbed the subject and sample. The sample data should be val val2date 11 2011-01-01 22 2011-01-02 33 2011-01-03 41 2011-01-04 52 2011-01-05 53 2011-01-01 41 2011-01-02 62 2011-01-03 43 2011-01-04 31 2011-01-05 22 2011-01-06 43 2011-01-07 61 2011-01-08 42 2011-01-09 53 2011-01-01 21 2011-01-02 42 2011-01-03 23 2011-01-04 11 2011-01-01 22 2011-01-02 33 2011-01-03 41 2011-01-04 32 2011-01-05 13 2011-01-01 21 2011-01-02 32 2011-01-03 43 2011-01-04 54 2012-01-01 resultset: 13 2011-01-01 22 2011-01-06 31 2011-01-05 42 2011-01-09 52 2011-01-05 61 2011-01-08 where any one of these 3 11 2011-01-01 11 2011-01-01 13 2011-01-01 or any one of these 2 31 2011-01-05 32 2011-01-05 are suitable for val = 1, val = 3 respectively. On 9/12/2011 8:54 PM, Reid Thompson wrote: Could someone point me in the right direction.. Thanks - reid Given the example data, how do I write a query that will give me the resultset: 12011-01-01 22011-01-06 32011-01-05 42011-01-09 52011-01-05 62011-01-08 I.E. for each distinct val, return the record with the most recent date. ex data val date 12011-01-01 22011-01-02 32011-01-03 42011-01-04 52011-01-05 52011-01-01 42011-01-02 62011-01-03 42011-01-04 32011-01-05 22011-01-06 42011-01-07 62011-01-08 42011-01-09 52011-01-01 22011-01-02 42011-01-03 22011-01-04 12011-01-01 22011-01-02 32011-01-03 42011-01-04 32011-01-05 12011-01-01 22011-01-02 32011-01-03 42011-01-04 52011-01-01 --- $ cat sampledata|sort -k1,2 12011-01-01 12011-01-01 12011-01-01 22011-01-02 22011-01-02 22011-01-02 22011-01-02 22011-01-04 22011-01-06 32011-01-03 32011-01-03 32011-01-03 32011-01-05 32011-01-05 42011-01-02 42011-01-03 42011-01-04 42011-01-04 42011-01-04 42011-01-04 42011-01-07 42011-01-09 52011-01-01 52011-01-01 52011-01-01 52011-01-05 62011-01-03 62011-01-08 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.
Could someone point me in the right direction.. Thanks - reid Given the example data, how do I write a query that will give me the resultset: 12011-01-01 22011-01-06 32011-01-05 42011-01-09 52011-01-05 62011-01-08 I.E. for each distinct val, return the record with the most recent date. ex data val date 12011-01-01 22011-01-02 32011-01-03 42011-01-04 52011-01-05 52011-01-01 42011-01-02 62011-01-03 42011-01-04 32011-01-05 22011-01-06 42011-01-07 62011-01-08 42011-01-09 52011-01-01 22011-01-02 42011-01-03 22011-01-04 12011-01-01 22011-01-02 32011-01-03 42011-01-04 32011-01-05 12011-01-01 22011-01-02 32011-01-03 42011-01-04 52011-01-01 --- $ cat sampledata|sort -k1,2 12011-01-01 12011-01-01 12011-01-01 22011-01-02 22011-01-02 22011-01-02 22011-01-02 22011-01-04 22011-01-06 32011-01-03 32011-01-03 32011-01-03 32011-01-05 32011-01-05 42011-01-02 42011-01-03 42011-01-04 42011-01-04 42011-01-04 42011-01-04 42011-01-07 42011-01-09 52011-01-01 52011-01-01 52011-01-01 52011-01-05 62011-01-03 62011-01-08 -- 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] Compatibility 9.1rc and 9.1.0
Thank you. Have a nice day :) 2011/9/13, Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I have the cluster created under 9.1rc version. When i do upgrade to 9.1.0, i can only reinstall binaries or i should import data too? Should work to just update the binaries. regards, tom lane -- pasman -- 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] writing block 6850 of relation 1663/17231/1259
On 11/09/2011 10:37 PM, Mudit Mishra wrote: Can anyone help me to fix this issue? I keep getting following errors in the log. I do not have a backup of the database and want to recover the database as much as possible from this error. 2011-09-09 11:06:39 PANIC: xlog flush request 2/190490D8 is not satisfied --- flushed only to 2/19004190 2011-09-09 11:06:39 CONTEXT: writing block 6850 of relation 1663/17231/1259 Before you do ANYTHING else, copy your data directory to other storage. Do not try to fix anything until you have made a copy of your data directory. Copy it while the server is stopped. -- Craig Ringer -- 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] 9.0, 9.1 RPM based parallel execution?
Hi, On Mon, 2011-09-12 at 18:58 -0700, John R Pierce wrote: so I have a centos6 machine running the 9.0.latest from the yum.postgresql.org repo... I'd like to bring up 9.1 on it in parallel, on a different port and directory.but I still want 9.0 to be the default install... I was looking at the PG wiki and didn't see this explained. Well, the current design is always to have the latest version as the default version. So, when you install 9.1, it will be the default under $PATH. However, after installing 9.1, you can play with alternatives, and set 9.0's priority something between 910 and 920, so that they will be picked up. rpm -q --scripts postgresql90 will give you the scripts that we run for alternatives. Just change 900 with 911 or so. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
