PSQL, \! . , Windows, . Psql, \!
\watch PSQL, PostgreSQL 9.3. , SQL , . , , , . , 3-3.
3-3. 10
SELECT datname, waiting, query
FROM pg_stat_activity
WHERE state = 'active' AND pid!= pg_backend_pid(); \watch 10
\watch , . 3-4, .
3-4.
SELECT * INTO log_activity FROM pg_stat_activity; - .
INSERT INTO log_activity SELECT * FROM pg_stat_activity; \watch 5 -
, , CTRL-X CTRL-C. , PSQL.
Michael Paquier: PSQL.
PSQL . 3-5 , , pg_catalog pg_t, .
3-5. Table \dt+
\dt+ pg_catalog.pg_t*
Schema | Name | Type | Owner | Size | Description
-----------+------------------+-------+----------+--------+-------------
pg_catalog | pg_tablespace | table | postgres | 40 kB |
pg_catalog | pg_trigger | table | postgres | 16 kB |
pg_catalog | pg_ts_config | table | postgres | 40 kB |
pg_catalog | pg_ts_config_map | table | postgres | 48 kB |
pg_catalog | pg_ts_dict | table | postgres | 40 kB |
pg_catalog | pg_ts_parser | table | postgres | 40 kB |
pg_catalog | pg_ts_template | table | postgres | 40 kB |
pg_catalog | pg_type | table | postgres | 112 kB |
, , pg_ts_config, \ d +, 3-6.
3-6. \d+
\d+ pg_ts_dict
Table "pg_catalog.pg_ts_dict"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+------+-----------+----------+--------------+-------------
dictname | name | not null | plain | |
dictnamespace | oid | not null | plain | |
dictowner | oid | not null | plain | |
dicttemplate | oid | not null | plain | |
dictinitoption | text | | extended | |
psql Gems | 51
Indexes:
"pg_ts_dict_dictname_index" UNIQUE, btree (dictname, dictnamespace)
"pg_ts_dict_oid_index" UNIQUE, btree (oid)
Has OIDs: yes
|
|
|
PSQL \copy(), . , . . , Fact Finder, . , DEC_10_SF1_QTH1_with_ann.csv, PostgreSQL.
psql
, , . - , , . , .
PostgreSQL, . . , PSQL . PSQL ; , . , , , , . , , , , .
Psql 3-7 Psql .
3-7. PSQL
\connect postgresql_book
\cd /postgresql_book/ch03
\copy staging.factfinder_import FROM DEC_10_SF1_QTH1_with_ann.csv CSV
3-7, Psql, , \cd, , \copy (). , CSV, , Psql .
, , :
\copy sometable FROM somefile.txt DELIMITER '|';
- , NULL AS:
\copy sometable FROM somefile.txt NULL As '';
\copy() PSQL SQL. PSQL , . SQL Postgres OS. , . PostgreSQL PSQL.
PSQL
, . . \copy () Psql . 3-8, , , .
3-8. PSQL
\connect postgresql_book
\copy (SELECT * FROM staging.factfinder_import WHERE s01 ~ E'^[0-9]+') TO '/
test.tab'
WITH DELIMITER E'\t' CSV HEADER
|
|
|
. , .
CSV ( 3-9).
3-9. PSQL
\connect postgresql_book
\copy staging.factfinder_import TO '/test.csv' WITH CSV HEADER QUOTE '"' FORCE
QUOTE *
FORCE QUOTE * , . , , PSQL .
Copy from/
PostgreSQL 9.3, PSQL , , LS, Wget- . 3-10 .
3-10. PSQL
\connect postgresql_book
CREATE TABLE dir_list (filename text);
\copy dir_list FROM PROGRAM 'dir C:\projects /b'
Hubert Lubaczewski \copy. Depesz: "" .
, PSQL HTML. HTML, 3-1.
psql -d postgresql_book -H -c
"SELECT category, count(*) As num_per_cat
FROM pg_settings
WHERE category LIKE '%Query%'
GROUP BY category
ORDER BY category;" -o test.html
3-1. HTML
. HTML-, HTML . , , 3-11.
3-11. settings_report.psql
\o settings_report.html
\T 'cellspacing=0 cellpadding=0'
\qecho '<html><head><style>H2{color:maroon}</style>'
\qecho '<title>PostgreSQL Settings</title></head><body>'
\qecho '<table><tr valign=''top''><td><h2>Planner Settings</h2>'
\x on
\t on
\pset format html
SELECT category, string_agg(name || '=' || setting, E'\n' ORDER BY name) As settings
FROM pg_settings
WHERE category LIKE '%Planner%'
GROUP BY category
ORDER BY category;
\H
\qecho '</td><td><h2>File Locations</h2>'
\x off
\t on
\pset format html
SELECT name, setting FROM pg_settings WHERE category = 'File Locations' ORDER BY
name;
\qecho '<h2>Memory Settings</h2>'
SELECT name, setting, unit FROM pg_settings WHERE category ILIKE '%memory%' ORDER
BY name;
\qecho '</td></tr></table>'
\qecho '</body></html>'
\o
file.
html .
HTML.
Expand. .
HTML.
string_agg (), PostgreSQL 9.0 .
. .
. .
3-11 , SQL PSQL, . 3-11 PSQL \i settings_report.psql, Psql -f settings_report.psql OS. , settings_report.html 3-2.
3-2. HTML
, pgAgent crontab.
