[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/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
Re: [GENERAL] Fwd: I could not see any row in audit table
Dear Adrian Klaver,
I have used pg_dump to take backup
eg: pg_dump my_db > /dbbackup/audit_table.sql
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*"
There is no error while restoring data.
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.
On Wed, Jan 25, 2017 at 7:56 PM, Adrian Klaver
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.kla...@aklaver.com
>
--
With Regards!
Shailesh Singh
[GENERAL] Urgent Help Required
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: [GENERAL] Urgent Help Required
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 wrote: > 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
Re: [HACKERS] [GENERAL] Urgent Help Required
yes i am executing psql to connect to this database. su - postgres psql patnadbold On Tue, Oct 8, 2013 at 8:48 PM, bricklen wrote: > > > > On Tue, Oct 8, 2013 at 8:13 AM, shailesh singh wrote: > >> >> On Tue, Oct 8, 2013 at 8:36 PM, bricklen wrote: >> >>> >>> On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh >>> wrote: >>> >>>> 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) >>>> >>> >>> The message does *not* say to run "VACUUM FULL", it says to run a >>> "full-database VACUUM". Different things. >>> Connect to "patnadbold" and issue "VACUUM;" (without double-quotes) as >>> the others have suggested. >>> >> >> When i am trying to connect "patnadbold" , it is giving error for >> "execute a full-database VACUUM in "patnadbold" " . >> is there any way to connect this database using backend process . pl let >> me know the command sequnce i need to run. >> Thanks. >> > > Please keep replies CC'd to the pgsql-general list, and follow the format > of the other messages, which is to bottom-post. > > You need to show (again?) exactly what you are executing. Are you > connecting via psql or using another command? Are you connecting directly > to that "patnadbold" database? If you are able to connect to it, are you > able to issue just "VACUUM;" ? > > > > > -- With Regards, शैलेश सिंह |Shailesh Singh +९१-९६५०३१७५१७ | +91-9650317517
