Re: [GENERAL] PostgreSQL reclaiming table space
On Montag, 28. Mai 2012, Anthony Bull wrote: Hi, This did not work either - postgres still reports 25GB being used by the database. Did you run analyze? Did the disk usage reported by the OS shrink? Regards, Jens -- 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] autovacuum and transaction id wraparound
On Wednesday 07 March 2012 21:13:26 pawel_kukawski wrote: Hi, Do you know any real reason why the autovacuum may fail to clear old XIDs? If it's unable to keep up. Or may be, if there're very long running idle in transactions. Is this highly probable ? postmaster will shutdown to prevent wraparound, if there are fewer than1 million transactions left until wraparound. Rgds, Jens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why warm-standby doesn't work using file-based log shipping method?
On Dienstag, 21. Februar 2012, sunpeng wrote: 00010009 obviously there is no 00020007. The standby is on timeline 2, but the WAL Files are on timeline 1. I guess, you should make a new base backup. Regards, Jens -- 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 list installed pl languages
On Thursday 10 November 2011 05:58:01 Dennis Ryan wrote: Is there a way to list the installed pl languages for a database and/or server cluster? they are registered in the pg_language system catalog. Rgds, Jens -- 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] PG 9.1 on Debian Squeeze
On Samstag, 8. Oktober 2011, Raymond O'Donnell wrote: Hi, rod@simecom:~$ psql -U postgres -h localhost Can't exec dpkg-architecture: No such file or directory at dpkg-architecture is missing. This file is provided by the package dpkg-dev. Regards, Jens -- 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] Newbiee Pls help.....
On Freitag, 29. Juli 2011, woow wrote: I have following function expectation is I want to return the items in array SELECT INTO rolelist role_id FROM roles WHERE role_type = $1; RETURN rolelist; Hi, you've to use array_agg: SELECT INTO rolelist array_agg(role_id) FROM roles WHERE role_type = $1; Regards, Jens -- 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] undead index
On Thursday 05 May 2011 16:46:05 Cédric Villemain wrote: I understood that you droped an index and when you dump/restore you get your index again. Yes, that's it, after the pg_upgrade error, i removed the target data directory, and initialzed a new target DB. After pg_dumpall|pg_dump i got an index that is not visible in the source DB. It had either been deleted weeks ago or disappeared from the system catalog for any other reason. I can't find this index in a new pg_dumpall output from the source DB. Regards, Jens -- 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] undead index
On Wednesday 04 May 2011 17:32:50 Tom Lane wrote: Hmm, is this an autogenerated index? I don't think so. And to confirm, that i really deleted the new cluster between the pg_upgrade run and the dump|restore i did it again and was able to revive this index again: foo=# \d+ foo.bar_idx Index foo.bar_idx Column | Type | Definition | Storage | Description --+---++--+- ulq_guid | character varying(24) | ulq_guid | extended | btree, for table foo.foo But it's again not in the pg_dumpall output, using the same binary like for the dump|restore. Regards, Jens -- 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] undead index
On Friday 06 May 2011 17:18:29 Tom Lane wrote: Hi Tom, Possibly if you showed us the actual (not obfuscated) table declaration, associated constraint declarations, and resulting index definition, things would be clearer. Thanks Tom, yes, the index is named Indexes: concurrently btree (ulq_guid) In the 8.4 cluster and 9.0.4's pg_dumpall dumps it as CREATE INDEX concurrently ON foo USING btree (ulq_guid); That's it. But shouldn't pg_upgrade be able to handle this? Regards, Jens -- 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] undead index
On Friday 06 May 2011 18:08:58 Tom Lane wrote: There doesn't appear to be any fix for this that doesn't require a time machine and/or a lot more effort than it's worth. Isn't it possible to backport the fix for pg_get_indexdef() to 8.* ? Suggest you rename the index in the 8.4 database. That's already done. Regards, Jens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] undead index
Hi, pg_upgrade brakes with the following error: pg_upgrade 8.4.5 to 9.0.4: Restoring user relation files /data1/postgres/pgsql/foo/data_8.4/base/11564/2613 ^M /data1/postgres/pgsql/foo/data_8.4/base/11564/2683 Could not find foo.bar_idx in old cluster This index was deleted several weeks ago. server_version | 8.4.5 foo=# select * from pg_class where relname = 'bar_idx'; (No rows) after pg_dumpall|psql from 8.4 to 9.0 the undead index revived on the target DB: server_version | 9.0.4 foo=# select * from pg_class where relname = 'bar_idx'; -[ RECORD 1 ]---+ relname | bar_idx relnamespace| 16409 Besides the question how this could happen, Is there another way to correct this without using dump|restore? I'd like to get pg_upgrade working. Regards, Jens -- 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] pg_reorg
On Tue, Apr 19, 2011 at 04:02:01AM +0530, Vibhor Kumar wrote: IIRC vacuum full mode rewrites the indexes as well. Till 8.4 no. From 9.0 onwards yes. However VACUUM FULL still locks the table. Don't be confused with the vacuum full term. This has nothing to do with the postgresql vacuum full command. Both pg_reorg's vacuum full and cluster mode do the pretty same thing. They rewrite the table and all their indexes. They use triggers to update the new table during the reorganisation. The only difference is that cluster does an additional order by. Both of them lock the original table at the end of the reorganisation just for the switch. If the lock is not granted within -T seconds, the backends holding locks are canceled. If you run out of diskspace, it's possible to reorg table by table. And yes, pg_reorg does only work with tables with a primary key. This will change in future releases, IIRC regards, Jens -- 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] pg_reorg
On Montag, 18. April 2011, Scott Mead wrote: I've seen it, but catalog hacks always make me nervous. Anybody else have good / bad experience to share? Hi, yes, we used it to reduce massive bloat after a database split and were very satisfied. IIRC vacuum full mode rewrites the indexes as well. There's a proposal about an alternative to pg_reorg on depesz' Blog: http://www.depesz.com/index.php/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/ regards, Jens -- 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] Autovacuum firing up during my manual vacuum on same table
On 3. April 2011, Joshua D. Drake wrote: On Sat, 2 Apr 2011 19:26:56 +0200, Henry C. he...@cityweb.co.za wrote: On Sat, April 2, 2011 14:17, Jens Wilke wrote: Nevertheless since at least 8.4 IMO there's no need to bother with manual vacuum any more. Uhh, this is entirely untrue. There are plenty of cases where 8.4 autovacuum can't cut it. Which cases? Isn't it more like something else went suboptimal when starting to think about manual vacuum? May be i better had written that since 8.4 there's the opportunity not to bother with manual vacuum any more. Regards, Jens -- 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] Autovacuum firing up during my manual vacuum on same table
On Samstag, 2. April 2011, Henry C. wrote: I just noticed something odd: I'm busy with a manual vacuum on a table and an autovacuum keeps firing up as well. Usually a manual vacuum cancels a running autovacuum task. You should find a notice about the cancelation in th logfile. current_query | vacuum analyze age | 11:46:57.245568 Where is the age column from? It's not in pg_stat_activity. Is one of the two processes waiting=t in pg_stat_activity? Shure it's the same Table? Do you have one Table named page_citation_text in public and one in anoter sheme? I'm trying to vacuum the table as quickly as possible so a manual vacuum seems to be in order as my understanding (and experience) is that the autovac is a hell of a lot slower to mitigate impact on general performance. Tune the autovacuum settings, especially the scale factors of the tables in question and the cost limit. Is autovacuum_vacuum_cost_limit != -1 ? This could be one reason, why manual vacuum is faster. Nevertheless since at least 8.4 IMO there's no need to bother with manual vacuum any more. Regards, Jens -- 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] Autovacuum firing up during my manual vacuum on same table
On Samstag, 2. April 2011, Henry C. wrote: Sadly, in my case, the db is so busy that autovac processes run for weeks and never catch up Increase the cost_limit and the HW and/or check your application, if it's possible to reduce the amount of deletes and/or updates. Regards, Jens -- 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] Hot Standby - ERROR: canceling statement due to conflict with recovery
On Sonntag, 27. Februar 2011, Sean Laurent wrote: Unfortunately, most queries against the hot standby fail. Worse yet, pg_dump fails: ... I'm not entirely certain I understand why I'm seeing this. Nor do I understand how to fix or work around this. Any advice or suggestions would be greatly appreciated. Long running queries on the standby are a bit tricky, because they might need to see row versions that are already removed on the master. It's well documented: http://www.postgresql.org/docs/9.0/static/hot-standby.html HTH, Jens -- 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] Hot Standby - ERROR: canceling statement due to conflict with recovery
On Sonntag, 27. Februar 2011, Sean Laurent wrote: In particular, I was really hoping to run database dumps against the standby, not the master. One solution is to begin idle transactions on the master by using e.g. dblink from the standby to the master before you start pg_dump on the standby and end them after pg_dump (or whatever) is finished. Jens -- 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] Mysql to Postgresql
On Tuesday 22 February 2011 10:21:01 Adarsh Sharma wrote: Hi, Today I need to back up a mysql database and restore in Postgresql database but I don't know how to achieve this accurately. Can anyone kindly describe me the way to do this. Have a look here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL HTH, Jens -- 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] No Password Access
On Dienstag, 8. Februar 2011, Carlos Mennens wrote: Why am I not prompted for a password when I connect from my laptop to the server? check your pg_hba.conf -- 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] Problem while pg_xlog directory
Am Montag 13 Dezember 2010, um 14:47:03 schrieb Adarsh Sharma: mv pg_xlog pg_xlog_old But me server fail to start and there is empty log in startup.log. I also Hi,, you have to move the contents from the old to the new pg_xlog directory as well. HTH, Jens -- 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] Warm Standby log filling up with FATAL: the database system is starting up entries
On Mittwoch, 1. Dezember 2010, Greg Swisher wrote: FATAL: the database system is starting up This is what you get on 8.4, when you try to connect to a standby. Rgds, Jens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: cache lookup failed for type 14237017
On Dienstag, 21. September 2010, Aleksey Tsalolikhin wrote: Yesterday, I had twelve thousand cache lookup failed for type N messages, like this: What does type 14237017 mean? pg_type oid What cache are we talking about? Did you alter a type before? There's a bug in postgres, that prevents the plan cache to be invalidated on type changes. We ran into this bug as well on 8.4.4. HTH, Jens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: cache lookup failed for type 14237017
On Dienstag, 21. September 2010, Aleksey Tsalolikhin wrote: Hi Aleksey, So PostgreSQL was trying to lookup a row in a system table and did not find it in a cache. yes, select * from pg_type where oid =14237017 Did you alter a type before? No. I don't even know how to alter a type. Sorry, what i mean is drop and recreate a type. Is there a bug id or a URL for the bug, please? I'd like to study No idea, sorry Jens -- 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] Second request: Problem with dumps
Am Mittwoch 11 August 2010 16:20:27 schrieb Bill Christensen: Hi, Export error: Failed to execute pg_dump (given path in your conf/config.inc.php : Is pg_dump executable? Did you try to do a manual dump w/o phpadmin? Regards, Jens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question on warm standby log shipping
Hi, I hacked up some scripts to do warm standby log shipping with postgresql version 8.2.7. Everything is fine, but I have one remaining curiosity. The restore script looks like: --snipp-- from=$1; to=$2; while true; do if test -f $ARCHIVEDIR/$from; then src=$ARCHIVEDIR/$from; mv $src $to; exit 0; fi # startup triggered if test -f $triggerfile; then exit 0; fi sleep 2; done --snipp-- However this way its not working, since when the database starts up it requests log files again that were already feeded. Here is a log of an unsuccessful startup: --snipp-- 2008-05-05 17:21:42 CEST LOG: restored log file 0001000B001C from archive 2008-05-05 17:22:42 CEST LOG: restored log file 0001000B001D from archive 2008-05-05 17:23:40 CEST LOG: restored log file 0001000B001E from archive 2008-05-05 17:24:40 CEST LOG: restored log file 0001000B001F from archive 2008-05-05 17:25:04 CEST LOG: could not open file pg_xlog/0001000B0020 (log file 11, segment 32): No such file or directory 2008-05-05 17:25:04 CEST LOG: redo done at B/1F00CB6C 2008-05-05 17:25:04 CEST PANIC: could not open file pg_xlog/0001000B001F (log file 11, segment 31): No such file or directory 2008-05-05 17:25:04 CEST LOG: startup process (PID 4873) was terminated by signal 6 2008-05-05 17:25:04 CEST LOG: aborting startup due to startup process failure 2008-05-05 17:30:02 CEST LOG: could not load root certificate file root.crt: no SSL error reported 2008-05-05 17:30:02 CEST DETAIL: Will not verify client certificates. 2008-05-05 17:30:02 CEST LOG: database system was interrupted while in recovery at log time 2008-05-05 17:22:14 CEST 2008-05-05 17:30:02 CEST HINT: If this has occurred more than once some data may be corrupted and you may need to choose an earlier recovery targe 2008-05-05 17:30:02 CEST LOG: starting archive recovery 2008-05-05 17:30:02 CEST LOG: restore_command = /var/lib/postgresql/8.2-wal-archive/pg-standby/restore.sh %f %p 2008-05-05 17:30:02 CEST LOG: could not open file pg_xlog/0001000B001D (log file 11, segment 29): No such file or directory 2008-05-05 17:30:02 CEST LOG: invalid primary checkpoint record 2008-05-05 17:30:02 CEST LOG: could not open file pg_xlog/0001000B001A (log file 11, segment 26): No such file or directory 2008-05-05 17:30:02 CEST LOG: invalid secondary checkpoint record 2008-05-05 17:30:02 CEST PANIC: could not locate a valid checkpoint record 2008-05-05 17:30:02 CEST LOG: startup process (PID 825) was terminated by signal 6 2008-05-05 17:30:02 CEST LOG: aborting startup due to startup process failure --snipp-- A successful startup looks like: --snipp-- 2008-05-13 15:55:47 CEST LOG: restored log file 000100270046 from archive 2008-05-13 15:56:47 CEST LOG: restored log file 000100270047 from archive 2008-05-13 15:57:47 CEST LOG: restored log file 000100270048 from archive 2008-05-13 15:59:21 CEST LOG: could not open file pg_xlog/000100270049 (log file 39, segment 73): No such file or directory 2008-05-13 15:59:21 CEST LOG: redo done at 27/48016BC0 2008-05-13 15:59:22 CEST LOG: restored log file 000100270048 from archive 2008-05-13 15:59:22 CEST LOG: selected new timeline ID: 2 2008-05-13 15:59:22 CEST LOG: archive recovery complete 2008-05-13 15:59:22 CEST LOG: database system is ready --snipp-- As we can see logfile ..48 is requested again. So the question is: Which log files can I safely delete from my archive? OTOH, is this a bug? I think postgresql should not request something again that was already feeded. Best, Jens -- Everything superfluous is wrong! // Jens Wilke - headissue GmbH - Munich - Germany \// http://www.headissue.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
