PostgreSQL

From AdminWiki

(Difference between revisions)
Jump to: navigation, search
(initial commit)
m (Accounts and permissions)
Line 19: Line 19:
</pre>
</pre>
-
That should settle it.
+
 
Also be sure to tune the [http://www.postgresql.org/docs/8.1/interactive/client-authentication.html pg_hba.conf] in the data-directory to your needs. To emulate MySQLs default "every user needs to authenticate from everywhere"-semantics use the following config:
Also be sure to tune the [http://www.postgresql.org/docs/8.1/interactive/client-authentication.html pg_hba.conf] in the data-directory to your needs. To emulate MySQLs default "every user needs to authenticate from everywhere"-semantics use the following config:

Revision as of 00:19, 25 May 2006

Contents

General

Setup

Storage

As for every tranactional database, disk I/O is the main limiting factor for PostgresSQL. If you plan to deploy a high-usage database please take some precautions on the used storage. Use either a RAID 1 or 10 and choose a FS that does fast block-IO (ext2).

Accounts and permissions

When starting with a fresh database, you've to create some users first before you can start using it.

Basic steps:

su - postgresql
createuser -P <user>
createdb -O <user> <db>


Also be sure to tune the pg_hba.conf in the data-directory to your needs. To emulate MySQLs default "every user needs to authenticate from everywhere"-semantics use the following config:

local   all         all                               md5
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5 

Maintenance

Vacuuming

What?

Since PostgresSQL is a transactional database, old rows don't get actually removed/replaced when you update/delete them (since they might be still needed in older/long running transactions). To actually free them you need to issue a vacuum.

A normal vacuum will only mark deprecated rows for reuse, to actually reclaim diskspace (e.g. when having deleted large amounts of data) you need to issue a full vacuum. Please note that it might be faster to backup the data you want to keep and truncate the table if you plan to remove large portions of a table.

autovacuum

Fixing broken databases

set zero_damaged_pages to on; vacuum; pray;

Personal tools