How not to lose data in PostgreSQL
PostgreSQL offers several options for data backup. All of them have already told more than once, including on habré. But mainly describes about the technical features of the methods. I want to try to talk about General backup strategy, combining all methods into an effective system that will help you to save all the data and reduce the number of deaths of nerve cells in critical situations.
Inputs: PostgreSQL 9.2 server, the Database size is > 100Gb.
As can be seen from manuals, there are 3 methods of backup:
the
They all have their own characteristics, so we use all of these methods.
Setup streaming replication is well described in articles here and here. The meaning of this replication is that if the primary server goes down, the slave can quickly make a master and work with him, because the slave is a complete copy of the database.
In streaming replication is of great importance to the WAL files. This is the file where the slave pulls the missing data, if the master they are no longer there. Hence there is a need to store the WAL-files longer. We store these files 8 days.
Directory with the WAL files should be available as the master (write) and slave (read-only). To ensure this, we have created a shared repository on the basis of glusterFS, which was pazmontirovanie on both servers. Thus, the first, achieved greater reliability (the fall of the wizard of wal-files will be available to the slave), and secondly there is the ability to quickly create additional slaves, who also need these wal.
Summary: streaming replication protects against failure of the server, with almost no data lost.
With the fall of the servers figured out, now let's deal with the human factor, when for some reason deleted the data in the tables, either table or database. In this case you have to restore data from a backup. Another copy of the database may need to test the application. This copy can be done in two ways — make a database dump or copy the entire directory with the data. For a long time, we used the first option — danile database to a file. But dump has a big disadvantage —the process locks the tables and other processes can no longer work with them (UPD: pg_dump does not lock tables. However, it creates heavy load on the database). For large database it is critical.
Now to backup the database we use the tool pg_basebackup, which essentially copies all the database files into one big files.
We keep 4 copies of weekly and 6 monthly. Copies are stored on the same storage GlusterFS. The copy we create so that they were self-sufficient, that is working immediately after deployment, without the need to download additional WAL files. So we can easily deploy the database, for example, three months ago.
It is noteworthy that the pg_basebackup utility can be run (under certain conditions) on the slave server, so the backup is absolutely not a load master.
To pg_basebackup worked on the slave, you need to enable saving WAL files that install option in postgresql.conf:
the
1000 is the number of wal files that postgreSQL stores on disk. You may need to increase or decrease this setting. The fact that pg_basebackup simply archives the contents of the database, but the backup some of the data have changed, and these changed data PostgreSQL then when you pull up the recovery from WAL files. For this pg_basebackup will retain in an archive all existing WAL files. So that all went well, you need to be available all WAL files since the start of pg_basebackup work until its completion. If you need the WAL files are deleted, pg_basebackup will fail. The right amount of wal_keep_segments can be determined empirically.
the
-F tells us that you need to keep everything in one file-z — that need to archive-Xf — to include in the archive the WAL files. Without the inclusion of WAL files backup will work, but when you restore Postgres will require to provide the missing WAL files.
Backup we do at the crown on Saturday this script:
the
This creates a file with 3 suffixes: with week number, month name and official last.
To restore the database from backup, you need to stop PostgreSQL, remove (or move) the old data from the data-directory, and unpack there the content of the archive and start the server. There is one interesting moment. Since pg_basebackup we did on the slave, the data is unzipped and file recovery.conf. So, if we want to restore data to the latest possible state, then this file need to leave, in this case, after starting the Postgres server will start to pull the WAL files from the location you specified in the recovery.conf. If you take the last weekly backup, then we will have all the necessary WAL files (since they are stored 8 days), and we will be able to restore the database to the latest normal state.
If we need data as at the time of creating backup copies before you start the database you need to uninstall file recovery.conf.
Backups we also use for testing purposes, for one is checked and the correctness of the backup.
Name base.last.tar.gz used to recover the last copy in the test database. The test database we restored every night this script:
the
Summary: back up database on the file level will protect from software failures and human errors. When the database is restored from backup will I lose the latest data.
We don't do a full SQL dump of a big database, but doing a dump of the changed tables, 1 table in 1 file. This allows you to quickly recover data in cases when corrupted only one table — no need to unpack the entire database from backup.
PostgreSQL provides us with statistics on the number of changes in the tables, and every night we look at what tables were changed and their denim. The statistics look about this request:
the
The result: an SQL dump will help to restore minor bugs. The data will be current at the time of the dump.
As you can see, to protect yourself from losing data, and you need to use all the features of PostgreSQL, especially because it is not so difficult.
Article based on information from habrahabr.ru
Inputs: PostgreSQL 9.2 server, the Database size is > 100Gb.
backup Options
As can be seen from manuals, there are 3 methods of backup:
the
-
the
- Streaming replication the
- Copy database files the
- SQL dump
They all have their own characteristics, so we use all of these methods.
Streaming replication
Setup streaming replication is well described in articles here and here. The meaning of this replication is that if the primary server goes down, the slave can quickly make a master and work with him, because the slave is a complete copy of the database.
In streaming replication is of great importance to the WAL files. This is the file where the slave pulls the missing data, if the master they are no longer there. Hence there is a need to store the WAL-files longer. We store these files 8 days.
Directory with the WAL files should be available as the master (write) and slave (read-only). To ensure this, we have created a shared repository on the basis of glusterFS, which was pazmontirovanie on both servers. Thus, the first, achieved greater reliability (the fall of the wizard of wal-files will be available to the slave), and secondly there is the ability to quickly create additional slaves, who also need these wal.
Summary: streaming replication protects against failure of the server, with almost no data lost.
Copying DB files
With the fall of the servers figured out, now let's deal with the human factor, when for some reason deleted the data in the tables, either table or database. In this case you have to restore data from a backup. Another copy of the database may need to test the application. This copy can be done in two ways — make a database dump or copy the entire directory with the data. For a long time, we used the first option — danile database to a file. But dump has a big disadvantage —
Now to backup the database we use the tool pg_basebackup, which essentially copies all the database files into one big files.
We keep 4 copies of weekly and 6 monthly. Copies are stored on the same storage GlusterFS. The copy we create so that they were self-sufficient, that is working immediately after deployment, without the need to download additional WAL files. So we can easily deploy the database, for example, three months ago.
It is noteworthy that the pg_basebackup utility can be run (under certain conditions) on the slave server, so the backup is absolutely not a load master.
To pg_basebackup worked on the slave, you need to enable saving WAL files that install option in postgresql.conf:
the
wal_level = hot_standby
wal_keep_segments = 1000
1000 is the number of wal files that postgreSQL stores on disk. You may need to increase or decrease this setting. The fact that pg_basebackup simply archives the contents of the database, but the backup some of the data have changed, and these changed data PostgreSQL then when you pull up the recovery from WAL files. For this pg_basebackup will retain in an archive all existing WAL files. So that all went well, you need to be available all WAL files since the start of pg_basebackup work until its completion. If you need the WAL files are deleted, pg_basebackup will fail. The right amount of wal_keep_segments can be determined empirically.
the
/usr/bin/pg_basebackup -U postgres -D /tmp/pg_backup -Ft-z -Xf
-F tells us that you need to keep everything in one file-z — that need to archive-Xf — to include in the archive the WAL files. Without the inclusion of WAL files backup will work, but when you restore Postgres will require to provide the missing WAL files.
Backup we do at the crown on Saturday this script:
the
#!/bin/bash
mkdir /tmp/pg_backup
/usr/bin/pg_basebackup -U postgres -D /tmp/pg_backup -Ft-z -Xf
WEEK=$(date +"%V")
MONTH=$(date +"%b")
let "INDEX = WEEK % 5"
test-e /collector/db-backup/base.${INDEX}.tar.gz && rm /collector/db-backup/base.${INDEX}.tar.gz
cp /tmp/pg_backup/base.tar.gz /collector/db-backup/base.${INDEX}.tar.gz
test-e /collector/db-backup/base.${MONTH}.tar.gz && rm /collector/db-backup/base.${MONTH}.tar.gz
ln /collector/db-backup/base.${INDEX}.tar.gz /collector/db-backup/base.${MONTH}.tar.gz
test-e /collector/db-backup/base.last.tar.gz && rm /collector/db-backup/base.last.tar.gz
ln /collector/db-backup/base.${INDEX}.tar.gz /collector/db-backup/base.last.tar.gz
rm -r /tmp/pg_backup
This creates a file with 3 suffixes: with week number, month name and official last.
To restore the database from backup, you need to stop PostgreSQL, remove (or move) the old data from the data-directory, and unpack there the content of the archive and start the server. There is one interesting moment. Since pg_basebackup we did on the slave, the data is unzipped and file recovery.conf. So, if we want to restore data to the latest possible state, then this file need to leave, in this case, after starting the Postgres server will start to pull the WAL files from the location you specified in the recovery.conf. If you take the last weekly backup, then we will have all the necessary WAL files (since they are stored 8 days), and we will be able to restore the database to the latest normal state.
If we need data as at the time of creating backup copies before you start the database you need to uninstall file recovery.conf.
Backups we also use for testing purposes, for one is checked and the correctness of the backup.
Name base.last.tar.gz used to recover the last copy in the test database. The test database we restored every night this script:
the
#!/bin/bash
/etc/init.d/postgresql stop
rm-r /data/*
tar-zxf /collector/db-backup/base.last.tar.gz -C /data/
rm /data/recovery.conf
/etc/init.d/postgresql start
Summary: back up database on the file level will protect from software failures and human errors. When the database is restored from backup will I lose the latest data.
SQL dump
We don't do a full SQL dump of a big database, but doing a dump of the changed tables, 1 table in 1 file. This allows you to quickly recover data in cases when corrupted only one table — no need to unpack the entire database from backup.
PostgreSQL provides us with statistics on the number of changes in the tables, and every night we look at what tables were changed and their denim. The statistics look about this request:
the
select schemaname,relname,n_tup_ins+n_tup_upd+n_tup_del from pg_stat_user_tables ;The result: an SQL dump will help to restore minor bugs. The data will be current at the time of the dump.
In conclusion
As you can see, to protect yourself from losing data, and you need to use all the features of PostgreSQL, especially because it is not so difficult.
Комментарии
Отправить комментарий