Categoria Sem categoria

porplague70

Criando zfs proxmox backup

primeiro tem que zerar os discos.

sgdisk –zap-all /dev/sdc

segundo tem que dar um boot no servidor para reconhecer os disco como limpo sem partição.

logo apos voltar.

zpool create -f -o ashift=12 RAID0 /dev/sdb /dev/sdc

pronto teu zfs esta criado com raid0 no comando acima.

para montar o raid0

crie um ponto de montagem no meu caso foi /backup

zfs set mountpoint=/backup RAID0

porplague70

Squid Proxy on pfSense for Home Web Cache and Security

About my setup

I have a pfSense box running 2.2.5-RELEASE (amd64), Squid 3.4 branch, and SquidGuard.

To customize the refresh rules, go to Services menu, select Squid proxy. Select the Local cache tab.

2015-12-09_11-32-58

At the bottom, enter the custom code in the”Custom refresh_patterns” box.

Download config

psynaps-pfsense-squid3-example.txt

Config

(updated 12-09-15)

# 1 year = 525600 mins, 1 month = 43800 mins, 1 week = 10080 min, 1 day = 1440 min

#Optional: dont cache wordpress admin panel
refresh_pattern (wp-admin)  0 0% 0

# specific cache
# live_user: twitch preview thumbs
refresh_pattern -i (live_user) 30 60% 300 override-lastmod reload-into-ims
# for malwarebytes update checking
refresh_pattern -i (mbamupdates.com) 1440 60% 10080 override-lastmod reload-into-ims

#All File
refresh_pattern -i \.(3gp|7z|ace|asx|avi|bin|cab|dat|deb|rpm|divx|dvr-ms)(\?|$) 43800 100% 129600 ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(rar|jar|gz|tgz|tar|bz2|iso)(\?|$)                         43800 100% 129600 ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(m1v|m2(v|p)|mo(d|v)|(x-|)flv)(\?|$)                       43800 100% 129600 ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(jp(e?g|e|2)|gif|pn[pg]|bm?|tiff?|ico|swf)(\?|$)           43800 100% 129600 ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(mp(e?g|a|e|1|2|3|4)|mk(a|v)|ms(i|u|p))(\?|$)              43800 100% 129600 ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(og(x|v|a|g)|rar|rm|r(a|p)m|snd|vob|wav)(\?|$)             43800 100% 129600 ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(pp(s|t)|wax|wm(a|v)|wmx|wpl|zip|cb(r|z|t))(\?|$)          43800 100% 129600 ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(woff|txt|exe|dmg|webm)(\?|$)                              43800 100% 129600 ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(css)(\?|$)                                                10080  60% 43800  ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload
refresh_pattern -i \.(js)(\?|$)                                                 10080  60% 10080  ignore-no-cache ignore-no-store ignore-private override-expire override-lastmod reload-into-ims ignore-reload

refresh_pattern -i \.(html|htm)(\?|$) 1440  60% 10080 ignore-no-cache ignore-no-store ignore-private override-expire reload-into-ims
refresh_pattern -i \.(doc|pdf)(\?|$)  10080 90% 43200 ignore-no-cache ignore-no-store ignore-private override-expire reload-into-ims

refresh_pattern -i \.(cdn) 10800 100% 43800 override-expire override-lastmod reload-into-ims ignore-reload ignore-no-cache ignore-private
refresh_pattern -i (cdn)   10800 100% 43800 override-expire override-lastmod reload-into-ims ignore-reload ignore-no-cache ignore-private

refresh_pattern \.ytimg\? 10800 90% 10800 override-expire ignore-reload ignore-no-cache
refresh_pattern ^http://*.facebook.com/* 720 100% 4320
refresh_pattern ^http://mail.yahoo.com/.* 720 100% 4320
refresh_pattern ^http://*.yahoo.*/.* 720 100% 4320
refresh_pattern ^http://*.yimg.*/.* 720 100% 4320
refresh_pattern ^http://*.gmail.*/.* 720 100% 4320
refresh_pattern ^http://*.google.*/.* 720 100% 4320
refresh_pattern ^http://*.kaskus.*/.* 720 100% 4320
refresh_pattern ^http://*.googlesyndication.*/.* 720 100% 4320
refresh_pattern ^http://*.plasa.*/.* 720 100% 4320
refresh_pattern ^http://*.telkom.*/.* 720 100% 4320

#catch all
refresh_pattern . 360 90% 1440 override-lastmod reload-into-ims

More details

To make the cache more aggressive, i used (\?|$). by adding /?, this caches objects when the URL ends in ? (example abc.css?). Typically a ? is used to indicate dynamic content, and is also used to instruct caches to NOT cache those objects, so be careful using this. However, I found it is OK to ignore the ? after objects like jpgs and css, and many times js. These objects are static content, and most of the time they do not change and can be cached for long periods of time. Caching these kinds of objects is great, because it reduces the number of requests you make to the web. I also use the $ option above. This means its the end of the string, so \.jpg$ just matches an object with .jpg at the end of the URL.

I use a catch all. The function of this rule is to match any requests that have not already matched the rules above. Be careful here, because this rule can/will cache dynamic content. The default pfsense Squid catchall exists in the default config already, however, the built-in config is not editable (as far as I understand). It will always revert to default (let me know if you know how to change that). Thus, to work-around I can simply add and customize the catch all at the bottom of my “Custom refresh_patterns”.

porplague70

Open Source Edition Backup Procedure

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    = '[email protected]';

# 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, [email protected] (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="[email protected]"	# 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

porplague70

Server webinterface rights management

Server webinterface rights management

The server web interface is protected by a pretty standard user system. You can create, manage and delete accounts. Those accounts are only linked loosely to clients by rights management. Be aware that after first installing UrBackup there is no administrator password set and everybody can see all backed up files! If you want to limit access you should immediately go to the account management in the settings and create an administrator account and set its password.
An admin account can do everything including browsing file backups of all clients. The web interface allows one to create a ’limited’ account that can only browse backups and view statistics from one client. The more sophisticated rights editor can be used to allow an account to access several clients or to limit some aspects. For example you could setup an account which can do everything except browse backups. Following domains, with which you can limit or expand an account’s rights, are currently available:

DomainDescription
browse_backupsBrowse and download files from file backups
lastactsView the last actions (file or image backups) the server did (including backup size and duration)
progressView the progress of currently running file or image backups
settingsAllows settings to be changed
client_settingsAllows client specific settings to be changed
statusAllows the current status to be viewed (last seen, last file backup and last image backup)
logsView the logs which were creating during backups
manual_archiveManually archive file backups
stop_backupStop backups for client on the server
piegraph*View statistics
users*Get client names
general_settings*Change general settings (like backup storage path)
mail_settingsChange the mail server settings
usermod*Create, change and delete users
remove_client*Remove clients and delete all their backups
start_backup*Start backups for a client on the server
download_imageDownload images of volumes from the server via restore CD

You can set the domains not marked with stars(*) either to one or several client ids (separated by ’,’) or to ’all’ – meaning the account can access all clients. The entries with stars(*) have to be set to ’all’ or ’none’ and don’t allow client ids. In order to be able to view statistics you need to set both ’piegraph’ and ’users’ to ’all’. There is a special domain ’all’ which is a wild card for all domains (this means if you set ’all’ to ’all’ the account has the right to do everything).

Currently a user needs the “status” right for at least one client, in order for the user to be able to log in.

porplague70

Force to remove client immediately

/etc/init.d/urbackupsrv stop

urbackupsrv cleanup -a 0% ou urbackupsrv remove-unknown

/etc/init.d/urbackupsrv startLog In

porplague70

Force to remove client immediately

/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

porplague70

teste

teste

porplague70

How to Install PostgreSQL Relational Databases on CentOS 7

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: