Search options

With debian it's all about the shades of gray...there's probably a fresh postgresql 7.4.6 or something in the untrusted, unstable, unwashed, boogie-oogie-oogie-unblessed repositories.  I've come to regard Debian linux like she's some subtly attractive woman until I realize that spending the night with her requires such a brutal level of attention to detail that only the most ardent enthusiast/fanboy/rabid-fanatic would be able to service her correctly.  That said, when I'm being brutally punished in an RPM dependency dungeon (crack that whip!) I think of her fondly, but then it passes and I forget about her for months, even years at a time.  :-)
I've heard she's wearing attractive installation lingere, I might have to date her again sometime.

Despite the marketeer feeling a thread like this carries...

A needed pgAdmin III link.

I've used phpPgAdmin but usually I'm up to absolutely no good at the prompt.

On a personal note, I really dislike/distrust gui database tools on production servers. Not only can they serve as a root-kit vector, but sometimes it's hard to understand all the nuances that go with the tools. To have something nice on a development server to help speed up things and then using these toolkits to easily package changes, queries, and scripts for upload and deployment on a production system makes life good. These amenities become liabilities when a developer/admin cannot keep up with the security changes and then someone develops an exploit against their database admin toolkit and they don't hear about it until some guy in charge of the network starts noticing massive amounts of activity.

I've been using PostgreSQL since 1997, when I wrote a support ticket web app using Perl and Postgresql.  I remember having to use gcc to compile the interface module to use PostgreSQL for CGI.  Once the Perl libs showed up I never looked at c-based CGI ever again.

If you're installing the current version PostgreSQL don't bother tinkering under the hood too much. It's easy to give PostgeSQL too much power, its setup to handle about anything you want to do with it unless you're working on something that wickedly parallel (clustered/sync'd/and nested joining up the wazoo).

Anyone using PostgreSQL should only have to do the two following tasks daily:

1)setup an scheduled backup

2)setup a scheduled vacuum

Here's some crontab examples:

30 4 * * * /home/db_su_name/scripts/dbroller 2>&1
* 3 * * * /usr/bin/vacuumdb -q -d punbb 2>&1

Here's the code I use for a database backup. Basically you add the name of any of the databases you want to keep a backup of into the array. Converting this to run with the database name passed as an argument is a trivial exercise.

#!/usr/bin/perl

$dbFileFilter="%s.%s";

my @dbName=("punbb","template1");

foreach $db(@dbName){

        $dbFile  = sprintf "$dbFileFilter",$db,"sql";
        $rslt=`pg_dump $db > ~db_su_name/pgData/$dbFile`;
}

I also use the logrotate daemon to do my bidding...basically keeping me from having a buttload of database files hanging around. If you are running a "snapshot" service, then this isn't for you.  The benefits of this are a limited number of database files are kept and you can set how much backstepping you want to do.

In the /etc/logrotate.d/ folder I create a file called "pg-bak"

/home/pg_su_name/pgData/*.sql
{
    rotate 7
    daily
    missingok
    compress
}

This means I keep 7 days of compressed backups.

Combine this with rsync to an offsite server and your bases are covered.

I cannot overstate the performance gains you will get from performing a vacuum on the punbb database once a day. PostgreSQL was loading the server pretty high until I started doing a daily vacuum.

Please check out the drop-down list in the timelab link I posted. I'm pretty sure there are 500 entries.

I did a tree of the zoneinfo folder and used a bit of perl to create a flatfile I could push at postgresql to fill a table. I'm going to pare it down a little but that's how many references there are.

When I was mentioning semaphores I was trying to say that I am using the existing code and definitions for regenerating cache files as an example. So I have setup the same mechanisms which exist for files PunBB is caching. I try to learn from examples.

As usual life is performing some blocking interrupts on my completing things.
I'm learning all about PHP's array fetishes and how to properly play with multi-dimensional associative arrays.

I've rolled a database mod, and modifications to several templates which comprise a complete change-over from using the numeric locality system to using the zoneinfo libs through php. I'm going to try to test this over the weekend with an event facility/plugin I wrote and if it works I'll post it all on punres.

In writing this mod I have learned about the caching/semaphores punbb uses, and reduced the static page sizes by several kb.

The flipside of this mod would probably be a localization layer. For English and some European forums it wouldn't be an issue, beyond that I imagine spelling "Asia/Pyongyang" would look much different.

The nice thing about using the zoneinfo calls is that instead of just relying on relative GMT the user can actually select a written locality. I also modified the header.php to display their timezone next to the Last Visit, so it looks like:

Last visit: 2005-05-09 15:16:34 (US/Arizona)

Hope everyone has a nice weekend.