Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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 ?

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!"
fi
4.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 
----------------
 off
If 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 testdb
 Once 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

No comments:

Post a Comment