[GENERAL] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian
Cédric Villemain [2009-03-15 23:58 +0100]: Any plan for 8.4 pre-beta package ? (Devrim Gunduz provide usefull rpm package, I'd like to have the same in debian). Can it be in the experimental repository ? So far I usually started packaging those with the first public beta version, but if we are close to that, sure. Packaging this is probably easy, it just needs some time to get all the bits in postgresql-common right (like correctly rewriting obsolete/removed/retyped configuration settings, and the like). I'll see to packaging a current snapshot soon. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- 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: [Pkg-postgresql-public] Postgres major version support policy on Debian
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Martin Pitt a écrit : Cédric Villemain [2009-03-15 23:58 +0100]: Any plan for 8.4 pre-beta package ? (Devrim Gunduz provide usefull rpm package, I'd like to have the same in debian). Can it be in the experimental repository ? So far I usually started packaging those with the first public beta version, but if we are close to that, sure. Packaging this is probably easy, it just needs some time to get all the bits in postgresql-common right (like correctly rewriting obsolete/removed/retyped configuration settings, and the like). I'll see to packaging a current snapshot soon. Martin Xcellent Martin. If I can help, ping me. Here is the announce from Devrim : http://archives.postgresql.org/pgsql-announce/2009-03/msg00012.php And more particulary about !production: http://yum.pgsqlrpms.org/news-8.4devel-ready-for-testing.php - -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkm+EDsACgkQo/dppWjpEvwftwCgvaCgY6XdFethA449EFCsxqG+ LnUAmgM9h8N3OTzlIGkg05dsWEcdse+N =1gdr -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
[GENERAL] deployment query
Hi, we are in the process of finding the best solution for Postgresql deployment with storage controller. I have some query, Please give some suggestion for the below 1) Can we get customer deployment scenarios for postgresql with storage controller. Any flow diagram, operation diagram and implementation diagram are welcome. 2) Which protocol is mostly used in production. [NFS,ISCSi,FCP,etc...] 3) What kind of application Mostly used with Postgresql. 4) What is the business and technical issues for Postgresql with storage controller at present stage. 5) In which area Postgresql most wanted. 6) What kind of DR solution customer using for Postgresql with storage controller. Thanks in advance, Any suggestion and recommendation are welcome. Regards Karthikeyan.N
[GENERAL] suggestion: log_statement = sample
Hi, we ran a large database on moderate hardware. Disks are usually the slowest part so we do not log every statement. Sometimes we do and our IOwait and CPU increases by 10%. too much for peak times! it would be nice if you could say: log_statement = sample sample_rate = 100 you would get a good sample to analyze your database usage. Of course log_min_duration helps a lot as you see your slowest queries. But with a tool like hibernate, you have often have the problem issuing many many small statements like SELECT * from table where id = ?. They don't show up in the log with a reasonable log_min_duration setting. With my proposal every 100th query is logged and you get a detailed view of your database usage without excessive disk IO. Of course it should be combinable with log_min_duration. What do you think about it? kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Imagenes
Saludos Cordiales. Mi nombre es Luis Cevallos y tengo muchas dudas de como hacer para guardar una imagen desde php hasta una tabla que tiene campo oid no lo logro hacer claro estoy usando ADODB pero no se como hacerlo. Por favor. Gracias. Que tenga un lindo dia. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] large table starting sequence scan because of default_statistic_target
Hi, we are running a large 8.3 database and had some trouble with a default statistic target. We had set it to one special table some time ago, when we got a problem with a growing table starting with sequence scans. Last week we did manually cluster this table (create table as ... order by; drop table orig, rename table temp to orig ). Of course the statistic target was dropped and we did not remember to set it again. Why does default_statistic_target defaults to 10? The documentation tells me, the only drawback is a longer ANALYZE run. we are setting it to 100 in postgresql.conf and we did not see a much longer run of ANALYZE. Of course, smaller tables won't need a setting of 100. But small tables are usually not very interesting when it comes to performance. With a setting of 10 you run into difficult problems if your table grows. Suddenly an execution plan changes and you get sequence scans on your largest table! We had such problems and it was annoying to have a real slow down just because of this minor configuration parameter. I suggest to setting it to 100 by default: - no problems for small installations - no problems for DBA who always adjust their system in every possible way. - no problems for growing databases with unequal distributed data But maybe there are some other reasons not setting it to a higher value. If so, please tell me. kind regards Janning -- 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] large table starting sequence scan because of default_statistic_target
On Mon, Mar 16, 2009 at 7:15 AM, Janning Vygen vy...@kicktipp.de wrote: Hi, Why does default_statistic_target defaults to 10? I suggest to setting it to 100 by default: Already done in 8.4 -- 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] [SQL] Oracle to PostgreSQL
On Mon, Mar 16, 2009 at 12:35 AM, Greenhorn user.postgre...@gmail.com wrote: Hi, I have almost 1300 files from Oracle (no access to oracle server). I have to create the tables and data as follows. (...) snip (...) Any recommendation is greatly appreciated :) Try here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle -- 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] large table starting sequence scan because of default_statistic_target
On Monday 16 March 2009 15:13:51 Scott Marlowe wrote: On Mon, Mar 16, 2009 at 7:15 AM, Janning Vygen vy...@kicktipp.de wrote: Hi, Why does default_statistic_target defaults to 10? I suggest to setting it to 100 by default: Already done in 8.4 GREAT! sorry for not searching the archives or changelog before. Janning -- 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] large table starting sequence scan because of default_statistic_target
On Mon, Mar 16, 2009 at 8:24 AM, Janning Vygen vy...@kicktipp.de wrote: On Monday 16 March 2009 15:13:51 Scott Marlowe wrote: On Mon, Mar 16, 2009 at 7:15 AM, Janning Vygen vy...@kicktipp.de wrote: Hi, Why does default_statistic_target defaults to 10? I suggest to setting it to 100 by default: Already done in 8.4 GREAT! sorry for not searching the archives or changelog before. Hey, no problem, there's plenty of new stuff coming up in 8.4, and a lot of it doesn't show up on the general list anyway. This just makes sure a few more people know about this change. -- 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] Imagenes
Hi Lius, Luis Cevallos wrote: Saludos Cordiales. Mi nombre es Luis Cevallos y tengo muchas dudas de como hacer para guardar una imagen desde php hasta una tabla que tiene campo oid no lo logro hacer claro estoy usando ADODB pero no se como hacerlo. it would be easier for us to help you if you could stick to the language of the page where you subscribed to the mailing list :-) (Unless this is localized nowadays?) e.g. English :-) Since most of us (including me) do not understand Spain. Your question seems to target the ways to store images for your application. There are several ways to do this which have their own respective advantages - disadvantages: - in the database as lo (large object) pro: - data store in sync with metadata - common backup (yes with some limitations) - accessible over common service (e.g. database connection) con: - interface to lo a little more complicated and not always well supported - backup more difficult - large binary data over database connection - in the database as raw pro: - data store in sync with metadata - common backup - accessible over common service (e.g. database connection) - easy access via sql con: - large binary data over database connection - file size limit of about 2G - in the filesystem, metadata (e.g. location) in database pro: - easy to implement - high troughput to and from fileystem - possible to deliver via FS access, e.g. with apache con: - backup needs to take care of the file system - overwrite and locking needs to be carefully considered - not accessible from single connection (extra service to access the files needed) - can easily get out of sync with the database (metadata w/o file or vice versa) HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Scanning a large binary field
On Sun, Mar 15, 2009 at 02:25:11PM -0700, John R Pierce wrote: Kynn Jones wrote: That's a fair question. The program in question already gets from the relational database most of the external data it needs. The only exception to this is these large amorphous blobs, as you describe them. My only reason for wanting to put the blobs in the DB as well is to consolidate all the external data sources for the program. well, look at the LO (large object) facility of postgres. this is available to apps that call libpq directly, I have no idea if any of the generic 'portable' APIs would have any such hooks. They are all exposed as normal SQL functions that can be used as needed; I've just gone through the pain of getting VB to talk to them which included writing base64 encoding and decoding routines as VB didn't seem to be very reliable at handling non-ascii characters. The C library interface is documented here: http://www.postgresql.org/docs/current/static/lo-interfaces.html and the SQL level variants are named similarly (sometimes without an underscore in the name) and have identical semantics. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] archive_command question
Hi, When setting archive_command in postgresql.conf to something different than '', in other words, not empty, does it mean that archiving will be attempted periodically since postgresql starts or just when doing pg_start_backup and pg_stop_backup? I just use archived files for recovery process when I do pg_start_backup and pg_stop_backup, so I don't need to archive at all times. Thanks, Daniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] alter multiple tables
Hello, I have in every table columns like: username character varying(20) NOT NULL I want to extend the length of varchar in all tables. Since I have a lot of tables and mirrored backups, I am wondering if there is a way to alter automatically all tables where colname matches 'username' Is there a way to do this? Marton
Re: [GENERAL] alter multiple tables
Hi, Kodok Marton wrote: Hello, I have in every table columns like: username character varying(20) NOT NULL I want to extend the length of varchar in all tables. next time you should probably consider using a domain type (or stick to text) Since I have a lot of tables and mirrored backups, I am wondering if there is a way to alter automatically all tables where colname matches 'username' Is there a way to do this? It should be possible to generate a list of tables either via query or using pg_dump -L with grep and create SQL based on this (with a script, unix shell) and execute it against the database. (Test this of course) HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] alter multiple tables
doing via shell is one way (long way if you use Windows), is it possible by updateding postgresql master tables? - Original Message - From: Tino Wildenhain t...@wildenhain.de To: Kodok Marton mar...@mybusinessanywhere.com Cc: pgsql-general@postgresql.org Sent: Monday, March 16, 2009 6:08 PM Subject: Re: [GENERAL] alter multiple tables Hi, Kodok Marton wrote: Hello, I have in every table columns like: username character varying(20) NOT NULL I want to extend the length of varchar in all tables. next time you should probably consider using a domain type (or stick to text) Since I have a lot of tables and mirrored backups, I am wondering if there is a way to alter automatically all tables where colname matches 'username' Is there a way to do this? It should be possible to generate a list of tables either via query or using pg_dump -L with grep and create SQL based on this (with a script, unix shell) and execute it against the database. (Test this of course) HTH Tino -- 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] deployment query
Nagalingam, Karthikeyan karthikeyan.nagalin...@netapp.com wrote: Hi, we are in the process of finding the best solution for Postgresql deployment with storage controller. I have some query, Please give some suggestion for the below Doesn't Network Appliance have anyone who could help you with this? This is the third time you've asked a set of incredibly broad general questions of this list, that level of information shopping would perhaps be best answered by a consulting service you would hire to do the task. An email list is more useful for answering specific questions, but can't really offer such broad advice given so little information. storage controller' could mean anything from a simple SATA port on a desktop PC, to a EMC Symmetrix SAN, but we can guess based on your email address, you're specifically interested in NAS storage like Network Appliance Filers. Customer Deployment Scenarios ?!? 1) Install postgres. 2) Create database schema. 3) Deploy application(s). Protocol? I'd venture a guess that the vast majority of postgres installations have direct attached JBOD or simple raid storage. What kind of application? Any application requiring a relational database, ranging from web applications to accounting systems to manufacturing execution systems. I don't even know what to make of your questions 4 and 5. -- 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] alter multiple tables
On Mar 16, 12:08 pm, t...@wildenhain.de (Tino Wildenhain) wrote:
Since I have a lot of tables and mirrored backups, I am wondering if
there is a way to alter automatically all tables where colname matches
'username'
Is there a way to do this?
Enter psql with '-E' flag, and see the query that is generated when
using commands like '\dt' (which is for showing the tables in the
current database).
For example:
-bash-3.1$ psql -U some_user -d test_db -E
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
eds_db=# \dt
* QUERY **
SELECT n.nspname as Schema,
c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
Type,
r.rolname as Owner
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**
List of relations
Schema | Name | Type | Owner
+--+---+
public | test | table | some_user
Use the generated query, and modify it to suit your needs. There are
ways of extracting the columns from a given table name. With that, do
a for loop.
Daniel
--
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] deployment query
Thanks for your reply john. Regards Karthikeyan.N -Original Message- From: John R Pierce [mailto:pie...@hogranch.com] Sent: Monday, March 16, 2009 11:08 PM To: Nagalingam, Karthikeyan Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] deployment query Nagalingam, Karthikeyan karthikeyan.nagalin...@netapp.com wrote: Hi, we are in the process of finding the best solution for Postgresql deployment with storage controller. I have some query, Please give some suggestion for the below Doesn't Network Appliance have anyone who could help you with this? This is the third time you've asked a set of incredibly broad general questions of this list, that level of information shopping would perhaps be best answered by a consulting service you would hire to do the task. An email list is more useful for answering specific questions, but can't really offer such broad advice given so little information. storage controller' could mean anything from a simple SATA port on a desktop PC, to a EMC Symmetrix SAN, but we can guess based on your email address, you're specifically interested in NAS storage like Network Appliance Filers. Customer Deployment Scenarios ?!? 1) Install postgres. 2) Create database schema. 3) Deploy application(s). Protocol? I'd venture a guess that the vast majority of postgres installations have direct attached JBOD or simple raid storage. What kind of application? Any application requiring a relational database, ranging from web applications to accounting systems to manufacturing execution systems. I don't even know what to make of your questions 4 and 5. -- 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] [ask] Return Query
thanks Tom Lane, my problem resolved, I'm trying to re-create my table and function, and this working well Tom Lane wrote: ataherster atahers...@yahoo.co.id writes: ... but this function is not work with this error : ERROR: structure of query does not match function result type CONTEXT: PL/pgSQL function penjualan line 6 at RETURN QUERY This looks like a known limitation in plpgsql: it's not very good with rowtypes that contain dropped columns. Have you dropped some columns in table PENJUALAN? If so, try remaking the table from scratch. 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] intermittant performance problem
Scott Marlowe wrote: On Mon, Mar 9, 2009 at 8:21 PM, Mike Charnoky n...@nextbus.com wrote: The random sampling query is normally pretty snappy. It usually takes on the order of 1 second to sample a few thousand rows of data out of a few million. The sampling is consistently quick, too. However, on some days, the sampling starts off quick, then when the process starts sampling from a different subset of data (different range of times for the same day), the sampling query takes a couple minutes. Then definitely look at saving explain plans before execution to compare fast to slow runs. This definitely sounds like ocassionally bad query plans to me so far. Tom Lane wrote: Mike Charnoky n...@nextbus.com writes: The sampling query which runs really slow on some days looks something like this: INSERT INTO sampled_data (item_name, timestmp, ... ) SELECT item_name, timestmp, ... ) FROM raw_data WHERE timestmp = ? and timestmp ? AND item_name=? AND some_data_field NOTNULL ORDER BY random() LIMIT ?; Hmph, I'd expect that that would run pretty slowly *all* the time :-(. There's no good way to optimize ORDER BY random(). However, it seems like the first thing you should do is modify the program so that it issues an EXPLAIN for that right before actually doing the query, and then you could see if the plan is different on the slow days. The problem came up over the weekend so I took a look at the info from EXPLAIN. The query plans were quite different on the days when the problem happened. I began to suspect that autoanalyze was not happening daily like the autovacuums were, and sure enough it was only running about every other day. In fact, I saw that autoanalyze happened once during the sampling process, and the sampling happened much faster afterward. We're tuning the autoanalyze parameters so it runs more frequently. Is it OK to run ANALYZE manually before I begin the sampling process? Or is there a possibility this will collide with an autoanalyze and result in problems? I seem to remember this was a problem in the past, though it may have been before PG8.3... Mike -- 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] [ADMIN] deployment query
On Mon, Mar 16, 2009 at 1:00 AM, Nagalingam, Karthikeyan karthikeyan.nagalin...@netapp.com wrote: Hi, we are in the process of finding the best solution for Postgresql deployment with storage controller. I have some query, Please give some suggestion for the below 1) Can we get customer deployment scenarios for postgresql with storage controller. Any flow diagram, operation diagram and implementation diagram are welcome. Like John said, install, initdb, configure postgresql.conf and pg_hba.conf, load dbs and go. Whether or not it's on a storage controller is kind of not that big of a deal, as long as it's reliable. 2) Which protocol is mostly used in production. [NFS,ISCSi,FCP,etc...] If I can, I almost always build databases on DAS. If I must use something else, I'd lean towards iSCSI. Don't trust NFS for databases. 3) What kind of application Mostly used with Postgresql. All kinds. We mostly use it for Content Management where I work. Last place I worked it was our primary database for RT (ticketing system), bugzilla, media wiki, our statistical monitoring db, etc. Our primary lifting db was oracle, not because oracle was better, but because the VC vultures wouldn't sign off on postgresql out of ignorance / prejudice / lack of basic understanding / you name it. 4) What is the business and technical issues for Postgresql with storage controller at present stage. Not sure what you're asking here. Right now most postgresql installations are on direct attached storage. The biggest issues affecting any deployment to remote storage are the ones having to do with the OS postgresql is most often deployed on, Linux. Device drivers, iSCSI drivers, things like that. I would think that if you wanted more traction for pgsql (or other dbs) on netapp under linux, you could look at this area. 5) In which area Postgresql most wanted. Got me. 6) What kind of DR solution customer using for Postgresql with storage controller. First rule of communications, define your acronyms. What's DR? -- 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 on Webmin
Thanks John, I tried that and it works but with some changes I made.
After the changes you suggested I was still getting the same message:
Warning: pg_pconnect() [function.pg-pconnect]: Unable to connect to
PostgreSQL server: could not connect to server: Permission denied Is the
server running on host localhost and accepting TCP/IP connections on port
5432?
Anyway I managed to connect to localhost, but after I made changes to
pg_connect and I removed the host name
FROM
1. pg_pconnect(host=localhost dbname=mydb user=myuser
password=mypassword);
TO
1. pg_pconnect(dbname=mydb user=myuser password=mypassword);
Now it works, but VERY SLOWER than before.
One of the scripts I'm running on the previous host took below 2 sec for
1000 entries and now it takes 1 sec for 100 entries.
working pg_hba.conf looks like this:
1. # IPv4 local connections:
2. local all all trust
3. local all my_user ident sameuser
4. local my_db my_user password
5. host my_db my_user 0.0.0.0/0 password
6. # IPv6 local connections:
7. hostall all 127.0.0.1/32 ident sameuser
8. hostall all ::1/128 ident sameuser
-Original Message-
From: John R Pierce [mailto:pie...@hogranch.com]
Sent: Monday, March 16, 2009 2:25 AM
To: Kostadin Solakov
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL on Webmin
Kostadin Solakov wrote:
Hi there!
I just installed Webmin on my dedicated server and I really had hard
time making all configuration that were needed to migrate my site.
Anyway, now everything is working except for one thing. I can't
connect to the database through PHP. Remote connection is working
fine, but local doesn't.
The connection string looks like this:
pg_connect('host=localhost port=5432 user=user password=pass dbname=db')
I made the necessary changes in postgresql.conf
listen_addresses = '*'
port=5432
And I added this in pg_nba.conf:
local all all trust
local all user ident sameuser
local db user password
But still I cannot connect using php. The connection file is the same
as it was on my previous host (shared one) so it's supposed to be working.
I created the same database and user.
the first local all all takes precedence as that matches any local
(domain socket) connections. as is, you're saying any process running
your server can connect to any database as any user with no
authorization required.
however, none of those lines affect localhost IP connections, those
instead would match a `host 127.0.0.1/32 `
I most typically use the following...
local all all ident sameuser # allow local domain connections to
authenticate only as themselves
host all all 127.0.0.1/32 md5 # allow localhost IP connections to
authenticate with passwords only
and sometimes...
host all all my.ip.sub.net/24 md5 # allow any user on my IP subnet to
authneticate with passwords
--
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] Maximum transaction rate
Tom Lane wrote: Jack Orenstein jack.orenst...@hds.com writes: The transaction rates I'm getting seem way too high: 2800-2900 with one thread, 5000-7000 with ten threads. I'm guessing that writes aren't really reaching the disk. Can someone suggest how to figure out where, below postgres, someone is lying about writes reaching the disk? AFAIK there are two trouble sources in recent Linux machines: LVM and the disk drive itself. LVM is apparently broken by design --- it simply fails to pass fsync requests. If you're using it you have to stop. (Which sucks, because it's exactly the kind of thing DBAs tend to want.) Otherwise you need to reconfigure your drive to not cache writes. I forget the incantation for that but it's in the PG list archives. hmm are you sure this is what is happening? In my understanding LVM is not passing down barriers(generally - it seems to do in some limited circumstances) which means in my understanding it is not safe on any storage drive that has write cache enabled. This seems to be the very same issue like linux had for ages before ext3 got barrier support(not sure if even today all filesystems do have that). So in my understanding LVM is safe on disks that have write cache disabled or behave as one (like a controller with a battery backed cache). For storage with write caches it seems to be unsafe, even if the filesystem supports barriers and it has them enabled (which I don't think all have) which is basically what all of linux was not too long ago. Stefan -- 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] deployment query
Nagalingam, Karthikeyan wrote: Hi, we are in the process of finding the best solution for Postgresql deployment with storage controller. I have some query, Please give some suggestion for the below 1) Can we get customer deployment scenarios for postgresql with storage controller. Any flow diagram, operation diagram and implementation diagram are welcome. well deployment is the same as for deploying it to plain old direct attached storage - so all the docs available on www.postgresql.org are more or less valid for this. 2) Which protocol is mostly used in production. [NFS,ISCSi,FCP,etc...] all of those are used - however NFS is quite often discouraged due to various reliability issues (mostly on the client side) and operational complexity that caused issues in the past. ISCSI and Fiberchannel deployments (both on netapp based storage and others) have worked very well for me. 3) What kind of application Mostly used with Postgresql. that is an extremely broad question - in doubt it is always the application the customer uses. 4) What is the business and technical issues for Postgresql with storage controller at present stage. not sure what a business issue would be here - but as for technical issues postgresql is comparable to the demands of other (commercial) databases in that regard. I personally found general tuning guidelines for storage arrays that got written for oracle to be pretty well suitable(within limits obviously) for postgresql too. 5) In which area Postgresql most wanted. it's the customer that counts :) 6) What kind of DR solution customer using for Postgresql with storage controller. not sure what the question here is - maybe you can explain that in more detail? Stefan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] (0x0000274D/10061) on Install
Hi, I am trying to install PostgreSQL on Windows Vista 32bit, I have deactivated UAC and the only kind of Firewall I have is Avast Home. I am installing postgresql-8.3.6-2.zip, have also tried the 8.3.5.1. I am installing with everything as standard. During installation, during Activating Procedural Languages I get the error : Failed to connect to the database. Procedural languages files are installed, but are not activated in any databased. Could not connect to server: (0x274D/10061) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? And during Activating contrib modules, error: Failed to connect to the 'template1' database.Contrib files are installed, but are not activated in any databased.Could not connect to server: (0x274D/10061) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? Then PostgreSQL get successfully installed on my system. But when I try to connect the default database or creating a new, it won't connect, and gives me the same error (0x274D/10061). I really don't have a clue, but since it won't install it's most likely something to do with my configutation, maybe I have deactivated some necessary services etc? Do you have an idea? Best Regards Joachim
Re: [GENERAL] Maximum transaction rate
On Mon, Mar 16, 2009 at 2:03 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: So in my understanding LVM is safe on disks that have write cache disabled or behave as one (like a controller with a battery backed cache). For storage with write caches it seems to be unsafe, even if the filesystem supports barriers and it has them enabled (which I don't think all have) which is basically what all of linux was not too long ago. I definitely didn't have this problem with SCSI drives directly attached to a machine under pgsql on ext2 back in the day (way back, like 5 to 10 years ago). IDE / PATA drives, on the other hand, definitely suffered with having write caches enabled. -- 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] Maximum transaction rate
Stefan Kaltenbrunner wrote: So in my understanding LVM is safe on disks that have write cache disabled or behave as one (like a controller with a battery backed cache). what about drive write caches on battery backed raid controllers? do the controllers ensure the drive cache gets flushed prior to releasing the cached write blocks ? -- 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] (0x0000274D/10061) on Install
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Joachim Tranvåg Sent: Monday, March 16, 2009 1:20 PM To: pgsql-general@postgresql.org Subject: [GENERAL] (0x274D/10061) on Install Hi, I am trying to install PostgreSQL on Windows Vista 32bit, I have deactivated UAC and the only kind of Firewall I have is Avast Home. I am installing postgresql-8.3.6-2.zip, have also tried the 8.3.5.1. I am installing with everything as standard. During installation, during Activating Procedural Languages I get the error : Failed to connect to the database. Procedural languages files are installed, but are not activated in any databased. Could not connect to server: (0x274D/10061) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? And during Activating contrib modules, error: Failed to connect to the 'template1' database.Contrib files are installed, but are not activated in any databased.Could not connect to server: (0x274D/10061) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? Then PostgreSQL get successfully installed on my system. But when I try to connect the default database or creating a new, it won't connect, and gives me the same error (0x274D/10061). I really don't have a clue, but since it won't install it's most likely something to do with my configutation, maybe I have deactivated some necessary services etc? Do you have an idea? What are you using to attach to PostgreSQL? (e.g. psql.exe ? pgadmin III? Something else?) What does your pg_hba.conf file look like? I usually change mine so that the local host can attach: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32trust # IPv6 local connections: host all all ::1/128 trust When you look at your services, do you see the PostgreSQL service running? -- 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 on Webmin
Kostadin Solakov wrote: Anyway I managed to connect to localhost, but after I made changes to pg_connect and I removed the host name FROM 1. pg_pconnect(host=localhost dbname=mydb user=myuser password=mypassword); TO 1. pg_pconnect(dbname=mydb user=myuser password=mypassword); Now it works, but VERY SLOWER than before. One of the scripts I'm running on the previous host took below 2 sec for 1000 entries and now it takes 1 sec for 100 entries. thats odd, as a domain socket should be slightly -faster- than a tcp/ip socket. now, since you say 'previous host' I could wonder if other configuration items are impacting this, such as buffer sizes in postgresql.conf, relative speed of disk controllers, etc. or perhaps this new database hasn't been analyzed since it was populated, or its indexes need rebuilding, or something similar... working pg_hba.conf looks like this: 1. # IPv4 local connections: 2. local all all trust 3. local all my_user ident sameuser 4. local my_db my_user password line 2 masks lines 3,4 as it accepts any connection to any database over 'local' (unix domain socket), so it would never bother to try the others. 5. host my_db my_user 0.0.0.0/0 password that line would allow anyone anywhere (assuming listen_address = '*' in postgresql.conf, and no firewalls intervene) to connect as myuser to mydb with a password -- 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] Maximum transaction rate
Scott Marlowe wrote: On Mon, Mar 16, 2009 at 2:03 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: So in my understanding LVM is safe on disks that have write cache disabled or behave as one (like a controller with a battery backed cache). For storage with write caches it seems to be unsafe, even if the filesystem supports barriers and it has them enabled (which I don't think all have) which is basically what all of linux was not too long ago. I definitely didn't have this problem with SCSI drives directly attached to a machine under pgsql on ext2 back in the day (way back, like 5 to 10 years ago). IDE / PATA drives, on the other hand, definitely suffered with having write caches enabled. I guess thats likely because most SCSI drives (at least back in the days) had write caches turned off by default (whereas IDE drives had them turned on). The Linux kernel docs actually have some stuff on the barrier implementation ( http://git.kernel.org/?p=linux/kernel/git/torvalds/linux-2.6.git;a=blob_plain;f=Documentation/block/barrier.txt;hb=HEAD) which seems to explain some of the issues related to that. Stefan -- 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] (0x0000274D/10061) on Install
I am using pgadmin to attach to PostgreSQL. My pg_hba.conf file is default with md5 instead of trust, but have tried to change to trust, without that having any affect on my connecting issue. And yes, I see 6 postgres.exe services running. Since it won't properly install it might be some of the settings on my computer instead of the settings on PostgreSQL?
Re: [GENERAL] PostgreSQL on Webmin
John R Pierce wrote: thats odd, as a domain socket should be slightly -faster- than a tcp/ip socket. now, since you say 'previous host' I could wonder if other configuration items are impacting this, such as buffer sizes in postgresql.conf, relative speed of disk controllers, etc. or perhaps this new database hasn't been analyzed since it was populated, or its indexes need rebuilding, or something similar... I think that is the issue. Previous server was old machine and the settings in postgresql.conf were the default ones. The new machine is Xeon quad with 8GB ram and I already made some changes in postgresql.conf, but still no result. I followed the instructions on http://www.powerpostgresql.com/PerfList/ and made the following changes: shared_buffers = 5 work_mem = 512000 checkpoint_segments = 32 effective_cache_size = 20 Also I ran REINDEX on the DB and VACUUM VERBOSE ANALYZE on each table, but still the results are the same as before. What are the settings you recommend for this server? The application that is going to use it has a very demanding back end, it parses very large XML files (20+) and saves the data in the DB. Thanks, Kosta -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 8.2.x support on Windows 2008
Hi, I would like to know if Postgres 8.2.x is supported on Windows 2008 (32 bit). If not, what is version of Postgres supported on Windows 2008? PLs let me know. Thanks Raji -- 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] Postgres 8.2.x support on Windows 2008
On 16/03/2009 22:42, Raji Sridar (raji) wrote: I would like to know if Postgres 8.2.x is supported on Windows 2008 (32 bit). If not, what is version of Postgres supported on Windows 2008? PLs let me know. There's a list of supported platforms in the manual - have a rummage there. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Postgres 8.2.x support on Windows 2008
On Mon, 2009-03-16 at 22:55 +, Raymond O'Donnell wrote: On 16/03/2009 22:42, Raji Sridar (raji) wrote: I would like to know if Postgres 8.2.x is supported on Windows 2008 (32 bit). If not, what is version of Postgres supported on Windows 2008? PLs let me know. There's a list of supported platforms in the manual - have a rummage there. http://www.postgresql.org/docs/8.2/static/supported-platforms.html Sincerely, Joshua D. Drake Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Postgres 8.2.x support on Windows 2008
On Mon, Mar 16, 2009 at 4:42 PM, Raji Sridar (raji) r...@cisco.com wrote: Hi, I would like to know if Postgres 8.2.x is supported on Windows 2008 (32 bit). If not, what is version of Postgres supported on Windows 2008? PLs let me know. Due to some problems with older versions of pgsql and windows, it is no recommended to go to 8.3 and later versions of postgresql. I believe updates for previous versions are no longer supplied, but I'm a unix guy so, I could be off by one version. I know that 8.0 and 8.1 pgsql are definitely no longer supported on windows. Also, pgsql is always 32 bit on windows, whether to the OS is 32 or 64 bit. -- 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] Postgres 8.2.x support on Windows 2008
On Mon, Mar 16, 2009 at 4:58 PM, Joshua D. Drake j...@commandprompt.com wrote: On Mon, 2009-03-16 at 22:55 +, Raymond O'Donnell wrote: On 16/03/2009 22:42, Raji Sridar (raji) wrote: I would like to know if Postgres 8.2.x is supported on Windows 2008 (32 bit). If not, what is version of Postgres supported on Windows 2008? PLs let me know. There's a list of supported platforms in the manual - have a rummage there. http://www.postgresql.org/docs/8.2/static/supported-platforms.html Ok, so 8.2 is the oldest version supported. I was wondering... Another handy url: http://buildfarm.postgresql.org/cgi-bin/show_status.pl -- 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] (0x0000274D/10061) on Install
Did you restart the server after making your changes to pg_hba.conf? From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Joachim Tranvåg Sent: Monday, March 16, 2009 2:03 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] (0x274D/10061) on Install I am using pgadmin to attach to PostgreSQL. My pg_hba.conf file is default with md5 instead of trust, but have tried to change to trust, without that having any affect on my connecting issue. And yes, I see 6 postgres.exe services running. Since it won't properly install it might be some of the settings on my computer instead of the settings on PostgreSQL?
[GENERAL] [Q] ODBC connect shows RELEASE / SAVEPOINT on selects
Hello, a newbie question: I am using ODBC on windows (the unicode version) to connect to 8.3.3 running on the same machine (XP 32 bit). My C++ program uses OTL C++ library (it's ODBC functions) Every time I execute a simple select fld from mytable; I see this LOG: duration: 0.000 ms statement: RELEASE _EXEC_SVP_01B06868 LOG: duration: 0.000 ms statement: SAVEPOINT _EXEC_SVP_01B06868 my connection is set to no autocommit so I do not understand why I am seeing savepoints and release (I have some other code against the same DB instance but from PHP, and I did not see those messages in the log). Thanks in advance, Vlad -- V S P torea...@fastmail.fm -- http://www.fastmail.fm - A no graphics, no pop-ups email service -- 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] Postgres 8.2.x support on Windows 2008
Thanks for the prompt responses. None of the urls mention Windows 2008. Looks like it is not supported. Who should I approach to get this supported and how? Thanks Raji -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Monday, March 16, 2009 4:16 PM To: j...@commandprompt.com Cc: r...@iol.ie; Raji Sridar (raji); pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres 8.2.x support on Windows 2008 On Mon, Mar 16, 2009 at 4:58 PM, Joshua D. Drake j...@commandprompt.com wrote: On Mon, 2009-03-16 at 22:55 +, Raymond O'Donnell wrote: On 16/03/2009 22:42, Raji Sridar (raji) wrote: I would like to know if Postgres 8.2.x is supported on Windows 2008 (32 bit). If not, what is version of Postgres supported on Windows 2008? PLs let me know. There's a list of supported platforms in the manual - have a rummage there. http://www.postgresql.org/docs/8.2/static/supported-platforms.html Ok, so 8.2 is the oldest version supported. I was wondering... Another handy url: http://buildfarm.postgresql.org/cgi-bin/show_status.pl -- 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] Postgres 8.2.x support on Windows 2008
On Mon, 2009-03-16 at 18:12 -0700, Raji Sridar (raji) wrote: Thanks for the prompt responses. None of the urls mention Windows 2008. Looks like it is not supported. Who should I approach to get this supported and how? How do you mean supported? I doubt that the community is going to back patch support for 8.2 Win32 to Windows 2008. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Postgres 8.2.x support on Windows 2008
I don't mind going to 8.3 or later. I want to ensure that Postgres is tested on Windows 2008 and patches created like other platforms. How do I enable that? Thanks Raji -Original Message- From: Joshua D. Drake [mailto:j...@commandprompt.com] Sent: Monday, March 16, 2009 7:10 PM To: Raji Sridar (raji) Cc: Scott Marlowe; r...@iol.ie; pgsql-general@postgresql.org Subject: RE: [GENERAL] Postgres 8.2.x support on Windows 2008 On Mon, 2009-03-16 at 18:12 -0700, Raji Sridar (raji) wrote: Thanks for the prompt responses. None of the urls mention Windows 2008. Looks like it is not supported. Who should I approach to get this supported and how? How do you mean supported? I doubt that the community is going to back patch support for 8.2 Win32 to Windows 2008. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Postgres 8.2.x support on Windows 2008
Raji Sridar (raji) wrote: I don't mind going to 8.3 or later. I want to ensure that Postgres is tested on Windows 2008 and patches created like other platforms. How do I enable that? postgres isn't 'patched', there are incremental releases, like 8.3.5, 8.3.6. within a given X,Y, you can install a newer version on top of an older without any hiccups. I know of no reasons why it, as a win32 service, wouldn't work on Win2008 Server, except perhaps having to dink around a bit with privileges to get the installer past the security stuff. that said, I've never tried it on win2008 server, we're still using win2003 at work (actually, mostly we use unix and linux). -- 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] Postgres 8.2.x support on Windows 2008
Raji Sridar (raji) r...@cisco.com writes: I don't mind going to 8.3 or later. I want to ensure that Postgres is tested on Windows 2008 and patches created like other platforms. How do I enable that? Contribute a test machine to the buildfarm: http://www.pgbuildfarm.org/index.html (Although I have to concede never having heard of Windows 2008. You sure you're not talking about Vista? If so, we have that covered.) 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] (0x0000274D/10061) on Install
Joachim Tranvåg wrote: Hi, I am trying to install PostgreSQL on Windows Vista 32bit, I have deactivated UAC and the only kind of Firewall I have is Avast Home. Just so you know, there's rarely any need to deactivate UAC. Just shift-right-click on installers and choose Run as administrator. During installation, during Activating Procedural Languages I get the error : Failed to connect to the database. Procedural languages files are installed, but are not activated in any databased. Could not connect to server: (0x274D/10061) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? I strongly suspect that your firewall will turn out to be the problem. Consider disabling it or uninstalling it and see if that helps. -- 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] Postgres 8.2.x support on Windows 2008
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Tom Lane Sent: Monday, March 16, 2009 8:02 PM To: Raji Sridar (raji) Cc: j...@commandprompt.com; Scott Marlowe; r...@iol.ie; pgsql- gene...@postgresql.org Subject: Re: [GENERAL] Postgres 8.2.x support on Windows 2008 Raji Sridar (raji) r...@cisco.com writes: I don't mind going to 8.3 or later. I want to ensure that Postgres is tested on Windows 2008 and patches created like other platforms. How do I enable that? Contribute a test machine to the buildfarm: http://www.pgbuildfarm.org/index.html (Although I have to concede never having heard of Windows 2008. You sure you're not talking about Vista? If so, we have that covered.) He is referring to Windows Server 2008. http://en.wikipedia.org/wiki/List_of_Microsoft_Windows_versions For example, my system information gives this: OS Name Microsoft(r) Windows Server(r) 2008 Standard Version 6.0.6001 Service Pack 1 Build 6001 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] using window functions
Hi, I'm trying to get an advance taste of the window function feature that I believe is supposed to be in 8.4. I'm running 8.4devel snapshot (dated Jan-01 which seems kind of old) grabbed from the snapshot page on the postgresql website. When I try a simple query select avg(my_int) over (order by my_int rows unbounded preceding) order by 1; I get an error: ERROR: syntax error at or near over Do I have to build from source to try window functions? Thank you, Daniel _ Windows Live™: Life without walls. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_032009
Re: [GENERAL] using window functions
Daniel Manesajian mane...@hotmail.com writes: When I try a simple query select avg(my_int) over (order by my_int rows unbounded preceding) order by 1; I get an error: ERROR: syntax error at or near over You sure you're actually talking to the 8.4 server? Because that's exactly what you'd get if you tried to feed the command to an older release. You might in fact need a newer snapshot too. The main window functions commit went in 2008-12-28 but I recall that there were some mop-up fixes afterwards. At best a 1-Jan snapshot would be a bit unstable. 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] [Q] ODBC Decimal(19,6)
I declared a field as DECIMAL(19,6) when doing select thatfield from tb1 for some reason ODBC thinks that it is a double I think it has to be a string, otherwise precision is lost or am I not understanding this right? I tried to do select thefield\\:\\:varchar but for some reason that did not work yet. thanks in advance, Vlad -- V S P torea...@fastmail.fm -- http://www.fastmail.fm - Access your email from home and the web -- 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] Postgres 8.2.x support on Windows 2008
On Mon, 2009-03-16 at 23:02 -0400, Tom Lane wrote: Raji Sridar (raji) r...@cisco.com writes: I don't mind going to 8.3 or later. I want to ensure that Postgres is tested on Windows 2008 and patches created like other platforms. How do I enable that? Contribute a test machine to the buildfarm: http://www.pgbuildfarm.org/index.html (Although I have to concede never having heard of Windows 2008. You sure you're not talking about Vista? If so, we have that covered.) Its beta of Windows 7 server essentially. http://www.microsoft.com/windowsserver2008/en/us/default.aspx Joshua D. Drake regards, tom lane -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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 on Webmin
On Monday 16 March 2009 17:55:00 Kostadin Solakov wrote: John R Pierce wrote: thats odd, as a domain socket should be slightly -faster- than a tcp/ip socket. now, since you say 'previous host' I could wonder if other configuration items are impacting this, such as buffer sizes in postgresql.conf, relative speed of disk controllers, etc. or perhaps this new database hasn't been analyzed since it was populated, or its indexes need rebuilding, or something similar... I think that is the issue. Previous server was old machine and the settings in postgresql.conf were the default ones. The new machine is Xeon quad with 8GB ram and I already made some changes in postgresql.conf, but still no result. I followed the instructions on http://www.powerpostgresql.com/PerfList/ and made the following changes: shared_buffers = 5 work_mem = 512000 checkpoint_segments = 32 effective_cache_size = 20 Also I ran REINDEX on the DB and VACUUM VERBOSE ANALYZE on each table, but still the results are the same as before. What are the settings you recommend for this server? The application that is going to use it has a very demanding back end, it parses very large XML files (20+) and saves the data in the DB. Unless you're actually running 8.2, that information is a bit out of date. There's a better write up at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server Once you go through that and restart, if it's still slow, can you paste explain analyze from the two different servers? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.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] deployment query
Thanks Stefan for all your answers. My last question is What is the Mostly used Disaster Recovery Solution for Postgresql in storage environment. Regards Karthikeyan.N -Original Message- From: Stefan Kaltenbrunner [mailto:ste...@kaltenbrunner.cc] Sent: Tuesday, March 17, 2009 1:53 AM To: Nagalingam, Karthikeyan Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] deployment query Nagalingam, Karthikeyan wrote: Hi, we are in the process of finding the best solution for Postgresql deployment with storage controller. I have some query, Please give some suggestion for the below 1) Can we get customer deployment scenarios for postgresql with storage controller. Any flow diagram, operation diagram and implementation diagram are welcome. well deployment is the same as for deploying it to plain old direct attached storage - so all the docs available on www.postgresql.org are more or less valid for this. 2) Which protocol is mostly used in production. [NFS,ISCSi,FCP,etc...] all of those are used - however NFS is quite often discouraged due to various reliability issues (mostly on the client side) and operational complexity that caused issues in the past. ISCSI and Fiberchannel deployments (both on netapp based storage and others) have worked very well for me. 3) What kind of application Mostly used with Postgresql. that is an extremely broad question - in doubt it is always the application the customer uses. 4) What is the business and technical issues for Postgresql with storage controller at present stage. not sure what a business issue would be here - but as for technical issues postgresql is comparable to the demands of other (commercial) databases in that regard. I personally found general tuning guidelines for storage arrays that got written for oracle to be pretty well suitable(within limits obviously) for postgresql too. 5) In which area Postgresql most wanted. it's the customer that counts :) 6) What kind of DR solution customer using for Postgresql with storage controller. not sure what the question here is - maybe you can explain that in more detail? Stefan -- 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] deployment query
On Mon, Mar 16, 2009 at 11:15 PM, Nagalingam, Karthikeyan karthikeyan.nagalin...@netapp.com wrote: Thanks Stefan for all your answers. My last question is What is the Mostly used Disaster Recovery Solution for Postgresql in storage environment. We use two methods of backup to keep the database afloat amid things going horribly wrong. We have 1 or more slony backup dbs that allow for failover and load balancing. We have offsite pg_dump backups which are transferred via ssh to an offsite server in case of catastrophic failure in the data center (like a huge power surge) that kills both servers. We routinely restore backup sets or parts of them for various testing scenarios. -- 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] deployment query
On Tue, 2009-03-17 at 10:45 +0530, Nagalingam, Karthikeyan wrote: Thanks Stefan for all your answers. My last question is What is the Mostly used Disaster Recovery Solution for Postgresql in storage environment. That vastly depends. The most common is likely warm standby (PITR). If you are running Linux DRBD is a common solution as well. There is also Slony-I, and Mammoth Replicator. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
