If you have set up a Zabbix server that is using as a database storage postgresql at home or company, then you will need to make sure you don’t loose any data by producing regular postgresql backups. This small article will show how to do the db backup in conventional way with pg_dump / pg_restore as well as how to set the continuous archiving point in time recovery end point recovery so called PITR..
1. Connect to the database and some postgres very basics
-bash-4.2$ psql DBNAME USERNAME
-bash-4.2$ psql zabbix zabbix
After you access a PostgreSQL database, you can run SQL queries and more. Here are some common psql commands:
• To view help for psql commands, type \?.
• To view help for SQL commands, type \h.
• To view information about the current database connection, type \conninfo.
• To list the database's tables and their respective owners, type \dt.
• To list all of the tables, views, and sequences in the database, type \z.
• To exit the psql program, type \q.
Fundamental backup approaches with postgres
As with everything that contains valuable data, PostgreSQL databases should be backed up regularly. While the procedure is essentially simple, it is important to have a clear understanding of the underlying techniques and assumptions.
There are three fundamentally different approaches to backing up PostgreSQL data:
• SQL dump
• File system level backup
• Continuous archiving
Each has its own strengths and weaknesses; each is discussed in turn in the following sections.
2. Creating SQL Dump of postgresql target database
2.1 Manual SQL dump (custom dump format)
Use pg_dump's custom dump format. If PostgreSQL was built on a system with the zlib compression library installed, the custom dump format will compress data as it writes it to the output file.
This will produce dump file sizes similar to using gzip, but it has the added advantage that tables can be restored selectively.
The following command dumps a database using the custom dump format:
# sudo su – postgres
-bash-4.2% cd /var/lib/pgsql/9.5/backups/
-bash-4.2$ pg_dump -Fc zabbix > /var/lib/pgsql/9.5/backups/zabbixdbdump`date "+%d%m%y%H%M%S"`.gz
2.2 Schedule backups with cron job
To automate the job schedule a cron job to do the work, somethjing like below:
# Minute Hour Day of Month Month Day of Week Command
# (0-59) (0-23) (1-31) (1-12 or Jan-Dec) (0-6 or Sun-Sat)
@daily pg_dump -Fc zabbix > /var/lib/pgsql/9.5/backups/Zabbix_db_dump`date "+\%d\%m\%y\%H\%M\%S"`.gz
3. Restore a database from the dump file
3.1. Restoring a database using pg_restore (usually used)
A custom-format dump is not a script for psql, but instead must be restored with pg_restore, for example:
# pg_restore -d zabbix /var/lib/pgsql/9.5/backups/Zabbix_db_dump281118151005.gz
3.2. Second Option restoing with psql after creating a restore database
# su postgres-bash-4.2$ psql template1
CREATE DATABASE zabbix OWNER zabbix ENCODING 'UTF8';
\q
-bash-4.2$ psql zabbix < /var/lib/pgsql/9.5/backups/zabbixdbdump291117151002.gz
exit
NOTE: if you get a permission denied error when trying to restore, check the Unix permissions on the backup file and all the parent directories.
4. Continuous Archiving and Point-in-Time Recovery (PITR) backups
At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's data directory. The log records every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by "replaying" the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the file system backup and then replay from the backed-up WAL files to bring the system to a current state. This approach is more complex to administer than either of the previous approaches, but it has some significant benefits:
Setting Up the WAL Archiving
In an abstract sense, a running PostgreSQL system produces an indefinitely long sequence of WAL records. The system physically divides this sequence into WAL segment files, which are normally 16MB a piece (although the segment size can be altered when building PostgreSQL). The segment files are given numeric names that reflect their position in the abstract WAL sequence. When not using WAL archiving, the system normally creates just a few segment files and then "recycles" them by renaming no-longer-needed segment files to higher segment numbers. It's assumed that segment files whose contents precede the checkpoint-before-last are no longer of interest and can be recycled.
When archiving WAL data, we need to capture the contents of each segment file once it is filled, and save that data somewhere before the segment file is recycled for reuse.
Depending on the application and the available hardware, there could be many different ways of "saving the data somewhere": we could copy the segment files to an NFS-mounted directory on another machine, write them onto a tape drive (ensuring that you have a way of identifying the original name of each file), or batch them together and burn them onto CDs, or something else entirely. To provide the database administrator with flexibility, PostgreSQL tries not to make any assumptions about how the archiving will be done. Instead, PostgreSQL lets the administrator specify a shell command to be executed to copy a completed segment file to wherever it needs to go. The command could be as simple as a cp, or it could invoke a complex shell script — it's all up to you.
To enable WAL archiving, set the wal_level configuration parameter to archive or higher, archive_mode to on, and specify the shell command to use in the archive_command configuration parameter.
In practice these settings will always be placed in the postgresql.conf e.g. (/etc/postgresql/12/main/postgresql.conf) file.
In archive_command, %p is replaced by the path name of the file to archive, while %f is replaced by only the file name. (The path name is relative to the current working directory, i.e., the cluster's data directory.)
Use %% if you need to embed an actual % character in the command.
The simplest useful command to enable PITR would, be something like:
# – Archiving config setction
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = '/bin/xz -2 -z < %p > /var/lib/pgsql/9.5/archivedir/%f' # command to use to archive a logfile segment
archive_timeout = 1h # force a logfile segment switch after this
# number of seconds; 0 disables
