psql variables – quoting and more
psql, postgreSQL powerful command line interface, has it's variables system. I do use it sometimes but... how many single quotes should I put there?
I have puzzled over this so many times, that I decided to put it here as a memo.
Proper quoting of text variables
Variable declaration:
\set d1 '''2008-01-01 00:00:00.000'''
\set d2 '''2008-12-31 23:59:59.999'''
Variable usage:
SELECT something FROM somewhere
WHERE now() BETWEEN :d1 AND :d2;
To make this post a bit more useful, here are some other
examples of psql variables usage
A shortcut for frequently used SQL phrase:
\set cnt 'SELECT COUNT(*) FROM'
:cnt users;
Another one:
\set csn 'SELECT * FROM DBA.CLIENTS WHERE name LIKE'
\set csni 'SELECT * FROM DBA.CLIENTS WHERE name ILIKE'
:csni 'kowalski'\g all_kowalskis.txt
This one if for pretty-ascii-report-printout lovers:
\set hr **************************************
\set inform '\\echo :hr \\echo :msg \\echo :hr'
Usage of the above:
\set msg 'starting mass update\nat ' `date`
:inform
**************************************
starting mass update
at Fri Dec 19 21:15:49 CET 2008
**************************************
Nice, eh?
You can put such "macros" into your .psqlrc file to save some keystrokes.
You can even create your very own "library" of psql macros and include it in your scripts with \i.
Have fun!
Note: these are not variables in SQL stored procedures sense. This is just plain text substitution.
A bit similar to Host Variables (note :colon usage), but not so powerful.
Replikacja w PostgreSQL za pomocą Slony-I – update
W poprzednim artykule opisałem jak zestawić replikację w postgresie za pomocą slon-tools.
Dziś chciałem dodać parę rzeczy których tam zabrakło, a które są często potrzebne w praktycznym zastosowaniu Slony-I.
Dodawanie tabeli do replikacji
Załóżmy że chcemy dodać do replikacji tabelę froobles.
- Tabela musi istnieć na obu serwerach i mieć klucz główny. Możemy utworzyć ją ręcznie, lub wykorzystać skrypt który to zrobi:
slonik_execute_script set1 /path/to/froobles.sql
- W konfigu (plik slon_tools.conf) musimy dodać kolejną publikację (set) Slony-I. Dopisujemy go do hasha $SLONY_SETS.
$SLONY_SETS = { set1 => { set_id => 1, table_id => 1, sequence_id => 1, pkeyedtables => [ 'accounts', 'branches', 'tellers', ], }, # tymczasowy set dla dodawanych tabel set2 => { set_id => 2, table_id => 100, sequence_id => 100, pkeyedtables => [ 'froobles', ], }, }; 1; -
Następnie musimy utworzyć publikację w klastrze.
postgres@merry:~$ slonik_create_set set2 | slonik
-
Utoworzoną publikację (set #2) musimy zasubskrybować do slave'a (node #2):
postgres@merry:~$ slonik_subscribe_set set2 2 | slonik
-
Zawartość tabeli zostanie przekopiowana na serwer slave. Może to potrwać zależnie od wielkości tabeli.
-
Gdy już upewniliśmy się że tabela jest replikowana, możemy złączyć publikacje z powrotem w jedną. Służy do tego polecenie slonik_merge_sets:
postgres@merry:~$ slonik_merge_sets 1 set1 set2 | slonik
-
Uaktualniamy plik slon_tools.conf (patrz wyżej) tak by odzwierciedlał aktualną konfigurację klastra. Nowo dodane tabele powinny się znaleźć w "set1". Robimy to aby uniknąć zamieszania w razie konieczności odtworzenia klastra.
$SLONY_SETS = { set1 => { set_id => 1, table_id => 1, sequence_id => 1, pkeyedtables => [ 'accounts', 'branches', 'tellers', 'froobles', ], }, }; 1;
Usuwanie tabeli z replikacji
Jeśli chcemy dodawać, to pewnie czasem i usuwać.
Przykład: Usuwamy tabelę z publikacji nr 1. Musimy znać też ID tabeli nadane przez slonika; możemy je podejrzeć w bazie w tabeli _webdev.sl_table. W tym przykładzie jest to 100.
postgres@merry:~$ slonik_drop_table 100 set1 | slonik
Podsumowanie
Narzędzia slon-tools są naprawdę proste.
Wszystkie skrypty dostępne w pakiecie rozpoczynają się od prefiksu slonik_ lub slon_. Polecam zapoznanie się z nimi oraz z doumentacją Slony-I dostępną pod adresem http://slony.info.
Powodzenia!
zliczanie błędów w logach postgresa
Kilka razy mi się przydało więc zapisuję...
for distance in {30..0}; do
date=`date +%Y-%m-%d --date "$distance days ago"`;
echo -n "$date ";
bzcat postgresql-$date*.bz2 |grep -wc ERROR;
done
Wersja ze zliczaniem nie tylko ERROR-ów:
for distance in {20..1}; do
date=`date +%Y-%m-%d --date "$distance days ago"`;
echo -n "$date ";
lzcat postgresql-$date*.lzma \
| perl -nle '$s{$1}++ if /\b(PANIC|FATAL|ERROR|WARNING|NOTICE)\b/; END{for(keys%s){$o.="$_:$s{$_};"};print $o}';
done
column number as column alias in postgres
I have seen such queries many times:
SELECT foo, bar, (some long and complicated SQL subexpression), count(*)
FROM somewhere
GROUP BY foo,bar, (some long and complicated SQL subexpression)
ORDER BY count(*) DESC
This has some drawbacks:
- you have to repeat exactly the same expression two times, which makes query longer
- it is easier to make a mistake while editing the subexpression
Did you know there's a neat shortcut for this in postgres?
SELECT foo, bar, (some long and complicated SQL subexpression), count(*)
FROM somewhere
GROUP BY 1,2,3
ORDER BY 4 DESC
This is known as column number aliases.
I have no idea if this comes from SQL standard or not.
But it saves me quite a lot of typing :)
I know it works at least in postgresql, mysql and informix.
Does it work in your database, too?
