Postgres related

Forgotten PostgreSQL Windows password

[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_');

Function to search schema for specified table name (partial name supported, related to Audit trigger 91plus):
--- 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_');

Debug PostgreSQL function using pgAdmin
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