use ftp and tar in shell script

ftp -in 192.168.1.2 << _EOF
quote user tester
quote pass tester
cd /download_folder
lcd /upload_folder
put | "tar -cjvf - test.data" test.data.tar.bz2
bye
_EOF

PostgreSQL check DB, Tablespace, Table, Index Size

PostgreSQL有內建的function可以view各種的Size

--check the tablespace size

SELECT spcname,

pg_size_pretty(pg_tablespace_size(spcname)) as size,

pg_tablespace_size(spcname) as for_sort

FROM pg_tablespace

ORDER BY pg_tablespace_size(spcname) DESC;

--check the database size

SELECT datname,

pg_size_pretty(pg_database_size(datname)) as size,

pg_database_size(datname) as for_sort

FROM pg_database

ORDER BY pg_database_size(datname) DESC;

--check the object size

SELECT relname,reltuples,

pg_size_pretty(pg_relation_size(oid)) as size,

pg_relation_size(oid) as for_sort

FROM pg_class

WHERE relkind IN ('r') --public table

ORDER BY pg_relation_size(oid) DESC;

--check table include index size

SELECT relname,reltuples,

pg_size_pretty(pg_total_relation_size(oid)) as size,

pg_total_relation_size(oid) as for_sort

FROM pg_class

WHERE relkind IN ('r') --public table

ORDER BY pg_total_relation_size(oid) DESC;

Postgresql 如何一次清掉所有的view, trigger及function

Postgresql有個麻煩的地方，若view, trigger或function綁著table，就無法在table上作新增欄位的動作，更麻煩的是view綁view時，上一層的view要更新就一定要先drop 下一層的view，不像Oracle這麼方便，因此若是上新版本的Database Schema時，需將view,trigger, function清掉，因此寫了一個shell script來作這個動作

filename: dropDBV.sh

usage ()

{

echo "

$0 version 0.9

put the description here

SYNOPSIS

$0 [ --help ] [ -h PGHOST ] [ -p PGPORT ] [ -U PGUSER ] [-d PGDATABASE]

DESCRITPTION

This script use to clean dbaegis History data

for example:

$0 -d cobra

$0 -h ora2 -p 5432 -U postgres -d cobra

[OPTIONS]

-h PGHOST DATABASE server host name or IP address

-p PGPORT DATABASE server port

-U PGUSER DATABASE USER

-d TARGET_DB DATABASE NAME

-v VERBOSE Purge Data use Verbose Mode

"

exit 0

}

#drop view

sqlDropView="

SELECT 'drop view if exists '||pc.relname||' cascade;'

FROM pg_class pc,

pg_namespace pn

WHERE pc.relkind = 'v'

AND pn.nspname = 'public'

AND pn.oid = pc.relnamespace

order by relname

"

#Trigger

sqlDropTrg="

SELECT 'drop trigger if exists '||tgname||' on '||tbl.relname||' cascade;'

from pg_trigger trg, pg_class tbl, pg_proc src, pg_language l,pg_namespace pn

where trg.tgrelid = tbl.oid

AND l.oid = src.prolang

AND pn.nspname = 'public'

AND pn.oid = tbl.relnamespace

AND trg.tgfoid = src.oid

order by tgname

"

#Drop Function

sqlDropFun="

SELECT 'drop function if exists '||

p.proname||'('||

coalesce(case when pt01.typname::varchar is not null then ''||pt01.typname::varchar end,'')||

coalesce(case when pt02.typname::varchar is not null then ','||pt02.typname::varchar end,'')||

coalesce(case when pt03.typname::varchar is not null then ','||pt03.typname::varchar end,'')||

coalesce(case when pt04.typname::varchar is not null then ','||pt04.typname::varchar end,'')||

coalesce(case when pt05.typname::varchar is not null then ','||pt05.typname::varchar end,'')||

coalesce(case when pt06.typname::varchar is not null then ','||pt06.typname::varchar end,'')||

coalesce(case when pt07.typname::varchar is not null then ','||pt07.typname::varchar end,'')||

coalesce(case when pt08.typname::varchar is not null then ','||pt08.typname::varchar end,'')||

coalesce(case when pt09.typname::varchar is not null then ','||pt09.typname::varchar end,'')||

coalesce(case when pt0a.typname::varchar is not null then ','||pt0a.typname::varchar end,'')||

') cascade;' as args

FROM pg_catalog.PG_PROC p

LEFT JOIN pg_type pt01 on pt01.oid = p.proargtypes[0]

LEFT JOIN pg_type pt02 on pt02.oid = p.proargtypes[1]

LEFT JOIN pg_type pt03 on pt03.oid = p.proargtypes[2]

LEFT JOIN pg_type pt04 on pt04.oid = p.proargtypes[3]

LEFT JOIN pg_type pt05 on pt05.oid = p.proargtypes[4]

LEFT JOIN pg_type pt06 on pt06.oid = p.proargtypes[5]

LEFT JOIN pg_type pt07 on pt07.oid = p.proargtypes[6]

LEFT JOIN pg_type pt08 on pt08.oid = p.proargtypes[7]

LEFT JOIN pg_type pt09 on pt09.oid = p.proargtypes[8]

LEFT JOIN pg_type pt0a on pt0a.oid = p.proargtypes[9]

LEFT JOIN PG_LANGUAGE l ON l.OID = p.prolang ,

pg_namespace pn

WHERE NOT p.proisagg

AND pg_catalog.pg_function_is_visible(p.OID)

AND pn.nspname = 'public'

AND pn.oid = p.pronamespace

-- and p.proname = 'dbaegis_string_apart_id'

AND l.lanname = 'plpgsql'

order by p.proname

"

run_f ()

{

cmdDropView=`psql -t -q ${cmd} -c "${sqlDropView}"`

echo "${cmdDropView}"

psql ${cmd} -c "${cmdDropView}"

cmdDropFun=`psql -t -q ${cmd} -c "${sqlDropFun}"`

psql ${cmd} -c "${cmdDropFun}"

cmdDropTrg=`psql -t -q ${cmd} -c "${sqlDropTrg}"`

psql ${cmd} -c "${cmdDropTrg}"

}

# Start the Program

PGCLIENTENCODING="UTF8"

export PGCLIENTENCODING

while [ ! "$*" = "" ]

do

case $1 in

--help) usage|more;exit 0;;

-h)shift ;PGHOST=$1;;

-p)shift ;PGPORT=$1;;

-U)shift ;PGUSER=$1;;

-v)VERBOSE=Y;;

-d)shift ;PGDATABASE=$1;;

esac

shift

done

if [ x"${PGHOST}" != "x" ] && [ x"${PGPORT}" != "x" ] && [ x"${PGUSER}" != "x" ] && [ x"${PGDATABASE}" != "x" ];

then

cmd=" -h ${PGHOST} -U ${PGUSER} -p ${PGPORT} "

run_f

else

usage

exit 1

fi

Windows command for loop

如何在windows環境下批次修改檔名？

範例一：將所有的檔案加上附檔名apk

for %f in (*.*) do rename %f %f.apk

For

Runs a specified command for each file in a set of files.

Syntax

for {%variable|%%variable} in (set) do command [ CommandLineOptions]

Top of page

Parameters

{%variable|%%variable} : Required. Represents a replaceable parameter. Use %variable to carry out for from the command prompt. Use %%variable to carry out the forcommand within a batch file. Variables are case-sensitive and must be represented with an alpha value, such as %A, %B, or %C.

(set) : Required. Specifies one or more files, directories, range of values, or text strings that you want to process with the specified command. The parentheses are required.

command : Required. Specifies the command that you want to carry out on each file, directory, range of values, or text string included in the specified (set).

CommandLineOptions : Specifies any command-line options that you want to use with the specified command.

/?: Displays help at the command prompt.

Top of page

Remarks

•	Using for You can use the for command within a batch file or directly from the command prompt.
•	Using batch parameters The following attributes apply to the for command: • The for command replaces %variable or %%variable with each text string in the specified set until the command processes all of the files. • For variable names are case-sensitive, global, and no more than 52 total can be active at any one time. • To avoid confusion with the batch parameters %0 through %9, you can use any character for variable except the numerals 0 through 9. For simple batch files, a single character such as %%f works. • You can use multiple values for variable in complex batch files to distinguish different replaceable variables.
•	Specifying a group of files The set parameter can represent a single group of files or several groups of files. You can use wildcards (that is, * and ?) to specify a file set. The following are valid file sets: (*.doc) (*.doc *.txt *.me) (jan*.doc jan*.rpt feb*.doc feb*.rpt) (ar??1991.* ap??1991.*) When you use the for command, the first value in set replaces %variable or %%variable, and then the specified command processes this value. This continues until all of the files (or groups of files) that correspond to the set value are processed.
•	Using the in and do keywords In and do are not parameters, but you must use them with for. If you omit either of these keywords, an error message appears.
•	Using additional forms of for If command extensions are enabled (that is, the default), the following additional forms of for are supported: • Directories only If set contains wildcards (* and ?), the specified command executes for each directory (instead of a set of files in a specified directory) that matches set. The syntax is: for /D {%% | %}variable in (set) do command [CommandLineOptions] • Recursive Walks the directory tree rooted at [Drive:]Path, executing the for statement in each directory of the tree. If no directory is specified after /R, the current directory is assumed. If set is just a single period (.), it only enumerates the directory tree. The syntax is: for /R [[Drive :]Path] {%% | %}variable in (set) do command [CommandLineOptions] • Iterating a range of values Use an iterative variable to set the starting value (start#) and then step through a set range of values until the value exceeds the set ending value (end#). /L will execute the iterative by comparing start# with end#. If start# is less than end# the command will execute. When the iterative variable exceeds end# the command shell exists the loop. You can also use a negative step# to step through a range in decreasing values. For example, (1,1,5) generates the sequence 1 2 3 4 5 and (5,-1,1) generates the sequence (5 4 3 2 1). The syntax is: for /L {%% | %}variable in (start#,step#,end#) do command [CommandLineOptions] • Iterating and file parsing Use file parsing to process command output, strings and file content. Use iterative variables to define the content or strings you want to examine and use the variousParsingKeywords options to further modify the parsing. Use the ParsingKeywords token option to specify which tokens should be passed as iterator variables. Note that when used without the token option, /F will only examine the first token. File parsing consists of reading the output, string or file content, breaking it up into individual lines of text and then parsing each line into zero or more tokens. The forloop is then called with the iterator variable value set to the token. By default, /F passes the first blank separated token from each line of each file. Blank lines are skipped. The different syntaxes are: for /F ["ParsingKeywords"] {%% | %}variable in (filenameset) do command [CommandLineOptions] for /F ["ParsingKeywords"] {%% | %}variable in ("LiteralString") do command [CommandLineOptions] for /F ["ParsingKeywords"] {%% | %}variable in ('command') do command [CommandLineOptions] The filenameset argument specifies one or more file names. Each file is opened, read and processed before going on to the next file in filenameset. To override the default parsing behavior, specify "ParsingKeywords". This is a quoted string that contains one or more keywords to specify different parsing options. If you use the usebackq option, use one of the following syntaxes: for /F ["usebackqParsingKeywords"] {%% | %}variable in ("filenameset") do command [CommandLineOptions] for /F ["usebackqParsingKeywords"] {%% | %}variable in ('LiteralString') do command [CommandLineOptions] for /F ["usebackqParsingKeywords"] {%% | %}variable in (`command`) do command [CommandLineOptions] The following table lists the parsing keywords that you can use for ParsingKeywords. Keyword Description eol=c Specifies an end of line character (just one character). skip=n Specifies the number of lines to skip at the beginning of the file. delims=xxx Specifies a delimiter set. This replaces the default delimiter set of space and tab. tokens=x,y,m-n Specifies which tokens from each line are to be passed to the for body for each iteration. As a result, additional variable names are allocated. The m-nform is a range, specifying the mth through the nth tokens. If the last character in the tokens= string is an asterisk (*), an additional variable is allocated and receives the remaining text on the line after the last token that is parsed. usebackq Specifies that you can use quotation marks to quote file names in filenameset, a back quoted string is executed as a command, and a single quoted string is a literal string command. • Variable substitution Substitution modifiers for for variable references have been enhanced. The following table lists optional syntax (for any variable I). Variable with modifier Description %~I Expands %I which removes any surrounding quotation marks (""). %~fI Expands %I to a fully qualified path name. %~dI Expands %I to a drive letter only. %~pI Expands %I to a path only. %~nI Expands %I to a file name only. %~xI Expands %I to a file extension only. %~sI Expands path to contain short names only. %~aI Expands %I to the file attributes of file. %~tI Expands %I to the date and time of file. %~zI Expands %I to the size of file. %~$PATH:I Searches the directories listed in the PATH environment variable and expands %I to the fully qualified name of the first one found. If the environment variable name is not defined or the file is not found by the search, this modifier expands to the empty string. The following table lists modifier combinations that you can use to get compound results. Variable with combined modifiers Description %~dpI Expands %I to a drive letter and path only. %~nxI Expands %I to a file name and extension only. %~fsI Expands %I to a full path name with short names only. %~dp$PATH:I Searches the directories listed in the PATH environment variable for %I and expands to the drive letter and path of the first one found. %~ftzaI Expands %I to an output line that is like dir. In the above examples, you can replace %I and PATH by other valid values. A valid for variable name terminates the %~ syntax. By use uppercase variable names such as %I, you can make your code more readable and avoid confusion with the modifiers, which are not case-sensitive.
•	Parsing a string You can use the for /F parsing logic on an immediate string, by wrapping the filenameset between the parentheses in single quotation marks (that is, 'filenameset').Filenameset is treated as a single line of input from a file, and then it is parsed.
•	Parsing output You can use the for /F command to parse the output of a command by making the filenameset between the parenthesis a back quoted string. It is treated as a command line, which is passed to a child Cmd.exe and the output is captured into memory and parsed as if it were a file.

Top of page

Examples

To use for in a batch file, use the following syntax:

for %%variable in (set) do command [CommandLineOptions]

To display the contents of all the files in the current directory that have the extension .doc or .txt using the replaceable variable %f, type:

for %f in (*.doc *.txt) do type %f

In the preceding example, each file that has the .doc or .txt extension in the current directory is substituted for the %f variable until the contents of every file are displayed. To use this command in a batch file, replace every occurrence of %f with %%f. Otherwise, the variable is ignored and an error message is displayed.

To parse a file, ignoring commented lines, type:

for /F "eol=; tokens=2,3* delims=," %i in (myfile.txt) do @echo %i %j %k

This command parses each line in Myfile.txt, ignoring lines that begin with a semicolon and passing the second and third token from each line to the FOR body (tokens are delimited by commas or spaces). The body of the FOR statement references %i to get the second token, %j to get the third token, and %k to get all of the remaining tokens. If the file names that you supply contain spaces, use quotation marks around the text (for example, "File Name"). To use quotation marks, you must use usebackq. Otherwise, the quotation marks are interpreted as defining a literal string to parse.

%i is explicitly declared in the FOR statement, and %j and %k are implicitly declared by using tokens=. You can specify up to 26 tokens using tokens=, provided that it does not cause an attempt to declare a variable higher than the letter 'z' or 'Z'.

To parse the output of a command by placing filenameset between the parentheses, type:

for /F "usebackq delims==" %i IN (`set`) DO @echo %i

This example enumerates the environment variable names in the current environment.

在postgresql 裏如何撈取資料表欄位?

#在postgresql 裏如何撈取資料表欄位

#範例撈取pg_tables的欄位屬性

SELECT a.attname,b.relname,a.attnum

FROM PG_ATTRIBUTE a, PG_CLASS b

WHERE b.oid = a.attrelid

AND b.relname = 'pg_tables'

AND a.attname NOT IN ('tableoid', 'cmax', 'xmax', 'cmin','xmin', 'ctid')

order by attnum;

Postgresql 常用備份及還原

#備份資料Postgresql中的testdb 資料庫到檔按testdb.dmp

pg_dump -F c -i -v -f testdb.dmp testdb

#還原資料庫到Postgresql

#1.建立資料庫testdb2

createdb testdb2

#2.還原資料

pg_restore -F c -i -v -d testedb2 testdb.dmp