I finally took the time to get my local PostgreSQL 7.4 series ebuilds back in sync with the portage tree. I've attached an ebuild for 7.4-r2 which builds off of 7.4-r1. My additions include: - pg_autovacuum init-script (highly recommended!) - USE=pg-vacuumdelay adds in the vacuum inter-page delay feature and configuration options which were discussed on the postgresql-hackers mailing list last month. This patch allows you to configure short pauses in the vacuum process which will reduce the IO rate of a vacuum command. If you're using pg_autovacuum, you probably want this patch. pg_autovacuum will issue vacuums right at inopportune moments on your database, precisely when the server doesn't need any more additional load. Configuring the vacuum page delay with this patch reduces the performance impact of vacuuming at a bad moment. See the postgresql-hackers mailing list archives for a lengthy discussion of this patch and why it's useful. (Note that the patch does not enable the delay by default; you must also turn the delay on. Read the patch file for directions.) - USE=pg-intdatetime enables the --enable-integer-datetimes configure option, which changes PG to use 64-bit integers for timestamp storage, instead of 64-bit doubles. I use this option, but most people probably don't. (int64 is less tested than the traditional double, but might be faster. Benchmark your own app and decide for yourself. initdb is required when changing this option.) Reproducible: Always Steps to Reproduce:
Created attachment 21758 [details] New ebuild The new ebuild, derived from 7.4-r1. Adds pg_autovacuum init script and optionally vacuum inter-page delay and integer-datetimes.
Created attachment 21760 [details] pg_autovacuum, for /etc/conf.d Tuning parameters for pg_autovacuum can be adjusted in this file.
Created attachment 21761 [details] pg_autovacuum - /etc/init.d Init-script for pg_autovacuum.
Created attachment 21762 [details] Vacuum inter-page delay patch The vacuum inter-page delay patch, discussed on pgsql-hackers.
Dang, I always forget that bugzille eats the filenames. The ebuild is postgresql-7.4-r2.ebuild, obviously. The others all belong in the files subdirectory: pg_autovacuum.conf-7.4 pg_autovacuum.init-7.4 postgresql-7.4-vacuum-delay.patch
great job :) I'll include them after resolving this problem. # /etc/init.d/pg_autovacuum start * Starting pg_autovacuum... [ !! ] This is log file: Dec 22 19:42:38 [pg_autovacuum] [2003-12-22 07:42:38 PM] Error: GUC variable stats_row_level must be enabled. Dec 22 19:42:38 [pg_autovacuum] [2003-12-22 07:42:38 PM] Please fix the problems and try again. The postgresql is emerged in USE="-doc +java +libg++ +nls +pam +perl -pg-hier -pg-intdatetime +pg-vacuumdelay -python +readline +ssl +tcltk +zlib" I'm not familiar with pg_autovaccum. What do you think?
As the error message and the pg_autovacuum docs say, you must have stats_row_level enabled in postgresql.conf for pg_autovacuum to work. GUC = Global User Configuration; in other words, settings in postgresql.conf and the "SET" command for per-connection adjustments. FYI, on pgsql-hackers, the announcement was made yesterday that 7.4.1 has been tagged & bundled for an expected release some time today. I'm unsure about the state of the vacuum-delay patch; previous discussion on that list left me confused as to whether it was accepted for 7.4.1. My "real job" needs the upgrade, so I should be on top of it this week.
OK. I just included them in postgresql-7.4.1.ebuild Thanks
[2003-12-25 01:33:32 PM] Failed connection to database template1 with error: fe_sendauth: no password supplied . [2003-12-25 01:33:32 PM] Failed connection to database template1 with error: fe_sendauth: no password supplied . [2003-12-25 01:33:32 PM] Error: Cannot connect to template1, exiting. How do I configure authentication for pg_autovacuum in a safe way ? What privileges does a user for pg_autovacuum require ? Right now, the authentication method used for unix-domain sockets and tcp/ip sockets is md5.
shadow, this is not a Gentoo problem. I suggest reading /usr/share/doc/postgresql-7.4.1/contrib/README.pg_autovacuum . You probably need a '.pgpass' file. Search the PostgreSQL docs and/or archive for more info on libpq and .pgpass. For installations using local trust authentication, pg_autovacuum works out of the box.
