DB Notes

 When we need to connect somewhere via cron job, batch script etc. , .pgpass file can be really useful for authorization. Connection information is saved within the .pgpass file, allowing us to connect to database without password prompts. General text format: host:port:db_name:user_name:password You can also put asterisk(*) to first 3 parameters to match anything. Example .pgpass file content: 192.168.1.40:5432:postgres:myadmin:On3GoodP@ssW0rD Running commands to activate for pg_basebackup (Linux Environment): $ echo "192.168.1.40:5432:*:rep_user:V3ryStr0ngP4ss" > /var/lib/pgsql/.pgpass $ chown postgres.postgres /var/lib/pgsql/.pgpass $ chmod 0600 /var/lib/pgsql/.pgpass $ su - postgres $ /usr/pgsql-14/bin/pg_basebackup -h 192.168.1.40 -D /pgdata/14/data/ -U rep_user -p 5432 -v -P --wall-method=stream --write-recovery-conf

 We can run our scripts in background & write output to a log file as below: nohup sh myscript.sh > myscript.log 2>&1 & disown That will create a separate process that runs the script, and you can tail the log for output & errors.

 Sometimes dummy and hard-coded vales might be needed for testing or some other purposes. We can return single rows easily with "select ...... from dual". But if we want several rows, special functions might be useful for it since "unioning" rows are not really useful and simple. Simple example is below: select column_value "mail" from table(sys.odcivarchar2list('alex@mymail.com','sally@mymail.com','campbell@mymail.com'); This will return a column with three rows as intended.

 Sometimes we may need to find some similar data (not exact one) regarding to our needs. For example, one digit of the phone number may have been registered incorrectly.So how can we find it?  From the classical point of view, we need to combine all possibilities. But we don't have to. But RDBMS' have some solutions for it. - For Oracle, you can use util_match.EDIT_DISTANCE function. Here is an example: select name,surname,utl_match.EDIT_DISTANCE('5822182911',phone) from HR.PERSONEL where utl_match.EDIT_DISTANCE('5822182911',phone)<=2 This looks for phone records that has only 2 different numbers (in the right order of course) from the original value (5822182911). Some other kind of functions are available at Oracle, according to your needs. - PostgreSQL has also a useful extension, named fuzzystrmatch. It also has some useful functions for detecting similar records. An example is below: SELECT levenshtein('FRANCE', 'FRANCOL'); --This returns ...

            It could be tricky to detect when your database slows down for no reason. At first, you should check system resources (CPU - RAM usage, network etc.) and if find some bottlenecks, then go deeper. ADDM report should be useful for detecting most problem causes.           But it is also probable that some queries may be using server resources excessively. How to detect them? Well, there is one simple query to check that: select s.sql_text,sl.sid,sl.target||'-'||sl.opname Target,sl.totalwork,sl.sofar,sl.time_remaining Seconds_remaining,sl.elapsed_seconds,sl.sql_id,sl.username from v$session_longops sl,v$sql s,v$session se where s1.sid=se.sid and se.sql_id=s.sql_id and totalwork!=0 and sofar<>totalwork;

     In general, people find dumping their whole databases in sever by pg_dumpall easily. But it takes too long since this tool is not utilized for using resources efficiently. Thus, it's essential to uncompress and parallelize jobs while taking dumps in PostgreSQL databases via pg_dump. Example command:                pg_dump -Z0 -j 8 -Fd mydb -f dump_folder -Z0 means "no compress" and -j 8 means use 8 cores. When using pg_restore, similar approach is applied to maximize performance. pg_restore -Fd -O -j 8 -d mydb dump_folder               

1. Check Required Packages Ensure the yum packages openssl and pigz are installed on your system. 2. Compress and Encrypt Files Use the following command to compress and encrypt files: tar -c -I pigz backup.dmp | openssl enc -aes-256-cbc -e -k mypassword > backup.tar.gz.enc 3. Decrypt and Decompress Files Use the following command to decrypt and decompress files: openssl enc -aes-256-cbc -d -in backup.tar.gz | tar -I pigz -x