Re: [GENERAL] Recommendations for SSDs in production?
On Wednesday, November 02, 2011 01:01:47 PM Yeb Havinga wrote: > Could you tell a bit more about the sudden death? Does the drive still > respond to queries for smart attributes? Just that. It's almost like somebody physically yanked them out of the machine, after months of 24x7 perfect performance. A cold reboot seems to restore order for a while, but the drives die again similarly fairly soon after a failure like this. From what I can tell, SMART is not worth much with SSDs. -Ben -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- 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] Recommendations for SSDs in production?
On Wednesday, November 02, 2011 11:39:25 AM Thomas Strunz wrote: > I have no idea what you do but just the fact that you bought ssds to > improve performance means it's rather high load and hence important. Important enough that we back everything up hourly. Because of this, we decided to give the SSDs a try. > Using a consumer drive for that IMHO is not the best idea. I know a lot > about ssds but just in consumer space. Intel has a good reputation in > terms of reliability but they are not the fastest. Which is what we're trying next, X25E. 710's apparently have 1/5th the rated write endurance, without much speed increase, so don't seem like such an exciting product. > I guess go Intel > route or some other crazy expensive enterprise stuff. It's advice about some of the "crazy expensive enterprise" stuff that I'm seeking...? I don't mind spending some money if I get to keep up this level of performance, but also am not looking to make somebody's private plane payment, either. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UTF-8 for bytea
When trying to INSERT on Postgres (9.1) to a bytea column, via E'' escaped strings, I get the strings rejected because they're not UTF8. I'm confused, since bytea isn't for strings but for binary. What causes this? How do I fix this? (I know that escaped strings is not the best way for binary data, but this is what I'm stuck with for now - it's a legacy system which I need to maintain.) -- 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] variable not found in subplan target list
Roger Niederland writes: > I stripped enough out of the database that it is only good for a test > case. Here is a public url for getting at the database backup: I've committed a fix for this: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e4e60e7b6125e77f679861ebf43cc6b9f9dbf16d Thanks for the test case! 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] 9.1 replication on different arch
2011/11/2 John R Pierce : > On 11/02/11 11:21 AM, Martín Marqués wrote: >> >> Don't worry, they are both x86 arch, so I'll just install 32bit >> postgresql on the 64 bit server. That should make it work, right? > > yes, that should work fine. Sad thing is that it's not so easy on Debian. With Fedora all I had to do is select the arch type and that's all. -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] OOM Killer / PG9 / RHEL 6.1
On Wed, Nov 2, 2011 at 10:42 AM, David Kerr wrote: > Howdy, > > just a quick check, is > vm.overcommit_memory = 2 > vm.swappiness = 0 > > Still the way to go with PG9.0 / RHEL 6.1 (64bit) ? If you've got lots of ram, it's better off to throw a "swapoff -a" at the end of rc.local, as I found that on large mem machines with late model kernels, setting swappiness to 0 I still had the occasionaly kswapd storm being my production servers to their knees. -- 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] Recommendations for SSDs in production?
On 2011-11-02 18:01, Benjamin Smith wrote: So after months of using this SSD without any issues at all, we tentatively rolled this out to production, and had blissful, sweet beauty until about 2 weeks ago, now we are running into sudden death scenarios. Could you tell a bit more about the sudden death? Does the drive still respond to queries for smart attributes? What firmware is on the Vertex 3? Anandtech talks about timing issues between certain IO controllers and the SandForce 2281 chipset, which appear to have been resolved in firmware 2.09 (http://www.anandtech.com/show/4341/ocz-vertex-3-max-iops-patriot-wildfire-ssds-reviewed/1). regards, Yeb -- 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] Error On Slony Replication
But Host Address should take automatically,
same thing is working good on other machine.
--- On Wed, 11/2/11, Raymond O'Donnell wrote:
From: Raymond O'Donnell
Subject: Re: [GENERAL] Error On Slony Replication
To: "Prashant Bharucha"
Cc: pgsql-general@postgresql.org
Received: Wednesday, November 2, 2011, 2:55 PM
On 02/11/2011 18:34, Prashant Bharucha wrote:
> Hello All
>
> For replication ,Created cluster and after I run Slon command
>
> getting error on
>
> PQconnectdb("dbname=XYZ host= user=cls password=1) failed - could
> not translate host name
The host is missing from the above.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] Server move using rsync
> > We're not doing this long-term, in order to have a backup server we can > fail-over to, but rather as a one-off low impact move of our database. > Consequently, instead of using pg_start_backup and pg_stop_backup, and > keeping all WAL, we're stopping the database, rsync of everything, and > starting the database in the new server, with it appearing to the new > server (if it was capable of noticing such things) that it had simply been > shutdown and restarted. > This is fine. If the database is shutdown, then the backup is completely safe. You can bring up the cluster as on backup time without any issues. > The initial and repeated rsyncs while the first server is running and in > use, are solely in order to reduce the time that the rsync takes while the > postgresql application is stopped. > > Do you still think we need to do anything special with pg_start_backup, > pg_stop_backup, and WAL archives? > Yes, after the initial sync, if the next repeated rsyncs are performed while the database cluster is up and running, then "pg_start_backup()-rsync-pg_stop_backup()" (as said earlier) must be performed. This will help Postgres know that the backup is going on. When you do pg_start_backup(), Postgres will make note and updates all the base file headers and makes a note of the TXN ids and Checkpoint time by creating a label. So, the WAL archives at time are needed for recovery (to recover any half written transactions). Without doing pg_start_backup, and with rsync not performing a "snapshot" > backup, my assumption is that until we do an rsync with the service > shutdown, whatever we've got at the location we're copying to, is not > self-consistent. > Above explanation should answer this. > If we start up postgresql on it, won't it think it is recovering from a > sudden crash? I think it may either appear to recover ok, or complain about > various things, and not start up ok, with neither option providing us with > much insight, as all that could tell us is that either some disk blocks are > consistent, or some are not, which is our starting assumption anyway. > Starting up postgresql would probably result in more disk block changes > that will result in more work next time we rsync. > This is normal behavior of rsync. It all depends on how volatile is your system and volume of changes performed. > How badly can we screw things up, given we intend to perform a final rsync > with no postgresql services running? What should we try and avoid doing, > and why? > > We might simply compare some hashes between the two systems, of some files > that haven't had their last-modified dates changed since the last rsync. > All this will be taken care by Postgres with the help of WAL archive files generated at the time when you performed rsync with postgres services up and running. Thanks VB
Re: [GENERAL] Error On Slony Replication
On Wed, Nov 2, 2011 at 2:55 PM, Raymond O'Donnell wrote:
> On 02/11/2011 18:34, Prashant Bharucha wrote:
> > Hello All
> >
> > For replication ,Created cluster and after I run Slon command
> >
> > getting error on
> >
> > PQconnectdb("dbname=XYZ host= user=cls password=1) failed - could
> > not translate host name
>
If you want to connect to an instance on the local machine with unix
sockets, don't include the host=
--
Scott Mead
OpenSCG http://www.openscg.com
> The host is missing from the above.
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>
> --
> 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] Recommendations for SSDs in production?
On 11/02/11 11:39 AM, Thomas Strunz wrote: For database I assume random read and writes are by way the most important thing and any recent ssd is orders of magnitude faster in that are compared to HDD even the "slow" Intel drives. actually, SSD's have issues with committed small block (8K) random writes such as databases do a lot of. the SSD has a rather large block size that has to be written all at once, so what they tend to do is accumulate random writes in a buffer, then write them all at once to a contiguous block (remapping the logical LBA sector address to an actual block/offset address). as a test at work, I compared a 2 x 100GB SAS enterprise SSD RAID0 with a 20 x 146GB SAS 15k HD RAID10, both raids using a HP p410 hardware raid controller with 1Gb cache, and both using XFS.Both file systems are approximately the same in sustainable random writes from postgres, up around 12000 wr/sec during heavy sustained pgbench activity (scaling factor of 500, 48 clients, 24 threads, on a 12 core 24 thread dual xeon e5660 48gb server).The HD raid is faster at sustained large block writes from iozone (1.2GB/sec vs 800MB/sec for the SSD). of course, the HD raid10 is 1.3TB of data, while the SSD raid0 is 200GB of data. -- 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] Error On Slony Replication
On 02/11/2011 18:34, Prashant Bharucha wrote:
> Hello All
>
> For replication ,Created cluster and after I run Slon command
>
> getting error on
>
> PQconnectdb("dbname=XYZ host= user=cls password=1) failed - could
> not translate host name
The host is missing from the above.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] Error On Slony Replication
Why do you have host= without a hostname? Where is your closing "?
Brandon Phelps
Global Linking Solutions
O: (704) 973-6855
C: (704) 222-2103
E: bphe...@gls.com
On 11/02/2011 02:34 PM, Prashant Bharucha wrote:
Hello All
For replication ,Created cluster and after I run Slon command
getting error on
PQconnectdb("dbname=XYZ host= user=cls password=1) failed - could
not translate host name
Could you please help ?
Thx
Prashant
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error On Slony Replication
Hello All
For replication ,Created cluster and after I run Slon command
getting error on
PQconnectdb("dbname=XYZ host= user=cls password=1) failed - could not
translate host name
Could you please help ?
Thx
Prashant
Re: [GENERAL] Recommendations for SSDs in production?
Vertex 3 and ocz in general has a very bad reputation in the "enthusiast scene". Sudden issues, hard locks, data loss and so on. Just go and look in the OCZ forums. I would not dare by on Vertex 3 for my desktop...have 2 Intel ones. I have no idea what you do but just the fact that you bought ssds to improve performance means it's rather high load and hence important. Using a consumer drive for that IMHO is not the best idea. I know a lot about ssds but just in consumer space. Intel has a good reputation in terms of reliability but they are not the fastest. I guess go Intel route or some other crazy expensive enterprise stuff. Note that consumers drives can lead to data loss in case of power failure (data in cache, no capacitors that would give enough power to flush to nand). For database I assume random read and writes are by way the most important thing and any recent ssd is orders of magnitude faster in that are compared to HDD even the "slow" Intel drives. Regards, Thomas > Date: Wed, 2 Nov 2011 12:18:10 -0500 > Subject: Re: [GENERAL] Recommendations for SSDs in production? > From: mmonc...@gmail.com > To: li...@benjamindsmith.com > CC: pgsql-general@postgresql.org > > On Wed, Nov 2, 2011 at 12:01 PM, Benjamin Smith > wrote: > > Well, > > > > After reading several glowing reviews of the new OCZ Vertex3 SSD last > > spring, > > we did some performance testing in dev on RHEL6. (CentOS) > > > > The results were nothing short of staggering. Complex query results returned > > in 1/10th the time as a pessimistic measurement. System loads dropped from > > 2+ > > to 0.1 or less. > > > > Wow. > > > > So after months of using this SSD without any issues at all, we tentatively > > rolled this out to production, and had blissful, sweet beauty until about 2 > > weeks ago, now we are running into sudden death scenarios. We have excellent > > backup system, so the damage is reduced to roadbumps, but are looking for a > > longer term solution that doesn't compromise performance too much. > > > > The config is super-basic, basically no tuning at all was done: > > > > # fdisk /dev/NNN; > > mke2fs -j $partn; > > mount $partn /var/lib/pgsql; > > rsync -vaz /var/lib/pgsql.old /var/lib/pgsql; > > service postgresql start; > > > > I don't mind spending some money. Can anybody comment on a recommended drive > > in real world use? > > > > After some review I found: > > > > 1) Micron P300 SSD: claims excellent numbers, can't find them for sale > > anywhere. > > > > 2) Intel X25E - good reputation, significantly slower than the Vertex3. > > We're > > buying some to reduce downtime. > > > > 3) OCZ "Enterprise" - reviews are mixed. > > > > 4) Kingston "Enterprise" drives appear to be identical to consumer drives > > with > > a different box. > > > > 5) STEC drives are astronomically expensive. (EG: "You're kidding, right?") > > > > 6) Corsair consumer drives getting excellent reviews, Aberdeen Inc > > recommended > > in use with RAID 1. > > > > 7) Seagate Pulsar drives, XT.2 drives are expensive SLC but can't find a > > vendor, Pulsar .2 drives are more available but having trouble finding > > reviews > > other than rehashed press releases. > > > > Thanks! > > The X25-E is now essentially being replaced by the 710. I would look there. > > 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] 9.1 replication on different arch
On 11/02/11 11:21 AM, Martín Marqués wrote: Don't worry, they are both x86 arch, so I'll just install 32bit postgresql on the 64 bit server. That should make it work, right? yes, that should work fine. -- 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] postgresql-9.1.1 PQescapeByteaConn() generating incorrectly quoted output.
Graham Murray writes: > Since upgrading test systems to postgresql 9.1, I am seeing some inserts > to bytea fields giving errors such as "ERROR: invalid byte sequence for > encoding "UTF8": 0xf9" Where the insert is from a C program using libpq > and is of the form "insert into xxx values(E'%s')" where the value is > the return of PQescapeByteaConn(); That is incorrect coding. The result of PQescapeByteaConn has never been meant to be put into an E'' literal. You might have managed to get away with it so long as standard_conforming_strings was off in the server, but it's on by default in 9.1. 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] 9.1 replication on different arch
Don't worry, they are both x86 arch, so I'll just install 32bit postgresql on the 64 bit server. That should make it work, right? El día 2 de noviembre de 2011 14:55, Adam Cornett escribió: > > 2011/11/2 Martín Marqués >> >> I have two servers, one a x86 32bit server, and the other one is a x86 >> 64 bit server. >> >> We want to use synchronous replication and make the 32 bit be a master >> and the 64bit be a read-only stand-by. >> >> Do I have to install 32bit postgresql on the 64bit server to be able >> to use wal replication? >> >> -- >> Martín Marqués >> select 'martin.marques' || '@' || 'gmail.com' >> DBA, Programador, Administrador >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > Native replication is only available on systems with the same architecture, > so the short answer is no. > But I'm pretty sure you can still use other replication tools like Slony or > Bucardo, although neither is synchronous. > -Adam -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] installation problems on OSX Lion
sean, could you share your solution in a little more detail. im having the exact problem now... -- View this message in context: http://postgresql.1045698.n5.nabble.com/installation-problems-on-OSX-Lion-tp4627419p4957366.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] select where not exists returning multiple rows?
We're using postgresql 9.1, and we've got a table that looks like this: testdb=# \d item Table "public.item" Column | Type | Modifiers ---+--+--- sig | bigint | not null type | smallint | data | text | Indexes: "item_pkey" PRIMARY KEY, btree (sig) And we're doing an insert like this: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4) In this case $1 and $4 should always be the same. The idea is to insert if the row doesn't already exist. We're getting primary key constraint violations: 011-10-31 22:50:26 CDT STATEMENT: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4 FOR UPDATE) 2011-10-31 22:52:56 CDT ERROR: duplicate key value violates unique constraint "item_pkey" 2011-10-31 22:52:56 CDT DETAIL: Key (sig)=(-4668668895560071572) already exists. I don't see how it's possible to get duplicate rows here, unless maybe the "select where not exists" is somehow returning multiple rows. Any ideas what's going on here? Thanks, Chris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql-9.1.1 PQescapeByteaConn() generating incorrectly quoted output.
Since upgrading test systems to postgresql 9.1, I am seeing some inserts
to bytea fields giving errors such as "ERROR: invalid byte sequence for
encoding "UTF8": 0xf9" Where the insert is from a C program using libpq
and is of the form "insert into xxx values(E'%s')" where the value is
the return of PQescapeByteaConn();
I noticed that with postgresql 9.0, the return string was of the form
"\\x" but with postgresql 9.1 it is "\x..."
I can work around this by specifying "E'\\%s" in the format string to
generate the query, but this will only work with postgrseql 9.1.
The following program illustrates the issue.
#include
#include
#include
int main(int argc, char *argv[])
{
PGconn *conn;
struct
{
u_int64_t byte1;
u_int64_t byte2;
} bindata;
char *enc;
size_t elen;
conn = PQconnectdb("");
bindata.byte1=0x0102030405060708;
bindata.byte2=0x090a0b0c0d0e0f10;
enc = PQescapeByteaConn(conn, (unsigned char *)&bindata, sizeof(bindata),
&elen);
printf("Server version %d\nEncoded string = %s\n", PQserverVersion(conn),
enc);
PQfreemem(enc);
exit(0);
}
Running the above program with postgres 9.0 & 9.1 generates the
following output.
graham@gmdev ~ $ ./byteatest
Server version 90101
Encoded string = \x0807060504030201100f0e0d0c0b0a09
graham@gmdev ~ $ ./byteatest
Server version 90005
Encoded string = \\x0807060504030201100f0e0d0c0b0a09
smime.p7s
Description: S/MIME cryptographic signature
[GENERAL] variable not found in subplan target list
Hello, I stripped down the original query to what is below. I am not saying that the query below is useful except to show an error I am getting in Postgresql 9.1.1 on both SL6.1 (64 bit) and Windows 2008 server 9.1.1 (32-bit and 64-bit). The error I am getting is: ERROR: variable not found in subplan target list SQL state: XX000 If I remove the inner join to person (at the end) to works on 9.1.1 I tried this query on Postgresql 9.1.0 on Windows Vista (32-bit) and it works. Also on the postgres 8.4 it works on both OS's. Thanks, Roger SELECT COALESCE(FreshUps.employeeId, Appts.employeeId) AS employeeId FROM ( SELECT COALESCE(Ups.employeeId, -1) AS employeeId FROM ( SELECT Lead.salesmanId AS employeeId FROM Lead GROUP BY Lead.salesmanID) AS Ups ) AS FreshUps FULL OUTER JOIN ( SELECT COALESCE(Ups.employeeId, -1) AS employeeId FROM ( SELECT SalesAppointment.SalesmanID AS employeeID FROM SalesAppointment GROUP BY SalesAppointment.SalesmanID) AS Ups ) AS Appts USING (employeeId) INNER JOIN Employee USING (employeeid) INNER JOIN Person ON Employee.employeeId = Person.personId
Re: [GENERAL] OOM Killer / PG9 / RHEL 6.1
On Wed, Nov 2, 2011 at 10:42, David Kerr wrote: > Howdy, > > just a quick check, is > vm.overcommit_memory = 2 > vm.swappiness = 0 > > Still the way to go with PG9.0 / RHEL 6.1 (64bit) ? IMHO yes (although I never touch swappiness...) > I know we gained some control over the OOM Killer in newer kernels > and remember reading that maybe postgres could handle it in a different way > now. If you compile with -DOOM_ADJ (which I think the red hat rpms do?) yes. As usual the docs are fairly good: http://www.postgresql.org/docs/9.1/interactive/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT -- 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.1 replication on different arch
2011/11/2 Martín Marqués > I have two servers, one a x86 32bit server, and the other one is a x86 > 64 bit server. > > We want to use synchronous replication and make the 32 bit be a master > and the 64bit be a read-only stand-by. > > Do I have to install 32bit postgresql on the 64bit server to be able > to use wal replication? > > -- > Martín Marqués > select 'martin.marques' || '@' || 'gmail.com' > DBA, Programador, Administrador > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Native replication is only available on systems with the same architecture, so the short answer is no. But I'm pretty sure you can still use other replication tools like Slony or Bucardo, although neither is synchronous. -Adam
[GENERAL] 9.1 replication on different arch
I have two servers, one a x86 32bit server, and the other one is a x86 64 bit server. We want to use synchronous replication and make the 32 bit be a master and the 64bit be a read-only stand-by. Do I have to install 32bit postgresql on the 64bit server to be able to use wal replication? -- Martín Marqués select 'martin.marques' || '@' || 'gmail.com' DBA, Programador, Administrador -- 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] Recommendations for SSDs in production?
you really need to watch out for excess write caching on SSDs. only a few are safe against power failures while under heavy database write activity. -- 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] Regarding the shared disk fail over cluster configuration
On Wed, Nov 2, 2011 at 11:55 AM, Debasis Mishra wrote: > Thanks a lot Ondrej Ivanic.I have few more doubts. > > 1)While installing the postgress it asks for the data directory,which i > refer to SAN volume(Shared LUN)-(example - /dbdata/pgsqldata). > > After that i am exporting $PGDATA= SAN Volume(example - /dbdata/pgsqldata). > Where /dbdata is the shared LUN . > > Then I am running initdb - but it is not running successfully. The Error is > /dbdata/pgsqldata is not empty. > > Because I can understand while installation postgress creates > files/directory in the data folder. While running initdb also it is trying > to create some directory which is already present. > > So I am not sure how to go ahead with this scenario. > It seems that your installer has already created the data for you. If you have files like: postgresql.conf base/ pg_xlog/ .. Then your postgres data directory is already initialized, no need for initdb. > > 2) Regarding the shared disk failover clustering you mentioned that if > primary fails , cluster should bring it down and start postgres in > secondary > node. > > My doubt is - Whether cluster should start the postgres service in > secondary > node during failover or postgress will be running always. My undersatnding > was in both the node postgress will be running and pointing to shared > dbdata. And if node1/primary fails then cluster software should bring > node2/secondary up.once it will bring node2 online there postgress must be > running already. > > Is this feasible? > > Do not do this. At first glance, you can actually run in this configuration (i.e. you can start the postmasters) but it will cause data corruption. DO NOT under ANY circumstances try to run both primary and secondary on the same data directory simultaneously. You will corrupt your entire database. The only way to run postgres with shared disk clustering is in Active/Passive mode. -- Scott Mead OpenSCG http://www.openscg.com > Thanks, > Debasis > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Regarding-the-shared-disk-fail-over-cluster-configuration-tp4952316p4958449.html > Sent from the PostgreSQL - general mailing list archive at Nabble.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] Recommendations for SSDs in production?
On 11/2/2011 11:01 AM, Benjamin Smith wrote: 2) Intel X25E - good reputation, significantly slower than the Vertex3. We're buying some to reduce downtime. If you don't mind spending money, look at the new 710 Series from Intel. Not SLC like the X25E, but still specified with a very high write endurance. -- 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] Regarding the shared disk fail over cluster configuration
On November 2, 2011 08:55:39 AM Debasis Mishra wrote: > My doubt is - Whether cluster should start the postgres service in > secondary node during failover or postgress will be running always. My > undersatnding was in both the node postgress will be running and pointing > to shared dbdata. And if node1/primary fails then cluster software should > bring node2/secondary up.once it will bring node2 online there postgress > must be running already. You definitely cannot have 2 postmasters running against the same shared disk. The cluster software will need to start PostgreSQL on the secondary node after taking over the IP address and shared disk resource. -- 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] Why is there no 8.3.16 rpm with _id ?
2011/10/30 Devrim GÜNDÜZ : > > [Moving to pgsql-general] > > On Sun, 2011-10-30 at 07:24 +0100, hubert depesz lubaczewski wrote: >> we'd like to upgrade to newest 8.3, and we're on 8.3.11 _id, but it >> looks like 8.3.11 is the newest version of 8.3 built with integer >> datetimes: >> http://yum.postgresql.org/8.3/redhat/rhel-5-x86_64-id/repoview/ >> >> Is there any reason for this, and will there be any newer versions >> built with integer datetimes? >> > I have no intention to build the -id packages again, given the lack of > request (first request since 8.3.11...). You can build your own packages > quite easily, though. > Hey Devrim, any chance you have published your rpm spec files you used on the earlier 8.3 -id builds? I looked around and couldn't find one. Robert Treat conjecture: xzilla.net consulting: omniti.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] Recommendations for SSDs in production?
On Wed, Nov 2, 2011 at 12:01 PM, Benjamin Smith wrote: > Well, > > After reading several glowing reviews of the new OCZ Vertex3 SSD last spring, > we did some performance testing in dev on RHEL6. (CentOS) > > The results were nothing short of staggering. Complex query results returned > in 1/10th the time as a pessimistic measurement. System loads dropped from 2+ > to 0.1 or less. > > Wow. > > So after months of using this SSD without any issues at all, we tentatively > rolled this out to production, and had blissful, sweet beauty until about 2 > weeks ago, now we are running into sudden death scenarios. We have excellent > backup system, so the damage is reduced to roadbumps, but are looking for a > longer term solution that doesn't compromise performance too much. > > The config is super-basic, basically no tuning at all was done: > > # fdisk /dev/NNN; > mke2fs -j $partn; > mount $partn /var/lib/pgsql; > rsync -vaz /var/lib/pgsql.old /var/lib/pgsql; > service postgresql start; > > I don't mind spending some money. Can anybody comment on a recommended drive > in real world use? > > After some review I found: > > 1) Micron P300 SSD: claims excellent numbers, can't find them for sale > anywhere. > > 2) Intel X25E - good reputation, significantly slower than the Vertex3. We're > buying some to reduce downtime. > > 3) OCZ "Enterprise" - reviews are mixed. > > 4) Kingston "Enterprise" drives appear to be identical to consumer drives with > a different box. > > 5) STEC drives are astronomically expensive. (EG: "You're kidding, right?") > > 6) Corsair consumer drives getting excellent reviews, Aberdeen Inc recommended > in use with RAID 1. > > 7) Seagate Pulsar drives, XT.2 drives are expensive SLC but can't find a > vendor, Pulsar .2 drives are more available but having trouble finding reviews > other than rehashed press releases. > > Thanks! The X25-E is now essentially being replaced by the 710. I would look there. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recommendations for SSDs in production?
Well, After reading several glowing reviews of the new OCZ Vertex3 SSD last spring, we did some performance testing in dev on RHEL6. (CentOS) The results were nothing short of staggering. Complex query results returned in 1/10th the time as a pessimistic measurement. System loads dropped from 2+ to 0.1 or less. Wow. So after months of using this SSD without any issues at all, we tentatively rolled this out to production, and had blissful, sweet beauty until about 2 weeks ago, now we are running into sudden death scenarios. We have excellent backup system, so the damage is reduced to roadbumps, but are looking for a longer term solution that doesn't compromise performance too much. The config is super-basic, basically no tuning at all was done: # fdisk /dev/NNN; mke2fs -j $partn; mount $partn /var/lib/pgsql; rsync -vaz /var/lib/pgsql.old /var/lib/pgsql; service postgresql start; I don't mind spending some money. Can anybody comment on a recommended drive in real world use? After some review I found: 1) Micron P300 SSD: claims excellent numbers, can't find them for sale anywhere. 2) Intel X25E - good reputation, significantly slower than the Vertex3. We're buying some to reduce downtime. 3) OCZ "Enterprise" - reviews are mixed. 4) Kingston "Enterprise" drives appear to be identical to consumer drives with a different box. 5) STEC drives are astronomically expensive. (EG: "You're kidding, right?") 6) Corsair consumer drives getting excellent reviews, Aberdeen Inc recommended in use with RAID 1. 7) Seagate Pulsar drives, XT.2 drives are expensive SLC but can't find a vendor, Pulsar .2 drives are more available but having trouble finding reviews other than rehashed press releases. Thanks! -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OOM Killer / PG9 / RHEL 6.1
Howdy, just a quick check, is vm.overcommit_memory = 2 vm.swappiness = 0 Still the way to go with PG9.0 / RHEL 6.1 (64bit) ? I know we gained some control over the OOM Killer in newer kernels and remember reading that maybe postgres could handle it in a different way now. Thanks Dave -- 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] Server move using rsync
On Tue, Nov 1, 2011 at 11:08 AM, Alan Hodgson wrote: > On October 31, 2011 03:01:19 PM Stephen Denne wrote: >> I'm wondering whether it's worth doing anyway, simply to check that it >> doesn't do something completely unexpected, which would presumably alert >> us to something we hadn't considered. >> > > Testing is always worthwhile, if only to ensure that PostgreSQL will actually > run with your configuration on the new machine (sufficient shared memory, IP > addresses specified in postgresql.conf, etc). > > However, assuming the PostgreSQL binary packages you're using are identical, > and assuming that you aren't changing tablespace pointers around, the rsync / > restart is pretty fool-proof in terms of reliably copying PostgreSQL itself. > PostgreSQL is good about updating time stamps on modified files, you don't > have > to worry about needing the full compare options on rsync or anything "-avr -- > delete" is generally sufficient . > > You might disable WAL archiving during a test startup to avoid sending > duplicates to your backup server. > You know, this looks like it will work, but if I were you, I would set up the database as a PITR standby on the new box, and have WAL shipping in place. When you're ready to move, you shutdown the old database, synch up the xlogs, and then failover to the new database. Not only should this be faster, it seems less error prone, and you can actually test the failover and lunch bits while the original server is up and running. Robert Treat conjecture: xzilla.net consulting: omniti.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] Regarding the shared disk fail over cluster configuration
Thanks a lot Ondrej Ivanic.I have few more doubts. 1)While installing the postgress it asks for the data directory,which i refer to SAN volume(Shared LUN)-(example - /dbdata/pgsqldata). After that i am exporting $PGDATA= SAN Volume(example - /dbdata/pgsqldata). Where /dbdata is the shared LUN . Then I am running initdb - but it is not running successfully. The Error is /dbdata/pgsqldata is not empty. Because I can understand while installation postgress creates files/directory in the data folder. While running initdb also it is trying to create some directory which is already present. So I am not sure how to go ahead with this scenario. 2) Regarding the shared disk failover clustering you mentioned that if primary fails , cluster should bring it down and start postgres in secondary node. My doubt is - Whether cluster should start the postgres service in secondary node during failover or postgress will be running always. My undersatnding was in both the node postgress will be running and pointing to shared dbdata. And if node1/primary fails then cluster software should bring node2/secondary up.once it will bring node2 online there postgress must be running already. Is this feasible? Thanks, Debasis -- View this message in context: http://postgresql.1045698.n5.nabble.com/Regarding-the-shared-disk-fail-over-cluster-configuration-tp4952316p4958449.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] equivalent to "replication_timeout" on standby server
Hi all, The postgres manual explains the "replication_timeout" to be used to "Terminate replication connections that are inactive longer than the specified number of milliseconds. This is useful for the primary server to detect a standby crash or network outage" Is there a similar configuration parameter that helps the WAL receiver processes to terminate the idle connections on the standby servers? It would be very useful (for monitoring purpose) if the termination of such an idle connection on either master or standby servers is logged with appropriate message. Could some one explain me if this is possible with postgres-9.1.1? Thanks and Regards, Samba
Re: [GENERAL] does reindex need exclusive table access?
Vincent de Phily writes:
> The technique kinda works (with some changes) using unique indexes however.
> Is
> there a functional difference between a unique index and a primary key index
> (knowing that my column is not null) ? Or is it just for documentation and
> ORM
> purposes ?
The only functional difference is that a foreign key declaration
referring to the table ("REFERENCES tabname") will default to the pkey
column list. If you haven't got a declared pkey then you have to spell
out the column list.
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] does reindex need exclusive table access?
On Tuesday 01 November 2011 12:00:33 Craig Ringer wrote: > A workaround for reindexing while live is to begin a transaction, create > the new index with a new name, drop the old one, rename the new one to > the old one, and commit. This only requires an exclusive lock for the > period of the drop and rename. On more recent versions you can even use > this for indexes that implement primary key or unique constrants by > using the 'ADD CONSTRAINT ... USING INDEX ...' syntax; see: > > http://www.postgresql.org/docs/current/static/sql-altertable.html Been happily doing this (without worrying about transactions, as Tom suggested), but couldn't quite figure out how to do it with my primary key indexes on 8.3. Do I have to bite the bullet and take an exclusive lock (or upgrade to 9.1) ? The technique kinda works (with some changes) using unique indexes however. Is there a functional difference between a unique index and a primary key index (knowing that my column is not null) ? Or is it just for documentation and ORM purposes ? -- Vincent de Phily -- 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] select where not exists returning multiple rows?
On Wed, Nov 2, 2011 at 8:20 AM, Chris Dumoulin wrote: > On 11-11-02 09:13 AM, Martijn van Oosterhout wrote: >> >> On Wed, Nov 02, 2011 at 07:22:09AM -0400, Chris Dumoulin wrote: >>> >>> And we're doing an insert like this: >>> INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS >>> ( SELECT NULL FROM Item WHERE Sig=$4) >>> >>> In this case $1 and $4 should always be the same. >> >> FWIW, If they're always going to be the same, you can put that it the >> query, >> like so: >> >> INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS >> ( SELECT NULL FROM Item WHERE Sig=$1) >> >> Saves a parameter. >> >>> I don't see how it's possible to get duplicate rows here, unless >>> maybe the "select where not exists" is somehow returning multiple >>> rows. >>> Any ideas what's going on here? >> >> As pointed out by others, you don't say if it this is a race condition >> between processes or if it always does this. > > It's only happening intermittently, but it doesn't appear to be a race > condition; I'm pretty sure there's only one thread or process issuing this > statement. Pretty sure? you need to be 100% sure. *Somebody* was worried about concurrency in the code, because the actual statement in the log has 'FOR UPDATE' -- your example does not. Intermittent failures is classic race condition behavior. The reason for the race is that your select happens before the insert does so that process A and B can select at approximately the same time and both make the decision to insert on the same key...bam. Logging all statements will positively prove this. select where exists ... does not return > 1 rows ever and there is precisely 0% chance you've uncovered a server bug that is causing it to :-). solve the problem by: a: LOCK the table before making the insert, making sure to wrap the lock and the insert in the same transaction (this should be the default method) b. retry the transaction on failure in the client c. or on the server if you push the insert into a function. 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] select where not exists returning multiple rows?
On 11-11-02 09:13 AM, Martijn van Oosterhout wrote: On Wed, Nov 02, 2011 at 07:22:09AM -0400, Chris Dumoulin wrote: And we're doing an insert like this: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4) In this case $1 and $4 should always be the same. FWIW, If they're always going to be the same, you can put that it the query, like so: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$1) Saves a parameter. I don't see how it's possible to get duplicate rows here, unless maybe the "select where not exists" is somehow returning multiple rows. Any ideas what's going on here? As pointed out by others, you don't say if it this is a race condition between processes or if it always does this. It's only happening intermittently, but it doesn't appear to be a race condition; I'm pretty sure there's only one thread or process issuing this statement. Thanks, Chris Have a nice day, -- 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] select where not exists returning multiple rows?
On Wed, Nov 02, 2011 at 07:22:09AM -0400, Chris Dumoulin wrote: > And we're doing an insert like this: > INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS > ( SELECT NULL FROM Item WHERE Sig=$4) > > In this case $1 and $4 should always be the same. FWIW, If they're always going to be the same, you can put that it the query, like so: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$1) Saves a parameter. > I don't see how it's possible to get duplicate rows here, unless > maybe the "select where not exists" is somehow returning multiple > rows. > Any ideas what's going on here? As pointed out by others, you don't say if it this is a race condition between processes or if it always does this. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] select where not exists returning multiple rows?
On 11-11-02 08:49 AM, Merlin Moncure wrote: On Wed, Nov 2, 2011 at 6:22 AM, Chris Dumoulin wrote: We're using postgresql 9.1, and we've got a table that looks like this: testdb=# \d item Table "public.item" Column | Type | Modifiers ---+--+--- sig | bigint | not null type | smallint | data | text | Indexes: "item_pkey" PRIMARY KEY, btree (sig) And we're doing an insert like this: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4) In this case $1 and $4 should always be the same. The idea is to insert if the row doesn't already exist. We're getting primary key constraint violations: 011-10-31 22:50:26 CDT STATEMENT: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4 FOR UPDATE) 2011-10-31 22:52:56 CDT ERROR: duplicate key value violates unique constraint "item_pkey" 2011-10-31 22:52:56 CDT DETAIL: Key (sig)=(-4668668895560071572) already exists. I don't see how it's possible to get duplicate rows here, unless maybe the "select where not exists" is somehow returning multiple rows. Any ideas what's going on here? race condition. lock the table first or retry the insert. merlin Could you elaborate a little more on the race condition? Are you suggesting that if two threads executed this statement at the same time, the results from the inner "SELECT NULL ..." in one of the threads could be incorrect by the time that thread did the INSERT? I thought about this possibility and tried "SELECT NULL ... FOR UPDATE", but still saw the same problem. Thanks, Chris -- 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] select where not exists returning multiple rows?
On Wed, Nov 2, 2011 at 6:22 AM, Chris Dumoulin wrote: > We're using postgresql 9.1, and we've got a table that looks like this: > > testdb=# \d item > Table "public.item" > Column | Type | Modifiers > ---+--+--- > sig | bigint | not null > type | smallint | > data | text | > Indexes: > "item_pkey" PRIMARY KEY, btree (sig) > > And we're doing an insert like this: > INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT > NULL FROM Item WHERE Sig=$4) > > In this case $1 and $4 should always be the same. The idea is to insert if > the row doesn't already exist. > We're getting primary key constraint violations: > > 011-10-31 22:50:26 CDT STATEMENT: INSERT INTO Item (Sig, Type, Data) SELECT > $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4 FOR UPDATE) > 2011-10-31 22:52:56 CDT ERROR: duplicate key value violates unique > constraint "item_pkey" > 2011-10-31 22:52:56 CDT DETAIL: Key (sig)=(-4668668895560071572) already > exists. > > I don't see how it's possible to get duplicate rows here, unless maybe the > "select where not exists" is somehow returning multiple rows. > Any ideas what's going on here? race condition. lock the table first or retry the insert. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] select where not exists returning multiple rows?
We're using postgresql 9.1, and we've got a table that looks like this: testdb=# \d item Table "public.item" Column | Type | Modifiers ---+--+--- sig | bigint | not null type | smallint | data | text | Indexes: "item_pkey" PRIMARY KEY, btree (sig) And we're doing an insert like this: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4) In this case $1 and $4 should always be the same. The idea is to insert if the row doesn't already exist. We're getting primary key constraint violations: 011-10-31 22:50:26 CDT STATEMENT: INSERT INTO Item (Sig, Type, Data) SELECT $1,$2,$3 WHERE NOT EXISTS ( SELECT NULL FROM Item WHERE Sig=$4 FOR UPDATE) 2011-10-31 22:52:56 CDT ERROR: duplicate key value violates unique constraint "item_pkey" 2011-10-31 22:52:56 CDT DETAIL: Key (sig)=(-4668668895560071572) already exists. I don't see how it's possible to get duplicate rows here, unless maybe the "select where not exists" is somehow returning multiple rows. Any ideas what's going on here? Thanks, Chris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
