Am 20.03.25 um 15:59 schrieb Rob Gerber:
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;
just a quick reply:
**thanks** a lot for all the help, I have to catch up still and will
read that next week when things calm down hopefully.
have a nice weekend all ...
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
