Postgres database corruption

Postgres database corruption

Please follow the steps, the there is database corruption with the below error message due to low disk space in the drive:

org.postgresql.util.PSQLException: ERROR: could not read block 0 in file "base/16384/116045":
org.postgresql.util.PSQLException: ERROR: invalid page header in block 2938 of relation base/16384/115816
etc

Steps:

1) Stop the NetFlow Analyzer service.

2) Open command prompt as Administrator and navigate to NetFlow_Home/bin and execute startDB.bat/.sh (Till Build 11001) or startPgsql.bat/.sh ( From Build 12000 and above )

3) The navigate to
NetFlow_Home/pgsql/bin and Connect to the database with the below command:

psql -U postgres -p 13310 -h 127.0.0.1 -d netflow     ( Till version 11001 and upgraded)

psql -U postgres -p 13306 -h 127.0.0.1 -d OpManagerDB    (From 12000 and above )

Once connected execute the below commands:


SET zero_damaged_pages = on;

If only particular table is corrupted follow the below steps:

For each Table follow the below steps
1) VACUUM FULL $damaged_table  
2) REINDEX TABLE $damaged_table
Here,
$damaged_table == Name of the Table that has indexing problem

e.g:
1) psql -U postgres -p 13310 -h 127.0.0.1 -d netflow
2) SET zero_damaged_pages = on; 
3) VACUUM FULL applicationout10min_nfa1
4) REINDEX TABLE applicationout10min_nfa1
To Reindex the entire database :

1) Connect to the database in client and then follow steps 2 to 4
2) SET zero_damaged_pages = on; 
3) VACUUM FULL; 
4) REINDEX database $database_Name;

e.g : For entire database
1) psql -U postgres -p 13310 -h 127.0.0.1 -d netflow
2) SET zero_damaged_pages = on;
3) VACUUM FULL; 
4) REINDEX database netflow;


Once done, Start the NetFlow Analyzer service and check on the issue.

          • Related Articles

          • Steps to add password to postgres database in NFA12 & NCM12

            1) Start OpManager service 2)Connect  to OpManager database :   a)Go to OpManager\pgsql\bin folder   b)Type psql -U postgres -h 127.0.0.1 -p13306  OpManagerDB   c)Once connected to the Database, execute the below query to set password          ALTER ...
          • Database Migration from Mysql to Postgres in NetFlow Analyzer version 10250

            Steps to Migrate NetFlow Standalone Mysql DB to pgsql Datase Note: Migration is applicable only in build 10250 Both the MYSQL and PGSQL installation should be in the same server to perform the Migration. Make sure that you have enough disk space ...
          • NFA and NCM 12 Default Admin password Reset_postgres Database

            Forgot Password: The Forgot Password option in the login page can be used to reset the password. Make sure the Mail Server Settings and User's email ID are configured. The auto generated password will be sent to the email ID entered as the User's ...
          • MSSQL Database shrink.

            To reduce the MSSQL database size, please follow the below steps. 1. Stop the NetFlow Analyzer service. 2.Open a command prompt as administrator(Right click and run as administrator) Navigate to <NetFlow_Home\troubleshooting\>. 3. Execute the ...
          • Patch to resolve the issue with NCM device backup(postgres)

            Please apply the patch to resolve the issue with the device backup in NCM 12200(postgres) 1. Stop the OpManager server. 2. Please download the fix from the below link : ...