[database vs] schema
Log changes made by users:
Audit trigger 91plus
Note to self:
From experience with idempiere database on Postgres 9.4, the code need to be changed:
from "quote_ident(target_table::text)" to "target_table"
Related to that matter:
Define table and column names as arguments in a plpgsql function?
SQL injection in Postgres functions vs prepared queries
Unexpected behavior of dynamic SQL using regclass
Function to activate audit trigger on several tables at one (related to Audit trigger 91plus):
--- code start ---
CREATE OR REPLACE FUNCTION audit.mass_on(
_schema_ character varying,
_table_regexp_ character varying)
RETURNS SETOF character varying AS
$BODY$
DECLARE
result VARCHAR := '';
BEGIN
FOR result IN SELECT table_name FROM information_schema.tables
WHERE table_schema = $1 AND table_name ~* $2 and table_type = 'BASE TABLE' ORDER BY table_name
LOOP
perform audit.audit_table($1 || '.' || result);
RETURN NEXT result;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION audit.mass_on(character varying, character varying)
OWNER TO postgres;
--- code finish ---
Sample usage:
select audit.mass_on('adempiere','_');
Function to de-activate audit trigger on several tables at one (related to Audit trigger 91plus):
--- code start ---
CREATE OR REPLACE FUNCTION audit.mass_off(
_schema_ character varying,
_table_regexp_ character varying)
RETURNS SETOF character varying AS
$BODY$
DECLARE
result VARCHAR := '';
BEGIN
FOR result IN SELECT table_name FROM information_schema.tables
WHERE table_schema = $1 AND table_name ~* $2 and table_type = 'BASE TABLE' ORDER BY table_name
LOOP
EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_row ON ' || $1 || '.' || result;
EXECUTE 'DROP TRIGGER IF EXISTS audit_trigger_stm ON ' || $1 || '.' || result;
RETURN NEXT result;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION audit.mass_off(character varying, character varying)
OWNER TO postgres;
--- code finish ---
Sample usage:
select audit.mass_off('adempiere','pa_');
--- code start ---
CREATE OR REPLACE FUNCTION audit.list_tables(
_schema_ character varying,
_table_regexp_ character varying)
RETURNS SETOF character varying AS
$BODY$
DECLARE
result VARCHAR := '';
BEGIN
FOR result IN SELECT table_name FROM information_schema.tables
WHERE table_schema = $1 AND table_name ~* $2 ORDER BY table_name
LOOP
RETURN NEXT result;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION audit.list_tables(character varying, character varying)
OWNER TO postgres;
--- code finsh ---
Sample usage:
select audit.list_tables('adempiere','ad_');
picture
Re-add database from files on disk:
Recover postgreSQL databases from raw physical files
Physical location of database files:
Where does PostgreSQL store the database?
Database backup using SQL Dump
An important advantage of pg_dump over the other backup methods described later is that pg_dump's output can generally be re-loaded into newer versions of PostgreSQL, whereas file-level backups and continuous archiving are both extremely server-version-specific. pg_dump is also the only method that will work when transferring a database to a different machine architecture, such as going from a 32-bit to a 64-bit server
How do I specify a password to psql non-interactively?
Set the PGPASSWORD environment variable ... before calling psql
