2.6 Tips, Useful Stuff & Quickies

• List all available databases on a database host:
  $ echo "SELECT datname FROM pg_database ORDER BY datname;" | psql -d template1
  
  
• Dump complete database into a single file:
  pg_dump -f "foo.dump" <DBNAME>

Please check the following URL for a listing of the current user-support mailing lists:

www.ca.postgresql.org/users-lounge/index.html#maillist↑

All of the mailing lists are currently archived and viewable at:

archives.postgresql.org/↑

And, so that we have an idea of who is using what, please connect to the
following registration URL:

www.pgsql.com/register/submit.php↑

How to access to a PostgreSQL DB from an external host?

Edit conf file:
vi /var/lib/pgsql/data/pg_hba.conf

host    all     195.112.71.7    255.255.255.255         trust

Might be necessary to restart PostgreSQL ;-)

bash-2.05a$ /usr/bin/pg_ctl stop -D $PGDATA -s -m fast
bash-2.05a$ postmaster -i -p 5432 >/dev/null 2>&1 &

Add a user

bash-2.05a$ createuser
Enter name of user to add: youruser
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

Enter DB Monitor

bash-2.05a$ psql -d yourdb
Welcome to psql, the PostgreSQL interactive terminal.

auctionline2002=# grant all on items to youruser;
GRANT

Access with Perl

sub open_db_connection {

        # Database parameters
        my $DB_DRIVER   = "Pg";
        my $DB_NAME     = "yourdb";
        my $DB_OPTIONS  = "host=yourhost";
        my $DB_USER     = "yourhost";
        my $DB_PASSWORD = "";

        my $DB_DSN = "DBI:$DB_DRIVER:dbname=$DB_NAME;$DB_OPTIONS";

        # Try to open the database connection
        my $dbh = DBI->connect($DB_DSN, $DB_USER, $DB_PASSWORD);

        unless (ref($dbh)) { return undef; }
        else { return $dbh; }
};

Simple CREATE

create table sample (
first varchar(50),
last  varchar(50),
age   numeric
);

Create an application log at your database

CREATE TABLE log (
        time            TIMESTAMP       NOT NULL ,
        module          VARCHAR(127)    NOT NULL ,
        usr             VARCHAR(31)     NOT NULL ,
        importance      SMALLINT                NOT NULL , -- 1 = highest
        message         VARCHAR(255)
);

INSERT INTO log 
        (time, module, usr, importance, message) 
        VALUES ('now'::Timestamp, 'test', 'retoh', 2, 'hallo welt'); 

Recipe «Within n days» (Type DATE)

SELECT COUNT(*) FROM users WHERE last_activity > now()::date - INTERVAL '90 DAYS';

Primary Keys:
must be unique und data required.

- Foreign Key: abzuhaken: data required.

Dennoch mössen Sie die Schlüssel dann in SQL definieren und dabei den SQL-Befehl alter table anwenden, z.B:

alter table personen primary key (panr); alter table telefon foreign key (panr) references personen;

Wollen Sie einen Schlössel wieder entfernen, so geschieht das ebenfalls mittels SQL mit den Anweisungen:

alter table personen drop primary key; alter table personen drop foreign key (panr);

su - postgres
vi .bashrc

foo=bar
PGDATA=/usr/local/pgsql/data/
export PGDATA bar