PostgreSQL

From AdminWiki

(Difference between revisions)
Jump to: navigation, search
m (fixed url)
(Table Size)
Line 55: Line 55:
The ''relkind'' column holds the type of the data and ''reltuples'' the count of rows in this table.
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 ;
+
   SELECT relname, relfilenode, relkind, reltuples, relpages, (relpages * 8) as relpages_kb FROM pg_class ORDER BY relpages DESC ;
 +
 
 +
relnames starting with <tt>pg_toast</tt> are [http://www.postgresql.org/docs/8.1/interactive/storage-toast.html TOAST]-storage for large tables. Compare the appended number with the relfilenodes to get the associated table.
example output:
example output:

Revision as of 00:11, 27 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;

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 * 8) as relpages_kb FROM pg_class ORDER BY relpages DESC ;

relnames starting with pg_toast are TOAST-storage for large tables. Compare the appended number with the relfilenodes to get the associated table.

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