Re: [GENERAL] Full text search ts_heading strange result
On 07/26/2012 02:14 PM, Johann Spies wrote:
Hallo Tom,
I believe the problem is that the one-argument form of to_tsquery() uses
the default TS configuration, which you have probably not got set to
"simple". For me, the default TS configuration is "english", which will
stem "polity" as "politi":
regression=# select to_tsquery('(polity & church)');
to_tsquery
-
'politi' & 'church'
(1 row)
However the "simple" configuration doesn't do anything to that lexeme:
Thanks for the explanation. I am working with a multi-language database
and that was the reason for using the 'simple' configuration.
I have asked, in an earlier message on this list, advice on how to
handle full text searches in a multi-language database, but got no
reaction to it. If there is a better way than using the 'simple'
configuration in this case, I would gladly try it.
You'll need to store language information alongside each text value if
you want to do anything more sophisticated. If you have mixed languages
within a single text value or if you don't store information about the
language a text value is in then you're largely out of luck.
--
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] Smaller data types use same disk space
> > Has there been any discussion of providing the ability to re-order
> > table columns through an ALTER TABLE command? I would love to see
> > this; when I add in a new column, I often want to put it next to
> > something just to be more visually appealing when I'm running ad-hoc
> > queries. It could potentially address this problem as well.
>
> see: http://wiki.postgresql.org/wiki/Alter_column_position
> as well as extensive discussion in the archives.
>
> merlin
Hello,
actually columns position can be changed with alter table using
ALTER TYPE ... USING. All constraints, indexes and possible serials
naturally still need to be handled as well
However, I'm not sure that this alternative may have any advantage
upon the method described on the wiki page.
create temp table test (a serial, b varchar);
insert into test (b) values ('a'),('b');
create index a_i on test(a);
create index b_i on test(b);
create index ab_i on test(a,b);
alter table test
ALTER a TYPE varchar USING b,
ALTER b TYPE int USING a;
alter table test rename b to _b;
alter table test rename a to b;
alter table test rename _b to a;
select * from test;
\d+ test
Marc Mamin
--
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] Full text search ts_heading strange result
On Thu, Jul 26, 2012 at 04:19:02PM +0800, Craig Ringer wrote: > You'll need to store language information alongside each text value > if you want to do anything more sophisticated. I was afraid that that will be the case :) I will have to update more than 32 entries which currently have inconsistent language indications and some of them none at all. Thanks for responding. Regards Johann -- Johann SpiesTelefoon: 021-808 4699 Databestuurder / Data manager Sentrum vir Navorsing oor Evaluasie, Wetenskap en Tegnologie Centre for Research on Evaluation, Science and Technology Universiteit Stellenbosch. "If any of you lack wisdom, let him ask of God, that giveth to all men liberally, and upbraideth not; and it shall be given him." James 1:5 E-pos vrywaringsklousule Hierdie e-pos mag vertroulike inligting bevat en mag regtens geprivilegeerd wees en is slegs bedoel vir die persoon aan wie dit geadresseer is. Indien u nie die bedoelde ontvanger is nie, word u hiermee in kennis gestel dat u hierdie dokument geensins mag gebruik, versprei of kopieer nie. Stel ook asseblief die sender onmiddellik per telefoon in kennis en vee die e-pos uit. Die Universiteit aanvaar nie aanspreeklikheid vir enige skade, verlies of uitgawe wat voortspruit uit hierdie e-pos en/of die oopmaak van enige lês aangeheg by hierdie e-pos nie. E-mail disclaimer This e-mail may contain confidential information and may be legally privileged and is intended only for the person to whom it is addressed. If you are not the intended recipient, you are notified that you may not use, distribute or copy this document in any manner whatsoever. Kindly also notify the sender immediately by telephone, and delete the e-mail. The University does not accept liability for any damage, loss or expense arising from this e-mail and/or accessing any files attached to this e-mail. -- 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] BI tools and postgresql
On Wed, Jul 25, 2012 at 1:41 PM, Mark Phillips wrote: > I am seeking suggestions for business intelligence and data mining tools > compatible with postgresql. A new manager at a client's shop is leaning > toward the MS offerings. I would like to be able to speak to the issue. > I'm in the same boat, and while it's not really my job, I'm looking for two things: 1) a tool for power users to be able to get their own data. There are a few folks here who are smart enough to be trusted with direct access to the database, but I don't want to ask them to learn SQL. Something like an open source version of the old Business Objects drag-n-drop interface would be great. 2) I could also use a more traditional reporting tool to replace (or augment) Crystal Reports. Heck, I'd take an open-source version of SQR if I could get it. I've looked at Pentaho and Jasper, but I have not had much success. (Can't remember exactly the issue right now, but I recall that they seemed to be huge products and I could never quite figure out how to do something simple with them.) I'd be interested in other suggestions from the community, or if people have some pointers to good newbie guides for Pentaho/Jasper, -Chris P.S. If the open source tools are written in Python, so much the better :) -- e-Mail is the equivalent of a postcard written in pencil. This message may not have been sent by me, or intended for you. It may have been read or even modified while in transit. e-Mail disclaimers have the same force in law as a note passed in study hall. If your corporate attorney says that you need an disclaimer in your signature, you need a new corporate attorney. -- 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] BI tools and postgresql
I think simple requirements would fit reporting tools space better. Another alternative worth looking into may be Eclipse BIRT On 26 Jul 2012, at 13:18, Chris Curvey wrote: > On Wed, Jul 25, 2012 at 1:41 PM, Mark Phillips > wrote: >> I am seeking suggestions for business intelligence and data mining tools >> compatible with postgresql. A new manager at a client's shop is leaning >> toward the MS offerings. I would like to be able to speak to the issue. >> > > I'm in the same boat, and while it's not really my job, I'm looking > for two things: > > 1) a tool for power users to be able to get their own data. There are > a few folks here who are smart enough to be trusted with direct access > to the database, but I don't want to ask them to learn SQL. Something > like an open source version of the old Business Objects drag-n-drop > interface would be great. > > 2) I could also use a more traditional reporting tool to replace (or > augment) Crystal Reports. Heck, I'd take an open-source version of > SQR if I could get it. > > I've looked at Pentaho and Jasper, but I have not had much success. > (Can't remember exactly the issue right now, but I recall that they > seemed to be huge products and I could never quite figure out how to > do something simple with them.) > > I'd be interested in other suggestions from the community, or if > people have some pointers to good newbie guides for Pentaho/Jasper, > > -Chris > > P.S. If the open source tools are written in Python, so much the better :) > > > -- > e-Mail is the equivalent of a postcard written in pencil. This > message may not have been sent by me, or intended for you. It may > have been read or even modified while in transit. e-Mail disclaimers > have the same force in law as a note passed in study hall. If your > corporate attorney says that you need an disclaimer in your signature, > you need a new corporate attorney. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- 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] BI tools and postgresql
On Thu, Jul 26, 2012 at 08:17:19AM -0400, Chris Curvey wrote: > 1) a tool for power users to be able to get their own data. There are > a few folks here who are smart enough to be trusted with direct access > to the database, but I don't want to ask them to learn SQL. Something > like an open source version of the old Business Objects drag-n-drop > interface would be great. > > 2) I could also use a more traditional reporting tool to replace (or > augment) Crystal Reports. Heck, I'd take an open-source version of > SQR if I could get it. I don't know since I haven't used it yet, but Sofa looks rather promising: http://www.sofastatistics.com/ Cheers, Peter -- http://sjamaan.ath.cx -- "The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music." -- Donald Knuth -- 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] BI tools and postgresql
Hi there, I've looked at Pentaho and Jasper, but I have not had much success. (Can't remember exactly the issue right now, but I recall that they seemed to be huge products and I could never quite figure out how to do something simple with them.) My advice here is to analyse the requirements and keep separated the BI components. Open source complete BI suites offered by BIRT, Pentaho and Jasper - to name a few - are really good products. I would suggest that - if you need to use the full product - you contact their sales representatives. Get more information, evaluate them, compare the products and pick one. Otherwise, I would simply look for specific products for each of the BI phases: * good ETL products are Kettle or Talend, both in Java * for reporting, I suggest Jasper report (with iReport), in Java again * for data mining you can look at Weka or, for in-database data mining algorithms in PostgreSQL, MADLib I am not sure if you need some tools for OLAP as well (I prefer to manage cubes and data marts within Postgres), you might want to look at Mondrian for Pentaho (Java). In my experience so far, in general the most common tools are for ETL and reporting. Data mining is less common (unfortunately), but in general it comes after the first two. All of the above technologies, require you to invest human resources and time in training. I prefer to see the BI framework as a set of components, rather than one single product. That gives you more flexibility and allows you to change a tool for a specific component in the future without changing the whole architecture. Of course, I take it for granted that you have already thought about the data warehouse layer (in case you have one). Cheers, Gabriele -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reporting tool for Npgsql
Le lundi 23 juillet 2012 à 11:53 -0700, Marcus Túlio Ramos a écrit : > I´m using Visual Studio 2010 - C# and Postgres with NPGSQL for data > provider. > > I´d like to know what´s the best option for a Report Builder on this > scenario. If you can separate it from your application, I find that a very convenient way to reduce reporting problems is to build an exporting tools that provides the data needed in a file in csv format, which users import into the various tools they have. A lot of tools can also connect to the db directly and build queries/reports (MS-Access for instance). You do need a good schema to do that, though. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique -- 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] BI tools and postgresql
Le mercredi 25 juillet 2012 à 10:41 -0700, Mark Phillips a écrit : > I am seeking suggestions for business intelligence and data mining tools > compatible with postgresql. > A new manager at a client's shop is leaning toward the MS offerings. I would > like to be able to speak to the issue. h..., I realize this will read like a wild exageration, but I sincerely doubt anybody pushing this type of solution can be reasoned. The money spent in licences alone would pay for scores of developpers to produce any kind of reporting you will need many times over (the data and its structure is what counts, reporting is easy if you have that) And that is probably small potatoes compared to the hours that the users will spend trying to have the mal-engineered thing output something. My guess is that your battle is political : a strong ally would be helpful. -- Vincent Veyron http://vincentveyron.com/ Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique -- 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] View definition and schema search path bug or expected behaviour?
On 07/25/2012 08:19 PM, Chris Bartlett wrote: Ah! The bob schema has no access privileges set. I had used pgAdmin3 - the schema definition pgAdmin3 reports is: CREATE SCHEMA bob AUTHORIZATION bob; (i.e., no grants) So: GRANT ALL ON SCHEMA bob TO bob; DROP VIEW bob.people; CREATE OR REPLACE VIEW bob.people_view AS SELECT people.name FROM people; SELECT * FROM bob.people_view -> returns records from bob.people as expected I had created the bob schema using pgAdmin3 (connected as bob), but when the schema owner is set to bob, there is no option in pgAdmin's New Schema... setup to grant privileges on the schema to bob. I guess I assumed that if bob owned the schema he would have all privileges on that schema. Thanks for pointing me in the right direction. There are a lot of dials to play with when creating objects and granting privileges. For my own sanity I make it a rule to confirm the privileges on an object when it is created or altered. Helps the blood pressure:) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BI tools and postgresql
Le jeudi 26 juillet 2012 à 08:17 -0400, Chris Curvey a écrit : > 1) a tool for power users to be able to get their own data. There are > a few folks here who are smart enough to be trusted with direct access > to the database, but I don't want to ask them to learn SQL. Something > like an open source version of the old Business Objects drag-n-drop > interface would be great. It's quite easy to build an exporting function with a web server, if you can use that. You can see one in action here : https://as-pro.biz/clients/login?nom_utilisateur=demo-mfp&mot_de_passe=demo-mfp Go into 'Statistiques' from the left menu. Check any checkbox next to 'Tableaux' (Menu 1, Menu 2...) Click on 'Envoyer' You can download the data in csv format for each table displayed by clicking into its header. This is an Apache web server using mod_perl to connect to a postgresql database. The module can be written in a day if you know the language (you can use others), the apache web server has a learning curve at first. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres assurances et des dossiers contentieux pour le service juridique -- 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] Full text search ts_heading strange result
Johann Spies writes: > On Thu, Jul 26, 2012 at 04:19:02PM +0800, Craig Ringer wrote: >> You'll need to store language information alongside each text value >> if you want to do anything more sophisticated. > I was afraid that that will be the case :) I'm not sure that there's anything horribly wrong with the strategy of using "simple" for everything. You won't get language-aware stemming, but maybe you don't need that. The problem with what you originally posted was not that "simple" was inadequate, but that you weren't applying it consistently --- you didn't have default_text_search_configuration set to match. 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] BI tools and postgresql
On Thu, Jul 26, 2012 at 9:04 AM, Vincent Veyron wrote: > Le mercredi 25 juillet 2012 à 10:41 -0700, Mark Phillips a écrit : >> I am seeking suggestions for business intelligence and data mining tools >> compatible with postgresql. >> A new manager at a client's shop is leaning toward the MS offerings. I would >> like to be able to speak to the issue. > > h..., I realize this will read like a wild exageration, but I > sincerely doubt anybody pushing this type of solution can be reasoned. > > The money spent in licences alone would pay for scores of developpers to > produce any kind of reporting you will need many times over (the data > and its structure is what counts, reporting is easy if you have that) > > And that is probably small potatoes compared to the hours that the users > will spend trying to have the mal-engineered thing output something. > > My guess is that your battle is political : a strong ally would be > helpful. Well, TBH, I wouldn't go that far. SQL server has some very nice OLAP extensions to SQL (PIVOT, UNPIVOT, ROLLUP, etc) that postgres doesn't have. SQL Server is expensive, but the costs are typically reasonable compared to paying for developers: in software development labor costs are very substantial (that's why I love this job, heh). For an end to end BI stack microsoft is a decent choice if (and only if) you're already heavily invested in the microsoft platform; familiarity being the most important criteria. In the same vein, Pentaho, BIRT, Jasper, etc are good choices if you're a java shop. (in my case, I'm doing almost 100% BI development now -- it's very 'hot' and I use 100% postgres, database driven, and it works great). 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] FATAL : could not read directory base": Invalid argument
FATAL : could not read directory base": Invalid argument 8.2.4 on Windows. Service will not start. Any idea -- 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] Smaller data types use same disk space
On Wed, Jul 25, 2012 at 4:12 PM, Tom Lane wrote: > Mike Christensen writes: >> On Wed, Jul 25, 2012 at 1:35 PM, Tom Lane wrote: >>> Yeah, this has been discussed multiple times. The sticking point is >>> the extra infrastructure needed to have a physical column numbering >>> different from the user-visible numbering, and the 100% certainty of >>> introducing a lot of bugs due to bits of code using one type of column >>> number where they should have used the other. We'll probably get it >>> done someday, but don't hold your breath ... > >> Has there been any discussion of providing the ability to re-order >> table columns through an ALTER TABLE command? > > It's more or less the same discussion. To do either one you need to > decouple the internal column order from what the user sees. I do not > think we'd bother with building the infrastructure involved if the > only application were squeezing out alignment padding; it's really > the (constant) requests for some kind of "ALTER TABLE REORDER COLUMNS" > feature that make it worth thinking about. I don't really think you'd need to decouple the internal column order from what the user sees. A REORDER COLUMNS command should re-build the table with the columns in the specified order. Internally, it should be no different from making a new table, copying all the data over, then deleting the old table. If there's any optimizations that can be done (such as making this faster on large tables), those could be done in future versions. I'd just like to changing column order easier without remaking the table or renaming columns and changing their data types (as suggested by Marc) Mike -- 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: how to calculate or know seq_scan scan how many blocks every time
hello everyone who can explain it ?thanks! -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-calculate-or-know-seq-scan-scan-how-many-blocks-every-time-tp5717892p5718082.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] Smaller data types use same disk space
On Thu, Jul 26, 2012 at 11:02 AM, Mike Christensen wrote: > I don't really think you'd need to decouple the internal column order > from what the user sees. A REORDER COLUMNS command should re-build > the table with the columns in the specified order. Internally, it > should be no different from making a new table, copying all the data > over, then deleting the old table. If there's any optimizations that > can be done (such as making this faster on large tables), those could > be done in future versions. I'd just like to changing column order > easier without remaking the table or renaming columns and changing > their data types (as suggested by Marc) That's a controversial point: doing it that way makes reordering of large tables highly impractical. A column map turns that into a catalog update which can be done at any time. I would argue that you can have it both ways: implement the map and have table rebuilding operations (like TRUNCATE and CLUSTER) opportunistically do the physical swap. 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] Smaller data types use same disk space
Merlin Moncure writes: > On Thu, Jul 26, 2012 at 11:02 AM, Mike Christensen wrote: >> I don't really think you'd need to decouple the internal column order >> from what the user sees. A REORDER COLUMNS command should re-build >> the table with the columns in the specified order. > That's a controversial point: doing it that way makes reordering of > large tables highly impractical. In particular, if the implementation works like that, you hardly need any system support at all. You can do the equivalent today with a few SQL commands: create a new table by selecting columns from the old, drop old table, rename new into place. The universal assumption has been that REORDER COLUMNS needs to work by just adjusting a few catalog entries, or it's not worth bothering with. 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] Smaller data types use same disk space
On Thu, Jul 26, 2012 at 9:12 AM, Merlin Moncure wrote: > On Thu, Jul 26, 2012 at 11:02 AM, Mike Christensen wrote: >> I don't really think you'd need to decouple the internal column order >> from what the user sees. A REORDER COLUMNS command should re-build >> the table with the columns in the specified order. Internally, it >> should be no different from making a new table, copying all the data >> over, then deleting the old table. If there's any optimizations that >> can be done (such as making this faster on large tables), those could >> be done in future versions. I'd just like to changing column order >> easier without remaking the table or renaming columns and changing >> their data types (as suggested by Marc) > > That's a controversial point: doing it that way makes reordering of > large tables highly impractical. A column map turns that into a > catalog update which can be done at any time. I would argue that you > can have it both ways: implement the map and have table rebuilding > operations (like TRUNCATE and CLUSTER) opportunistically do the > physical swap. Yea, it all comes down to level of effort. It sounds like adding the ability to store columns in a different order than they're displayed would have a huge development cost associated with it, however it would have some other advantages. For example, perhaps Postgres could always optimize the column order on disk automatically (similar to how a compiler does).. However, once the syntax is defined for how you re-order a column, the underlying implementation could be changed and improved in future versions without breaking changes. 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] PostgreSQL limitations question
On Sun, Jul 15, 2012 at 08:54:53PM +0200, Bartosz Dmytrak wrote: > 2012/7/13 Chris Angelico > > > Does that help? > > > Sure :) > I know what unlimited means, but I suggest to change docs to be more accurate. > Those "limits" are huge (e.g. number of indexes limited by pg_class table > size), but still exists. > > it is like the famous Henry Ford's color choose: > "Any customer can have a car painted any color that he wants so long as it is > black." > Number of indexes is unlimited until it is limited by pg_class table size > (regardless free HD space). What is the pg_class table size limit then? Is that really helping anyone? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Smaller data types use same disk space
On Thu, Jul 26, 2012 at 11:19 AM, Tom Lane wrote: > Merlin Moncure writes: >> On Thu, Jul 26, 2012 at 11:02 AM, Mike Christensen >> wrote: >>> I don't really think you'd need to decouple the internal column order >>> from what the user sees. A REORDER COLUMNS command should re-build >>> the table with the columns in the specified order. > >> That's a controversial point: doing it that way makes reordering of >> large tables highly impractical. > > In particular, if the implementation works like that, you hardly need > any system support at all. You can do the equivalent today with a few > SQL commands: create a new table by selecting columns from the old, > drop old table, rename new into place. Er, well, if you did that you'd also have to: 1) drop and recreate and foreign keys referring to your table, rebuild triggers, etc 2) drop and recreate and views and functions with a dependency on the table type 3) if you happened to have inlined the table type directly into another table for purposes of storage, punt. (table based composites have seem to suggest only logical reordering is possible anyways unless you want to cascade the physical reorder). Definitely non-trivial. CLUSTER requires a lock, but doesn't require messing around with the RI and other dependencies. You'd still have to work out #3 though. 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] Smaller data types use same disk space
Tom Lane wrote: > > That's a controversial point: doing it that way makes reordering of > > large tables highly impractical. > > In particular, if the implementation works like that, you hardly need > any system support at all. You can do the equivalent today with a few > SQL commands: create a new table by selecting columns from the old, > drop old table, rename new into place. The universal assumption has > been that REORDER COLUMNS needs to work by just adjusting a few catalog > entries, or it's not worth bothering with. But if the table has indexes, triggers, integrity constraints, check constraints, or default values for columns, moving these after a CREATE TABLE AS select ... is not easy. Personally, every time I had the need to reorder columns, having it as a fast operation was irrelevant to me, whereas figuring out how to deal with the above was the time-consuming part. If we had the feature but it was as slow as say, CLUSTER, that would already be quite nice. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] upgrade causes psql to not work
I looked on the http://jdbc.postgresql.org/ and could not find any docs on the jdbc string to use my old one with localhost and 127.0.0.1 do not work any more. here is the conf # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust host all all 127.0.0.1 255.255.255.255 trust host all all 0.0.0.0 0.0.0.0 trust Merlin Moncure sent the following on 7/25/2012 9:42 AM: On Wed, Jul 25, 2012 at 11:42 AM, Merlin Moncure wrote: On Wed, Jul 25, 2012 at 10:48 AM, BJ Freeman wrote: I apologize, psql is running if I do [root@main jdbc]# psql -U gameserver passwordfromentityengine.xml with out defining the host, it works. I believe it is using Unix pipes instead of TCP I also can manage the psql server through webmin. yeah. probably the issue is that your postgresql.conf is changed. you probably want to set listen_addresses to 'all' or something like that. er, '*' 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] BI tools and postgresql
Le jeudi 26 juillet 2012 à 10:02 -0500, Merlin Moncure a écrit : > job, heh). For an end to end BI stack microsoft is a decent choice > if (and only if) you're already heavily invested in the microsoft > platform; You're right. Because he posted on the list, I had assumed the OP _was_ using Postgres already, and that this was being challenged by the new manager. Maybe they are not using Postgres yet. > (in my case, I'm doing almost 100% BI development now -- it's very > 'hot' and I use 100% postgres, database driven, and it works great). Hence my rant about the impossibility to reason people who won't take that kind of evidence :-) -- Vincent Veyron -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WAL tuning advice
I'm hoping someone can help me figure out how to better tune my WAL parameters. Right now I am seeing WAL activity that is faster than I think it should be - we average about 30 write queries a second at our peak, and yet we're getting a new WAL file every 15-20 seconds. Our postgresql.conf parameters: wal_level = hot_standby # minimal, archive, or hot_standby checkpoint_segments = 10# in logfile segments, min 1, 16MB each checkpoint_timeout = 5min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 disables Here is "select * from pg_stat_bgwriter;" checkpoints_timed: 2081; checkpoints_req: 18889; buffers_checkpoint: 4320036; buffers_clean: 416180117; maxwritten_clean: 1580518; buffers_backend: 77676446; bueffers_backend_fsync: 0; buffers_alloc: 4804432940; stats_reset: "2012-06-24 23:13:10.716096-04" >From my previous research, I *think* that that data implies: 1) Due to high ratio of checkpoints_req to checkpoints_timed, I should increase checkpoints_segments, maybe from 10 to 15? 2) There are only about 206 buffers (buffers_checkpoint/(checkpoints_timed+checkpoints_req)) for each checkpoint. I think that is far lower than it should be, but I'm not positive about that, and I don't know what would increase it? 3) maxwitten_clean should be much lower than that? Thanks for your help! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] File system level backup
Hi, I have two virtual machines with two different versions of Postgresql. One machine contains Postgres 7.4.19 and another has Postgres 8.4.3. I also have other instances of these two virtual machines. I need to transfer the database from one machine to other. Both machines contain a database with the same name, for example: testdb, but with different data/values, but the structure is exactly same. I want to do the following: . Take file system level backup from from first machine for Postgres database 'testdb' and restore it in another instance that has SAME version of Postgres. i.e. backup the database 'testdb' from Postgres 7.4.19 and restore it on another virtual machine with same Postgres version 7.4.19. . Take file system level backup from from first machine for Postgres database 'testdb' and restore it in another instance that has DIFFERENT version of Postgres. i.e. backup the database testdb from Postgres 7.4.19 and restore it to another virtual machine with different Postgres version 8.4.3. I can achieve it with pg_dump and pg_restore, but not with file level backups. The data directory is /var/lib/pgsql/data that contains base directory amongst others, that contains directories for each database referred to by their OIDs. If I replace the complete data directory of one machine from the instance of another machine (with same or different Postgres version), It is failing to identify OIDs for that database. It should be possible in Postgres to swap two data directories in two different virtual machines without requiring pg_dump and pg_restore. With me, it doesn't work in both the cases mentioned above. In first case, it gives an error of missing OID for the database. In second case, it is giving version incompatibility issue. Is there a way in Postgres to do file system level backup? The objective is to push /var/lib/pgsql/data directory across different virtual machines, without the need to backup and restore Postgres database with 'pg_dump' and 'pg_restore' commands. Any help will be highly appreciated. Kind Regards, Manoj Agarwal
Re: [GENERAL] File system level backup
On Thu, Jul 26, 2012 at 3:39 AM, Manoj Agarwal wrote: > Hi, > > > > I have two virtual machines with two different versions of Postgresql. One > machine contains Postgres 7.4.19 and another has Postgres 8.4.3. I also > have other instances of these two virtual machines. I need to transfer the > database from one machine to other. Both machines contain a database with > the same name, for example: testdb, but with different data/values, but the > structure is exactly same. > > > > I want to do the following: > > · Take file system level backup from from first machine for > Postgres database ‘testdb’ and restore it in another instance that has SAME > version of Postgres. i.e. backup the database ‘testdb’ from Postgres 7.4.19 > and restore it on another virtual machine with same Postgres version 7.4.19. > > · Take file system level backup from from first machine for > Postgres database ‘testdb’ and restore it in another instance that has > DIFFERENT version of Postgres. i.e. backup the database testdb from > Postgres 7.4.19 and restore it to another virtual machine with different > Postgres version 8.4.3. > > > > I can achieve it with pg_dump and pg_restore, but not with file level > backups. The data directory is /var/lib/pgsql/data that contains base > directory amongst others, that contains directories for each database > referred to by their OIDs. If I replace the complete data directory of one > machine from the instance of another machine (with same or different > Postgres version), It is failing to identify OIDs for that database. > > > > It should be possible in Postgres to swap two data directories in two > different virtual machines without requiring pg_dump and pg_restore. With > me, it doesn’t work in both the cases mentioned above. In first case, it > gives an error of missing OID for the database. In second case, it is > giving version incompatibility issue. > > > > Is there a way in Postgres to do file system level backup? The objective is > to push /var/lib/pgsql/data directory across different virtual machines, > without the need to backup and restore Postgres database with ‘pg_dump’ and > ‘pg_restore’ commands. Any help will be highly appreciated. You definitely cannot take a filesystem level backup from one version and throw it into a different version (perhaps minor versions, but not 7.x vs. 8.x). This is basically what pg_upgrade was created to solve (however with a different purpose). But pg_upgrade definitely doesn't support 7.x, and I'm not even sure that it supports 8.x. In fact, I don't even know that 7.x is a supported version of postgresql in any context any longer. As for the issue of moving a filesystem level backup between identical versions, I believe that should work (although I have no clue if there were bugs preventing this in a version as old as 7.x). Can you provide exact details & commands of what you're trying to do, and the exact errors you're seeing? -- 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] File system level backup
Manoj Agarwal wrote on 26.07.2012 12:39: Hi, I have two virtual machines with two different versions of Postgresql. One machine contains Postgres 7.4.19 and another has Postgres 8.4.3. I also have other instances of these two virtual machines. I need to transfer the database from one machine to other. Both machines contain a database with the same name, for example: testdb, but with different data/values, but the structure is exactly same. Unrelated: why are you still using 7.3 It was end-of-live 2 years ago. In general: you can *not* just copy files between different versions or servers. The only way this works if both systems are running the exact same version, the exact same operating system and are using the same architecture. (So essentially a cold backup can be used to restore the server where the backup was taken). It should be possible in Postgres to swap two data directories in two different virtual machines without requiring pg_dump and pg_restore. No, this is not possible especially not with such an outdated version (I actually consider 8.4 as somewhat "outdated" as well). In your case pg_dump is the only way to go. -- 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 limitations question
2012/7/26 Bruce Momjian > > What is the pg_class table size limit then? Is that really helping > anyone? > > Fist of all - thanks for Your attentions, I really appreciate it. is that helping? - as it has been mentioned before: a small audience has noticed that fact, so probably not. I think it is a matter of description quality, not real limitations - they are huge, but... exists. Regards, Bartek
Re: [GENERAL] PostgreSQL limitations question
On Thu, Jul 12, 2012 at 8:53 AM, Adrian Klaver wrote: > On 07/12/2012 06:44 AM, Tom Lane wrote: >> >> Adrian Klaver writes: >>> >>> If a table has a maximum size and rows have size then at some point you >>> will reach a limit on number of rows per table. >> >> >> I think the "unlimited" should be read as "you'll hit some other limit >> first". For example, I trust no one would read that line as implying >> that we can store more data than will fit on the machine's disks. >> In the same way, it's not meant to suggest that the number of rows isn't >> effectively limited by the max table size. > > > I would agree, but the OPs question was: > > " > My question is: > how is it possible to *reach* unlimited rows in table? > " > and then you can have "partitioned" tables, while the system consider them almost independent tables (they are dependent only in the sense that if you read the parent it will read the childs too) but for the user they will look as one single table. so even say see limited by table size is not that true from certain point of view maybe: limited by other constraints (or some other words to say that) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] log select access
Hello everybody,
For PCI compliance I need to log user access to my PCI columns in a table and
retain for 2 years.
I know I can grep the log, but with 1m log rows/day and infrequent PCI access,
I'm thinking this isn't the most efficient method.
I've been thinking about a SELECT rule, for the access views defined on the
tables with PCI columns.
I reviewed the doc but belive I'm constrained
I would like to create a select rule that would log the statement in addition
to executing the select.
"Presently, ON SELECT rules must be unconditional INSTEAD rules and must have
actions that consist of a
single SELECT command. Thus, an ON SELECT rule effectively turns the table into
a view, whose visible"
makes me think I can't do this. Any advice how I might accomplish the goal.
format
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
Create rule pci_select as on select to creditcard do
Instead (begin
Insert into pci_log( sql statement);
Select * from creditcard;
end)
Thanks
Doug Little
Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz
Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
[cid:image001.jpg@01CD6B4B.4CEC3480] orbitz.com
Re: [GENERAL] log select access
On Thu, Jul 26, 2012 at 4:32 PM, Little, Douglas
wrote:
>
> Hello everybody,
>
>
>
> For PCI compliance I need to log user access to my PCI columns in a table
> and retain for 2 years.
>
> I know I can grep the log, but with 1m log rows/day and infrequent PCI
> access, I’m thinking this isn’t the most efficient method.
>
>
>
> I’ve been thinking about a SELECT rule, for the access views defined on
> the tables with PCI columns.
>
> I reviewed the doc but belive I’m constrained
>
> I would like to create a select rule that would log the statement in
> addition to executing the select.
>
>
>
> “Presently, ON SELECT rules must be unconditional INSTEAD rules and must
> have actions that consist of a
>
> single SELECT command. Thus, an ON SELECT rule effectively turns the table
> into a view, whose visible”
>
>
>
> makes me think I can’t do this. Any advice how I might accomplish the
> goal.
>
>
>
> format
>
> CREATE [ OR REPLACE ] RULE name AS ON event
>
> TO table [ WHERE condition ]
>
> DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
>
>
>
> Create rule pci_select as on select to creditcard do
>
> Instead (begin
>
> Insert into pci_log( sql statement);
>
> Select * from creditcard;
>
> end)
Well for starters you don't have access to the sql statement: the only
way to do that is through server side logging or capture inside the
application. You can be tricky with views (which is a select rule)
and try something like this:
create table log(log text);
create or replace function log() returns bool as $$
insert into log values (clock_timestamp() || ': ' || current_user);
select false
$$ language sql;
create view log_foo as select * from foo union all select * from foo
where (select log());
this crude way of making a 'statement level trigger' for a table. of
course, this only logs the database role which isn't much help if
you're using application managed roles -- but this is solvable as long
as you can get it the logged in user somehow -- perhaps through a
session table.
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] Schema-only dump dumps no constraints, no triggers
Hi, we are using "pg_dump -s" (schema-only) to copy the structure of a template/prototype database as a set-up for several dozen Fedora boxes. The dump used to work alright until very recently. Now, across new machines that are to be introduced into the network it consistently refuses to dump constraints and triggers - seems pg_dump just skips over them. Otherwise the dump seems to be complete, the schema-table-column layout is complete. We thought it was the template server problem, but the oddity is that a dump made with Windows version of pgAdmin3 comes out complete. The command we use is: /usr/bin/pg_dump -h -p -U -F p -N '*somename*' -N 'somename2' -N 'somename3' -N 'somename4' -N 'somename5' -T '*somename6*' -s -v -f /some/dir/schemacopy.sql The dump is made using a script we consider to be stable and therefore it hasn't changed since a long while ago. We also weren't able to pin down any other change between the systems where it previously worked and the ones where it now refuses to - the operating system (Fedora 16) is the same, the hardware is the same, the template database server is the same. It doesn't matter whether we are running the script on an up-to-date system or an outdated-off-liveCD-installation version, so it most probably is not update-related. The server (as a system) is sometimes under pretty much load so it might be resource-related - be it currently or previously. Searching through the archives, I have only stumbled upon a post from 2003 about a similar issue (http://archives.postgresql.org/pgsql-admin/2003-08/msg00239.php) which might be connected, however, since the reporter gave up quickly, the issue remained unsolved. How can we dig into this further? What might be happening? Best regards, Marek Kielar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication/cloning: rsync vs modification dates?
On Tue, Jul 17, 2012 at 07:42:38AM +1000, Chris Angelico wrote: > On Tue, Jul 17, 2012 at 4:35 AM, Sergey Konoplev > wrote: > > On Mon, Jul 16, 2012 at 8:01 PM, Chris Angelico wrote: > >> On Tue, Jul 17, 2012 at 1:58 AM, Michael Nolan wrote: > >>> As I understand the docs for rsync, it will use both mod time and file > >>> size > >>> if told not to do checksums. > > > > I wonder if it is correct in general to use mtime and size to perform > > these checks from the point of view of PostgreSQL. > > > > If it works with the current version then is there a guaranty that it > > will work with the future versions? > > That was my exact question. Ideally, I'd like to hear from someone who > works with the Postgres internals, but the question may not even be > possible to answer. You might want to look at the hackers list thread I started about the same topic a week before your post: http://archives.postgresql.org/pgsql-hackers/2012-07/msg00416.php Basically, you can only use mtime/size if you are replaying WAL. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication/cloning: rsync vs modification dates?
On Fri, Jul 27, 2012 at 9:53 AM, Bruce Momjian wrote: > You might want to look at the hackers list thread I started about the > same topic a week before your post: > > http://archives.postgresql.org/pgsql-hackers/2012-07/msg00416.php > > Basically, you can only use mtime/size if you are replaying WAL. I'll check that out in a bit; but hot standby includes replaying WAL, right? That's what we're doing - full live replication with possibility to "pg_ctl promote" a slave straight up to master. ChrisA -- 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] FATAL : could not read directory base": Invalid argument
On 07/26/2012 11:27 PM, Abraham, Danny wrote: FATAL : could not read directory base": Invalid argument 8.2.4 on Windows. Service will not start. Any idea Which version of Windows? Is it on a business network that might have group policy? Where is the data directory? Could you have tried to put it on a FAT32 or exFAT volume like an external hard drive or USB key? When did this error arise? Immediately after installation? After you changed something? Apparently at random? -- 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] how to calculate or know seq_scan scan how many blocks every time
On 07/25/2012 07:38 PM, leo xu wrote: hello: i know oracle scatter read blocks,can set db_file_multiblock_read_count every time, i see postgres,it exists scatter read blocks,it is seq_scan,how to know scan many bloks every time? If I understand correctly, you want to have a sequential scan read from more than one place at once. This is not currently supported. The effective_io_concurrency setting would be where you would set it, but it does not affect sequential scans at the moment. -- 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] Replication/cloning: rsync vs modification dates?
On Fri, Jul 27, 2012 at 09:57:55AM +1000, Chris Angelico wrote: > On Fri, Jul 27, 2012 at 9:53 AM, Bruce Momjian wrote: > > You might want to look at the hackers list thread I started about the > > same topic a week before your post: > > > > http://archives.postgresql.org/pgsql-hackers/2012-07/msg00416.php > > > > Basically, you can only use mtime/size if you are replaying WAL. > > I'll check that out in a bit; but hot standby includes replaying WAL, > right? That's what we're doing - full live replication with > possibility to "pg_ctl promote" a slave straight up to master. Yes, WAL is replayed in that case and any sub-second changes are going to be replayed from the WAL log. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Where is diskchecker.pl ?
On Wed, Jul 18, 2012 at 04:11:21PM +0200, jg wrote: > Hi, > > The PostgreSQK documentation refers to diskchecker.pl on the page > http://brad.livejournal.com/2116715.html > But on this page, the given link for diskchecker.pl does not exist anymore. > After some unsuccessfull queries on Google to find the missing file, > I wonder if one of you have a lin or a copy of this file. Script URL is: https://gist.github.com/3172656 Documentation updated. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 答复: [GENERAL] how to calculate or know seq_scan scan how many blocks every time
Hello Craig: I want to know a sequential scan( full scan table) it can read how many blocks every io? I know scan index read is one block every io. Oracle parameter db_file_multiblock_read_count it can control scattered read (full scan table), assume db_file_multiblock_read_count =10,when scan full table,every io request blocks is 10 blocks. I know scan index read is one block every io. So I want to when happen scan full table,postgresql scan full table scan how many blocks every io? Thank you! -邮件原件- 发件人: Craig Ringer [mailto:ring...@ringerc.id.au] 发送时间: 2012年7月27日 8:41 收件人: leo xu 抄送: pgsql-general@postgresql.org 主题: Re: [GENERAL] how to calculate or know seq_scan scan how many blocks every time On 07/25/2012 07:38 PM, leo xu wrote: > hello: > i know oracle scatter read blocks,can set > db_file_multiblock_read_count every time, > i see postgres,it exists scatter read blocks,it is seq_scan,how to > know scan many bloks every time? > If I understand correctly, you want to have a sequential scan read from more than one place at once. This is not currently supported. The effective_io_concurrency setting would be where you would set it, but it does not affect sequential scans at the moment. -- 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] upgrade causes psql to not work
On 07/26/2012 10:59 AM, BJ Freeman wrote: I looked on the http://jdbc.postgresql.org/ and could not find any docs on the jdbc string to use my old one with localhost and 127.0.0.1 do not work any more. here is the conf # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust host all all 127.0.0.1 255.255.255.255 trust host all all 0.0.0.0 0.0.0.0 trust Merlin Moncure sent the following on 7/25/2012 9:42 AM: On Wed, Jul 25, 2012 at 11:42 AM, Merlin Moncure wrote: On Wed, Jul 25, 2012 at 10:48 AM, BJ Freeman wrote: I apologize, psql is running if I do [root@main jdbc]# psql -U gameserver passwordfromentityengine.xml with out defining the host, it works. I believe it is using Unix pipes instead of TCP I also can manage the psql server through webmin. yeah. probably the issue is that your postgresql.conf is changed. you probably want to set listen_addresses to 'all' or something like that. er, '*' The pg_hba.conf file is half the answer. As Merlin pointed out you need to check postgresql.conf and see what listen_address is set to. Per his suggestion if it is not set to '*', this will listen on all IP addresses. This requires a restart of the server to take effect. merlin -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Schema-only dump dumps no constraints, no triggers
On 07/26/2012 04:09 PM, Marek Kielar wrote: Hi, we are using "pg_dump -s" (schema-only) to copy the structure of a template/prototype database as a set-up for several dozen Fedora boxes. The dump used to work alright until very recently. Now, across new machines that are to be introduced into the network it consistently refuses to dump constraints and triggers - seems pg_dump just skips over them. Otherwise the dump seems to be complete, the schema-table-column layout is complete. We thought it was the template server problem, but the oddity is that a dump made with Windows version of pgAdmin3 comes out complete. The command we use is: /usr/bin/pg_dump -h -p -U -F p -N '*somename*' -N 'somename2' -N 'somename3' -N 'somename4' -N 'somename5' -T '*somename6*' -s -v -f /some/dir/schemacopy.sql The dump is made using a script we consider to be stable and therefore it hasn't changed since a long while ago. We also weren't able to pin down any other change between the systems where it previously worked and the ones where it now refuses to - the operating system (Fedora 16) is the same, the hardware is the same, the template database server is the same. It doesn't matter whether we are running the script on an up-to-date system or an outdated-off-liveCD-installation version, so it most probably is not update-related. The server (as a system) is sometimes under pretty much load so it might be resource-related - be it currently or previously. Searching through the archives, I have only stumbled upon a post from 2003 about a similar issue (http://archives.postgresql.org/pgsql-admin/2003-08/msg00239.php) which might be connected, however, since the reporter gave up quickly, the issue remained unsolved. How can we dig into this further? What might be happening? Postgres version? Is there more than one version of PG on machine? The dump made with PgAdmin uses the same parameters? Any errors in the logs on either the dump or restore side? Best regards, Marek Kielar -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: 答复: [GENERAL] how to calculate or know seq_scan scan how many blocks every time
On 07/27/2012 10:04 AM, leoxu wrote: > Hello Craig: > I want to know a sequential scan( full scan table) it can read how many > blocks every io? >I know scan index read is one block every io. > >Oracle parameter db_file_multiblock_read_count it can control scattered > read (full scan table), >assume db_file_multiblock_read_count =10,when scan full table,every io > request blocks is 10 blocks. >I know scan index read is one block every io. > >So I want to when happen scan full table,postgresql scan full table scan > how many blocks every io? PostgreSQL uses the normal operating system facilities for file storage and access to do its I/O, rather than doing direct unbuffered I/O to files or even raw volumes like Oracle. It works quite differently, using regular buffered I/O. The amount of I/O required for and the I/O patterns of a sequential scan in PostgreSQL depend on: - The operating system readahead setting - Whether the operating system has any of the desired blocks cached - The file system and I/O scheduler in use Because of that I'm not really sure your question can be answered, except by benchmarking and real world measurement. PostgreSQL doesn't really have the same concept of "an I/O" - though I guess a pread() call would be fairly close. What is your goal? What are you trying to achieve? Why are you trying to determine this? What is the problem you are trying to solve? -- 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
[GENERAL] postgres maintenance db
I'm using postgres 9.1.4 on Suse Linux Enterprise Server 11. After successful installation I by default have one database installed called postgres. I'm starting the process of migrating some database schemas off Oracle and mysql onto postgres but I want to understand how to best set up the "databases". What is the purpose of the postgres database? I try and drop it and get "maintenance database can't be dropped" error. Should I create a separate database that has all my application schemas in it and let the postgres database be stand-alone, or should I put my application schemas inside the postgres database? I didn't really want my database to be called postgres, can it be renamed? Thank you Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-maintenance-db-tp5718134.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] postgres maintenance db
Hi, On 27 July 2012 08:07, hartrc wrote: > What is the purpose of the postgres database? I try and drop it and get > "maintenance database can't be dropped" error. 'postgres' database is something like 'mysql' database in MySQL. You should be able to see additional database like 'template0' and 'template1' > Should I create a separate database that has all my application schemas in > it and let the postgres database be stand-alone, or should I put my > application schemas inside the postgres database? You should create your own database (as many as you need) and create all schemas/tables/... there. See http://www.postgresql.org/docs/9.1/static/manage-ag-createdb.html -- you can use "CREATE DATABASE" or createdb command. > I didn't really want my database to be called postgres, can it be renamed? That's the system database let it be. -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication/cloning: rsync vs modification dates?
On Fri, Jul 27, 2012 at 9:57 AM, Chris Angelico wrote:
> On Fri, Jul 27, 2012 at 9:53 AM, Bruce Momjian wrote:
>> You might want to look at the hackers list thread I started about the
>> same topic a week before your post:
>>
>> http://archives.postgresql.org/pgsql-hackers/2012-07/msg00416.php
>>
>> Basically, you can only use mtime/size if you are replaying WAL.
>
> I'll check that out in a bit; but hot standby includes replaying WAL,
> right? That's what we're doing - full live replication with
> possibility to "pg_ctl promote" a slave straight up to master.
Hi, thanks for that link. Just got a chance to read through the thread.
In this post[1] the script executes "checkpoint" before
"pg_start_backup" - is that important? According to the docs[2]:
"There is an optional second parameter of type boolean. If true, it
specifies executing pg_start_backup as quickly as possible. This
forces an immediate checkpoint which will cause a spike in I/O
operations, slowing any concurrently executing queries."
Is "checkpoint; select pg_start_backup('foo');" the same as "select
pg_start_backup('foo',true);"? And what are the consequences of not
calling for a checkpoint that way? My understanding of the docs is
that the pg_start_backup call will hang until a checkpoint happens
organically, ie delaying the backup rather than other clients, but I'm
not really sure and haven't a sample database big or busy enough to
test this on.
Other than that, I think our current setup is fine. I have a script
that, every time a computer attempts to join the cluster, redoes the
"start backup, rsync, stop backup" sequence. I'm depending on (and
assuming) the correct transfer of the last bit of log via the
replication link, as soon as the new slave starts up - presumably
this'll all be provided from wal_keep_segments.
Again, thanks for the pointer! A good read.
ChrisA
[1] http://archives.postgresql.org/pgsql-hackers/2012-07/msg00417.php
[2] http://www.postgresql.org/docs/9.1/static/functions-admin.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
