How to analyze PostgreSQL logs?
Sometimes you need to analyze what actually is hitting your database. It could be due to poor performance or just maybe for improvement purposes. Whatever is the reason, here are the options that I enable to achieve this:
Edit postgresql.conf file and enable the following options:
log_destination = ’stderr’
redirect_stderr = on
log_directory = ‘pg_log’
log_filename = ‘postgresql-%Y-%m-%d.log’
client_min_messages = notice
log_min_messages [...]
Keyboard shortcuts in interactive psql
psql = PostgreSQL interactive terminal
I’ve read this article (http://www.depesz.com/index.php/2008/03/30/keyboard-shortcuts-in-psql) today and decided to try on my MacBook.
Interestingly, all shortcuts were working fine in normal shell, but no action as soon as run “psql db_name” and go into interactive psql prompt.
After searching google, found out that psql on Mac OS was [...]
Read/write activity on tables – PostgreSQL
If you ever wanted to know which tables are actively being hit on your database, please pay attention to: http://www.postgresql.org/docs/8.1/static/monitoring-stats.html
postgres@server:~$ psql testdb -c “select relname, idx_tup_fetch as seeks, n_tup_ins + n_tup_upd + n_tup_del as writes from pg_stat_user_tables order by writes desc limit 5;”
relname | seeks | writes
——————+———+——–
user [...]
How big is PostgreSQL database size?
Today found another very useful info from http://www.planetpostgresql.org/
tempdb=# SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;
And this is what it shows:
datname | size
——————+———
postgres | 3480 kB
jasperserver | 82 MB
testdb | 3480 kB
template1 | 3537 kB
template0 | 3480 kB
tempdb | 127 MB
(6 rows)
Time: 143.405 ms
PostgreSQL, copy from a file as non-superuser
testdb=> COPY weather FROM ‘/tmp/input_data.txt’;
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql’s \copy command also works for anyone.
So, how do we copy?
there is more than a way to skin a cat…
CREATE TABLE table1 (
code char(5),
name char(10)
);
synack@deimos db $ cat /tmp/input_data.txt
1,kamchi
2,mahabat
3,kamila
postgres@deimos [...]
postgres prompt
Source: http://www.planetpostgresql.org/
When you drop into postgresql promt this is how the standard prompt looks like:
postgres@deimos ~ $ psql database_name
Welcome to psql 8.0.13, the PostgreSQL interactive terminal.
Type: copyright for distribution terms
h for help with SQL commands
? for help with psql commands
[...]
bash hacks with postgres
I’ve got quite a number of different bash scripts which will accept command-line arguments to do various stuff… I needed to apply same logic to get some data pulled-out from PostgreSQL.
So, what we have here is basically an input file which will have one record per line ( employee_id), these lines will be used as [...]