[GENERAL] Re: Is PostgreSQL 9.3 using indexes for pipelined top-N window function queries?
Behrang Saeedzadeh, 15.02.2014 02:35: Hi, I just stumbled upon this article from 2012 [1], according to which (emphasis mine): Window functions offer yet another way to implement pagination in SQL. This is a flexible, and above all, standards-compliant method. However, only SQL Server and the Oracle database can use them for a pipelined top-N query. */PostgreSQL does not use indexes for those queries and therefore executes them very inefficiently./* MySQL does not support window functions at all. Is this still the case? Or is PostgreSQL 9.3 capable to execute suchlike queries efficiently? [1] http://use-the-index-luke.com/sql/partial-results/window-functions My local Postgres 9.3 installation does use an index for such a query. I ran a quick (an un-scientific) test on a sample table filled with auto-generated test data: postgres= \d+ products Table public.products Column | Type | Modifiers | Storage | Stats target | Description ---++---+--+--+- product_id| integer| not null | plain| | ean_code | bigint | not null | plain| | product_name | character varying(100) | not null | extended | | manufacturer_name | character varying | not null | extended | | price | numeric(10,2) | not null | main | | publish_date | date | not null | plain| | Indexes: products_pkey PRIMARY KEY, btree (product_id) idx_publish_date btree (publish_date, product_id) Has OIDs: no postgres= select count(*) from products; count - 100 (1 row) Then I tried the following statement: select * from ( select products.*, row_number() over (order by publish_date, product_id) as rn from products ) tmp where rn between 200 and 300 order by publish_date, product_id; http://explain.depesz.com/s/5u9 And Postgres does use the index idx_publish_date. Interesting enough: my local Oracle 11.2 does *not* use an index scan for the above test (same test data). On the other hand Oracle's table scan is much faster (about ~0.5 seconds) for the first pages but than gets slower when increasing the limits of the pagincation. Oracle takes over 5 seconds when changing the limit to between 90 and 900100 whereas Postgres execution time pretty much stays the same. -- 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] excute function before DROP EXTENSION
Hi Beena, How about using *TWO* extension? 1. ext_install 2. ext_uninstall Let me explain, 1. ext_uninstall will contain two command, A] SQL calling to cleanup function B] DROP EXTENSION ext_install 2. ext_install will have DROP EXTENSION IF EXISTS ext_install (cycle of install/uninstall) other wise you may not able to create ext_install again other command. it wont sound good, but AFAICS no other option. Thoughts? Comments? Regards, Amul Sul -- View this message in context: http://postgresql.1045698.n5.nabble.com/excute-function-before-DROP-EXTENSION-tp5758279p5792350.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] Toast and slice of toast
On 2014-02-17 14:16:33 +1100, Haribabu Kommi wrote: On Sun, Feb 16, 2014 at 9:38 PM, Rémi Cura remi.c...@gmail.com wrote: Hey Dear List, could somebody point me to some ressources about getting only parts of toasted data? I have a very big custom type and I would like to take blocks of it (like byte A to B then byte C to D then... ). I found a function in http://doxygen.postgresql.org/tuptoaster_8c.html#called toast_fetch_datum_slice, is it the right way to use it (a for loop and calling it several time?). pg_detoast_datum_slice is the function which will solve your problem. Note that you need to prevent your type/column from being compressed for that being effective. Check the storage options for CREATE TABLE et al. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] could not create lock file postmaster.pid: No such file or directory, but file does exist
Hi, This is my first post to this list, so I hope I am posting it to the correct lists. But I am really stuck and getting pretty desperate at the moment. This weekend my database crashed while importing some Openstreetmapdata and I can't get it back to work again. It happened before and normally I would reset the WAL-dir with the pg_resetxlog command. I would loose some data but that would be all. This time it is somehow different because he doesn't recognize any of the important files anymore. For example when I try to start Postgresql again with the command: /usr/lib/postgresql/9.1/bin/pg_ctl -D OSM/ start I get the following error: FATAL: could not create lock file postmaster.pid: No such file or directory But when I do a ls -l on the directory I can see the file exists. drwx-- 0 postgres postgres 0 Jan 24 10:07 backup drwx-- 0 postgres postgres 0 Feb 14 11:10 base drwx-- 0 postgres postgres 0 Feb 17 09:46 global drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_clog -rwxr-xr-x 0 postgres postgres 4476 Oct 11 10:49 pg_hba.conf -rwxr-xr-x 0 postgres postgres 1636 Oct 11 10:49 pg_ident.conf drwx-- 0 postgres postgres 0 Feb 17 11:29 pg_log drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_multixact drwx-- 0 postgres postgres 0 Feb 17 08:58 pg_notify drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_serial drwx-- 0 postgres postgres 0 Feb 12 09:58 pg_stat_tmp drwx-- 0 postgres postgres 0 Feb 14 09:01 pg_subtrans drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_tblspc drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_twophase -rwxr-xr-x 0 postgres postgres 4 Oct 11 10:49 PG_VERSION drwx-- 0 postgres postgres 0 Feb 14 13:37 pg_xlog -rwxr-xr-x 0 postgres postgres 19168 Oct 11 11:41 postgresql.conf -rwxr-xr-x 0 postgres postgres 121 Feb 17 08:57 postmaster.opts -rwxr-xr-x 0 postgres postgres88 Feb 17 08:58 postmaster.pid I cannot perform any action on the postmaster.pid file. I tried cp, mv and rm, but nothing works. Is there anything I can do to make the system recognize this file again? And get my database up and running? Or is all hopelessly lost? I have Postgresql 9.1 installed on Ubuntu 12.04. Kind regards, Rob.
[GENERAL] Re: could not create lock file postmaster.pid: No such file or directory, but file does exist
Rob Goethals wrote: This is my first post to this list, so I hope I am posting it to the correct lists. But I am really stuck and getting pretty desperate at the moment. You should not post to more than one list. This weekend my database crashed while importing some Openstreetmapdata and I can’t get it back to work again. It happened before and normally I would reset the WAL-dir with the pg_resetxlog command. I would loose some data but that would be all. That is not a good idea. PostgreSQL should recover from a crash automatically. If you run pg_resetxlog your database cluster is damaged, and all you should do is pg_dump all the data you can, run initdb and import the data. This time it is somehow different because he doesn’t recognize any of the important files anymore. For example when I try to start Postgresql again with the command: /usr/lib/postgresql/9.1/bin/pg_ctl -D OSM/ start I get the following error: FATAL: could not create lock file postmaster.pid: No such file or directory But when I do a ls –l on the directory I can see the file exists. [...] -rwxr-xr-x 0 postgres postgres88 Feb 17 08:58 postmaster.pid I cannot perform any action on the postmaster.pid file. I tried cp, mv and rm, but nothing works. Is there anything I can do to make the system recognize this file again? And get my database up and running? Or is all hopelessly lost? I have Postgresql 9.1 installed on Ubuntu 12.04. What is the error message you get for cp, mv or rm? Can you describe the crash of your machine in greater detail? What was the cause? One wild guess: could it be that the OS automatically remounted the file system read-only because it encountered a problem? Check your /var/log/messages (I hope the location is the same on Ubuntu and on RHEL). In that case unmount, fsck and remount should solve the problem. Yours, Laurenz Albe -- 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 of shut down standby does not work?
Hi all, I have a master-slave configuration running the master with WAL archiving enabled and the slave in recovery mode reading back the WAL files from the master (Log-shipping standby as described in http://www.postgresql.org/docs/9.1/static/warm-standby.html) I take frequent backups of the standby server: 1) Stop standby server (fast shutdown). 2) Rsync to another fileserver 3) Start standby server. I just tried to recover one of these backups which *failed* with the following errors: 2014-02-17 14:27:28 CET LOG: incomplete startup packet 2014-02-17 14:27:28 CET LOG: database system was shut down in recovery at 2013-12-25 18:00:03 CET 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30061 (log file 483, segment 97): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid primary checkpoint record 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30060 (log file 483, segment 96): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid secondary checkpoint record 2014-02-17 14:27:28 CET PANIC: could not locate a valid checkpoint record 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:32 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET LOG: startup process (PID 26186) was terminated by signal 6: Aborted 2014-02-17 14:27:33 CET LOG: aborting startup due to startup process failure So it seems the server is missing some WAL files which are not in the backup? Or is it simply not possible to take a backup of a standby server in recovery? Best, Juergen signature.asc Description: OpenPGP digital signature
[GENERAL] Re: could not create lock file postmaster.pid: No such file or directory, but file does exist
-Oorspronkelijk bericht- Van: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Verzonden: maandag 17 februari 2014 14:22 Aan: Rob Goethals Onderwerp: RE: could not create lock file postmaster.pid: No such file or directory, but file does exist Dear Rob, you should send your reply to the list. This way a) people know that your problem is solved and won't spend their time trying to help you. b) others can benefit from the information. OK, clear. I hereby send this reply also to the list. This weekend my database crashed while importing some Openstreetmapdata and I can’t get it back to work again. It happened before and normally I would reset the WAL-dir with the pg_resetxlog command. I would loose some data but that would be all. That is not a good idea. PostgreSQL should recover from a crash automatically. If you run pg_resetxlog your database cluster is damaged, and all you should do is pg_dump all the data you can, run initdb and import the data. But what if Postgresql doesn't recover automatically? When my database crashed and I try to restart it, I most of the time get a message like: LOG: could not open file pg_xlog/0001011400D2 (log file 276, segment 210): No such file or directory LOG: invalid primary checkpoint record LOG: invalid secondary checkpoint link in control file PANIC: could not locate a valid checkpoint record LOG: startup process (PID 3604) was terminated by signal 6: Aborted LOG: aborting startup due to startup process failure Interesting. How did you get PostgreSQL into this state? Did you set fsync=off or similar? Which storage did you put pg_xlog on? I am adding OSM-changefiles to my database with the command: osm2pgsql --append --database $database --username $user --slim --cache 3000 --number-processes 6 --style /usr/share/osm2pgsql/default.style --extra-attributes changes.osc.gz At the moment of the crash the postgresql-log says: 2014-02-15 00:49:04 CET LOG: WAL writer process (PID 1127) was terminated by signal 6: Aborted 2014-02-15 00:49:04 CET LOG: terminating any other active server processes 2014-02-15 00:49:04 CET [unknown] WARNING: terminating connection because of crash of another server process 2014-02-15 00:49:04 CET [unknown] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. So what exactly is happening, I don't know. When it is trying to startup again this is the logfile output: 2014-02-15 00:49:08 CET LOG: could not open temporary statistics file global/pgstat.tmp: Input/output error 2014-02-15 00:49:14 CET LOG: all server processes terminated; reinitializing 2014-02-15 00:49:17 CET LOG: database system was interrupted; last known up at 2014-02-15 00:32:01 CET 2014-02-15 00:49:33 CET [unknown] [unknown]LOG: connection received: host=[local] 2014-02-15 00:49:33 CET [unknown] FATAL: the database system is in recovery mode 2014-02-15 00:49:56 CET LOG: database system was not properly shut down; automatic recovery in progress 2014-02-15 00:49:57 CET [unknown] [unknown]LOG: connection received: host=[local] 2014-02-15 00:49:57 CET [unknown] FATAL: the database system is in recovery mode 2014-02-15 00:50:01 CET LOG: redo starts at 114/C8B27330 2014-02-15 00:50:02 CET LOG: could not open file pg_xlog/0001011400CB (log file 276, segment 203): No such file or directory 2014-02-15 00:50:02 CET LOG: redo done at 114/CA80 2014-02-15 00:50:02 CET LOG: checkpoint starting: end-of-recovery immediate 2014-02-15 00:50:05 CET PANIC: could not create file pg_xlog/xlogtemp.5390: Input/output error 2014-02-15 00:50:22 CET [unknown] [unknown]LOG: connection received: host=[local] 2014-02-15 00:50:22 CET [unknown] FATAL: the database system is in recovery mode 2014-02-15 00:50:23 CET LOG: startup process (PID 5390) was terminated by signal 6: Aborted 2014-02-15 00:50:23 CET LOG: aborting startup due to startup process failure Furthermore I checked my conf-file and my fsync is indeed set to off. I mounted a directory on a NTFS network-disk (because of the available size and considering the amount of OSM-data is pretty big). This is where I put all my database data, so also the pg_xlog. Is there a better procedure to follow when something like this happens? I am fairly new at the whole Postgresql thing so I am very willing to learn all about it anyway I can from experienced users. I am googling all my way round the internet to try and solve all the questions I have, but as with many things there's most of the time more than 1 answer to a problem and for me it is very hard to figure out what is the best solution. No, in that case I would restore from a backup. One wild guess: could it be that the OS automatically remounted the file system read-only because it
Re: [GENERAL] Re: could not create lock file postmaster.pid: No such file or directory, but file does exist
On 17 February 2014 14:42, Rob Goethals / SNP rob.goeth...@snp.nl wrote: 2014-02-15 00:49:04 CET LOG: WAL writer process (PID 1127) was terminated by signal 6: Aborted Signal 6 is usually caused by hardware issues. Then again, you also say: I mounted a directory on a NTFS network-disk (because of the available size and considering the amount of OSM-data is pretty big). This is where I put all my database data, so also the pg_xlog. That will cause problems as well. SMBFS does not support all the necessary file flags, locks and such that the database needs to operate on those files in a safe way. That's probably worse than running with sciss... ehr... fsync=off Alban Hertroys. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] Re: could not create lock file postmaster.pid: No such file or directory, but file does exist
Rob Goethals / SNP rob.goeth...@snp.nl writes: When it is trying to startup again this is the logfile output: ... 2014-02-15 00:50:05 CET PANIC: could not create file pg_xlog/xlogtemp.5390: Input/output error The above PANIC is the reason for the abort that happens immediately thereafter. On local storage I'd think this meant disk hardware problems, but since you say you've got the database on an NTFS volume, what it more likely means is that there's a bug in the kernel's NTFS support. Anyway, it's fruitless to try to get Postgres going again until you have a stable filesystem underneath it. Generally speaking, longtime Postgres users are very suspicious of running Postgres atop any kind of networked filesystem. We find that network filesystems are invariably less stable than local ones. NTFS seems likely to be a particularly unfortunate choice from this standpoint, as you get to benefit from Windows' bugs along with Linux's. 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] Database connection Pooling using JNDI
Hi, I am planning to create my Application to use the database connection via connection pool which i registered to the JNDI service. I found one link that speaks about it: http://www.postgresql.org/docs/7.4/static/jdbc-datasource.html But the classes mentioned in the above link is not available in the latest Postgresql jdbc driver. Can you please point to a place where i can get all the information related the subject mentioned above. Thanks, Sumit
[GENERAL] Re: could not create lock file postmaster.pid: No such file or directory, but file does exist
Rob Goethals wrote: OK, clear. I hereby send this reply also to the list. Cool. Interesting. How did you get PostgreSQL into this state? Did you set fsync=off or similar? Which storage did you put pg_xlog on? 2014-02-15 00:49:04 CET LOG: WAL writer process (PID 1127) was terminated by signal 6: Aborted Ouch. Furthermore I checked my conf-file and my fsync is indeed set to off. Well, that is one reason why crash recovery is not working. I mounted a directory on a NTFS network-disk (because of the available size and considering the amount of OSM-data is pretty big). This is where I put all my database data, so also the pg_xlog. Double ouch. CIFS is not a supported file system. At least that explains your problems. Try with a local file system or NFS with hard foreground mount. Yours, Laurenz Albe -- 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] Toast and slice of toast
Thanks everybody ! Cheers, Rémi-C 2014-02-17 10:37 GMT+01:00 Andres Freund and...@2ndquadrant.com: On 2014-02-17 14:16:33 +1100, Haribabu Kommi wrote: On Sun, Feb 16, 2014 at 9:38 PM, Rémi Cura remi.c...@gmail.com wrote: Hey Dear List, could somebody point me to some ressources about getting only parts of toasted data? I have a very big custom type and I would like to take blocks of it (like byte A to B then byte C to D then... ). I found a function in http://doxygen.postgresql.org/tuptoaster_8c.html#calledtoast_fetch_datum_slice, is it the right way to use it (a for loop and calling it several time?). pg_detoast_datum_slice is the function which will solve your problem. Note that you need to prevent your type/column from being compressed for that being effective. Check the storage options for CREATE TABLE et al. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
[GENERAL] Re: could not create lock file postmaster.pid: No such file or directory, but file does exist
OK, it is clear to me that I didn't make the best choices setting up this database. :( I am happy I found this list because I am learning a lot in a very short period of time. :) Thank you all for your tips and comments. I will definitely move the database to a Linux-system and set fsync to on. I hope this will give me a more stable environment. Furthermore I'll dive into the whole database-backup subject so next time I'll have something to restore if things go wrong. Rob Goethals. -Oorspronkelijk bericht- Van: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Verzonden: maandag 17 februari 2014 16:20 Aan: Rob Goethals CC: 'pgsql-general@postgresql.org' Onderwerp: RE: could not create lock file postmaster.pid: No such file or directory, but file does exist Rob Goethals wrote: OK, clear. I hereby send this reply also to the list. Cool. Interesting. How did you get PostgreSQL into this state? Did you set fsync=off or similar? Which storage did you put pg_xlog on? 2014-02-15 00:49:04 CET LOG: WAL writer process (PID 1127) was terminated by signal 6: Aborted Ouch. Furthermore I checked my conf-file and my fsync is indeed set to off. Well, that is one reason why crash recovery is not working. I mounted a directory on a NTFS network-disk (because of the available size and considering the amount of OSM-data is pretty big). This is where I put all my database data, so also the pg_xlog. Double ouch. CIFS is not a supported file system. At least that explains your problems. Try with a local file system or NFS with hard foreground mount. Yours, Laurenz Albe -- 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] Database connection Pooling using JNDI
Hi Sumit, I think you need to refer to the documentation for the JDBC driver and not the actual PostgresSQL database server documentation. See here: http://jdbc.postgresql.org/documentation/92/jndi.html#ds-jndi Hope this helps. Andrew On 17/02/14 09:27, Sumit Sureka wrote: Hi, I am planning to create my Application to use the database connection via connection pool which i registered to the JNDI service. I found one link that speaks about it: http://www.postgresql.org/docs/7.4/static/jdbc-datasource.html But the classes mentioned in the above link is not available in the latest Postgresql jdbc driver. Can you please point to a place where i can get all the information related the subject mentioned above. Thanks, Sumit -- 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 do I track down a possible locking problem?
I have a production system using Postgresql 9.1.2. The system basically receives messages, puts them in a queue, and then several parallel modules, each in its own thread, read from that queue, and perform two inserts, then release the message to the next queue for non-database-related processing. Today, after we received complaints from a customer about delays, I noticed odd behavior in the system. About 2 percent of the messages were inserted into the tables more than an hour after they got into the system. The queue never has more than 27,000 messages at the same time, and all together, the parallel modules process about 5000 or 6000 messages per minute. So basically, the delay for a single message should never be more than a few minutes. Even if one module gets stuck, another will grab the next message from the queue. I believe the only way for a message to be stuck for so long would be for it to be grabbed by a module, and then for the database write to be somehow delayed for a hour, although it's merely a simple insert performed with a prepared statement. The database in production is very busy with millions of writes per hour. Could there be a situation in which a particular connection gets starved while other connections are able to run queries without noticeable delay? How can I truck such locks down? Does anybody have any ideas other than starvation? The system lets me view statistics of how many messages were processed in each modules and the average latency. None of the four modules running has long average latency or low number of messages processes, so I don't think the issue is related to any particular thread in my (Java) system being slow or locked away by the others. TIA, Herouth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Deleted files still open long after droping a database
Hi List, I have droped a database a few hours ago to reclaim some badly-needed space, but that disk space has not been freed yet. Investigating further, there are lots of deleted but open files that seem to correspond to the droped db, all open by some postgres process. A lot of process were db connections to databases other than the droped one (I'm not using connection pooling). The other process is the autovacuum launcher. I have reset the various connection processes, but I hesitate to kill the autovacuum process. I'm not sure wether that's relevant, but I moved the database to another server using slony1-2.2.2 before droping it on the old server. There were already no more slon processes running when I droped the db. 1) why does this happen at all (keeping files from a droped databse open, even by processes that never connected to that specific db) ? 2) Can the autovacuum launcher process be safely killed (there are no long- running vacuum queries) ? 3) Is there a generally cleaner way to do all this ? PostgreSQL 9.1.2 on debian linux squeeze $ ps aux|grep autovacuum postgres 5025 0.0 0.2 122992 39124 ?Ss Jan27 14:28 postgres: autovacuum launcher process $ sudo lsof +aL1 /path/to/pgsql/ postgres 5025 postgres9u REG8,74308992 0 14943966 /path/to/pgsql/base/35355/5979724 (deleted) postgres 5025 postgres 24u REG8,7 8192 0 14948153 /path/to/pgsql/base/35355/35460 (deleted) postgres 5025 postgres 35u REG8,7 286720 0 14947766 /path/to/pgsql/base/35355/5979918 (deleted) postgres 5025 postgres 63u REG8,7 1073741824 0 14947390 /path/to/pgsql/base/35355/5979501.1 (deleted) postgres 5025 postgres 73u REG8,7 319488 0 14947627 /path/to/pgsql/base/35355/5979758 (deleted) postgres 5025 postgres 79u REG8,7 1073741824 0 14947428 /path/to/pgsql/base/35355/5979507 (deleted) postgres 5025 postgres 84u REG8,7 1073741824 0 14947317 /path/to/pgsql/base/35355/5979501 (deleted) postgres 5025 postgres 113u REG8,7 8192 0 14948150 /path/to/pgsql/base/35355/35398 (deleted) postgres 5025 postgres 131u REG8,72326528 0 14943967 /path/to/pgsql/base/35355/5979727 (deleted) postgres 5025 postgres 141u REG8,7 16384 0 14947008 /path/to/pgsql/base/35355/5979746 (deleted) postgres 5025 postgres 142u REG8,7 28991488 0 14944726 /path/to/pgsql/base/35355/5979728 (deleted) postgres 5025 postgres 146u REG8,7 1073741824 0 14947442 /path/to/pgsql/base/35355/5979508 (deleted) postgres 5025 postgres 150u REG8,7 82337792 0 14947615 /path/to/pgsql/base/35355/5979710 (deleted) postgres 5025 postgres 169u REG8,7 37855232 0 14944962 /path/to/pgsql/base/35355/5979729 (deleted) postgres 5025 postgres 178u REG8,7 8192 0 14948156 /path/to/pgsql/base/35355/35723 (deleted) postgres 5025 postgres 179u REG8,7 1073741824 0 14947429 /path/to/pgsql/base/35355/5979509 (deleted) postgres 5025 postgres 181u REG8,7 450560 0 14947764 /path/to/pgsql/base/35355/5979914 (deleted) postgres 5025 postgres 185u REG8,7 95968 0 14946595 /path/to/pgsql/base/35355/5979508.1 (deleted) postgres 5025 postgres 200u REG8,7 224239616 0 14947343 /path/to/pgsql/base/35355/5979507.1 (deleted) postgres 5025 postgres 204u REG8,7 74473472 0 14947620 /path/to/pgsql/base/35355/5979713 (deleted) postgres 5025 postgres 208u REG8,7 106536960 0 14947623 /path/to/pgsql/base/35355/5979719 (deleted) postgres 5025 postgres 210u REG8,7 105611264 0 14947619 /path/to/pgsql/base/35355/5979712 (deleted) postgres 5025 postgres 212u REG8,7 446078976 0 14947611 /path/to/pgsql/base/35355/5979704 (deleted) postgres 5025 postgres 216u REG8,7 75988992 0 14947469 /path/to/pgsql/base/35355/5979509.1 (deleted) postgres 5025 postgres 222u REG8,7 513097728 0 14947263 /path/to/pgsql/base/35355/5979501.2 (deleted) postgres 5025 postgres 239u REG8,7 119832576 0 14947621 /path/to/pgsql/base/35355/5979716 (deleted) -- Vincent de Phily -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deleted files still open long after droping a database
On Monday 17 February 2014 18:46:49 Vincent de Phily wrote: Hi List, I have droped a database a few hours ago to reclaim some badly-needed space, but that disk space has not been freed yet. Investigating further, there are lots of deleted but open files that seem to correspond to the droped db, all open by some postgres process. A lot of process were db connections to databases other than the droped one (I'm not using connection pooling). The other process is the autovacuum launcher. I have reset the various connection processes, but I hesitate to kill the autovacuum process. I'm not sure wether that's relevant, but I moved the database to another server using slony1-2.2.2 before droping it on the old server. There were already no more slon processes running when I droped the db. 1) why does this happen at all (keeping files from a droped databse open, even by processes that never connected to that specific db) ? 2) Can the autovacuum launcher process be safely killed (there are no long- running vacuum queries) ? I solved that particular issue by disabling and reenabling autovacuum. So I finally got my disk space back, but I'm still interested in an answer to those questions. 3) Is there a generally cleaner way to do all this ? -- Vincent de Phily -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deleted files still open long after droping a database
Vincent de Phily vincent.deph...@mobile-devices.fr writes: I have droped a database a few hours ago to reclaim some badly-needed space, but that disk space has not been freed yet. Investigating further, there are lots of deleted but open files that seem to correspond to the droped db, all open by some postgres process. I believe this is taken care of in recent PG releases. 9.1.2 is, um, not recent. 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] Will modifications to unlogged tables also be flused to disk?
On Sat, Feb 15, 2014 at 3:03 AM, Clemens Eisserer linuxhi...@gmail.comwrote: Hi, I would like to use postgresql's unlogged tables on an embedded system to avoid frequent writes to flash memory. While documentation clearly states that unlogged tables don't have to go through the WAL, That's not really true. There are no per-row WAL records. There is still a per-transaction WAL record, the commit record. If you only care about the timing of the WAL and not the volume, changing to unlogged will not make a difference. (These commit-only records are automatically dealt with asynchronously, even if synchronous-commit is on.) Cheers, Jeff
Re: [GENERAL] CREATE EXTENSION does not seem to work in 9.2
Hi, On Wed, 2014-01-22 at 11:07 -0800, Susan Cassidy wrote: It wasn't installed in the yum package that we used to install postgres 9.2. This has nothing to do with the RPMs -- PL/pgSQL is installed by defaults since 9.0. As Adrian wrote, you will need to omit the file path there (still, you don't need it with 9.2) Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
[GENERAL] Database compare tools
Hello all, I was wondering if anyone of you know any database compare tools. I've been using PgComparer, and it has been working great until we upgraded Postgres from 9.0 to 9.3, and PgComparer stops to work. Any help is appreciated. Thanks! -Laurent
[GENERAL] Re: [ADMIN] could not create lock file postmaster.pid: No such file or directory, but file does exist
You don't give a lot of information, but try sudo rm postmaster.pid or sudo -u postgres rm postmaster.pid if you are sure that postgres is not running. Cheers, Cliff On Tue, Feb 18, 2014 at 12:07 AM, Rob Goethals / SNP rob.goeth...@snp.nlwrote: Hi, This is my first post to this list, so I hope I am posting it to the correct lists. But I am really stuck and getting pretty desperate at the moment. This weekend my database crashed while importing some Openstreetmapdata and I can't get it back to work again. It happened before and normally I would reset the WAL-dir with the pg_resetxlog command. I would loose some data but that would be all. This time it is somehow different because he doesn't recognize any of the important files anymore. For example when I try to start Postgresql again with the command: /usr/lib/postgresql/9.1/bin/pg_ctl -D OSM/ start I get the following error: FATAL: could not create lock file postmaster.pid: No such file or directory But when I do a ls -l on the directory I can see the file exists. drwx-- 0 postgres postgres 0 Jan 24 10:07 backup drwx-- 0 postgres postgres 0 Feb 14 11:10 base drwx-- 0 postgres postgres 0 Feb 17 09:46 global drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_clog -rwxr-xr-x 0 postgres postgres 4476 Oct 11 10:49 pg_hba.conf -rwxr-xr-x 0 postgres postgres 1636 Oct 11 10:49 pg_ident.conf drwx-- 0 postgres postgres 0 Feb 17 11:29 pg_log drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_multixact drwx-- 0 postgres postgres 0 Feb 17 08:58 pg_notify drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_serial drwx-- 0 postgres postgres 0 Feb 12 09:58 pg_stat_tmp drwx-- 0 postgres postgres 0 Feb 14 09:01 pg_subtrans drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_tblspc drwx-- 0 postgres postgres 0 Oct 11 10:49 pg_twophase -rwxr-xr-x 0 postgres postgres 4 Oct 11 10:49 PG_VERSION drwx-- 0 postgres postgres 0 Feb 14 13:37 pg_xlog -rwxr-xr-x 0 postgres postgres 19168 Oct 11 11:41 postgresql.conf -rwxr-xr-x 0 postgres postgres 121 Feb 17 08:57 postmaster.opts -rwxr-xr-x 0 postgres postgres88 Feb 17 08:58 postmaster.pid I cannot perform any action on the postmaster.pid file. I tried cp, mv and rm, but nothing works. Is there anything I can do to make the system recognize this file again? And get my database up and running? Or is all hopelessly lost? I have Postgresql 9.1 installed on Ubuntu 12.04. Kind regards, Rob.
Re: [GENERAL] File system level backup of shut down standby does not work?
On Mon, Feb 17, 2014 at 7:02 PM, Jürgen Fuchsberger juergen.fuchsber...@uni-graz.at wrote: Hi all, I have a master-slave configuration running the master with WAL archiving enabled and the slave in recovery mode reading back the WAL files from the master (Log-shipping standby as described in http://www.postgresql.org/docs/9.1/static/warm-standby.html) I take frequent backups of the standby server: 1) Stop standby server (fast shutdown). 2) Rsync to another fileserver 3) Start standby server. I just tried to recover one of these backups which *failed* with the following errors: 2014-02-17 14:27:28 CET LOG: incomplete startup packet 2014-02-17 14:27:28 CET LOG: database system was shut down in recovery at 2013-12-25 18:00:03 CET 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30061 (log file 483, segment 97): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid primary checkpoint record 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30060 (log file 483, segment 96): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid secondary checkpoint record 2014-02-17 14:27:28 CET PANIC: could not locate a valid checkpoint record 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:32 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET LOG: startup process (PID 26186) was terminated by signal 6: Aborted 2014-02-17 14:27:33 CET LOG: aborting startup due to startup process failure So it seems the server is missing some WAL files which are not in the backup? Or is it simply not possible to take a backup of a standby server in recovery? From version 9.2, you can take backups from standby also using pg_basebackup utility. Is the WAL file is present in archive folder? if yes, Did you provided the archive command in recovery.conf file? I am not sure what happened? During fast shutdown of standby it should create a restart point for further replay of WAL. Can you please enable log_checkpoints guc and check whether any restart point is getting created or not during fast shutdown. Regards, Hari Babu Fujitsu Australia
Re: [GENERAL] avoiding file system caching of a table
On 02/16/2014 10:33 PM, Gabriel Sánchez Martínez wrote: Is there a way of asking PostgreSQL to read the files of a table directly off the disk, asking the OS not to use the file cache? I am running PostgreSQL 9.1 on Ubuntu Server 64-bit. The server in question has the maximum amount of RAM it supports, but the database has grown much larger. Most of the time it doesn't matter, because only specific tables or parts of indexed tables are queried, and all of that fits in the file cache. But we have a new requirement of queries to a table several times larger than the total RAM, and the database has slowed down considerably for the other queries. I am assuming that with every query to the large table, the OS caches the files containing the table's data, and since the table is larger than total RAM, all the old caches are cleared. The caches that were useful for other smaller tables are lost, and the new caches of the large table are useless because on the next query caching will start again from the first files of the table. Please point out if there is a problem with this assumption. Note that I am refering to OS file caching and not PostgreSQL caching. Is there a way around this? I have read that there is a way of asking the OS not to cache a file when the file is opened. Is there a way of telling PostgreSQL to use this option when reading files that belong a specific table? What about putting the table on a tablespace that is on a different device partition with the sync mount option? Would that help? I have read forum postings saying that the sync option affects writes, and will not prevent reads from caching. At some forum posting I came across nocache, a utility for linux. It is used by typing nocache command in a shell. But I can't do that with a postgres process when a connection opens because postgres is the one opening the process. Does someone know a work-around, or a different solution to the problem? Shouldn't PostgreSQL be smart about this and based on the statistics collected for a table and on the query plan know the harm that will be done if all of a very large table's pages are read and flush the cache? All suggestions will be appreciated. Thanks, Gabriel -- 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] Deleted files still open long after droping a database
Vincent de Phily vincent.deph...@mobile-devices.fr wrote: I have droped a database a few hours ago to reclaim some badly-needed space, but that disk space has not been freed yet. Investigating further, there are lots of deleted but open files that seem to correspond to the droped db, all open by some postgres process. A lot of process were db connections to databases other than the droped one (I'm not using connection pooling). The other process is the autovacuum launcher. I have reset the various connection processes, but I hesitate to kill the autovacuum process. I'm not sure wether that's relevant, but I moved the database to another server using slony1-2.2.2 before droping it on the old server. There were already no more slon processes running when I droped the db. 1) why does this happen at all (keeping files from a droped databse open, even by processes that never connected to that specific db) ? 2) Can the autovacuum launcher process be safely killed (there are no long- running vacuum queries) ? 3) Is there a generally cleaner way to do all this ? I suspect that the other backends needed to flush a page from cache to free space for a page they wanted to use, and the file was not closed in case the same backend needed to flush other pages from the same file. Perhaps we should arrange for a DROP DATABASE command to somehow signal all backends to close files from that backend? Or they should periodically check? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] avoiding file system caching of a table
On Mon, Feb 17, 2014 at 5:09 PM, Gabriel E. Sánchez Martínez gabrielesanc...@gmail.com wrote: On 02/16/2014 10:33 PM, Gabriel Sánchez Martínez wrote: Is there a way of asking PostgreSQL to read the files of a table directly off the disk, asking the OS not to use the file cache? I am running PostgreSQL 9.1 on Ubuntu Server 64-bit. The server in question has the maximum amount of RAM it supports, but the database has grown much larger. Most of the time it doesn't matter, because only specific tables or parts of indexed tables are queried, and all of that fits in the file cache. But we have a new requirement of queries to a table several times larger than the total RAM, and the database has slowed down considerably for the other queries. I am assuming that with every query to the large table, the OS caches the files containing the table's data, and since the table is larger than total RAM, all the old caches are cleared. The caches that were useful for other smaller tables are lost, and the new caches of the large table are useless because on the next query caching will start again from the first files of the table. Please point out if there is a problem with this assumption. Note that I am refering to OS file caching and not PostgreSQL caching. If you told postgresql to tell the kernel not to cache the data it reads, how would this help? The data you want in cache would no longer be pushed out of the cache, but that is because it would no longer be there in the first place. You would have to make this instruction to the kernel be selective. It would only tell it not to cache when it is doing a very large query. It might be theoretically possible to do this, but it it would probably cause more harm than good to most people most of the time. Is there a way around this? I have read that there is a way of asking the OS not to cache a file when the file is opened. Is there a way of telling PostgreSQL to use this option when reading files that belong a specific table? What about putting the table on a tablespace that is on a different device partition with the sync mount option? Would that help? I have read forum postings saying that the sync option affects writes, and will not prevent reads from caching. At some forum posting I came across nocache, a utility for linux. It is used by typing nocache command in a shell. But I can't do that with a postgres process when a connection opens because postgres is the one opening the process. You would have to start the entire service with that utility, then. Which again would defeat the purpose. Does someone know a work-around, or a different solution to the problem? Shouldn't PostgreSQL be smart about this and based on the statistics collected for a table and on the query plan know the harm that will be done if all of a very large table's pages are read and flush the cache? PostgreSQL does know this. It has a special ring buffer access strategy that it uses to prevent a large sequential scan from pushing all of the other data out of its shared_buffers. It sounds like it is the kernel which is failing to employ similar logic on the file cache which the *kernel* manages. The kernel does also have some logic to prevent this, but it may or may not be very effective in your case (you haven't us what version of the kernel you are using). In fact one effort of the kernel to fix this problem for cases like yours ended up making it worse for other conditions, i.e. when the file being read sequentially was less than available RAM but greater than 1/2 available RAM. You could try increasing shared_buffers (you haven't told us what it is set to now) until it takes up a big chunk of RAM, so that PostgreSQL manages more of the cache and the kernel manages less of it. Setting it like that has been reported to cause problems on write-heavy work loads, but I haven't heard of problems on read-mostly workloads. Cheers, Jeff
Re: [GENERAL] Deleted files still open long after droping a database
Kevin Grittner kgri...@ymail.com writes: Perhaps we should arrange for a DROP DATABASE command to somehow signal all backends to close files from that backend? See commit ff3f9c8de, which was back-patched into 9.1.x as of 9.1.7. Unfortunately, the complainant is running 9.1.2. 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] avoiding file system caching of a table
Jeff Janes jeff.ja...@gmail.com writes: If you told postgresql to tell the kernel not to cache the data it reads, how would this help? I seem to recall also that O_DIRECT disables optimizations we definitely want, like read-ahead. So it could come out a loser independently of any what's-in-cache concerns. 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] avoiding file system caching of a table
On 02/17/2014 08:45 PM, Jeff Janes wrote: On Mon, Feb 17, 2014 at 5:09 PM, Gabriel E. Sánchez Martínez gabrielesanc...@gmail.com mailto:gabrielesanc...@gmail.com wrote: On 02/16/2014 10:33 PM, Gabriel Sánchez Martínez wrote: Is there a way of asking PostgreSQL to read the files of a table directly off the disk, asking the OS not to use the file cache? I am running PostgreSQL 9.1 on Ubuntu Server 64-bit. The server in question has the maximum amount of RAM it supports, but the database has grown much larger. Most of the time it doesn't matter, because only specific tables or parts of indexed tables are queried, and all of that fits in the file cache. But we have a new requirement of queries to a table several times larger than the total RAM, and the database has slowed down considerably for the other queries. I am assuming that with every query to the large table, the OS caches the files containing the table's data, and since the table is larger than total RAM, all the old caches are cleared. The caches that were useful for other smaller tables are lost, and the new caches of the large table are useless because on the next query caching will start again from the first files of the table. Please point out if there is a problem with this assumption. Note that I am refering to OS file caching and not PostgreSQL caching. If you told postgresql to tell the kernel not to cache the data it reads, how would this help? The data you want in cache would no longer be pushed out of the cache, but that is because it would no longer be there in the first place. You would have to make this instruction to the kernel be selective. It would only tell it not to cache when it is doing a very large query. It might be theoretically possible to do this, but it it would probably cause more harm than good to most people most of the time. I read that the suggestion not to cache a file when reading it is given by programs at the time the file is opened. That prompted me to think that there might be a way of telling PostgreSQL to apply that to the pages of a specific relation. I did not mean to suggest it should be a process-wide or database-wide setting. Is there a way around this? I have read that there is a way of asking the OS not to cache a file when the file is opened. Is there a way of telling PostgreSQL to use this option when reading files that belong a specific table? What about putting the table on a tablespace that is on a different device partition with the sync mount option? Would that help? I have read forum postings saying that the sync option affects writes, and will not prevent reads from caching. At some forum posting I came across nocache, a utility for linux. It is used by typing nocache command in a shell. But I can't do that with a postgres process when a connection opens because postgres is the one opening the process. You would have to start the entire service with that utility, then. Which again would defeat the purpose. Since a process is launched every time a session opens, e.g. a query window in pgAdmin, I thought it would be possible to do it per session rather than for the whole service. Either way, I agree this wouldn't solve the problem. Does someone know a work-around, or a different solution to the problem? Shouldn't PostgreSQL be smart about this and based on the statistics collected for a table and on the query plan know the harm that will be done if all of a very large table's pages are read and flush the cache? PostgreSQL does know this. It has a special ring buffer access strategy that it uses to prevent a large sequential scan from pushing all of the other data out of its shared_buffers. It sounds like it is the kernel which is failing to employ similar logic on the file cache which the *kernel* manages. I have no idea how the kernel manages its cache, but I think that since individual pages that store the data of the table are small, the kernel has no way of knowing that a process will read a very large number of relatively small files that collectively will cause harm. Maybe if it were a single file large than total physical RAM it would act differently. But I am just speculating. The kernel does also have some logic to prevent this, but it may or may not be very effective in your case (you haven't us what version of the kernel you are using). Thanks for asking. 3.8.0-35-generic. I'm curious. What does it do? Or do you know where I can read about this (just out of curiosity). In fact one effort of the kernel to fix this problem for cases like yours ended up making it worse for other conditions, i.e. when the file being read sequentially was
Re: [GENERAL] avoiding file system caching of a table
On Mon, Feb 17, 2014 at 2:33 PM, Gabriel Sánchez Martínez gabrielesanc...@gmail.com wrote: Is there a way of asking PostgreSQL to read the files of a table directly off the disk, asking the OS not to use the file cache? I am running PostgreSQL 9.1 on Ubuntu Server 64-bit. The server in question has the maximum amount of RAM it supports, but the database has grown much larger. Most of the time it doesn't matter, because only specific tables or parts of indexed tables are queried, and all of that fits in the file cache. But we have a new requirement of queries to a table several times larger than the total RAM, and the database has slowed down considerably for the other queries. I am assuming that with every query to the large table, the OS caches the files containing the table's data, and since the table is larger than total RAM, all the old caches are cleared. The caches that were useful for other smaller tables are lost, and the new caches of the large table are useless because on the next query caching will start again from the first files of the table. Please point out if there is a problem with this assumption. Note that I am refering to OS file caching and not PostgreSQL caching. Is there a way around this? I have read that there is a way of asking the OS not to cache a file when the file is opened. Is there a way of telling PostgreSQL to use this option when reading files that belong a specific table? What about putting the table on a tablespace that is on a different device partition with the sync mount option? Would that help? All suggestions will be appreciated. Can you please check the following extension, it may be useful to you. https://github.com/klando/pgfincore Regards, Hari Babu Fujitsu Australia
[GENERAL] Postgres service (Windows) running as local system
Hi Postgres on windows - Is it ok to activate Postgres service, while logged on as local system Thanks, Yuval Yuval Sofer BMC Software CTMD Business Unit DBA Team 972-52-4286-282 yuval_so...@bmc.commailto:yuval_so...@bmc.com
