PostgreSQL
From AdminWiki
m (→Accounts and permissions) |
|||
Line 46: | Line 46: | ||
vacuum; | vacuum; | ||
pray; | pray; | ||
+ | |||
+ | == Table Size == | ||
+ | |||
+ | if you want to know which table in your database uses the most data, here is query that returns the size of the tables. | ||
+ | |||
+ | a block is 8192 bytes, 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 (main/). | ||
+ | the relkind, holds the type of the data and reltuples the rows in this column. | ||
+ | |||
+ | SELECT relname, relfilenode, relkind, reltuples, relpages, ((relpages * 8192) / 1024) as relpages_kb FROM pg_class ORDER BY relpages DESC ; | ||
+ | |||
+ | example output: | ||
+ | |||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | |||
+ | = 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 | ||
+ | |||
+ | [http://www.postgresql.org/docs/8.1/static/libpq-pgpass.html|Postgres (8.1) pgpass Documentation] | ||
+ | |||
+ | basically just | ||
+ | hostname:port:database:username:password |
Revision as of 01:04, 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;
Table Size
if you want to know which table in your database uses the most data, here is query that returns the size of the tables.
a block is 8192 bytes, 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 (main/). the relkind, holds the type of the data and reltuples the rows in this column.
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
basically just
hostname:port:database:username:password