Thread: [GENERAL] Fwd: I could not see any row in audit table
Dear Group Member ,
I had configured the audit trigger for my datbase following the below document url:https://wiki.postgresql.org/
Now my audit table :
CREATE TABLE audit.logged_actions ( event_id bigserial PRIMARY KEY, schema_name text NOT NULL, TABLE_NAME text NOT NULL, relid oid NOT NULL, session_user_name text, action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL, action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL, action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL, transaction_id BIGINT, application_name text, client_addr inet, client_port INTEGER, client_query text NOT NULL, action CHAR(1) NOT NULL CHECK (action IN ('I','D','U', 'T')), row_data hstore, changed_fields hstore, statement_only BOOLEAN NOT NULL );Now this table contains 50 GB of data , But when taking its backup using pg_dump and after restoring , it show that it has zero row.
How to see the restored data?
--
With Regards!
Shailesh Singh
Shailesh Singh
On 25/01/17 11:03, Shailesh Singh wrote: > > > > Dear Group Member , > > > I had configured the audit trigger for my datbase following the below > document url: > > *https://wiki.postgresql.org/wiki/Audit_trigger_91plus > <https://wiki.postgresql.org/wiki/Audit_trigger_91plus> > > > * > Now my audit table : > > CREATE TABLE audit.logged_actions ( > event_id bigserial PRIMARY KEY, > schema_name text NOT NULL, > TABLE_NAME text NOT NULL, > relid oid NOT NULL, > session_user_name text, > action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL, > action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL, > action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL, > transaction_id BIGINT, > application_name text, > client_addr inet, > client_port INTEGER, > client_query text NOT NULL, > action CHAR(1) NOT NULL CHECK (action IN ('I','D','U', 'T')), > row_data hstore, > changed_fields hstore, > statement_only BOOLEAN NOT NULL > ); > > > Now this table contains 50 GB of data , But when taking its backup using > pg_dump and after restoring , it show that it has zero row. How did you restore it? What is showing that there are zero rows? Did you do "select count(*) from ...", or something else? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 01/25/2017 03:03 AM, Shailesh Singh wrote: > > > > Dear Group Member , > > > I had configured the audit trigger for my datbase following the below > document url: > > *https://wiki.postgresql.org/wiki/Audit_trigger_91plus > <https://wiki.postgresql.org/wiki/Audit_trigger_91plus> > > > * > Now my audit table : > > CREATE TABLE audit.logged_actions ( > event_id bigserial PRIMARY KEY, > schema_name text NOT NULL, > TABLE_NAME text NOT NULL, > relid oid NOT NULL, > session_user_name text, > action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL, > action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL, > action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL, > transaction_id BIGINT, > application_name text, > client_addr inet, > client_port INTEGER, > client_query text NOT NULL, > action CHAR(1) NOT NULL CHECK (action IN ('I','D','U', 'T')), > row_data hstore, > changed_fields hstore, > statement_only BOOLEAN NOT NULL > ); > > > Now this table contains 50 GB of data , But when taking its backup using > pg_dump and after restoring , it show that it has zero row. What was the dump command you used? Where there any errors during the restore? > > > How to see the restored data? > ** > > -- > With Regards! > Shailesh Singh > > > > -- Adrian Klaver adrian.klaver@aklaver.com
Dear Adrian Klaver,
I have used pg_dump to take backupSome thing related to toastble i found as my table uses extended data type size , but till now not able to view data after restoring.
On Wed, Jan 25, 2017 at 7:56 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 01/25/2017 03:03 AM, Shailesh Singh wrote:
Dear Group Member ,
I had configured the audit trigger for my datbase following the below
document url:
*https://wiki.postgresql.org/wiki/Audit_trigger_91plus
<https://wiki.postgresql.org/wiki/Audit_trigger_91plus>
*
Now my audit table :
CREATE TABLE audit.logged_actions (
event_id bigserial PRIMARY KEY,
schema_name text NOT NULL,
TABLE_NAME text NOT NULL,
relid oid NOT NULL,
session_user_name text,
action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL,
action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL,
action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL,
transaction_id BIGINT,
application_name text,
client_addr inet,
client_port INTEGER,
client_query text NOT NULL,
action CHAR(1) NOT NULL CHECK (action IN ('I','D','U', 'T')),
row_data hstore,
changed_fields hstore,
statement_only BOOLEAN NOT NULL
);
Now this table contains 50 GB of data , But when taking its backup using
pg_dump and after restoring , it show that it has zero row.
What was the dump command you used?
Where there any errors during the restore?
How to see the restored data?
**
--
With Regards!
Shailesh Singh
--
Adrian Klaver
adrian.klaver@aklaver.com
--
With Regards!
Shailesh Singh
Shailesh Singh
On 01/25/2017 09:57 AM, Shailesh Singh wrote: > Dear Adrian Klaver, > > I have used pg_dump to take backup > eg: pg_dump my_db > /dbbackup/audit_table.sql To be clear you dumped the entire database, not just the audit table, correct? Meant to ask before, are you dumping/restoring between the same Postgres version, if so what version would that be? If not what versions are we talking about and which version of pg_dump did you use? > > And > > I have restored it using psql command > > eg: psql my_test_db < /dbbackup/audit_table.sql > > before it has had also created necessay hstore extension for my new db > "*my_test_db*" Again, just to be clear, you created the hstore extension in the new database and then restored the data, correct? > > There is no error while restoring data. The data is in the audit_table.sql file? > > After Restoring when i issued select count(*) from audit.logged_actions > ; , it shows zero row count , > > Some thing related to *toastble *i found as my table uses extended data > type size , but till now not able to view data after restoring. Looking at the table schema, there are non-toastable fields in it also, so I would except you would see something or get an error when you did the select. I don't think this is a TOAST problem. > > On Wed, Jan 25, 2017 at 7:56 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 01/25/2017 03:03 AM, Shailesh Singh wrote: > > > > > Dear Group Member , > > > I had configured the audit trigger for my datbase following the > below > document url: > > *https://wiki.postgresql.org/wiki/Audit_trigger_91plus > <https://wiki.postgresql.org/wiki/Audit_trigger_91plus> > <https://wiki.postgresql.org/wiki/Audit_trigger_91plus > <https://wiki.postgresql.org/wiki/Audit_trigger_91plus>> > > > * > Now my audit table : > > CREATE TABLE audit.logged_actions ( > event_id bigserial PRIMARY KEY, > schema_name text NOT NULL, > TABLE_NAME text NOT NULL, > relid oid NOT NULL, > session_user_name text, > action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL, > action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL, > action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL, > transaction_id BIGINT, > application_name text, > client_addr inet, > client_port INTEGER, > client_query text NOT NULL, > action CHAR(1) NOT NULL CHECK (action IN ('I','D','U', 'T')), > row_data hstore, > changed_fields hstore, > statement_only BOOLEAN NOT NULL > ); > > > Now this table contains 50 GB of data , But when taking its > backup using > pg_dump and after restoring , it show that it has zero row. > > > What was the dump command you used? > > Where there any errors during the restore? > > > > How to see the restored data? > ** > > -- > With Regards! > Shailesh Singh > > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > > -- > With Regards! > Shailesh Singh -- Adrian Klaver adrian.klaver@aklaver.com