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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> -- With Regards! Shailesh Singh
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
