Unix: Tar archive a list of files which are saved in a file

You could find the desired list of files by running the find command e.g.

find /path -type f -name "*.log" | grep -v SomeFiles | grep OtherFiles > mylist.txt

Then archive the list using the command below.

tar -cvf allfiles.tar -T mylist.txt
Read More

PostgreSQL: Simple script to output all functions from DB

#!/bin/bash

PSQLBIN=/usr/lib/postgresql/9.5/bin
DB=db_name
FUNCS=`$PSQLBIN/psql -d $DB -c "select proname FROM pg_catalog.pg_proc f where proname like 'process%';"`

for FUNC in $FUNCS ; do
        if [[ $FUNC =~ process.* ]] ; then
                echo `$PSQLBIN/psql -d $DB -c "SELECT pg_get_functiondef(f.oid) FROM pg_catalog.pg_proc f INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid) WHERE n.nspname = 'public' and f.proname='$FUNC';"` > /tmp/"$FUNC".sql

        fi
done
Read More

PostgreSQL – Check all tables and count rows

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;
Read More

PostgreSQL – find and kill open sessions

This select helps you find all the activity on the database.

select * from pg_stat_activity;

Use this select to kill all open sessions (except from the background own, the current session) for a specific database name.

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname like '%database%';
Read More

PostgreSQL – select all tables and all columns from the database

How to find all the table names in your schema:

select table_catalog, table_name, table_type from information_schema.tables where table_type like '%TABLE%' and table_name not like 'pg%' and table_name not like 'sql%' order by table_name asc;

How to find out all the columns and column types:

select table_name, column_name, data_type  from information_schema.columns col where table_name in (select table_name from information_schema.tables tab where table_type like '%TABLE%' and table_name not like 'pg%' and table_name not like 'sql%' order by table_name asc);
Read More

Postgres: Select all table names from schema

SELECT table_name
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE'
Read More

Unix: Find/replace in all files

find . -type f -name filename.* -exec sed -i 's/stringtosearch/stringtoreplacewith/g' {} \;
Read More

Putty – convert ppk to OpenSSH and use multiple keys

Open PuttyGen
Click Load
Load your private key
Go to Conversions->Export OpenSSH and export your private key
Copy your private key to ~/.ssh/id_dsa (or id_rsa).

Create the RFC 4716 version of the public key using ssh-keygen

ssh-keygen -e -f ~/.ssh/id_dsa > ~/.ssh/id_dsa_com.pub

Convert the RFC 4716 version of the public key to the OpenSSH format:

ssh-keygen -i -f ~/.ssh/id_dsa_com.pub > ~/.ssh/id_dsa.pub

So in each of the subfolders you will have id_rsa and id_rsa.pub.
Pointing to the Key

The only problem now, is that when ssh searches for a key, it won’t be able to find the one it’s looking for. You need to correctly edit your config file that should be located in your ~/.ssh/ folder. If it isn’t already there, just create a new file.

Mine looks like the following:

Host github.com
	User git
	Hostname github.com
	PreferredAuthentications publickey
	IdentityFile ~/.ssh/git/id_rsa
Host fedoraproject.org
	Hostname fedoraproject.org
	PreferredAuthentications publickey
	IdentityFile ~/.ssh/fedoraproject/id_rsa
Host fedorapeople.org
	Hostname fedorapeople.org
	PreferredAuthentications publickey
	IdentityFile ~/.ssh/fedoraproject/id_rsa
Read More

Linux: port forwarding using MobaXterm

ssh user@host -L localport:localhost:remoteport

e.g.

ssh guest@my-ip-address -L 5500:localhost:5500
Read More

Swift: Add basic HTTPAuth to NSURLSession

First you need to make sure that you use a different NSURLSessionConfiguration, where you will modify the headers.
The HTTP Auth needs to be like “Basic: base64 string”, the base64 string being the encoding of username:password.

let userPasswordString = "\(email):\(password)"
            let userPasswordData = userPasswordString.dataUsingEncoding(NSUTF8StringEncoding)
            let base64EncodedCredential = userPasswordData!.base64EncodedStringWithOptions(nil)
            let authString = "Basic \(base64EncodedCredential)"
            
            sessionConfiguration.HTTPAdditionalHeaders = ["Authorization" : authString]
Read More