PostgreSQL Manager

From apnsGuide

Contents 1 Connection Strings 1.1 PHP4, PHP5 1.2 Perl 1.3 Ruby w/ Rails config: 1.4 Java 2 Vacuuming 3 Database Creation

Connection Strings

Where *username* is the user, *password* is the password, and *database* is the database name:

PHP4, PHP5

$conn = pg_connect("host=localhost dbname=*database* user=*username* password=*password*");
$rs   = pg_query($conn, "SELECT ...");

Perl

use DBI;
my $conn = DBI->connect("dbi:Pg:dbname=*database*;".
                        "host=127.0.0.1;user=*username*;".
                        "password=*password*");
$stmt = $conn->prepare("SELECT ...");

Ruby w/ Rails config:

add the following to file *application_name*/config/database.yml

production: 
   adapter: postgresql
   database: *database*
   username: *username*
   password: *password*
   host: 127.0.0.1
 
development: 
   adapter: postgresql
   database: *database*
   username: *username*
   password: *password*
   host: 127.0.0.1

Java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
/** class declaration ... method declarations ... */
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/*database*",
                                              "*username*",
                                              "*password*");
stmt = conn.createStatement();
rs   = stmt.executeQuery("SELECT ...");

Vacuuming

Prior to version 8.1, periodic vacuuming was necessary to

1. recover disk space occupied by updated or deleted rows
2. update data statistics used by the PostgreSQL query planner
3. protect against loss of very old data due to transaction ID wraparound

PostgreSQL 8.1 introduced an autovacuum daemon which automates this process periodically and vacuums for you, thus manually vacuuming a database is no longer necessary. All servers run 8.1 with the autovacuum daemon enabled.

Database Creation

Databases are created using a tablespace within your filesystem. The tablespaces are created under /var/lib/pgsql/ (relative to the chroot'd view).