Admin Pack for PostgreSQL and pgAdmin

PgAdmin3 admittedly is a nice Postgres GUI. It's got features from simple query tool to server monitoring. However, if you have your "Guru Hints" on, you'll see the following message in a pop-up window:

Server instrumentation
The server lacks instrumentation functions.

pgAdmin III uses some support functions that are not available by default in all PostgreSQL versions. These enable some tasks that make life easier when dealing with log files and configuration files.

When you install PostgreSQL 8.0 or up using the Windows installer, you just need to select the "admin" or "adminpack" module.c

When compiling from source, the necessary files can be found in the xtra subdirectory of the pgAdmin source tree. For PostgreSQL 8.0, copy the admin directory under the postgresql contrib source directory, make and make install from there. For PostgreSQL 8.1, use the admin81 directory for that.

PostgreSQL 8.2 and above include the instrumentation functions in the "adminpack" contrib module. After the module is installed, you need to create the instrumentation functions in your maintenance database using the admin.sql script (admin81.sql for PostgreSQL 8.1) which are usually located in the pgsql share directory (e.g. /usr/local/pgsql/share)

Extended server instrumentation is not supported for PostgreSQL 7.3 and 7.4.

While this does provide useful informaiton about some sort of adminpack that is necessary, the actually steps necessary are omitted.


What is adminpack?

As it turns out Postgres comes with a number of additional helpful "contrib" sets of functions which you can install on demand .

One of these sets of function is the Admin Pack it, which:

"provides a number of support functions which pgAdmin and other administration and management tools can use to provide additional functionality, such as remote management of server log files."

Read more about it at http://www.postgresql.org/docs/8.4/static/adminpack.html


Installation:

Locate adminpack.sql on your system.
On Gentoo for PostgreSQL 8.4 it is in: /usr/share/postgresql-8.4/contrib/

Installation itself is quite simple:

psql -U postgres --file /usr/share/postgresql-8.4/contrib/adminpack.sql

* Substitute "postgres" for your distor's default PosgreSQL admin user.

Now close down pgadmin, restart PostgreSQL server and run pgadmin again.

If you ever need to uninstall it you can run:

psql -U postgres --file /usr/share/postgresql-8.4/contrib/uninstall_adminpack.sql

Other Utility Functions:

AdminPack is not the only goodie that Postgre comes with. The full list of additional utilities can be found here: http://www.postgresql.org/docs/8.4/static/contrib.html

A quick look inside the contrib/ folder reveals:

_int.sql            fuzzystrmatch.sql        pg_trgm.sql
adminpack.sql       hstore.sql               pgcrypto.sql
autoinc.sql         insert_username.sql      pgrowlocks.sql
btree_gin.sql       int_aggregate.sql        pgstattuple.sql
btree_gist.sql      isn.sql                  pgxml.sql
chkpass.sql         lo.sql                   refint.sql
citext.sql          ltree.sql                seg.sql
cube.sql            moddatetime.sql          sslinfo.sql
dblink.sql          pageinspect.sql          tablefunc.sql
dict_int.sql        pg_buffercache.sql       test_parser.sql
dict_xsyn.sql       pg_freespacemap.sql      timetravel.sql
earthdistance.sql   pg_stat_statements.sql   tsearch2.sql

Configuring PostgreSQL to use Database Stored Passwords

One of the neat features of PostgreSQL is it's flexible authentication. It allows use to map system logins to specific postgres users using pg_ident.conf and configure specific trust levels for any users, ip ranges/sockets, and databases you want in pg_hba.conf

However, what sometimes confuses a newcomer to Postgres (and in my experience MySQL folks) is that on on a number of distros the default installation does not really require them to enter the password when logining into "psql" or any other DB GUI. In fact many people continue to provide the password not knowing it is not actually having any effect.

Please note that I use Gentoo as a example disto, but only affects the defaults, such as file location and usename choices.

This happens because the default configuration of Postgres to automatically authenticate all localhost users. This is how it is done.

Locate and open Postgres pg_hba.conf file. (On Gentoo, it is in "/var/lib/postgresql/8.4/data/" where 8.4 is the current Postgres version.

Look at he following lines:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

As you can see the authentication method for local access, for all uses and all database and local IPs is set to "trust" which means they will be logged in without any password.

Postgres comes with a large variety of authentication methods such as:
"trust", "reject", "md5", "password", "gss", "sspi", "krb5", "ident", "pam", "ldap" or "cert".  Note that "password" sends passwords in clear text; "md5" is preferred since it sends encrypted passwords.

STOP: If you change your method to "md5" right now and reload Postgres, you'll probably be stuck with no way to get in to the database. I suggest you first change the default admin password. On Gentoo the admin user is "postgres" though on FreeBSD and some other systems it is different.

Keep the method at "trust" for now.

Now login to psql" from console.

psql -U postgres

* Change the Postgre admin "username" according to your system.

On Gentoo, your user has to belong to "postgres" group to be able to login to postgres using a Unix socket. Alternatively you can use sudo or specify the localhost using -h. (Example: psql -U postgres -h localhost). This probably works on other distributions.

ALTER USER postgres WITH LOGIN ENCRYPTED PASSWORD 'yourpassword';

* Change the Postgre admin "username" and "yourpassword" according to your system.

As you might have guessed the "LOGIN" option makes sure the user can do interactive logins.

Now edit pg_hba.conf and replace "trust" with "md5" and reload Postgres.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

All the options for configuring pg_hba.conf and Postgres authentication are available here:  http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.html

INTERESTING NOTE:

By default Postgres users *are NOT* per-database but are instead server users. However that should not be confused with user-database permissions, as Postgres fully supports those.

If you want to look into configuring Postgres with per-database users look please read about "db_user_namespace" server options - http://www.postgresql.org/docs/8.4/interactive/runtime-config-connection.html

Cleaning out distfiles on Gentoo

One thing about Gentoo is that it takes a bit of hard disk space to run it. For example the distfiles of outdated ebuilds do not automatically get cleaned out.

But doing so is a breeze with eclean utility from app-portage/gentoolkit

To clean (adding -i will prompt you before deletion, so it is also a dry run):

eclean -i distfiles

There are any number of ways you can do automatic cleaning, a few that come to mind are:

• adding cron task
• /etc/portage/postsync.d to clean out after every sync

Cleaning up package.keywords and more ...

If you are a power Gentoo user your package.keywords tends to get a bit full. Same is true of package.mask, package.unmask, package.use.

What's worse is that after a while the version program your wanted goes stable you have to remember the remove the reference to it in one of the /etc/portage/package.* files or potentially spend more time resolving dependencies.

Fortunately there is an excellent solution available in the form of eix-test-obsolete command. I'll let its help page describe itself:

It calls eix -tTc several times with various variable settings in order to
display missing packages or packages with obsolete entries in
/etc/portage/package.*

Eix is the uberfast, pre-cached Portage search alternative to emerge -s that every Gentoo system should have.

emerge -av app-portage/eix

Enable logging in PostgreSQL 8.4

To get rotated file logging working on PostgreSQL 8.4 edit "postgresql.conf" on your system. On Gentoo the file is located "/var/lib/postgresql/x.x/" where x.x is your PostgreSQL major and minor version (i.e 8.3, 8.4). Mine is currently 8.4.

Before we start though, you might want to make sure that the log files are written to a directory that can be read by other users, but still writable by the user that run PostgreSQL server. That would make tailing them much easier.

So on the Gentoo system I suggest you use /var/log and create a nested folder to reflect the PostgreSQL version. Also to make sure that is owned by both "postgres" user and "postgres" group.

cd  /var/log/
sudo mkdir -p postgresql/8.4
sudo chown -R postgres:postgres postgresql/8.4

Now modify the /var/lib/postgresql/8.4/postgresql.conf

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/lib/postgresql/8.4/'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
log_statement = 'mod'

Verbosity depends on the "log_statement" setting as mentioned here:
http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html

Here, logs are:

• written to "/var/log/postgresql/8.4" directory
• log files are time stamped
• log rotation is daily which is achieved by setting "log_rotation_age" to 1 day and log_rotation_size is disabled by setting it to 0

IMPORTANT NOTE:

The confusing part is that "logging_collector" used to be called redirect_stderr in versions before 8.3. (http://www.postgresql.org/docs/8.3/static/release-8-3.html)