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 <adrian.kla...@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.kla...@aklaver.com
>
--
With Regards!
Shailesh Singh
[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
[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 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
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 brick...@gmail.com wrote: On Tue, Oct 8, 2013 at 8:13 AM, shailesh singh shaileshj...@gmail.comwrote: On Tue, Oct 8, 2013 at 8:36 PM, bricklen brick...@gmail.com wrote: On Tue, Oct 8, 2013 at 8:03 AM, shailesh singh shaileshj...@gmail.comwrote: 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
