http://amd.co.at/adminw/index.php?title=Special:Contributions/Sts&feed=atom&limit=50&target=Sts&year=&month=AdminWiki - User contributions [en]2024-03-28T18:03:05ZFrom AdminWikiMediaWiki 1.16.2http://amd.co.at/adminwiki/PostgreSQLPostgreSQL2011-07-26T12:50:58Z<p>Sts: /* Useful Queries */</p>
<hr />
<div><br />
= General =<br />
<br />
= Setup =<br />
<br />
== Storage ==<br />
<br />
As for every transactional 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).<br />
<br />
== Accounts and permissions ==<br />
<br />
When starting with a fresh database, you've to [http://www.postgresql.org/docs/current/interactive/user-manag.html create some users] first before you can start using it.<br />
<br />
Basic steps:<br />
<br />
<pre><br />
su - postgresql<br />
createuser -P <user><br />
createdb -O <user> <db><br />
</pre><br />
<br />
<br />
<br />
Also be sure to tune the [http://www.postgresql.org/docs/current/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:<br />
<br />
<pre><br />
local all all md5<br />
host all all 127.0.0.1/32 md5<br />
host all all ::1/128 md5 <br />
</pre><br />
<br />
= Performance and Tuning =<br />
<br />
== Useful Queries ==<br />
<br />
SELECT * FROM pg_stat_activiy;<br />
<br />
This will show you all currently running transactions/queries. Eg. Look out for longer running queries, if things go wrong.<br />
<br />
SELECT * FROM pg_lock;<br />
<br />
This will show you a list of currently locked resources.<br />
<br />
== Sequential Scans ==<br />
<br />
If you're getting sequential scans despite having indexes and have run a <tt>vacuum analyze</tt> recently you might need to increase the [http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET default_statistics_target].<br />
<br />
== max_fsm_* ==<br />
<br />
<br />
* http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp<br />
* http://www.postgresql.org/docs/current/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM<br />
<br />
== document me ==<br />
<br />
* sort_mem<br />
* shared_buffers<br />
<br />
= Maintenance =<br />
<br />
== Vacuuming ==<br />
<br />
=== What? ===<br />
<br />
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 [http://www.postgresql.org/docs/current/interactive/maintenance.html vacuum].<br />
<br />
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.<br />
<br />
=== autovacuum ===<br />
<br />
Check status of autovacuums in a given Database:<br />
<br />
<pre><br />
select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables;<br />
</pre><br />
<br />
=== Transaction ID Wraparound ===<br />
<br />
A regularly run plain or "lazy" vacuum should prevent Transaction ID Wraparound in all cases, a full vacuum is not required.<br />
<br />
You can check your TXID counters with the following query:<br />
<br />
<pre><br />
SELECT datname, age(datfrozenxid) FROM pg_database;<br />
</pre><br />
<br />
If the age is noticeably higher than 1 Billion (2^30 or 1073741824 to be exact) after a recent vacuum something is probably not working as intended; if it should be anywhere near 2 billion you should take immediate action to prevent problems.<br />
<br />
* http://www.postgresql.org/docs/current/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND<br />
<br />
== Fixing broken databases ==<br />
<br />
set zero_damaged_pages to on;<br />
vacuum;<br />
pray;<br />
<br />
== Table Size ==<br />
<br />
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.<br />
<br />
''Note'': These examples assume that you use the default page size of 8kb.<br />
<br />
<br />
''relfilenode'' column holds the file name for this table / data. You can find it in the data directory from postgres (main/).<br><br />
The ''relkind'' column holds the type of the data and ''reltuples'' the count of rows in this table.<br />
<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT relname, pg_size_pretty(relpages::bigint * 8 * 1024) as size, relkind, reltuples::bigint as rows, relpages, relfilenode FROM pg_class ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
relnames starting with <tt>pg_toast</tt> are [http://www.postgresql.org/docs/current/interactive/storage-toast.html TOAST]-storage for large tables. Compare the appended number with the relfilenodes to get the associated table. <br />
<br />
If you have a lot of huge data, thre will be a lot of toast tables. With this query you get an additional field that shows the toast table name if this table has one, and the original table for each toast table:<br />
<br />
This works until PostgreSQL 9.0, see modified query below.<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS size, CASE WHEN relkind = 't' THEN (SELECT pgd.relname FROM pg_class pgd WHERE pgd.relfilenode::text = SUBSTRING(pg.relname FROM 10)) ELSE (SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.relfilenode) END AS refrelname, relfilenode, relkind, reltuples::bigint, relpages FROM pg_class pg ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
example output:<br />
<br />
<pre><br />
relname | relfilenode | relkind | reltuples | relpages | relpages_kb<br />
-----------------------------------------+-------------+---------+-------------+----------+-------------<br />
pg_toast_16496 | 16499 | t | 6.74842e+06 | 1684158 | 13473264<br />
eintrag | 16510 | r | 3.97601e+06 | 271484 | 2171872<br />
admin_log | 16496 | r | 9.49351e+06 | 248608 | 1988864<br />
history | 16654 | r | 1.98684e+07 | 204714 | 1637712<br />
ctimes | 16695 | r | 1.36451e+07 | 189826 | 1518608<br />
</pre><br />
<br />
For PostgreSQL 9.0 the query below will work, it also adds some additional info for toast indexes:<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT pgn.nspname, relname, pg_size_pretty(relpages::bigint * 8 * 1024) AS size, <br />
CASE WHEN relkind = 't' THEN <br />
(SELECT pgd.relname FROM pg_class pgd WHERE pgd.reltoastrelid = pg.oid) <br />
WHEN nspname = 'pg_toast' AND relkind = 'i' THEN<br />
(SELECT pgt.relname FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', ''''''')) <br />
ELSE <br />
(SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.oid) <br />
END::varchar AS refrelname, <br />
CASE WHEN nspname = 'pg_toast' AND relkind = 'i' THEN<br />
(SELECT pgts.relname FROM pg_class pgts WHERE pgts.reltoastrelid = (SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '''''''))) <br />
END AS relidxrefrelname, <br />
relfilenode, relkind, reltuples::bigint, relpages <br />
FROM pg_class pg, pg_namespace pgn WHERE pg.relnamespace = pgn.oid <br />
AND pgn.nspname NOT IN ('information_schema', 'pg_catalog') <br />
ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
Sample output:<br />
<br />
<pre><br />
nspname | relname | size | refrelname | relidxrefrelname | relfilenode | relkind | reltuples | relpages <br />
----------+--------------------------------------------------------------+------------+----------------------------+----------------------------+-------------+---------+-----------+----------<br />
pg_toast | pg_toast_12633551 | 12 GB | error_email_collect | | 16113687 | t | 6506013 | 1532166<br />
public | log_email_sent | 4394 MB | pg_toast_12633624 | | 16112041 | r | 28645328 | 562416<br />
public | mail_log | 3260 MB | pg_toast_12633661 | | 16113649 | r | 13556149 | 417328<br />
public | error_email_collect | 1003 MB | pg_toast_12633551 | | 16113684 | r | 1161103 | 128345<br />
public | log_hash_reference | 789 MB | pg_toast_12633642 | | 18241270 | r | 7046247 | 100960<br />
public | email | 504 MB | pg_toast_12633482 | | 16111750 | r | 3673276 | 64568<br />
public | log_email_sent_log_id_idx | 492 MB | | | 16112048 | i | 28684712 | 62947<br />
public | log_email_sent_pkey | 492 MB | | | 16112047 | i | 28684712 | 62947<br />
public | idx_mail_log_date_created | 291 MB | | | 16113677 | i | 13563234 | 37192<br />
public | mail_log_pkey | 233 MB | | | 16113655 | i | 13563234 | 29765<br />
pg_toast | pg_toast_12633551_index | 139 MB | pg_toast_12633551 | error_email_collect | 16113689 | i | 6506013 | 17840<br />
</pre><br />
<br />
relidxrefrelname is the original table on which the toast table is based for an toast index<br />
<br />
More about admin functions regarding databasize and table size can be found in the [http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE System Admin Functions] under the Table 9-48.<br />
<br />
== Cancelling queries ==<br />
<br />
To gracefully cancel queries, one can use [http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE pg_cancel_backend()]. This should be safer than SIGTERMing the according process.<br />
<br />
= Accessing the Database =<br />
<br />
== psql and other CLI programs ==<br />
<br />
=== psql password prompts ===<br />
<br />
psql has, unlike the mysql client, no option to supply a password on the command line (which would be insecure). There are two solutions for this problem:<br />
<br />
* environment variables:<br />
export PGPASSWORD=password<br />
export PGUSER=username<br />
export PGHOST=host<br />
<br />
* a <tt>~/.pgpass</tt> file<br />
<br />
[http://www.postgresql.org/docs/current/static/libpq-pgpass.html pgpass Documentation]<br />
<br />
Format:<br />
hostname:port:database:username:password</div>Stshttp://amd.co.at/adminwiki/PostgreSQLPostgreSQL2011-07-26T12:50:46Z<p>Sts: /* Useful Queries */</p>
<hr />
<div><br />
= General =<br />
<br />
= Setup =<br />
<br />
== Storage ==<br />
<br />
As for every transactional 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).<br />
<br />
== Accounts and permissions ==<br />
<br />
When starting with a fresh database, you've to [http://www.postgresql.org/docs/current/interactive/user-manag.html create some users] first before you can start using it.<br />
<br />
Basic steps:<br />
<br />
<pre><br />
su - postgresql<br />
createuser -P <user><br />
createdb -O <user> <db><br />
</pre><br />
<br />
<br />
<br />
Also be sure to tune the [http://www.postgresql.org/docs/current/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:<br />
<br />
<pre><br />
local all all md5<br />
host all all 127.0.0.1/32 md5<br />
host all all ::1/128 md5 <br />
</pre><br />
<br />
= Performance and Tuning =<br />
<br />
== Useful Queries ==<br />
<br />
SELECT * FROM pg_stat_activiy;<br />
<br />
This will show you all currently running transactions/queries. Eg. Look out for longer running queries, if things go wrong.<br />
<br />
SELECT * FROM pg_lock<br />
<br />
This will show you a list of currently locked resources.<br />
<br />
== Sequential Scans ==<br />
<br />
If you're getting sequential scans despite having indexes and have run a <tt>vacuum analyze</tt> recently you might need to increase the [http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET default_statistics_target].<br />
<br />
== max_fsm_* ==<br />
<br />
<br />
* http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp<br />
* http://www.postgresql.org/docs/current/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM<br />
<br />
== document me ==<br />
<br />
* sort_mem<br />
* shared_buffers<br />
<br />
= Maintenance =<br />
<br />
== Vacuuming ==<br />
<br />
=== What? ===<br />
<br />
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 [http://www.postgresql.org/docs/current/interactive/maintenance.html vacuum].<br />
<br />
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.<br />
<br />
=== autovacuum ===<br />
<br />
Check status of autovacuums in a given Database:<br />
<br />
<pre><br />
select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables;<br />
</pre><br />
<br />
=== Transaction ID Wraparound ===<br />
<br />
A regularly run plain or "lazy" vacuum should prevent Transaction ID Wraparound in all cases, a full vacuum is not required.<br />
<br />
You can check your TXID counters with the following query:<br />
<br />
<pre><br />
SELECT datname, age(datfrozenxid) FROM pg_database;<br />
</pre><br />
<br />
If the age is noticeably higher than 1 Billion (2^30 or 1073741824 to be exact) after a recent vacuum something is probably not working as intended; if it should be anywhere near 2 billion you should take immediate action to prevent problems.<br />
<br />
* http://www.postgresql.org/docs/current/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND<br />
<br />
== Fixing broken databases ==<br />
<br />
set zero_damaged_pages to on;<br />
vacuum;<br />
pray;<br />
<br />
== Table Size ==<br />
<br />
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.<br />
<br />
''Note'': These examples assume that you use the default page size of 8kb.<br />
<br />
<br />
''relfilenode'' column holds the file name for this table / data. You can find it in the data directory from postgres (main/).<br><br />
The ''relkind'' column holds the type of the data and ''reltuples'' the count of rows in this table.<br />
<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT relname, pg_size_pretty(relpages::bigint * 8 * 1024) as size, relkind, reltuples::bigint as rows, relpages, relfilenode FROM pg_class ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
relnames starting with <tt>pg_toast</tt> are [http://www.postgresql.org/docs/current/interactive/storage-toast.html TOAST]-storage for large tables. Compare the appended number with the relfilenodes to get the associated table. <br />
<br />
If you have a lot of huge data, thre will be a lot of toast tables. With this query you get an additional field that shows the toast table name if this table has one, and the original table for each toast table:<br />
<br />
This works until PostgreSQL 9.0, see modified query below.<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS size, CASE WHEN relkind = 't' THEN (SELECT pgd.relname FROM pg_class pgd WHERE pgd.relfilenode::text = SUBSTRING(pg.relname FROM 10)) ELSE (SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.relfilenode) END AS refrelname, relfilenode, relkind, reltuples::bigint, relpages FROM pg_class pg ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
example output:<br />
<br />
<pre><br />
relname | relfilenode | relkind | reltuples | relpages | relpages_kb<br />
-----------------------------------------+-------------+---------+-------------+----------+-------------<br />
pg_toast_16496 | 16499 | t | 6.74842e+06 | 1684158 | 13473264<br />
eintrag | 16510 | r | 3.97601e+06 | 271484 | 2171872<br />
admin_log | 16496 | r | 9.49351e+06 | 248608 | 1988864<br />
history | 16654 | r | 1.98684e+07 | 204714 | 1637712<br />
ctimes | 16695 | r | 1.36451e+07 | 189826 | 1518608<br />
</pre><br />
<br />
For PostgreSQL 9.0 the query below will work, it also adds some additional info for toast indexes:<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT pgn.nspname, relname, pg_size_pretty(relpages::bigint * 8 * 1024) AS size, <br />
CASE WHEN relkind = 't' THEN <br />
(SELECT pgd.relname FROM pg_class pgd WHERE pgd.reltoastrelid = pg.oid) <br />
WHEN nspname = 'pg_toast' AND relkind = 'i' THEN<br />
(SELECT pgt.relname FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', ''''''')) <br />
ELSE <br />
(SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.oid) <br />
END::varchar AS refrelname, <br />
CASE WHEN nspname = 'pg_toast' AND relkind = 'i' THEN<br />
(SELECT pgts.relname FROM pg_class pgts WHERE pgts.reltoastrelid = (SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '''''''))) <br />
END AS relidxrefrelname, <br />
relfilenode, relkind, reltuples::bigint, relpages <br />
FROM pg_class pg, pg_namespace pgn WHERE pg.relnamespace = pgn.oid <br />
AND pgn.nspname NOT IN ('information_schema', 'pg_catalog') <br />
ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
Sample output:<br />
<br />
<pre><br />
nspname | relname | size | refrelname | relidxrefrelname | relfilenode | relkind | reltuples | relpages <br />
----------+--------------------------------------------------------------+------------+----------------------------+----------------------------+-------------+---------+-----------+----------<br />
pg_toast | pg_toast_12633551 | 12 GB | error_email_collect | | 16113687 | t | 6506013 | 1532166<br />
public | log_email_sent | 4394 MB | pg_toast_12633624 | | 16112041 | r | 28645328 | 562416<br />
public | mail_log | 3260 MB | pg_toast_12633661 | | 16113649 | r | 13556149 | 417328<br />
public | error_email_collect | 1003 MB | pg_toast_12633551 | | 16113684 | r | 1161103 | 128345<br />
public | log_hash_reference | 789 MB | pg_toast_12633642 | | 18241270 | r | 7046247 | 100960<br />
public | email | 504 MB | pg_toast_12633482 | | 16111750 | r | 3673276 | 64568<br />
public | log_email_sent_log_id_idx | 492 MB | | | 16112048 | i | 28684712 | 62947<br />
public | log_email_sent_pkey | 492 MB | | | 16112047 | i | 28684712 | 62947<br />
public | idx_mail_log_date_created | 291 MB | | | 16113677 | i | 13563234 | 37192<br />
public | mail_log_pkey | 233 MB | | | 16113655 | i | 13563234 | 29765<br />
pg_toast | pg_toast_12633551_index | 139 MB | pg_toast_12633551 | error_email_collect | 16113689 | i | 6506013 | 17840<br />
</pre><br />
<br />
relidxrefrelname is the original table on which the toast table is based for an toast index<br />
<br />
More about admin functions regarding databasize and table size can be found in the [http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE System Admin Functions] under the Table 9-48.<br />
<br />
== Cancelling queries ==<br />
<br />
To gracefully cancel queries, one can use [http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE pg_cancel_backend()]. This should be safer than SIGTERMing the according process.<br />
<br />
= Accessing the Database =<br />
<br />
== psql and other CLI programs ==<br />
<br />
=== psql password prompts ===<br />
<br />
psql has, unlike the mysql client, no option to supply a password on the command line (which would be insecure). There are two solutions for this problem:<br />
<br />
* environment variables:<br />
export PGPASSWORD=password<br />
export PGUSER=username<br />
export PGHOST=host<br />
<br />
* a <tt>~/.pgpass</tt> file<br />
<br />
[http://www.postgresql.org/docs/current/static/libpq-pgpass.html pgpass Documentation]<br />
<br />
Format:<br />
hostname:port:database:username:password</div>Stshttp://amd.co.at/adminwiki/PostgreSQLPostgreSQL2011-07-26T12:50:35Z<p>Sts: /* Performance and Tuning */</p>
<hr />
<div><br />
= General =<br />
<br />
= Setup =<br />
<br />
== Storage ==<br />
<br />
As for every transactional 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).<br />
<br />
== Accounts and permissions ==<br />
<br />
When starting with a fresh database, you've to [http://www.postgresql.org/docs/current/interactive/user-manag.html create some users] first before you can start using it.<br />
<br />
Basic steps:<br />
<br />
<pre><br />
su - postgresql<br />
createuser -P <user><br />
createdb -O <user> <db><br />
</pre><br />
<br />
<br />
<br />
Also be sure to tune the [http://www.postgresql.org/docs/current/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:<br />
<br />
<pre><br />
local all all md5<br />
host all all 127.0.0.1/32 md5<br />
host all all ::1/128 md5 <br />
</pre><br />
<br />
= Performance and Tuning =<br />
<br />
== Useful Queries ==<br />
<br />
SELECT * FROM pg_stat_activiy;<br />
<br />
This will show you all currently running transactions/queries. Eg. Look out for longer running queries, if things go wrong.<br />
<br />
<br />
SELECT * FROM pg_lock<br />
<br />
This will show you a list of currently locked resources.<br />
<br />
<br />
== Sequential Scans ==<br />
<br />
If you're getting sequential scans despite having indexes and have run a <tt>vacuum analyze</tt> recently you might need to increase the [http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET default_statistics_target].<br />
<br />
== max_fsm_* ==<br />
<br />
<br />
* http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp<br />
* http://www.postgresql.org/docs/current/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM<br />
<br />
== document me ==<br />
<br />
* sort_mem<br />
* shared_buffers<br />
<br />
= Maintenance =<br />
<br />
== Vacuuming ==<br />
<br />
=== What? ===<br />
<br />
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 [http://www.postgresql.org/docs/current/interactive/maintenance.html vacuum].<br />
<br />
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.<br />
<br />
=== autovacuum ===<br />
<br />
Check status of autovacuums in a given Database:<br />
<br />
<pre><br />
select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_user_tables;<br />
</pre><br />
<br />
=== Transaction ID Wraparound ===<br />
<br />
A regularly run plain or "lazy" vacuum should prevent Transaction ID Wraparound in all cases, a full vacuum is not required.<br />
<br />
You can check your TXID counters with the following query:<br />
<br />
<pre><br />
SELECT datname, age(datfrozenxid) FROM pg_database;<br />
</pre><br />
<br />
If the age is noticeably higher than 1 Billion (2^30 or 1073741824 to be exact) after a recent vacuum something is probably not working as intended; if it should be anywhere near 2 billion you should take immediate action to prevent problems.<br />
<br />
* http://www.postgresql.org/docs/current/interactive/maintenance.html#VACUUM-FOR-WRAPAROUND<br />
<br />
== Fixing broken databases ==<br />
<br />
set zero_damaged_pages to on;<br />
vacuum;<br />
pray;<br />
<br />
== Table Size ==<br />
<br />
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.<br />
<br />
''Note'': These examples assume that you use the default page size of 8kb.<br />
<br />
<br />
''relfilenode'' column holds the file name for this table / data. You can find it in the data directory from postgres (main/).<br><br />
The ''relkind'' column holds the type of the data and ''reltuples'' the count of rows in this table.<br />
<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT relname, pg_size_pretty(relpages::bigint * 8 * 1024) as size, relkind, reltuples::bigint as rows, relpages, relfilenode FROM pg_class ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
relnames starting with <tt>pg_toast</tt> are [http://www.postgresql.org/docs/current/interactive/storage-toast.html TOAST]-storage for large tables. Compare the appended number with the relfilenodes to get the associated table. <br />
<br />
If you have a lot of huge data, thre will be a lot of toast tables. With this query you get an additional field that shows the toast table name if this table has one, and the original table for each toast table:<br />
<br />
This works until PostgreSQL 9.0, see modified query below.<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS size, CASE WHEN relkind = 't' THEN (SELECT pgd.relname FROM pg_class pgd WHERE pgd.relfilenode::text = SUBSTRING(pg.relname FROM 10)) ELSE (SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.relfilenode) END AS refrelname, relfilenode, relkind, reltuples::bigint, relpages FROM pg_class pg ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
example output:<br />
<br />
<pre><br />
relname | relfilenode | relkind | reltuples | relpages | relpages_kb<br />
-----------------------------------------+-------------+---------+-------------+----------+-------------<br />
pg_toast_16496 | 16499 | t | 6.74842e+06 | 1684158 | 13473264<br />
eintrag | 16510 | r | 3.97601e+06 | 271484 | 2171872<br />
admin_log | 16496 | r | 9.49351e+06 | 248608 | 1988864<br />
history | 16654 | r | 1.98684e+07 | 204714 | 1637712<br />
ctimes | 16695 | r | 1.36451e+07 | 189826 | 1518608<br />
</pre><br />
<br />
For PostgreSQL 9.0 the query below will work, it also adds some additional info for toast indexes:<br />
<blockquote style="font-family: monospace; background-color:#F9F9F9; border:1px dashed #2F6FAB; color:black; line-height:1.1em; padding:1em;"><br />
SELECT pgn.nspname, relname, pg_size_pretty(relpages::bigint * 8 * 1024) AS size, <br />
CASE WHEN relkind = 't' THEN <br />
(SELECT pgd.relname FROM pg_class pgd WHERE pgd.reltoastrelid = pg.oid) <br />
WHEN nspname = 'pg_toast' AND relkind = 'i' THEN<br />
(SELECT pgt.relname FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', ''''''')) <br />
ELSE <br />
(SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid = pgc.oid) <br />
END::varchar AS refrelname, <br />
CASE WHEN nspname = 'pg_toast' AND relkind = 'i' THEN<br />
(SELECT pgts.relname FROM pg_class pgts WHERE pgts.reltoastrelid = (SELECT pgt.oid FROM pg_class pgt WHERE SUBSTRING(pgt.relname FROM 10) = REPLACE(SUBSTRING(pg.relname FROM 10), '_index', '''''''))) <br />
END AS relidxrefrelname, <br />
relfilenode, relkind, reltuples::bigint, relpages <br />
FROM pg_class pg, pg_namespace pgn WHERE pg.relnamespace = pgn.oid <br />
AND pgn.nspname NOT IN ('information_schema', 'pg_catalog') <br />
ORDER BY relpages DESC;<br />
</blockquote><br />
<br />
Sample output:<br />
<br />
<pre><br />
nspname | relname | size | refrelname | relidxrefrelname | relfilenode | relkind | reltuples | relpages <br />
----------+--------------------------------------------------------------+------------+----------------------------+----------------------------+-------------+---------+-----------+----------<br />
pg_toast | pg_toast_12633551 | 12 GB | error_email_collect | | 16113687 | t | 6506013 | 1532166<br />
public | log_email_sent | 4394 MB | pg_toast_12633624 | | 16112041 | r | 28645328 | 562416<br />
public | mail_log | 3260 MB | pg_toast_12633661 | | 16113649 | r | 13556149 | 417328<br />
public | error_email_collect | 1003 MB | pg_toast_12633551 | | 16113684 | r | 1161103 | 128345<br />
public | log_hash_reference | 789 MB | pg_toast_12633642 | | 18241270 | r | 7046247 | 100960<br />
public | email | 504 MB | pg_toast_12633482 | | 16111750 | r | 3673276 | 64568<br />
public | log_email_sent_log_id_idx | 492 MB | | | 16112048 | i | 28684712 | 62947<br />
public | log_email_sent_pkey | 492 MB | | | 16112047 | i | 28684712 | 62947<br />
public | idx_mail_log_date_created | 291 MB | | | 16113677 | i | 13563234 | 37192<br />
public | mail_log_pkey | 233 MB | | | 16113655 | i | 13563234 | 29765<br />
pg_toast | pg_toast_12633551_index | 139 MB | pg_toast_12633551 | error_email_collect | 16113689 | i | 6506013 | 17840<br />
</pre><br />
<br />
relidxrefrelname is the original table on which the toast table is based for an toast index<br />
<br />
More about admin functions regarding databasize and table size can be found in the [http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE System Admin Functions] under the Table 9-48.<br />
<br />
== Cancelling queries ==<br />
<br />
To gracefully cancel queries, one can use [http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE pg_cancel_backend()]. This should be safer than SIGTERMing the according process.<br />
<br />
= Accessing the Database =<br />
<br />
== psql and other CLI programs ==<br />
<br />
=== psql password prompts ===<br />
<br />
psql has, unlike the mysql client, no option to supply a password on the command line (which would be insecure). There are two solutions for this problem:<br />
<br />
* environment variables:<br />
export PGPASSWORD=password<br />
export PGUSER=username<br />
export PGHOST=host<br />
<br />
* a <tt>~/.pgpass</tt> file<br />
<br />
[http://www.postgresql.org/docs/current/static/libpq-pgpass.html pgpass Documentation]<br />
<br />
Format:<br />
hostname:port:database:username:password</div>Sts