Power

1
Power
psql
Selena Deckelmann
End Point Corporation
2
Thanks, Greg Sabino Mullane )
3
(No Transcript)
4
Nobodys Perfect
BATCH
5
Connecting
psql h yow p 5432 d cats U alanis
6
Connecting
7
Connecting
8
Connecting
hostportdatabaseusernamepassword 5432bu
cardoZippityDoDah bucardobucardoGoatInTheBo
x
.pgpass
9
Connecting
pg_service.conf
ironic hostyow dbnameherdocats useralanis pas
sword littl3p1ll
10
Connecting
psql serviceironic psql d serviceironic psq
l dbnameherdocats port5432
11
Connecting
Use pg_service.conf
Consistent ordering
Avoid shortcuts
12
HELP!
man psql psql -- help \? \h \h ALTER TABLE foo INT
13
Tab Completion
14
Tab Completion
15
Backslash
gt \d gt \dt gt \df gt \d ltthinggt gt \dg \du
\dr? gt \o
16
Prompt
PROMPT1
/R
PROMPT2
postgres
PROMPT3
17
Weak!
/R
18
\set PROMPT1 'n_at_mgt/Rx '
alanis_at_yow5432ironicgt
alanis_at_yow5432ironicgt SELECT
19
(No Transcript)
20
03313340mn_at_/R0330m
alanis_at_yow5432ironicgt
21
Variables
22
\set PROMPT2 PROMPT1
23
readline
ea"explain analyze"
24
.psqlrc
pslqrc
25
AUTOCOMMIT trick

alanis_at_yow5432ironicgt begin BEGIN alanis_at_yow5
432ironicgt delete from noise where id 123
DELETE 1 alanis_at_yow5432ironicgt
rollback ROLLBACK alanis_at_yow5432ironicgt
26
AUTOCOMMIT trick

alanis_at_yow5432ironicgt \set AUTOCOMMIT
off alanis_at_yow5432ironicgt delete from noise
where id 123 DELETE 1 alanis_at_yow5432ironicgt
alanis_at_yow5432ironicgt rollback ROLLBACK
27
AUTOCOMMIT trick

alanis_at_yow5432ironicgt begin BEGIN alanis_at_yow5
432ironicgt delete from noise where id 123
DELETE 1 alanis_at_yow5432ironicgt delete frm
noise where id 345 ERROR syntax error at or
near "frm" LINE 1 delete frm noise where id
145
alanis_at_yow5432ironicgt! select 123 ERROR
current transaction is aborted
28
AUTOCOMMIT trick

alanis_at_yow5432ironicgt \set ON_ERROR_ROLLBACK
on alanis_at_yow5432ironicgt begin BEGIN alanis_at_yo
w5432ironicgt delete frm noise where id 123
ERROR syntax error at or near
"frm" alanis_at_yow5432ironicgt
29
AUTOCOMMIT trick

alanis_at_yow5432ironicgt \set AUTOCOMMIT
off alanis_at_yow5432ironicgt \set ON_ERROR_STOP
on alanis_at_yow5432ironicgt select
123 123 alanis_at_yow5432ironicgt
30
AUTOCOMMIT trick

\set PROMPT1 'n_at_m/gtRx '
'n_at_m/gtRxxxxx'
alanis_at_yow5432ironicgt select
123 123 alanis_at_yow5432ironicgt
31
AUTOCOMMIT trick

\set PROMPT1 'n_at_m/gtRx '
'n_at_m/gtRxxxxx'
alanis_at_yow5432ironicgt select
123 123 alanis_at_yow5432ironicgt
32
AUTOCOMMIT trick

http//bucardo.org/check_postgres/
check_postgres_txn_idle --warning'5
seconds' --critical'60 seconds'
33
PAGING
34
Peeking inside
35
Quick modify
gt \t gt select 'DROP TABLE 'quote_ident(relname
)'' FROM pg_class WHERE relname 'testing_'
AND relkind'r' DROP TABLE testing_alpha DROP
TABLE "testing_one two three" gt \o
drop_testing_tables.sql gt \g gt \o gt \! less
drop_testing_tables.sql gt \set ON_ERROR_STOP
on gt BEGIN gt \i drop_testing_tables.sql
36
Batch and cron
Flags -A -X -q -t
37
Batch and cron
-AX qt
38
Batch and cron
PSQL'psql -AX -qt' /5 PSQL -d bucardo
-c "SELECT bucardo_purge_ delta('5
minutes'interval)"
39
Batch and cron
psql AX qt c 'SELECT pg_sleep(0)' wc 1 0
1
40
Batch and cron
PSQL'psql -AX -qt' /5 PSQL -d bucardo
-c "SELECT bucardo_purge_ delta('5
minutes'interval)" perl pe 's/\n// if .lt2'
41
Batch and cron
/5 (echo "COPY ark FROM STDIN" PSQL
d prod -c "COPY (SELECT FROM ark_audit) TO
STDOUT" echo "\\.") PSQL -d postgres -f -
42
The Future
\dT
\timing
rap
\dfS
\c
seq
\x09
ref
TABLE
kid
\l
-w
7.4
lttabgt
\ef
\d
You
help
43
Power
psql
Twitter
postgres_totd