pg dump
From SkyPHP
Contents |
How To Migrate A Database To Another Machine
on webservers
uncomment this line in index.php of all websites that use this database.
$down_for_maintenance = true;
on the old db server
su - postgres cd /var/lib/pgsql/backups pg_dump -s -f dbname-schema-112210.sql dbname pg_dump -av --disable-triggers -f dbname-data-112210.sql dbname scp dbname-schema-112210.sql newhost:/var/lib/pgsql/backups/ scp dbname-data-112210.sql newhost:/var/lib/pgsql/backups/
on the new db server
first make sure postgresql.conf is set up properly with the following configurations
listen_addresses = '*' max_connections = 1000 shared_buffers = 8GB # 25% of physical ram work_mem = 50MB maintenance_work_mem = 256MB max_fsm_pages = 300000 max_fsm_relations = 2000 wal_buffers = 16MB checkpoint_segments = 256 # 4GB at a time (good for bulk load) checkpoint_completion_target = 0.9 # good for large checkpoint_segments effective_cache_size = 16GB # 50% of physical ram default_statistics_target = 50 log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_min_error_statement = log log_statement = 'mod'
Do not forget to restart postgresql
/etc/init.d/postgresql-9.0 restart
then issue the following restore commands. Output is redirected so we can read errors easily.
createdb dbname psql dbname <dbname-schema-112210.sql >schema_out psql dbname <dbname-data-112210.sql >data_out vacuumdb -vz dbname
set the password for postgres
ALTER USER postgres WITH PASSWORD '****'
pg_hba.conf
host all all 0.0.0.1 0.0.0.0 md5
Restart postgresql again
/etc/init.d/postgresql-9.0 restart
Set up a .pgpass file for the accounts root and postgres for each database server. For each web server, setup a .pgpass for the nobody account (/.pgpass)
*:*:*:*:mypassword
- chmod 600
- chown username:wheel
whitelist the webserver ip on the db server and vice versa - or whitelist the private network on all servers
$ apf -a 192.168.1.0/24 $ service apf restart
Save the following database backup script to /var/lib/pgsql/9.0/backups/script
#!/usr/bin/perl -l
###############
# Daily Database schema and data backup script
# backup.pl
#
# For cron:
# cd /path/to/script/directory ; /usr/bin/perl backup.pl >>db_backup_log
#
# cd is not necesarily required unless config.pl is used, but it is harmless and convinient in cron
#
# Last modified JUNE 8 2011
#
################
# CONFIG
################
# These are all the configuration variable defaults. If the file config.pl exists in the working directory, it is included.
#
$db_name = "postgres";
$db_host = "localhost";
$db_user = "postgres";
# These databases will not be backed up
@exclude_databases = (
'template1',
'template0',
'postgres',
);
$backup_path = '/var/lib/pgsql/9.0/backups'; #directory of backups
$compress_path = "$backup_path/compressed"; #directory for compressed files.
$data_file_name = "data.sql"; #name of data backup file
$schema_file_name = "schema.sql"; #name of schema backup file
$delete_backups_older_than = 10; #days, will not delete anything if there is not more than this number of backups
$max_purge = 3; #maximum number of deletes before purging is stopped
$suppress_output = 0;
$skip_vaccum = 0;
$skip_data = 0;
$skip_schema = 0;
$skip_purge = 0;
$skip_compress = 0;
$compress_cmd = "cd $backup_path; tar czvf "; #uses gzip. Worse compression rates but MUCH faster than bzip2. Speed is the most important thing.
$compressed_file_extension = '.tar.gz';
$time_format = '%Y-%m-%d'; #strftime
# Overwrite any of the above variables in config.pl
require "config.pl" if -e "config.pl";
# END CONFIG
############
use DBI;
use POSIX;
$db = DBI->connect("DBI:Pg:dbname=$db_name;host=$db_host", $db_user, $db_pass, {'RaiseError' => 1}) || die "Unable to connec to to database";
############
# FUNCTIONS
############
sub db_execute{
my $sql = shift();
my $_db = shift() || $db;
my $rs = $_db->prepare($sql);
$rs->execute();
$rs;
}
sub generate_cmd_connection_string{
my $name = shift() || $db_name;
my $host = shift() || $db_host;
my $user = shift() || $db_user;
my $port = shift() || $db_port;
($user?"-U $user ":'').($host?"-h $host ":'').($port?"-p $port ":'').$name;
}
sub echo{
print shift() unless $suppress_output;
}
sub trim{
$1 if shift() =~ /^\s*(.+)\s*$/;
}
sub get_time{
my $time = shift();
$time = [$time?localtime($time):localtime];
POSIX::strftime($time_format, @$time);
}
sub get_databases{
my $rs = db_execute("select * from pg_database where datname not in ('" . join("', '", @exclude_databases) . "')");
my @return;
push(@return, $ref->{'datname'}) while $ref = $rs->fetchrow_hashref();
@return;
}
sub backup_database{
my $db_name = shift();
my $db_cmd_connection_string = generate_cmd_connection_string($db_name);
my $time = get_time();
`mkdir -p $backup_path/$db_name/$time`;
unless($skip_schema){
my $full_backup_path = "$backup_path/$db_name/$time/$schema_file_name";
echo("Backing up $db_name schema to $full_backup_path ...");
`pg_dump --schema-only --disable-triggers $db_cmd_connection_string >$full_backup_path`;
echo("Schema backup complete.");
}else{
echo("Skipping schema backup of $db_name");
}
unless($skip_data){
my $full_backup_path = "$backup_path/$db_name/$time/$data_file_name";
echo("Backing up $db_name data to $full_backup_path ...");
`pg_dump --data-only --disable-triggers $db_cmd_connection_string >$full_backup_path`;
echo("Data backup complete.");
}else{
echo("Skipping data backup of $db_name...");
}
1;
}
sub compress_backups{
my $db_name = shift();
unless($skip_compress){
echo("Compressing $db_name backups...");
`mkdir -p $compress_path/$db_name`;
my @ls = `ls -1 $backup_path/$db_name`;
foreach(@ls){
$_ = trim($_);
if($_ =~ /\d\d\d\d\-\d\d\-\d\d$/){
my $compress_file_path;
unless(-e ($compress_file_path = "$compress_path/$db_name/$_" . $compressed_file_extension)){
echo("Compressing $backup_path/$db_name/$_ to $compress_file_path ...");
#!!! This command structure assumes we are cded to $backup_path
`$compress_cmd $compress_file_path $db_name/$_`;
echo("Finished compressing $backup_path/$db_name/$_ .");
}else{
echo("Already compressed $backup_path/$db_name/$_ ...");
}
}
}
echo("Finished compressing $db_name backups.");
}else{
echo("Skipping compression of $db_name...");
}
}
sub delete_old{
my $db_name = shift();
my $delete_older_than = get_time(time - ($delete_backups_older_than * 86400)); #seconds per day
unless($skip_purge){
echo("Purging old $db_name backups...");
my @ls = `ls -1 $backup_path/$db_name`;
my $count = 0;
foreach(@ls){
$_ = trim($_);
$count++ if $_ =~ /\d\d\d\d\-\d\d\-\d\d$/; #so we don't accidentally count non-backups
}
if($count < $delete_backups_older_than){
echo("Not enough backups to start purging.");
return 0;
}
$delete_count = 0;
foreach(@ls){
last if $delete_count >= $max_purge;
(`rm -rf $backup_path/$db_name/$_`, $delete_count++, echo("deleting $backup_path/$db_name/$_")) if $_ lt $delete_older_than && $_ =~ /\d\d\d\d-\d\d\-\d\d$/; #the regexp so we don't accidentally delete non-backups
}
echo("Purge complete.");
}else{
echo("Skipping purge of $db_name...");
}
1;
}
sub handle_database{
my $db_name = shift();
echo("Handling $db_name ...");
backup_database($db_name);
delete_old($db_name);
compress_backups($db_name);
echo("Finished handling $db_name.");
1;
}
sub vacuum{
unless($skip_vaccum){
echo("vacuumdb started...");
`vacuumdb -a -z -U postgres`;
echo("vacuumdb finished.");
}else{
echo("Skipping vacuumdb...");
}
1;
}
# END FUNCTIONS
###############
###############
# SCRIPT
###############
vacuum();
handle_database($_) foreach get_databases();
Add the nightly db backup script to the crontab with crontab -e
MAILTO=test@test.com 0 0 * * * cd /var/lib/pgsql/9.0/backups/script; /usr/bin/perl backup.pl >>db_backup_log
This script will require some cpan modules that can be installed via
perl -MCPAN -e "install DBI" perl -MCPAN -e "install DBD::Pg"
Make sure the backup script has the proper permissions
chmod a+x /var/lib/pgsql/9.0/backups/script/backup.pl
