Stefan,
Here is some information to assist with crafting postgres sql queries. The
postgres psql shell provides tools to look up tables and their columns so
you know what keywords to specify in your queries. You can also easily test
queries and get feedback. psql will tell you when you've made a syntax
error. Imagine if we were trying to write a shell script to work with
files, without access to a shell and tools like ls or find. This is the
position we are in if we are trying to craft a query without the ability to
look up tables and the columns inside them.
Once you have a working query, I recommend that you use bconsole query or
bconsole sql commands to make those queries. This is more portable and
likely safer. psql can be dangerous.
First, safety:
I recommend that you take a bacula catalog backup, and then restore it to a
safe place BEFORE you do any work in psql. Just in case of some accident or
error that damages the catalog. psql gives you the option to enter commands
that could be destructive. I don't expect that simple queries in psql will
damage the bacula database, but caution is wise.
You will want to use this command to launch psql:
psql -h 127.0.0.1 -U bacula -W
You will need to enter your bacula postgres database password after hitting
enter. If you don't know it, reply and I'll help you get it reset.
You should be dumped to a 'bacula=>' prompt.
Some notes:
psql allows you to enter multiple lines of query text. It only executes
your query when you type a semicolon (;) and hit enter, either at the end
of the last line, or on its own line.
Clear the query buffer with \r. This may be useful to abort the previous
query, and start a new one. You might run into errors related to malformed
queries when you didn't even know any query text was in the buffer.
You can cancel a running query with ctrl + c.
If you hit the up arrow key you can edit the previous query (hold left
arrow to go up to previous lines).
In psql it is not necessary to end lines with a / in order to continue to
the next line.
Commands to display some information or take some action are prefixed with
\ (NOT /).
Here are my notes from when I experimented with psql. These notes are
centered around a select statement that looks up file entries with certain
filenames or hashes, but the methods are the same regardless of which query
you use.
/* multi-line
comment */
-- single line comment
-- get help (doesn't appear to be context sensitive)
\?
-- clear the query buffer
-- useful to prevent fouling up a pasted query with some previously entered
query text.
\r
/*
list all tables
note that slash commands with inline comments after them will parse the
comments, whether you want them to or not.
*/
\dt
-- list columns for a given table
\d tablename
/*
Find all entries for a specific filename.
Note that this could find different files with the same name. Verify
they're all at the same pathid to confirm if they're actually the same
file.
*/
SELECT * FROM file WHERE filename = 'bacula-dir.conf'; --queries with
inline comments are fine, though
-- find entries with the same filename and pathid, but more than one hash.
SELECT
filename,
pathid,
COUNT(DISTINCT md5) AS distinct_hashes -- the column md5 should more
accurately be named 'hashes' since it can hold a hash value from any
algorithm, but here we are.
FROM
file
WHERE
filename ILIKE '%.conf' -- % is the wildcard character. This example
is equivalent to '*.conf'
-- ILIKE means 'case
insensitive LIKE'
GROUP BY
filename, pathid
HAVING
COUNT(DISTINCT md5) > 1; -- end your queries with a semicolon.
/*
Here are a couple methods to output to a designated file, overwriting any
contents
this is handy if the output you need is really wide and you don't want it
to be wrapped by the terminal.
I ran into issues when manually unwrapping output because sometimes there
was a whitespace on the intersection between two fields.
The trailing whitespace was trimmed by the terminal, so when I backspaced
line 2 up to connect it to the end of line 1,
I accidentally concatenated two values together incorrectly.
Basically, without understanding exactly what it's doing and manually
correcting issues like that,
unwrapping wrapped output from the shell is risky.
*/
*/
Any queries entered between the \o and \o will be dumped to the file
designated
If you put the output filename in double quotes it will erroneously
complain that the file doesn't exist
You have to paste in or type the first \o statement, then hit enter.
* Pasting in the whole block at once doesn't work!**/
\o '/tmp/outputfile.txt'
SELECT * FROM file WHERE filename = 'bacula-dir.conf';
\o
-- end the session with another \o
/*
This takes query output and dumps it to a csv file. no extraneous spaces or
other text-format padding.
*/
\COPY (SELECT * FROM file WHERE filename ILIKE '%.conf') TO
'/tmp/csvOutputFile.csv' WITH CSV HEADER;
Regards,
Robert Gerber
402-237-8692
r...@craeon.net
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
