Introduction
The modern information system have many requirements for effectiveness and stability work. One of this fault tolerance, which provide protect from attack on integrity and availability, technical issue and another different incident, that can corrupt sufficiently data. For using tools and utilities we need to determine area of usage, in that instruction we will consider reservation and backup data in DBMS PostgreSQL through the proprietary and open-source utility.
Best practice
We can have database with different size, structure and volume, but we need to make full integrity backup and restore that properly. First utility that we can use it's pg_dump and psql or pg_restore. It use mechanism of read currently databases and convert to the command in the text file, therefore for input at the utility we indicate DB, for output we get text file.
That method have several advantages: version consistency, simplicity on export step and there isn't requirement for privilege rights in the system. For use that command type of backup, first of all we need to check there are databases and choose needed object:
\l
Let's make sure, how does the data look like? Type command below:
SELECT * FROM customers;
We have test record in the DB with theoretical user's data, we use pg_dump utility with option: -U for determine user of DBMS, -d identify database and for remote administration we can use -h and -p parameters. Host and port accordance:
pg_dump -U postgres -d db12 > backup.sql
Also we can use sequences of command for completing that task:
ssh user@hostname -p 5432 pg_dump -U postgres -d db12 > backup.sqlReplace username, hostname and port for your desirable configuration. If you want to backup and restore DB rapidly between several server, then enter that:
pg_dump -h 78.43.11.2 db_name | psql -h 72.43.11.2 db_nameFirst part allowed connect to the server with db and by using pipe redirect output to the target server with command after separated sign.
And using command scp we can transfer db:
scp ./backup.sql root@94.141.98.9:/
We should create database and restore content by the command below:
CREATE DATABASE db12;Then restore with psql command:
psql -U postgres -d db12 < backup.sql
Make sure integrity of content in the DB:
SELECT * FROM customers;
If database have a lot of data and great size, use split and zip-utility for transfer pices of data:
pg_dump name_db | gzip > name_archive.gzgunzip -c name_archive.gz | psql name_dbAnd split with syntax below:
pg_dump name_db | split -b 4G - name_filecat name_file | psql name_dbThat help for more effectiveness transfer data in process of backup.
Conclusion
By following these practices and utilizing the recommended commands and utilities, one can effectively and efficiently manage data backup and restoration in PostgreSQL, thereby ensuring the stability and reliability of the modern information system in the face of potential challenges and threats.
