[HACKERS] I could not see any row in audit table

[Shailesh Singh]

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 to see the restored data?

-- 
With Regards!
Shailesh Singh

[HACKERS] Urgent Help Required

[shailesh singh]

I had got this message while running vacuum full from backend . Now My
database is not starting , Help pls.

backend vacuum full debug;
WARNING:  database debug must be vacuumed within 99 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
ERROR:  relation debug does not exist
backend vacuum full;
WARNING:  database debug must be vacuumed within 98 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 97 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 96 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 95 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 94 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 93 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 92 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 91 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 90 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 89 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 88 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 87 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 86 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 85 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 84 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
WARNING:  database debug must be vacuumed within 83 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in
debug.
ERROR:  could not access status of transaction 449971277
DETAIL:  could not open file pg_clog/01AD: No such file or directory


Now what?

Thanks in advance.

Shailesh Singh

Re: [HACKERS] [GENERAL] Urgent Help Required

[shailesh singh]

Dear all,
 First of all i wish to share actual error meassge,

Below are the queries i had executed on the terminal on my server


-bash-3.2$ touch fix.sql
-bash-3.2$ echo VACUUM FULL;  fix.sql
-bash-3.2$ postgres -D /var/lib/pgsql/data patnadbold  fix.sql
WARNING: database patnadbold must be vacuumed within 100 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 100 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.

PostgreSQL stand-alone backend 8.1.11
backend WARNING: database patnadbold must be vacuumed within 99
transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 98 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 97 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 96 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 95 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 94 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 93 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 92 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 91 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 90 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 89 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 88 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 87 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 86 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 85 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
WARNING: database patnadbold must be vacuumed within 84 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
patnadbold.
ERROR: could not access status of transaction 33011
DETAIL: could not open file pg_clog/: No such file or directory
exit


After this i am able to stop /start my db server but i am not able to
connect to my databases (it tells to run vacuum full first on patnadbold
databases)


1)I am using postgres 8.4 version.
2) I had two databases on this server i) patnadbold  ii) patnaonlinedb

For me patnadbold is of no use if at this moment i lost this database that
also fine to me.
I wanted to connect patnaonlinedb any how and wanted to perform backup of
this , Solution please.


On Tue, Oct 8, 2013 at 6:19 PM, Adrian Klaver adrian.kla...@gmail.comwrote:

 On 10/08/2013 03:55 AM, shailesh singh wrote:

 I had got this message while running vacuum full from backend . Now My
 database is not starting , Help pls.

 backend vacuum full debug;
 WARNING:  database debug must be vacuumed within 99 transactions
 HINT:  To avoid a database shutdown, execute a full-database VACUUM in
 debug.
 ERROR:  relation debug does not exist



 Now what?


 First some information.

 1) What version of Postgres are you using?

 2) Does database debug in fact exist or not?
 In other words does it show up with \l in psql?

 Also it not necessary to use FULL with the VACUUM.



 Thanks in advance.

 Shailesh Singh



 --
 Adrian Klaver
 adrian.kla...@gmail.com




-- 
  With Regards,
  शैलेश सिंह |Shailesh Singh
 +९१-९६५०३१७५१७ | +91-9650317517