Re: [Bacula-users] needed tapes

[Stefan G. W. <li...@xu...>]

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 ...