i wanted to know how many transactions per second is my machine processing.
how to do so? a simple select to pg_stat_database will do the job (actually 2 selects 🙂
but since i have to write it anyway, perhaps i can/should make it so it will print the current value continuously?
and, while i'm at it, some kind of graph wouldn't be bad 🙂
faber4 on irc asked about how to get ascii-based sorting, while his postgresql was initdb'ed with utf-8 based locale (en_US.UTF-8 to be exact).
what can we do about it?
some time ago merlin moncure wrote about one of new features of postgresql 8.2 – row-wise comparison.
i also read about it, but at first didn't find anything very useful about it – after all it doesn't give you any new functionality. any kind “(a, b, c) > (…)" can be written with standard column-based operators. so it's basically just a syntactic-sugar.
that's true. but merlin pointed me to not-so-obvious benefit – basically it allows much better paging of results. how? let's see.
luckymurali_81 on freenodes #postgresql had a problem. his query returns data in wrong order. what can we do about it?
let's imagine simple situation – you have postgresql server. configuration was fine-tuned, hardware is ok. yet the system is not really as fast as it should.
most common problem – slow queries.
second most common problem – fast queries, but too many of them. for example – i once saw a system which did something like this:
reason? very “interesting" orm.
now i'll show you how i deal with these kind of situations 🙂
now, some time ago i wrote about how to “hack" system with postgresql. today i'll write more about how to make pg database as safe as possible.
i'll go through several steps, tell you what's possible, what's easy, and what is not so easy 🙂
so, let's start the tutorial.
Continue reading securing your postgresql database
one of the nice (for some things) features of oracle is rownum() function.
i assume you know what it does – if you don't – i think a quick google will show it.
today i will show how to make rownum in postgresql. if you're not interested in rownum itself – please continue reading anyway, as some functions shown here have other uses as well 🙂
Continue reading rownum anyone? cumulative sum in one query?
< french translation is now available >
very recently i got a task which involved finding out what happened (what sql queries were called) on remote machine.
the problem was that i didn't have any kind of shell access to server – i could connect only to postgresql. the good side was that i had a superuser-level access.
so the task was quite simple – how to find and read a file from psql. on remote machine. with no access to ssh, ftp, nor anything like this.
this is when i got idea that i'll write more about it. some might say that you shouldn't write this kind of information. my view is that i'm not disclosing any secrets – i will be using only basic (remote) psql with superuser access. all things that i will describe in here are in documentation – you just have to know where to look for them.
i tend to use more (than standard) informative prompts.
whether it's shell, or psql i set it up to match my needs.
for the longest time i used psql prompt:
\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] %[%012%]%x%# '
(this line should be in .psqlrc).
it gives some information:
(depesz@[local]:5810) 15:26:06 [depesz] #
at a glance i know what user i am conencted as, to which machine, over which port, what database i'm connected to, and what's current time.
unfortunately this prompt had one issue – whenever i used history, and then something like <ctrl-a> (go to begining of line) something weird happened – cursor got distorted, and it looked like it was on second letter, while in fact it was on first (hard to explain, try it for yourself :).
it was painful, but since i couldn't do anything about it, i just learned to live with it.
now, due to some new environment setup, i decided that i've got enough of it. i tried to find any help in manual (i was looking for something like \[ and \] from bash prompting), but to no avail.
i did find out that the problem lies in %[012%]. i tried to give there literal <enter> (thus making \set statement multi-line), but it didn't work.
and then i simply gave there standard “\n". and it was it – now i have the prompt the way i like it, with no side effects on command line edition.
final prompt1, in case you're interested:
\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%# '
this is nothing new, but i blog it to have a place to point people to.
let's assume you accidentally loaded dump file in template1 database.
this is definitely not something one could have wanted (i mean i can see some uses for this but it's not really likely).
so, now you want to cleanup your template1.
how? that's easy.
connect with your superuser account (postgres) to some database other than template1.
and then issue these queries:
be sure that there is no connection to template1 at the moment you're dealing with it.
and that's all. nothing really complicated.