[GENERAL] Indexing strategy
Hi, Need some help here. I have a large table (200 million rows already). There are two columns. ColA ColB There is an index on ColA. It's an important column. ColB is a boolean. Either 1 or 0. For about 10% of the data, ColB is 1. Otherwise it's default is 0. Now, my question: for that 10%, is it worth adding a conditional index on ColA WHERE ColB = 1? Will this save time for the queries that are related to 10% of my data where the ColB is 1? Or will the main leading index on ColA already speed things up as much as it can? Thanks for any ideas! PK -- 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] SSDs with Postgresql?
On Thu, April 14, 2011 18:56, Benjamin Smith wrote: After a glowing review at AnandTech (including DB benchmarks!) I decided to spring for an OCX Vertex 3 Pro 120 for evaluation purposes. It cost about $300 with shipping, etc and at this point, won't be putting any Considering that I sprang for 96 GB of ECC RAM last spring for around $5000, even if I put the OCX drives in pairs w/RAID1, I'd still come out well ahead if it allows me to put off buying more servers for a year or two. Exactly. Be aware of the risks, plan for failure and reap the rewards. -- 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] SSDs with Postgresql?
On Thu, April 14, 2011 20:54, Andrew Sullivan wrote: On Thu, Apr 14, 2011 at 12:27:34PM -0600, Scott Marlowe wrote: That's what a UPS and genset are for. Â Who writes critical stuff to *any* drive without power backup? Because power supply systems with UPS never fail. Right, there's obviously a trade-off here. Some of this has to do with how much your data is worth vs. how much the speed is worth. There's also the issue of whether you can stand to lose a few rows, and whether you can stand to lose them for a short time. For instance, collecting user comments might be a matter of great value, but if you write them to more than one system, you might not care whether one of the systems fails briefly. In that case, maybe big redundancy of cheap disks with power backup is good enough to meet the price:value ratio. On stock trades worth maybe millions of dollars, not so much: you miss your teeny window of opportunity to do a trade and suddenly you're out in the street wearing a barrel. I can think of lots of different points to be along that continuum, and surely nobody is suggesting that there is one right answer for everything. Exactly. To address the issue of something going wrong (it will, given enough time), we recently started trying out Pg's built-in replication (we were using Londiste - nothing wrong with it, but life is simpler now that the Pg overlords have realised what everyone has known for a long time). When the super-fast SSD-based machine fails, switching to a (slower) standard hard-drive based machine provides continuity and buys time while we get the primary machine back online. For us, life is better, thanks to SSDs. h -- 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 column aliasses in the same query
Hi, I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. This would make queries with a lot of logic in those aliased columns a lot smaller and this easier to write/debug. I already know you can use the following syntax: SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and lots of logic here) as col2, col3 FROM table) s WHERE col2 aValue But when you need to use (calculated) values from the actual record and or have sub-selects in your main select that also need to use these values things get really hairy. I don't know if the SQL specification allows it but I know that RDBMS's like Sybase already support this. Any thoughts? Cheers, Robert -- 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] Howto sort the result of UNION (without modifying its type)?
Clemens Eisserer linuxhi...@gmail.com Friday 08 April 2011 23:44:21 Hi, I have a query where I UNION several select statements which return IDs of type INTEGER. This works well, if the IDs don't need to be sorted: SELECT id FROM table WHERE id IN ((select id FROM table WHERE ...) UNION (SELECT id FROM table_ WHERE )) However I need the result the UNIONs to be sorted, so I tried the following: SELECT id FROM table WHERE id IN (SELECT col0 FROM ((select id FROM table WHERE ...) UNION (SELECT id FROM table_ WHERE )) AS col0 ORDER BY col0 OFFSET X LIMIT Y) FEHLER: Operator existiert nicht: integer = record ERROR: Operator does not exist: integer = record LINE 1: Select id FROM table WHERE id IN (Select col0 FROM ... Is there any way to get the results of UNIONs sorted, without converting it to record? Thanks, Clemens PS: I know the query looks really stupid, however the queries I UNION are auto-generated SQL. I don't have much choice here to write the query more efficient :/ May you try construct like this: SELECT i FROM (/* Your query example: */ SELECT i from v1 union select i from v2) as alias order by i; ? P.S. Sorry if this message was sent twice. -- 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] Using column aliasses in the same query
On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: Hi, I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. This would make queries with a lot of logic in those aliased columns a lot smaller and this easier to write/debug. I already know you can use the following syntax: SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and lots of logic here) as col2, col3 FROM table) s WHERE col2 aValue But when you need to use (calculated) values from the actual record and or have sub-selects in your main select that also need to use these values things get really hairy. I don't know if the SQL specification allows it but I know that RDBMS's like Sybase already support this. Any thoughts? It's easy to define a view or an SQL function and stash the hairy logic there. regards, Leif -- 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] Using column aliasses in the same query
On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote: On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: Hi, I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. This would make queries with a lot of logic in those aliased columns a lot smaller and this easier to write/debug. I already know you can use the following syntax: SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and lots of logic here) as col2, col3 FROM table) s WHERE col2 aValue But when you need to use (calculated) values from the actual record and or have sub-selects in your main select that also need to use these values things get really hairy. I don't know if the SQL specification allows it but I know that RDBMS's like Sybase already support this. Any thoughts? It's easy to define a view or an SQL function and stash the hairy logic there. regards, Leif True, but that is essentially the same thing as the example query I gave. There are plenty of cases where this approach is not workable. Cheers, Robert -- 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] Using column aliasses in the same query
2011/4/17 Robert J.C. Ivens rob...@roclasi.com: On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote: On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: Hi, I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. This would make queries with a lot of logic in those aliased columns a lot smaller and this easier to write/debug. I already know you can use the following syntax: SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and lots of logic here) as col2, col3 FROM table) s WHERE col2 aValue But when you need to use (calculated) values from the actual record and or have sub-selects in your main select that also need to use these values things get really hairy. I don't know if the SQL specification allows it but I know that RDBMS's like Sybase already support this. Any thoughts? It's easy to define a view or an SQL function and stash the hairy logic there. regards, Leif True, but that is essentially the same thing as the example query I gave. There are plenty of cases where this approach is not workable. select bar.*, b-c from (select i,i,i from foo ) as bar(a,b,c) where c!=1; you can also look at: http://www.postgresql.org/docs/9.0/static/queries-with.html -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- 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] Using column aliasses in the same query
Maybe you think about WITH queries? 2011/4/17, Robert J.C. Ivens rob...@roclasi.com: On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote: On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: Hi, I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. This would make queries with a lot of logic in those aliased columns a lot smaller and this easier to write/debug. I already know you can use the following syntax: SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and lots of logic here) as col2, col3 FROM table) s WHERE col2 aValue But when you need to use (calculated) values from the actual record and or have sub-selects in your main select that also need to use these values things get really hairy. I don't know if the SQL specification allows it but I know that RDBMS's like Sybase already support this. Any thoughts? It's easy to define a view or an SQL function and stash the hairy logic there. regards, Leif True, but that is essentially the same thing as the example query I gave. There are plenty of cases where this approach is not workable. Cheers, Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using column aliasses in the same query
On 17 apr 2011, at 13:43, pasman pasmański wrote: Maybe you think about WITH queries? 2011/4/17, Robert J.C. Ivens rob...@roclasi.com: On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote: On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote: Hi, I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. This would make queries with a lot of logic in those aliased columns a lot smaller and this easier to write/debug. I already know you can use the following syntax: SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and lots of logic here) as col2, col3 FROM table) s WHERE col2 aValue But when you need to use (calculated) values from the actual record and or have sub-selects in your main select that also need to use these values things get really hairy. I don't know if the SQL specification allows it but I know that RDBMS's like Sybase already support this. Any thoughts? It's easy to define a view or an SQL function and stash the hairy logic there. regards, Leif True, but that is essentially the same thing as the example query I gave. There are plenty of cases where this approach is not workable. Cheers, Robert CTE's are another option yes. But again it becomes really hairy (if not impossible) when you have a query where the calculated columns are used all over the place as input values for other subqueries. Being able to use the aliases in the same scope would simplify things tremendously. Cheers, Robert -- 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] Using column aliasses in the same query
Robert J.C. Ivens rob...@roclasi.com writes: I am not sure if there ever was a feature request for using defined column aliases in the rest of a query. Yes, we've heard that before. Many times. It's not going to happen, and here's why: it's flat out contrary to the SQL specification, as well as to the basic intuitive semantics of SQL. The SELECT list is supposed to be evaluated as the last step of a query (well, last except for ORDER BY, which is why there's an exception for that). It's nonsensical for WHERE etc to depend on the results of the SELECT list. As an example of why this is important, consider SELECT x/y AS z FROM tab WHERE y 0 If the WHERE clause doesn't act before the SELECT list is computed, the query is going to fail with divisions-by-zero, exactly what the WHERE clause is trying to prevent. So it'd be nonsensical to refer to z in the WHERE clause. 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] Installing PGDG on a fresh CentOS 5.6
Hello, I have installed CentOS from a CentOS-5.6-x86_64-bin-DVD-1of2.iso and then installed PGDG because I want to have PostgreSQL 8.4.7: # rpm -Uvh http://www.pgrpms.org/8.4/redhat/rhel-5-x86_64/pgdg-centos-8.4-2.noarch.rpm # cat /etc/yum.repos.d/pgdg-84-centos.repo [pgdg84] name=PostgreSQL 8.4 $releasever - $basearch baseurl=http://yum.pgrpms.org/8.4/redhat/rhel-$releasever-$basearch enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG [pgdg84-source] name=PostgreSQL 8.4 $releasever - $basearch - Source failovermethod=priority baseurl=http://yum.pgrpms.org/srpms/8.4/redhat/rhel-$releasever-$basearch enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Then I'm trying to install the PGDG-packaged PostgreSQL, but keep getting the error message I don't understand: # sudo yum install postgresql-server postgresql-devel postgresql-docs postgresql Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirror.atrpms.net * extras: mirror.atrpms.net * updates: mirror.atrpms.net Setting up Install Process Resolving Dependencies -- Running transaction check --- Package postgresql.x86_64 0:8.4.7-1PGDG.rhel5 set to be updated -- Processing Dependency: postgresql-libs = 8.4.7-1PGDG.rhel5 for package: postgresql -- Processing Dependency: libpq.so.5()(64bit) for package: postgresql --- Package postgresql-devel.i386 0:8.1.23-1.el5_6.1 set to be updated -- Processing Dependency: postgresql = 8.1.23-1.el5_6.1 for package: postgresql-devel --- Package postgresql-devel.x86_64 0:8.4.7-1PGDG.rhel5 set to be updated --- Package postgresql-docs.x86_64 0:8.4.7-1PGDG.rhel5 set to be updated --- Package postgresql-server.x86_64 0:8.4.7-1PGDG.rhel5 set to be updated -- Running transaction check --- Package postgresql-devel.i386 0:8.1.23-1.el5_6.1 set to be updated -- Processing Dependency: postgresql = 8.1.23-1.el5_6.1 for package: postgresql-devel -- Processing Dependency: libpq.so.4()(64bit) for package: php53-pgsql -- Processing Dependency: libpq.so.4()(64bit) for package: apr-util -- Processing Dependency: libpq.so.4()(64bit) for package: perl-DBD-Pg --- Package postgresql-libs.x86_64 0:8.4.7-1PGDG.rhel5 set to be updated -- Running transaction check --- Package compat-postgresql-libs.x86_64 0:4-1PGDG.rhel5 set to be updated --- Package postgresql-devel.i386 0:8.1.23-1.el5_6.1 set to be updated -- Processing Dependency: postgresql = 8.1.23-1.el5_6.1 for package: postgresql-devel -- Finished Dependency Resolution postgresql-devel-8.1.23-1.el5_6.1.i386 from updates has depsolving problems -- Missing Dependency: postgresql = 8.1.23-1.el5_6.1 is needed by package postgresql-devel-8.1.23-1.el5_6.1.i386 (updates) Error: Missing Dependency: postgresql = 8.1.23-1.el5_6.1 is needed by package postgresql-devel-8.1.23-1.el5_6.1.i386 (updates) You could try using --skip-broken to work around the problem You could try running: package-cleanup --problems package-cleanup --dupes rpm -Va --nofiles --nodigest The program package-cleanup is found in the yum-utils package. I've tried to find a combination of packages which would work, but haven't succeeded. Here are the currently installed packages: # rpm -qa|grep -i \\pg pgdg-centos-8.4-2 php53-pgsql-5.3.3-1.el5_6.1 perl-DBD-Pg-1.49-2.el5_3.1 # rpm -qa|grep -i post postgresql-libs-8.1.23-1.el5_6.1 postgresql-libs-8.1.23-1.el5_6.1 Does anybody know the magic command for me? (besides man yum :-) Thank you Alex -- 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] Installing PGDG on a fresh CentOS 5.6
On 04/17/11 11:05 AM, Alexander Farber wrote: ... --- Package postgresql-libs.x86_64 0:8.4.7-1PGDG.rhel5 set to be updated -- Running transaction check --- Package compat-postgresql-libs.x86_64 0:4-1PGDG.rhel5 set to be updated ... postgresql-devel-8.1.23-1.el5_6.1.i386 from updates has depsolving problems -- Missing Dependency: postgresql = 8.1.23-1.el5_6.1 is needed by package postgresql-devel-8.1.23-1.el5_6.1.i386 (updates) Error: Missing Dependency: postgresql = 8.1.23-1.el5_6.1 is needed by package postgresql-devel-8.1.23-1.el5_6.1.i386 (updates) ... odd, that compat-postgresql.libs should have worked ... OH! you have i386 (32 bit) postgresql.devel and are installing 64bit otherstuffs. remove it. rpm -e postgresql-devel THEN install the 64 bit shiny new stuff, and all should be good. if you need the 32 bit postgresql-devel, you can install that too seperately -- 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] Installing PGDG on a fresh CentOS 5.6
Alexander Farber alexander.far...@gmail.com writes: I have installed CentOS from a CentOS-5.6-x86_64-bin-DVD-1of2.iso and then installed PGDG because I want to have PostgreSQL 8.4.7: I'm not sure if CentOS is caught up, but postgresql 8.4.7 is available for RHEL5 from Red Hat --- it's called postgresql84. Possibly you'd have better luck with that than with Devrim's RPMs. Then I'm trying to install the PGDG-packaged PostgreSQL, but keep getting the error message I don't understand: It seems to be trying to update a 32-bit version of postgresql-devel: --- Package postgresql-devel.i386 0:8.1.23-1.el5_6.1 set to be updated If you have that installed, you probably need to remove it before you can install a 64-bit version, quite aside from any version differences. -devel packages typically are not multilib safe. 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] How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)
I have a single-disk virtual Linux system and a read-only dataset which is exposed to internet and completely replaced from time to time. I compiled following steps in order to secure and speedup such PostgreSQL/PostGIS instance: 1. Re-configure PostgreSQL server as following: a. Disabling autovacuum daemon. b. Setting postgresql.conf parameters: fsync=off synchronous_commit=off full_page_writes=off 2. Restart server, login as db admin, create database, create an app.-user. 3. Load dataset...: a. with owner 'app.-user' in schema PUBLIC; b. create indexes; c. issue a VACUUM ANALYZE command on user tables. 4. Create a 'read-only' user (login role) with only read access to user defined tables: GRANT SELECT ... TO read_only_user 5. Optimize and secure session by following parameters: SET transaction_read_only TO FALSE; SET TRANSACTION READ ONLY; 6. Go to step 3 in case of new data or a harddisk crash. Questions: = Any comments on securing such a PostgreSQL instance further? All user tables reside in schema PUBLIC, Ok? = Any comments on making this PostgreSQL instance 'robust'? E.g. which situations (except for harddisk crashes) can leave a read-only dataset in an inconsistent state where PostgreSQL server can't restart? An immediate shutdown? = Any comments on speeding up/optimizing such a read-only dataset? What about wal_level and archive_mode? Yours, 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] Indexing strategy
On 04/17/2011 03:52 AM, Phoenix Kiula wrote: Hi, Need some help here. I have a large table (200 million rows already). There are two columns. ColA ColB There is an index on ColA. It's an important column. ColB is a boolean. Either 1 or 0. For about 10% of the data, ColB is 1. Otherwise it's default is 0. Now, my question: for that 10%, is it worth adding a conditional index on ColA WHERE ColB = 1? Will this save time for the queries that are related to 10% of my data where the ColB is 1? Or will the main leading index on ColA already speed things up as much as it can? Thanks for any ideas! PK I'll take a wild guess: it depends on your data: if colA is highly selectable (ie unique or very nearly so), then it wont help. if colA is much less unique, and adding colB = 1 will drop the dup's a lot, and you'll always frame query query as ColA = ?? and colB = 1, then it'll help. said another way, if index on colA gets you very close to what you need, then index on colB wont help. If index on colA still has a lot of similar results, then you gotta ask yourself: will very few have colB = 1? If yes, then add the index. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Howto sort the result of UNION (without modifying its type)?
Clemens Eisserer linuxhi...@gmail.com Friday 08 April 2011 23:44:21 Hi, I have a query where I UNION several select statements which return IDs of type INTEGER. This works well, if the IDs don't need to be sorted: SELECT id FROM table WHERE id IN ((select id FROM table WHERE ...) UNION (SELECT id FROM table_ WHERE )) However I need the result the UNIONs to be sorted, so I tried the following: SELECT id FROM table WHERE id IN (SELECT col0 FROM ((select id FROM table WHERE ...) UNION (SELECT id FROM table_ WHERE )) AS col0 ORDER BY col0 OFFSET X LIMIT Y) FEHLER: Operator existiert nicht: integer = record ERROR: Operator does not exist: integer = record LINE 1: Select id FROM table WHERE id IN (Select col0 FROM ... Is there any way to get the results of UNIONs sorted, without converting it to record? Thanks, Clemens PS: I know the query looks really stupid, however the queries I UNION are auto-generated SQL. I don't have much choice here to write the query more efficient :/ May you try construct like this: SELECT i FROM (/* Your query example: */ SELECT i from v1 union select i from v2) as alias order by i; ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] interval hour to minute or interval day to minute
Hi I discovered the 'fields' option of 'interval', but i can't figure out from the docs how it is supposed to work. Are hour to minute and day to minute really the same thing? And if not, in what circumstances are they treated differently? psql (8.4.7) Type help for help. postgres= select '3 years 2 months - 1 day + 10 hours 5 minutes'::interval day to minute; interval -- 3 years 2 mons -1 days +10:05:00 (1 row) postgres= select '3 years 2 months - 1 day + 10 hours 5 minutes'::interval hour to minute; interval -- 3 years 2 mons -1 days +10:05:00 (1 row) Warm regards Jack Douglas -- 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 configure a read-only database server and session? (Was: read-only UNLOGGED tables)
On 04/18/2011 04:04 AM, Stefan Keller wrote: 5. Optimize and secure session by following parameters: SET transaction_read_only TO FALSE; SET TRANSACTION READ ONLY; AFAIK, neither of those have any effect on security. They're purely advisory hints to Pg. Personally I think it'd be cool if read-only transactions were denied the use of INSERT/UPDATE/DELETE, any untrusted PLs, and any INSERT/UPDATE/DELETE via SPI from PLs. But would be cool isn't want to try to implement it and I'm sure if it were easy, it'd have already been done. All user tables reside in schema PUBLIC, Ok? Yep. Make sure you don't grant CREATE on public to the target user, only grant USAGE, and revoke all from public. = Any comments on making this PostgreSQL instance 'robust'? E.g. which situations (except for harddisk crashes) can leave a read-only dataset in an inconsistent state where PostgreSQL server can't restart? An immediate shutdown? AFAIK: - Kill -9 of a backend - segfault / sigabrt / sigbus / etc of a backend (though backends may try to handle some of these it'd normally be unsafe and I doubt it, but I haven't checked) - Kill -9 of the postmaster - OS crash or unexpected reset - probably other things = Any comments on speeding up/optimizing such a read-only dataset? Depending on dataset size and access patterns, it could be worth pinning a few indexes in a tablespace that lives on a ramdisk. Usually Pg's and the OS's cache management will do the job well, but if you know more than them - say, that this index will always be really hot, or that certain queries are more important than others and must be more responsive - you can play with that sort of thing. -- 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 Start up Error
Dear all, Today I by mistake deleted all the contents of pg_log pg_xlog directory and my Postgresql refuses to start due to the below error :- 2011-04-18 10:51:39 ISTLOG: database system was interrupted; last known up at 2011-04-18 10:06:42 IST 2011-04-18 10:51:39 ISTLOG: could not open file pg_xlog/000100060007 (log file 6, segment 7): No such file or directory 2011-04-18 10:51:39 ISTLOG: invalid primary checkpoint record 2011-04-18 10:51:39 ISTLOG: could not open file pg_xlog/0001000500C9 (log file 5, segment 201): No such file or directory 2011-04-18 10:51:39 ISTLOG: invalid secondary checkpoint record 2011-04-18 10:51:39 ISTPANIC: could not locate a valid checkpoint record 2011-04-18 10:51:39 ISTLOG: startup process (PID 11021) was terminated by signal 6: Aborted 2011-04-18 10:51:39 ISTLOG: aborting startup due to startup process failure I try to reinstall with the same previous data directory, but it fails again. Any suggestions to solve it without losing the previous data. Thanks best Regards, Adarsh Sharma -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
