Re: [ADMIN] Massive insert created 975 WAL segments.. what?
On 12 September 2012 01:10, Wells Oliver wellsoli...@gmail.com wrote: We were doing a test and inserted 1.5 million rows. In doing so, postgres created 974 WAL segments of 16MB apiece. The relevant configuration from my master's postgresql.conf: wal_level = hot_standby archive_mode = on archive_command = 'test ! -f /mnt/postgresql-logs/%f cp %p /mnt/postgresql-logs/%f' max_wal_senders = 3 wal_keep_segments = 16 So, now I have 16GB of WAL segments, 974 of them. Will postgresql clean this up? Will it remove these files? Will it create more with big inserts? What is the best way to manage this? These are transaction log files, created by database writes. They protect you if you crash and also allow replication/backup. These will be rotated every checkpoint, which by default is 5 minutes. So they'll be long gone by now. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] pg_restore problem Found unexpected Block id
Hi , I am getting an error while restoring the Database Backup. I am postgre sql 8.0 with Windows XP O.S. while restoring the data i am getting pg_restore: [custom archiver] found unexpected block ID (0) when reading data -- expected 4034 Plz provide any solution. Thanks in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-restore-problem-Found-unexpected-Block-id-tp5723501.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Missing loadable libraries when running pg_upgrade 9.1 - 9.2
using Windows XP SP3 (32bit). When running pg_upgrade with the --check option I get the following error: Your installation references loadable libraries that are missing from the new installation. You can add these libraries to the new installation, or remove the functions using them from the old installation. A list of problem libraries is in the file: loadable_libraries.txt The content of loadable_libraries.txt is: Could not load library $libdir/targetinfo ERROR: could not access file $libdir/targetinfo: No such file or directory Could not load library $libdir/plugins/plugin_debugger ERROR: could not access file $libdir/plugins/plugin_debugger: No such file or directory And indeed there is no targetinfo.dll and no plugin_debugger.dll for 9.2 (there isn't even a plugins directory in lib) The relevant entries in the postgresql.conf for 9.1 are this: #shared_preload_libraries = '' # (change requires restart) #dynamic_library_path = '$libdir' #local_preload_libraries = '' So I'm a bit stumped on why pg_upgrade thinks that I'm using those libraries. Is there a way to find out which function (or extension?) is using those libraries in order to remove that dependency? The alternative would be to install those libraries, but the StackBuilder doesn't list them, and I didn't find anything in pgAdmin that would allow me to install them. They weren't installed with the installer and they are not part of the Windows ZIP distribution either. Any ideas? Thanks in advance Thomas -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Missing loadable libraries when running pg_upgrade 9.1 - 9.2
Thomas Kellerer wrote on 12.09.2012 11:09: Is there a way to find out which function (or extension?) is using those libraries in order to remove that dependency? What I ended up doing is to search through my pg_dump backups and I found the offending database. After removing the functions in question, everything went smoothly. Is there a better way to find those functions rather than doing a grep on the dump files? Thomas -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_restore problem Found unexpected Block id
ramana.pls ramana@gmail.com writes: I am getting an error while restoring the Database Backup. I am postgre sql 8.0 with Windows XP O.S. 8.0? You do know that's been out of support for several years now, right? while restoring the data i am getting pg_restore: [custom archiver] found unexpected block ID (0) when reading data -- expected 4034 This looks somewhat like a pg_restore bug we fixed awhile back. I'd suggest updating to a more modern Postgres version and seeing if it works any better. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_restore problem Found unexpected Block id
ramana.pls ramana@gmail.com wrote: I am getting an error while restoring the Database Backup. I am postgre sql 8.0 with Windows XP O.S. PostgreSQL version 8.0 has been out of support overall for years, and out of support for Windows for years before that. http://archives.postgresql.org/pgsql-announce/2008-01/msg5.php http://www.postgresql.org/support/versioning/ Once you get past the immediate problems, it is extremely important that you update to a supported version and stay up-to-date with minor (bug-fix) releases. Regarding the immediate problem, your report could use a lot more detail. What steps have you taken to get to where you are now? Exactly what version of PostgreSQL is this (8.0.what?)? http://wiki.postgresql.org/wiki/Guide_to_reporting_problems Without knowing more, it's hard to give much advice. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Missing loadable libraries when running pg_upgrade 9.1 - 9.2
On Wed, Sep 12, 2012 at 12:03:21PM +0200, Thomas Kellerer wrote: Thomas Kellerer wrote on 12.09.2012 11:09: Is there a way to find out which function (or extension?) is using those libraries in order to remove that dependency? What I ended up doing is to search through my pg_dump backups and I found the offending database. After removing the functions in question, everything went smoothly. Is there a better way to find those functions rather than doing a grep on the dump files? Well, pg_upgrade uses SQL queries to find those entries, so you could turn on logging and look at the SQL queries that look in the pg_proc table for such entries. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_upgrade with postgis
Hi - Thanks. As we installed using pg repos (yum) I don't think it will allow me to re-install postgis. Is it possible to specify a target cluster for a postgis install from repo or will it need to be installed from source? Thanks again. On Wed, Sep 12, 2012 at 1:17 AM, Bruce Momjian br...@momjian.us wrote: On Tue, Sep 11, 2012 at 04:15:31PM +0300, Ronit Allen wrote: HI - I am looking to upgrade our postgresql 9 to 9,2 using pg_upgrade. We have postgis installed on the current cluster (9). Both postgresql and postgis was installed using the pg repos. Will I need to re-install postgis after running pg_upgrade? You will need to have all plug-in stuff installed in the new cluster before running pg_upgrade. You should then be able to run pg_upgrade and all your postgis data will be migrated. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: [ADMIN] what is maximum allowed value of autovacuum_freeze_max_age
Radovan Jablonovsky wrote: In documentation http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html is this information about autovacuum_freeze_max_age: if autovacuum_freeze_max_age is set to its maximum allowed value of a little less than two billion. What is exact maximum allowed value of autovacuum_freeze_max_age? test=# select name, setting, min_val, max_val test-# from pg_settings where name = 'autovacuum_freeze_max_age'; name| setting | min_val | max_val ---+---+---+ autovacuum_freeze_max_age | 2 | 1 | 20 (1 row) Perhaps we should drop a little less than from the docs. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] pg_upgrade with postgis
On Thu, Sep 13, 2012 at 02:12:40AM +0300, Ronit Allen wrote: Hi - Thanks. As we installed using pg repos (yum) I don't think it will allow me to re-install postgis. Is it possible to specify a target cluster for a postgis install from repo or will it need to be installed from source? You just need to have a valid data directory and binaries --- you don't need source. --- Thanks again. On Wed, Sep 12, 2012 at 1:17 AM, Bruce Momjian br...@momjian.us wrote: On Tue, Sep 11, 2012 at 04:15:31PM +0300, Ronit Allen wrote: HI - I am looking to upgrade our postgresql 9 to 9,2 using pg_upgrade. We have postgis installed on the current cluster (9). Both postgresql and postgis was installed using the pg repos. Will I need to re-install postgis after running pg_upgrade? You will need to have all plug-in stuff installed in the new cluster before running pg_upgrade. You should then be able to run pg_upgrade and all your postgis data will be migrated. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin