Backup Scripts

Backup Using LVM

If you have your Zimbra installation on its own logical volume, you can use this script:

#!/bin/bash
  
time=`date +%Y-%m-%d_%H-%M-%S`

# Modify the following variables according to your installation
#########################################

# backup_dir - directory to backup to
backup_dir=/path/to/backups/$time

# vol_group - the Volume Group that contains $zimbra_vol
vol_group=PUT_VOL_GROUPNAME_HERE

# zimbra_vol - the Logical Volume that contains /opt/zimbra
zimbra_vol=PUT_ZIMBRA_VOLNAME_HERE

# zimbra_vol_fs - the file system type (ext3, xfs, ...) in /opt/zimbra
zimbra_vol_fs=PUT_ZIMBRA_FILE_SYSTEM_TYPE_HERE

# lvcreate and lvremove commands path - 
lvcreate_cmd=`which lvcreate`
lvremove_cmd=`which lvremove`
  
# Do not change anything beyond this point
#########################################

# Test for an interactive shell
if [[ $- != *i* ]]
   then say() { echo -e $1; }
     # Colors, yo!
     GREEN="\e[1;32m"
     RED="\e[1;31m"
     CYAN="\e[1;36m"
     PURPLE="\e[1;35m"
   else say() { true; } # Do nothing
fi

# Output date
say $GREEN"Backup started at "$RED"`date`"$GREEN"."

# Stop the Zimbra services
say $CYAN"Stopping the Zimbra services..."
say $PURPLE"  This may take several minutes."
/etc/init.d/zimbra stop

# Create a logical volume called ZimbraBackup
say $GREEN"Creating a LV called ZimbraBackup:"$PURPLE
$lvcreate_cmd -L1000M -s -n ZimbraBackup /dev/$vol_group/$zimbra_vol

# Create a mountpoint to mount the logical volume to
say $GREEN"Creating a mountpoint for the LV..."
# WARNING: this is insecure!
mkdir -p /tmp/ZimbraBackup

# Mount the logical volume to the mountpoint
say $GREEN"Mounting the LV..."
# WARNING: remove nouuid option if the filesystem is not formatted as XFS !!!   
mount -t $zimbra_vol_fs -o nouuid,ro /dev/$vol_group/ZimbraBackup /tmp/ZimbraBackup/

# Start the Zimbra services
say $CYAN"Starting the Zimbra services..."
# WARNING: it's safer not to put this command in background
/etc/init.d/zimbra start &

# For testing only
#say $RED"Press Enter to continue...\e[0m"
#read input

# Create the current backup
say $GREEN"Creating the backup directory and backup..."
mkdir -p $backup_dir
tar zcvf $backup_dir/zimbra.backup.tar.gz /tmp/ZimbraBackup/zimbra/ 2&> /dev/null

# Unmount /tmp/ZimbraBackup and remove the logical volume
say $GREEN"Unmounting and removing the LV."$PURPLE
umount /tmp/ZimbraBackup/
$lvremove_cmd --force /dev/$vol_group/ZimbraBackup

# Done!
say $GREEN"Zimbra backed up to "$CYAN$backup_dir$GREEN"!"
say $GREEN"Backup ended at "$RED"`date`"$GREEN".\e[0m"

More elaborated script, using LVM and rsync

More info at https://github.com/sergevanginderachter/sysadmintools/tree/master/zimbrackup

Yet another backup script, using LVM and duplicity

The previous examples use tar and rsync respectively. Both have disadvantages:

• they don’t support incremental backups
• you need to do additional scripting to encrypt the backup and purging of old backups

I modified the first script to use duplicity instead of plain tar. This way you have the flexibility to use any protocol you like (scp, rsync, ftp) to upload the backup, you don’t have to worry about old backups and you can save a lot of disk space!

Get the script: http://www.nervous.it/2007/01/zimbra-lvm-backup-with-duplicity-volume/ Get duplicity: http://duplicity.nongnu.org/

The Perl way of doing it (without LVM)

ZCS Tools currently contains a Cold Backup script written in Perl. It also supports backup rotation. This script does not use LVM.

Currently: zimbraColdBackup-Ver0.02beta

#!/usr/bin/perl
use strict;
use warnings;
use POSIX;
use IO::Scalar; # for building up output on a string
use Proc::ProcessTable; # for killing processes
use File::Path; # for removing directories
use File::Rsync; # for syncing using rsync
use Mail::Mailer; # for sending email

#############################################################################
# Please make changes below to suit your system and requirements

# absolute path to'rsync' on your system
my $rsync       = '/usr/bin/rsync'; 

# absolute path to zimbra directory
my $zimbra      = '/opt/zimbra';

# absolute path to backup directory. ensure that it exists!
my $backup_dir  = '/backup';

# do you want to rotate backups?
my $rotate_bak  = 1; # 1 = yes, 0 = no

# if yes, after how many days?
# make sure that you don't specify '0'. Specifying zero will delete even
# the latest backup - that is, the backup taken today and you will end up
# with no data!
my $rotate_days = 7;

# do you want to send the backups to a remote location? (using rsync)
my $send_remote = 0; # 1 = yes, 0 = no

# if you would like to use Rsync to send to remote location:
# please enter the destination server below
# (before using the script make sure that you have password-less and
# passphrase-less SSH login setup using private / public cryptography
# this script will neither provide SSH password nor the passphrase
my $ssh_server  = 'host.domain.com'; # SSH server IP or hostname
my $remote_path = '/backups'; # path on remote server to send backup

# Finally:
# Do you want to have the results of backup emailed?
my $send_result = 1; # 1 = yes, 0 = no

# if yes, to whom whould it be emailed?
my $to_email    = 'sm***@si*.in';

# CC email (optional: you can leave this empty)
my $cc_email    = '';

# BCC email (optional: you can leave this empty)
my $bcc_email   = '';

# Sender / From email (it will look like the email arrive from this person)
my $from_email  = 'root@localhost';

# That's it!
# Don't edit below this line unless you know what you're doing
#############################################################################

my $zimbra_user = 'zimbra';
my $prog_name = $0;

# properties of this program

# name of program
my $progname    = "zimbraColdBackup";
# version number
my $version     = "0.2Beta";
# revision number (independent of version number)
my $revision    = "30";
# license under which distributed
my $license     = qq(
#############################################################################
# Program Name      $progname                                        #
# Program Version   $version                                                 #
# Program Revision  $revision                                                      #
#                                                                           #
# This script can be used to backup Zimbra Collaboration Suite 4.0.0 GA     #
#                                                                           #
# Most recent version of this script can be downloaded from:                #
# http://sourceforge.net/projects/zcstools/                                 #
#                                                                           #
# Copyright (C) 2006 Chintan Zaveri                                         #
# E-mail: smile\@sis.net.in                                                  #
#                                                                           #
# This program is free software; you can redistribute it and/or modify      #
# it under the terms of the GNU General Public License version 2, as        #
# published by the Free Software Foundation.                                #
#                                                                           #
# This program is distributed in the hope that it will be useful,           #
# but WITHOUT ANY WARRANTY; without even the implied warranty of            #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the             #
# GNU General Public License for more details.                              #
#                                                                           #
# You should have received a copy of the GNU General Public License along   #
# with this program; if not, write to the Free Software Foundation, Inc.,   #
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.               #
#############################################################################
);

# usage text
my $usage = qq(
Usage:
    zimbraColdBackup OPTION

    help|usage|?|--help|--usage
        All of these options, or providing no options will print this text
        about usage.

    overview|--overview
        What this script does.

    install|installation|--install|--installation
        These options will display a text on installation of this script.

    confirm|--confirm
        These options will run the backup procedure.

    version|--version
        These options will print the Name, Version and Revision Number for
        this script.

    license|licence|lisense|lisence|--license|--licence|--lisense|--lisence
        These options will print the License under which this program is
        distributed.
);

# overview text
my $overview = qq(
Overview:

This script can be used to take off-line backup of Zimbra Collaboration Suite.

The following is the series of actions undertaken by this script:

1. Stop Zimbra
2. Backup Zimbra in the specified local directory using Rsync
3. Start Zimbra

Optionally, if you specified, this script will also do the following:

1. Rotate the backups
2. Send the backup to another system using Rsync
3. Email the results of backup
);

# installation text
my $installation = qq(
Installation:

It is fairly easy to install this script. The installation requires you to do
the following:

1. Install all required Perl modules
2. Configure this script
3. Run it once - Test it!
4. Schedule it using crontab

1. Install all required Perl modules

The best way to do this is by running the script. Once you run the script, you
would receive an error message similar to the following:

Can't locate Mail/Mailer.pm in \@INC (\@INC contains:
/usr/lib/perl5/5.8.5/i386-linux-thread-multi /usr/lib/perl5/5.8.5
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.4/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.2/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.1/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl/5.8.4
/usr/lib/perl5/site_perl/5.8.3 /usr/lib/perl5/site_perl/5.8.2
/usr/lib/perl5/site_perl/5.8.1 /usr/lib/perl5/site_perl/5.8.0
/usr/lib/perl5/site_perl
/usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.4/i386-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.2/i386-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.1/i386-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl/5.8.4
/usr/lib/perl5/vendor_perl/5.8.3 /usr/lib/perl5/vendor_perl/5.8.2
/usr/lib/perl5/vendor_perl/5.8.1 /usr/lib/perl5/vendor_perl/5.8.0
/usr/lib/perl5/vendor_perl .) at ./ZimbraColdBackup.pl line 9.
BEGIN failed--compilation aborted at ./zimbraColdBackup.pl line 9.

In the first line you can see that it is unable to locate Mail/Mailer.pm

To install this module, just type the following:

perl -MCPAN -e 'install Mail::Mailer'

This command will install the Mail::Mailer module.

Remember, the "/" must be converted to "::" when providing the command and
the ".pm" must be removed.

You may receive such errors for a few times. Just install the relevant
modules.

2. Configure the script

Once the modules are installed, you need to open this script in a text editor,
such as "vi" or "nano". Please enter correct values against the variables at
the top of the script. Once you open it in a text editor, it will become
self-explanatory.

3. Run it once - Test it!

Just say: "./zimbraColdBackup confirm" after you have configured it. It should
run and do as promised. If it doesn't just let me know, or ensure that it has
been properly configured.

4. Schedule it using crontab

Create a cron job using the command 'crontab -e' to run the script at fixed
intervals.
);

# parse the arguments

# if there are no arguments print usage and die
die $usage, @_ if ( $#ARGV + 1 < 1 );

# what was the argument? (ignore more than one arguments...)
my $option = $ARGV[0];

# select action
if (    ( $option =~ /^(--)?help$/ )     ||
        ( $option =~ /^(--)?usage$/ )    ||
        ( $option =~ /^\?$/) 
   ) {
        die $usage, @_;
}
elsif   ( $option =~ /^(--)?overview$/ ) {
        die $overview, @_;
}
elsif   ( $option =~ /^(--)?install(ation)?$/ ) {
        die $installation, @_;
}
elsif   ( $option =~ /^(--)?confirm$/ ) {
        1; # go ahead and run the script
}
elsif   ( $option =~ /^(--)?version$/) {
        die $progname, " Ver. ", $version, " Rev. ", $revision, "\n", @_;
}
elsif   ( $option =~ /^(--)?li[sc]en[sc]e$/ ) {
        die $license, @_;
}
else {
        die "Invalid option: Please try again", $usage, @_;
}

# going ahead and running the script :-)

# check inputs

if  ( ( $rsync eq "" ) || ( $rsync !~ /^\// ) ) {
    die "Please provide an absolute path to 'rsync'", "\n", @_;
}

if  (! ( -d $zimbra ) ) {
    die "Please provide an absolute path to 'zimbra' directory", "\n", @_;
}

if  (! ( -d $backup_dir ) ) {
    die "Please provide an absolute path to backup directory", "\n", @_;
}

if  ( $send_remote =~ /\D/ ) {
    die "Please enter either '1' or '0' in \$send_remote", "\n", @_;
}

if  ( $send_remote ) {
    # check ssh params
    if ( $ssh_server eq "" ) {
        die "Please enter valid SSH server to rsync to.", "\n", @_;
    }
}

if  ( $rotate_bak =~ /\D/ ) {
    die "Please enter either '1' or '0' in \$rotate_bak", "\n", @_;
}

if  ( $rotate_days =~ /\D/ ) {
    die "Please enter either '1' or '0' in \$rotate_days", "\n", @_;
}

if  ( $send_result =~ /\D/ ) {
    die "Please enter either '1' or '0' in \$send_result", "\n", @_;
}

if  ( $send_result ) {
    if  ( ! $to_email ) {
        die "Please enter valid email in \$to_email", "\n", @_;
    }
    if  ( ! $from_email ) {
        die "Please enter valid email in \$from_email", "\n", @_;
    }
}

# if you reach here, everything is valid, please proceed

my $result = '';
my $res_fh = IO::Scalar->new ( \$result );
# now whatever output we want to build up, we will print to $res_fh

print $res_fh "Date: ",
    POSIX::strftime ( '%m-%d-%Y, %A, %H:%M', localtime ( time ) ),
    " Hours\n";

# current day, date, month, time, ...
my $current_time = POSIX::strftime ( '%m-%d-%Y-%A-%H-%M', localtime ( time ) );
my $since_epoch = time ( ); # seconds since epoch

my $bak_dir = $backup_dir; # we want to use the backup dir path later
$backup_dir .= '/'.$current_time.'-'.$since_epoch;

# Stop Zimbra
my $zmstopstat = system ( "su - zimbra -c '$zimbra/bin/zmcontrol stop'" );
if ( $zmstopstat ) {
    print $res_fh "Stopping Zimbra: Some Problem Occurred. Please check.\n";
}
else {
    print $res_fh "Stopping Zimbra: Success\n";
}

# Kill all lingering Zimbra processes
my $zimbra_uid = getpwnam ( $zimbra_user );

my $process_table = Proc::ProcessTable->new;

# Gracefully kill lingering processes: kill -15, sleep, kill -9
foreach my $process ( @{$process_table->table} ) {
    if  ( ( $process->uid eq $zimbra_uid )          || 
        ( ( $process->cmndline =~ /$zimbra_user/ )  && 
        ( $process->cmndline !~ /$prog_name/ ) ) ) 
        {
            kill -15, $process->pid; # thanks, merlyn
            sleep 10; # not sure if there'll be buffering.
            kill -9, $process->pid;
    }
}

# Backup Zimbra using "rsync"
my $rsync_obj = File::Rsync->new ( {
    'rsync-path'    => $rsync,
    'archive'       => 1,
    'recursive'     => 1,
    'links'         => 1,
    'hard-links'    => 1,
    'keep-dirlinks' => 1,
    'perms'         => 1,
    'owner'         => 1,
    'group'         => 1,
    'devices'       => 1,
    'times'         => 1
} );

my $zmrsyncstat = $rsync_obj->exec ( { 
    src     =>  "$zimbra/", 
    dest    =>  "$backup_dir" 
} );

if ( $zmrsyncstat ) {
    print $res_fh "Rsync Zimbra: Successfully created $backup_dir\n";
}
else {
    print $res_fh "Rsync Zimbra: Some Problem Occurred. Please check.\n";
}

# Now that backup is done, start Zimbra
my $zmstartstat = system ( "su - zimbra -c '$zimbra/bin/zmcontrol start'" );
if ( $zmstartstat ) {
    print $res_fh "Starting Zimbra: Some Problem Occurred. Please check.\n";
}
else {
    print $res_fh "Starting Zimbra: Success\n";
}

print $res_fh "Zimbra was off-line for: ", time ( ) - $since_epoch, " seconds\n";

# Rotate backups
if ( $rotate_bak ) { # should we rotate backups?
    # get a list of all files from the backup directory
    opendir ( DIR, $bak_dir ) or die "can't opendir $bak_dir: $!";

    while ( defined ( my $filename = readdir ( DIR ) ) ) {
        # if $filename is . or .. do not remove it
            if ( $filename !~ /\./ ) { # if this isn't there, you're dead
            # if $filename is older than $rotation_days then delete it
            my @filename_parts = split ( "-", $filename ); # to get epoch sec
            # allowed age of backups
            my $allowed_age = $since_epoch - ( 60 * 60 * 24 * $rotate_days );
            # if the last part of $filename < allowed age
            if ( ( $filename_parts[6] < $allowed_age ) && ($filename ne "") ) {
                # delete it
                my $zmrmtreestat = rmtree ( "$bak_dir/$filename" );
                # print the status of removing
                if ( $zmrmtreestat ) {
                    print $res_fh "Rotating Backup: Removed $bak_dir/$filename\n";
                }
                else {
                    print $res_fh "Rotating Backup: Can't delete $filename\n";
                }
            }
        }
    }
    closedir ( DIR );
}

# Send to remote system
if ( $send_remote ) {
    # Backup Zimbra using "rsync"
    my $rem_rsync_obj = File::Rsync->new ( {
        'rsync-path'    => $rsync,
        'archive'       => 1,
        'recursive'     => 1,
        'links'         => 1,
        'hard-links'    => 1,
        'keep-dirlinks' => 1,
        'perms'         => 1,
        'owner'         => 1,
        'group'         => 1,
        'devices'       => 1,
        'times'         => 1
    } );
    my $destination =
$ssh_server.':'.$remote_path.'/'.$current_time.'-'.$since_epoch;
    my $zmremrsyncstat = $rem_rsync_obj->exec ( {
        src     =>  "$backup_dir/",
        dest    =>  "$destination"
    } );

    if ( $zmremrsyncstat ) {
        print $res_fh "Remote Rsync: Successfully created $destination\n";
    }
    else {
        print $res_fh "Remote Rsync: Some Problem Occurred. Please check.\n";
    }
}

print $res_fh "The backup took: ", time ( ) - $since_epoch, " seconds\n";

# Send email report
if ( $send_result ) {
    # send results by email
    my $mailer = Mail::Mailer->new ( "sendmail" );
    $mailer->open( {
        'From'      =>  $from_email,
        'To'        =>  $to_email,
        'Cc'        =>  $cc_email,
        'Bcc'       =>  $bcc_email,
        'Subject'   =>  'Result of zimbraColdBackup'
    } ) or die "Can't open: $!\n";
    print $mailer $result;
    $mailer->close();
}

# print results on std output
print $result;

NB: This script uses Mail::Mailer to send a notification using sendmail. You should make sure that sendmail is installed on your system and that it is not set to start at boot otherwise you’ll have a conflict with the Zimbra MTA not starting.

A Simple Shell Script Method

The following script can be called from the command line or crontab, and relies only on rsync, tar, and a scriptable ftp client. I used ncftp but you can use others and modify the syntax accordingly. This script was written and tested in Ubuntu 6.06 LTS server. I cannot confirm if it requires any modification to work in other distros but would appreciate feedback if necessary to make it more general.

#!/bin/bash
 
# Zimbra Backup Script
# Requires ncftp to run
# This script is intended to run from the crontab as root
# Date outputs and su vs sudo corrections by other contributors, thanks, sorry I don't have names to attribute!
# Free to use and free of any warranty!  Daniel W. Martin, 5 Dec 2008

 
# Outputs the time the backup started, for log/tracking purposes
echo Time backup started = $(date +%T)
before="$(date +%s)"

# Live sync before stopping Zimbra to minimize sync time with the services down
# Comment out the following line if you want to try single cold-sync only
rsync -avHK --delete /opt/zimbra/ /backup/zimbra

# which is the same as: /opt/zimbra /backup 
# Including --delete option gets rid of files in the dest folder that don't exist at the src 
# this prevents logfile/extraneous bloat from building up overtime.

# Now we need to shut down Zimbra to rsync any files that were/are locked
# whilst backing up when the server was up and running.
before2="$(date +%s)"

# Stop Zimbra Services
su - zimbra -c"/opt/zimbra/bin/zmcontrol stop"
sleep 15

# Kill any orphaned Zimbra processes
ORPHANED=`ps -u zimbra -o "pid="` && kill -9 $ORPHANED

# Only enable the following command if you need all Zimbra user owned
# processes to be killed before syncing
# ps auxww | awk '{print $1" "$2}' | grep zimbra | kill -9 `awk '{print $2}'`
 
# Sync to backup directory
rsync -avHK --delete /opt/zimbra/ /backup/zimbra

# Restart Zimbra Services
su - zimbra -c "/opt/zimbra/bin/zmcontrol start"

# Calculates and outputs amount of time the server was down for
after="$(date +%s)"
elapsed="$(expr $after - $before2)"
hours=$(($elapsed / 3600))
elapsed=$(($elapsed - $hours * 3600))
minutes=$(($elapsed / 60))
seconds=$(($elapsed - $minutes * 60))
echo Server was down for: "$hours hours $minutes minutes $seconds seconds"

# Create a txt file in the backup directory that'll contains the current Zimbra
# server version. Handy for knowing what version of Zimbra a backup can be restored to.
su - zimbra -c "zmcontrol -v > /backup/zimbra/conf/zimbra_version.txt"
# or examine your /opt/zimbra/.install_history

# Display Zimbra services status
echo Displaying Zimbra services status...
su - zimbra -c "/opt/zimbra/bin/zmcontrol status"
 
# Create archive of backed-up directory for offsite transfer
# cd /backup/zimbra
umask 0177
tar -zcvf /tmp/mail.backup.tgz -C /backup/zimbra .
 
# Transfer file to backup server
ncftpput -u <username> -p <password> <ftpserver> /<desired dest. directory> /tmp/mail.backup.tgz

rm /tmp/mail.backup.tgz

# Outputs the time the backup finished
echo Time backup finished = $(date +%T)

# Calculates and outputs total time taken
after="$(date +%s)"
elapsed="$(expr $after - $before)"
hours=$(($elapsed / 3600))
elapsed=$(($elapsed - $hours * 3600))
minutes=$(($elapsed / 60))
seconds=$(($elapsed - $minutes * 60))
echo Time taken: "$hours hours $minutes minutes $seconds seconds"

One further note: I have observed some odd behavior in this and other scripts that, when run from the command line work flawlessly, but when run from crontab the script may get ahead of itself and, for example, try to ftp the file before tar is done creating it; resulting in a useless backup. Loading the script into crontab with the parameters to create a log file, for example

. /etc/zimbra.backup > /temp/zbackup.log 2>&1

seems to solve this problem (while creating the log, or showing the output on the screen, the script seems to follow the sequence more carefully), while giving you a line-by-line record of the backup procedure. In my installation with just over 3GB backed up, the logfile is 2.5 mb and is overwritten each night.

NB You may find that using su on your operating system has problems and some services don’t start or stop correctly. If that’s the case use ‘sudo -u zimbra’ in the following format for the commands:

sudo -u zimbra zmcontrol start

A Simple Shell Script Method like above, but with rsync over ssh

#!/bin/bash

# Zimbra Backup Script
# Requires that you have ssh-keys: https://help.ubuntu.com/community/SSHHowto#Public%20key%20authentication
# This script is intended to run from the crontab as root
# Date outputs and su vs sudo corrections by other contributors, thanks, sorry I don't have names to attribute!
# Free to use and free of any warranty!  Daniel W. Martin, 5 Dec 2008
## Adapted for rsync over ssh instead of ncftp by Ace Suares, 24 April 2009 (Ubuntu 6.06 LTS)

# the destination directory for local backups
DESTLOCAL=/backup/backup-zimbra
# the destination for remote backups
DESTREMOTE="yourserver:/backup/backup-zimbra"

# Outputs the time the backup started, for log/tracking purposes
echo Time backup started = $(date +%T)
before="$(date +%s)"
# a backup dir on the local machine. This will fill up over time!
BACKUPDIR=$DESTLOCAL/$(date +%F-%H-%M-%S)

# Live sync before stopping Zimbra to minimize sync time with the services down
# Comment out the following line if you want to try single cold-sync only
rsync -avHK --delete --backup --backup-dir=$BACKUPDIR /opt/zimbra/ $DESTLOCAL/zimbra

# which is the same as: /opt/zimbra /backup 
# Including --delete option gets rid of files in the dest folder that don't exist at the src 
# this prevents logfile/extraneous bloat from building up overtime.
# the backupdir will hold all files that changed or where deleted during the previous backup

# Now we need to shut down Zimbra to rsync any files that were/are locked
# whilst backing up when the server was up and running.
before2="$(date +%s)"

# Stop Zimbra Services
/etc/init.d/zimbra stop
#su - zimbra -c"/opt/zimbra/bin/zmcontrol stop"
#sleep 15
# Kill any orphaned Zimbra processes
#kill -9 `ps -u zimbra -o "pid="`
pkill -9 -u zimbra

# Only enable the following command if you need all Zimbra user owned
# processes to be killed before syncing
# ps auxww | awk '{print $1" "$2}' | grep zimbra | kill -9 `awk '{print $2}'`

# Sync to backup directory
rsync -avHK --delete --backup --backup-dir=$BACKUPDIR /opt/zimbra/ $DESTLOCAL/zimbra

# Restart Zimbra Services
#su - zimbra -c "/opt/zimbra/bin/zmcontrol start"
/etc/init.d/zimbra start

# Calculates and outputs amount of time the server was down for
after="$(date +%s)"
elapsed="$(expr $after - $before2)"
hours=$(($elapsed / 3600))
elapsed=$(($elapsed - $hours * 3600))
minutes=$(($elapsed / 60))
seconds=$(($elapsed - $minutes * 60))
echo SERVER WAS DOWN FOR: "$hours hours $minutes minutes $seconds seconds"

# Create a txt file in the backup directory that'll contains the current Zimbra
# server version. Handy for knowing what version of Zimbra a backup can be restored to.
# su - zimbra -c "zmcontrol -v > $DESTLOCAL/zimbra/conf/zimbra_version.txt"
# or examine your /opt/zimbra/.install_history

# Display Zimbra services status
echo Displaying Zimbra services status...
su - zimbra -c "/opt/zimbra/bin/zmcontrol status"
# /etc/init.d/zimbra status # seems not to work
# backup the backup dir (but not the backups of the backups) to remote
rsync -essh -avHK --delete-during $DESTLOCAL/zimbra $DESTREMOTE

# Outputs the time the backup finished
echo Time backup finished = $(date +%T)

# Calculates and outputs total time taken
after="$(date +%s)"
elapsed="$(expr $after - $before)"
hours=$(($elapsed / 3600))
elapsed=$(($elapsed - $hours * 3600))
minutes=$(($elapsed / 60))
seconds=$(($elapsed - $minutes * 60))
echo Time taken: "$hours hours $minutes minutes $seconds seconds"

# end

Backup Shell Script with Compressed & Encrypted Archives

This script has following features:

• Easy to use!
• Backups with or without strong encryption!
• Compressed archives
• Optional Off-site copying of archives after creation
• MD5 checksums for integrity checks of archives
• Weekly backup rotation – 1 Full & 6 Diff’s per rotation
• Email report on Full backup
• Email notifications on errors
• Backup file lists (attached to weekly full backup report)
• Installer & Setup option for quick deployment (install needed software and setup env e.g. ssh pki auth and cronjobs)

You can follow the development and find the latest info about usage and so on in the zimbra forum under the thread “[Yet Another Backup Script Community Version]” To download a current version of this script go to the forum thread there you will find a link to the file in the first post. If you need any help you can contact me in the forum, I would be happy to help!

#!/bin/bash
## *** Info ***
# USAGE:    -h or --help for help & usage.
#           -f or --full for Full backup.
#           -d or --diff for Diff backup.
#           -V or --version for version info.
#           --INSTALL	 for script install and setup.
#
# This is a backup script for the FOSS version of Zimbra mail server.
# The script is free and open source and for use by anyone who can find a use for it.
#
# THIS SCRIPT IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
# ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
# A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE COPYRIGHT
# HOLDERS AND/OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
# BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
# AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
# OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF
# THE USE OF THIS DOCUMENT, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#
# CONTRIBUTORS:
# heinzg of osoffice.de (original author)
# Quentin Hartman of Concentric Sky, qh******@co***********.com (refactor and cleanup)
#
# What this script does:
# 1. Makes daily off-line backups, at a service downtime of ~ < 2 min.
# 2. Weekly backup cycle - 1 full backup & 6 diffs.
# 3. Predefined archive sizes, for writing backups to CD or DVD media...
# 4. Backup archive compression.
# 5. Backup archive encryption.
# 6. Backup archive integrity checks and md5 checksums creation.
# 7. Automated DR - Off-site copy of backup archives via ssh.
# 8. Install and setup function for needed software (Ubuntu Systems only)
# 9. Weekly eMail report & eMail on error - including CC address.
#
# This script makes use of following tools:
# apt-get, cron, dar, dpkg, mailx, md5sum, rsync, ssh, uuencode, wget, zimbra mta.
#
# We have opted to use a pre-sync directory to save on "down time", but this 
# causes one to have huge additional space usage.
# But hard drives are cheep today!
#
# What is still to come or needs work on:
# 1. Recovery option
# 2. Better documentation

##------- CONFIG -------#
# Edit this part of the script to fit your needs.

#--- Directories ---#
# Please add the trailing "/" to directories!
ZM_HOME=/opt/zimbra/	# where zimbra lives
SYNC_DIR=/tmp/fakebackup/	# intermediate dir for hot/cold syncs. must have at least as much free space as ZM_HOME consumes
ARCHIVEDIR=/Backup/zimbra_dars/	# where to store final backups
TO_MEDIA_DIR=/Backup/burn/	# where to put fulls for archiving to media

#--- PROGRAM OPTIONS ---# 
RSYNC_OPTS="-aHK --delete --exclude=*.pid" # leave these unless you are sure you need something else

#--- ARCHIVE NAMES ---#
BACKUPNAME="Zimbra_Backup"	# what you want your backups called
FULL_PREFIX="FULL"	# prefix used for full backups
DIFF_PREFIX="DIFF"	# prefix used for differential backups
BACKUPDATE=`date +%d-%B-%Y`	# date format used in archive names
BACKUPWEEK=`date +%W`	# Week prefix used for backup weekly rotation and naming

#--- ARCHIVE SIZE ---#
ARCHIVESIZE="4395M"	# storage media size, for full-backup archiving
COMPRESS="9"		# valid answers are 1 - 9 ( 9 = best )

#--- Encryption Options ---#
CRYPT="yes"		# valid answers are "yes" or "no"
PASSDIR=/etc/`basename $0`/ # the directory the encryption hash is stored in. 
PASSFILE="noread"	# the file containing the password hash

#--- Log Settings ---#
EMAIL="yo**@ma*********.local"	# the address to send logs to
EMAILCC=""				# another address to send to
LOG="/var/log/zim_backup.log"	# log location

#--- SSH REMOTE DR COPY ---#
# This option will secure copy your archives to a remote server via 'scp'
DRCP="no"		# valid answers are "yes" or "no" 
SSHUSER="you"	# recommend creating a user on the remote machine just for transferring backups	
SSHKEY="rsa"	# recommended answers are "rsa" or "dsa" but "rsa1" is also valid.
REMOTEHOST="remote.server.fqdn"	# can use IP too
REMOTEDIR="/tmp/"	# where you want your backups saved.

#--- Use Hacks? ---#
# Built in hacks to fix common problems
#Hack to start Stats, even run zmlogprocess if needed
STATHACK="yes" 		# valid answers are "yes" or "no"


## ~~~~~!!!! SCRIPT RUNTIME !!!!!~~~~~ ##
# Best you don't change anything from here on, 
# ONLY EDIT IF YOU KNOW WHAT YOU ARE DOING

Snapshots

While the above three methods can be used on a rotating fashion, their mainly full copies; as anyone with a 20TB store knows, backups take up space.

Thus, this section is devoted to reducing storage needed through incrementalish snapshots.

-Labeled ‘ish’ because this is nothing like the NE method of hot/live backups using redologs to restore to any given second.

-In short, so we don’t confuse it with NE, please don’t say “my incrementals aren’t working” in the forums without first mentioning that you’re using a FOSS method.

(Hence this is labled ‘snapshots’ to cut down on some confusion.)

Utilities to help you make rotating snapshots:

rsnapshot

rdiff-backup

An rsync script

Another script being worked out: http://www.zimbra.com/forums/showthread.php?threadid=15275

Another: http://www.zimbra.com/forums/showthread.php?threadid=15963

Emergency Repairs

Preparing to Back Up

Before we begin, make sure that you are logged in as a user that can perform the tasks outlined here.

It is always good practice to backup your copy of Zimbra in the event of unforeseen circumstances.

To prevent changes to any Zimbra databases during the backup processes you may wish to use:

>su zimbra

>zmcontrol stop

to terminate Zimbra.

If you get some kind of error, you may want to make sure that Zimbra has completly stopped by running:

>ps auxww | grep zimbra

and kill any left over processes such as the log.

Alternatively as root you could run the following command to kill all Zimbra user owned processes instantly (use wisely):

>ps auxww | awk '{print $1" "$2}' | grep zimbra | kill -9 `awk '{print $2}'`

Backing Up

Make sure that the copy location has enough space to support your backup copy (i.e. the /tmp folder probably isn’t the best location).

Since all of the components Zimbra needs are stored in the Zimbra folder itself, you can simply copy the folder to a safe location.

It may be possible to create a cron job to do these tasks automatically.

Copy Command: cp -Rp /opt/zimbra [location path]

Depending on your hardware and the amount of data contained in your Zimbra installation, this process can take a while.

Note: It is a very good idea to tag your installation with the version/build of zimbra that will be backed up (ie 3.0.0_GA_156) and the date of backup. You’ll need this later.

Restoring

Before restoring, you should make sure that all of the processes accociated with the damaged/failed Zimbra installation are terminated. Failure to terminate all of the processes could have dire consquences. See “Preparing to Backup” for additional info.

====Rename your “broken” Zimbra installation.’

You may be able to scavenge data, if needed. If you simply do not want the old data, you can skip this part.

>mv /opt/zimbra [new location i.e. /tmp/zimbra-old]

You may want to move it completly out of the /opt folder just to be safe.

Copy your backup Zimbra installation to the /opt folder and name it “zimbra”.

>cp -rp [location of backup] /opt

>mv /opt/[backup name] /opt/zimbra

Restore to Existing/Backup Zimbra Server

In the event of a failure, you can either restore your /opt/zimbra folder to a server that is not currently running Zimbra and download a dummy copy of Zimbra and run an upgrade in order to clean everything up and make it run correctly again (see next section), or you may restore the backup to an existing Zimbra server. This will only work if the existing server is running the EXACT SAME VERSION of Zimbra as the backup you want to restore. Also, this has been tested and seems to work well with CE 4.5.8, but did not work with 4.5.7. This may be, for example, a backup email server that you want to always keep current with last night’s backup, so it can replace the production server on short notice in the event of a disaster.

Simply set up your backup server identical to your production server (preferably the same OS, but necessarily the exact same version of Zimbra). Any modifications you made or packages you added for your production server with regards to extra anti-spam protection, etc., should also be added to this server. Shut down Zimbra on the backup server. Copy /opt/zimbra from your backup to the backup server.

chown -R zimbra:zimbra /opt/zimbra
/opt/zimbra/libexec/zmfixperms

Start Zimbra. Everything should work. The advantage to this method is that you can retain all your customizations (anti-spam modifications, for example) that would otherwise have been lost in the “upgrade” method. Also, this allows you to use a script to keep a backup server current without having to reinstall Zimbra after each time the backup server is made current, or before putting the backup server into production in the event of a failure.

Downloading a Dummy Copy of Zimbra

Now, we need to know what build/version of zimbra you were running. If you followed the backup instructions above, then the backup folder should be tagged with the version/build you have backed up.

You need to download the full install of the Zimbra version you were running. You may find all Zimbra releases at: Sourceforge.

If you don’t know your version number, you can find it by:

Method 1 – view install_history file.

cat /opt/zimbra/.install_history

Method 2 – dont think this will work unless you did a bunch of chroots.

zmcontrol -v

Both of the above listed methods were from this forum post

Installing the Dummy Copy of Zimbra

Once you have downloaded the tar file, you will need to uncompress it by:

>tar -xvzf [name of file].tgz

This will create a folder in the directory named “zcs”, cd to it and run the install script:

>cd zcs

>./install.sh

WARNING: Do not run the script with the -u option. You will remove all of your backup data & you must run the script as root, NOT ZIMBRA.

The script will remove the existing packages, and install them again. The script will attempt to stop Zimbra Services and “UPGRADE FAILED – exiting”. This is okay, simply rerun the script, and it will install normally.

If you experence error 389, -1, connection refused errors, please search the Forums. These errors are covered extensivly.

Resetting Permissions

If you are up and running now, you may have one more hurdle to overcome: permissions.

You may need to reset the permissions on the message store by running the following:

>/opt/zimbra/libexec/zmfixperms

This is potentially a dangerous suggestion, but if you have trouble getting tomcat to start even after you run zmfixperms, try running (worked for ZCS 4.5.7 CE)

chown -R zimbra:zimbra /opt/zimbra

Of course, you must run

/opt/zimbra/libexec/zmfixperms

again after that. It appears that zmfixperms is supposed to chown zimbra:zimbra on something, but it misses it. This way, you chown EVERYTHING zimbra:zimbra, and zmfixperms puts back things that need to be owned as root, postfix, etc.

Disaster Recovery

In the unfortunate event of losing your complete server or installation, the following will get you up and running.

This has been tested successfully on v5.0.5

• The machine you are recovering to must have the same hostname as the original machine.
• Once you have your OS and all pre-requisites installed. Download and install Zimbra as normal, using the same zimbra version as the backup. If it is a new server, you can install using install.sh -s to install just the packages to create the users and programs before recreating the old installation/
• Once Zimbra is installed, stop all Zimbra services and move/rename the /opt/zimbra folder:

sudo -u zimbra /opt/zimbra/bin/zmcontrol shutdown
mv /opt/zimbra /tmp/zimbra-old

• Uncompress the most recent backup file to the /opt/zimbra folder

mkdir /opt/zimbra
tar zxvf [backup file] -C /opt/zimbra

or if you prefer…

tar zxvf [backup file] -C /tmp/zimbra-backup
cp -Rp /tmp/zimbra-backup /opt/zimbra

• Fix the Zimbra permissions

chown -R zimbra:zimbra /opt/zimbra
/opt/zimbra/libexec/zmfixperms

• Restart all Zimbra services

sudo -u zimbra /opt/zimbra/bin/zmcontrol startup
zmcontrol startup

You should now be able to access Zimbra as normal. If not, you may need to “upgrade” zimbra to set up the old configuration, for example if you have zimbra listening on a web port other than 80. install.sh from the directory you extracted the zimbra install to. This will also restart services when completed, so you should be up and running

/etc/init.d/urbackupsrv stop

urbackupsrv cleanup -a 0%

/etc/init.d/urbackupsrv start

ou

systemctl stop urbackupsrv

urbackupsrv cleanup -a 0%

systemctl start urbackupsrv

• 

• 

• 

teste

The PostgreSQL relational database system is a powerful, scalable, and standards-compliant open-source database platform. This guide will help you install and configure PostgreSQL on your CentOS 7 Linode.

Before You Begin

1. Familiarize yourself with our Getting Started guide and complete the steps for setting your Linode’s hostname and timezone.
2. Complete the sections of our Securing Your Server guide to create a standard user account, harden SSH access and remove unnecessary network services.
3. Update your system:sudo yum update

NoteThis guide is written for a non-root user. Commands that require elevated privileges are prefixed with sudo. If you’re not familiar with the sudo command, visit the Users and Groups guide for more information.

Install PostgreSQL

In this section, we’ll cover two different methods for installing PostgreSQL. If you do not need the latest version, we recommend using the first method to install PostgreSQL from the CentOS repositories.

Unless otherwise noted, the instructions in subsequent sections of this guide will be compatible with versions installed by either method.

Install From the CentOS Repositories

1. As of this writing, the CentOS 7 repositories ship with PostgreSQL version 9.2.15. To install from the CentOS repositories, simply run:sudo yum install postgresql-server postgresql-contrib
2. Initialize your Postgres database and start PostgreSQL:sudo postgresql-setup initdb sudo systemctl start postgresql
3. Optional: Configure PostgreSQL to start on boot:sudo systemctl enable postgresql

Install From the Postgres Repositories

Alternatively, you can install the latest version from the Postgres repositories. As of this publication, PostgreSQL 9.6.3 is the most recent version available for CentOS 7, but these steps can be applied to any RPM-based installation.

NoteWhen Postgres is installed using this method, the version number is included in its configuration directories. For example, /var/lib/pgsql becomes /var/lib/pgsql/9.6. This is also the case with systemd units; systemctl status postgresql becomes systemctl status postgresql-9.6.

1. Select the version you wish to install from the Postgres Yum repositories. Locate the CentOS 7 link for your chosen version and download it to your Linode:wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
2. Install the RPM, as well as the EPEL repositories, which will be used to satisfy dependencies:sudo yum install pgdg-centos96-9.6-3.noarch.rpm epel-release
3. Update Yum to apply your changes and install PostgreSQL. When installing Postgres manually, you will have to specify the version:sudo yum update sudo yum install postgresql96-server postgresql96-contrib
4. Initialize your database and start PostgreSQL:sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb sudo systemctl start postgresql-9.6
5. Optional: Configure PostgreSQL to start on boot:sudo systemctl enable postgresql-9.6

Configure PostgreSQL

Secure the Postgres Users

By default, PostgreSQL will create a Linux user named postgres to access the database software.

CautionThe postgres user should not be used for other purposes (e.g., connecting to other networks). Doing so presents a serious risk to the security of your databases.

1. Change the postgres user’s Linux password:sudo passwd postgres
2. Issue the following commands to set a password for the postgres database user. Be sure to replace newpassword with a strong password and keep it in a secure place.su - postgres psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';" Note that this user is distinct from the postgres Linux user. The Linux user is used to access the database, and the PostgreSQL user is used to perform administrative tasks on the databases.The password set in this step will be used to connect to the database via the network. Peer authentication will be used by default for local connections. See the Secure Local PostgreSQL Access section for information about changing this setting.

Access the PostgreSQL Shell

The PostgreSQL client shell allows you to issue SQL commands to administer your databases. As the postgres Linux user, log in by running:

psql postgres

This will log you in as the postgres database user. You’ll see a prompt similar to this:

psql (9.2.15)
Type "help" for help.

postgres=#

In the last line, postgres=# indicates the name of the current database. To see a list of available commands, type \h. You may find more information on a specific command by adding it after \h. Once you’ve finished using the shell, you can exit with \q.

Work with Databases

This section will cover how to create, delete and access databases.

Create a Database

You can create databases with the createdb command. Create a sample database called mytestdb by running this command as the postgres Linux user:

createdb mytestdb

It’s also possible to assign ownership of the database to a specific Postgres user/role. For example, you could assign ownership to the examplerole role by running:

createdb mytestdb -O examplerole

The createdb command has several additional options, which can be found in the PostgreSQL documentation.

Connect to a Database

You can use the psql command to connect to a specific database.

1. Connect to the test database:psql mytestdb
2. You will see the following output:psql (9.2.15) Type "help" for help. mytestdb=# By default, you will connect to a database as your peer-authenticated user. However, if you’ve enabled local password access, it’s also possible to specify which user you wish to connect as:psql mytestdb -U examplerole You’ll be prompted to enter the password for the examplerole database user before you access the shell.

List Databases

From the Postgres shell, you can list all of your databases with the \l or \list command. You will receive output similar to this:

postgres=# \l
                          List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mytestdb  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|         |          |          |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|         |          |          |             | postgres=CTc/postgres
(4 rows)

You may also show the current database and user by entering \c from the Postgres shell. Additional info, like socket and port, will be included if you use \conninfo:

You are connected to database "mytestdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Delete a Database

You can delete, or drop, databases with the dropdb command. For example, to delete the mytestdb database created previously, issue this command as the postgres Linux user:

dropdb mytestdb

CautionDeleted databases cannot be recovered.

Work With Tables

PostgreSQL databases use tables to store and organize information within a database. In this section, you’ll find practical examples for adding, removing and manipulating tables. Unless otherwise noted, the commands in this section should be issued from the Postgres shell once you’ve connected to your database.

Create Tables

This section contains examples that create a test database with an employee’s first and last name, assigning each a unique key. When creating your own tables, you may specify as many parameters (columns) as you need and name them appropriately.

1. Create a table called “employees” in your test database:CREATE TABLE employees (employee_id int, first_name varchar, last_name varchar);
2. Insert a record into the table:INSERT INTO employees VALUES (1, 'John', 'Doe');

View the Content of a Table

To view the contents of the “employees” table:

SELECT * FROM employees;

This produces the following output:

employee_id | first_name | last_name
-------------+------------+-----------
          1 | John       | Doe
(1 row)

List Tables in a Database

You can list all tables in the current database with the \dt command:

mytestdb-# \dt
          List of relations
Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
public | employees | table | postgres

Delete Tables

Delete tables with DROP TABLE. To delete the employees table:

DROP TABLE employees;

DROP TABLE accepts multiple comma-separated table names as arguments. For example, if you had two separate tables called employees1 and employees2, you could delete them both by running:

DROP TABLE employees1, employees2;

Add Columns

Tables can be altered to add definitions, data types and columns. In this example you’ll add a new start_date column that uses the date data type.

1. Add the start_date column to the employees table:ALTER TABLE employees ADD start_date date;
2. Verify your change:SELECT * FROM employees; You’ll see that the new column has been created, but it does not contain any data:employee_id | first_name | last_name | start_date -------------+------------+-----------+------------ 1 | John | Doe | (1 row) In this example you’ve used the date data type, but PostgreSQL tables support several different types of data. See the PostgreSQL Documentation for a full explanation of supported data types.

Add and Update Rows

In this section, you’ll use UPDATE to enter a value into the existing row you’ve created. Then, you’ll create an entirely new row with INSERT.

1. Update the start_date field for the user with the value 1 in the employee_id column:UPDATE employees SET start_date = '2016-09-28' WHERE employee_id = '1';
2. Create a new row in the employees table:INSERT INTO employees VALUES (2, 'Jane', 'Smith', '2015-03-09');
3. Verify your changes:SELECT * FROM employees; You’ll see that the start date of 2016-09-28 has been added to the first row, and that a new row has been created for “Jane Smith”:employee_id | first_name | last_name | start_date -------------+------------+-----------+------------ 1 | John | Doe | 2016-09-28 2 | Jane | Smith | 2015-03-09 (2 rows)

Remove Columns and Rows

In this section, you’ll remove a column from your table and then remove the second row.

1. Use ALTER TABLE to remove the start_date column you made previously:ALTER TABLE employees DROP start_date;
2. Now use DELETE to remove the second row of your employees table. The following command will remove the row with a value of 2 in the employee_id column:DELETE FROM employees WHERE employee_id = '2';
3. Confirm your changes:SELECT * FROM employees; Your table now consists of a single row, with the start_date column removed:employee_id | first_name | last_name -------------+------------+----------- 1 | John | Doe (1 row)

Query a Table

You can use queries to pull specific information from your database. This command will query your employees table to only return values for the employee_id and last_name columns:

SELECT last_name,employee_id FROM employees;

You’ll receive an output similar to this:

last_name | employee_id
-----------+-------------
Doe       |           1
(1 row)

PostgreSQL supports many querying options. See the PostgreSQL Documentation for more information.

Work With Roles

PostgreSQL grants database access via roles, which are used to specify privileges. Roles can be understood as having a similar function to Linux “users.” In addition, roles may also be created as a set of other roles, similar to a Linux “group.” PostgreSQL roles apply globally, so you will not need to create the same role twice if you’d like to grant it access to more than one database on the same server.

Create Roles

New user roles are added with the createuser command. To create a new user called examplerole, issue this command as the postgres Linux user:

createuser examplerole --pwprompt

You will be prompted to create a password for the new user.

Give a Role Access to a Database

In this example, you’ll give the newly created examplerole user access to your database.

1. Connect to the database:psql mytestdb You’ll be connected as the postgres database user by default.
2. From the PostgreSQL shell, enter the following to grant all privileges on the table employees to the user examplerole:GRANT ALL ON employees TO examplerole;
3. Exit the database with \q.

List All Roles

You can list all roles from the Postgres Shell by running \du. You’ll see an output similar to this:

postgres=# \du
                             List of roles
Role name   |                   Attributes                   | Member of
-------------+------------------------------------------------+-----------
examplerole |                                                | {}
postgres    | Superuser, Create role, Create DB, Replication | {}

Group Roles

For ease of administration, it’s possible to add multiple user roles to a single group, so that their privileges can be managed as a whole. In this section you’ll create a new group and add the examplerole user to it. These commands should be run as the postgres Linux user.

1. Use the createuser command to create a new group role. The --no-login option is specified because groups do not need login capability.createuser examplegroup --no-login
2. Log into the Postgres shell and add examplerole to the new group:psql postgres GRANT examplegroup TO examplerole;
3. From the Postgres shell, verify your changes with \du. You’ll see that the examplerole user is now listed as a member of the examplegroup group:postgres=# \du List of roles Role name | Attributes | Member of --------------+------------------------------------------------+---------------- examplegroup | Cannot login | {} examplerole | | {examplegroup} group | | {} postgres | Superuser, Create role, Create DB, Replication | {} The createuser command has several other options. See the PostgreSQL documentation for more details.
4. When you’ve finished applying your changes, exit the Postgres shell with \q.

Alter Roles

While specific settings and privileges can be applied to a role when it’s created, you can also modify a role’s properties later on. In this example, we’ll modify the examplerole user so that it can create new databases. The commands in this section should be run as the postgres Linux user.

1. Log in as the postgres database user:psql postgres
2. From the Postgres shell, add the CREATEDB parameter to the examplerole user:ALTER ROLE examplerole CREATEDB; A number of permissions can be applied when creating or altering a role. See the PostgreSQL Documentation for more details.
3. Use \du to confirm your changes. You’ll see that the “Create DB” attribute is listed next to the examplerole user:postgres=# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------+----------- examplerole | Create DB | {} group | | {} postgres | Superuser, Create role, Create DB, Replication | {}
4. Once you’ve finished, exit the Postgres shell with \q.

Delete Roles

The dropuser command is used to delete PostgreSQL roles. To delete the examplerole user, issue this command as the postgres Linux user:

dropuser examplerole

Peer Authentication

PostgreSQL uses peer authentication by default. This means that database connections will be granted to local system users if their Linux username matches the name of their PostgreSQL role. To make use of peer authentication effectively, you would need to create both a Linux user and a corresponding PostgreSQL role. For the examplerole role you just created, you can use peer authentication by creating an examplerole local system user. This command must be run as a user with sudo access:

sudo adduser examplerole && passwd examplerole

Note that you will be prompted to create a password for the new examplerole Linux user. Alternatively, you can follow our steps to secure local access.

Secure PostgreSQL

Secure Local Access

While PostgreSQL’s default peer authentication is useful in cases where a particular system user will be running a local program (e.g., scripts, CGI/FastCGI processes owned by separate users, etc.), you may wish to require passwords for greater security.

Commands in this section should be run as the postgres Linux user unless otherwise specified.

1. Edit the /var/lib/pgsql/data/pg_hba.conf file, under the # "local" is for Unix domain socket connections only header:File: /var/lib/pgsql/data/pg_hba.conf1 2 # "local" is for Unix domain socket connections only local all all peerReplace peer with md5 on this line to activate password authentication using an MD5 hash.NoteIf you installed PostgreSQL from the Postgres repositories, you will need to specify your version number in this file path, for example: /var/lib/pgsql/9.6/data/pg_hba.conf.
2. To enable these changes, you need to restart PostgreSQL. However, you did not grant the postgres user sudo privileges for security reasons. Return to the normal user shell:exit
3. Restart PostgreSQL and switch back to the postgres user:sudo systemctl restart postgresql su - postgres
4. As postgres, connect to the test database as the examplerole PostgreSQL user:psql mytestdb -U examplerole You will be prompted to enter the password for the examplerole user and then given psql shell access to the database. When using a database, you may check access privileges for each of its tables with the \z command.

Secure Remote Access

PostgreSQL listens for connections on localhost by default, and it is not advised to reconfigure it to listen on public IP addresses. If you wish to make PostgreSQL externally accessible, it’s recommended that you follow the Postgres documentation for using SSL to secure your remote connections. Alternatively, you could connect to PostgreSQL over an SSH tunnel. To access your databases remotely using a graphical tool, please follow one of these guides:

• Securely Manage Remote PostgreSQL Servers with pgAdmin on Windows
• Securely Manage Remote PostgreSQL Servers with pgAdmin on Mac OS X

Aqui vai um jeito bem fácil e rápido de instalar e habilitar o SSH no CentOS 7.
Para isso, nosso ponto de partida será uma imagem padrão do CentOS 7 e vamos seguir os seguintes passos:

1. Instalar o SSH
2. Habilitar as regras de firewall
3. Testar 🙂

Instalar o SSH

Usando `yum`, basta seguir os seguintes comandos (você vai precisar executar o comando como root):

# yum update -y && yum install -y openssh openssh-server

Para verificar se tudo está funcionando:

$ systemctl status sshd

Se por algum motivo o serviço não foi corretamente configurado durante a instalação, podemos fazê-lo com:

# chkconfig sshd on# service sshd start

Configurações finas sobre o serviço do SSH podem ser encontradas em /etc/ssh/sshd_config.

Habilitar as regras de firewall

Normalmente esse processo de instalação habilita as configurações de ssh no serviço de firewall do CentOS 7 (firewalld)

Para verificar as configurações podemos:

# firewall-cmd --list-service | grep ssh

Se não houver configuração apropriada, podemos:

# firewall-cmd --add-service ssh# firewall-cmd --reload

Testar

Nesse ponto, já temos tudo certo para realizar o primeiro acesso.

Próximos passos

Esse mini tutorial mostra uma instalação simples destinada a ambientes de estudo e testes, sendo desaconselhável para ambientes produtivos.

Para ganhar um pouco de robustez e segurança você pode procurar sobre:

1. Habilitar acesso SSH através de certificados
2. Habilitar restrição de IP no firewall

Introduction

Screen or GNU Screen is a terminal multiplexer. In other words, it means that you can start a screen session and then open any number of windows (virtual terminals) inside that session. Processes running in Screen will continue to run when their window is not visible even if you get disconnected.

Install Linux GNU Screen

The screen package is pre-installed on most Linux distros nowadays. You can check if it is installed on your system by typing:

screen --version

Screen version 4.06.02 (GNU) 23-Oct-17

If you don’t have screen installed on your system, you can easily install it using the package manager of your distro.

Install Linux Screen on Ubuntu and Debian

sudo apt updatesudo apt install screenCopyCopy

Install Linux Screen on CentOS and Fedora

sudo yum install screen

Starting Linux Screen

To start a screen session, simply type screen in your console:

screen

This will open a screen session, create a new window, and start a shell in that window.

Now that you have opened a screen session, you can get a list of commands by typing:

Ctrl+a ?

Starting Named Session

Named sessions are useful when you run multiple screen sessions. To create a named session, run the screen command with the following arguments:

screen -S session_name

It’s always a good idea to choose a descriptive session name.

Working with Linux Screen Windows

When you start a new screen session, it creates a single window with a shell in it.

You can have multiple windows inside a Screen session.

To create a new window with shell type Ctrl+a c, the first available number from the range 0...9 will be assigned to it.

Below are some most common commands for managing Linux Screen Windows:

• Ctrl+a c Create a new window (with shell)
• Ctrl+a " List all window
• Ctrl+a 0 Switch to window 0 (by number )
• Ctrl+a A Rename the current window
• Ctrl+a S Split current region horizontally into two regions
• Ctrl+a | Split current region vertically into two regions
• Ctrl+a tab Switch the input focus to the next region
• Ctrl+a Ctrl+a Toggle between the current and previous region
• Ctrl+a Q Close all regions but the current one
• Ctrl+a X Close the current region

Detach from Linux Screen Session

You can detach from the screen session at any time by typing:

Ctrl+a d

The program running in the screen session will continue to run after you detach from the session.

Reattach to a Linux Screen

To resume your screen session use the following command:

screen -r

In case you have multiple screen sessions running on your machine, you will need to append the screen session ID after the r switch.

To find the session ID list the current running screen sessions with:

screen -ls

There are screens on:
    10835.pts-0.linuxize-desktop   (Detached)
    10366.pts-0.linuxize-desktop   (Detached)
2 Sockets in /run/screens/S-linuxize.

If you want to restore screen 10835.pts-0, then type the following command:

screen -r 10835

Customize Linux Screen

When screen is started, it reads its configuration parameters from /etc/screenrc and ~/.screenrc if the file is present. We can modify the default Screen settings according to our preferences using the .screenrc file.

Here is a sample ~/.screenrc configuration with customized status line and few additional options:~/.screenrc

# Turn off the welcome message
startup_message off

# Disable visual bell
vbell off

# Set scrollback buffer to 10000
defscrollback 10000

# Customize the status line
hardstatus alwayslastline
hardstatus string '%{= kG}[ %{G}%H %{g}][%= %{= kw}%?%-Lw%?%{r}(%{W}%n*%f%t%?(%u)%?%{r})%{w}%?%+Lw%?%?%= %{g}][%{B} %m-%d %{W}%c %{g}]'

Copy

How to Install PostgreSQL on Debian 9

Updated  Feb 13, 2019•

5 min read

PostgreSQL, often known simply as Postgres, is an open-source general-purpose object-relational database management system. PostgreSQL has many advanced features such as online backups, point in time recovery, nested transactions, SQL and JSON querying, multi-version concurrency control (MVCC), asynchronous replication and more.

In this tutorial, we will show you how to install PostgreSQL on Debian 9 and explore the fundamentals of basic database administration.

Prerequisites

Before proceeding with this tutorial, make sure the user you are logged in as has sudo privileges .

Installing PostgreSQL

At the time of writing this article, the latest version of PostgreSQL available from the Debian repositories is PostgreSQL version 9.6.

To install PostgreSQL on your Debian server complete the following steps:

1. Begin by updating the local package index:sudo apt updateCopy
2. Install the PostgreSQL server and PostgreSQL contrib package which provides additional features for the PostgreSQL database:sudo apt install postgresql postgresql-contribCopy
3. When the installation is completed, the PostgreSQL service will start automatically. To verify the installation we’ll connect to the PostgreSQL database server using the psql utility and print the server version :sudo -u postgres psql -c "SELECT version();"CopyThe output will look like this: version ----------------------------------------------------------------------------------------------------------- PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit (1 row)Copy

Psql is an interactive terminal program that allows you to interact with the PostgreSQL server.

PostgreSQL Roles and Authentication Methods

PostgreSQL handles database access permissions using the concept of roles. A role can represent a database user or a group of database users.

PostgreSQL supports a number of authentication methods . The most commonly used methods are:

• Trust – With this method, the role can connect without a password, as long as the criteria defined in the pg_hba.conf are met.
• Password – A role can connect by providing a password. The passwords can be stored as scram-sha-256 md5 and password (clear-text)
• Ident – This method is only supported on TCP/IP connections. Works by obtaining the client’s operating system user name, with an optional user name mapping.
• Peer – Same as Ident but it is only supported on local connections.

PostgreSQL client authentication is defined in the configuration file named pg_hba.conf. By default for local connections, PostgreSQL is set to use the peer authentication method.

The postgres user is created automatically when you install PostgreSQL. This user is the superuser for the PostgreSQL instance and it is equivalent to the MySQL root user.

To log in to the PostgreSQL server as the postgres user first you need to switch to the user postgres and then you can access a PostgreSQL prompt using the psql utility:

sudo su - postgrespsqlCopyCopy

From here, you can interact with your PostgreSQL instance. To exit out of the PostgreSQL shell type:

\q

You can use the sudo command to access the PostgreSQL prompt without switching users:

sudo -u postgres psql

The postgres user is typically used only from the local host and it is recommended not to set the password for this user.

Creating PostgreSQL Role and Database

You can create new roles from the command line using the createuser command. Only superusers and roles with CREATEROLE privilege can create new roles.

In the following example, we will create a new role named john a database named johndb and grant privileges on the database.

1. Create a new PostgreSQL RoleThe following command will create a new role named “john”:sudo su - postgres -c "createuser john"Copy
2. Create a new PostgreSQL DatabaseCreate a new database named “johndb” using the createdb command:sudo su - postgres -c "createdb johndb"Copy
3. Grant privilegesTo grant permissions to the john user on the database we created in the previous step, connect to the PostgreSQL shell:sudo -u postgres psqlCopyand run the following query:GRANT ALL PRIVILEGES ON DATABASE johndb TO john;Copy

Enable remote access to PostgreSQL server

By default the PostgreSQL, server listens only on the local interface 127.0.0.1. To enable remote access to your PostgreSQL server open the configuration file postgresql.conf and add listen_addresses = '*' in the CONNECTIONS AND AUTHENTICATION section.

sudo vim /etc/postgresql/9.6/main/postgresql.conf

/etc/postgresql/9.6/main/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

save the file and restart the PostgreSQL service with:

sudo service postgresql restart

Verify the changes with the ss utility:

ss -nlt | grep 5432

LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

As you can see from the output above the PostgreSQL server is listening on all interfaces (0.0.0.0).

The last step is to configure the server to accept remote connections by editing the pg_hba.conf file.

Below are some examples showing different use cases:/etc/postgresql/9.6/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane will be able to access all databases from all locations using a md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane will be able to access only the janedb from all locations using a md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane will be able to access all databases from a trusted location (192.168.1.134) without a password
host    all             jane            192.168.1.134            trust

Conclusion

You have learned how to install and configure PostgreSQL on your Debian 9 server. For more information on this topic, consult the PostgreSQL Documentation .

If you have any questions, please leave a comment below.