Spark-in.me. 4 |
backup , , setup , , .
, , . , .
. , - , ; , -, , , , , , . , , , , . , . , , .
. , . , . , . ? , . , , . , it-, , , , .
, , - , , . , , , . , , - .
, . , , . . , - , - .
, . , . :
TIME=`date '+%m-%d-%Y'`
mkdir -p /path/to-your-db-backups/$TIME
pg_dump mydb1 > /path/to-your-db-backups/$TIME/mydb1.sql
pg_dump mydb2 > /path/to-your-db-backups/$TIME/mydb2.sql
, , . - . (, , , ) ssh, . , .
, ssh ( , RSA, ). , , . , . , , , . secure copy scp. , , . .
, . , ssh- .
TIME=`date '+%m-%d-%Y'`
mkdir -p /path/to/your/backups/local/backup-$TIME
ssh sshuser@remote "pg_dump mydb > /etc/postgresql/x.y/backups/backup-$TIME.sql"
scp sshuser@remote:/etc/postgresql/x.y/backups/backup-$TIME.sql /path/to/you/backups/local/backup-$TIME
sleep 2m
ssh rsshuser@remote "rm /etc/postgresql/x.y/backups/backup-$TIME.sql"
, . , ssh- .
#START
# This Command will add date in Backup File Name.
TIME=`date +%b-%d-%y`
# Here i define Backup file name format.
FILENAME=some-system-backup-$TIME.tar.gz
# Backed up folder (system root) location
SRCDIR=/
# Destination of backup file
DESDIR=/home/backups
# exclude folder list
EXCLUDE='--exclude /home/backups --exclude=/another'
# Do not include files on a different filesystem
ONEFSYSPARAM='--one-file-system'
ssh sshuser@remote "tar -cpzf $DESDIR/$FILENAME $EXCLUDE $ONEFSYSPARAM $SRCDIR"
scp sshuser@remote:$DESDIR/$FILENAME /place/backup/here/
ssh sshuser@remote "echo 'WHOLE_SYSTEM_BACKUP is successful: $(date)' >> /home/bash-scripts/cron_log.log"
ssh sshuser@remote "rm $DESDIR/$FILENAME"
#END, . , . -, , ( ). -, ssh , , , . , , ( ), .
. . (crontab -e). , . , 4 ( , ls , ):
DIR=/path/to-your-backups/somesystem/somedate
ls -dt $DIR/* | tail -n +5 | xargs rm -r --
, , . . . . 14 , 9 , , . , .
, ( , ). .
tar -cpzf /destination/directory/filename --exclude /exclude/directory1 --exclude /exclude/directory2 --one-file-system /source/directory( ) ( ).
sql . , , . :
psql mydb1 < /path/to-your-db-backup/mydb1.sql, , . /var/lib/postgresql, /etc/postgresql ( ). , , . . (chmod ) , , postgres ( chown).
, . .
, postgresql.conf error reporting and logging. . . , ( , ), : , , , . ( ). , , .
. , , , . -.
- . ( , , . = 1 , 1 = 1 ). , , . , , . , .
, , . , .., , , ( , ).
create role somerole nocreaterole nocreateuser nocreatedb noinherit login noreplication connection limit 5 password 'somepassword';
alter role somerole set statement_timeout=30000;
grant select on all tables in schema public to somerole;
WITH inactive_connections AS (
SELECT
pid,
rank() over (partition by client_addr order by backend_start ASC) as rank
FROM
pg_stat_activity
WHERE
-- Exclude the thread owned connection (ie no auto-kill)
pid <> pg_backend_pid( )
AND
-- Exclude known applications connections
application_name !~ '(?:psql)|(?:pgAdmin.+)'
AND
-- Include connections to the same database the thread is connected to
datname = current_database()
AND
-- Include connections using the same thread username connection
usename = current_user
AND
-- Include inactive connections only
state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND
-- Include old connections (found with the state_change field)
current_timestamp - state_change > interval '5 minutes'
)
SELECT
pg_terminate_backend(pid)
FROM
inactive_connections
WHERE
rank > 1 -- Leave one connection for each application connected to the database
=)
