PostgreSQL

From AdminWiki

Revision as of 03:26, 25 May 2006 by Robe (Talk | contribs)
Jump to: navigation, search

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;

Table Size

If you want to know which table in your database claims the most diskspace, here is a query that returns the size of the tables from the current database.

A block is 8192 bytes therefore I calculate the kb size in an extra column. relfilenode column holds the file name for this table / data. You can find it in the data directory from postgres (main/). The relkind column holds the type of the data and reltuples the count of rows in this table.

 SELECT relname, relfilenode, relkind, reltuples, relpages, ((relpages * 8192) / 1024) as relpages_kb FROM pg_class ORDER BY relpages DESC ;

example output:

            relname               | relfilenode | relkind | reltuples | relpages | relpages_kb
----------------------------------+-------------+---------+-----------+----------+-------------
 pg_toast_3377061                 |     3377067 | t       |      1982 |      433 |        3464
 user_logging                     |     3377300 | r       |      3313 |      243 |        1944
 pg_proc_proname_args_nsp_index   |       16642 | i       |      1695 |      138 |        1104
 article_menu                     |     3377283 | r       |      8267 |      124 |         992
 article_menu_pkey                |     3395779 | i       |      8267 |       74 |         592
 article_element                  |     3377234 | r       |      2799 |       69 |         552

Accessing the Database

psql and other CLI programs

psql and no password login

pgsql, command unlike the mysql command, has no option for a password. But there are two ways to go around this in a very efficient way.

enviroment variable

 export PGPASSWORD=password
 export PGUSER=username
 export PGHOST=host

with PGPASSWORD set you can start hacking away your bash scripts.

with a ~/.pgpass file

pgpass Documentation

basically just

hostname:port:database:username:password
Personal tools