How to verifiy postgres dump correct or not?
1.Mention log file as "backup successfull" at the time of backup completed.
2.count the row count some specific table if you want to move cloud or other server need down time only for insert,update,delete statement.
3.write shell script for mail alert if pg_dump successfull or not ?
PostgreSQL has supported page-level checksums since 9.3. If page checksums are enabled pgBackRest will validate the checksums for every file that is copied during a backup. All page checksums are validated during a full backup and checksums in files that have changed are validated during differential and incremental backups.
the data checksum has to be turned on during server set up – specifically when running initdb. Checksums can’t be enabled after a database is created either
If you’ve turned on checksums, PostgreSQL still won’t fix data problems for you. It will, however, throw an error when bad data is retrieved from disk
If you haven’t enabled the checksums, you’ll have to move the data into a new PostgreSQL installation
Check the checksum enabled or not:
For this example, we will use a fresh 9.4 database, and set it up with checksums:
2.count the row count some specific table if you want to move cloud or other server need down time only for insert,update,delete statement.
3.write shell script for mail alert if pg_dump successfull or not ?
su postgres "pg_dump our_database 2>> $LOG_FILE | gzip > /home/smb/shared/database_backup.bak.gz" cat $LOG_FILE | mailx $MAINTAINERS -s "Postgresql backup" LOG_FILE=/tmp/pgdump.err if ! pg_dump -U backupuser "our_database" 2> $LOG_FILE then cat $LOG_FILE | mailx 'youremailaddress' -s "Postgresql backup failure!" fi4.pgBackRest:
PostgreSQL has supported page-level checksums since 9.3. If page checksums are enabled pgBackRest will validate the checksums for every file that is copied during a backup. All page checksums are validated during a full backup and checksums in files that have changed are validated during differential and incremental backups.
the data checksum has to be turned on during server set up – specifically when running initdb. Checksums can’t be enabled after a database is created either
If you’ve turned on checksums, PostgreSQL still won’t fix data problems for you. It will, however, throw an error when bad data is retrieved from disk
If you haven’t enabled the checksums, you’ll have to move the data into a new PostgreSQL installation
Check the checksum enabled or not:
show data_checksums; data_checksums ---------------- offIf not enable it
For this example, we will use a fresh 9.4 database, and set it up with checksums:
~$ cd ~/pg/9.4 ~/pg/9.4$ bin/initdb --data-checksums lotus The files belonging to this database system will be owned by user "greg". ... Data page checksums are enabled. ... ~/pg/9.4$ echo port=5594 >> lotus/postgresql.conf ~/pg/9.4$ bin/pg_ctl start -D lotus -l lotus.log server starting ~/pg/9.4$ bin/createdb -p 5594 testdbOnce that is done, the checksum will fail and we will, as expected, receive a checksum error:
~/pg/9.4$ bin/pg_ctl restart -D lotus -l lotus.log waiting for server to shut down.... done server stopped server starting ~/pg/9.4$ LC_ALL=C sed -r -i "s/(.{8})../\1NI/" $D ~/pg/9.4$ psql testdb -p$P -tc 'select rtrim(baz) from foobar' WARNING: page verification failed, calculated checksum 6527 but expected 18766 ERROR: invalid page in block 0 of relation base/16384/16385
Comments
Post a Comment