postgreSQL 坏块与checksum使用验证

 su - postgres
initdb -k -D $PGDATA
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/10/main -l logfile start

	
	
pg_ctl -D /var/lib/postgresql/10/main -l logfile start
waiting for server to start.... done
server started
	
	
psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

postgres=#



wget https://zcdn.askmac.cn/dellstore2-normal-1.0.tar.gz

tar -zxvf dellstore2-normal-1.0.tar.gz

createdb mac
psql mac -f dellstore2-normal-1.0/dellstore2-normal-1.0.sql  




postgres@vultr:~$ psql mac
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

mac=# \d
                    List of relations
 Schema |           Name           |   Type   |  Owner   
--------+--------------------------+----------+----------
 public | categories               | table    | postgres
 public | categories_category_seq  | sequence | postgres
 public | cust_hist                | table    | postgres
 public | customers                | table    | postgres
 public | customers_customerid_seq | sequence | postgres
 public | inventory                | table    | postgres
 public | orderlines               | table    | postgres
 public | orders                   | table    | postgres
 public | orders_orderid_seq       | sequence | postgres
 public | products                 | table    | postgres
 public | products_prod_id_seq     | sequence | postgres
 public | reorder                  | table    | postgres
(12 rows)




psql mac -c "SELECT relname, relpages, reltuples, relfilenode FROM pg_class
  WHERE relkind = 'r' AND relname NOT LIKE 'pg%'
  ORDER BY relpages DESC
  LIMIT 1;"
  
  
  
  relname  | relpages | reltuples | relfilenode 
-----------+----------+-----------+-------------
 customers |      488 |     20000 |       16478
(1 row)



psql mac -c "SELECT datname, oid FROM pg_database;"

postgres@vultr:~$ psql mac -c "SELECT datname, oid FROM pg_database;"
  datname  |  oid  
-----------+-------
 postgres  | 13055
 mac       | 16457
 template1 |     1
 template0 | 13054
(4 rows)




wget https://zcdn.askmac.cn/corrupt.pl


pg_ctl stop -D $PGDATA

waiting for server to shut down.... done


perl corrupt.pl $PGDATA/base/16457/16478 5


pg_ctl start -D $PGDATA


psql mac -c "SELECT * FROM customers;"

postgres@vultr:~$ psql mac -c "SELECT * FROM customers;"
2019-11-20 07:11:42.487 UTC [24934] WARNING:  page verification failed, calculated checksum 56287 but expected 28524
WARNING:  page verification failed, calculated checksum 56287 but expected 28524
2019-11-20 07:11:42.487 UTC [24934] ERROR:  invalid page in block 1 of relation base/16457/16478
2019-11-20 07:11:42.487 UTC [24934] STATEMENT:  SELECT * FROM customers;
ERROR:  invalid page in block 1 of relation base/16457/16478



postgres@vultr:~$ psql mac 
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

  
SHOW zero_damaged_pages ;
SHOW ignore_checksum_failure;
SET zero_damaged_pages to on;
SET
set ignore_checksum_failure to on;
SET

postgres=#  SELECT * FROM customers;






or 

设置参数 

SET ignore_checksum_failure to on ;
ALTER SYSTEM SET ignore_checksum_failure = on;
SELECT pg_reload_conf();