Posts Tagged ‘segment’

How to dump and restore Zabbix database to do monitoring database snapshot, PostGreSQL continuous archiving Point in time recovery setup

Monday, November 21st, 2022

postgresql-db-backup-and-continuous-archiving-on-linux-keep-zabbix-backup-regularly

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

How to configure VIVACOM 3g USB ( internet ) modem HUAWEI Mobile broadband E173 on Debian and Ubuntu GNU / Linux

Wednesday, July 4th, 2012

sakis3g-configure-usb-modem-kdialog-shot

I've been given a HUAWEI Mobile Broadband E173 USB 3g model. The USB modem contains a flash USB Storage segment storing a little install program dedicated to make the modem work fine on Microsoft Windows XP / Vista / 7 and probably other M$ OSes. I'm a long time DebianGNU / Linux user and as a free software enthusiast I ofcourse wanted to be able to use Vivacom's 3G USB Modem on my Linux powered notebook.

Thanksfully as I've red on Vivacom's website the modem supports Linux OS 🙂

For those unaware in Bulgaria there are currently 3 major GSM network providers providing 3G internet this are;;;
 

  • VIVACOM – The ex Government ran national company BTC (Bulgarian Telecommunication Company)
  • M-Tel – The first GSM network provider that entered Bulgaria around year 1995
  • GLOBUL – The 3rd and last GSM mobile and net provider entered last and not so much used by Bulgarians today

Until today I had no experience in running any 3G modems on Linux, neither I had used the 3 networks 3G internet to determine which one is best, however I've been given for temporal use a VIVACOM 3G internet modem today so I proceeded to try installing it on my Debian host.

My Linux system is a bit strangely configured as I use wicd network connection manager -( wicd-gtk ) to manage wireless and LAN connections instead of the standard installed GNOME network manager – available through package ( network-manager-gnome ).

The reason I use wicd is not that it is so much better than GNOME network manger but rather for historical reasons because few years past I had impression it works better in connecting me to wireless networks. Another reason why I choosed wicd back then was the nice looking stats …

I tried plugging in the Vivacom USB 3G modem stick and checked in wicd to see if I can see a possibility to connect to the mobile opeartor 3G network but unfortunately nothing appeared.

Though the 3G adsl modem was unavailable straing in wicd, checking about it in the list of attached USB devices I could see it detected, e.g.:

noah:~# lsusb |grep -i huawei
Bus 001 Device 007: ID 12d1:1c05 Huawei Technologies Co., Ltd.

This was at least a good sign pointing me to the thoughts that the modem is probably gonna work.

I did a quick Google search to see if other people succeded running the device on a Linux host and came across a few blog posts in Bulgarian explaining a "success story" on Ubuntu Linux through using a tweakened shell script – sakis3g. For more on how the script works and script download check out Sakis3g

Here is a quote from sakis3g's website describing the script:
 

It automagically setups your USB or Bluetooth™ modem, and may even detect operator settings.
You should try it when anything else fails!

Sakis3g has different versions designed for for plenty of spacific hware architectures i.e. for (i386, amd64, armv4t, armv5t).
There is also a version of the script which by the way contains a combination of bash shell scripting instruction and some binary exec data.

To run sakis3g on my laptop I did:

1. Download sakis3g

My notebook architecture is 64 bit so I download and used the amd64 version of the script;;;

hipo@noah:~$ mkdir sakis3g
hipo@noah:~$ cd sakis3g
hipo@noah:~/sakis3g$ wget http://www.sakis3g.org/versions/latest/amd64/sakis3g.gz

I've made also a mirror of sakis3g i386, 64 bit and all architecture the mirrors just in case it disappears in future. The mirror versions of sakis3g are here:

a. sakis3g i386 b. sakis3g amd64 c. sakis3g all architectures source

2. Unarchive and make it executable

After downloading it as it is in gzip I had to do the usual de-gzipping and making the file executable;;;

hipo@noah:~/sakis3g$ /bin/gzip -d sakis3g.gz
hipo@noah:~/sakis3g$ chmod +x sakis3g

The script is then ready to run by either clicking twice on it or (as I prefer for debugging reasons to run it in terminal):

hipo@noah:~$ ./sakis3g

Something that I have wondered a bit was the dialog where I had to fill in some data of some variable APN abbreviation for – (Access Point Name)

The APN host for VIVACOM mobile internet is;;;
APN: internet.vivacom.bg

I've used the Windows configuration progrma to gather also the following data that I thought might be important for configuring the 3G adsl modem on the Linux host;;;

Auth: *99#
User: VIVACOM
pass: VIVACOM

Here are all the configuration screenshots I've taken from sakis3g and all the data that I filled in.
Next the following tiny window appeared on screen:

Sakis3g configure usb modem kdialog shot 1 VIVACOM USB Modem Sakis 3g Shot 2 sakis 3g usb modem vivacom connect screenshot 2 vivacom 3g modem linux sakis3g enter pin dialog shot 4 Sending pin screenshot 5 sakis3g APN Dialog sakis3g screenshot 6sakis3g Internet Linux VIVACOM screenshot 7sakis3g Debian GNU Linux VIVACOM 3g Internet screenshot 8sakis3g initializing modem screenshot 9sakis3g successful connect to VIVACOM mobile 3g usb adls modem shot 10

Well that's all folks, now sakis3g succesfully connected to the I_net via an (PPP) VPN connection tunnel here is data from ifconfig command showing the succesful 3G connection to VIVACOM;;;

noah:~# /sbin/ifconfig ppp0
ppp0 Link encap:Point-to-Point Protocol
inet addr:10.58.146.232 P-t-P:10.64.64.64 Mask:255.255.255.255
UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1500 Metric:1
RX packets:2066 errors:1 dropped:0 overruns:0 frame:0
TX packets:1609 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:3
RX bytes:2232058 (2.1 MiB) TX bytes:341693 (333.6 KiB)

The internet via the 3G connection is not blazing fast but good enough to check your mail or read some webpages. VIVACOM currently has different (traffic limited packages) for their 3G internet, I'm not sure which package exactly is the 3G USB stick modem but probably the "quick" internet connection that is now would slow down once the traffic limit is reached …
Hope this post helps someone to configure 3G internet on VIVACOM in Debian and Ubuntu Linux. Though I've tested sakis3g on Debian it should work with no hassles on any other GNU Linux distribution that has bash installed.

How to fix “imapd-ssl: Maximum connection limit reached for ::ffff:xxx.xxx.xxx.xxx” imapd-ssl error

Saturday, May 28th, 2011

One of the mail server clients is running into issues with secured SSL IMAP connections ( he has to use a multiple email accounts on the same computer).
I was informed that part of the email addresses are working correctly, however the newly created ones were failing to authenticate even though all the Outlook Express email configuration was correct as well as the username and password typed in were a real existing credentials on the vpopmail server.

Initially I thought, something is wrong with his newly configured emails but it seems all the settings were perfectly correct!

After a lot of wondering what might be wrong I was dumb enough not to check my imap log files.

After checking in my /var/log/mail.log which is the default log file I’ve configured for vpopmail and some of my qmail server services, I found the following error repeating again and again:

imapd-ssl: Maximum connection limit reached for ::ffff:xxx.xxx.xxx.xxx" imapd-ssl error

where xxx.xxx.xxx.xxx was the email user computer IP address.

This issues was caused by one of my configuration settings in the imapd-ssl and imap config file:

/usr/lib/courier-imap/etc/imapd

In /usr/lib/courier-imap/etc/imapd there is a config segment called
Maximum number of connections to accept from the same IP address

Right below this commented text is the variable:

MAXPERIP=4

As you can see it seems I used some very low value for the maximum number of connections from one and the same IP address.
I suppose my logic to set such a low value was my desire to protect the IMAP server from Denial of Service attacks, however 4 is really too low and causes problem, thus to solve the mail connection issues for the user I raised the MAXPERIP value to 50:

MAXPERIP=50

Now to force the new imapd and imapd-ssl services to reload it’s config I did a restart of the courier-imap, like so:

debian:~# /etc/init.d/courier-imap restart

That’s all now the error is gone and the client could easily configure up to 50 mailbox accounts on his PC 🙂

How to solve qmail /usr/local/bin/tcpserver: libc.so.6: failed to map segment from shared object: Cannot allocate memory

Saturday, April 30th, 2011

If you’re building (compiling) a new qmail server on some Linux host and after properly installing the qmail binaries and daemontools, suddenly you notice in readproctitle service errors: or somewhere in in qmail logs for instance in/var/log/qmail/current the error:

/usr/local/bin/tcpserver: error while loading shared libraries:
libc.so.6: failed to map segment from shared object: Cannot allocate memory

then you have hit a bug caused by insufficient memory assigned for tcpserver in your /var/qmail/supervise/qmail-smtpd/run daemontools qmail-smtpd initialize script:

This kind of issue is quite common especially on hardware architectures that are 64 bit and on Linux installations that are amd65 (x86_64) e.g. run 64 bit version of Linux.

It relates to the 64 bit architecture different memory distribution and thus as I said to solve requires increase in memory softlimit specified in the run script an example good qmail-smtpd run script configuration which fixed the libc.so.6: failed to map segment from shared object: Cannot allocate memory I use currently is as follows:

#!/bin/shQMAILDUID=`id -u vpopmail`NOFILESGID=`id -g vpopmail`MAXSMTPD=`cat /var/qmail/control/concurrencyincoming`# softlimit changed from 8000000exec /usr/local/bin/softlimit -m 32000000 /usr/local/bin/tcpserver -v -H -R -l 0 -x /home/vpopmail/etc/tcp.smtp.cdb -c "$MAXSMTPD"
-u "$QMAILDUID" -g "$NOFILESGID" 0 smtp
/var/qmail/bin/qmail-smtpd
/home/vpopmail/bin/vchkpw /bin/true 2>&1

The default value which was for softlimit was:

exec /usr/local/bin/softlimit -m 8000000

A good softlimit raise up values which in most cases were solving the issue for me are:

exec /usr/local/bin/softlimit -m 3000000

or exec /usr/local/bin/softlimit -m 4000000

The above example run configuration fixed the issue on a amd64 debian 5.0 lenny install, the server hardware was:

CPU: Intel(R) Core(TM)2 Duo CPU @ 2.93GHz
System Memory: 4GB
HDD Disk space: 240GB

The softlimit configuration which I had to setup on another server with system parameters:

Intel(R) Core(TM) i7 CPU (8 CPUS) @ 2.80GHz
System Memory: 8GB
HDD Disk Space: 1.4Terabytes

is as follows:

#!/bin/sh
QMAILDUID=`id -u vpopmail`
NOFILESGID=`id -g vpopmail`
MAXSMTPD=`cat /var/qmail/control/concurrencyincoming`
exec /usr/bin/softlimit -m 64000000
/usr/local/bin/tcpserver -v -H -R -l 0
-x /home/vpopmail/etc/tcp.smtp.cdb -c "$MAXSMTPD"
-u "$QMAILDUID" -g "$NOFILESGID" 0 smtp
/var/qmail/bin/qmail-smtpd
/home/vpopmail/bin/vchkpw /bin/true 2>&1

If none of the two configurations pointed out in the post works, for you just try to manually set up the exec /usr/bin/softlimit -m to some high value.

To assure that the newly set value is not producing the same error you will have to, reload completely the daemontools proc monitor system.
To do so open /etc/inittab comment out the line:

SV:123456:respawn:/command/svscanboot
to
#SV:123456:respawn:/command/svscanboot

Save again /etc/inittab and issue te cmd:

linux:~# init q

Now again open /etc/inittab and uncomment the commented line:

#SV:123456:respawn:/command/svscanboot to
SV:123456:respawn:/command/svscanboot

Lastly reload the inittab script once again with command:

linux:~# init q

To check if the error has disappeared check the readproctitle process, like so:

linux:~# ps ax|grep -i readproctitle

The command output should produce something like:

3070 ? S 0:00 readproctitle service errors: .......................................

Hope that helps.