pg_restore -h localhost -p 5432 -U user -d homemania -v "/home/user/Downloads/hm.sql"
Или если input file does not appear to be a valid archive
То вот так
cat db.txt | psql dbname
CREATE OR REPLACE PROCEDURE P_GK_FX_TJ_NEWS_LAST_HC IS
--重点人员个人轨迹信息数据量统计 全量统计
PROC_KSSJ DATE; --存储过程开始时间
V_SQL_STMT VARCHAR2(500);
BEGIN
--变量赋值
PROC_KSSJ := SYSDATE;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE GK_FX_TJ_NEWS_LAST';
--网吧轨迹
--插入新增统计重点人员编号
EXECUTE IMMEDIATE 'TRUNCATE TABLE GK_TJCOUNT_ACT_HCADD';
INSERT INTO GK_TJCOUNT_ACT_HCADD
SELECT GLBH, COUNT(1),TO_DATE(TO_CHAR(T.SWSJ,'YYYY-MM-DD'),'YYYY-MM-DD') SJ FROM GK_GJ_WBGJ T GROUP BY T.GLBH ,TO_CHAR(T.SWSJ,'YYYY-MM-DD');
COMMIT;
INSERT INTO GK_FX_TJ_NEWS_LAST
(GRBH,HDSJ)
SELECT B.GLBH,B.SJ
FROM GK_TJCOUNT_ACT_HCADD B
WHERE NOT EXISTS (SELECT 1 FROM GK_FX_TJ_NEWS_LAST S WHERE S.GRBH = B.GLBH AND S.HDSJ=B.SJ) ;
COMMIT;
UPDATE GK_FX_TJ_NEWS_LAST S
SET S.WBGJ = NVL((SELECT NUMVALUES FROM GK_TJCOUNT_ACT_HCADD T WHERE T.GLBH = S.GRBH AND T.SJ=S.HDSJ),0);
COMMIT;
--旅馆轨迹
--插入新增统计重点人员编号
EXECUTE IMMEDIATE 'TRUNCATE TABLE GK_TJCOUNT_ACT_HCADD';
INSERT INTO GK_TJCOUNT_ACT_HCADD
SELECT GLBH, COUNT(1),TO_DATE(TO_CHAR(T.RZSJ,'YYYY-MM-DD'),'YYYY-MM-DD') SJ FROM GK_GJ_LGGJ T GROUP BY T.GLBH ,TO_CHAR(T.RZSJ,'YYYY-MM-DD');
COMMIT;
INSERT INTO GK_FX_TJ_NEWS_LAST
(GRBH,HDSJ)
SELECT B.GLBH,B.SJ
FROM GK_TJCOUNT_ACT_HCADD B
WHERE NOT EXISTS (SELECT 1 FROM GK_FX_TJ_NEWS_LAST S WHERE S.GRBH = B.GLBH AND S.HDSJ=B.SJ);
COMMIT;
UPDATE GK_FX_TJ_NEWS_LAST S
SET S.LGGJ = NVL((SELECT NUMVALUES FROM GK_TJCOUNT_ACT_HCADD T WHERE T.GLBH = S.GRBH AND T.SJ=S.HDSJ),0);
COMMIT;
--民航信息
--插入新增统计重点人员编号
EXECUTE IMMEDIATE 'TRUNCATE TABLE GK_TJCOUNT_ACT_HCADD';
INSERT INTO GK_TJCOUNT_ACT_HCADD
SELECT GLBH, COUNT(1),TO_DATE(TO_CHAR(T.LGSJ,'YYYY-MM-DD'),'YYYY-MM-DD') SJ FROM GK_GJ_MHXX T GROUP BY T.GLBH ,TO_CHAR(T.LGSJ,'YYYY-MM-DD');
COMMIT;
INSERT INTO GK_FX_TJ_NEWS_LAST
(GRBH,HDSJ)
SELECT B.GLBH,B.SJ
FROM GK_TJCOUNT_ACT_HCADD B
WHERE NOT EXISTS (SELECT 1 FROM GK_FX_TJ_NEWS_LAST S WHERE S.GRBH = B.GLBH AND S.HDSJ=B.SJ);
COMMIT;
UPDATE GK_FX_TJ_NEWS_LAST S
SET S.MHXX = NVL((SELECT NUMVALUES FROM GK_TJCOUNT_ACT_HCADD T WHERE T.GLBH = S.GRBH AND T.SJ=S.HDSJ),0);
COMMIT;
---更新数据为空的
--- UPDATE GK_FX_TJ_NEWS_LAST S
-- SET S.WBGJ = NVL(S.WBGJ,0),
-- S.LGGJ = NVL(S.LGGJ,0),
--- S.MHXX = NVL(S.MHXX,0);
-- COMMIT;
--成功时记录日志
INSERT INTO GK_BD_LOG
(BD_LOG_CODE, PROC_KSSJ, PROC_JSSJ, BD_JZSJC, CGBZ, PROC_NAME)
VALUES
(F_GETSEQ(30), PROC_KSSJ, SYSDATE, SYSDATE, 'Y', 'P_GK_FX_TJ_NEWS_LAST_HC');
COMMIT;
END;
EXCEPTION
WHEN OTHERS THEN
V_SQL_STMT := 'sqlcode:' || SQLCODE || '###sqlerrm:' || SQLERRM;
ROLLBACK;
--失败时记录日志
INSERT INTO GK_BD_LOG
(BD_LOG_CODE,
PROC_KSSJ,
PROC_JSSJ,
BD_JZSJC,
CGBZ,
ERROR_INFO,
PROC_NAME)
VALUES
(F_GETSEQ(30),
PROC_KSSJ,
SYSDATE,
SYSDATE,
'N',
V_SQL_STMT,
'P_GK_FX_TJ_NEWS_LAST_HC');
COMMIT;
END P_GK_FX_TJ_NEWS_LAST_HC;
To login to server with port append -p (port number)
eg. ssh root@vbb -p 2222
To access mysql through ssh:
use the db user and password dummy!
not the ssh root user, you do need to be logged in as root thought so "su root"
mysql -u (username) -p (db name)
eg. mysql -u root -p password
to show tables: mysql> show tables;
to alter table name: mysql> ALTER TABLE commIssue RENAME commissue;
to add columns: mysql> ALTER TABLE `name` ADD COLUMN `whateverworker` VARCHAR( 50 ) NULL;
To import existing table run this command from the directory that the .sql file is in
mysql -p -u (user) (dbname) < filename.sql
eg. mysql> mysql -p -u root password < whatever.sql
To put a backup of databse in current directory
mysqldump -uUSERNAME -pPASSWORD DATABASE > whatever.sql
To Drop Table
DROP TABLE table_name;
-- SELECT pc_search_tablefield('%Charter has been activated%', '%', '%', '%', '%', null);
-- The above returns all column names tht contains given text
-- SELECT pc_search_tablefield('%john@hotmail%', '%email%', '%', '%', '%', null);
-- The above will return all database field names that have the phrase email in the field name and that contain the term john@hotmail
CREATE OR REPLACE FUNCTION pc_search_tablefield(param_search text, param_field_like text, param_table_like text,
param_schema_like text, param_datatype_like text, param_max_length integer)
RETURNS text AS
$$
DECLARE
result text := '';
var_match text := '';
searchsql text := '';
BEGIN
searchsql := array_to_string(ARRAY(SELECT 'SELECT ' || quote_literal(quote_ident(c.table_schema) || '.'
|| quote_ident(c.table_name) || '.' || quote_ident(c.column_name)) ||
' WHERE EXISTS(SELECT ' || quote_ident(c.column_name) || ' FROM '
|| quote_ident(c.table_schema) || '.' || quote_ident(c.table_name) ||
' WHERE ' || CASE WHEN c.data_type IN('character', 'character varying', 'text') THEN
quote_ident(c.column_name) ELSE 'CAST(' || quote_ident(c.column_name) || ' As text) ' END
|| ' LIKE ' || quote_literal(param_search) || ') ' As subsql
FROM information_schema.columns c
WHERE c.table_schema NOT IN('pg_catalog', 'information_schema')
AND c.table_name LIKE param_table_like
AND c.table_schema LIKE param_schema_like
AND c.column_name LIKE param_field_like
AND c.data_type IN('"char"','character', 'character varying', 'integer', 'numeric', 'real', 'text')
AND c.data_type LIKE param_datatype_like
AND (param_max_length IS NULL OR param_max_length = 0
OR character_maximum_length <= param_max_length) AND
EXISTS(SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_type = 'BASE TABLE'
AND t.table_name = c.table_name AND t.table_schema = c.table_schema)),
' UNION ALL ' || E'\r');
--do an exists check thru all tables/fields that match field table pattern
--return those schema.table.fields that contain search pattern information
RAISE NOTICE '%', searchsql;
IF searchsql > '' THEN
FOR var_match IN EXECUTE(searchsql) LOOP
IF result > '' THEN
result := result || ';' || var_match;
ELSE
result := var_match;
END IF;
END LOOP;
END IF;
RETURN result;
END;$$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
-- http://stackoverflow.com/a/20056883/63650
-- Usage: SELECT db_to_csv('/home/user/dir');
-- this will create one csv file per table, in /home/user/dir/
CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
declare
tables RECORD;
statement TEXT;
begin
FOR tables IN
SELECT (table_schema || '.' || table_name) AS schema_table
FROM information_schema.tables t INNER JOIN information_schema.schemata s
ON s.schema_name = t.table_schema
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'configuration')
ORDER BY schema_table
LOOP
statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
EXECUTE statement;
END LOOP;
return;
end;
$$ LANGUAGE plpgsql;