Databases "hot" backup : PostgreSQL

I hadn't installed Postgres in a while and apparently my current software stack doesn't require it.  So I first had to install from the repos:

sudo apt-get install postgresql

I loaded some sample data using a tutorial available from https://www.postgresqltutorial.com/load-postgresql-sample-database.  However I had to fix the "CREATE DATABASE" statement in this file, apparently the codepage 1252 is specified (Windows) and my Linux Mint PostgreSQL didn't like it.  So :

• CREATE DATABASE dvdrental WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252';
  

was replaced by:

• CREATE DATABASE dvdrental WITH TEMPLATE = template0 ENCODING = 'UTF8';

The pg_dumpall command is supposedly able to backup everything.  Trying manually first:

• sudo -u postgres pg_dumpall -f /var/tmp/postgres.backup.`date +%Y%m%d_%H%M%S`.psql

That worked fine, so added it in postgres's crontab:

$ sudo mkdir /var/backups/postgres

$ sudo chown postgres:postgres /var/backups/postgres

$ sudo crontab -lu postgres

# m h  dom mon dow   command

50 16 * * *  pg_dumpall -f /var/backups/postgres/postgres.backup.`date +%Y%m%d_%H%M%S`.psql

Well now that doesn't work anymore, for some reason the datestamp part of the command doesn't work quite right under cron.  So I wrote a short script to do it:

$ more dailybackup.sh
#! /bin/sh

pg_dumpall -f /var/backups/postgres/postgres.backup.`date +%Y%m%d_%H%M%S`.psql

That's OK now.

$ sudo crontab -lu postgres
# m h  dom mon dow   command
10 17 * * * /home/luc/Projects/dvdrentals/dailybackup.sh
$ ls -l /var/backups/postgres
total 96
-rw-rw-r-- 1 postgres postgres 45710 nov.  30 17:06 postgres.backup.20201130_170614.psql
-rw-rw-r-- 1 postgres postgres 45710 nov.  30 17:10 postgres.backup.20201130_171001.psql

 

/var was still too small...

 

So : 

mkdir  /var/backups/urbackup/tmp

chown urbackup:urbackup  /var/backups/urbackup/tmp

Then in  /etc/default/urbackupsrv :

 DAEMON_TMPDIR="/var/backups/urbackup/tmp"

Current disks installed in backup server

sda

• small 150GB Hitachi system disk
• includes root, swap and /var partitions
• Linux should be able to boot from just this disk
  

luc@kid2:~$ sudo fdisk -l /dev/sda
Disk /dev/sda: 149,5 GiB, 160041885696 bytes, 312581808 sectors
Disk model: Hitachi HDP72501
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x676b6100

Device     Boot     Start       End   Sectors  Size Id Type
/dev/sda1  *           63  25173854  25173792   12G 27 Hidden NTFS WinRE
/dev/sda2        25174016 193069055 167895040 80,1G 83 Linux
/dev/sda3       193071104 201860166   8789063  4,2G 82 Linux swap / Solaris
/dev/sda4       233072640 312581807  79509168 37,9G 83 Linux 

sdb

• smallish 300GB Seagate system disk
• used by a /home partition
• any data remaining on this partition should probably migrate to the laptop (or external)
  

luc@kid2:~$ sudo fdisk -l /dev/sdb
Disk /dev/sdb: 298,9 GiB, 320072933376 bytes, 625142448 sectors
Disk model: ST320DM000-1BC14
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x0008daeb

Device     Boot Start       End   Sectors   Size Id Type
/dev/sdb1  *     2048 625141759 625139712 298,1G 83 Linux

sdc

• 2TB Seagate system disk, recently installed
• is BTRFS devid 2
  

  luc@kid2:~$ sudo fdisk -l /dev/sdc
Disk /dev/sdc: 1,84 TiB, 2000398934016 bytes, 3907029168 sectors
Disk model: ST2000DM001-1ER1
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes

sdd

• 2TB HP system disk, recently reformatted
• is BTRFS devid1

 luc@kid2:~$ sudo fdisk -l /dev/sdd
Disk /dev/sdd: 1,84 TiB, 2000398934016 bytes, 3907029168 sectors
Disk model: MB2000GDUNV
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Adding a second drive to BTRFS

luc@kid2:~$ sudo btrfs device add -f /dev/sdc /var/backups/urbackup/
luc@kid2:~$ sudo btrfs fi show
Label: none  uuid: 9c8025a0-5351-4807-b7ac-97ffa87ededa
        Total devices 2 FS bytes used 447.00GiB
        devid    1 size 1.82TiB used 450.02GiB path /dev/sdd
        devid    2 size 1.82TiB used 0.00B path /dev/sdc

Making room on /var for urbackup

Well /var/urbackup is now too small. Or rather, the / filesystem is (almost) full due to /var/log (4GB) and /var/urbackup (12GB) competing for getting a few more gigabytes from the 37GB root filesystem. It turns out I still have a Windows partition on /dev/sda2 (80GB) which isn't used anymore, never boots up, doesn't seem like it contains any valuable data... so perfect candidate for a new /var filesystem

1. fdisk /dev/sda : change type to 83 for partition 2
2. mkfs.ext4 /dev/sda2
3. blkid /dev/sda2 and copy the UUID
4. vi /etc/fstab and make a line to mount /var.  Stupid me, I had defined that line at the end of fstab, it should have been before the line for /var/backup/urbackup obviously

So after fixing this, urbackup should be able to take backups again.  But while I was there, I also opened the box and removed the failed Western Digital 2TB drive, and inserted a Seagate 2TB that I had lying around.  Looks like this is working fine now as a new /dev/sdc.  I will check it and then add it to the BTRFS later on.

 Also took note of /dev/sdd being labeled HP Enterprise 2TB.  This is the one still working fine after the WD disaster.

Re-create BTRFS lost filesystem

fdisk /dev/sdc, removed /dev/sdc1 partition so I can start over on the bare device. sdc is the Western Digital 2TB disk:

luc@kid2:~$ sudo fdisk -l /dev/sdc
Disk /dev/sdc: 1,84 TiB, 2000398934016 bytes, 3907029168 sectors
Disk model: WDC WD20EARX-00P
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes

Creating the first device on new BTRFS :

luc@kid2:~$ sudo mkfs.btrfs -f /dev/sdc
btrfs-progs v5.4.1 
See http://btrfs.wiki.kernel.org for more information.

Label:              (null)
UUID:               b12623df-e351-4d48-856b-867e38361682
Node size:          16384
Sector size:        4096
Filesystem size:    1.82TiB
Block group profiles:
  Data:             single            8.00MiB
  Metadata:         DUP               1.00GiB
  System:           DUP               8.00MiB
SSD detected:       no
Incompat features:  extref, skinny-metadata
Checksum:           crc32c
Number of devices:  1
Devices:
   ID        SIZE  PATH
    1     1.82TiB  /dev/sdc

So we have an unmounted btrfs filesystem, let's take note of the UUID and update fstab:

luc@kid2:~$ sudo btrfs fi show /dev/sdc
Label: none  uuid: b12623df-e351-4d48-856b-867e38361682
	Total devices 1 FS bytes used 128.00KiB
	devid    1 size 1.82TiB used 2.02GiB path /dev/sdc
luc@kid2:~$ sudo vi /etc/fstab     
luc@kid2:~$ sudo tail /etc/fstab 
UUID=2a51d4ad-e97f-4d6c-bde4-a44ebd1ff267 /home           ext4    defaults        0       2
# swap was on /dev/sdb3 during installation
UUID=5690db75-3d2a-4171-a789-b5179a501942 none            swap    sw              0       0
/dev/fd0        /media/floppy0  noauto    rw,user,noauto,exec,utf8 0       0

# urbackup data
UUID=b12623df-e351-4d48-856b-867e38361682 /var/backups/urbackup	btrfs defaults 0 3
#UUID=4881245e-bed5-4526-b9ff-3b5ba39b283d /var/backups/urbackup	btrfs defaults 0 3

luc@kid2:~$ sudo mount /var/backups/urbackup/
luc@kid2:~$ 

No error message. However the mount didn't happen. Apparently the system still "remembers" of a zombie BTRFS partition that used to be mounted there, from /dev/sdd. I could mount the new one (/dev/sdc) manually on another mount point (/mnt) but not on /var/backups/urbackup where I need it.

I ended up using fdisk to wipe out the BTRFS label (?) on /dev/sdd by creating then removing /dev/sdd1. After that I rebooted and the system happily mounted /dev/sdc thanks to my previous update in /etc/fstab. However there was another slight problem with permissions, the mount is only accessible by root after this point. It still needed some chown/chmod commands:

luc@kid2:/var/backups/urbackup$ sudo -u urbackup ls
luc@kid2:/var/backups/urbackup$ sudo -u urbackup touch a
touch: cannot touch 'a': Permission denied
luc@kid2:/var/backups/urbackup$ sudo chown root:urbackup .
luc@kid2:/var/backups/urbackup$ cd ..
luc@kid2:/var/backups$ sudo chmod g+w urbackup/

The Urbackup server immediately started backing up two clients. Unfortunately on one of them the external USB drive wasn't mounted so that failed. I restarted afterwards, and asked for a full backup. But that failed soon enough also, this time the new BTRFS file system had been remounted ro (readonly). Clearly something really wrong is going on with that disk drive (/dev/sdc):

luc@kid2:/var/log$ sudo fdisk /dev/sdc

Welcome to fdisk (util-linux 2.34).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

fdisk: cannot open /dev/sdc: Input/output error

So I started back doing the same BTRFS re-creation on /dev/sdd this time. That worked fine but of course I now have only half the capacity to take backups. It should be just enough though for the moment, the server displays statistics going up to 1701GB at the moment.