Juan Pedro Pérez Alcántara malkab

## postgresql-sql_scripts_variables.sql
/**

  Usage and expansion of variables in SQL scripts to be executed with psql \i command.

*/

-- Expansion with quotes as varchar variables example
\set analysis_id a

delete from warehouse.analysis_tasks where analysis_id = :'analysis_id';

## git-branches.sh
# Fetch remote branches info
git fetch -av

# Check a branch from a remote and track it
git checkout -t origin/remote-branch-name

# To change the name of the local branch on checkout (not recommended)
git checkout -b local-branch-name origin/remote-branch-name

# Delete remote branches (and their local counterpart)

## postgresql_general_management_recipes.sql
/**

  Check number of connections to the DB.

*/
SELECT count(distinct(numbackends)) FROM pg_stat_database;

## ssh-recipes.md

      
              2 files
            
          
              0 forks
            
          
              0 comments
            
          
              0 stars
            
          
                malkab
                / ssh-recipes.md
            
            
              Last active
              May 19, 2022 07:42
            
              
                SSH - Recipes
              
          
    Recipes for SSH

SSH bridge using a in-the-middle machine

The situation:

A_HOST is a machine that has access to another machine, T_HOST, where the target resource is. For example, a machine able to open a privileged VPN to the resource's machine that B_HOST is unable to open;
B_HOST is the machine that is unable to open a connection to the T_HOST where the target resource is;
T_HOST is the machine that has the target resource on port T_PORT;
T_PORT is the port where the target host T_HOST publish the resource;


## postgresql-materialized_views.sql
-- Refresh a materialized view
REFRESH MATERIALIZED VIEW whatever;

## dvc.md

      
              1 file
            
          
              0 forks
            
          
              0 comments
            
          
              0 stars
            
          
                malkab
                / dvc.md
            
            
              Last active
              August 31, 2021 10:34
            
              
                DVC (Data Version Control)
              
          
    DVC (Data Version Control)

A Git for Big Data. Use DVC for storing big data assets as the perfect companion to Git for Big Data projects.
Project homepage: https://dvc.org/
Remotes

We currently SSH remotes. At the development machine where the folder used as repository is located, the .dvc/config has this structure:

  
## postgresql-set_up_database-example.sql
\c postgres

\set search_path public

/**

  Create roles. This is a group to make things easier.

*/
create user researcher with nosuperuser nocreatedb nocreaterole nologin noreplication;

## tar.sh
# TAR
tar -cvf archive.tar folder
tar -xvf archive.tar -C folder_to_deploy --strip 1
tar -tvf archive.tar

# BZ2
tar -jcvf archive.tar.bz2 folder
tar -jxvf archive.tar.bz2 -C folder_to_deploy --strip 1
tar -jtvf archive.tar.bz2

## postgresql-functions.sql
/**
 *
 * A Pl/PgSql function example.
 *
 */
create or replace function public.gs__split_qualified_name(
  _name text
) returns public.gs__qualified_name as
$$
declare

## postgresql-query-based_updates.sql
/**
 *
 * Example 0.
 *
 */
update trash.arqueta_point b set "Text"=a.text
from
(
  select
    a.gid as gid,
	/**

	Usage and expansion of variables in SQL scripts to be executed with psql \i command.

	*/

	-- Expansion with quotes as varchar variables example
	\set analysis_id a

	delete from warehouse.analysis_tasks where analysis_id = :'analysis_id';
	# Fetch remote branches info
	git fetch -av

	# Check a branch from a remote and track it
	git checkout -t origin/remote-branch-name

	# To change the name of the local branch on checkout (not recommended)
	git checkout -b local-branch-name origin/remote-branch-name

	# Delete remote branches (and their local counterpart)
	/**

	Check number of connections to the DB.

	*/
	SELECT count(distinct(numbackends)) FROM pg_stat_database;
	-- Refresh a materialized view
	REFRESH MATERIALIZED VIEW whatever;
	\c postgres

	\set search_path public

	/**

	Create roles. This is a group to make things easier.

	*/
	create user researcher with nosuperuser nocreatedb nocreaterole nologin noreplication;
	# TAR
	tar -cvf archive.tar folder
	tar -xvf archive.tar -C folder_to_deploy --strip 1
	tar -tvf archive.tar

	# BZ2
	tar -jcvf archive.tar.bz2 folder
	tar -jxvf archive.tar.bz2 -C folder_to_deploy --strip 1
	tar -jtvf archive.tar.bz2
	/**
	*
	* A Pl/PgSql function example.
	*
	*/
	create or replace function public.gs__split_qualified_name(
	_name text
	) returns public.gs__qualified_name as
	$$
	declare
	/**
	*
	* Example 0.
	*
	*/
	update trash.arqueta_point b set "Text"=a.text
	from
	(
	select
	a.gid as gid,